Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to : Insert If Not Exist, Update When Exist, Then Returning the Value ?

437457Mar 24 2007 — edited Mar 24 2007
Hi all,

I have a table :
CREATE TABLE doc_number ( register VARCHAR2(3),
doc_code VARCHAR2(6), doc_nbr NUMBER(12))

I need to do the following :
- If row already exist, update doc_nbr = doc_nbr+1, then return the last doc_nbr values
- if not exist, inser the row, doc_nbr = 1, also return the last doc_nbr value

I cannot use MERGE because of the Returning Values.

Right now I have this statement :

Update doc_number set doc_nbr = doc_nbr + 1
Where register = pRegsCode and doc_code = pDocCode
Returning TO_CHAR(doc_nbr, '0000000') into vDocNo ;

How to implement the INSERT if NOT EXIST ?

Thank you for your help,
xtanto

Comments

MichaelS
Maybe this:
BEGIN
   UPDATE    doc_number
         SET doc_nbr = doc_nbr + 1
       WHERE REGISTER = pregscode AND doc_code = pdoccode
   RETURNING TO_CHAR (doc_nbr, '0000000')
        INTO vdocno;

   IF SQL%ROWCOUNT = 0
   THEN
      INSERT INTO doc_number
           VALUES (myregister,
                   mydoc_code,
                   mydoc_nbr
                  )
        RETURNING TO_CHAR (doc_nbr, '0000000')
             INTO vdocno;
   END IF;
END;
/
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 21 2007
Added on Mar 24 2007
1 comment
773 views