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;
转载于:https://www.cnblogs.com/Jin520304/p/7307621.html
