本文共 6705 字，大约阅读时间需要 22 分钟。

CREATE DEFINER=`dur_dev`@`%` PROCEDURE `prescriptionDrugStatistics`()BEGIN	DECLARE code CHAR(5) DEFAULT '00000';  DECLARE msg TEXT;  DECLARE rows INT;  DECLARE result TEXT;		-- 声明异常处理  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    BEGIN      -- 获取异常code,异常信息      GET DIAGNOSTICS CONDITION 1        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;    END;	SET @whereStr = CONCAT(' group by tp.HosCode, tp.PrescriptionSource , tp.DeptCode, DATE_FORMAT(tp.PrescriptionTime,"%Y-%m-%d") 	');		-- 抗菌药物相关数据统计	SET @AntiBacter = CONCAT('SELECT 	DATE_FORMAT(tp.PrescriptionTime,"%Y-%m-%d") as prescriptionTime,	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	count(DISTINCT(tp.JZTClaimNo)) as preOfAntiBacter, 	count(DISTINCT(tp.ClientCardCode)) as patOfAntiBacter   FROM t_prescription_drugs tpd   left join t_prescription tp   on tpd.JZTClaimNo = tp.JZTClaimNo 	where tpd.IsAntiBacter = 1 ', @whereStr);		-- 基药相关数据统计	SET @Basic = CONCAT('SELECT 	DATE_FORMAT(tp.PrescriptionTime,"%Y-%m-%d") as prescriptionTime,	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	count(DISTINCT(tpd.DrugCode)) as drugOfBasic,	count(DISTINCT(tp.JZTClaimNo)) as preOfBasic, 	count(DISTINCT(tp.ClientCardCode)) as patOfBasic   FROM t_prescription_drugs tpd   left join t_prescription tp 	on tpd.JZTClaimNo = tp.JZTClaimNo 	where tpd.IsBasic = 1 ', @whereStr);		-- 注射药相关数据统计	SET @Injection = CONCAT('SELECT 	DATE_FORMAT(tp.PrescriptionTime,"%Y-%m-%d") as prescriptionTime,	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	count(DISTINCT(tpd.DrugCode)) as drugOfInjection,	count(DISTINCT(tp.JZTClaimNo)) as preOfInjection, 	count(DISTINCT(tp.ClientCardCode)) as patOfInjection   FROM t_prescription_drugs tpd   left join t_prescription tp 	on tpd.JZTClaimNo = tp.JZTClaimNo 	where tpd.IsInjection = 1 ', @whereStr);	-- 处方信息	SET @Prescription = CONCAT('SELECT 	DATE_FORMAT(tp.PrescriptionTime,"%Y-%m-%d") as prescriptionTime,	tp.HosCode as hosCode, 	tp.PrescriptionSource as prescriptionSource, 	tp.DeptCode as deptCode, 	tp.HosName as hosName, 	tp.DeptName as deptName,  IFNULL(count(DISTINCT(tpd.DrugCode)),0) as totalOfDrug,		IFNULL(count(DISTINCT(tp.JZTClaimNo)),0) as totalOfPrescription,  IFNULL(count(DISTINCT(tp.ClientCardCode)),0) as totalOfPatient	from t_prescription_drugs tpd 	left join t_prescription tp 	on tpd.JZTClaimNo = tp.JZTClaimNo 	where  tp.prescriptionTime is not null ', @whereStr);		-- 创建抗菌药信息零时表	DROP TABLE if EXISTS tmp_AntiBacter;	CREATE TEMPORARY TABLE tmp_AntiBacter(    		prescriptionTime VARCHAR(128),		hosCode VARCHAR(128),		prescriptionSource VARCHAR(128),		deptCode VARCHAR(128),		preOfAntiBacter bigint,		patOfAntiBacter bigint	);		-- 创建基药信息零时表	DROP TABLE if EXISTS tmp_Basic;	CREATE TEMPORARY TABLE tmp_Basic(     		prescriptionTime VARCHAR(128),		hosCode VARCHAR(128),		prescriptionSource VARCHAR(128),		deptCode VARCHAR(128),		drugOfBasic bigint,		preOfBasic bigint,		patOfBasic bigint	);		-- 创建注射药信息临时表	DROP TABLE if EXISTS tmp_Injection;	CREATE TEMPORARY TABLE tmp_Injection(    		prescriptionTime VARCHAR(128),		hosCode VARCHAR(128),		prescriptionSource VARCHAR(128),		deptCode VARCHAR(128),		drugOfInjection bigint,		preOfInjection bigint,		patOfInjection bigint	);		-- 创建处方信息零时表	DROP TABLE if EXISTS tmp_Prescription;	CREATE TEMPORARY TABLE tmp_Prescription(    		prescriptionTime VARCHAR(128),		hosCode VARCHAR(128),		prescriptionSource VARCHAR(128),		deptCode VARCHAR(128),		hosName VARCHAR(128),		deptName VARCHAR(128),		totalOfDrug bigint,		totalOfPrescription bigint,		totalOfPatient bigint	);	  set @AntiBacterSql = CONCAT('insert into tmp_AntiBacter (prescriptionTime, hosCode, prescriptionSource, deptCode, preOfAntiBacter,patOfAntiBacter)',  @AntiBacter);		PREPARE AntiBacterSql FROM @AntiBacterSql;#定义预处理语句 	EXECUTE AntiBacterSql;							#执行预处理语句 	DEALLOCATE PREPARE AntiBacterSql;	#删除定义 		set @BasicSql = CONCAT('insert into tmp_Basic (prescriptionTime, hosCode, prescriptionSource, deptCode, drugOfBasic, preOfBasic, patOfBasic)',  @Basic);		PREPARE BasicSql FROM @BasicSql;#定义预处理语句 	EXECUTE BasicSql;							#执行预处理语句 	DEALLOCATE PREPARE BasicSql;	#删除定义 		set @InjectionSql = CONCAT('insert into tmp_Injection (prescriptionTime, hosCode, prescriptionSource, deptCode, drugOfInjection, preOfInjection, patOfInjection)',  @Injection);		PREPARE InjectionSql FROM @InjectionSql;#定义预处理语句 	EXECUTE InjectionSql;							#执行预处理语句 	DEALLOCATE PREPARE InjectionSql;	#删除定义 		set @PrescriptionSql = CONCAT('insert into tmp_Prescription (prescriptionTime, hosCode, prescriptionSource, deptCode, hosName, deptName, totalOfDrug, totalOfPrescription, totalOfPatient)',  @Prescription);		PREPARE PrescriptionSql FROM @PrescriptionSql;#定义预处理语句 	EXECUTE PrescriptionSql;							#执行预处理语句 	DEALLOCATE PREPARE PrescriptionSql;	#删除定义 			SET @Str = CONCAT(	'SELECT x.*, 	  IFNULL(y.preOfAntiBacter, 0) as preOfAntiBacter, 		IFNULL(y.patOfAntiBacter, 0) as patOfAntiBacter, 		IFNULL(z.drugOfBasic, 0) as drugOfBasic,		IFNULL(z.preOfBasic, 0) as preOfBasic, 		IFNULL(z.patOfBasic, 0) as patOfBasic, 		IFNULL(w.drugOfInjection, 0) as drugOfInjection,  		IFNULL(w.preOfInjection, 0) as preOfInjection,  		IFNULL(w.patOfInjection, 0) as patOfInjection		FROM tmp_Prescription x 		left join tmp_AntiBacter y 		on x.hosCode = y.hosCode and x.prescriptionSource = y.prescriptionSource and x.deptCode = y.deptCode and x.prescriptionTime = y.prescriptionTime		left join tmp_Basic z 		on x.hosCode = z.hosCode and x.prescriptionSource = z.prescriptionSource and x.deptCode = z.deptCode and x.prescriptionTime = z.prescriptionTime		left join tmp_Injection w 		on x.hosCode = w.hosCode and x.prescriptionSource = w.prescriptionSource and x.deptCode = w.deptCode and x.prescriptionTime = w.prescriptionTime 		');		 set @StatisticsSql = CONCAT('insert into t_prescription_drugs_statistics (prescriptionTime, hosCode, prescriptionSource, deptCode, hosName, deptName, totalOfDrug, totalOfPrescription, totalOfPatient, preOfAntiBacter, patOfAntiBacter, drugOfBasic,preOfBasic, patOfBasic, drugOfInjection, preOfInjection, patOfInjection)', @Str);    START TRANSACTION; 			  TRUNCATE TABLE t_prescription_drugs_statistics;										PREPARE StatisticsSql FROM @StatisticsSql;#定义预处理语句 				EXECUTE StatisticsSql;#执行预处理语句 				DEALLOCATE PREPARE StatisticsSql;	#删除定义 		  -- 检查code是否改变，如果改变表示插入异常了		IF code = '00000' THEN			GET DIAGNOSTICS rows = ROW_COUNT;			SET result = CONCAT('insert succeeded, row count = ',rows);		  COMMIT; 		ELSE			-- 复制异常code，异常信息			SET result = CONCAT('insert failed, error = ',code,', message = ',msg);			ROLLBACK;   		END IF;				    SELECT result;   -- 返回标识位的结果集；		 END

转载地址：http://switi.baihongyu.com/