我想写一个mysql事务,如果query执行异常则回滚,否则提交,但是搜了半天都是在存储过程中实现的,怎么不写存储过程实现这个功能呢,存储过程的代码大致都是这样的:
DELIMITER $$
DROP PROCEDURE IF EXISTS test_sp1 $$
CREATE PROCEDURE test_sp1( )
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
INSERT INTO test VALUES(NULL, 'test sql 001');
INSERT INTO test VALUES('1', 'test sql 002');
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
怎么写直接开启一个事务,然后判断异常。如果事务非得在存储过程中写,那也太不灵活了吧,刚看mysql,不懂,望大神指导
DELIMITER $$
DROP PROCEDURE IF EXISTS test_sp1 $$
CREATE PROCEDURE test_sp1( )
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
INSERT INTO test VALUES(NULL, 'test sql 001');
INSERT INTO test VALUES('1', 'test sql 002');
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
怎么写直接开启一个事务,然后判断异常。如果事务非得在存储过程中写,那也太不灵活了吧,刚看mysql,不懂,望大神指导
解决方案 »
- 如何查询前1000条数据
- 新手求教,mysql查询序列化存储的数组的问题?
- mysql命令行表格歪曲显示怎么办?
- 用ADO链接mysql,不能运行source命令,如何解决?
- MyISAM表类型的最大容量是多少?系统是linux,ext3
- MYSQL动态判断加主键问题
- 我的jsp程序连接mysql数据库,运行后提示: Server configuration denies access to data source!请问高手,为什么????
- MYSQL写入或者读取速度慢 怎么解决呢 是固态硬盘。
- 有一个问题,假设Mysql 中一个表有20条记录,id是其中一个自增长的字段,由于id从1开始自增,所以现在id=20,但当删除一条记录之后,id不再连续
- mysql 我居然找不到datadir
- Linux下安装Mysql总是报错
- insert into ... select 问题求解答
CREATE
PROCEDURE ps_ddgl_ggwxx_insert(IN ps_sblxid INT(11), IN ps_sblxmc VARCHAR(20) CHARACTER SET UTF8, IN ps_ggwzbh VARCHAR(20) CHARACTER SET UTF8, IN ps_ggwzmc VARCHAR(20) CHARACTER SET UTF8, IN ps_bzxx VARCHAR(20) CHARACTER SET UTF8, OUT exceResult INT)
COMMENT '新增广告位'
BEGIN
START TRANSACTION;
select count(*) into @likeset from ddgl_ggwxx where ggwzbh = ps_ggwzbh ;
select count(*) into @totals from ddgl_ggwxx where sblxid = ps_sblxid ;
select ggwsx into @ggwsx from ddgl_sblx where lx_id = ps_sblxid ;
IF(@totals >= @ggwsx) then
SET exceResult = 3;
ELSEIF(@likeset >0) then
SET exceResult = 2;
else
INSERT INTO ddgl_ggwxx ( sblxid, sblxmc, ggwzbh, ggwzmc, bzxx) VALUES(ps_sblxid, ps_sblxmc, ps_ggwzbh, ps_ggwzmc, ps_bzxx);
COMMIT;
SET exceResult = 1;
end if ;
END
由你的应用程序来判断每条语句的执行结果, 错误就发语句rollback
select @a:=count(0) from table1;
if @a>0
...
else
...
end if;
楼主说的是匿名块mssql和plsql都支持mysql是没有这种的,mysql必须通过命名块,也就是存储过程来执行