1 --创建包 2 create or replace package PAK_ExecuteAttendingDoctor is 3 type cp_results is ref cursor; 4 procedure ExecuteAttendingDoctor(start_time in varchar2,end_time in varchar2,cp_results out cp_results); 5 end PAK_ExecuteAttendingDoctor;
1 --创建包体 2 create or replace package body PAK_ExecuteAttendingDoctor is 3 procedure ExecuteAttendingDoctor(start_time in varchar2, 4 end_time in varchar2, 5 cp_results out cp_results) is 6 begin 7 open cp_results for 8 select distinct * 9 from (select t.dept_code, t.dept_name 10 from dcp_sys_user t 11 where t.dept_code > 0 12 and t.dept_code is not null 13 group by t.dept_code, t.dept_name) mm, 14 (select a.execute_dept, 15 a.attending_doctor, 16 nvl(a.fhrs, 0) fhrs, 17 nvl(b.nrrs, 0) nrrs, 18 nvl(c.wcrs, 0) wcrs, 19 round(nvl(b.nrrs / a.fhrs, 0), 4) nrl, 20 round(nvl(c.wcrs / b.nrrs, 0), 4) wcl, 21 nvl(d.xdyzzs, 0) xdyzzs, 22 nvl(xdlcljyzs, 0) xdlcljyzs, 23 round(nvl(xdlcljyzs / d.xdyzzs, 0), 4) xdl 24 from --按医生统计符合人数 25 (select execute_dept, attending_doctor, count(patient_no) fhrs 26 from lcp_patient_visit v 27 where v.conform_master_id > 0 28 and v.admission_date >= 29 to_date(start_time, 'yyyy-mm-dd') 30 and v.admission_date <= 31 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss') 32 group by v.execute_dept, v.attending_doctor) a, 33 ----纳入人数--- 34 (select execute_dept, 35 v1.attending_doctor, 36 count(patient_no) nrrs 37 from lcp_patient_visit v1 38 where v1.cp_master_id > 0 39 and v1.admission_date >= 40 to_date(start_time, 'yyyy-mm-dd') 41 and v1.admission_date <= 42 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss') 43 group by v1.execute_dept, v1.attending_doctor) b, 44 ----完成人数----- 45 (select execute_dept, 46 v2.attending_doctor, 47 count(patient_no) wcrs 48 from lcp_patient_visit v2 49 where v2.cp_master_id > 0 50 and v2.cp_state = 11 51 and v2.admission_date >= 52 to_date(start_time, 'yyyy-mm-dd') 53 and v2.admission_date <= 54 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss') 55 group by v2.execute_dept, v2.attending_doctor) c, 56 ----下达医嘱总数----- 57 (select count(*) xdyzzs, v.execute_dept, v.attending_doctor 58 from lcp_patient_log_order t, lcp_patient_visit v 59 where t.patient_no in 60 (select t.patient_no 61 from lcp_patient_visit t 62 where t.cp_state in (1, 11, 21) 63 and t.admission_date >= 64 to_date(start_time, 'yyyy-mm-dd') 65 and t.admission_date <= 66 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss')) 67 and t.patient_no = v.patient_no 68 group by v.execute_dept, v.attending_doctor) d, 69 ----下达临床路径医嘱数----- 70 (select count(*) xdlcljyzs, v.execute_dept, v.attending_doctor 71 from lcp_patient_log_order t, lcp_patient_visit v 72 where t.patient_no in 73 (select t.patient_no 74 from lcp_patient_visit t 75 where t.cp_state in (1, 11, 21) 76 and t.admission_date >= 77 to_date(start_time, 'yyyy-mm-dd') 78 and t.admission_date <= 79 to_date(end_time, 'yyyy-mm-dd hh24:mi:ss')) 80 and t.cp_node_order_item_id > 0 81 and t.patient_no = v.patient_no 82 group by v.execute_dept, v.attending_doctor) e 83 84 where a.execute_dept = b.execute_dept(+) 85 and a.attending_doctor = b.attending_doctor(+) 86 and a.execute_dept = c.execute_dept(+) 87 and a.attending_doctor = c.attending_doctor(+) 88 and a.execute_dept = d.execute_dept(+) 89 and a.attending_doctor = d.attending_doctor(+) 90 and a.execute_dept = e.execute_dept(+) 91 and a.attending_doctor = e.attending_doctor(+)) aa 92 where mm.dept_name = aa.execute_dept 93 order by aa.execute_dept, nrrs desc; 94 end ExecuteAttendingDoctor; 95 96 end PAK_ExecuteAttendingDoctor;