|
在Oracle中执行以下语句创建存储过程 根据归档份数判断，如果只有一份则为正本，如果有多份 第一份为正本其余都显示为副本
CREATE OR REPLACE PROCEDURE "PRINTJIBEIXUHAO6CM_VOL10" (dids in varchar2, cur out sys_refcursor )
AS
v_ename varchar2(200);
usercodevar varchar2(200);
ls_qzh varchar2(200);
ls_flh varchar2(200);
ls_ajh varchar2(200);
ls_bgqx varchar2(200);
ls_title varchar2(800);
ls_xmh varchar2(64);
ls_jzh varchar2(4);
llDID number;
ll_duenum number;
ll_gdfs number;
v_duenumname varchar2(200);
v_tablename varchar2(20);
p_sql Varchar2(4000);
d_volxh number;
type rc_type is ref cursor;
mycur rc_type;
BEGIN
d_volxh:=1;
p_sql:='delete from TEMP_VOLPRINT_10_6_BH';
Execute Immediate p_sql;
p_sql:='SELECT BGQX,QZH,FLH,AJH,TITLE,GDFS,F1 FROM D_VOL10 WHERE status=0 and F6=''6CM'' and DID in ( '||dids||')';
open mycur for p_sql ;
fetch mycur into ls_bgqx,ls_qzh,ls_flh,ls_ajh, ls_title,ll_gdfs,ls_xmh;
while (mycur%found)
loop
dbms_output.put_line('0');
if (ll_gdfs>=1) then
p_sql:='insert into TEMP_VOLPRINT_10_6_BH(volbgqx,volqzh,volflh,volxmh ,volajh,voltitle ,volzfb) values(:1,:2,:3,:4,:5,:6,:7)';
dbms_output.put_line(p_sql);
Execute Immediate p_sql using ls_bgqx,ls_qzh,ls_flh,ls_xmh,ls_ajh,ls_title,'正本';
end if ;
while (ll_gdfs>1)
loop
p_sql:='insert into TEMP_VOLPRINT_10_6_BH(volbgqx,volqzh,volflh,volxmh ,volajh,voltitle ,volzfb) values(:1,:2,:3,:4,:5,:6,:7)';
dbms_output.put_line(p_sql);
Execute Immediate p_sql using ls_bgqx,ls_qzh,ls_flh,ls_xmh,ls_ajh,ls_title,'副本';
d_volxh:=d_volxh+1;
commit;
ll_gdfs:=ll_gdfs-1;
end loop;
fetch mycur into ls_bgqx,ls_qzh,ls_flh,ls_ajh, ls_title,ll_gdfs,ls_xmh;
end loop;
close mycur;
dbms_output.put_line('3');
open cur for 'select volbgqx as 案卷_保管期限,volqzh as 案卷_全宗号,volxmh as 案卷_项目代号,volflh 案卷_分类号,volajh as 案卷_案卷号,voltitle as 案卷_题名,volzfb as 案卷_备注 from TEMP_VOLPRINT_10_6_BH';
end;
---------------------
在数据库中创建完成后在报表中调用
D_VOL21JIBEI6--param--dids
|
