MySQL中Procedure事务编写基础笔记

原文:MySQL中Procedure事务编写基础笔记

目录:

一、PROCEDURE;

二、CREATE PROCEDURE基本语法;

三、PROCEDURE小进阶

  3.1、基本的DECLARE语句;

  3.2、声明HANDLER句柄;

  3.3、声明CURSOR游标;

  3.4、循环语句;

四、顺带提一下触发器TRIGGER

一、PROCEDURE:

PROCEDURE,事务,一个存储过程,实际上就是在服务器端直接在数据库中编写一段代码作运算,在服务器端进行高效的运算,运算结果直接返还给客户端。

它和FUNCTION一个明显的不同点是,FUNCTION最后会有RETURN语句,返回运算结果,PROCEDURE不允许有RETURN语句的,但是可以在参数表中指定返还数据。

PROCEDURE编译完成后会存储在数据库中,需要调用的时候使用CALL语句对事务或者函数进行调用。编写PROCEDURE不仅可以避免重复编码,同时还可以提高计算效率。

二、CREATE PROCEDURE基本语法:

下面不妨先看一看CREATE PROCEDURE以及CREATE FUNCTION的语法:

MySQL中Procedure事务编写基础笔记MySQL中Procedure事务编写基础笔记
 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
View Code

其中有几个注意点是:

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

而在形参部分,则是通过OUTIN指明参数传入还是传出,如果某个参数在传入之后要作为结果传出,那么不需要作特定指示,直接写明参数名称和参数类型即可。

调用这个事务则用CALL表达式即可:

SET @b=100;
CALL simpleTest(@a,@b);
SELECT @a;      --显示结果

三、PROCEDURE小进阶:

知道PROCEDURE的基本语法以后,学习一下编写一个PROCEDURE经常需要用到的语句,分别有:DECLARE声明语句,SET设值语句,DECLARE...HANDLER句柄声明语句,DECLARE...CURSOR游标声明语句;条件判断IFCASE;三种循环体: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句柄:

基本语法:

MySQL中Procedure事务编写基础笔记MySQL中Procedure事务编写基础笔记
 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
View 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内的内容就会被执行。

上一篇:重复addEventListener("事件名",的问题


下一篇:[Struts]使用tiles管理界面遇到困难