手上有段前任写的输出总账账本目录的部分脚本，如下，如何调用这段脚本，实现输出呢？
--1、创建包sp_slzb,结果输出到result_bb
/*创建包头*/
create or replace package sp_slzb is
type outlist is ref cursor;
procedure sp_sl01
(set_year in varchar2,
st_code in varchar2,
ele_level in varchar2, --要素使用'#'连接，后面使用()说明级次，如#en(1-3)#bs(1-*)#hold11(2-2)#
per_print number, --每页打印行数
result_bb out outlist
);
end sp_slzb;
/*创建包体*/
create or replace package body sp_slzb is
procedure sp_sl01( set_year in varchar2,
st_code in varchar2,
ele_level in varchar2, --要素使用'#'连接，后面使用()说明级次，*代表底级（PS：只要底级写*-*），如#en(1-3)#bs(1-*)#hold11(2-2)#
per_print number, --每页打印行数
result_bb out outlist
) is
v_sql varchar2(4000);
v_sql0 long;
v_sql1 long;
v_sql2 long;
v_sql3 varchar2(4000);
v_sql4 varchar2(4000);
v_sql5 varchar2(4000);
v_sql6 varchar2(4000);
v_sql7 varchar2(4000);
v_sql8 varchar2(4000);
v_sql9 varchar2(4000);
cs number;
cs1 number;
sum_jcws number;
yscs number;
ele_1 varchar2(100);
ele_2 varchar2(100);
min_lev varchar2(10);
max_lev varchar2(10);
lev1 number;
ele_bb varchar2(40);
jl varchar2(3);
ml_sql0 varchar2(4000);
ml_sql1 varchar2(4000);
ml_sql2 varchar2(4000);
ml_sql3 varchar2(4000);
ml_sql4 long;
ml_sql5 long;
ml_sql6 long;
ml_sql7 long;
ml_sql8 long;
ml_sql9 long;
begin
/*首先，拼接出完整的三栏明细账（PS：只打底级）插入物理表SLNR01*/
--判断写入参数中的要素个数
select length(ele_level)-length(replace(ele_level, '#', ''))
into cs
from dual;
commit;
--循环截取单个要素并开始拼接
cs1 := 1;
loop
select substr(ele_level,
instr(ele_level, '#', 1, cs1) + 1,
instr(ele_level, '#', 1, cs1 + 1) -
instr(ele_level, '#', 1, cs1) - 1)
into ele_1 from dual;
select substr(ele_1,1,instr(ele_1,'(',1)-1) into ele_2 from dual;
--获取单个要素的数据源表
select distinct t.ele_source into ele_bb from sys_element t
where t.ele_code=ele_2
and t.set_year=set_year;
--截取出单个要素的最低级次
select substr(ele_1,instr(ele_1,'(',1)+1,instr(ele_1,'-',1)-instr(ele_1,'(',1)-1) into min_lev from dual;
if min_lev='*'
then
v_sql:='select max(t3.level_num)
from gl_voucher_detail t1,gl_voucher t2,'||ele_bb||' t3
where exists (select 1 from ele_book_set p where p.chr_id=t2.st_id and p.chr_code='''||st_code||''' and t2.set_year='||set_year||')
and t1.voucher_id=t2.voucher_id
and t3.chr_id='||ele_2||'_id';
execute immediate v_sql into min_lev;
if min_lev is null then min_lev:=1;
else min_lev:=min_lev;
end if;
else min_lev:=min_lev;
end if;
--截取出单个要素的最高级次
select substr(ele_1,instr(ele_1,'-',1)+1,instr(ele_1,')',1)-instr(ele_1,'-',1)-1) into max_lev from dual;
if max_lev='*'
then
v_sql:='select max(t3.level_num)
from gl_voucher_detail t1,gl_voucher t2,'||ele_bb||' t3
where exists (select 1 from ele_book_set p where p.chr_id=t2.st_id and p.chr_code='''||st_code||''' and t2.set_year='||set_year||')
and t1.voucher_id=t2.voucher_id
and t3.chr_id='||ele_2||'_id';
execute immediate v_sql into max_lev;
if max_lev is null then max_lev:=1;
else max_lev:=max_lev;
end if;
else max_lev:=max_lev;
end if;
sum_jcws:=0;
--根据获取到的最低和最高要素级次loop循环处理拼接
lev1:=min_lev;
loop
v_sql0:=v_sql0||'(select p1.chr_code from '||ele_bb||' p,'||ele_bb||' p1 where t1.'||ele_2||'_id=p.chr_id and p.set_year=p1.set_year
and p1.chr_id=p.chr_id'||lev1||' and p1.level_num='||lev1||' ) as '
||ele_2||'_code'||lev1||',(select p1.chr_name from '||ele_bb||' p,'||ele_bb||' p1 where t1.'||ele_2||'_id=p.chr_id and p.set_year=p1.set_year
and p1.chr_id=p.chr_id'||lev1||' and p1.level_num='||lev1||' ) as '
||ele_2||'_name'||lev1||',';
v_sql3:=v_sql3||ele_2||'_code'||lev1||','||ele_2||'_name'||lev1||',';
v_sql:='select max(length(chr_code)) from '||ele_bb||' where set_year='||set_year||'
and level_num='||lev1;
execute immediate v_sql into sum_jcws;
ml_sql0:=ml_sql0||'nvl('||ele_2||'_code'||lev1||',lpad('' '','''||to_number(sum_jcws+1)||''',''*'')) as '||ele_2||'_code'||lev1||','||ele_2||'_name'||lev1||',';
ml_sql8:=ml_sql8||ele_2||'_code'||lev1||',';
yscs:=length(ml_sql8)-length(replace(ml_sql8, ',', ''));
yscs:=yscs+1;
ml_sql4:=ml_sql4||yscs||',lpad('' '','||yscs||')||'
||ele_2||'_code'||lev1||',';
ml_sql5:=ml_sql5||'grouping('||ele_2||'_code'||lev1||')+';
ml_sql6:=ml_sql6||'nvl('||ele_2||'_code'||lev1||',''*'') as '||ele_2||'_code'||lev1||',';
ml_sql7:=ml_sql7||ele_2||'_code'||lev1||'||'' ''||'||ele_2||'_name'||lev1||' as '||ele_2||'_code'||lev1||',';
lev1:=lev1+1;
exit when lev1>max_lev;
end loop;
cs1 := cs1 + 1;
exit when cs1=cs;
end loop;
--生成本年发生数明细
v_sql1:=' select as_code,as_name,'||v_sql0||
'substr(t.vou_date,6,2) as yf,
substr(t.vou_date,9,2) as rq,
to_char(t.voucher_no) as voucher_no,
t1.main_line,
t1.summary,
deb_money as jfje,
cre_money as dfje,
deb_money-cre_money as fsje,
''1'' as bz
from gl_voucher t, gl_voucher_detail t1
where t.set_year = '||set_year||'
and t.voucher_id = t1.voucher_id
and exists (select 1
from ele_book_set p
where p.chr_id = t.st_id
and p.chr_code = '''||st_code||''')
union all';
--叠加期初数明细行
v_sql2:='(select as_code,
as_name,
'||v_sql0||
'''0'' as yf,
''0'' as rq,
''0'' as voucher_no,
''0'' as main_line,
''期初余额'' as summary,
0 as jfje,
0 as dfje,
decode(substr(as_code,1,1),2,-nvl(balance,0),nvl(balance,0)) as fsje,
''0'' as bz
from gl_remain t1
where t1.set_year = '||set_year||
' and exists (select 1
from ele_book_set p
where p.chr_id = t1.st_id
and p.chr_code = '''||st_code||''')';
--明细行开头一段
v_sql4:='with bb01 as (select AS_CODE,AS_NAME,'||v_sql3
||'bz,YF,RQ,VOUCHER_NO,main_line,SUMMARY,sum(jfje) as jfje,
sum(dfje) as dfje,sum(fsje) as fsje from (';
--明细行后续一段
v_sql5:='))
group by AS_CODE,
AS_NAME,'||v_sql3||'YF,
RQ,
VOUCHER_NO,
main_line,
SUMMARY,
bz),';
--汇总生成每月合计行
v_sql6:=' bb02 as
(select AS_CODE,
as_name,'||v_sql3||'''2'' as bz,
yf,(case when yf in (''01'',''03'',''05'',''07'',''08'',''10'',''12'') then ''31''
when yf in ''02'' then ''28'' else ''30'' end) as rq,
'''' AS voucher_no,
'''' as main_line,
''本月合计'' as summary,sum(jfje) as jfje,sum(dfje) as dfje,sum(fsje) as fsje
from bb01 where bz=''1''
group by
AS_CODE,
as_name,'||v_sql3||'yf),';
v_sql7:=' bb03 as
(select AS_CODE,
as_name,'||v_sql3||'''1'' as bz,yf,rq,voucher_no,
summary,
main_Line,
jfje,
dfje,
sum(fsje) over(partition by
AS_CODE,AS_NAME,'||v_sql3||'''1'' order by yf,bz,rq,voucher_No,main_Line,summary) as fsje
from bb01),';
--汇总生成每月全年累计行
v_sql8:=' bb04 as
(select
AS_CODE,AS_NAME,'||v_sql3||'''4'' as bz,yf,rq,voucher_no,main_line,summary,jfje,dfje,fsje
from
(select
AS_CODE,AS_NAME,'||v_sql3||'bz,
yf,rq,
voucher_no,
main_line,
''本年累计'' as summary,
sum(jfje)
over(partition by AS_CODE,AS_NAME,'||v_sql3||'''1'' order by yf) as jfje,
sum(dfje)
over(partition by AS_CODE,AS_NAME,'||v_sql3||'''1'' order by yf) as dfje,
sum(fsje)
over(partition by AS_CODE,AS_NAME,'||v_sql3||'''1'' order by yf) as fsje
from
(select * from bb01 where bz=''0''
union all
select * from bb02)) where bz<>''0'')';
--拼接组合年初数，发生数明细，月合计行，月累计行
v_sql9:=' select AS_CODE,AS_NAME,'||v_sql3
||'BZ,YF,RQ,VOUCHER_NO,SUMMARY,MAIN_LINE,JFJE,DFJE,FSJE
from
(select * from bb03
union all
select * from bb02
union all
select * from bb04) order by
AS_CODE,AS_NAME,'||v_sql3||'yf,bz,rq,voucher_no,main_line,summary';
--将生成的三栏明细账插入物理表slnr01（如果已存在，则直接删除表，否则重新创建）
select count(1) into jl from user_tables t where t.table_name='SLNR01';
if jl=0 then
execute immediate 'create table slnr01 as '||v_sql4||v_sql1||v_sql2||v_sql5||v_sql6||v_sql7||v_sql8||v_sql9;
commit;
else
execute immediate 'drop table slnr01';
execute immediate 'create table slnr01 as '||v_sql4||v_sql1||v_sql2||v_sql5||v_sql6||v_sql7||v_sql8||v_sql9;
end if;
/********以下开始生成目录,并将生成的目录插入物理表SLML01********/
--计算叶节点的页数（PS：补空页）
ml_sql1:='with bb01 as
(select
AS_CODE,AS_NAME,'||ml_sql0||
'(case when mod(ceil(count(1)/'||per_print||'),2)=1
then ceil(count(1)/38)+1
else ceil(count(1)/38) end) as hs
from slnr01
group by AS_CODE,AS_NAME,'||v_sql3||'''1''),';
--计算叶节点对应的累计页数
ml_sql2:=' bb02 as
(select
dense_rank() over(order by AS_CODE,AS_NAME,'||v_sql3||'''1'') as bh,
AS_CODE,AS_NAME,'||v_sql3||'sum(hs) over(order by AS_CODE,AS_NAME,'||v_sql3||'''1'') as hs
from bb01),';
--错位相拼计算出子节点起始页码
ml_sql3:=' bb03 as
(select
t1.AS_CODE,t1.AS_NAME,'||v_sql3||'1 as ym
from bb02 t1
where t1.bh=''1''
union all
select
t1.AS_CODE,t1.AS_NAME,'||v_sql3||'t2.hs+1 as ym
from bb02 t1,(select bh,hs from bb02) t2 where t1.bh=t2.bh+1) ';
--逐级计算显示起始页码
ml_sql9:=' select * from
(select decode(jc1,1,as_code,'||ml_sql4||'''#'') as km,ym
from
(select dense_rank() over(partition by as_code order by jc desc) as jc1,p.*
from (
select grouping(as_code)+'||ml_sql5||'+0 as jc,nvl(as_code, ''#'') as as_code,'
||ml_sql6||'min(ym) as ym from (select as_code||'' ''||as_name as as_code,'
||ml_sql7||'ym
from bb03
) t
group by rollup(as_code,'||ml_sql8||'1)
) p
order by as_code,'||ml_sql8||'1) )
where km not like ''#%'' and km not like ''%*%'' and trim(km) is not null';
/*dbms_output.put_line(ml_sql1);
dbms_output.put_line(ml_sql2);
dbms_output.put_line(ml_sql3);
dbms_output.put_line(ml_sql9);*/
open result_bb for ml_sql1||ml_sql2||ml_sql3||ml_sql9;
end;
end sp_slzb;
--2、打印内容输出到slnr01物理表中，目录输出到游标参数result_bb中
