存储过程的编写

CREATE OR REPLACE 
PROCEDURE GET_STATIC_ABOUT_MYD(V_IN_TYPE IN VARCHAR2) IS
  V_ID         VARCHAR2(32); --主键
  V_MYD_AVG    NUMBER(3, 1); --满意度
  V_START_DATE DATE; --开始时间
  V_END_DATE   DATE; --结束时间
  V_TYPE       VARCHAR2(2);
  V_YEAR       VARCHAR2(10); --年
  V_MONTH      VARCHAR2(10); --月
  V_WEEK       VARCHAR2(10); --周

  --用户
  CURSOR CUR1 IS
    SELECT A.*, B.ORGANIZATION_LEVEL, B.COUNTY_ID
      FROM POS_EMP@KFPT A, PH_ORGANIZATION B
     WHERE A.POS_ID = B.ORGANIZATION_ID
       AND A.EMP_STATUS = 1
       AND A.EMP_PROPERTY = 8
       AND B.ORGANIZATION_LEVEL IN (1, 2);
BEGIN
  DELETE FROM PH_CHARTS_MYD_RZ;
  V_TYPE := V_IN_TYPE;
  --年--
  IF V_TYPE = '1' THEN
    SELECT TRUNC(SYSDATE, 'YY'),
           TRUNC(SYSDATE, 'MM'),
           TO_CHAR(SYSDATE, 'YYYY'),
           TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1), 'YYYYMM')
      INTO V_START_DATE, V_END_DATE, V_YEAR, V_MONTH
      FROM DUAL;
    V_WEEK := NULL;
    --月--
  ELSIF V_TYPE = '2' THEN
    SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1),
           TRUNC(SYSDATE, 'MM'),
           TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1), 'YYYYMM'),
           TO_CHAR(TRUNC(SYSDATE, 'IW'), 'IW')
      INTO V_START_DATE, V_END_DATE, V_MONTH, V_WEEK
      FROM DUAL;
    V_YEAR := NULL;
    --星期--
  ELSIF V_TYPE = '3' THEN
    SELECT TRUNC(SYSDATE, 'IW') - 7,
           TRUNC(SYSDATE, 'IW'),
           TO_CHAR(SYSDATE, 'YYYY'),
           TO_CHAR(TRUNC(SYSDATE, 'IW'), 'IW')
      INTO V_START_DATE, V_END_DATE, V_YEAR, V_WEEK
      FROM DUAL;
    V_MONTH := NULL;
  END IF;

  --按用户
  FOR LINE1 IN CUR1 LOOP
    --平均满意度
    SELECT CASE
             WHEN NVL(SUM(A.SCORE) / COUNT(A.USER_ID), 0) = 0 THEN
              NVL(SUM(A.SCORE) / COUNT(A.USER_ID), 0)
             ELSE
              (10 - NVL(SUM(A.SCORE) / COUNT(A.USER_ID), 0))
           END
      INTO V_MYD_AVG
      FROM PH_SERVICE_EVALUATION A
     WHERE A.SCORE_DATE >= V_START_DATE
       AND A.SCORE_DATE < V_END_DATE
       AND A.USER_ID = LINE1.EMP_ID
       AND A.SCORE != '0';
    --主键
    SELECT LOWER(RAWTOHEX(SYS_GUID())) INTO V_ID FROM DUAL;
    INSERT INTO PH_CHARTS_MYD
      (ID, --  varchar2(32)      主键
       USER_ID, --  varchar2(32)  y   用户id
       USER_NAME, --  varchar2(30)  y   用户名
       CREATE_DATE, --  date  y   创建时间
       ORG_ID, -- varchar2(32)  y   机构id
       MYD_AVG, -- number  y   满意度
       ORG_LEVEL, --  number(1) y   机构级别
       SERVIAL_NO, -- number(10)  y   序列号
       COUNTY_ID,
       YEAR, -- varchar2(10)  y   年份
       MONTH, --  varchar2(10)  y   月份
       WEEK --  varchar2(5) y   星期
       )
    VALUES
      (V_ID,
       LINE1.EMP_ID,
       LINE1.EMP_NAME, --  varchar2(30)  y   用户名
       SYSDATE, --  date  y   创建时间
       LINE1.POS_ID, -- varchar2(32)  y   机构id
       V_MYD_AVG, -- number  y   随访量
       LINE1.ORGANIZATION_LEVEL, --  number(1) y   机构级别
       NULL, -- number(10)  y   序列号
       LINE1.COUNTY_ID,
       V_YEAR, -- varchar2(10)  y   年份
       V_MONTH, --  varchar2(10)  y   月份
       V_WEEK --  varchar2(5) y   星期)
       );
    --日志
    INSERT INTO PH_CHARTS_MYD_RZ
      (ID, MYD_AVG, ORG_LEVEL, COUNTY_ID)
    VALUES
      (V_ID, V_MYD_AVG, LINE1.ORGANIZATION_LEVEL, LINE1.COUNTY_ID);
  END LOOP;
  --更新序列号
  UPDATE PH_CHARTS_MYD A
     SET A.SERVIAL_NO =
         (SELECT XH
            FROM (SELECT ROW_NUMBER() OVER(PARTITION BY B.COUNTY_ID, B.ORG_LEVEL ORDER BY B.MYD_AVG DESC) AS XH,
                         B.ID
                    FROM PH_CHARTS_MYD_RZ B) C
           WHERE C.ID = A.ID)
   WHERE EXISTS (SELECT XH
            FROM (SELECT ROW_NUMBER() OVER(PARTITION BY B.COUNTY_ID, B.ORG_LEVEL ORDER BY B.MYD_AVG DESC) AS XH,
                         B.ID
                    FROM PH_CHARTS_MYD_RZ B) C
           WHERE C.ID = A.ID);
  COMMIT;
