create or replace procedure GetEmergencyList_test
(
bdSrc in date, --急診日期
edSrc in date, --急診日期
emp_sn in varchar2, --操作人
emer_stateV in varchar2, --急診標志(為空則回傳所有)
dept_sn in varchar2, --急診科室
shift_id in int, --班次(暫未使用)
querytype in int := 0 --查詢型別(0:護士站,1:醫生站)
) is
bd date;
ed date;
bd4Hour date;
begin
bd4Hour := sysdate - 4/24;
if querytype <> 0 then
ed := to_date(TO_CHAR( sysdate,'YYYY-MM-DD') + ' 23:59:59' ,'YYYY-MM-DD HH24:MI:SS');
bd := sysdate - 4/24;
end if;
if querytype = 0 then
ed := to_date(TO_CHAR( edSrc,'YYYY-MM-DD') + ' 23:59:59' ,'YYYY-MM-DD HH24:MI:SS');
bd := to_date(TO_CHAR( bdSrc,'YYYY-MM-DD') + ' 00:00:00' ,'YYYY-MM-DD HH24:MI:SS'); --設定成護士站查詢一整天時間
end if;
select * from (
select
a.p_id, --病人id
a.times, --就診次數
a."NUMBER", --排隊號
a.emer_id, --急診id
a.operator_date as emer_date, --分診時間
a.dept_sn, --分診科室
a.emer_state, --急診標志
a.man_grade, --病情分級
a.operator, --分診人編號
a.operator_date, --保存時間
a.notice_dept, --通知科室
a.notice_date, --通知時間
a.doctor_sn, --醫生編號
a.visit_date, --就診時間
a.observed_bd, --留觀開始時間
a.observed_ed, --留觀結束時間
b.address, --病人住址
b.outpatient_id, --病人id
b.telephone, --病人電話
b.reg_id, --注冊id
b.code_no, --病人編號
b.id_card, --身份證號
b.birthday, --病人生日
b.name, --病人姓名
b.name as patientname,--病人姓名
c.dept_name, --分診科室名稱
d.room_name, --就診科室名稱
e.opera_name, --創建人名稱
(select doctor_name from dic_organization_doctor where querytype=0 and doctor_code=a.doctor_sn) doctor_name, --醫生名稱
to_date(TO_CHAR( a.operator_date,'YYYY-MM-DD') + ' 00:00:00' ,'YYYY-MM-DD HH24:MI:SS') yymmdd, --排隊日期
ROUND(TO_NUMBER(operator_date - sysdate) * 24 * 60) as dmin, --相差時間（分）
case b.sex when '1' then '男' when '2' then '女' else '待定' end sex --性別
--,cast(null as varchar(5)) as visit_flag
,(select visit_flag from mz_visit
where visit_dept in('a1201','a1202','a1221','a1222') and visit_date >= bd4Hour
and a.p_id = p_id and a.times = times) as visit_flag
--,cast(null as varchar(5)) as patient_type
,(select patient_type from mz_visit
where visit_dept in('a1201','a1202','a1221','a1222') and visit_date >= bd4Hour
and a.p_id = p_id and a.times = times) patient_type
from bk_emergency a
left join bk_patient b on a.p_id=b.outpatient_id
left join dic_organization_dept c on a.dept_sn=c.dept_code
left join dic_organization_dept_room d on a.notice_dept=d.room_no
left join bk_users e on a.operator=e.opera
where
a.dept_sn=dept_sn
and (a.isend=0 or (emer_stateV='已就診' and querytype=0 and a.emer_date between bd and ed))
and a.emer_state<>'已取消'
and( (case when emer_state <> '留院觀察' and observed_bd is not null then 0 else 1 end)=1 )
and
(
(
emer_stateV = '留院觀察' and not (observed_bd is null or observed_ed is not null)
)
or
(
emer_stateV = '已分診'
and
(
(querytype=0 and not (emer_date < bd or emer_date > ed))
or
(
emer_state in('已分診','已通知','看診中')
and
(
case
when emer_state='已通知' and notice_date>dateadd(n,-30,getdate()) then 1
when emer_state='已分診' and not( emer_date < bd or emer_date > ed) then 1
when emer_state='看診中' then 1
else 0
end)=1
)
)
)
or
(
emer_state = '已就診' and emer_state='已就診'
and
(
(case
when querytype=1 and (doctor_sn=emp_sn and emer_date>convert(varchar(10),getdate()-1,121)) then 1
when querytype=0 then 1
else 0
end)=1
)
)
or
(a.emer_state = emer_stateV)
)
) t
where
(
(case when querytype=0 and emer_state='已就診' and not(emer_date between bd and ed) then 0
else 1
end )=1
)
order by emer_date ;
end GetEmergencyList_test;
--SELECT TO_CHAR( sysdate,'YYYY-MM-DD') FROM dual;
這些地方報錯，不可以用 變數名=常量的語法嗎？
uj5u.com熱心網友回復：
emer_stateV = '留院觀察' and not (observed_bd is null or observed_ed is not null)這些語法的都報錯
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/209966.html
標籤：基礎和管理
上一篇：求助優化存盤程序