Oracle利用存储过程返回结果集代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】 Oracle利用存储过程返回结果集
作者:Dezai.CN / 发布于2013/11/21/ 757

	procedure pro_tj_getDate_normal_year(v_sDate varchar2,ds out ds_cur) as
	 d_date date;
	 d_eDate date;
	 n_diff number :=0; --年份差
	 n_item number :=0; --是否保存有指标
	 v_sql varchar2(10000);
	 v_sql_t varchar2(5000);
	 v_sql_where varchar2(5000);
	 v_itemCode varchar2(100);--指标类别
	 n_i number;
	 v_fields varchar2(1000);
	 v_year varchar(5);
	 begin
	 --计算年份差
	 select substr(v_eDate,1,4) - substr(v_sDate,1,4)
	 into n_diff
	 from dual;
	 --判断当前用户在所选公司是否保存指标
	 select count(*) into n_item from BP_J_TJZH_ITEM_LIST l
	 where l.org_code=v_complany and l.work_code=v_user;
	 if n_item>0 then
	 v_sql_where := ' and i.item_code in(
	 select l.item_code from BP_J_TJZH_ITEM_LIST l
	 where l.org_code='''|| v_complany || ''' and l.work_code=''' || v_user || ''')';
	 else
	 v_sql_where := '';
	 end if;
	 --判断指标类别是否为空
	 if v_type='' or v_type is null then
	 v_itemCode := v_complany || '-0-000001';
	 else
	 v_itemCode := v_type;
	 end if;
	
	 if n_diff<=0 then --以截止接月份为准
	 v_year := substr(v_eDate,1,4);
	 v_sql := 'select i.item_code_prefix,
	 lpad('' '',2 * level - 2) || i.item_name as item_name,
	 u.unit_name,
	 t.data_value "'||v_year||'" from
	 bp_c_stat_item i
	 left join bp_c_measure_unit u
	 on i.unit_id=u.unit_id
	 left join
	 (
	 select n.data_date,n.item_code_prefix,n.data_value,
	 row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
	 from bp_j_stat_ntz n
	 where n.org_code='''||v_complany||'''
	 and to_char(n.data_date,''yyyy-mm'') = '''||v_eDate||'''
	 ) t
	 on i.item_code_prefix=t.item_code_prefix and t.rn=1
	 where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where || '
	 start with i.item_code_prefix = '''||v_itemCode || '''
	 connect by prior i.item_code_prefix=i.parent_item_code
	 order SIBLINGS BY i.order_by asc';
	 else --时间段查询
	 d_date := to_date(v_sDate,'yyyy-mm');
	 d_eDate := to_date(v_eDate,'yyyy-mm');
	 n_i:=1;
	 v_fields := '';
	 v_sql := '';
	 while d_date<=d_eDate loop
	 v_year := to_char(d_date,'yyyy');
	 if EXTRACT(year FROM d_eDate) - EXTRACT(year FROM d_date)>0 then
	 v_sql := v_sql||'
	 left join (select t.item_code_prefix,t.data_value from (
	 select n.data_date,n.item_code_prefix,n.data_value,
	 row_number() over(partition by to_char(n.data_date,''yyyy''),n.item_code_prefix order by n.data_date desc) rn
	 from bp_j_stat_ntz n
	 where n.org_code='''||v_complany||'''
	 and to_char(n.data_date,''yyyy'') = '''||to_char(d_date,'yyyy') ||'''
	 ) t where t.rn=1) t'||n_i||'
	 on i.item_code_prefix=t'||n_i||'.item_code_prefix
	 ';
	 else
	 v_sql := v_sql||'
	 left join (select t.item_code_prefix,t.data_value from (
	 select n.data_date,n.item_code_prefix,n.data_value,
	 row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
	 from bp_j_stat_ntz n
	 where n.org_code='''||v_complany||'''
	 and to_char(n.data_date,''yyyy-mm'') = '''||to_char(d_date,'yyyy-mm') ||'''
	 ) t where t.rn=1) t'||n_i||'
	 on i.item_code_prefix=t'||n_i||'.item_code_prefix
	 ';
	 end if;
	 v_fields := v_fields || 't' || n_i || '.data_value as "'||v_year||'",';
	
	 n_i := n_i+1;
	 d_date :=add_months(d_date,12);
	 end loop;
	
	 v_sql_t := 'select i.item_code_prefix,
	 lpad('' '',2 * level - 2) || i.item_name as item_name,
	 u.unit_name,
	 '||v_fields||'
	 null
	 from bp_c_stat_item i
	 left join bp_c_measure_unit u
	 on i.unit_id=u.unit_id
	 ';
	
	 v_sql := v_sql_t || v_sql || '
	 where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where ||
	 'start with i.item_code_prefix = '''||v_itemCode || '''
	 connect by prior i.item_code_prefix=i.parent_item_code
	 order SIBLINGS BY i.order_by asc';
	 end if;
	
	 --打开游标
	 open ds for v_sql;
	
	 end pro_tj_getDate_normal_year;
试试其它关键字
　结果集　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)