SELECT NAME SORT1, LINE SORT2, 'Create or Replace ' || TEXT FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND LINE = 1 UNION SELECT NAME SORT1, LINE SORT2, TEXT FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND LINE > 1 UNION SELECT NAME SORT1, 999999 SORT2, '/' FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND LINE = 1 ORDER BY 1, 2;TYPE里自己改成你所需要查找的,比如TYPE = 'INSERT INTO EMP'
晕,还真有办法啊,长见识叻。试了一下,可行。 select * from( SELECT NAME SORT1, LINE SORT2, 'Create or Replace ' || TEXT as text FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND LINE = 1 UNION SELECT NAME SORT1, LINE SORT2, TEXT as text FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND LINE > 1 UNION SELECT NAME SORT1, 999999 SORT2, '/' as text FROM USER_SOURCE WHERE TYPE = 'PROCEDURE' AND LINE = 1 ORDER BY 1, 2 ) c where UPPER(c.text) like '%INSERT INTO DAT_CT_USER%';
如果insert into ..在2行甚至3行里面体现就不行了 如果要这样写的话 直接 select distinct name from user_source where type='PROCEDURE' and lower(text) like '%insert%into%table1%'
SQL> create procedure pro3 2 is 3 begin 4 dbms_output.put_line('wang 5 hai 6 up'); 7 end; 8 /过程已创建。 SQL> select distinct name from user_source 2 where type='PROCEDURE' 3 and lower(text) like '%wang%hai%';NAME ---------- PRO1 --这里为什么PRO3没显示出来呢?SQL> select distinct name from user_source 2 where type='PROCEDURE' 3 and lower(text) like '%wang%';NAME ---------- PRO2 PRO1 PRO3
PRO1和PRO2的定义语句如下 SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO2','SCOTT') text from dual;
TEXT ----------------------------------------
CREATE OR REPLACE PROCEDURE "SCOTT"."P RO2" is begin dbms_output.put_line('wangpeng up'); end;
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO1','SCOTT') text from dual;
TEXT ----------------------------------------
CREATE OR REPLACE PROCEDURE "SCOTT"."P RO1" is begin dbms_output.put_line('wanghai up'); end;
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2, TEXT
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2, '/'
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
ORDER BY 1, 2;TYPE里自己改成你所需要查找的,比如TYPE = 'INSERT INTO EMP'
晕,还真有办法啊,长见识叻。试了一下,可行。
select * from(
SELECT NAME SORT1, LINE SORT2, 'Create or Replace ' || TEXT as text
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
UNION
SELECT NAME SORT1, LINE SORT2, TEXT as text
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE > 1
UNION
SELECT NAME SORT1, 999999 SORT2, '/' as text
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND LINE = 1
ORDER BY 1, 2
) c
where UPPER(c.text) like '%INSERT INTO DAT_CT_USER%';
如果要这样写的话
直接
select distinct name from user_source
where type='PROCEDURE'
and lower(text) like '%insert%into%table1%'
2 is
3 begin
4 dbms_output.put_line('wang
5 hai
6 up');
7 end;
8 /过程已创建。
SQL> select distinct name from user_source
2 where type='PROCEDURE'
3 and lower(text) like '%wang%hai%';NAME
----------
PRO1 --这里为什么PRO3没显示出来呢?SQL> select distinct name from user_source
2 where type='PROCEDURE'
3 and lower(text) like '%wang%';NAME
----------
PRO2
PRO1
PRO3
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO2','SCOTT') text from dual;
TEXT
----------------------------------------
CREATE OR REPLACE PROCEDURE "SCOTT"."P
RO2"
is
begin
dbms_output.put_line('wangpeng up');
end;
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO1','SCOTT') text from dual;
TEXT
----------------------------------------
CREATE OR REPLACE PROCEDURE "SCOTT"."P
RO1"
is
begin
dbms_output.put_line('wanghai up');
end;