• 1阅读
  • 0回复

报表区分正副本-存储过程实现-实战

在线 zfc2020
级别: 新手上路
楼主   发表于: 2021-08-16 , 来自: From:Unknown


在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
发帖 回复
« 返回列表
描述
快速回复
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容