我想写一个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,不懂,望大神指导
解决方案 »
- 用MySQL Workbench创建的数据库只会生成frm文件和ibd文件,怎么才能生成myd文件和myi文件?
- mysql-standard-5.0.27-linux-i686-glibc23.tar.gz
- access 数据.mdb文件导入mysql数据库
- windows客户端如何通过程序访问unix/linux下的数据库(如mysql,postgresql,oracle)
- MySQL 5.1GA 和MySQL 5.4是不是不支持双核cpu?
- 如何在A_Table中用select检索到的数据再用它为索引在B_Table中Insert?
- 帮忙这句话是什么意思(急)
- 我这里上网不方便,所以请教 MySql 和 PostgreSql 的连接写法。
- 用phpMyAdmin连接MySQL数据库不成功
- 一个sql
- 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必须通过命名块,也就是存储过程来执行