Oracle用存储过程实现动态报表
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;
--判断当前用户在所选公司是否保存指标
selectcount(*) 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 isnullthen
v_itemCode := v_complany || '-0-000001';
else
v_itemCode := v_type;
end if;
if n_diff
页:
[1]