1.ORACLE
create or replace procedure proc_test (v_pat_no in varchar2,
cv_1 out sys_refcursor) as
v_patient_id varchar2(12);
v_times number;
tempsize number;
begin
select count(1) into tempsize
from zy_actpatient
where inpatient_no = v_pat_no
and substr(patient_id, 12, 1) = '0'
and nvl(visit_type, '0') <> 'XX';
if tempsize >0 then
select patient_id, admiss_times
into v_patient_id, v_times
from zy_actpatient
where inpatient_no = v_pat_no;
else
select '',''into v_patient_id, v_times from dual;
end if;
open cv_1 for
select a.* from a ;
end;
=========================================
2. sql sever 版本
create procedure proc_test_a
@v_pat_no varchar(20)
as
declare
@v_patient_id varchar(12),
@v_times float,
@tempsize float
begin
select @tempsize =count(1)
from zy_actpatient
where inpatient_no = @v_pat_no
and substring(patient_id, 12, 1) = '0'
and isnull(visit_type, '0') <> 'XX'
if @tempsize >0
select @v_patient_id = patient_id, @v_times=admiss_times from zy_actpatient where inpatient_no = @v_pat_no
and substring(patient_id, 12, 1) = '0' and isnull(visit_type, '0') <> 'XX'
else
select @v_patient_id='',@v_times=''
select a.* from a where a.admiss_times = @v_times
end
从上面的1与2可以得到Oracle与sql sever写存过的区别是：：
=============================================================
3.Oracle
create or replace procedure p_hld(v_ward in varchar2,
cv_1 out sys_refcursor)
as
begin
insert into hld_general_1 (enc_id,hlcg)
select enc_id,DISPLAY_NAME
from general_order
where serial = '**' and DISPLAY_NAME like '%护理常规%' and order_status in ('2','3','4') and input_type = 'd'
and ward_sn = v_ward;
insert into hld_general_1 (enc_id,fjhl)
select enc_id,DISPLAY_NAME
from general_order
where serial = '**' and DISPLAY_NAME like '%级护理%' and order_status in ('2','3','4') and input_type = 'd'
and ward_sn = v_ward;
open cv_1 for
select distinct b.bed_no,b.name, enc_id,
(select to_char(wm_concat(hlcg)) From hld_general_1 b where b.enc_id = a.enc_id) hlcg ,
(select to_char(wm_concat(fjhl)) From hld_general_1 b where b.enc_id = a.enc_id) fjhl,
(select name from zd_unit_code where code =v_ward) 科室
from hld_general_1 a,cisdb_dev.patient_encounter b
where b.code = 'inp' and b.status_code = 'active'
and b.ward = v_ward
and a.enc_id = b.patient_encounter_id
order by b.bed_no;
delete hld_general_1;
end;
从这个图片可以看到上面insert的结果，主要是用了一个distinct就把数据显示在一起了。
===============================================
4.0 sql sever
CREATE procedure [dbo].[p_hld](@ward varchar(7))
as
begin
insert into hld_general_1 (enc_id,hlcg)
select enc_id,display_name
from general_order
where serial = '**' and display_name like '%护理常规%' and order_status in ('2','3','4') and input_type = 'd'
and stop_time is null and ward_sn = @ward;
insert into hld_general_1 (enc_id,fjhl)
select enc_id,display_name
from general_order
where serial = '**' and display_name like '%级护理%' and order_status in ('2','3','4') and input_type = 'd'
and stop_time is null and ward_sn = @ward;
insert into hld_general_1 (enc_id,ys)
select enc_id,display_name
from general_order where serial = '**' and display_name like '%饮食%' and order_status in ('2','3','4') and input_type = 'd'
and stop_time is null and ward_sn = @ward;
insert into hld_general_1 (enc_id,jh)
select enc_id,display_name + freq_code as display_name from general_order
where code in ('516245','500381','500382','516330','516317','516251') and order_status in ('2','3','4')
and stop_time is null and ward_sn = @ward;
insert into hld_general_1 (enc_id,xy)
select enc_id,display_name from general_order where serial = '**' and (display_name like '%吸氧%' or display_name like '%氧气%' )and order_status in ('2','3','4') and input_type = 'd'
and stop_time is null and ward_sn = @ward;
insert into hld_general_1 (enc_id,qt)
select enc_id, display_name from general_order where code not in ('516245','500381','500382','516330','516246','999999','516090','516251','516317') and serial = '**' and display_name not like '%护理常规%'
and display_name not like '%级护理%' and display_name not like '%饮食%' and display_name not like '%吸氧%' and order_type not in('1', '2')
and order_status in ('2','3','4') and input_type = 'd'
and stop_time is null and ward_sn = @ward;
select distinct b.bed_no,b.name, enc_id,
( STUFF(( SELECT ',' + hlcg FROM hld_general_1 hlcg WHERE hlcg.enc_id = a.enc_id FOR XML PATH('') ), 1, 1, '') ) hlcg ,
( STUFF(( SELECT ',' + fjhl FROM hld_general_1 fjhl WHERE fjhl.enc_id = a.enc_id FOR XML PATH('') ), 1, 1, '') ) fjhl,
( STUFF(( SELECT ',' + ys FROM hld_general_1 ys WHERE ys.enc_id = a.enc_id FOR XML PATH('') ), 1, 1, '') ) ys,
( STUFF(( SELECT ',' + jh FROM hld_general_1 jh WHERE jh.enc_id = a.enc_id FOR XML PATH('') ), 1, 1, '') ) jh,
( STUFF(( SELECT ',' + xy FROM hld_general_1 xy WHERE xy.enc_id = a.enc_id FOR XML PATH('') ), 1, 1, '') ) xy,
( STUFF(( SELECT ',' + qt FROM hld_general_1 qt WHERE qt.enc_id = a.enc_id FOR XML PATH('') ), 1, 1, '') ) qt,
(select name from zd_unit_code where code =@ward) 科室
from hld_general_1 a,cisdb_dev.dbo.patient_encounter b
where b.code = 'inp' and b.status_code = 'active'
and b.ward = @ward
and a.enc_id = b.patient_encounter_id
order by b.bed_no;
-- select name INTO @warnname from zd_unit_code where code =@ward;
delete hld_general_1;
-- commit;
end;
GO
上面的主要提出的知识点：
1.往临时表里面写数据，而且还是分次写入用到语句是
insert into hld_general_1 (enc_id,hlcg)
select enc_id,DISPLAY_NAME from general_order
where DISPLAY_NAME like '%护理常规%'
下面的表后面跟上字段，代表往这个表的具体哪个字段写入数据
hld_general_1 (enc_id,hlcg)