-- 创建存储过程
create or replace procedure SYNC_YESTERDAY_SETTLEMENT_DATA as
begin
INSERT INTO YW_SKJL_VIEW(SKJLID, GRBH, XM, SFZH, JGBH, JGMC, SKSJ, JSSJ, SFDB, BDJG, LSH, MBZP, XCZP, AKC190,
AAE072,
DBRSFZH, DBRXM, DBRLXFS, DBGX, DBYY)
SELECT MIN(S.SKJLID) SKJLID,
MIN(S.PERSONALNUMBER) GRBH,
MIN(S.XM) XM,
MIN(S.SFZH) SFZH,
MIN(L.INSTITUTION_NO) JGBH,
MIN(L.JGMC) JGMC,
MIN(S.SKSJ) SKSJ,
MIN(TO_CHAR(S.SKSJ, 'yyyymmddhh24miss')) JSSJ,
MAX(S.SUBSTITUTES) SFDB,
MAX(S.BDSFCG) BDJG,
S.LSH,
MAX(DECODE(S.SUBSTITUTES, 0,
DECODE(S.FIRST_TEMPLATE_PHOTO, NULL, S.SECOND_TEMPLATE_PHOTO, S.FIRST_TEMPLATE_PHOTO),
S.SUBSTITUTEPHOTOPATH)) MBZP,
MAX(DECODE(S.SUBSTITUTES, 0, S.SKRXZPLJ, S.SUBSTITUTEPHOTOPATH)) XCZP,
MAX(S.VISIT_SERIAL_NUMBER) AKC190,
MAX(S.DOC_CODE) AAE072,
MAX(S.SUBSTITUTEID) DBRSFZH,
MAX(S.SUBSTITUTENAME) DBRXM,
MAX(S.SUBSTITUTEMOBILE) DBRLXFS,
MAX(S.SUBSTITUTEREASON) DBGX,
MAX(S.SUBSTITUTERELATION) DBYY
FROM YW_SKJL S,
JC_LDJG L
WHERE S.YDH = L.JGDM
AND S.ZPLX != -5
AND S.ZPLX != -6
AND S.ZPLX != -2
AND NOT EXISTS(SELECT 1 FROM YW_SKJL_VIEW V WHERE S.SKJLID = V.SKJLID)
AND TO_CHAR(S.SKSJ, 'yyyyMMdd') = TO_CHAR(sysdate - 2, 'yyyyMMdd')
GROUP BY S.LSH;
end;
-- 创建定时任务
declare
tm_job number;
begin
dbms_job.submit(tm_job,
'SYNC_YESTERDAY_SETTLEMENT_DATA;',
TRUNC(sysdate + 1) + 1 / 24,
'TRUNC(sysdate+1)+1/24'
);
commit;
end;
-- 查询定时器
select *
from user_jobs;
-- 运行定时器
begin
DBMS_JOB.RUN(63);
end ;
-- 删除
begin
dbms_job.remove(63);
end;
/*结算明细清单*/
SELECT
LSH,
S.PERSONALNUMBER GRBH,
J.XM,
J.SFZH,
L.INSTITUTION_NO JGBH,
L.JGMC,
TO_CHAR(S.SKSJ,'yyyymmddhh24miss') JSSJ,
S.SUBSTITUTES SFDB,
DECODE(S.zplx, 1, 1, 2, 1, 3, 1, 0) BDJG
FROM YW_SKJL S
LEFT JOIN YW_JZZP J ON S.ZPID = J.ZPID,
JC_LDJG L
WHERE S.YDH = L.JGDM
ORDER BY S.SKSJ DESC;
create or replace view v_hzfi_settlementDetails
as
/*考勤明细清单(LX: 1:签到 2:签退 BDJG: 0:失败 1:成功)*/
SELECT D.DOCTORNAME XM,
D.DOCTORIDCARDNUMBER SFZH,
L.INSTITUTION_NO JGBH,
L.JGMC,
DECODE(D.TYPE,2,2,1) LX,
TO_CHAR(D.ATTENDANCETIME,'yyyymmddhh24miss') KQSJ,
DECODE(D.RESULT, 1, 1, 0) BDJG
FROM YW_ATTENDANCERECORD D,
JC_LDJG L
WHERE D.HOSPITALID = L.ID ORDER BY KQSJ DESC ;
create user hzfi identified by "hzsi#2020";
grant connect to hzfi;
/*#给表赋予权限*/
grant select on v_hzfi_settlementDetails to hzfi;
grant select on v_hzfi_attendanceDetails to hzfi;
/*#创建同义词*/
grant create synonym to hzfi;
create or replace synonym hzfi.v_hzfi_settlementDetails for v_hzfi_settlementDetails;
create or replace synonym hzfi.v_hzfi_attendanceDetails for v_hzfi_attendanceDetails;
/*注意:如果不创建同义词,那么hzfi用户查询表时,必须使用table这类的写法,不能直接用table.*/
ORACLE存储过程、定时任务、赋予用户只读视图
最新推荐文章于 2024-06-02 09:15:00 发布