ORACLE存储过程、定时任务、赋予用户只读视图

-- 创建存储过程
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.*/