orcale管道函数

1、管道函数即是可以返回行集合（可以使嵌套表nested table 或数组 varray）的函数，我们可以像查询物理表一样查询它或者将其

赋值给集合变量。

2、管道函数为并行执行，在普通的函数中使用dbms_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端

实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数(pipeline function)。

3、关键字PIPELINED表明这是一个Oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元 素，函数以一个空的RETURN 语句结束，以表明它已经完成。

4、由于管道函数的并发多管道流式设计以及实时返回查询结果而去除了中间环节因此可以带来可观的性能提升。

主体函数：

CREATE OR REPLACE FUNCTION fc_get_client_list RETURN tab_client PIPELINED ------------------------------------------------------------------------------ ---功能描述：客户列表 ---时间： 2017-08-15 ---作者： ---版本： 1.0 beta ------------------------------------------------------------------------------ IS v_client obj_client; BEGIN --循环主表主表，根据主表client_id聚合其他信息 FOR cur IN (SELECT a.client_id, a.client_type, a.client_name, a.client_source, a.create_user, a.create_date, a.update_user, a.update_date, a.notes, a.is_valid, b.client_level, d.company_name, d.company_type, d.company_size FROM tb_client a, tb_client_level b, tb_client_company d WHERE a.client_id = b.client_id(+) AND a.client_id = d.client_id(+)) LOOP v_client := obj_client(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); --获取客户ID v_client.client_id := cur.client_id; --获取客户名称 v_client.client_name := cur.client_name; --获取客户摊位号 BEGIN SELECT DISTINCT listagg(a.firm_id, ',') within GROUP(ORDER BY a.firm_id) over(PARTITION BY client_id) INTO v_client.firm_id FROM tb_client_firm_rel a WHERE a.client_id = cur.client_id; EXCEPTION WHEN no_data_found THEN v_client.firm_id := NULL; END; --聚合客户负责人id、负责人 BEGIN SELECT b.team_id, b.user_id, b.user_name INTO v_client.team_id, v_client.user_id, v_client.user_name FROM tb_team_client_rel a, tb_team b WHERE a.client_id = cur.client_id AND a.team_id = b.team_id AND b.team_role = 'A' AND b.is_valid = 'Y'; EXCEPTION WHEN no_data_found THEN v_client.user_id := NULL; v_client.user_name := NULL; v_client.team_id := NULL; END; --获取公司名称 v_client.company_name := cur.company_name; --获取公司规模 v_client.company_size := cur.company_size; --聚合联系人(多个联系人时只显示 我负责的联系人中的第一条) BEGIN SELECT contact_name INTO v_client.contact_name FROM (SELECT b.contact_name FROM tb_client_contact_rel a, tb_contact b WHERE a.client_id = cur.client_id AND a.contact_id = b.contact_id AND b.is_valid = 'Y' ORDER BY a.create_date DESC) WHERE rownum = 1; EXCEPTION WHEN no_data_found THEN v_client.contact_name := NULL; END; --聚合通讯号码 BEGIN SELECT "'A'" mobile, "'B'" phone INTO v_client.mobile, v_client.phone FROM (SELECT DISTINCT contact_id, listagg(phone_no, ',') within GROUP(ORDER BY phone_no) over(PARTITION BY phone_type) phone_no, phone_type FROM (SELECT t.contact_id, phone_no, phone_type FROM tb_contact_phone t, tb_client_contact_rel t1 WHERE t1.contact_id = t.contact_id AND t1.client_id = cur.client_id ORDER BY t1.create_date DESC) WHERE rownum = 1) pivot(MAX(phone_no) FOR phone_type IN('A', 'B')); EXCEPTION WHEN no_data_found THEN v_client.mobile := NULL; v_client.phone := NULL; END; --获取客户最近跟进人/最后跟进时间 BEGIN SELECT create_user, create_date INTO v_client.last_follow_user, v_client.last_follow_date FROM (SELECT a.create_user, a.create_date FROM tb_sale_record a, tb_sale_record_client_rel b WHERE a.sale_record_id = b.sale_record_id AND b.client_id = cur.client_id AND a.is_valid = 'Y' ORDER BY a.create_date DESC) WHERE rownum = 1; EXCEPTION WHEN no_data_found THEN v_client.last_follow_user := NULL; v_client.last_follow_date := NULL; END; --获取客户类型 v_client.client_type := cur.client_type; --获取客户来源 v_client.client_source := cur.client_source; --获取客户级别 v_client.client_level := cur.client_level; --获取客户类型 v_client.company_type := cur.company_type; --获取客户地址 BEGIN SELECT a.address, a.province, a.city, a.county INTO v_client.address, v_client.province, v_client.city, v_client.county FROM tb_client_address a WHERE a.is_default = 'Y' AND a.client_id = cur.client_id; EXCEPTION WHEN no_data_found THEN v_client.address := NULL; v_client.province := NULL; v_client.city := NULL; v_client.county := NULL; END; --所属于公海 --获取客户分配人 --获取客户分配时间 --获取客户创建人 v_client.create_user := cur.create_user; --获取客户创建时间 v_client.create_date := cur.create_date; --获取客户修改人 v_client.update_user := cur.update_user; --获取客户修改时间 v_client.update_date := cur.update_date; --获取客户备注 v_client.notes := cur.notes; --获取客户是否有效 v_client.is_valid := cur.is_valid; --管道函数输出数据 PIPE ROW(v_client); END LOOP; RETURN; EXCEPTION WHEN no_data_needed THEN RETURN; WHEN OTHERS THEN raise_application_error(-20001, '系统异常，请联系管理员！' || SQLERRM); END fc_get_client_list;

tab_client:

CREATE OR REPLACE TYPE tab_client AS TABLE OF obj_client

obj_client

CREATE OR REPLACE TYPE obj_client AS OBJECT ( client_id VARCHAR2(50), --客户id client_name VARCHAR2(50), --客户名称 is_valid VARCHAR2(1), --是否有效 firm_id VARCHAR2(50), --交易号 team_id VARCHAR2(32), --团队id user_id VARCHAR2(32), --负责人id user_name VARCHAR2(50), --负责人 client_status VARCHAR2(50), --状态 company_name VARCHAR2(50), --公司名称 company_size VARCHAR2(1), --公司规模 contact_name VARCHAR2(50), --联系人 mobile VARCHAR2(50), --手机 phone VARCHAR2(50), --电话 last_follow_user VARCHAR2(50), --最近跟进人 last_follow_date DATE, --最后跟进时间 client_type VARCHAR2(50), --客户类型 client_source VARCHAR2(50), --客户来源 client_level VARCHAR2(50), --客户级别 company_type VARCHAR2(50), --公司类型 address VARCHAR2(256), --地址 province VARCHAR2(50), city VARCHAR2(50), county VARCHAR2(50), assign_user VARCHAR2(32), --分配人 assign_date DATE, --分配时间 high_seas VARCHAR2(50), --所属公海 create_user VARCHAR2(32), --创建人 create_date DATE, --创建时间 update_user VARCHAR2(32), --修改人 update_date DATE, --最后修改时间 notes VARCHAR2(256) --备注 )

sql里的查询：

select * from table(fc_get_client_list)