oracle函数-动态结果输出

1、oracle函数创建

create or replace function fun_test()

begin		

end fun_test;

2、函数调用

select * from table(fun_test());

3、以结果集返回（仅自己研究的一种方法）

--创建 TYPE 类型 atrr_type

CREATE OR REPLACE TYPE atrr_type FORCE  AS OBJECT (
       orgIdObj varchar2(100),
       orgNameObj varchar2(100),
	   orgTypeObj VARCHAR2(1)
);

--将 TYPE 类型 atrr_type 定义为表， 用做接收返回值

CREATE OR REPLACE TYPE attr_table AS TABLE of atrr_type;

--以 Table 形式 返回结果集

create or replace function fun_test(orgId in VARCHAR2)
  return attr_table 
  is
    attr_row 	atrr_type;        	 		  -- 定义单条数据变量
    attr     	attr_table := attr_table();   -- 定义返回结果，并初始化
  begin
    for thisrow in (SELECT id as orgIdObj,org_name as orgNameObj,'0' as             
              orgTypeObj FROM sys_org START WITH id 
              ='88a9bed0f9d5bc882e9140feb8f3cfa1' CONNECT BY org_parent_id = PRIOR 
              id) 
    loop
      attr_row :=atrr_type(thisrow.orgIdObj,thisrow.orgNameObj,thisrow.orgTypeObj);
      attr.extend;
      attr(attr.count) := attr_row;--将每一行放到结果中	
    end loop;
  return(attr);--返回结果集
end fun_test;

4、调用函数：

select * from table(fun_test('88a9bed0f9d5bc882e9140feb8f3cfa1'));

5、遇到的问题，以及心得

（1）动态sql放到for thisrow in (sql)，遍历的过程需要用游标，否则不能取出值

        （1.1）定义一个动态游标

TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标  
usrs ref_cursor_type;  

        （1.2）遍历动态游标

open usrs for sqlSb;--拼接的动态sql,放到游标中进行循环
fetch usrs into orgIdObj,orgNameObj,orgTypeObj,isSub,isItem,isReportType,isCollect,isShowChild;
			while usrs%found
			loop 
				 attr_row := atrr_type(orgIdObj,orgNameObj,orgTypeObj,isSub,isItem,isReportType,isCollect,isShowChild);
				 attr.extend;
				 attr(attr.count) := attr_row;
				fetch usrs into  orgIdObj,orgNameObj,orgTypeObj,isSub,isItem,isReportType,isCollect,isShowChild;
			end loop;
			close usrs; 

           （1.3）完整代码块，仅供参考

