create PROCEDURE aa(SqlCMD1 varchar(8000),SqlCMD2 varchar(8000),SqlCMD3 varchar(8000))
begin
declare exit handler for sqlexception rollback;
start TRANSACTION;
EXECUTE SqlCMD1;
EXECUTE SqlCMD2;
EXECUTE SqlCMD3;
COMMIT;
end#drop PROCEDURE aacall aa('insert into a(vName) values('B')','insert into a(vName) values('B')','insert into a(vName) values('B')')现在有3个SQL命令,要用事务来处理其运行情况。我创建存储过程如上,三条SQL命令当做参数传入,然后事务开始,再依次运行3条语句,再提交事务,如果发生错误,回滚事务。测试的时候报错。。要实现这这样的操作,应该怎么样写这个存储过程??
要用这种语法
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;
begin
declare exit handler for sqlexception rollback;
start TRANSACTION;
PREPARE stmt_name FROM SqlCMD1;
EXECUTE stmt_name;
DEALLOCATE stmt_name;
PREPARE stmt_name FROM SqlCMD2;
EXECUTE stmt_name;
DEALLOCATE stmt_name;
PREPARE stmt_name FROM SqlCMD3;
EXECUTE stmt_name;
DEALLOCATE stmt_name;
COMMIT;
end#drop PROCEDURE aa
2. 'insert into a(vName) values('B')' 这种语法上是不对的,需要转义call aa('insert into a(vName) values(\'B\')','insert into a(vName) values(\'B\')','insert into a(vName) values(\'B\')')
1:在MYSQL-FRONT里执行报错
直接在msql.exe 工具中看一下。如果有错误,则把错误信息一起贴出来。
-> begin
-> declare exit handler for sqlexception rollback;
-> start TRANSACTION;
->
-> set @SqlCMD=SqlCMD1;
-> PREPARE stmt_name FROM @SqlCMD;
-> EXECUTE stmt_name;
-> DEALLOCATE PREPARE stmt1;
->
-> set @SqlCMD=SqlCMD2;
-> PREPARE stmt_name FROM @SqlCMD;
-> EXECUTE stmt_name;
-> DEALLOCATE PREPARE stmt1;
->
-> set @SqlCMD=SqlCMD3;
-> PREPARE stmt_name FROM @SqlCMD;
-> EXECUTE stmt_name;
-> DEALLOCATE PREPARE stmt1;
->
-> COMMIT;
-> end//
Query OK, 0 rows affected (0.00 sec)mysql> call aa('select 1;','select now();','select \'aa\';')//
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
start TRANSACTION;
这个呀。
在MYSQL-FRONT里执行不了。
drop PROCEDURE t1create PROCEDURE T1(SqlCMD1 varchar(8000),SqlCMD2 varchar(8000))
begin
declare exit handler for sqlexception rollback;
SET @s=SqlCMD1;
PREPARE stmt1 FROM @s; #'select * from A';
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; SET @s=SqlCMD2;
PREPARE stmt1 FROM @s; #'select * from A';
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; end
call t1('select * from A','insert into A(vName) values("asdaadsada")')测试成功。。
但是还有一个问题。在CALL的时候,'insert into A(vName) values("asdaadsada")' 这个插入语句的时候,字符串如果用单引号引起来,就出错,需要用双引号。。这和普通的SQL又有区别。。怎么样才能用单引号来引字符串??