通用：东软请求和返回日志表

配置表（请求参数、返回参数说明）

CREATE TABLE `neusoft_log_column_remark` (
  `request_type` varchar(255) NOT NULL COMMENT 'input请求参数，output输出参数',
  `data_type` varchar(255) NOT NULL COMMENT '0,读卡，1，结算',
  `column_index` int(8) NOT NULL COMMENT '序号',
  `remark` varchar(255) NOT NULL COMMENT '字段名称',
  PRIMARY KEY (`request_type`,`data_type`,`column_index`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 日志表（东软接口请求参数和返回参数）

CREATE TABLE `neusoft_log` (
  `Payment_Mode_Code` varchar(255) DEFAULT NULL COMMENT '支付方式编码',
  `Data_Type` varchar(255) DEFAULT NULL COMMENT '0,读卡，1，结算',
  `input1` text,
  `input2` text,
  `input3` text,
  `input4` text,
  `input5` text,
  `input6` text,
  `input7` text,
  `input8` text,
  `input9` text,
  `input10` text,
  `input11` text,
  `input12` text,
  `input13` text,
  `input14` text,
  `input15` text,
  `input16` text,
  `input17` text,
  `input18` text,
  `input19` text,
  `input20` text,
  `input21` text,
  `input22` text,
  `input23` text,
  `input24` text,
  `input25` text,
  `input26` text,
  `input27` text,
  `input28` text,
  `input29` text,
  `input30` text,
  `output1` text,
  `output2` text,
  `output3` text,
  `output4` text,
  `output5` text,
  `output6` text,
  `output7` text,
  `output8` text,
  `output9` text,
  `output10` text,
  `output11` text,
  `output12` text,
  `output13` text,
  `output14` text,
  `output15` text,
  `output16` text,
  `output17` text,
  `output18` text,
  `output19` text,
  `output20` text,
  `output21` text,
  `output22` text,
  `output23` text,
  `output24` text,
  `output25` text,
  `output26` text,
  `output27` text,
  `output28` text,
  `output29` text,
  `output30` text,
  `output31` text,
  `output32` text,
  `output33` text,
  `output34` text,
  `output35` text,
  `output36` text,
  `output37` text,
  `output38` text,
  `output39` text,
  `output40` text,
  `output41` text,
  `output42` text,
  `output43` text,
  `output44` text,
  `output45` text,
  `output46` text,
  `output47` text,
  `output48` text,
  `output49` text,
  `output50` text,
  `output51` text,
  `output52` text,
  `output53` text,
  `output54` text,
  `output55` text,
  `output56` text,
  `output57` text,
  `output58` text,
  `output59` text,
  `output60` text,
  `output61` text,
  `output62` text,
  `output63` text,
  `output64` text,
  `output65` text,
  `output66` text,
  `output67` text,
  `output68` text,
  `output69` text,
  `output70` text,
  `output71` text,
  `output72` text,
  `output73` text,
  `output74` text,
  `output75` text,
  `output76` text,
  `output77` text,
  `output78` text,
  `output79` text,
  `output80` text,
  `output81` text,
  `output82` text,
  `output83` text,
  `output84` text,
  `output85` text,
  `output86` text,
  `output87` text,
  `output88` text,
  `output89` text,
  `output90` text,
  `output91` text,
  `output92` text,
  `output93` text,
  `output94` text,
  `output95` text,
  `output96` text,
  `output97` text,
  `output98` text,
  `output99` text,
  `output100` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　

自定义函数（获取查询sql）

DELIMITER $$
create function GetSelect(datatype int)returns varchar(5000)
BEGIN
    -- 声明一个变量 使用declare 变量名 数据类型 默认值
    declare str varchar(5000) default ' select Data_Type,Payment_Mode_Code,'; 
		declare remark2 varchar(500) default '';		 
    -- 使用set 对变量进行赋值    		
		declare i int DEFAULT 1; 		 
		    while i <= 30 do
					 select Remark into remark2 from neusoft_log_column_remark where column_Index=i and Data_Type=datatype and request_type='input';	
					 if (remark2!='' and remark2 is not null  ) then  			
								set remark2=replace(remark2,'(','_');
								set remark2=replace(remark2,')','_');			
							 set str = CONCAT(str,'input',i , ' as in',remark2,'_',i,',');       							 				 
					 end if;	
					 set remark2='';	 
					 set i = i + 1;					 
     end while; 
		
		set i=1;
    while i <= 100 do
					 select Remark into remark2 from neusoft_log_column_remark where column_Index=i and Data_Type=datatype and request_type='output';	
				 	if (remark2!='' and remark2 is not null  ) then  		
								set remark2=replace(remark2,'(','_');
								set remark2=replace(remark2,')','_');								
					     set str = CONCAT(str,'output',i , ' as out',remark2,'_',i,','); 
					 end if;	 
					 set remark2='';	
					 set i = i + 1;		
     end while; 
		set str = CONCAT(str,' '''' from neusoft_log  where Data_Type=''',datatype,  ''' and  Payment_Mode_Code= '''' ' );  
		return str;				
end $$
DELIMITER ;

插入测试数据

INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1', 1, '住院号(门诊号)');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1', 2, '单据号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',3, '结算日期');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',4, '报销类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',5, '账户使用标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',6, '中途结算标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',7, '经办人');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',8, '是否保存处方标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',9, '备用');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',10, '报销预付款');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',11, '备用');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',12, '使用账户金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',1, '本次医疗费用');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',2, '本次帐户支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',3, '本次基本统筹支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',4, '本次定点医疗机构分担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',5, '本次大病（额）支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',6, '本次补充医疗支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',7, '本次现金支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',8, '本次离休统筹支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',9, '本次伤残军人补助支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',10, '起付标准');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',11, '基本统筹分段自付');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',12, '本次进入基本统筹金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',13, '自费总金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',14, '帐户余额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',15, '人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',16, '低保人员医院分担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',17, '低保标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',18, '公立医院补助标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',19, '公立医院住院补助金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',20, '门慢累计金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',21, '参保人所属统筹区编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',22, '按床日付费医院承担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',23, '单病种医院承担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',24, '保底报销医院承担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',25, '大病分段自付');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',26, '本次进入大病金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',27, '本次乙类先付金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',28, '本次民政医疗救助金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',29, '本次健康扶贫补充保险报销金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',30, '本次自费费用超比例医院分担');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',31, '民政医疗救助人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',32, '民政医疗救助对象属地');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',33, '单病种结余奖励金额');


INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',1, '个人编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',2, '单位编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',3, '身份证号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',4, '姓名');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',5, '性别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',6, '民族');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',7, '出生日期');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',8, '社会保障卡卡号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',9, '医疗人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',10, '医疗证号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',11, '人员状态');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',12, '参保状态');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',13, '社保卡密码');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',14, '行政职务');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',15, '原系统医保个人编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',16, '副县副团标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',17, '灵活就业人员本次医疗连续缴费月数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',18, '基金类型');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',19, '单位名称');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',20, '参保地');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',21, '单位类型');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',22, '特困企业标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',23, '比例类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',24, '年度');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',25, '上年结转金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',26, '本年个人缴费部分本金');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',27, '本年单位缴费划拨部分本金');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',28, '本年补充医疗注入');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',29, '本年企业补充医疗保险注入');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',30, '本年机关补充医疗保险注入');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',31, '继承金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',32, '本年利息增加额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',33, '历年利息增加额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',34, '截止上年末基本医疗累计缴费月数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',35, '基本医疗本年缴费月数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',36, '帐户余额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',37, '在院状态');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',38, '本年医疗费累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',39, '本年现金支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',40, '本年帐户支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',41, '本年统筹支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',42, '本年补充医疗支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',43, '本年大额（病）支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',44, '本年离休统筹支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',45, '本年伤残军人补助支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',46, '本年住院次数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',47, '本年进入分段统筹费用累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',48, '本年异地安置住院费用累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',49, '本年超大额统筹个人自付累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',50, '本年门诊特检特治结算次数累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',51, '月结单类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',52, '公立医院补助资格标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',53, '门诊慢性病年度累计支付');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',54, '门诊慢性病名称');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',55, '城乡居民人员类型');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',56, '城乡居民人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',57, '民政医疗救助人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',58, '本年民政医疗救助金额累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',59, '本年健康扶贫补充保险报销金额累计');

使用方法：

select GetSelect(0) '获取：读卡日志的查询sql ';
select GetSelect(1) '获取：结算日志的查询sql';

　　

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/linmilove/p/12673859.html