mysql 窗口写procedure_MySQL中Procedure事务编写基础笔记

最新推荐文章于 2024-02-17 19:56:42 发布

原创最新推荐文章于 2024-02-17 19:56:42 发布 · 588 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql 窗口写procedure

本文详细介绍了MySQL中的PROCEDURE，包括其与FUNCTION的区别、CREATE PROCEDURE的基本语法和使用注意事项。还探讨了PROCEDURE的高级特性，如DECLARE语句、HANDLER句柄、CURSOR游标和循环结构，以及触发器TRIGGER的概念和创建方法。

一、PROCEDURE：

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

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

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

二、CREATE PROCEDURE基本语法：

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

1 CREATE

2 [DEFINER = { user | CURRENT_USER }]

3 PROCEDURE sp_name ([proc_parameter[,...]])4 [characteristic ...]routine_body5 CREATE

6 [DEFINER = { user | CURRENT_USER }]

7 FUNCTION sp_name ([func_parameter[,...]])8 RETURNStype9 [characteristic ...]routine_body10 proc_parameter:11 [IN | OUT | INOUT]param_name type12 func_parameter:13 param_name type14 type:15 Anyvalid MySQL data type16 characteristic:17 COMMENT 'string'

18 |LANGUAGE SQL19 | [NOT]DETERMINISTIC20 | { 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 INVOKEBEGIN

SELECT COUNT(*) INTO outParam FROM tbl WHERE col

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 PROCEDUREtest()BEGIN

DECLARE usrID INT;DECLARE usrName VARCHAR(10) DEFAULT 'NERO';

..........--一些事务操作

END;

3.2、声明HANDLER句柄：

基本语法：

1 DECLAREhandler_type HANDLER2 FOR condition_value [, condition_value]...3 statement4 handler_type:5 CONTINUE

6 | EXIT

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 //

5 CREATE PROCEDUREHANDLER_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 //

16 CALLL HANDLER_DEMO()//

18 SELECT @x //

19 SELECT @x2 //

21 delimiter ;

结果当然是@x为3,@x2为1了。在代码11行，重复插入相同的值到主键上触发了23000错误，因而执行statement：SET @x2 = 1,然后再继续执行主程序的SET @x = 3.

2、EXIT，一旦条件被触发，当前BEGIN...END闭合语句将会终止执行，比如说：

1 delimiter //

2 CREATE PROCEDUREEXIT_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 PROCEDURECURSOR_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 FROMtbl1;9 DECLARE CUR2 CURSOR FOR SELECT I FROMtbl2;10 /*声明CONTINUE句柄，当游标触底时被触发*/

11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;12

13 /*打开游标*/

14 OPENCUR1;15 OPENCUR2;16

17 /*循环插入数据，使用REPEAT...UNTIL语句*/

18 REPEAT19 FETCH CUR1 INTO a,b; --读取游标中的数据，并移向下一行

20 FETCH CUR2 INTOc;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 ENDREPEAT;30

31 /*使用完毕，关闭游标*/

32 CLOSECUR1;33 CLOSECUR2;34 END//

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)BEGINlabel1: LOOPSET param = param +1;IF param < 100 THENITERATE label1;--回到标签开始处

END IF;

LEAVE label1;--离开标签，退出流控制结构

END LOOP label1; --结束循环

END;

delimiter ;

2、REPEAT：

先给出REPEAT语法定义：

1 [begin_label:] REPEAT2 statement_list3 UNTIL search_condition4 END REPEAT [end_label]

可见，同样是可以在代码开始处插入label标签，不过REPEAT循环是自己有控制条件的，最好能直接使用UNTIL来进行条件判断。

比如下面这个例子：

1 delimiter //

2 CREATE PROCEDURE REPEAT_DEMO(param INT)3 BEGIN

4 SET @x = 0;5 REPEAT6 SET @x = @x+1;7 UNTIL @x >param8 ENDREPEAT;9 END//

11 delimiter ;

3、WHILE：

WHILE循环语法定义如下：

[begin_label:] WHILEsearch_condition DO

statement_listEND WHILE [end_label]

比如下面这个小例子：

delimiter //

CREATE PROCEDUREWHILE_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 }]

TRIGGERtrigger_name trigger_time trigger_eventON 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' TRIGGERtrigger_demo3 BEFORE INSERT ON tbl1 FOREACH ROW4 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内的内容就会被执行。