Hi All, I have 2 questions related to Merge.
Below is the code related to that:
CREATE TABLE TAB_SEQ_NAME
(TABLE_NAME VARCHAR2(50),
SEQ_NAME VARCHAR2(50));
INSERT INTO TAB_SEQ_NAME VALUES('TAB1', 'TAB1_SEQ');
INSERT INTO TAB_SEQ_NAME VALUES('TAB2', 'TAB2_SEQ');
CREATE TABLE TAB_KEYS
(TABLE_NAME VARCHAR2(50),
SOURCE_KEY NUMBER,
TARGET_KEY NUMBER);
INSERT INTO TAB_KEYS VALUES('TAB1', 7, NULL);
INSERT INTO TAB_KEYS VALUES('TAB1', 4, NULL);
INSERT INTO TAB_KEYS VALUES('TAB1', 9, NULL);
INSERT INTO TAB_KEYS VALUES('TAB1', 3, NULL);
INSERT INTO TAB_KEYS VALUES('TAB2', 23, NULL);
INSERT INTO TAB_KEYS VALUES('TAB2', 20, NULL);
INSERT INTO TAB_KEYS VALUES('TAB2', 4, NULL);
INSERT INTO TAB_KEYS VALUES('TAB2', 1, NULL);
CREATE SEQUENCE TAB1_SEQ
MINVALUE 200
MAXVALUE 999999999999999999999999999
START WITH 200
INCREMENT BY 1;
CREATE SEQUENCE TAB2_SEQ
MINVALUE 100
MAXVALUE 999999999999999999999999999
START WITH 100
INCREMENT BY 1;
SET SERVEROUTPUT ON;
DECLARE
V_SQL VARCHAR2(1000);
BEGIN
FOR I IN (SELECT TABLE_NAME, SEQ_NAME
FROM TAB_SEQ_NAME) LOOP
V_SQL := 'MERGE INTO TAB_KEYS TGT
USING (SELECT A.TABLE_NAME, A.SOURCE_KEY, A.TARGET_KEY, B.SEQ_NAME
FROM TAB_KEYS A, TAB_SEQ_NAME B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = ''' || I.TABLE_NAME ||
''' ORDER BY A.SOURCE_KEY) SRC
ON ( TGT.TABLE_NAME = SRC.TABLE_NAME AND TGT.SOURCE_KEY=SRC.SOURCE_KEY)
WHEN MATCHED THEN
UPDATE SET TGT.TARGET_KEY = ' || I.SEQ_NAME || '.NEXTVAL';
DBMS_OUTPUT.PUT_LINE(V_SQL);
END LOOP;
END;
/
Output:
MERGE INTO TAB_KEYS TGT
USING (SELECT A.TABLE_NAME, A.SOURCE_KEY, A.TARGET_KEY, B.SEQ_NAME
FROM TAB_KEYS A, TAB_SEQ_NAME B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = 'TAB1' ORDER BY A.SOURCE_KEY) SRC
ON ( TGT.TABLE_NAME = SRC.TABLE_NAME AND TGT.SOURCE_KEY=SRC.SOURCE_KEY)
WHEN MATCHED THEN
UPDATE SET TGT.TARGET_KEY = TAB1_SEQ.NEXTVAL;
MERGE INTO TAB_KEYS TGT
USING (SELECT A.TABLE_NAME, A.SOURCE_KEY, A.TARGET_KEY, B.SEQ_NAME
FROM TAB_KEYS A, TAB_SEQ_NAME B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = 'TAB2' ORDER BY A.SOURCE_KEY) SRC
ON ( TGT.TABLE_NAME = SRC.TABLE_NAME AND TGT.SOURCE_KEY=SRC.SOURCE_KEY)
WHEN MATCHED THEN
UPDATE SET TGT.TARGET_KEY = TAB2_SEQ.NEXTVAL;
Here are my questions:
1. When the statements are executed, I need the seq val to be generated as
per the order of the source_key.
I got this
TAB1 7 204
TAB1 4 205
TAB1 3 206
TAB1 9 207
but How can i get?
TAB1 3 204
TAB1 4 205
TAB1 7 206
TAB1 9 207
2. I am using the loop to substitute the seq name which is in the column.
Can the loop be avoided with just one merge statement?
Thanks in advance.
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