--条件查询
create or replace function fun_selecttest(orgId in VARCHAR2,params in VARCHAR2)
return attr_table 
  is
    attr_row 	atrr_type;        	 		  -- 定义单条数据变量
    attr     	attr_table := attr_table();   -- 定义返回结果，并初始化
		param0    VARCHAR2(100);
		param1    VARCHAR2(100);
		param2    VARCHAR2(100);
		param3    VARCHAR2(100);
		param4    VARCHAR2(100);
		param5    VARCHAR2(100);
		sqlSb     VARCHAR2(2000);--sql 拼接条件
		TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标  
		usrs ref_cursor_type;  
		 orgIdObj varchar2(100);
		 orgNameObj varchar2(100);
		 orgTypeObj VARCHAR2(1);
		 isSub Char(1);
		 isItem Char(1);
		 isReportType Char(1);
		 isCollect VARCHAR2(3);
		 isShowChild Char(1);
  begin
			DBMS_OUTPUT.ENABLE(buffer_size => null);
			param0:=substr(params,1,1);--角色： 0股份，1集团，2工程公司，3分公司
			param1:=substr(params,2,1);--是否分包：0否：集团内分包，1是：集团内及集团外 ,(3组织没有)
			param2:=substr(params,3,1);--是否显示明细：0否，1是(3组织没有)
			param3:=substr(params,4,1);--报表类型：0境内，1境外，2全部 (3组织没有)
			param4:=substr(params,5,1);--汇总产业：0工程承包，1绿色环保，2投资运营，（3组织），4全部
			param5:=substr(params,6,1);--显示下级所有：0不显示，1显示
			dbms_output.put_line(param0||param1||param2||param3||param4||param5);
			--拼接动态sql
			sqlSb:='select * from table(fun_alltest('''||orgId||''','''||param5||''')) where 1=1';--	第二个单引号被作为转义符,第三个单引号被转义
			if param0='0'  then  
					if param5='1' then 
							if param2='1' then 
								sqlSb:=sqlSb||' and (isShowChild =1 or isShowChild =2)';--工程公司以及下属公司和项目
							else
								sqlSb:=sqlSb||' and isShowChild =1';--只查工程公司
							end if;
					end if;
					if param1='0'  then sqlSb:=sqlSb||' and (isSub =3 or isSub=1)';
					end if;
					if param1='1'  then sqlSb:=sqlSb||' and (isSub =3 or isSub=1 or isSub=0)';
					end if;
					if (param2='0' or param2='1') then sqlSb:=sqlSb||' and (isItem=3 or isItem='||param2||')';
					end if;
					if (param3='0' or param3='1') then sqlSb:=sqlSb||' and (isReportType =3 or isReportType='||param3||')';
					end if;
					if param3='2'  then sqlSb:=sqlSb||' and (isReportType =3 or isReportType=0 or isReportType=1)';
					end if;
					if param4='0'  then sqlSb:=sqlSb||' and (isCollect ='||'''3'''||' or isCollect='||'''001'''||')';
					end if;
					if param4='1'  then sqlSb:=sqlSb||' and (isCollect ='||'''3'''||' or isCollect='||'''002'''||')';
					end if;
					if param4='2'  then sqlSb:=sqlSb||' and (isCollect ='||'''3'''||' or isCollect='||'''003'''||')';
					end if;
			else
					if param1='0'  then sqlSb:=sqlSb||' and (isSub =3 or isSub=1)';
					end if;
					if param1='1'  then sqlSb:=sqlSb||' and (isSub =3 or isSub=1 or isSub=0)';
					end if;
					if (param2='0' or param2='1') then sqlSb:=sqlSb||' and (isItem=3 or isItem='||param2||')';
					end if;
					if (param3='0' or param3='1') then sqlSb:=sqlSb||' and (isReportType =3 or isReportType='||param3||')';
					end if;
					if param3='2'  then sqlSb:=sqlSb||' and (isReportType =3 or isReportType=0 or isReportType=1)';
					end if;
					if param4='0'  then sqlSb:=sqlSb||' and (isCollect ='||'''3'''||' or isCollect='||'''001'''||')';
					end if;
					if param4='1'  then sqlSb:=sqlSb||' and (isCollect ='||'''3'''||' or isCollect='||'''002'''||')';
					end if;
					if param4='2'  then sqlSb:=sqlSb||' and (isCollect ='||'''3'''||' or isCollect='||'''003'''||')';
					end if;
			end if;
			dbms_output.put_line('sql:'||sqlSb);
			open usrs for sqlSb;--拼接的动态sql,放到游标中进行循环
			fetch usrs into  orgIdObj,orgNameObj,orgTypeObj,isSub,isItem,isReportType,isCollect,isShowChild;
			while usrs%found
			loop 
				 attr_row := atrr_type(orgIdObj,orgNameObj,orgTypeObj,isSub,isItem,isReportType,isCollect,isShowChild);
				 attr.extend;
				 attr(attr.count) := attr_row;
				fetch usrs into  orgIdObj,orgNameObj,orgTypeObj,isSub,isItem,isReportType,isCollect,isShowChild;
			end loop;
			close usrs; 
  return(attr);
end fun_selecttest;