MySQL中Procedure事务编写基础笔记
目录:
一、PROCEDURE:
PROCEDURE,事务,一个存储过程,实际上就是在服务器端直接在数据库中编写一段代码作运算,在服务器端进行高效的运算,运算结果直接返还给客户端。
它和FUNCTION一个明显的不同点是,FUNCTION最后会有RETURN语句,返回运算结果,PROCEDURE不允许有RETURN语句的,但是可以在参数表中指定返还数据。
PROCEDURE编译完成后会存储在数据库中,需要调用的时候使用CALL语句对事务或者函数进行调用。编写PROCEDURE不仅可以避免重复编码,同时还可以提高计算效率。
二、CREATE PROCEDURE基本语法:
下面不妨先看一看CREATE PROCEDURE以及CREATE FUNCTION的语法:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 CREATE 2 [DEFINER = { user | CURRENT_USER }] 3 PROCEDURE sp_name ([proc_parameter[,...]]) 4 [characteristic ...] routine_body 5 CREATE 6 [DEFINER = { user | CURRENT_USER }] 7 FUNCTION sp_name ([func_parameter[,...]]) 8 RETURNS type 9 [characteristic ...] routine_body 10 proc_parameter: 11 [ IN | OUT | INOUT ] param_name type 12 func_parameter: 13 param_name type 14 type: 15 Any valid MySQL data type 16 characteristic: 17 COMMENT 'string' 18 | LANGUAGE SQL 19 | [NOT] DETERMINISTIC 20 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 21 | SQL SECURITY { DEFINER | INVOKER } 22 routine_body: 23 Valid SQL routine statement
其中有几个注意点是:
1,DEFINER你可以用这个选项指定可以调用该PROCEDURE的用户,比如说允许本地的用户nero使用,那么可以指定为:DEFINER='nero'@'localhost',如果这个事务就是创建给当前用户使用的,那么可以指定为:DEFINER=CURRENT_USER。
2,事务安全性:characteristic中如果使用了SQL SECURITY,那么事务每次执行的时候,指定的安全上下文都会被执行,它们会检查当前执行这个事务的人是否拥有执行权限。
比如说下面这个小例子:
delimiter // --指定临时分隔符 CREATE DEFINER = 'nero'@'localhost' PROCEDURE simpleTest(OUT outParam int, IN inParam int) SQL SECURITY INVOKE BEGIN SELECT COUNT(*) INTO outParam FROM tbl WHERE col < inParam; END; delimter ;
像上面这个例子,我们在定义的时候启用了“SQL SECURITY INVOKE”,只有是:a,对这个事务有调用权限;b,对这个表tbl有select权限的用户才能成功执行该PROCEDURE。
而在形参部分,则是通过OUT和IN指明参数传入还是传出,如果某个参数在传入之后要作为结果传出,那么不需要作特定指示,直接写明参数名称和参数类型即可。
调用这个事务则用CALL表达式即可:
SET @b=100; CALL simpleTest(@a,@b); SELECT @a; --显示结果
三、PROCEDURE小进阶:
知道PROCEDURE的基本语法以后,学习一下编写一个PROCEDURE经常需要用到的语句,分别有:DECLARE声明语句,SET设值语句,DECLARE...HANDLER句柄声明语句,DECLARE...CURSOR游标声明语句;条件判断IF和CASE;三种循环体:LOOP,REPEAT,WHILE。
3.1、基本的DECLARE语句:
DECLARE基本语法:
DECLARE var_name [, var_name] ... type [DEFAULT value]
比如说在某个事务中声明几个临时变量:
CREATE PROCEDURE test() BEGIN DECLARE usrID INT ; DECLARE usrName VARCHAR(10) DEFAULT 'NERO'; .......... --一些事务操作 END;
3.2、声明HANDLER句柄:
基本语法:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 DECLARE handler_type HANDLER 2 FOR condition_value [, condition_value] ... 3 statement 4 handler_type: 5 CONTINUE 6 | EXIT 7 | UNDO 8 condition_value: 9 SQLSTATE [VALUE] sqlstate_value 10 | condition_name 11 | SQLWARNING 12 | NOT FOUND 13 | SQLEXCEPTION 14 | mysql_error_code
句柄的作用,就是在condition_value中,如果指定的任意条件出现了,那么statement这里的指定语句就会被执行。conditions条件有几种类型:
1、SQLSTATE指的是当前SQL返回的状态,这个对应的状态就比较多了,比如状态Error: 1169 SQLSTATE: 23000,指的是”因特定限制而导致的无法写入的错误“;Error: 1162 SQLSTATE: 42000 ,指的是”结果字符串超过了最大限制“。相关的状态代码请自行查阅帮助文档的”Server Error Codes and Messages“词条。
2、SQLWARNING,但凡是SQL发出的警告信息。
3、NOT FOUND,一般来说出现在SELECT语句中,游标触底;
4、SQLEXCEPTION,SQL错误。
不同的结果分别对应:
1、CONTINUE,如果条件成立,那么,在执行句柄的statement之后再继续执行程序,比如说下面这个例子:
1 CREATE TABLE tbl(col INT ,PRIMARY KEY(col)); 2 3 delimiter // 4 5 CREATE PROCEDURE HANDLER_DEMO() 6 BEGIN 7 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 8 SET @x = 1; 9 INSERT INTO tbl VALUES(1); 10 SET @x = 2; 11 INSERT INTO tbl VALUES(1); --触发句柄的statement执行 12 SET @x = 3; 13 END; 14 // 15 16 CALLL HANDLER_DEMO()// 17 18 SELECT @x // 19 SELECT @x2 // 20 21 delimiter ;
结果当然是@x为3,@x2为1了。在代码11行,重复插入相同的值到主键上触发了23000错误,因而执行statement:SET @x2 = 1,然后再继续执行主程序的SET @x = 3.
2、EXIT,一旦条件被触发,当前BEGIN...END闭合语句将会终止执行,比如说:
1 delimiter // 2 CREATE PROCEDURE EXIT_DEMO() 3 BEGIN 4 BEGIN 5 DECLARE EXIT HANDLER FOR SQLSTATE '23000' 6 ....... 7 END; 8 END; 9 delimiter ;
上述代码中,一旦出现23000错误,代码行4到7的BEGIN...END闭合语句立刻终止执行。
3.3、声明CURSOR游标 :
声明一个CURSOR游标:
DECLARE cursor_name CURSOR FOR select_statement
比如说最基本的:
DECLARE cur1 CURSOR FOR SELECT id,data FROM tbl;
此时cur1表示的即是SELECT语句返回的首个结果,有点类似于指针。
下面不妨看一个比较完整的例子:
1 delimiter // 2 CREATE PROCEDURE CURSOR_DEMO() 3 BEGIN 4 DECLARE done INT DEFAULT 0; --INT型值,默认为0 5 DECLARE a CHAR(5); 6 DECLARE b,c INT; 7 /*声明两个游标*/ 8 DECLARE CUR1 CURSOR FOR SELECT ID,DATA FROM tbl1; 9 DECLARE CUR2 CURSOR FOR SELECT I FROM tbl2; 10 /*声明CONTINUE句柄,当游标触底时被触发*/ 11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 12 13 /*打开游标*/ 14 OPEN CUR1; 15 OPEN CUR2; 16 17 /*循环插入数据,使用REPEAT...UNTIL语句*/ 18 REPEAT 19 FETCH CUR1 INTO a,b; --读取游标中的数据,并移向下一行 20 FETCH CUR2 INTO c; 21 IF NOT done THEN --当done为0的时候条件成立 22 IF b < c THEN --取b和c的较小者插入表3 23 INSERT INTO tbl3 VALUES(a,b); 24 ELSE 25 INSERT INTO tbl3 VALUES(a,c); 26 END IF; 27 END IF; --当游标触底,句柄将被触发,done值被设为1,然后从这里继续执行主程序 28 UNTIL done --句柄触发后,done为1,执行UNTIL 29 END REPEAT; 30 31 /*使用完毕,关闭游标*/ 32 CLOSE CUR1; 33 CLOSE CUR2; 34 END// 35 36 delimiter ;
其中,FETCH语句的基本语法如下:
FETCH cursor_name INTO var_name [, var_name] ...
该语句每次都会返回SELECT结果中的下一行(如果有的话)。
3.4、循环语句:
循环涉及到的语句有:1、LOOP、ITERATE和LEAVE;2、REPEAT;3、WHILE。
下面直接给出对应的循环例子:
1、LOOP、ITERATE和LEAVE:
/*LOOP,ITERATE,LEAVE*/ delimiter // CREATE PROCEDURE LOOP_DEMO(param INT) BEGIN label1: LOOP SET param = param +1; IF param < 100 THEN ITERATE label1; --回到标签开始处 END IF; LEAVE label1; --离开标签,退出流控制结构 END LOOP label1; --结束循环 END; delimiter ;
2、REPEAT:
先给出REPEAT语法定义:
1 [begin_label:] REPEAT 2 statement_list 3 UNTIL search_condition 4 END REPEAT [end_label]
可见,同样是可以在代码开始处插入label标签,不过REPEAT循环是自己有控制条件的,最好能直接使用UNTIL来进行条件判断。
比如下面这个例子:
1 delimiter // 2 CREATE PROCEDURE REPEAT_DEMO(param INT) 3 BEGIN 4 SET @x = 0; 5 REPEAT 6 SET @x = @x+1; 7 UNTIL @x > param 8 END REPEAT; 9 END// 10 11 delimiter ;
3、WHILE:
WHILE循环语法定义如下:
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
比如下面这个小例子:
delimiter // CREATE PROCEDURE WHILE_DEMO() BEGIN SET param INT DEFAULT 10; WHILE param < 1000 .... --循环内书写具体需要处理的事务 SET param = param + 100; END WHILE; END; delimiter ;
四、顺带提一下触发器TRIGGER:
触发器都是和某个特定的表相关联的,对该表设定触发器以后,一旦对这个表进行了某个特定操作(诸如INSERT,UPDATE,DELETE),触发器就会被触发。
先给出CREATE TRIGGER语法定义:
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
同样的,可以通过DEFINER自行指定触发器的适用对象。
在trigger_time中可以指定触发时间(诸如:BEFORE,AFTER),trigger_event前面已经提到过了,另外,DROP TABLE或TRUNCATE TABLE这种操作是不会触发TRIGGER的。
下面给出个小例子:
1 delimiter // 2 CREATE DEFINER 'nero'@'localhost' TRIGGER trigger_demo 3 BEFORE INSERT ON tbl1 FOR EACH ROW 4 BEGIN 5 INSERT INTO tbl2 VALUES(...........); --INSERT操作 6 DELETE FROM tbl3 WHERE .......... ; --删除操作 7 UPDATE tbl4 SET col1 = ...... ; --更新操作 8 END; // 9 delimiter ;
这样,一旦本地用户nero对表tbl1进行INSERT操作的时候(之前,这里设置的是BEFORE),BEGIN...END内的内容就会被执行。