How to use procedure parameters in merge statement

Question

Hi, i'm creating a procedure to update/insert a table using merge statement(upsert).now i have a problem: using procedure parameters i have to do this upsert.

procedure xyz( a in table.a%type,b in table.b%type,....) is some local variables; begin merge into target_table using source_table --instead of the source table, i have to use procedure parameters here on (condition on primary key in the table) when matched then update the table when not matched then insert the table ; end xyz; so how to use procedure parameters instead of source table in merge statement?? or suggest me a query to fetch the procedure parameters and use it as source table values.

help me please. Thanks in advance.

Answer 1

Maby something like

DECLARE V_EXISTS NUMBER;
BEGIN SELECT COUNT(*) INTO V_EXISTS FROM TARGET_TABLE WHERE PK_ID = :ID;

    IF V_EXISTS  > 0 THEN
     -- UPDATE
    ELSE
     -- INSERT
    END IF;
END;

Also, you may try to use so-called tempotary table (select from DUAL)

CREATE TABLE TEST (N NUMBER(2), NAME VARCHAR2(20), ADRESS VARCHAR2(100));
INSERT INTO TEST VALUES(1, 'Name1', 'Adress1');
INSERT INTO TEST VALUES(2, 'Name2', 'Adress2');
INSERT INTO TEST VALUES(3, 'Name3', 'Adress3');
SELECT * FROM TEST;
-- test update
MERGE INTO TEST trg
USING (SELECT 1 AS N, 'NameUpdated' AS NAME, 
  'AdressUpdated' AS ADRESS FROM Dual )  src                
ON ( src.N = trg.N )
WHEN MATCHED THEN
    UPDATE 
    SET trg.NAME = src.NAME,
        trg.ADRESS = src.ADRESS
WHEN NOT MATCHED THEN
    INSERT VALUES (src.N, src.NAME, src.ADRESS);
SELECT * FROM TEST;
-- test insert
MERGE INTO TEST trg
USING (SELECT 34 AS N, 'NameInserted' AS NAME, 
  'AdressInserted' AS ADRESS FROM Dual )  src                
ON ( src.N = trg.N )
WHEN MATCHED THEN
    UPDATE 
    SET trg.NAME = src.NAME,
        trg.ADRESS = src.ADRESS
WHEN NOT MATCHED THEN
    INSERT VALUES (src.N, src.NAME, src.ADRESS);
SELECT * FROM TEST;
DROP TABLE TEST;

see here

Answer 2

Its very difficult to tell from you question exactly what you what, but I gather you want the table that you are merging into ( or on ) to be dynamic. In that case, what you should be using is the DBMS_SQL package to create dynamic SQL