SCOTT@orcl> edit tablefunc.sql
1 -------------------------------------------------------------
2 -- --
3 -- Usage: start scriptname --
4 -- --
5 -------------------------------------------------------------
6 create or replace type typ_varc2tab is table of varchar2(200);
7 /
8 create or replace function typ_returntab(
9 p_str varchar2) return typ_varc2tab
10 is
11 /*****************************************************
12 * ver1.0 Created by xxx on 2012-09-20
13 *
14 * create type typ_varc2tab is table of varchar2(200);
15 ******************************************************/
16 v_numtab typ_varc2tab := typ_varc2tab();
17 begin
18 for rec in (select rownum r1,table_name
19 from user_tables where table_name like '%'|| p_str ||'%')
20 loop
21 v_numtab.extend;
22 v_numtab(rec.r1) := rec.table_name;
23 end loop;
2 -- --
3 -- Usage: start scriptname --
4 -- --
5 -------------------------------------------------------------
6 create or replace type typ_varc2tab is table of varchar2(200);
7 /
8 create or replace function typ_returntab(
9 p_str varchar2) return typ_varc2tab
10 is
11 /*****************************************************
12 * ver1.0 Created by xxx on 2012-09-20
13 *
14 * create type typ_varc2tab is table of varchar2(200);
15 ******************************************************/
16 v_numtab typ_varc2tab := typ_varc2tab();
17 begin
18 for rec in (select rownum r1,table_name
19 from user_tables where table_name like '%'|| p_str ||'%')
20 loop
21 v_numtab.extend;
22 v_numtab(rec.r1) := rec.table_name;
23 end loop;
24
25 return v_numtab;
26 exception
27 when others then
28 v_numtab.delete;
29 end;
30 /
31
32 select * from table(typ_returntab('F_REGS'))
33 /
25 return v_numtab;
26 exception
27 when others then
28 v_numtab.delete;
29 end;
30 /
31
32 select * from table(typ_returntab('F_REGS'))
33 /
SCOTT@orcl> edit tablefunc.sql
SCOTT@orcl> start tablefunc.sql
Type created.
Function created.
COLUMN_VALUE
--------------------------------------------------------------------------------
F_REGS
1 row selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27795363/viewspace-744471/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
1-7. 高效删除表数据
下一篇：
10-15 跟踪SQL会话
请登录后发表评论
登录
全部评论
