| Merge into table name using cursor [message #462885] |
Mon, 28 June 2010 08:51  |
amul
Messages: 252
Registered: April 2001
Location: Chennai
|
Senior Member |
|
|
Hi,
I am trying to merge into test table using a cursor.
If the contract number in the test table matches with the contract number from the cursor then i need to update the creation_date else i need to insert values from the cursor into the test table. I know this is wrong usage of cursor, but i would appreciate suggestions regarding this.
FOR i IN cursortest
MERGE INTO test
USING cursortest
ON (i.contract_number = test.contract_number)
WHEN MATCHED THEN
UPDATE
SET creation_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT (customer_id, contract_number, creation_date)
VALUES (i.customer_cpr_id, i.contract_number,SYSDATE);
end loop; |
|
|
|
|
|
|
|
| Re: Merge into table name using cursor [message #462889 is a reply to message #462887] |
Mon, 28 June 2010 09:15  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
In this cases (if contract_number is a primary key or a unique index) I often use the "try to insert, if it runs on a dup_val_on_index do an update" approach.
SQL> CREATE TABLE test_1 (
2 colA NUMBER(10),
3 colB NUMBER(10)
4 );
Table created.
SQL>
SQL> ALTER TABLE test_1 ADD CONSTRAINT pk_test_1 PRIMARY KEY(colA);
Table altered.
SQL>
SQL> CREATE TABLE test_2 (
2 colA NUMBER(10),
3 colB NUMBER(10)
4 );
Table created.
SQL>
SQL> INSERT INTO test_1 VALUES (1,1);
1 row created.
SQL> INSERT INTO test_1 VALUES (2,1);
1 row created.
SQL> INSERT INTO test_1 VALUES (3,1);
1 row created.
SQL> INSERT INTO test_1 VALUES (4,1);
1 row created.
SQL> INSERT INTO test_1 VALUES (5,1);
1 row created.
SQL>
SQL> SELECT * FROM test_1;
COLA COLB
---------- ----------
1 1
2 1
3 1
4 1
5 1
SQL>
SQL>
SQL> INSERT INTO test_2 VALUES (1,1);
1 row created.
SQL> INSERT INTO test_2 VALUES (2,2);
1 row created.
SQL> INSERT INTO test_2 VALUES (3,3);
1 row created.
SQL> INSERT INTO test_2 VALUES (4,4);
1 row created.
SQL> INSERT INTO test_2 VALUES (5,5);
1 row created.
SQL> INSERT INTO test_2 VALUES (6,6);
1 row created.
SQL> INSERT INTO test_2 VALUES (7,7);
1 row created.
SQL>
SQL> SELECT * FROM test_2;
COLA COLB
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
7 rows selected.
SQL>
SQL> DECLARE
2 BEGIN
3 FOR l2 IN (SELECT * FROM test_2) LOOP
4 BEGIN
5 INSERT INTO test_1 VALUES l2;
6
7 EXCEPTION
8 WHEN Dup_Val_On_Index THEN
9 UPDATE test_1
10 SET ROW = l2
11 WHERE test_1.colA = l2.colA;
12 END;
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM test_1;
COLA COLB
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
7 rows selected.
SQL>
It might perhaps not be as fast as the merge, but it is definetely 'saver' when each row is for example a sale or a contract or another document where you want the other insert/update of the other documents to succeed even if one document has problems.
|
|
|
|
|
|
| Re: Merge into table name using cursor [message #462896 is a reply to message #462890] |
Mon, 28 June 2010 09:58 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, how else would you process "public" interface tables in your application (tables that other applications can fill to update or insert data in your internal applicaton tables, for example, a customer entered into the CRM system is transfered to the financial system).
There the basic requirement that you have to provide is that all rows (or other appropriate logical units) that can be process are processed, and rows with errors have to be flagged as invalid.
In that cases there really is no other way than row-by-row processing. People would be pretty pissed if a night batch with five million documents has completely failed because one document had an logical error the single merge statement you tried to run failed.
|
|
|
|
| Re: Merge into table name using cursor [message #462919 is a reply to message #462896] |
Mon, 28 June 2010 11:39 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Well, I did not say "in all cases". And I did not say "use MERGE for that". For "five million document update" scenario I would explore BULK UPDATE with LIMIT, for "CRM system is transfered to the financial system" I would look into replication and/or materialized views, etc. All my reply was suggesting OP should disect the design before implementing slow-by-slow solution.
SY.
|
|
|
|
| Re: Merge into table name using cursor [message #463399 is a reply to message #462885] |
Thu, 01 July 2010 03:16 |
connerly
Messages: 2
Registered: June 2010
Location: china
|
Junior Member |
|
|
why didn't you code like this:
declare
num_contract number;
stmt varchar2(2000);
begin
...
FOR i IN cursortest
num_contract:=i.contract_number;
stmt :='MERGE INTO test
USING cursortest
ON ('||num_contract||' = test.contract_number)
WHEN MATCHED THEN
UPDATE
SET creation_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT (customer_id, contract_number, creation_date)
VALUES (i.customer_cpr_id, i.contract_number,SYSDATE)';
execute immediate stmt;
end loop;
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
