最近在工作中碰到一个问题。
数据库是MySQL5.0,有这样一个储存过程。delimiter //
CREATE PROCEDURE queryFuturePriceByIndexs(IN indexCodes varchar(255),IN li char(1),IN tableName varchar(129))
BEGIN
DECLARE str varchar(255);#原始字符串
DECLARE paramStr char(255); #解析出来的字符串
set str = concat(indexCodes,li); #获取传送的字符串 set @createTableStr = 'CREATE TEMPORARY TABLE ';
set @createTableStr = concat(@createTableStr,tableName);
set @createTableStr = concat(@createTableStr, ' (futureNameCh varchar(255)');
set @createTableStr = concat(@createTableStr, ', nominal DECIMAL(41,20)');
set @createTableStr = concat(@createTableStr, ', netChange DECIMAL(41,20)');
set @createTableStr = concat(@createTableStr, ', percentChange DECIMAL(41,20)');
set @createTableStr = concat(@createTableStr, ', futurePriceUpdateTime datetime');
set @createTableStr = concat(@createTableStr, ', indexCode varchar(255)');
set @createTableStr = concat(@createTableStr, ') TYPE=HEAP');
PREPARE createTmpTable FROM @createTableStr;
EXECUTE createTmpTable;
WHILE LENGTH(str) > 0 do
set paramStr = left(str,INSTR(str,li)-1);
set str = substring(str,INSTR(str,li)+1);
if (paramStr != 'ALL') then
set @runStr = 'insert into ';
set @runStr = concat(@runStr,tableName);
set @runStr = concat(@runStr,' select f.futureNameCh,fp.nominal,fp.netChange,fp.percentChange,fp.futurePriceUpdateTime,i.indexCode');
set @runStr = concat(@runStr,' from futurePrice fp');
set @runStr = concat(@runStr,' inner join future f on f.id=fp.future_id');
set @runStr = concat(@runStr,' inner join indexInfo i on i.id=f.index_id');
set @runStr = concat(@runStr,' where i.indexCode=','\'',paramStr,'\'');
set @runStr = concat(@runStr,' order by fp.futurePriceUpdateTime desc limit 1');
end if;
PREPARE runInsert FROM @runStr;
EXECUTE runInsert;
END WHILE; set @queryStr = concat('select * from ',tableName);
PREPARE queryTmp FROM @queryStr;
EXECUTE queryTmp; set @dropStr = concat('drop table ',tableName);
PREPARE dropTmp FROM @dropStr;
EXECUTE dropTmp;END//delimiter ;里面的表"future"和"futurePrice"是“一对多”的关系。
这个储存过程用处是根据参数组装SQL循环的动态的执行,然后把一个结果放在一个预先建立的内存型临时表然后一起返回。
我使用的是"Hibernate3.2"。我有几个问题想请教一下。1.Hibernate如何映射这个储存过程?现在是直接用"SQLQuery"执行"call queryFuturePriceByIndexs(?,?,?)"语句来调用的。2.因为原来是JDBC事务,现在改变全局事务了。这个储存过程返回了如下的错误信息。
SQL Error: 1399, SQLState: XAE07
XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
查询"MySQL"的文档,我认为应该是储存过程中的"create table"是一个不能"rollback"的语句,不被允许执行在全局事务中。
现在的"MySQL5.0"没有表类型的变量,我想问一下我如何来修改这个储存过程?
我想到的是使用一个"text"变量来记录多个动态构造的用"union"拼接的"select"语句,最后一并执行。各位给个意见。
数据库是MySQL5.0,有这样一个储存过程。delimiter //
CREATE PROCEDURE queryFuturePriceByIndexs(IN indexCodes varchar(255),IN li char(1),IN tableName varchar(129))
BEGIN
DECLARE str varchar(255);#原始字符串
DECLARE paramStr char(255); #解析出来的字符串
set str = concat(indexCodes,li); #获取传送的字符串 set @createTableStr = 'CREATE TEMPORARY TABLE ';
set @createTableStr = concat(@createTableStr,tableName);
set @createTableStr = concat(@createTableStr, ' (futureNameCh varchar(255)');
set @createTableStr = concat(@createTableStr, ', nominal DECIMAL(41,20)');
set @createTableStr = concat(@createTableStr, ', netChange DECIMAL(41,20)');
set @createTableStr = concat(@createTableStr, ', percentChange DECIMAL(41,20)');
set @createTableStr = concat(@createTableStr, ', futurePriceUpdateTime datetime');
set @createTableStr = concat(@createTableStr, ', indexCode varchar(255)');
set @createTableStr = concat(@createTableStr, ') TYPE=HEAP');
PREPARE createTmpTable FROM @createTableStr;
EXECUTE createTmpTable;
WHILE LENGTH(str) > 0 do
set paramStr = left(str,INSTR(str,li)-1);
set str = substring(str,INSTR(str,li)+1);
if (paramStr != 'ALL') then
set @runStr = 'insert into ';
set @runStr = concat(@runStr,tableName);
set @runStr = concat(@runStr,' select f.futureNameCh,fp.nominal,fp.netChange,fp.percentChange,fp.futurePriceUpdateTime,i.indexCode');
set @runStr = concat(@runStr,' from futurePrice fp');
set @runStr = concat(@runStr,' inner join future f on f.id=fp.future_id');
set @runStr = concat(@runStr,' inner join indexInfo i on i.id=f.index_id');
set @runStr = concat(@runStr,' where i.indexCode=','\'',paramStr,'\'');
set @runStr = concat(@runStr,' order by fp.futurePriceUpdateTime desc limit 1');
end if;
PREPARE runInsert FROM @runStr;
EXECUTE runInsert;
END WHILE; set @queryStr = concat('select * from ',tableName);
PREPARE queryTmp FROM @queryStr;
EXECUTE queryTmp; set @dropStr = concat('drop table ',tableName);
PREPARE dropTmp FROM @dropStr;
EXECUTE dropTmp;END//delimiter ;里面的表"future"和"futurePrice"是“一对多”的关系。
这个储存过程用处是根据参数组装SQL循环的动态的执行,然后把一个结果放在一个预先建立的内存型临时表然后一起返回。
我使用的是"Hibernate3.2"。我有几个问题想请教一下。1.Hibernate如何映射这个储存过程?现在是直接用"SQLQuery"执行"call queryFuturePriceByIndexs(?,?,?)"语句来调用的。2.因为原来是JDBC事务,现在改变全局事务了。这个储存过程返回了如下的错误信息。
SQL Error: 1399, SQLState: XAE07
XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
查询"MySQL"的文档,我认为应该是储存过程中的"create table"是一个不能"rollback"的语句,不被允许执行在全局事务中。
现在的"MySQL5.0"没有表类型的变量,我想问一下我如何来修改这个储存过程?
我想到的是使用一个"text"变量来记录多个动态构造的用"union"拼接的"select"语句,最后一并执行。各位给个意见。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货