先创建包，再创建包体
---------------创建包体---------------------
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();
ls_newxh Varchar2();
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,,) 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,,);
xh := '';
End p_getxhb; --初始化科室代码
Procedure p_init_ksdm(as_rootid In Varchar2,as_ksdm In Varchar2,as_last In Varchar2)
As
ls_ksdm Varchar2();
ls_jb Numeric();
ls_sjid Varchar2();
ls_tempdm Varchar2();
ls_mjpb Numeric();
ls_ksid Varchar2();
CURSOR c1 (p1 Varchar2) Is
Select ksid From rsgl_ksdm Where sjid=p1;
Begin
Select nvl(mjpb,),ksdm Into ls_mjpb, ls_ksdm From rsgl_ksdm Where ksid=as_rootid;
If(as_ksdm Is Null) Then
Select length(''||to_char(to_number(as_last)+))- Into ls_jb From dual;
Select ''||to_char(to_number(as_last)+) Into ls_sjid From dual;
Update rsgl_ksdm Set ksdm=substr(ls_sjid,ls_jb,) Where ksid=as_rootid;
Else
Update rsgl_ksdm Set ksdm=nvl(as_ksdm,'')||substr(''||to_char(to_number(as_last)+),length(''||to_char(to_number(as_last)+))-,) Where ksid=as_rootid;
End If;
If ls_mjpb= Then
ls_tempdm:='';
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(''||to_char(to_number(ls_tempdm)+),length(''||to_char(to_number(ls_tempdm)+))-,);
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= and zt=;
ls_zgid Varchar2();
ls_xh Varchar2();
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 = ,zxpb= Where zgid = ls_zgid;
Update rsgl_ygjcjl Set zt = 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=;
ls_zgid Varchar2();
ls_zrks Varchar2();
ls_zcks Varchar2();
ls_zrylz Varchar2();
ls_zcylz Varchar2();
ls_xh Varchar2();
ls_newxh Varchar2();
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 = 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= Or b.zt=) and (a.zt= or a.zt=) And (to_char(a.kssj,'yyyymmdd') =p1 or to_char(a.jssj+,'yyyymmdd') =p1);
ls_xh Varchar2();
ls_zgid Varchar2();
ls_oldszks Varchar2();
ls_szks Varchar2();
ls_oldylzid Varchar2();
ls_ylzid Varchar2();
li_zt Int;
ls_newxh Varchar2();
/*ls_sjid Varchar2(10);*/
xtcs_RSGL_LZKSDD Varchar2();
li_count Int;
Begin
xtcs_RSGL_LZKSDD:=F_GET_XTCS('RSGL_LZKSDD');--1人事科室跟着考勤组调, 只调考勤组
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='' or xtcs_RSGL_LZKSDD='' then if li_zt= then
Select ylzid,szks Into ls_oldylzid,ls_oldszks From rsgl_zgda Where zgid=ls_zgid;
if(xtcs_RSGL_LZKSDD ='') then
select count() into li_count from rsgl_ksdm where ksid =ls_ylzid and rskspb= and mjpb='';
if(li_count=) 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= where xh=ls_xh and zt=; Update rsgl_lzjhmx Set zt = Where xh = ls_xh And to_char(kssj,'yyyymmdd') =as_dt;
Update rsgl_lzjhmx Set zt = Where xh = ls_xh And to_char(jssj+,'yyyymmdd') =as_dt And zt=; 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,,,'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 = Where xh = ls_xh And to_char(jssj+,'yyyymmdd') =as_dt And zt=;
select count(*) into li_count from rsgl_lzjh where xh=ls_xh and jsrq<sysdate and zt=;
if(li_count=) then
update rsgl_lzjh set zt= where xh=ls_xh and zt=;
Update rsgl_zgda Set lbsx='' Where zgid = ls_zgid;
end if;
end if;
else --轮转科室不做实际调转
if li_zt= then
update rsgl_lzjh set zt= where xh=ls_xh and zt=; Update rsgl_lzjhmx Set zt = Where xh = ls_xh And to_char(jssj+,'yyyymmdd') =as_dt And zt=;
Update rsgl_lzjhmx Set zt = 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=) then
Update rsgl_lzjhmx Set zt = Where xh = ls_xh And to_char(jssj+,'yyyymmdd') =as_dt And zt=;
update rsgl_lzjh set zt= where xh=ls_xh ;
Update rsgl_zgda Set lbsx='' 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();
stryestoday Varchar2();
rowcount1 Int;
Begin
Select to_char(Sysdate,'YYYY-MM-DD') Into strtoday From dual;
Select to_char(Sysdate-,'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=
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();
li_count Int;
ls_szks Varchar2();
begin
select sjid into ls_sjid from rsgl_ksdm where ksid =ls_ksid;
select count() into li_count from rsgl_ksdm where ksid =ls_sjid and rskspb=;
if(li_count=) 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;