《mysql存储过程技术》由会员分享，可在线阅读，更多相关《mysql存储过程技术（21页珍藏版）》请在金锄头文库上搜索。
1、老 狼 ,存储过程的概念 为什么使用存储过程 基本创建、删除语法 存储过程中的变量 BEGIN.END复合语句 流程控制结构语句 注释语法 使用权限 条件和异常处理程序 游标,存储过程的概念： 所谓的存储过程就是存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。 与自定义函数的区别： 自定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用。 存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。 执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过
2、程不行。 函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少。 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。,为什么使用存储过程： 存储过程的优点： 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 当对数据
3、库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 存储过程可以重复使用，可减少数据库开发人员的工作量。 安全性高，可设定只有某此用户才具有对指定存储过程的使用权。,基本的创建、删除语法： CREATE PROCEDURE sp_name (proc_parameter,.) characteristic . routine_body 默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。 sp_nam
4、e 存储过程的名字 proc_parameter指定参数为IN， OUT，或INOUT characteristic 特征 routine_body 包含合法的SQL过程语句。 DROP PROCEDURE | FUNCTION IF EXISTS sp_name 这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL 5.1中，你必须有ALTER ROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。 IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误。,基本的创建、调用、删除语法： delimiter / DRO
5、P PROCEDURE IF EXISTS test / CREATE PROCEDURE test /* 存储过程名 */ (IN inparms INT, OUT outparams varchar(32) /* 输入参数 */ BEGIN /* 语句块头 */ DECLARE var CHAR(10); /* 变量声明 */ IF inparms = 1 THEN /* IF条件开始*/ SET var = hello; /* 赋值 */ ELSE SET var = world; END IF; /* IF结束 */ INSERT INTO t1 VALUES (var); /* SQ
6、L语句 */ SELECT name FROM t1 LIMIT 1 INTO outparams; END / delimiter ; call test(1, out);,存储过程的变量： 声明变量： DECLARE var_name,. type DEFAULT value 这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。 局部变量的作用范围在它被声明的BEGIN . END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。 变量赋值，SET语句： S
7、ET var_name = expr , var_name = expr . 也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符，如下所示： mysql SET t1=0, t2=0, t3=0; mysql SELECT t1:=0,t2:=0,t3:=0; 对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过值，则为NULL。 变量赋值，SELECT . INTO语句 SELECT col_name,. INTO var_name,. t
8、able_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。 SELECT id,data INTO x,y FROM test.t1 LIMIT 1;,BEGIN.END复合语句： begin_label: BEGIN statement_list END end_label 存储子程序可以使用BEGIN . END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。 复合语句可以被标记。除非begin_label存在，否则end_label不能被给出，并且如果二者
9、都存在，他们必须是同样的。 使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处理。改变查询结尾定界符;（比如改变为/）使得; 可被用在子程序体中。,流程控制结构语句： IF语句 IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list . ELSE statement_list END IF IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condi
10、tion匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。 举例： DELIMITER / CREATE PROCEDURE p1(IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 +
11、 2; END IF; END; / DELIMITER ;,流程控制结构语句： CASE语句 CASE case_value WHEN when_value THEN statement_list WHEN when_value THEN statement_list . ELSE statement_list END CASE Or: CASE WHEN search_condition THEN statement_list WHEN search_condition THEN statement_list . ELSE statement_list END CASE 存储程序的CASE
12、语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。 举例： CREATE PROCEDURE p2 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (
13、19); END CASE; END; /,流程控制结构语句： 循环语句 WHILE END WHILE LOOP END LOOP REPEAT END REPEAT GOTO 前三种是标准的循环方式，至于GOTO就如C语言里的GOTO一样，尽量少用！ 在循环中还穿插一些循环控制语句，如LEAVE（类似C语言的break）、ITERATE（类似C语言的continue）等。 LEAVE语句 LEAVE label 这个语句被用来退出任何被标注的流程控制构造。它和BEGIN . END或循环一起被使用。 ITERATE语句 ITERATE label ITERATE只可以出现在LOOP, RE
14、PEAT, 和WHILE语句内。ITERATE意思为：再次循环。,流程控制结构语句： 循环语句 WHILE END WHILE 举例： CREATE PROCEDURE p4 () BEGIN DECLARE v INT; SET v = 0; WHILE v 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; /,流程控制结构语句： 循环语句 LOOP END LOOP 举例： CREATE PROCEDURE p5 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP
15、INSERT INTO t VALUES (v); SET v = v + 1; IF v = 5 THEN LEAVE loop_label; END IF; END LOOP; END; / begin_label: LOOP statement_list END LOOP end_label LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE 语句。,流程控制结构语句： 循环语句 REPEAT END REPEAT 举例： CREATE PROCEDURE p6 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v = 5 END REPEAT; END; / 功能与WHILE差不多，差别是在执行一次后检查，而WHILE是在开始时检查，累死DOWHILE功能。,流程控制结构语句： 补充：迭代（ITERATE）语句 CREATE PROCEDURE p7 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3
