存储程序和函数
MySQL存储程序的语法
1. 环境登录
无需密码自动登录,系统用户名shiyanlou
2. 环境介绍
本实验环境采用带桌面的Ubuntu Linux环境,实验中会用到桌面上的程序:
命令行终端: Linux命令行终端,打开后会进入Bash环境,可以使用Linux命令
Firefox及Opera:浏览器,可以用在需要前端界面的课程里,只需要打开环境里写的HTML/JS页面即可
gvim:非常好用的Vim编辑器,最简单的用法可以参考课程Vim编辑器
gedit及Brackets:如果您对gvim的使用不熟悉,可以用这两个作为代码编辑器,其中Brackets非常适用于前端代码开发
3. 环境使用
使用GVim编辑器输入实验所需的代码及文件,使用Xfce终端(XfceTerminal)运行所需命令进行操作。
实验报告可以在个人主页中查看,其中含有每次实验的截图及笔记,以及每次实验的有效学习时间(指的是在实验桌面内操作的时间,如果没有操作,系统会记录为发呆时间)。这些都是您学习的真实性证明。
4. 实验内容
4.1 CREATE PROCEDURE和CREATE FUNCTION
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_bodyCREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name typetype: Any valid MySQL data typecharacteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'routine_body: Valid SQL procedure statement or statements
这些语句用于创建并存储子程序。要在MySQL 5.1中创建子程序,必须具有CREATE ROUTINE权限,并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。
默认情况下,子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来,可以在创建子程序的时候指定其名字为db_name.sp_name。
如果子程序名和SQL内部函数名一样,在定义子程序时,你需要在这个名字和随后括号中间插入一个空格,否则发生语法错误。在你之后调用子程序的时候也要插入。为此,即使有可能出现这种情况,我们还是建议最好避免给你的存储子程序取与已经存在的SQL函数一样的名字。
由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。每个参数默认都是一个IN(内部)参数。要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT。
注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。(FUNCTION参数总是被认为是IN)
下面是一个使用OUT参数的简单的存储程序的例子。例子为:在程序被定义的时候,用mysql客户端delimiter命令来把语句定界符从;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。
mysql> delimiter //mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END -> //mysql> delimiter ;mysql> CALL simpleproc(@a);mysql> SELECT @a;
当使用delimiter命令时,你应该避免使用反斜杠(‘\’)字符,因为那是MySQL的转义字符。
下列是一个例子,一个采用参数的函数使用一个SQL函数执行一个操作,并返回结果:
mysql> delimiter //mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> //mysql> delimiter ;mysql> SELECT hello('world');
如果在存储函数中的RETURN语句中返回一个类型不同于在函数的RETURNS子句中指定类型的值,返回值被强制转换为相对恰当的类型。比如,如果一个函数返回一个ENUM或SET值,但是RETURN语句返回一个整数,对于SET成员集合的相应的ENUM成员,从函数返回的值是字符串类型数据。
4.2 ALTER PROCEDURE和ALTER FUNCTION
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'
这个语句可以被用来改变存储程序或函数的特征。
在ALTER PROCEDURE和ALTER FUNCTION语句中,可以指定一个以上的改变。
4.3 DROP PROCEDURE和DROP FUNCTION
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
这个语句被用来移除一个存储程序或函数。即从服务器上移除一个制定的子程序。
IF EXISTS子句是一个MySQL的扩展。它防止如果程序或函数不存在产生的错误,并产生一个可以用SHOW WARNINGS查看的警告。
4.4 SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重新创建并且已命名的子程序的字符串。
例如以下语句返回test数据库内的hello程序信息:
mysql> SHOW CREATE FUNCTION test.hello\G
4.5 SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
这个语句是一个MySQL的扩展。它返回子程序的特征,如数据库,名字,类型,创建者及创建和修改日期。如果没有指定样式,根据你使用的语句,所有存储程序和所有存储函数的信息都被列出。
例如:
mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
4.6 CALL语句
CALL sp_name([parameter[,...]])
CALL语句调用一个先前使用CREATE PROCEDURE创建的程序。
CALL语句可以用声明为OUT或INOUT参数的参数给它的调用者传回返回值。它也“返回”受影响的行数,客户端程序可以在SQL级别上通过调用ROW_COUNT()函数获得这个数,在C中是调用the mysql_affected_rows()这个 C API函数来获得。
4.7 BEGIN ... END复合语句
begin_label:] BEGIN [statement_list]END [end_label]
存储子程序可以使用BEGIN ... END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list内每个语句都必须用分号(;)来结尾。
复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。
请注意,可选的[NOT] ATOMIC子句现在还不被支持。这意味着在指令块的开始没有交互的存储点被设置,并且在上下文中用到的BEGIN子句对当前交互动作没有影响。
使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来改变查询结尾定界符;(比如改为//)使得; 可被用在子程序体中。
4.8 DECLARE语句
DECLARE语句被用来把不同项目涵盖到一个子程序:局部变量,条件和处理程序 及光标。SIGNAL和RESIGNAL语句当前还不被支持。
DECLARE仅被用在BEGIN ... END复合语句里,并且必须在复合语句的开头,在任何其它语句之前。
光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。
4.9 存储过程中的变量
4.9.1 DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值,请包含一个DEFAULT子句。值可以被指定为一个表达式,不需要为一个常数。如果没有DEFAULT子句,初始值为NULL。
局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中,除了那些用相同名字声明变量的块。
4.9.2 变量SET语句
SET var_name = expr [, var_name = expr] ...
在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量,或者是全局服务器变量。
在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型(局域声明变量及全局和集体变量)可以被混合使用。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下,此选项会被识别,但是也会被忽略。
4.9.3 SELECT ... INTO语句
SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量中。因此,只有单一的行可以被取回。
例如:
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的引用,并包含一个与列相同名字的局部变量,MySQL会把引用解释为一个变量的名字。例如,在下面的语句中,xname 被解释为到xname 变量的引用而不是到xname列的引用:
CREATE PROCEDURE sp1 (x VARCHAR(5)) BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid FROM table1 WHERE xname = xname; SELECT newname; END;
当这个程序被调用的时候,无论table.xname列的值是什么,变量newname将返回值‘bob’。
4.10 条件和处理程序
4.10.1 DECLARE条件
DECLARE condition_name CONDITION FOR condition_valuecondition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code
这个语句用于指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以之后被用在DECLARE HANDLER语句中。
4.10.2 DECLARE处理程序
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statementhandler_type: CONTINUE | EXIT | UNDOcondition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code
这个语句用于指定每一个可以处理一个或多个条件的处理程序。如果产生一个或多个条件,指定的语句将被执行。
对一个CONTINUE处理程序,当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序,当前BEGIN...END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。 SQLWARNING是对所有以01开头的SQLSTATE代码的速记。
NOT FOUND是对所有以02开头的SQLSTATE代码的速记。
SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。
除了SQLSTATE值,MySQL错误代码也不被支持。
例如:
mysql> CREATE TABLE test.t (s1 int,primary key (s1));mysql> delimiter //mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO test.t VALUES (1); -> SET @x = 2; -> INSERT INTO test.t VALUES (1); -> SET @x = 3; -> END; -> //mysql> CALL handlerdemo()//mysql> SELECT @x//
注意到,@x是3,这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;这一行不存在,第二个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取默认(EXIT)路径,导致SELECT @x可能返回2。
4.11 光标
简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。当前的光标是不敏感的,分为只读的以及不滚动的。光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。
例如:
CREATE PROCEDURE curdemo()BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b
DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。
其中select_statement中的SELECT语句不能有INTO子句。
4.11.2 光标OPEN语句
OPEN cursor_name
这个语句用于打开先前声明的光标。
4.11.3 光标FETCH语句
FETCH cursor_name INTO var_name [, var_name] ...
这个语句用指定的打开光标读取下一行(如果有下一行的话),并且向前移动光标指针。
4.11.4 光标CLOSE语句
CLOSE cursor_name
这个语句关闭先前打开的光标。
如果未被明确地关闭,光标在它被声明的复合语句的末尾被关闭。
4.12 流程控制构造
4.12.1 IF语句
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list]END IF
IF实现了一个基本的条件构造。如果search_condition求值为真,相应的SQL语句列表被执行。如果没有search_condition匹配,在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。
请注意,还有有一个IF() 函数,它不同于这里描述的IF语句。
4.12.2 CASE语句
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list]END CASEOr: CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list]END CASE
存储程序的CASE语句实现一个复杂的条件构造。如果search_condition求值为真,相应的SQL被执行。如果没有搜索条件匹配,在ELSE子句里的语句被执行。
4.12.3 LOOP语句
[begin_label:] LOOP statement_listEND LOOP [end_label]
LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造。在循环内的语句一直重复直循环被退出,退出循环通常伴随着一个LEAVE语句。
LOOP语句可以被标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们必须是同样的。
4.12.4 LEAVE语句
LEAVE label
这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。
4.12.5 ITERATE语句
ITERATE label
ITERATE只可以出现在LOOP,REPEAT, 和WHILE语句内。ITERATE意思为:“再次循环”。
例如:
CREATE PROCEDURE doiterate(p1 INT)BEGIN label1: LOOP SET p1 = p1 + 1; IF p1
[begin_label:] REPEAT statement_listUNTIL search_conditionEND REPEAT [end_label]
REPEAT语句内的语句或语句群被重复,直至search_condition为真。
REPEAT 语句可以被标注。除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。
例如:
mysql> delimiter //mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; -> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; -> END -> //mysql> CALL dorepeat(1000)//mysql> SELECT @x//1.12.7 WHILE语句
[begin_label:] WHILE search_condition DO statement_listEND WHILE [end_label]
WHILE语句内的语句或语句群被重复,直至search_condition为真。
WHILE语句可以被标注。 除非begin_label也存在,end_label才能被用,如果两者都存在,它们必须是一样的。
例如:
CREATE PROCEDURE dowhile()BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE;END5. 课后作业
填写代码的时候挖了一个坑,因为有些代码需要完整的实现前提是建立好相关的数据库和表,因此建议同学们把以上语法和代码完整的实现并截图,因为这样有利于理解相对枯燥的语法。
动手实践是学习 IT 技术最有效的方式!