Re: Stored Procedure Help

>Please help!
>
>Does anyone have an example of a stored procedure that takes several input
>parameters of keys and values and does a select on a row, if the row exists
>then do an update, else, do an insert? A return code is returned indicating
>success/fail and whether an insert or update was done.
>
>Thanks,
>Bill
>
I don't have a specific example, but what I believe should work is something like this:

function doit (...) return varchar2 is
declare

      cursor target_data is select <something> from <table> where <...> for update;

      my_data  <.... to put results of fetch into>
      return_status varchar2(10);
begin
     open target_data (...);
     fetch target_data into my_data;
     if target_data%found then
          update <table> set ...=... where current of target_data;
          return_status := 'Update';
     else
          insert into <table>...;
          return_status := 'Insert';
     end if;
     close target_data;
     return return_status;

You really need to put some exception handling around in case the update or insert fail; in that case change the return_status to 'Error', or null, or something.

Hope this gets you started.

Dan Received on Sat Feb 07 1998 - 00:00:00 CET