END;

CREATE OR REPLACE 
FUNCTION GET_UUID RETURN VARCHAR IS
  GUID VARCHAR(50);
BEGIN
  GUID := LOWER(RAWTOHEX(SYS_GUID()));
  RETURN SUBSTR(GUID, 1, 8) || '-' || SUBSTR(GUID, 9, 4) || '-' || SUBSTR(GUID,
                                                                          13,
                                                                          4) || '-' || SUBSTR(GUID,
                                                                                              17,
                                                                                              4) || '-' || SUBSTR(GUID,
                                                                                                                  21,
                                                                                                                  12);
END GET_UUID;

CREATE OR REPLACE 
PROCEDURE AUTO_CREATE_MANAGE_DIA IS
  V_AUTO_CREATE_MANAGE_ID VARCHAR2(36); --主键
  V_AUTO_TYPE             VARCHAR2(1); --类型（人群）
  V_AUTO_STATUS           VARCHAR2(1); --创建状态
  --V_CREATE_TIME           DATE; --创建时间
  --V_PERSON_INFO_ID        VARCHAR2(36); --个人档案ID

  --糖尿病

  CURSOR PERSONS IS
    SELECT PERSON.PERSON_INFO_ID, PERSON.IS_HYPERTENSION
      FROM PH_PERSON_INFO PERSON
     WHERE EXISTS (SELECT 1
              FROM PH_PERSON_DISEASE P
             WHERE P.PERSON_INFO_ID = PERSON.PERSON_INFO_ID
               AND P.DISEASE_CODE = '3')
       AND PERSON.IS_HYPERTENSION != 1
       AND PERSON.STATUS_CODE = '0'
       AND NOT EXISTS (SELECT 1
              FROM PH_AUTO_CREATE_MANAGE P
             WHERE P.PERSON_INFO_ID = PERSON.PERSON_INFO_ID
               AND P.AUTO_TYPE = '3');
BEGIN
  V_AUTO_STATUS := '0';
  V_AUTO_TYPE   := '3';
  FOR LINE1 IN PERSONS LOOP
    SELECT LOWER(RAWTOHEX(SYS_GUID()))
      INTO V_AUTO_CREATE_MANAGE_ID
      FROM DUAL;
    INSERT INTO PH_AUTO_CREATE_MANAGE
      (AUTO_CREATE_MANAGE_ID,
       PERSON_INFO_ID,
       AUTO_TYPE,
       CREATE_TIME,
       AUTO_STATUS)
    VALUES
      (V_AUTO_CREATE_MANAGE_ID,
       LINE1.PERSON_INFO_ID,
       V_AUTO_TYPE,
       SYSDATE,
       V_AUTO_STATUS);
  END LOOP;
  COMMIT;
