/*
PL/SQL表---table()函数用法:
利用table()函数,我们可以将PL/SQL返回的结果集代替table。
oracle内存表在查询和报表的时候用的比较多,它的速度相对物理表要快几十倍。
simple example:
1、table()结合数组:
*/
create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);
create or replace type t_test_table as table of t_test;
create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
end loop;
return v_test;
end f_test_array;
/
select * from table(f_test_array(10));
select * from the(select f_test_array(10) from dual);
/*
2、table()结合PIPELINED函数:
*/
create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
pipe row(t_test(i,sysdate,'mc'||i));
end loop;
return;
end f_test_pipe;
/
select * from table(f_test_pipe(20));
select * from the(select f_test_pipe(20) from dual);
/*
3、table()结合系统包:
*/
create table test (id varchar2(20));
insert into test values('1');
commit;
explain plan for select * from test;
select * from table(dbms_xplan.display);
oracle 管道化表函数(Pipelined Table)
管道化表函数必须返回一个集合。在函数中,PIPE ROW 语句被用来返回该集合的单个元素,该函数必须以一个空的 RETURN 语句结束,以表明它已经完成。一旦我们创建了上述函数,我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
例1:
Type Definition
- CREATE OR REPLACE TYPE COLOR_HEX_CODE AS OBJECT
- (
- -- Attributes
- colorName Varchar2(10),
- colorCode varchar2(10)
- -- Member functions and procedures
- --member procedure <ProcedureName>(<Parameter> <Datatype>)
- );
Create Table Type
- CREATE OR REPLACE TYPE COLOR_TYPE AS TABLE OF COLOR_HEX_CODE;
Create Table Function
- CREATE OR REPLACE FUNCTION F_PIPELINE_TEST RETURN COLOR_TYPE PIPELINED
- AS
- BEGIN
- PIPE ROW(COLOR_HEX_CODE('black','#000000'));
- PIPE ROW(COLOR_HEX_CODE('white','#FFFFFF'));
- PIPE ROW(COLOR_HEX_CODE('red','#FF0000'));
- PIPE ROW(COLOR_HEX_CODE('green','#00FF00'));
- PIPE ROW(COLOR_HEX_CODE('blue','#0000FF'));
- RETURN;
- END;
测试:
- select * from table( f_pipeline_test );
结果:
COLORNAME COLORCODE
black #000000
white #FFFFFF
red #FF0000
green #00FF00
blue #0000FF
例2:实现split函数功能
- CREATE OR REPLACE TYPE RESOLVE_STR IS TABLE OF VARCHAR2 (4000);
- CREATE OR REPLACE FUNCTION F_SPLIT
- (
- P_STR IN VARCHAR2,
- P_DELIMITER IN varchar2
- ) RETURN RESOLVE_STR PIPELINED
- AS
- j INT := 0;
- i INT := 1;
- len INT := 0;
- len1 INT := 0;
- tmp VARCHAR2 (4000);
- v_str VARCHAR2 (4000);
- BEGIN
- v_str := TRIM(BOTH P_DELIMITER FROM P_STR);--去掉前后的分隔符
- len := LENGTH (v_str);
- len1 := LENGTH(P_DELIMITER);
- WHILE j < len
- LOOP
- j := INSTR (v_str, P_DELIMITER, i);--寻分隔符位置
- IF j = 0 THEN--没有了
- tmp := SUBSTR (v_str, i);
- PIPE ROW (tmp);
- EXIT;--结束
- ELSE
- tmp := SUBSTR (v_str, i, j - i);
- i := j + len1;
- PIPE ROW (tmp);
- END IF;
- END LOOP;
- RETURN;
- END;
测试:
- SELECT * FROM TABLE (F_SPLIT('-12-02-ab-cd-a0-ef-', '-'));
例3:产生6个0..49的随机数
First the quick-and-dirty solution without a pipelined function
- select r from (
- select r from (
- select rownum r from all_objects where rownum < 50
- ) order by dbms_random.VALUE
- )where rownum <= 6;
pipelined function
- CREATE OR REPLACE TYPE ARRAY AS TABLE OF NUMBER;
- CREATE OR REPLACE FUNCTION gen_numbers(n IN NUMBER DEFAULT NULL)
- RETURN ARRAY PIPELINED
- AS
- BEGIN
- FOR i IN 1 .. nvl(n,9999)
- LOOP
- PIPE ROW(i);
- END LOOP;
- RETURN;
- END;
测试:
- select * from (
- select *
- from (select * from table(gen_numbers(49))) order by dbms_random.random
- )
- where rownum <= 6
generating that range of dates
- select to_date('2009-5-10','yyyy-mm-dd') + column_value-1 from TABLE(gen_numbers(15));
Note the name of the column we used: COLUMN_VALUE. That is the default name for the column coming back from the PIPELINED function.
another example
- CREATE OR REPLACE TYPE date_array AS TABLE OF DATE;
- CREATE OR REPLACE FUNCTION date_table(sdate DATE, edate DATE)
- RETURN date_array PIPELINED AS
- BEGIN
- FOR i IN 0 .. (edate - sdate)
- LOOP
- PIPE ROW(sdate + i);
- END LOOP;
- RETURN;
- END date_table;