(

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_col_list dba_tab_columns%rowtype;

r_pk_list dba_tab_columns%rowtype;

SETCOL VARCHAR2(4000):='';

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 UPDATE_'||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 UPDATE_'||substr(r_tb_list.table_name,0,23)||CHR(10)||'('||CHR(10);

SETCOL:='';

WHERECOL:='';

OPEN r_pk(r_tb_list.table_name);

LOOP

FETCH r_pk INTO r_pk_list;

EXIT WHEN r_pk%notfound;

sqltext:=sqltext||' p'||r_pk_list.column_name||' IN '||r_pk_list.data_type||','||CHR(10);

WHERECOL:=WHERECOL||r_pk_list.column_name||'=p'||r_pk_list.column_name||' AND ';

END LOOP;

CLOSE r_pk;

OPEN r_col(r_tb_list.table_name);

LOOP

FETCH r_col INTO r_col_list;

EXIT WHEN r_col%notfound;

sqltext:=sqltext||' p'||r_col_list.column_name||' IN '||r_col_list.data_type||','||CHR(10);

SETCOL:=SETCOL||r_col_list.column_name||'=p'||r_col_list.column_name||',';

END LOOP;

CLOSE r_col;

SETCOL:= substr(SETCOL, 0, LENGTH(SETCOL)-1);

SELECT DECODE(WHERECOL,'','',' WHERE '||substr(WHERECOL, 0, LENGTH(WHERECOL)-5)) INTO WHERECOL FROM DUAL;

sqltext:=substr(sqltext, 0, LENGTH(sqltext)-2)||CHR(10)||')'||CHR(10)||'AS'||CHR(10)||'BEGIN'||CHR(10)||' UPDATE '||r_tb_list.table_name||' SET '||SETCOL||WHERECOL||';'||CHR(10)||' COMMIT;'||CHR(10)||'END UPDATE_'||substr(r_tb_list.table_name,0,23)||';'||CHR(10)||'/'||CHR(10);

END LOOP;

CLOSE r_tb;

END;

/

create or replace PROCEDURE DROP_ALL_OBJECTS

--功能:删除用户下所有的结构关系

--参数:DBUSER-数据库用户，TAG-结构类型，见下方说明

--调用:EXECUTE DROP_ALL_OBJECTS('OPENVONE',0)

--作者:郭君

--日期:2013-02-10

(

DBUSER IN VARCHAR2,

TAG IN NUMBER

)

--操作均基于指定用户下进行，其他用户不做处理

--0.删除全部

--1.删除视图

--2.删除函数

--3.删除过程

--4.删除类型

--5.删除包

--6.删除触发器

--7.删除表（解除主键关系再删除）

--8.删除序列

--9.清空作业

--10.清空回收站

AS

CURSOR CUR_OBJECTS IS --表、视图、函数、过程、类型、包、触发器、序列

SELECT * FROM DBA_OBJECTS WHERE OWNER=DBUSER

AND OBJECT_NAME<>'DROP_ALL_OBJECTS' --不能删除自己

AND OBJECT_TYPE IN('VIEW','FUNCTION','PROCEDURE','TYPE','PACKAGE','TRIGGER','TABLE','SEQUENCE')

AND OBJECT_TYPE=DECODE(TAG,0,OBJECT_TYPE,1,'VIEW',2,'FUNCTION',3,'PROCEDURE',4,'TYPE',5,'PACKAGE',6,'TRIGGER',7,'TABLE',8,'SEQUENCE')

ORDER BY DECODE(OBJECT_TYPE,'VIEW',1,'FUNCTION',2,'PROCEDURE',3,'TYPE',4,'PACKAGE',5,'TRIGGER',6,'TABLE',7,'SEQUENCE',8);

CURSOR CUR_JOBS IS --作业