！求高人帮助MSSQL过程转换成ORALCE过程

tak 2006-05-15 03:45:10

create procedure tran_Add_REGISTER_in

as
declare @patientID char(10)
declare @registerID char(12)
declare @inpatientid varchar(12)
declare @inpatientdate datetime
declare @deptid char(10)
declare @areaid char(10)
declare @bedid char(10)
DECLARE @times tinyint

declare @transferID char(12)
declare @targetdeptID char(18)
DECLARE @targetareaID char(18)
declare @targetbedID char(18)

declare @bedCode char(10)
declare @bed_sex tinyint
declare @category varchar(10)

declare @leaveID char(12)
declare @leave_type varchar(20)
declare @outDate datetime

declare @e_deptid char(10)
declare @e_areaid char(10)
declare @e_roomid char(10)
declare @e_bedid char(10)
declare @pstatus tinyint --(1-in,3-out)
declare @inbedenddate datetime
declare @sex varchar(10)

declare @t_deptid char(10)
declare @t_areaid char(10)
declare @t_bedid char(10)

DECLARE @count int
DECLARE @t_count int

set @count = 200
set @t_count = 200

declare cur1222 cursor for
select DISTINCT rtrim(a.InPatientID) as InPatientID,a.InPatientDate,rtrim(a.InDeptID) as

InDeptID,rtrim(a.Area_ID) as Area_ID,rtrim(a.Room_ID),
rtrim(a.Bed_ID) as Bed_ID,a.InBedEndDate as InBedEndDate from InDeptInfo a inner join

InPatientDateInfo b
on a.InPatientID = b.InPatientID and a.InPatientDate = b.InPatientDate where

a.InBedEndDate='1900-1-1' --a.InPatientID ='D688302'
order by InPatientID,InBedEndDate
open cur1222
fetch next from cur1222 into @inpatientid,@inpatientdate,@e_deptid,@e_areaid,@e_roomid,@e_bedid,@inbedenddate
while(@@fetch_status=0)
begin

select @deptid=DEPTID_CHR from T_BSE_DEPTDESC where rtrim(CODE_VCHR) = @e_deptid

select @areaid=DEPTID_CHR from T_BSE_DEPTDESC where rtrim(CODE_VCHR) = @e_areaid

select @bedCode=Bed_Name from InPatient_Bed_Desc where Bed_ID=@e_bedid

select @bedid=BEDID_CHR from T_BSE_BED t where t.code_chr = bedCode and t.areaid_chr = @areaid

if(@bedid is not null) begin
set @pstatus = '1'
end
else
set @pstatus = '0'

select @sex=Sex,@times=Times from patientbaseinfo where InPatientID=@inpatientid
if(@sex is not null)
begin
if(rtrim(@sex) = '男')
set @bed_sex='1'
else
set @bed_sex='2'
end
if(@times is null)
set @times='1'

select @registerID=REGISTERID_CHR from t_opr_bih_register where INPATIENTID_CHR = @inpatientid and INPATIENT_DAT =

@inpatientdate
----------------------------------------------------------------------
if(@registerID is not null)
begin
select top 1 @t_deptid=DEPTID_CHR,@t_areaid=AREAID_CHR,@t_bedid=BEDID_CHR from T_OPR_BIH_REGISTER
where REGISTERID_CHR = @registerID --order by MODIFY_DAT desc
if(rtrim(@t_deptid) <> rtrim(@deptid) or rtrim(@t_areaid) <>rtrim(@areaid) or rtrim(@t_bedid)

<>rtrim(@e_bedid))
begin
update T_OPR_BIH_REGISTER set

MODIFY_DAT=getdate(),PSTATUS_INT=@pstatus,DEPTID_CHR=@deptid,AREAID_CHR=@areaid, BEDID_CHR=@bedid
where REGISTERID_CHR=@registerID
select top 1 @transferID=TRANSFERID_CHR,@category=TYPE_INT from T_OPR_BIH_TRANSFER

where REGISTERID_CHR=@registerID order by MODIFY_DAT desc
if(@transferID is not null and @category=6)
update T_OPR_BIH_TRANSFER set

SOURCEDEPTID_CHR=@t_deptid,SOURCEAREAID_CHR=@t_areaid,SOURCEBEDID_CHR=@t_bedid,TARGETDEPTID_CHR=@deptid,TARGETAREAID_CHR=@are

