oracle如何只导出存储过程与触发器

1459

1页

1次

2020-04-26

5墨值下载

用 PL/SQL DEVELOPER 或者 TOAD 都行。如果实在找不到，可以从 user_source 里查。代码如下：

create or replace procedure P_CREATEOPEROBJECT(OPERUSER varchar2) is

vMaxline number(10);

vTableExist number(1);

vObjectName varchar2(40);

vObjectType varchar2(15);

vSqltext varchar2(300);

cursor c_fetch_object_name is

select distinct name,type

from user_source

where type in ('PACKAGE','PACKAGE BODY','TRIGGER');

begin

vSqltext := 'CREATE TABLE TMP_OPEROBJECT (NAME VARCHAR2(40),LINE

NUMBER(10),TYPEKIND VARCHAR2(15),TEXT VARCHAR2(1000));';

execute immediate vSqltext;

Open c_fetch_object_name;

Loop

Fetch c_fetch_object_name

into vObjectName,vObjectType;

select max(line) into vMaxline from user_source where name = vObjectname and

type = vObjectType;

insert into TMP_OPEROBJECT(name,line,typekind,text)

select a.name,a.line,a.type,

(case when a.line = 1 then 'CREATE OR REPLACE '||a.text

when a.line = vMaxline then a.text||CHR(13)||CHR(10)||'/'

else a.text end)

from user_source a

where a.name = vObjectName and a.type = vObjectType;

commit;

End Loop;

Close c_fetch_object_name;

end P_CREATEOPEROBJECT;

of 1

5墨值下载

关注