oracle存儲函數實例

CREATE OR REPLACE 
PROCEDURE      "PROC_INS_DAY_DOCTOR_LL"(v_date in VARCHAR) is

--PROC_INS_DAY_DOCTOR_LL 是存儲函數名

    v_id                                                DAY_DOCTOR_STATS.DDOS_ID%TYPE;--主鍵
    v_hospital_id                                       DAY_DOCTOR_STATS.HOSPITAL_ID%TYPE;--醫院ID
    v_doctor_id                                         DAY_DOCTOR_STATS.DOCTOR_ID%TYPE;--醫生id
    v_total_money                                              DAY_DOCTOR_STATS.TOTAL_MONEY%TYPE;--總金額
    v_anti_money                                              DAY_DOCTOR_STATS.ANTI_MONEY%TYPE;--抗菌藥金額    
    v_create_date                                         DAY_DOCTOR_STATS.CREATE_DATE%TYPE;--創建時間
    
    CURSOR CUR_HOSPITAL_DOCT IS--聲明游標  醫生數據(住院)
    SELECT DISTINCT c.DOCTOR_ID
    FROM FIN_IPR_INMAININFO a,FIN_IPB_MEDICINELIST b,DOCTOR c
    WHERE a.INPATIENT_NO= b.INPATIENT_NO
    and b.RECIPE_DOCCODE=c.EMPL_CODE
    AND to_char(a.OUT_DATE,'yyyy-mm-dd')=v_date
    and rownum<2;
    

BEGIN
     OPEN CUR_HOSPITAL_DOCT; --打開游標  
  --迴圈游標  
  LOOP  
    FETCH CUR_HOSPITAL_DOCT  INTO v_doctor_id; --取值  
    EXIT WHEN CUR_HOSPITAL_DOCT%NOTFOUND; --當沒有記錄時退出迴圈  
    
        --把住院用藥信息數據導入到一個臨時表中
        Insert into FIN_IPB_MEDICINELIST_NEW (HOSPITAL_CODE,RECIPE_NO,SEQUENCE_NO,INPATIENT_NO,TRANS_TYPE,CARD_NO,NAME,INHOS_DEPTCODE,RECIPE_DOCCODE,DRUG_CODE,DRUG_NAME,SPECS,DOSE_MODEL_CODE,UNIT_PRICE,QTY,FREQUENCY_NAME,FREQUENCY_CODE,USAGE_CODE,USE_NAME,DOSE_ONCE,DOSE_UNIT,BASE_DOSE,DAYS,PACK_QTY,PRICE_UNIT,OWN_MONEY,MEDICARE_MONEY,FEE_DATE,MO_ORDER,BROUGHT_FLAG,MEDICAL_TEAM_CODE,IS_HERBS,MANUFACTURER,ID)
select * from FIN_IPB_MEDICINELIST WHERE INPATIENT_NO in(select INPATIENT_NO from FIN_IPR_INMAININFO where to_char(OUT_DATE,'yyyy-mm-dd')=v_date);


    
    --主鍵
    select substr(sys_guid(),1,8)||'-'||substr(sys_guid(),9,4)||'-'||substr(sys_guid(),13,4)||'-'||substr(sys_guid(),17,4)||'-'||substr(sys_guid(),20,12) 
      into v_id from dual;
    
    --HOSPITAL_ID
    --醫院編碼
    select '9991' into v_hospital_id from dual;
    
    --醫生編碼
    SELECT EMPL_CODE INTO v_empl_code FROM  DOCTOR WHERE DOCTOR_ID=v_doctor_id;

    --TOTAL_MONEY
    --總金額
    SELECT nvl(sum(OWN_MONEY),0) into v_total_money 
    FROM FIN_IPB_MEDICINELIST_NEW
    WHERE DRUG_CODE IN (SELECT DRUG_CODE FROM PHA_COM_BASEINFO WHERE  manufacturer is not null )
    AND RECIPE_DOCCODE =v_empl_code;
    
    --ANTI_MONEY
    --抗菌藥金額
    select nvl(sum(OWN_MONEY),0) into v_anti_money
    from FIN_IPB_MEDICINELIST_NEW 
    where  RECIPE_DOCCODE =v_empl_code
    AND DRUG_CODE IN (SELECT DRUG_CODE FROM PHA_COM_BASEINFO WHERE  IS_KJY='1');
    
    --CREATE_DATE
    --創建時間
        select TO_DATE(v_date,'yyyy-mm-dd') into v_create_date from dual;

    --刪除當天的數據
    delete from DAY_DOCTOR_STATS 
     where CREATE_DATE=v_create_date and DOCTOR_ID=v_doctor_id;
    
    --清除住院用藥表數據
    DELETE FROM FIN_IPB_MEDICINELIST_NEW;

    --生成一條新數據（其實就是向一張表插入數據）
    insert into DAY_DOCTOR_STATS
    (DDOS_ID,HOSPITAL_ID,DOCTOR_ID,TOTAL_MONEY,ANTI_MONEY,CREATE_DATE)        
    values(v_id,v_hospital_id,v_doctor_id,v_total_money,v_anti_money,v_create_date);
  commit;
  END LOOP; 
  CLOSE CUR_HOSPITAL_DOCT;--關閉這個游標

END;