aid,TARGETBEDID_CHR=@bedid,TYPE_INT='5',
MODIFY_DAT = getdate() where TRANSFERID_CHR=@transferID
else if(@transferID is not null and @category=5)
begin

select @transferID=max(cast(TRANSFERID_CHR as int))+1 from T_OPR_BIH_TRANSFER
insert into

T_OPR_BIH_TRANSFER(TRANSFERID_CHR,SOURCEDEPTID_CHR,SOURCEAREAID_CHR,SOURCEBEDID_CHR,TARGETDEPTID_CHR,TARGETAREAID_CHR,TARGETB

EDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)

values(@transferID,@t_deptid,@t_areaid,@t_bedid,@deptid,@areaid,@bedid,'5',@registerID,getdate())
end
delete from T_OPR_BIH_LEAVE where REGISTERID_CHR=@registerID
if(@inbedenddate <> '1900-1-1') begin
--set @t_count=@t_count+1
select @transferID=max(cast(TRANSFERID_CHR as int))+1 from T_OPR_BIH_TRANSFER
insert into

T_OPR_BIH_TRANSFER(TRANSFERID_CHR,SOURCEDEPTID_CHR,SOURCEAREAID_CHR,SOURCEBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@deptid,@areaid,@bedid,'6',@registerID,getdate())
select @leaveID=max(cast(LEAVEID_CHR as int))+1 from T_OPR_BIH_LEAVE

insert into

T_OPR_BIH_LEAVE(LEAVEID_CHR,REGISTERID_CHR,TYPE_INT,OUTDEPTID_CHR,OUTAREAID_CHR,STATUS_INT,MODIFY_DAT,OUTBEDID_CHR,PSTATUS_IN

T)

values(@leaveID,@registerID,'3',@deptid,@areaid,'1',@inbedenddate,@bedid,'1')
end

if(@bedid is not null)
begin
update T_BSE_BED set STATUS_INT = '2',SEX_INT=@bed_sex where BEDID_CHR=@bedid
end

end
else
begin
update T_OPR_BIH_REGISTER set MODIFY_DAT=getdate(),PSTATUS_INT=@pstatus where

REGISTERID_CHR=@registerID
delete from T_OPR_BIH_LEAVE where REGISTERID_CHR=@registerID
end

end
else
-------------------------------------------------------------
begin
select @registerID= right('000000000000'+rtrim(max(cast(REGISTERID_CHR as int))+1),12) from

T_OPR_BIH_REGISTER
select @patientID=PATIENTID_CHR from T_BSE_PATIENT where INPATIENTID_CHR = @inpatientid
if(@patientID is null)
return

begin
insert into

T_OPR_BIH_REGISTER(REGISTERID_CHR,MODIFY_DAT,PATIENTID_CHR,INPATIENTID_CHR,INPATIENT_DAT,DEPTID_CHR,AREAID_CHR,BEDID_CHR,TYPE

_INT,INPATIENTCOUNT_INT,STATE_INT,STATUS_INT,PSTATUS_INT,INAREADATE_DAT)

values(@registerID,getdate(),@patientID,rtrim(@inpatientid),@inpatientdate,@deptid,@areaid,@bedid,'1',@times,'3','1','1',@inp

atientdate)
select @transferID=right('000000000000'+rtrim(max(cast(TRANSFERID_CHR as int))+1),12) from

T_OPR_BIH_TRANSFER

insert into

T_OPR_BIH_TRANSFER(TRANSFERID_CHR,TARGETDEPTID_CHR,TARGETAREAID_CHR,TARGETBEDID_CHR,TYPE_INT,REGISTERID_CHR,MODIFY_DAT)
values(@transferID,@deptid,@areaid,@bedid,'5',@registerID,getdate())

if(@bedid is not null)
begin
update T_BSE_BED set STATUS_INT = '2',SEX_INT=@bed_sex where BEDID_CHR=@bedid
end
end
end
--set @count = @count+1
--set @t_count=@t_count+1
fetch next from cur1222 into @inpatientid,@inpatientdate,@e_deptid,@e_areaid,@e_roomid,@e_bedid,@outDate
end
close cur1222
deallocate cur1222

...全文