一个简单的存储过程
来源:互联网 发布:a站程序员 编辑:程序博客网 时间:2024/05/17 06:07
create or replace PROCEDURE yy_gh_insert AS
cursor registration_yb is
select r.id, r.mrn as mrn, c.schid as schid, r.timepart as timepart
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd');
t_row registration_yb%rowtype;
v_regid varchar2(40); --挂号id
v_invoiceid varchar2(40); --挂号id
v_hosnum VARCHAR2(10); --医院编号
v_mrn varchar2(60); --病人标示号
v_name VARCHAR2(60); --病人名称
v_regsex varchar2(20); --病人性别
v_regage NUMBER(10); --年龄
v_clctimes NUMBER(10); --就诊次数
v_schid varchar2(60); --排班编号
v_datereg DATE; --挂号时间
v_regno NUMBER(5); --挂号序号
v_clcdate DATE; --门诊日期
v_clctime VARCHAR2(10); --时间类别
v_empid varchar2(60);
begin
FOR t_row in registration_yb LOOP
select sys_guid() into v_regid from dual; --生成regid
select sys_guid() into v_invoiceid from dual; --收费id
--修改排班表
if t_row.timepart = 1 then
update clc_sch
set m_currentnum =
(select m_currentnum + 1 as m_currentnum
from clc_sch
where schid = t_row.schid)
where schid = t_row.schid;
else
update clc_sch
set a_currentnum =
(select a_currentnum + 1 as a_currentnum
from clc_sch
where schid = t_row.schid)
where schid = t_row.schid;
end if;
--修改就诊次数
update bas_patients t
set t.clctimes = t.clctimes + 1
where t.patientid = t_row.mrn;
select c.hosnum,
r.mrn,
r.name,
(select p.sex from BAS_PATIENTS p where p.patientid = r.mrn) as regsex,
(select floor(months_between(SYSDATE,
to_date(to_char(p.dateofbirth,
'yyyy-mm-dd'),
'yyyy-mm-dd')) / 12) regage
from BAS_PATIENTS p
where p.patientid = r.mrn) as regage,
(select p.CLCTIMES from BAS_PATIENTS p where p.patientid = r.mrn) as CLCTIMES,
c.schid as schid,
sysdate as datereg,
decode(r.timePart,
'1',
(select t.a_currentnum
from clc_sch t
where t.schid = c.schid),
(select t.m_currentnum
from clc_sch t
where t.schid = c.schid)) as regno,
trunc(sysdate + 1) + 8 / 24 as clcdate,
decode(r.timePart, '1', 'M', 'A') as CLCTIME,
r.empid
into v_hosnum,
v_mrn,
v_name,
v_regsex,
v_regage,
v_clctimes,
v_schid,
v_datereg,
v_regno,
v_clcdate,
v_clctime,
v_empid
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
--插入挂号表
insert into clc_reg_temp
(hosnum,
regid,
patientid,
regpatname,
regsex,
regage,
clctimes,
instype,
discount,
schid,
datereg,
regno,
clcdate,
clctime,
operator,
operatorname,
freereg,
invoiceid,
refund,
relationid,
accounttype,
accountno,
accountdiag,
accountunit,
nodecode)
select c.hosnum,
v_regid,
r.mrn,
r.name,
(select p.sex from BAS_PATIENTS p where p.patientid = r.mrn) as regsex,
(select floor(months_between(SYSDATE,
to_date(to_char(p.dateofbirth,
'yyyy-mm-dd'),
'yyyy-mm-dd')) / 12) regage
from BAS_PATIENTS p
where p.patientid = r.mrn) as regage,
(select p.CLCTIMES
from BAS_PATIENTS p
where p.patientid = r.mrn) as CLCTIMES,
'自费' as instype,
'' as discount,
c.schid as schid,
sysdate as datereg,
decode(r.timePart,
'1',
(select t.a_currentnum
from clc_sch t
where t.schid = c.schid),
(select t.m_currentnum
from clc_sch t
where t.schid = c.schid)) as regno,
trunc(sysdate - 1) + 8 / 24 as clcdate,
decode(r.timePart, '1', 'M', 'A') as CLCTIME,
r.empid as operator,
'医快付' as operatorname,
'Y' as freereg,
v_invoiceid as invoiceid,
null as refund,
null as relationid,
null as accounttype,
null as accountno,
null as accountdiag,
null as accountunit,
c.hosnum as nodecode
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
--收费发票表
insert into chg_invoice_temp
(hosnum,
invoiceid,
invoiceno,
chgdate,
clcorinp,
patientid,
instype,
billno,
discount,
diagname,
conditiondes,
totalcost,
selfcare,
selfcost,
payment,
relationno,
opayamt,
cashin,
change,
operator,
reportdate,
reportid,
regid,
dtmainid,
cancelflag,
totalcost_a,
banlance_oy,
banlance_ty,
selfdebt)
select c.hosnum as hosnum,
v_invoiceid as invoiceid,
null as invoiceno,
sysdate as chgdate,
'挂号' as clcorinp,
r.mrn as patientid,
'自费' as instype,
null as billno,
null as discount,
null as diagname,
null as conditiondes,
0.0 as totalcost,
0.0 as selfcare,
0.0 as selfcost,
null as payment,
null as relationno,
null as opayamt,
0.0 as cashin,
0.0 as change,
r.empid as operator,
null as reportdate,
null as reportid,
v_regid as regid,
null as dtmainid,
null as cancelflag,
0.0 as totalcost_a,
0.0 as banlance_oy,
0.0 as banlance_ty,
0.0 as selfdebt
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
--支付明细表
insert into chg_paydetails_temp
(PAYID,
HOSNUM,
INVOICEID,
PAYTYPE,
RSHEETNO,
AMT,
PAYUNIT,
PAYSHEETNO,
PAYDATE,
OPERATOR,
OPERATORNAME,
COMMENTS)
select sys_guid() as PAYID,
c.hosnum as hosnum,
v_invoiceid as INVOICEID,
'现金' as PAYTYPE,
null as RSHEETNO,
0.0 as AMT,
null as PAYUNIT,
null as PAYSHEETNO,
sysdate as PAYDATE,
r.empid as operator,
'医快付' as operatorname,
null as COMMENTS
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);
DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);
ROLLBACK;
end;
cursor registration_yb is
select r.id, r.mrn as mrn, c.schid as schid, r.timepart as timepart
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd');
t_row registration_yb%rowtype;
v_regid varchar2(40); --挂号id
v_invoiceid varchar2(40); --挂号id
v_hosnum VARCHAR2(10); --医院编号
v_mrn varchar2(60); --病人标示号
v_name VARCHAR2(60); --病人名称
v_regsex varchar2(20); --病人性别
v_regage NUMBER(10); --年龄
v_clctimes NUMBER(10); --就诊次数
v_schid varchar2(60); --排班编号
v_datereg DATE; --挂号时间
v_regno NUMBER(5); --挂号序号
v_clcdate DATE; --门诊日期
v_clctime VARCHAR2(10); --时间类别
v_empid varchar2(60);
begin
FOR t_row in registration_yb LOOP
select sys_guid() into v_regid from dual; --生成regid
select sys_guid() into v_invoiceid from dual; --收费id
--修改排班表
if t_row.timepart = 1 then
update clc_sch
set m_currentnum =
(select m_currentnum + 1 as m_currentnum
from clc_sch
where schid = t_row.schid)
where schid = t_row.schid;
else
update clc_sch
set a_currentnum =
(select a_currentnum + 1 as a_currentnum
from clc_sch
where schid = t_row.schid)
where schid = t_row.schid;
end if;
--修改就诊次数
update bas_patients t
set t.clctimes = t.clctimes + 1
where t.patientid = t_row.mrn;
select c.hosnum,
r.mrn,
r.name,
(select p.sex from BAS_PATIENTS p where p.patientid = r.mrn) as regsex,
(select floor(months_between(SYSDATE,
to_date(to_char(p.dateofbirth,
'yyyy-mm-dd'),
'yyyy-mm-dd')) / 12) regage
from BAS_PATIENTS p
where p.patientid = r.mrn) as regage,
(select p.CLCTIMES from BAS_PATIENTS p where p.patientid = r.mrn) as CLCTIMES,
c.schid as schid,
sysdate as datereg,
decode(r.timePart,
'1',
(select t.a_currentnum
from clc_sch t
where t.schid = c.schid),
(select t.m_currentnum
from clc_sch t
where t.schid = c.schid)) as regno,
trunc(sysdate + 1) + 8 / 24 as clcdate,
decode(r.timePart, '1', 'M', 'A') as CLCTIME,
r.empid
into v_hosnum,
v_mrn,
v_name,
v_regsex,
v_regage,
v_clctimes,
v_schid,
v_datereg,
v_regno,
v_clcdate,
v_clctime,
v_empid
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
--插入挂号表
insert into clc_reg_temp
(hosnum,
regid,
patientid,
regpatname,
regsex,
regage,
clctimes,
instype,
discount,
schid,
datereg,
regno,
clcdate,
clctime,
operator,
operatorname,
freereg,
invoiceid,
refund,
relationid,
accounttype,
accountno,
accountdiag,
accountunit,
nodecode)
select c.hosnum,
v_regid,
r.mrn,
r.name,
(select p.sex from BAS_PATIENTS p where p.patientid = r.mrn) as regsex,
(select floor(months_between(SYSDATE,
to_date(to_char(p.dateofbirth,
'yyyy-mm-dd'),
'yyyy-mm-dd')) / 12) regage
from BAS_PATIENTS p
where p.patientid = r.mrn) as regage,
(select p.CLCTIMES
from BAS_PATIENTS p
where p.patientid = r.mrn) as CLCTIMES,
'自费' as instype,
'' as discount,
c.schid as schid,
sysdate as datereg,
decode(r.timePart,
'1',
(select t.a_currentnum
from clc_sch t
where t.schid = c.schid),
(select t.m_currentnum
from clc_sch t
where t.schid = c.schid)) as regno,
trunc(sysdate - 1) + 8 / 24 as clcdate,
decode(r.timePart, '1', 'M', 'A') as CLCTIME,
r.empid as operator,
'医快付' as operatorname,
'Y' as freereg,
v_invoiceid as invoiceid,
null as refund,
null as relationid,
null as accounttype,
null as accountno,
null as accountdiag,
null as accountunit,
c.hosnum as nodecode
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
--收费发票表
insert into chg_invoice_temp
(hosnum,
invoiceid,
invoiceno,
chgdate,
clcorinp,
patientid,
instype,
billno,
discount,
diagname,
conditiondes,
totalcost,
selfcare,
selfcost,
payment,
relationno,
opayamt,
cashin,
change,
operator,
reportdate,
reportid,
regid,
dtmainid,
cancelflag,
totalcost_a,
banlance_oy,
banlance_ty,
selfdebt)
select c.hosnum as hosnum,
v_invoiceid as invoiceid,
null as invoiceno,
sysdate as chgdate,
'挂号' as clcorinp,
r.mrn as patientid,
'自费' as instype,
null as billno,
null as discount,
null as diagname,
null as conditiondes,
0.0 as totalcost,
0.0 as selfcare,
0.0 as selfcost,
null as payment,
null as relationno,
null as opayamt,
0.0 as cashin,
0.0 as change,
r.empid as operator,
null as reportdate,
null as reportid,
v_regid as regid,
null as dtmainid,
null as cancelflag,
0.0 as totalcost_a,
0.0 as banlance_oy,
0.0 as banlance_ty,
0.0 as selfdebt
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
--支付明细表
insert into chg_paydetails_temp
(PAYID,
HOSNUM,
INVOICEID,
PAYTYPE,
RSHEETNO,
AMT,
PAYUNIT,
PAYSHEETNO,
PAYDATE,
OPERATOR,
OPERATORNAME,
COMMENTS)
select sys_guid() as PAYID,
c.hosnum as hosnum,
v_invoiceid as INVOICEID,
'现金' as PAYTYPE,
null as RSHEETNO,
0.0 as AMT,
null as PAYUNIT,
null as PAYSHEETNO,
sysdate as PAYDATE,
r.empid as operator,
'医快付' as operatorname,
null as COMMENTS
from yy_registration r
left join CLC_SCH c
on r.schid = c.schid
where r.yy_date =
to_date(to_char(sysdate + 1, 'yyyy-mm-dd'), 'yyyy-mm-dd')
and r.id = t_row.id;
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlcode : ' || sqlcode);
DBMS_OUTPUT.put_line('sqlerrm : ' || sqlerrm);
ROLLBACK;
end;
阅读全文
0 0
- 一个简单的存储过程~
- 一个简单的存储过程
- 一个简单的存储过程
- 一个简单的存储过程
- 分页存储过程--一个简单的SQL存储过程
- oracle的一个简单的存储过程
- 一个简单的sql存储过程
- 一个简单的存储过程数据分页
- 一个简单的存储过程数据分页
- 一个简单的存储过程数据分页
- 一个简单的oracle存储过程
- 一个简单的MYSQL存储过程
- 一个简单的oracle存储过程
- 一个简单存储过程的性能分析
- 存储过程的一个简单实例
- 一个简单存储过程的测试
- 创建存储过程的一个简单代码
- 一个简单的oracle存储过程
- 屏下指纹识别夭折 明年所有新iPhone将支持Face ID
- 《C++ Primer Plus》Ch07笔记
- 第一个驱动程序(在开发板上运行)
- java.util.Date的转换
- 网上超市开发阶段的文档
- 一个简单的存储过程
- Leetcode-Unique Paths II(dp)
- C#回顾学习笔记四十一:在三层架构中使用事务
- 数据结构-二维数组-特殊矩阵压缩存储
- 第四篇:添加记录应用崩溃的modual
- Appium安装步骤
- JSP基础(八)——使用Cookie记录信息
- 害怕变砖?iOS 11发布后24小时升级率不如10和9!
- 第九届未来赢销峰会盛大开幕,新物种重磅来袭