oracle 之 包，包体创建和使用案例

先创建包，再创建包体
---------------创建包体---------------------
create or replace package body pkg_yygl_service
Is
PROCEDURE p_service(as_date In Varchar2)
As

Begin
  p_zgda(as_date);
  p_rydd(as_date);
  p_rylz(as_date);
End p_service;

Procedure p_job
As
ls_date Varchar2(10);
ls_newxh Varchar2(10);
Begin
     Select to_char(Sysdate(),'yyyymmdd') Into ls_date From dual;
    p_getxhb('RSGL_ZDFWZXJL',ls_newxh);
    insert into RSGL_ZDFWZXJL(XH,lb,RQ,ZXsj) values(ls_newxh,'自动服务',ls_date,sysdate);
     p_service(ls_date);

End p_job;
procedure p_getxhb(as_tablename In Varchar2, xh Out Varchar2)
As
li_dqz Int;
li_zz Int;
Begin
     Select dqz,decode(zz,Null,1,1)  Into li_dqz,li_zz From xt_xhb Where bm=as_tablename;
     li_dqz := li_dqz +li_zz;
     Update xt_xhb Set dqz = li_dqz Where bm=as_tablename;
     xh := to_char(li_dqz);
     exception
     when no_data_found then
     Insert Into xt_xhb Values(as_tablename,1,1);
     xh :=  '1';
End p_getxhb;



--初始化科室代码
Procedure p_init_ksdm(as_rootid In Varchar2,as_ksdm In Varchar2,as_last In Varchar2)
As
ls_ksdm Varchar2(100);
ls_jb Numeric(1);
ls_sjid Varchar2(100);
ls_tempdm Varchar2(20);
ls_mjpb Numeric(1);
ls_ksid Varchar2(100);
CURSOR c1 (p1 Varchar2) Is
Select ksid From rsgl_ksdm Where sjid=p1;
Begin
   Select nvl(mjpb,1),ksdm Into ls_mjpb, ls_ksdm From rsgl_ksdm Where ksid=as_rootid;
   If(as_ksdm Is Null) Then
   Select length('00'||to_char(to_number(as_last)+1))-1 Into ls_jb From dual;
   Select '00'||to_char(to_number(as_last)+1) Into ls_sjid From dual;
   Update rsgl_ksdm Set ksdm=substr(ls_sjid,ls_jb,2) Where ksid=as_rootid;
   Else
    Update rsgl_ksdm Set ksdm=nvl(as_ksdm,'')||substr('00'||to_char(to_number(as_last)+1),length('00'||to_char(to_number(as_last)+1))-1,2) Where ksid=as_rootid;
   End If;
   If ls_mjpb=0 Then
        ls_tempdm:='00';
        Open c1(as_rootid);
           Loop
           FETCH c1 Into ls_ksid;
           IF c1 %NOTFOUND THEN EXIT; END IF;
           p_init_ksdm(ls_ksid,ls_ksdm,ls_tempdm);
           ls_tempdm:=substr('00'||to_char(to_number(ls_tempdm)+1),length('00'||to_char(to_number(ls_tempdm)+1))-1,2);
           END LOOP;
        CLOSE c1;
    End If ;
End p_init_ksdm;

--处理职工档案
Procedure p_zgda(as_dt In Varchar2)
As
Cursor c1 (p1 Varchar2) Is
Select xh,zgid From rsgl_ygjcjl where to_char(rq,'YYYYMMDD')=p1 And lb=0 and zt=0;
ls_zgid Varchar2(10);
ls_xh   Varchar2(10);
Begin
     Open c1(as_dt);
      LOOP
        FETCH c1 Into ls_xh,ls_zgid;
             IF c1 %NOTFOUND THEN EXIT; END IF;
             Update rsgl_zgda Set sypb = 0 ,zxpb=1  Where zgid = ls_zgid;
             Update rsgl_ygjcjl Set zt = 1  Where xh = ls_xh;
        END LOOP;
      CLOSE c1;
End p_zgda;

