Oracle数据库入门练习题参考答案(2)

请将病人的两次治疗记录分别记录在dw.patient_script_detl和dw.patient_script_genl中，然后进行结算（fyid、jshid和fyfsrq自行确定），最终要求显示结算结果。

insert into dw.patient_script_detl(fyid,sxh,ylxmbm,sl,dj,zje,bxje,zfje) values ('20130411001',1,'200005',1,15,1*15,12,3);

insert into dw.patient_script_detl(fyid,sxh,ylxmbm,sl,dj,zje,bxje,zfje) values('20130411001',2,'200008',1,150,1*150,1*150*0.8,1*150*0.2); ………..

insert into dw.patient_script_genl (fyid,fyfsrq,ysbm,ksbm,grbm,zje,bxje,zfje) select '20130411001',to_date(to_char(sysdate,'yyyy.mm.dd') ,'yyyy.mm.dd'), '015','0001','610122193305225413',sum(zje),sum(bxje),sum(zfje)

from dw.patient_script_detl where fyid='20130411001'; commit;

第二次：。。。。。。。。 update dw.patient_script_genl set jshid='1177'

where grbm='610122193305225413' and jshid is null; insert into dw.patient_settle(jshid,zje,bxje,zfje,grbm)

select '1177',sum(zje),sum(bxje),sum(zfje),'610122193305225413' from dw.patient_script_genl

where grbm='610122193305225413' and jshid =’1177’; commit;

注意：一旦有SQL执行出现问题，需要rollback

6