MYSQL存储过程技术课件.ppt

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_name 存储过程的

4、名字proc_parameter指定参数为IN，OUT，或INOUTcharacteristic 特征routine_body 包含合法的SQL过程语句。DROP PROCEDURE|FUNCTION IF EXISTS sp_name这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL 5.1中，你必须有ALTER ROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。IF EXISTS 子句是一个MySQL的扩展。如果程序或函数不存在，它防止发生错误。基本的创建、调用、删除语法：基本的创建、调用、删除语法：delimiter/DROP PROC

5、EDURE 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);/*SQL语句*/SELECT name FROM t1 LIMIT 1 INTO outpa

6、rams;END/delimiter;call test(1,out);存储过程的变量：存储过程的变量：l 声明变量：DECLARE var_name,.type DEFAULT value这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。局部变量的作用范围在它被声明的BEGIN.END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。l 变量赋值，SET语句：SET var_name=expr,var_name=expr.也可以用语句代替SET来为用户变量分

7、配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符，如下所示：mysql SET t1=0,t2=0,t3=0;mysql SELECT t1:=0,t2:=0,t3:=0;对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过值，则为NULL。l 变量赋值，SELECT.INTO语句SELECT col_name,.INTO var_name,.table_expr这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。SELECT id,data INTO

8、 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不能被给出，并且如果二者都存在，他们必须是同样的。使用多重语句需要客户端能发送包含语句定界符;的查询字符串。这个符号在命令行客户端被用delimiter命令来处

9、理。改变查询结尾定界符;（比如改变为/）使得;可被用在子程序体中。流程控制结构语句：流程控制结构语句：l IF语句IF search_condition THEN statement_list ELSEIF search_condition THEN statement_list.ELSE statement_listEND IF IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。举例：DELIMITER/CR

10、EATE 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+2;END IF;END;/DELIMITER;流程控制结构语句：流程控制结构语句：l CASE语句CASE case_value WHEN when_value THEN stat

11、ement_list WHEN when_value THEN statement_list.ELSE statement_listEND CASE Or:CASE WHEN search_condition THEN statement_list WHEN search_condition THEN statement_list.ELSE statement_listEND CASE 存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。举例：CREATE PROCEDURE p2

12、(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(19);END CASE;END;/流程控制结构语句：流程控制结构语句：l 循环语句WHILE END WHILELOOP END LOOPREPEAT END REPEATGOTO前三种是标准的循环方式，至于GOTO就如

13、C语言里的GOTO一样，尽量少用！在循环中还穿插一些循环控制语句，如LEAVE（类似C语言的break）、ITERATE（类似C语言的continue）等。LEAVE语句LEAVE label 这个语句被用来退出任何被标注的流程控制构造。它和BEGIN.END或循环一起被使用。ITERATE语句ITERATE label ITERATE只可以出现在LOOP,REPEAT,和WHILE语句内。ITERATE意思为：再次循环。流程控制结构语句：流程控制结构语句：l 循环语句WHILE END WHILE 举例：CREATE PROCEDURE p4()BEGIN DECLARE v INT;SET

14、 v=0;WHILE v=5 THEN LEAVE loop_label;END IF;END LOOP;END;/begin_label:LOOP statement_listEND LOOP end_labelLOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE 语句。流程控制结构语句：流程控制结构语句：l 循环语句REPEAT END REPEAT 举例：CREATE PROCEDURE p6()BEGIN DECLARE v INT;SET v=0;REPEAT INSERT INTO t VALUES(v

15、);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 THEN SET v=v+1;ITERATE loop_label;END IF;INSERT INTO t VALUES(v);SET v=v+1;IF v=5 THEN LEAVE loop_label;EN

16、D IF;END LOOP;END;/注释语法：注释语法：mysql存储过程可使用两种风格的注释双模杠：-，该风格一般用于单行注释c风格：/*注释内容*/，一般用于多行注释使用权限：使用权限：关于存储过程的权限管理，一种是SQL SECURITY INVOKER，一种是SQL SECURITY DEFINER,也就是一种是调用者权限，一种是定义者权限，如果使用第一种，那么执行的时候是以执行者本身的权限来操作存储过程中包含的表。如果是第二种，那么执行的时候，是以该存储过程的定义者权限来操作。条件和异常处理程序：条件和异常处理程序：DECLARE handler_type HANDLER FOR

17、condition_value,.sp_statement handler_type:CONTINUE|EXIT condition_value:SQLSTATE VALUE sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN.END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。SQLWARNING是对所

18、有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。声明自定义条件：DECLARE condition_name CONDITION FOR condition_valuecondition_value:SQLSTATE VALUE sqlstate_value条件和异常处理程序：条件和异常处理程序：举例：CREATE TABLE test.t(s1 int,primary key(s1);delimiter/CREATE PR

19、OCEDURE 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;/delimiter;游标：游标：声明游标DECLARE cursor_name CURSOR FOR select_statement这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。打开游标OPEN cursor_nam

20、e这个语句打开先前声明的光标。游标FETCHFETCH cursor_name INTO var_name,var_name.这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。关闭游标CLOSECLOSE cursor_name这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。游标的特性：READ ONLY 只读，只能取值而不能赋值；NOT SCROOLABLE 不可回滚，只能顺序读取；ASENSITIVE 敏感，不能在已经打开游标的表上执行update事务；游标：游标：举例：CREATE PROCEDURE curdemo()BEG

21、IN 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 c THEN INSERT INTO test.t3 VALUES(a,b);ELSE INSERT INTO test.t3 VALUES(a,c);END IF;END IF;UNTIL done END REPEAT;CLOSE cur1;CLOSE cur2;END谢谢 谢谢