AND table_name LIKE '%'||TBNAME||'%' --模糊匹配表名。可生成相同部分结构表的过程

AND table_name NOT LIKE '%$%'; --去掉回收站被删除的表

CURSOR r_pk(tablename VARCHAR2) IS

SELECT * FROM dba_tab_columns WHERE owner=DBUSER

AND table_name=tablename AND column_name IN

(

SELECT col.column_name FROM user_constraints con, user_cons_columns col

WHERE con.constraint_name=col.constraint_name AND con.constraint_type='P' AND col.table_name=tablename

)

ORDER BY column_id;

r_tb_list dba_tables%rowtype;

r_pk_list dba_tab_columns%rowtype;

wherecol VARCHAR2(4000):='';

BEGIN

OPEN r_tb;

LOOP--外循环

FETCH r_tb INTO r_tb_list;

EXIT WHEN r_tb%notfound;

sqltext:=sqltext||'--------------------------------------------------------'||CHR(10);

sqltext:=sqltext||'-- DDL for Procedure DEL_'||substr(r_tb_list.table_name,0,25)||'(删除'||r_tb_list.table_name||'表记录)'||CHR(10);

sqltext:=sqltext||'--------------------------------------------------------'||CHR(10);

sqltext:=sqltext||'CREATE OR REPLACE PROCEDURE DEL_'||substr(r_tb_list.table_name,0,25)||CHR(10)||'('||CHR(10);

wherecol:='';

OPEN r_pk(r_tb_list.table_name);

LOOP

FETCH r_pk INTO r_pk_list;

EXIT WHEN r_pk%notfound;

wherecol:=wherecol||r_pk_list.column_name||'=p'||r_pk_list.column_name||' AND '; --参数名称默认以小写'p'开头，可以修改该处的规则为想要的命名方式

sqltext:=sqltext||' p'||r_pk_list.column_name||' IN '||r_pk_list.data_type||','||CHR(10); --参数名称默认以小写'p'开头，可以修改该处的规则为想要的命名方式

END LOOP;

CLOSE r_pk;

IF LENGTH(wherecol)<>0 THEN

sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||CHR(10)||')'||CHR(10)||'AS'||CHR(10)||'BEGIN'||CHR(10)||' DELETE '||r_tb_list.table_name||' WHERE '||substr(wherecol, 0, LENGTH(wherecol)-5)||';'||CHR(10)||' COMMIT;'||CHR(10)||'END DEL_'||substr(r_tb_list.table_name,0,25)||';'||CHR(10)||'/'||CHR(10);

ELSE

sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||'AS'||CHR(10)||'BEGIN'||CHR(10)||' DELETE '||r_tb_list.table_name||';'||CHR(10)||' COMMIT;'||CHR(10)||'END DEL_'||substr(r_tb_list.table_name,0,25)||';'||CHR(10)||'/'||CHR(10);

END IF;

END LOOP;

CLOSE r_tb;

END;

/

create or replace PROCEDURE UPDATE_ALL_TABLES

--功能:执行修改的存储过程(根据主键列修改,注意标识符不能超过30个字符),返回大字段，需要保存到文件进行读取

--参数:DBUSER-数据库用户，TBNAME-模糊匹配的表名，如果为空或''则生成全库的过程，SQLTEXT-返回的存储过程大文本字段

--调用:

--作者:郭君

--日期:2013-02-10

(

DBUSER IN VARCHAR2,

TBNAME IN VARCHAR2,

SQLTEXT OUT CLOB

)

AS

CURSOR r_tb IS --参数传递

SELECT * FROM dba_tables WHERE owner=DBUSER

AND table_name LIKE '%'||TBNAME||'%' --模糊匹配表名。可生成相同部分结构表的过程

AND table_name NOT LIKE '%$%'; --去掉回收站被删除的表

CURSOR r_col(TABLENAME VARCHAR2) IS

SELECT * FROM dba_tab_columns WHERE owner=DBUSER

AND table_name=TABLENAME AND column_name NOT IN

(

SELECT col.column_name FROM user_constraints con, user_cons_columns col

WHERE con.constraint_name=col.constraint_name AND con.constraint_type='P' AND col.table_name=TABLENAME

)

ORDER BY column_id;

CURSOR r_pk(TABLENAME VARCHAR2) IS

SELECT * FROM dba_tab_columns WHERE owner=DBUSER AND table_name NOT LIKE '%$%'