|
|
| Re: pl/sql stored procedure [message #37976 is a reply to message #37972] |
Tue, 12 March 2002 03:16   |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
pls try this sample
SQL> CREATE TABLE test( col1 NUMBER, col2 VARCHAR2(10));
/
INSERT INTO test VALUES(10, 'asd');
INSERT INTO test VALUES(10, 'asd');
INSERT INTO test VALUES(10, 'asd');
INSERT INTO test VALUES(20, 'zx');
INSERT INTO test VALUES(30, 'zx');
INSERT INTO test VALUES(10, 'asd');
INSERT INTO test VALUES(10, 'asd');
INSERT INTO test VALUES(10, 'asd');
INSERT INTO test VALUES(20, 'zx');
INSERT INTO test VALUES(30, 'zx');
/
CREATE OR REPLACE PROCEDURE del_dup
AS
BEGIN
DELETE FROM test_table WHERE
ROWID NOT IN ( SELECT MIN(ROWID) FROM test
GROUP BY col1);
END dup1;
/
|
|
|
|
| Re: pl/sql stored procedure [message #38004 is a reply to message #37972] |
Wed, 13 March 2002 03:23 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Or try using dynamic SQL:
CREATE OR REPLACE PROCEDURE del_duplicate_val(p_tname IN varchar2,
p_pkname IN varchar2)
AS
BEGIN
execute immediate
'BEGIN
DELETE FROM ' || p_tname || ' a
WHERE ROWID > (SELECT min(rowid)
FROM ' || p_tname || ' b
WHERE a.' || p_pkname || ' = b.' || p_pkname || ');
END;';
COMMIT;
END;
/
SQL> desc temp_tab
Name Null? Type
----------------------------------------- -------- --------------
TNO NOT NULL NUMBER(5)
TVALUE VARCHAR2(30)
SQL> @del_dup.sql
Procedure created.
SQL> select * from temp_tab;
TNO TVALUE
-------------- ------------------------------
1234 Testing 1
1234 Testing 2
2345 Testing 3
3456 Testing 4
SQL> exec del_duplicate_val('temp_tab', 'tno');
PL/SQL procedure successfully completed.
SQL> select * from temp_tab;
TNO TVALUE
-------------- ------------------------------
1234 Testing 1
2345 Testing 3
3456 Testing 4
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