END;

CREATE OR REPLACE 
PROCEDURE AUTO_CREATE_MANAGE_ELDER(V_SYSTEM_PARAM IN VARCHAR2) IS
  --未创建专档记录表
  V_AUTO_CREATE_MANAGE_ID VARCHAR2(36); --主键
  V_AUTO_TYPE             VARCHAR2(1); --类型（人群）
  V_AUTO_STATUS           VARCHAR2(1); --创建状态
  --老年人管理卡
  V_ELDER_INFO_ID VARCHAR2(36); --老年人专档主键
  --V_END_DATE              VARCHAR(4);
  --V_CREATE_TIME           DATE; --创建时间
  --V_PERSON_INFO_ID        VARCHAR2(36); --个人档案ID

  --应建立老年人专档但未建立的
  CURSOR PERSONS IS
    SELECT PERSON.PERSON_INFO_ID,
           PERSON.MANAGE_ORG_ID,
           PERSON.NAME,
           PERSON.BIRTHDAY,
           PERSON.IS_ELDER
      FROM PH_PERSON_INFO PERSON
     WHERE (TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy')) -
           TO_NUMBER(TO_CHAR(PERSON.BIRTHDAY, 'yyyy'))) >= 65
       AND PERSON.IS_ELDER != 1
       AND PERSON.STATUS_CODE = '0';

BEGIN
  --若系统参数为1，则往老年人专档表中插入记录，并删除ph_auto_create_manage表中老年人的记录
  IF V_SYSTEM_PARAM = '1' THEN
    FOR PER IN PERSONS LOOP
      SELECT LOWER(RAWTOHEX(SYS_GUID())) INTO V_ELDER_INFO_ID FROM DUAL;
      INSERT INTO PH_ELDER_INFO
        (ELDER_INFO_ID,
         PERSON_INFO_ID,
         MANAGE_ORG_ID,
         NAME,
         MANAGE_DATE,
         IS_CANCEL)
      VALUES
        (V_ELDER_INFO_ID,
         PER.PERSON_INFO_ID,
         PER.MANAGE_ORG_ID,
         PER.NAME,
         SYSDATE,
         '0');
      --每往老年人专档记录中插入一条数据，则更新个人档案表中的is_elder和IS_ELDER_DATE字段
      UPDATE PH_PERSON_INFO PI
         SET PI.IS_ELDER = 1, PI.IS_ELDER_DATE = SYSDATE
       WHERE PI.PERSON_INFO_ID = PER.PERSON_INFO_ID
         AND PI.STATUS_CODE = '0';
    END LOOP;
    --删除未创建专档记录表中老年人的记录
    DELETE FROM PH_AUTO_CREATE_MANAGE AC WHERE AC.AUTO_TYPE = '1';
  
  ELSE
    --若系统参数不为1，则往未建专档记录表中插入数据
    --状态
    V_AUTO_STATUS := '0';
    --类型
    V_AUTO_TYPE := '1';
    FOR LINE1 IN PERSONS LOOP
      SELECT LOWER(RAWTOHEX(SYS_GUID()))
        INTO V_AUTO_CREATE_MANAGE_ID
        FROM DUAL;
      INSERT INTO PH_AUTO_CREATE_MANAGE
        (AUTO_CREATE_MANAGE_ID,
         PERSON_INFO_ID,
         AUTO_TYPE,
         CREATE_TIME,
         AUTO_STATUS)
      VALUES
        (V_AUTO_CREATE_MANAGE_ID,
         LINE1.PERSON_INFO_ID,
         V_AUTO_TYPE,
         SYSDATE,
         V_AUTO_STATUS);
    END LOOP;
  END IF;
  COMMIT;
END;