Oracle函数中使用管道函数
我自己比较偏爱MySQL和MongoDB，但是公司一直都是使用Oracle，所以和Oracle打交道特别多，但是不得不说Oracle里面很多功能做的很不错，废话不多说，直接说下在Oracle的Function中使用PIPELINED，有些人叫它“管道流函数”，有些人叫它“管道表函数”这里我就叫它“管道函数”吧！
阅读目录：
函数介绍
在pl/sql开发中如果想创建重复使用的代码块是，可以考虑使用子程序。存储过程和函数都称为子程序。过程一般是不具有返回值的代码块，函数会有一个返回值。这里我们只说函数，一般我们创建完子程序之后它会储存在数据字典中“USER_OBJECTS”，以便重复使用。在你的sqldeveloper可以直接查询数据字典查看里面的Function:
select * from user_objects where object_type='FUNCTION';
上面是查看所有的函数包含自己创建的和系统的。函数的创建语法如下：
[CREATE [OR REPLACE]] FUNCTION function_name [(PARAMETER [, PARAMETER]...)] RETURN datatype [AUTHID { DEFINER | CURRENT_USER } ] [PRAGMA AUTONOMOUS_TRANSACTION;] [ local declarations] BEGIN executable statements [EXCEPTION execption handlers] END [function_name];
一般函数都会返回一个数据值，是一个。所以有时候想在函数里面返回数据集就要用其他方式！
管道函数介绍
这里我引用下园子里面其他人的一段话：
为了让 PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。Oracle 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效， 因为数据可以尽可能快地返回。
管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。
管道函数就是为了可以上函数返回不是一个结果，而是一个数据集(相当于表)。管道函数在使用的时候必须要有自定义类型的配合(类型就相当于表里的字段)。数据集里面的数据可以来自于一张表或者视图，也可以从很多表或者视图里面获取，所以管道函数可以做到把很多分散的数据集合到一起让你使用。因此对于开发人员来说掌握此函数是很重要的。但是此函数却有一点很让人无奈，那就是调试的时候很麻烦。但是也有些工具可以调试它只不过收费而已而且很高。
创建函数
这里我新建一个完整的函数做说明。
表结构：
CREATE TABLE "SYSTEM"."USERS" ( "ID" NUMBER NOT NULL ENABLE, "NAMES" VARCHAR2(20 BYTE) NOT NULL ENABLE, "EMAILS" VARCHAR2(80 BYTE) NOT NULL ENABLE, "AGE" NUMBER NOT NULL ENABLE );
数据：
ID NAMES EMAILS AGE 1 AAA aaa@live.cn 21 2 BBB bbb@live.cn 22 3 CCC ccc@live.cn 23 4 DDD ddd@live.cn 24 5 EEE eee@live.cn 25 6 FFF fff@live.cn 26 7 GGG ggg@live.cn 27 8 HHH hhh@live.cn 28
自定义类型：
create or replace type "TYPE_USERS_OPTION" as object ( seq number, --ID attr_name varchar2(300), --顯示的名稱 attr_mail varchar2(300), --顯示的郵件 attr_age number --顯示的年齡 ); create or replace TYPE TYPE_TABLE_OF_USERS_OPTION IS TABLE OF TYPE_USERS_OPTION;
函数：
create or replace FUNCTION fun_get_users_option RETURN TYPE_TABLE_OF_USERS_OPTION PIPELINED IS /*------------------------------------------------------------------------- * 程式類型： FUNCTION * 程式名稱： fun_get_users_option * 程式功能： 獲取用戶的詳細信息的函數 * 傳入參數： 無 * 使用方法： --查詢所有的用戶信息 select * from table(fun_get_users_option); * 返回值格式說明： SEQ: 用戶ID ATTR_NAME: 用戶名稱 ATTR_MAIL: 用戶郵箱 ATTR_AGE: 用戶年齡 * 前台如何使用返回值： 自由發揮，這個是一個測試Function * -----------------------建立與異動記錄------------------------------------ * 序次 修改日期 修改人員 記錄說明 * 001 2013-11-02 Dn9x 首次建立 *-------------------------------------------------------------------------*/ --初始化參數 out_rec TYPE_USERS_OPTION := TYPE_USERS_OPTION (0, null, null, null); --遊標：查詢所有的用戶信息 CURSOR c1 IS select id, names, emails, age from users where 1=1; BEGIN --循環遊標 FOR r1 IN c1 LOOP out_rec.seq := r1.id; out_rec.attr_name := r1.names; out_rec.attr_mail := r1.emails; out_rec.attr_age := r1.age; PIPE ROW (out_rec); END LOOP; --返回值，這裡必須只有一個return; RETURN; END;
使用函数
使用的时候就很方便了，就想查询表一样查询就行了：
select * from table(fun_get_users_option);
掌握这个并且熟练合理使用管道函数可以大大的提高工作质量。