--处理人员调动
Procedure p_rydd(as_dt In Varchar2)
As
CURSOR c1 (p1 Varchar2) IS
Select xh,zgid,zrks,zcks,zrylz,zcylz from rsgl_zgdd where to_char(bdrq,'YYYYMMDD')=p1 And dqzt=0;
ls_zgid Varchar2(10);
ls_zrks Varchar2(10);
ls_zcks Varchar2(10);
ls_zrylz Varchar2(10);
ls_zcylz Varchar2(10);
ls_xh   Varchar2(10);
ls_newxh   Varchar2(10);
Begin
     Open c1(as_dt);
      LOOP
        FETCH c1 Into ls_xh,ls_zgid,ls_zrks,ls_zcks,ls_zrylz,ls_zcylz;
             IF c1 %NOTFOUND THEN EXIT; END IF;

             if ls_zrks is not null then
                Update rsgl_zgda Set szks = ls_zrks Where zgid = ls_zgid; --所在科室
             end if;

             if ls_zrylz is not null then
                Update rsgl_zgda Set ylzid = ls_zrylz Where zgid = ls_zgid;--考勤组
             end if;

             Update rsgl_zgdd Set dqzt = 1  Where xh = ls_xh;
             p_zgdals(ls_zgid);

          p_getxhb('RSGL_ZDFWZXJL',ls_newxh);
          insert into RSGL_ZDFWZXJL(xh,lb,Zgid,RQ,ZXsj) values(ls_newxh,'自动服务人员调动',ls_zgid,as_dt,sysdate);
        END LOOP;

      CLOSE c1;
End p_rydd;

--处理人员轮转
Procedure p_rylz(as_dt In Varchar2)
As
CURSOR c1 (p1 Varchar2) IS
Select b.xh,b.zgid,a.ksid,a.zt From rsgl_lzjhmx a,rsgl_lzjh b Where a.xh=b.xh And (b.zt=0 Or b.zt=1) and (a.zt=0 or a.zt=1) And (to_char(a.kssj,'yyyymmdd') =p1 or to_char(a.jssj+1,'yyyymmdd') =p1);
ls_xh Varchar2(10);
ls_zgid Varchar2(10);
ls_oldszks Varchar2(10);
ls_szks Varchar2(10);
ls_oldylzid Varchar2(10);
ls_ylzid Varchar2(10);
li_zt   Int;
ls_newxh Varchar2(10);
/*ls_sjid Varchar2(10);*/
xtcs_RSGL_LZKSDD Varchar2(10);
li_count   Int;
Begin
    xtcs_RSGL_LZKSDD:=F_GET_XTCS('RSGL_LZKSDD');--1人事科室跟着考勤组调,2 只调考勤组
     Open C1(as_dt);
      LOOP
        FETCH c1 Into ls_xh,ls_zgid,ls_ylzid,li_zt;
             IF c1 %NOTFOUND THEN EXIT; END IF;
            if xtcs_RSGL_LZKSDD='1' or xtcs_RSGL_LZKSDD='2' then

            if li_zt=0 then
             Select ylzid,szks Into ls_oldylzid,ls_oldszks From rsgl_zgda Where zgid=ls_zgid;
             if(xtcs_RSGL_LZKSDD ='1') then
                  select count(1) into li_count from rsgl_ksdm where ksid =ls_ylzid and rskspb=1 and mjpb='1';
                  if(li_count=1) then
                       ls_szks :=ls_ylzid;
                  else
                      ls_szks := pkg_yygl_service.f_getrskssjid(ls_ylzid);
