管道函数(pipelined function)简单使用示例


管道函数(pipelined function)简单使用示例
 
管道函数是一种比较特殊的函数,其返回值为集合类型.
 
在PL/SQL中,管道函数和表函数、游标一起联合使用能实现一些比较复杂的功能,当和并行处理一起使用时,还能较大的改善性能.
 
使用示例1：
 
实现简单的字符串分割,函数参数为常见的VARCHAR2类型.
 
--先创建集合类型,供管道函数返回使用
 
[sql] 
23:38:14 SCOTT@orcl> CREATE OR REPLACE TYPE EMP_element as table of varchar2(100);  
23:54:04   2  /  
  
Type created.  
  
Elapsed: 00:00:00.11  
 
 建立管道函数
 
[sql] 
create or replace function pipe_test(p_empno varchar2,  
                                     p       varchar2 default ',')  
  return EMP_element  
  pipelined is  
  v_element varchar2(1000) := p_empno;  
begin  
  pipe row(substr(p_empno, 0, instr(p_empno, p) - 1));  
  loop  
    v_element := substr(v_element,  
                        instr(v_element, p) + 1,  
                        length(v_element) - instr(v_element, p) + 1);  
    exit when instr(v_element, p) = 0;  
    pipe row(substr(v_element, 0, instr(v_element, p) - 1));  
  end loop;  
  pipe row(v_element);  
  return;  
end pipe_test;  
 
注意pipelined及pipe row等关键字.
 
pipelined表明该函数为管道函数,pipe row用来立即返回单个元素.
 
使用如下:
 
[sql] 
01:06:16 SCOTT@orcl>  select pipe_test(empno||','||ename) a from emp;  
  
A  
------------------------------  
EMP_ELEMENT('7369', 'SMITH')  
EMP_ELEMENT('7499', 'ALLEN')  
EMP_ELEMENT('7521', 'WARD')  
EMP_ELEMENT('7566', 'JONES')  
EMP_ELEMENT('7654', 'MARTIN')  
EMP_ELEMENT('7698', 'BLAKE')  
EMP_ELEMENT('7782', 'CLARK')  
EMP_ELEMENT('7788', 'SCOTT')  
EMP_ELEMENT('7839', 'KING')  
EMP_ELEMENT('7844', 'TURNER')  
EMP_ELEMENT('7876', 'ADAMS')  
EMP_ELEMENT('7900', 'JAMES')  
EMP_ELEMENT('7902', 'FORD')  
EMP_ELEMENT('7934', 'MILLER')  
  
14 rows selected.  
  
Elapsed: 00:00:00.04  
 
即每一行为一个集合变量.
 
 也可以通过表函数查询单个集合元素的值:(这里可看作一种单行转单列的应用)
 
[sql] 
01:07:28 SCOTT@orcl>  select * from table( select pipe_test(empno||','||ename) A  
 from emp where empno=7788);  
  
COLUMN_VALUE  
--------------------------------------------------  
7788  
SCOTT  
  
2 rows selected.  
  
Elapsed: 00:00:00.01  
 
 
 
使用示例2：
 
使用集合类型做函数传入参数类型.
 
建立函数如下:
 
[sql] 
create or replace function pipe_test1(c_empno EMP_element,  
                                      p       varchar2 default ',')  
  return EMP_element  
  pipelined is  
  v_element varchar2(1000);  
begin  
  for i in 1 .. c_empno.count loop  
    v_element := c_empno(i);  
    pipe row(substr(v_element, 0, instr(v_element, p) - 1));  
    loop  
      v_element := substr(v_element,  
                          instr(v_element, p) + 1,  
                          length(v_element) - instr(v_element, p) + 1);  
      exit when instr(v_element, p) = 0;  
      pipe row(substr(v_element, 0, instr(v_element, p) - 1));  
    end loop;  
    pipe row(v_element);  
  end loop;  
  return;  
end pipe_test1;  
 
注意嵌套表类型的使用.
 
使用与上面的基本类似:
 
[sql] 
01:13:16 SCOTT@orcl> col a format a50  
01:13:22 SCOTT@orcl> select pipe_test1(EMP_element(ename||','||'ename',empno||',  
'||'empno')) a from emp;  
  
A  
--------------------------------------------------  
EMP_ELEMENT('SMITH', 'ename', '7369', 'empno')  
EMP_ELEMENT('ALLEN', 'ename', '7499', 'empno')  
EMP_ELEMENT('WARD', 'ename', '7521', 'empno')  
EMP_ELEMENT('JONES', 'ename', '7566', 'empno')  
EMP_ELEMENT('MARTIN', 'ename', '7654', 'empno')  
EMP_ELEMENT('BLAKE', 'ename', '7698', 'empno')  
EMP_ELEMENT('CLARK', 'ename', '7782', 'empno')  
EMP_ELEMENT('SCOTT', 'ename', '7788', 'empno')  
EMP_ELEMENT('KING', 'ename', '7839', 'empno')  
EMP_ELEMENT('TURNER', 'ename', '7844', 'empno')  
EMP_ELEMENT('ADAMS', 'ename', '7876', 'empno')  
EMP_ELEMENT('JAMES', 'ename', '7900', 'empno')  
EMP_ELEMENT('FORD', 'ename', '7902', 'empno')  
EMP_ELEMENT('MILLER', 'ename', '7934', 'empno')  
  
14 rows selected.  
  
Elapsed: 00:00:00.01  
 
 
 
[sql] 
01:14:02 SCOTT@orcl> select * from table(select pipe_test1(EMP_element(ename||',  
'||'ename',empno||','||'empno')) from emp