Oracle创建主键时处理重复数据的程序
/****************************
v_index 为主键语句
tablename 为表名
written by:wonder
modified by:
****************************/
v_index 为主键语句
tablename 为表名
written by:wonder
modified by:
****************************/
CREATE OR REPLACE PROCEDURE prc_delete(v_index IN VARCHAR2, tablename IN VARCHAR2) IS
v_sql VARCHAR2(2000);
v_sql1 VARCHAR2(2000);
BEGIN
EXECUTE IMMEDIATE upper('truncate table except_table');
v_sql1 := upper(REPLACE(v_index, ';', ' ') || ' exceptions into except_table');
BEGIN
EXECUTE IMMEDIATE v_sql1;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
v_sql := upper('delete from ' || tablename || ' where rowid in (select row_id from except_table where table_name=''' ||
tablename || ''')');
EXECUTE IMMEDIATE v_sql;
EXECUTE IMMEDIATE REPLACE(v_index, ';', ' ');
COMMIT;
END prc_delete;