/*原来只支持两层 注释掉      select sjid into ls_sjid from rsgl_ksdm where ksid =ls_ylzid;
                      select count(1) into li_count  from rsgl_ksdm where ksid =ls_sjid  and rskspb=1 and mjpb='1';
                       if(li_count=1) then
                            ls_szks :=ls_sjid;
                       else
                           ls_szks := ls_oldszks;
                       end if;*/
                  end if;
             else
                  ls_szks := ls_oldszks;
             end if;

             Update rsgl_zgda Set ylzid=ls_ylzid,szks=ls_szks   Where zgid = ls_zgid;

             update rsgl_lzjh set zt=1 where xh=ls_xh and zt=0;

             Update rsgl_lzjhmx Set zt = 1  Where xh = ls_xh And  to_char(kssj,'yyyymmdd') =as_dt;
             Update rsgl_lzjhmx Set zt = 2  Where xh = ls_xh And  to_char(jssj+1,'yyyymmdd') =as_dt And zt=1;

             p_getxhb('RSGL_ZGDD',ls_newxh);
             Insert Into rsgl_zgdd (xh,zgid,bdrq,zrks,zcks,zrylz,zcylz,ddlx,dqzt,czid,bz,czrq)
             Values(ls_newxh,ls_zgid,to_date(to_char(Sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),ls_szks,ls_oldszks,ls_ylzid,ls_oldylzid,1,1,'p_service','后台服务处理轮转调动',Sysdate);
             p_zgdals(ls_zgid);
             delete rsgl_kqls where zgid=ls_zgid and to_char(kqrq,'yyyy-mm-dd')>=to_char(Sysdate,'yyyy-mm-dd') and ksid=ls_oldylzid;
             delete rsgl_ksygkqpb where zgid=ls_zgid and to_char(rq,'yyyy-mm-dd')>=to_char(Sysdate,'yyyy-mm-dd') and ksid=ls_oldylzid;

           else
             Update rsgl_lzjhmx Set zt = 2  Where xh = ls_xh And  to_char(jssj+1,'yyyymmdd') =as_dt And zt=1;
             select count(*) into li_count  from rsgl_lzjh where xh=ls_xh  and jsrq<sysdate and zt=1;
             if(li_count=1) then
                 update rsgl_lzjh set zt=2 where xh=ls_xh and zt=1;
                 Update rsgl_zgda Set lbsx='0' Where zgid = ls_zgid;
              end if;
          end if;
         else  --轮转科室不做实际调转
            if li_zt=0 then
             update rsgl_lzjh set zt=1 where xh=ls_xh and zt=0;

             Update rsgl_lzjhmx Set zt = 2  Where xh = ls_xh And  to_char(jssj+1,'yyyymmdd') =as_dt And zt=1;
             Update rsgl_lzjhmx Set zt = 1  Where xh = ls_xh And  to_char(kssj,'yyyymmdd') =as_dt;
             else
             select count(*) into li_count  from rsgl_lzjh where xh=ls_xh  and jsrq<sysdate ;
             if(li_count=1) then
                 Update rsgl_lzjhmx Set zt = 2  Where xh = ls_xh And  to_char(jssj+1,'yyyymmdd') =as_dt And zt=1;
                 update rsgl_lzjh set zt=2 where xh=ls_xh ;
                 Update rsgl_zgda Set lbsx='0' Where zgid = ls_zgid;
              end if;
            end if;
         end if;
           p_getxhb('RSGL_ZDFWZXJL',ls_newxh);
          insert into RSGL_ZDFWZXJL(xh,lb,Zgid,RQ,ZXsj) values(ls_newxh,'自动服务人员轮转',ls_zgid,as_dt,sysdate);
        END LOOP;
      CLOSE c1;
End p_rylz;


 --处理历史档案表
Procedure p_zgdals(as_zgid In Varchar2)
As
strtoday Varchar2(20);
stryestoday Varchar2(20);
rowcount1 Int;
Begin
  Select  to_char(Sysdate,'YYYY-MM-DD') Into strtoday From dual;
  Select  to_char(Sysdate-1,'YYYY-MM-DD') Into stryestoday From dual;
  Select  count(*) Into  rowcount1 from rsgl_zgdals where  zgid=as_zgid and to_char(ksrq,'YYYY-MM-DD')=strtoday And to_char(jsrq,'YYYY-MM-DD')='2900-01-01';
If rowcount1=0
Then
update rsgl_zgdals set jsrq=to_date(stryestoday,'yyyy-mm-dd') where zgid=as_zgid and to_char(jsrq,'YYYY-MM-DD')='2900-01-01';
Else
  delete from rsgl_zgdals where  zgid=as_zgid and to_char(ksrq,'YYYY-MM-DD')=strtoday and to_char(jsrq,'YYYY-MM-DD')='2900-01-01';
End If;

insert into rsgl_zgdals (zgid,zggh, xm,xb,xl,mz,csrq,sfz,jtdz,jtyb,dzyj,jtdh, grdh, cjgzsj,szks,dyrq,zgxz,zjfs,zxpb, zxrq,zxyy,zw,zc,zglb,xzjb,jg,srm1,srm2,srm3,sypb, dlkl, gj,ryxz,zzmh,rdsj,bysj1, dexl,bysj2, zchdsj,byxx,sxzy,bz, wkxz,wkszd,kqpb, xgrq,czid,lbsx,xz,xw,hyzk,gzdh,yhmc,yhzh,yanlaobx,yiliaobx,sybxzh,zfgjjzh,htlb,htksrq,htjsrq,syqpb,syksrq,syjsrq,zyzg,zghdrq,ksrq,jsrq,dslb,dslbsj,rcpy, rcpysj, xhrz,rzsj,szxk,szxk2,szxk3,ynqz,kjbz,xj,xjd,gl,dsid,hspb,gzffpb,hl,gwdj,xxzrmsj,xxzrznx,qzw,qxzrmsj,qxzrznx,xjsjssj,xjsprsj,xjsrznx,qzc,qjsjssj,qjsrznx,qjsprsj,jpzc,jpsj,xgrdj,xgrprsj,xgrrznx,ygrdj,ygrprsj,ygrrznx,tglb,zsgl,fggl,zjnx,tgnx,xwsj,jylb,pg,brsbdj,ksjyfhjb,qdjb,gzbz,zzpb,gbpb,htgpb,bianzhi,gzsb,yppb,txpb,kqpxxh,jylb1,jylb2,ylzid) select  zgid,zggh, xm,xb,xl,mz,csrq,sfz,jtdz,jtyb,dzyj, jtdh, grdh, cjgzsj,szks,dyrq,zgxz,zjfs,zxpb, zxrq,zxyy,zw,zc,zglb,xzjb,jg,srm1,srm2,srm3,sypb, dlkl, gj,ryxz,zzmh,rdsj,bysj1, dexl,bysj2, zchdsj,byxx,sxzy,bz, wkxz,wkszd,kqpb, xgrq,czid,lbsx ,xz,xw,hyzk,gzdh,yhmc,yhzh,yanlaobx,yiliaobx,sybxzh,zfgjjzh,htlb,htksrq,htjsrq,syqpb,syksrq,syjsrq,zyzg,zghdrq,
to_date(strtoday,'yyyy-mm-dd'),to_date('2900-01-01','YYYY-MM-DD'),dslb,dslbsj,rcpy, rcpysj, xhrz,rzsj,szxk,szxk2,szxk3,ynqz,kjbz,xj,xjd,gl,dsid,hspb,gzffpb,hl,ygrdj,xxzrmsj,xxzrznx,qzw,qxzrmsj,qxzrznx,xjsjssj,xjsprsj,xjsrznx,qzc,qjsjssj,qjsrznx,qjsprsj,jpzc,jpsj,xgrdj,xgrprsj,xgrrznx,ygrdj,ygrprsj,ygrrznx,tglb,zsgl,fggl,zjnx,tgnx,xwsj,jylb,pg,brsbdj,ksjyfhjb,qdjb,gzbz,zzpb,gbpb,htgpb,bianzhi,gzsb,yppb,txpb,kqpxxh,jylb1,jylb2,ylzid from rsgl_zgda where zgid=as_zgid ;

End p_zgdals;


function f_getrskssjid(ls_ksid varchar) return varchar2--返回该科室的上级人事科室
 is
    ls_sjid Varchar2(10);
    li_count   Int;
    ls_szks Varchar2(10);
  begin
    select sjid into ls_sjid from rsgl_ksdm where ksid =ls_ksid;
    select count(1) into li_count  from rsgl_ksdm where ksid =ls_sjid  and rskspb=1;
    if(li_count=1) then
        ls_szks :=ls_sjid;
    else
        ls_szks := f_getrskssjid(ls_sjid);
    end if;
    return(ls_szks);
end f_getrskssjid;


end pkg_yygl_service;





--------------------创建包------------

create or replace package pkg_yygl_service is

-- Author : MOJIAOYANG
-- Created : 2006-9-9 上午 11:27:54
-- Purpose : 医院管理后台服务程序
-- 最近更新 : 2014-09-28 tangwei

Type cursorType Is Ref Cursor;
Procedure p_service(as_date In Varchar2);

--得到序号表序号
procedure p_getxhb(as_tablename In Varchar2, xh Out Varchar2);
--自动任务
Procedure p_job;
--初始化科室代码;
Procedure p_init_ksdm(as_rootid In Varchar2,
as_ksdm In Varchar2,
as_last In Varchar2);

--处理职工档案
Procedure p_zgda(as_dt In Varchar2);
--处理职工档案历史
Procedure p_zgdals(as_zgid In Varchar2);
--处理人员调动
Procedure p_rydd(as_dt In Varchar2);
--处理人员轮转
Procedure p_rylz(as_dt In Varchar2);
--获取人事科室上级ID函数
function f_getrskssjid (ls_ksid varchar) return varchar2;

end pkg_yygl_service;