oracle 存储过程 转换为mysql存储过程 实例总结 (数据库有感篇
最近在接触数据库的有关知识,因为水平有限,对数据库方面的一些知识缺乏了解,这次遇见的主要是 数据库的存储过程,根据公司项目需求,将oracle的存储过程切换为mysql的存储过程,首先oracle的存储过程与mysql的存储过程差别是很大的(语法差别很大),通过好多天的网上查询资料,与同事们的交流,简单的实现了基本的转换,在这里肯定还有一些错误的地方,也欢迎各位大牛指出,让我更加深入的了解 数据库的存储过程,有疑问的大家也可以一起交流进步。 下面就进入这次的话题:oracle 存储过程 转换为mysql存储过程 实例总结
首先我门先看一下我最近大致转换的存储过程的一个例子代码,首先是原始的 oracle的存储过程:
CREATE OR REPLACE PROCEDURE xp_stat_BigDiseaseAnalyse( v_beginDate varchar2,--统计开始日期,格式:yyyyMMdd v_endDate varchar2,--统计结束日期,格式:yyyyMMdd v_orgCode varchar2,--检查医疗机构 v_dataCur out PK_base.cur,--返回结果集 v_total out number,--返回查询记录总数 v_msg out varchar2 --返回查询记录总数 ) is v_sql varchar2(5000); begin v_sql := 'select * from ( select max(ehr.forg_code) forg_code,''冠心病'' fdiag_name,count(a.fehrid) FDiseaseCount,sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%冠心病%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''椎基底供血不足'' fdiag_name,count(a.fehrid) FDiseaseCount,sum(famount) famount,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%椎基底供血不足%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''糖尿病'' fdiag_name,sum (famount) famount,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%糖尿病%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''脑出血'' fdiag_name,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%脑出血%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''无指征剖腹产分娩'' fdiag_name,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%无指征剖腹产分娩%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''肺炎'' fdiag_name,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%肺炎%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''肺恶性肿瘤'' fdiag_name,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%肺恶性肿瘤%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''高血压'' fdiag_name,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%高血压%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; end if; v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,''乳腺恶性肿瘤'' fdiag_name,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%乳腺恶性肿瘤%'' and ehr.fehr_date >= ''20160301'' and ehr.fehr_date <= ''' || v_endDate || ''' '; if v_orgCode is not null then v_sql := v_sql || ' and ehr.forg_code =''' || v_orgCode || ''' ) tt where tt.FDiseaseCount>0 order by tt.FDiseaseCount desc'; end if; open v_dataCur for v_sql; exception when others then pk_base.xp_saveLog(sqlcode,sqlerrm,'pk_stat.xp_stat_BigDiseaseAnalyse','大病补偿统计出错'); open v_dataCur for select * from dual where 1 = 2; end;
DROP PROCEDURE IF EXISTS xp_stat_BigDiseaseAnalyse; CREATE PROCEDURE xp_stat_BigDiseaseAnalyse( v_beginDate varchar(100),-- 统计开始日期,格式:yyyyMMdd v_endDate varchar(100),-- 统计结束日期,格式:yyyyMMdd v_orgCode varchar(50),-- 检查医疗机构 -- v_dataCur PK_base.cur,--返回结果集 out v_total int,-- 返回查询记录总数 out v_msg varchar(100) -- 返回查询记录总数 ) begin DECLARE<span style="white-space:pre"> </span>v_sql varchar(5000); DECLARE iPos int; DECLARE iCount int; DECLARE v_sqltmp varchar(420); DECLARE CONTINUE HANDLER FOR SQLEXCEPTION begin CALL xp_saveLog(@sqlcode,@sqlerrm,'pk_him.xp_stat_BigDiseaseAnalyse','大病补偿统计出错'); COMMIT; end; -- =================================================== 异常处理结束 SET v_sql = CONCAT(' select * from (select max(ehr.forg_code) forg_code,SUM(famount) famount,SUM(famount_insurance) famount_insurance,SUM(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%冠心病%'' and ehr.fehr_date >= ''',v_endDate,''''); SET v_sql = CONCAT(v_sql,v_beginDate,' '' and ehr.fehr_date <= ''',''''); if (v_orgCode is not null and v_orgCode !='' ) then -- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || ''''; SET v_sql = CONCAT(v_sql,' and ehr.forg_code = ''',v_orgCode,''''); end if; -- =================================================脑出血======================================================= SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%脑出血%'' and ehr.fehr_date >=''',''''); SET v_sql = CONCAT(v_sql,v_sqltmp,''''); end if; -- ==========================================椎基底供血不足============================================================== SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,count(a.fehrid)FDiseaseCount,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%椎基底供血不足%'' and ehr.fehr_date >=''',''''); end if; -- ========================================糖尿病================================================================ SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%糖尿病%'' and ehr.fehr_date >=''',''' '); SET v_sql = CONCAT(v_sql,''''); end if; -- ======================================================================================================== SET v_sqltmp = CONCAT('union select max(ehr.forg_code) forg_code,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%无指征剖腹产分娩%'' and ehr.fehr_date >= ''',''''); end if; -- =======================================================肺炎================================================= SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%肺炎%'' and ehr.fehr_date >=''',''''); end if; -- ===============================================肺恶性肿瘤========================================================= SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%肺恶性肿瘤%'' and ehr.fehr_date >=''',''''); end if; -- ================================================高血压======================================================== SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%高血压%'' and ehr.fehr_date >=''',''''); end if; -- ============================================乳腺恶性肿瘤============================================================ SET v_sqltmp = CONCAT(' union select max(ehr.forg_code) forg_code,count(a.fehrid) FDiseaseCount,sum(famount_self) famount_self from tbl_ehr_comm_disease a join tbl_ehr_archives ehr on ehr.fid=a.fehrid join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid where ehr.fdata_type=''3001'' and a.fdiag_name like ''%乳腺恶性肿瘤%'' and ehr.fehr_date >=''',''' '); SET v_sql = CONCAT(v_sql,''''); if (v_orgCode is not null and v_orgCode !='' ) then SET v_sql = CONCAT(v_sql,' '' '); end if; SET v_sql = CONCAT(v_sql,' ) tt order by tt.FDiseaseCount desc'); -- where tt.FDiseaseCount>0 -- 创建临时表 create temporary table if not exists tmpTab( FID int not null auto_increment,forg_code varchar(20),fdiag_name varchar(50),fDiseaseCount decimal(19,0),fAmount decimal(19,2),famount_insurance decimal(19,famount_self decimal(19,primary key (FID) ) ; truncate TABLE tmpTab; -- SET v_sql = 'insert into tmpTab(forg_code,fdiag_name,FDiseaseCount,famount,famount_insurance,famount_self) ' || v_sql; SET v_sql = CONCAT('insert into tmpTab(forg_code,famount_self) ',v_sql); set @s_sql = v_sql; prepare stmt from @s_sql; execute stmt; SELECT * from tmpTab; end 通过大致阅读上面的代码,我们可以发现,不同的地方有很多,不单单从字面上,从逻辑上也有很多不同之处。 为了更加直观的说明不同之处,下面列出一个表格来大致说明:
|