[SOLVED] Procedures in Oracle

[Adam]

Hey up guys,

 

Working with some procedures in Oracle. I'm not brilliant with PL/SQL syntax yet so I'm a little stuck with this and lack the terminology to search for it properly on Google. What I'd like to do is run a select query that will hopefully return an `item_id`. If there is an item ID then I'd like to run an update, if not, an insert. My code looks like this:

 

  select item_id into vItemId
  from replaced_table
  where
    replace_field = pSitecode
    and replaced_field = pLang
    and replaced_field = pPageId
    and replaced_field = pGroupId
    and replaced_field = pRuleId
    and replaced_field = pTypeId
    and replaced_field = pSku;
    
  if vItemId <> null then
    ...
  else
    ...
  end if;

 

However I'm getting a compile error in SQL developer with that, saying that the if statement is unreachable code? To be honest though I don't actually know how to test if the query returned a result, or to test if `vItemId` contains any data (and I'm not entirely sure what value it would have if there was no results returned - guessed null).

 

Could anybody shed any light on this?

 

NB. To be on the safe side of violating my contract I just replaced the table and field names.

 

Thanks

 

Adam

[artacus]

You may want to review how null comparisons work (or don't in your case). Use IS NOT NULL or IS NULL instead.

 

Also your life would be easier if you used the MERGE statement instead.

http://www.psoug.org/reference/merge.html

[Adam]

Thanks a lot for the reply. I've been trying to use the merge statement but having a few troubles. This is the code I have so far:

 

    merge into merchandise_items mi
      using dual
      on (mi.item_id = pItemId)
      when matched then
        update
        set ...
      when not matched then
        select merchandise_container_seq.nextval into vItemId from dual;
        insert
        (
          ...
        )
        values
        (
          ...
        );

 

I'm not exactly sure what the problem is, but all the field names, though left out where not essential, are correct and so shouldn't be causing the problem. In SQL developer I'm getting the following errors:

 

Error(1166,5): PL/SQL: SQL Statement ignored

> Refers to: "merge into merchandise_items mi"

 

Error(1184,9): PL/SQL: ORA-00905: missing keyword

> Refers to: "select merchandise_container_seq.nextval into vItemId from dual;"

 

Error(1185,9): PL/SQL: SQL Statement ignored

> Refers to: "insert"

 

Error(1186,9): PL/SQL: ORA-00925: missing INTO keyword

> Refers to: "(" - just underneath "insert"

 

I'd be very grateful if you, or anyone else, could take another quick look and see if you can spot what the error is?

 

Thanks again for your help,

 

Adam

[Adam]

Sorted it. The problem was the sequence select; moved it before the merge and all the errors disappeared. Thanks for your advice! I've not quite finished the job yet but I think I'll be able to work out the rest on my own now, thanks again!

 

Adam