MySQL心得7-1-存储过程_MySQL

bitsCN.com MySQL心得7-1-存储过程 1. 使用存储过程的优点有： （1）存储过程在服务器端运行，执行速度快。 （2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 （3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。 2.创建存储过程可以使用create procedure语句。 要在MySQL 5.1中创建存储过程，必须具有CREATE routine权限。要想查看数据库中有哪些存储过程，可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息，可使用SHOWCREATE PROCEDURE sp_name命令，其中sp_name是存储过程的名称。 CREATE PROCEDURE的语法格式： CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body 其中，proc_parameter的参数如下： [ IN | OUT | INOUT ] param_name type characteristic特征如下： language SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' 说明： ● sp_name：存储过程的名称，默认在当前数据库中创建。需要在特定数据库中创建存储过程时，则要在名称前面加上数据库的名称，格式为：db_name.sp_name。值得注意的是，这个名称应当尽量避免取与MySQL的内置函数相同的名称，否则会发生错误。 ● proc_parameter：存储过程的参数，param_name为参数名，type为参数的类型，当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三种类型的参数：输入参数、输出参数和输入/输出参数，关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候，存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数，但是名称后面的括号是不可省略的。 注意：参数的名字不要等于列的名字，否则虽然不会返回出错消息，但是存储过程中的SQL语句会将参数名看做列名，从而引发不可预知的结果。 characteristic：存储过程的某些特征设定，下面一一介绍: language sql：表明编写这个存储过程的语言为SQL语言，目前来讲，MySQL存储过程还不能用外部编程语言来编写，也就是说，这个选项可以不指定。将来将会对其扩展，最有可能第一个被支持的语言是PHP。 deterministic：设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果，设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。 contains SQL：表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句，但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。 SQL SECURITY：SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。默认值是DEFINER。 COMMENT 'string'：对存储过程的描述，string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。 ● routine_body：这是存储过程的主体部分，也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句，这个部分总是以begin开始，以end结束。当然，当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。 3. 在开始创建存储过程之前，先介绍一个很实用的命令，即delimiter命令。在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。 DELIMITER语法格式为：DELIMITER $$ 说明：$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，一个“￥”、数字、字母等都可以。当使用DELIMITER命令时，应该避免使用反斜杠（“/”）字符，因为那是MySQL的转义字符。 例：创建存储过程，实现的功能是删除一个特定学生的信息。 DELIMITER $$ CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6)) BEGIN DELETE FROM XS WHERE 学号=XH; END $$ DELIMITER ; 说明：当调用这个存储过程时，MySQL根据提供的参数XH的值，删除对应在XS表中的数据。 在关键字BEGIN和END之间指定了存储过程体，当然，BEGIN-END复合语句还可以嵌套使用。 4. 局部变量 在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用declare语句。在声明局部变量的同时也可以对其赋一个初始值。 DECLARE语法格式：DECLARE var_name[,...] type [DEFAULT value] 说明：var_name为变量名；type为变量类型；default子句给变量指定一个默认值，如果不指定默认为NULL的话。可以同时声明多个类型相同的局部变量，中间用逗号隔开。 例： 声明一个整型变量和两个字符变量。 DECLARE num INT(4); DECLARE str1, str2 VARCHAR(6); declare n char(10) default ‘abcdefg’; 说明：局部变量只能在BEGIN…END语句块中声明。 局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGIN…END语句块中使用该变量，其他语句块中不可以使用它。 在存储过程中也可以声明用户变量，不过千万不要将这两个混淆。局部变量和用户变量的区别在于：局部变量前面没有使用@符号，局部变量在其所在的BEGIN…END语句块处理完后就消失了，而用户变量存在于整个会话当中。 5. 使用SET语句赋值 要给局部变量赋值可以使用SET语句，SET语句也是SQL本身的一部分。语法格式为：SET var_name = expr [,var_name = expr] ... 例: 在存储过程中给局部变量赋值。 SET num=1, str1= 'hello'; 说明：与声明用户变量时不同，这里的变量名前面没有@符号。注意，例中的这条语句无法单独执行，只能在存储过程和存储函数中使用。 6. SELECT...INTO语句（重点） 使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。语法格式为： SELECT col_name[,...] INTO var_name[,...] table_expr 说明：col_name是列名，var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分，这里不再叙述。 例: 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。 SELECT 姓名,专业名 INTO name, project FROMXS; WHERE 学号= '081101'; 7. 流程控制语句 在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句、iterate语句和LEAVE语句。 （1）IF语句 IF-THEN-ELSE语句可根据不同的条件执行不同的操作。 语法格式为： IF 判断的条件THEN 一个或多个SQL语句 [ELSEIF判断的条件THEN一个或多个SQL语句] ... [ELSE一个或多个SQL语句] END IF 说明：当判断条件为真时，就执行相应的SQL语句。 IF语句不同于系统的内置函数IF()函数，IF()函数只能判断两种情况，所以请不要混淆。 例： 创建XSCJ数据库的存储过程，判断两个输入的参数哪一个更大。 DELIMITER $$ CREATE PROCEDURE XSCJ.COMPAR (IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) ) BEGIN IFK1>K2 THEN SET K3= '大于'; ELSEIFK1=K2 THEN SET K3= '等于'; ELSE SET K3= '小于'; ENDIF; END$$ DELIMITER ; 说明：存储过程中K1和K2是输入参数，K3是输出参数。 （2）CASE语句 前面已经介绍过了，这里介绍CASE语句在存储过程中的用法，与之前略有不同。语法格式为： CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE 或者： CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE 说明：一个CASE语句经常可以充当一个IF-THEN-ELSE语句。 第一种格式中case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的when_value参数指定要与case_value比较的值，如果为真，就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。 第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，search_condition指定了一个比较表达式，表达式为真时执行THEN后面的语句。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更方便。 例： 创建一个存储过程，针对参数的不同，返回不同的结果。 DELIMITER $$ CREATE PROCEDURE XSCJ.RESULT (IN str VARCHAR(4), OUT sex VARCHAR(4) ) BEGIN CASE str WHEN'M' THEN SET sex='男'; WHEN'F' THEN SET sex='女'; ELSE SET sex='无'; ENDCASE; END$$ DELIMITER ; 例： 用第二种格式的CASE语句创建以上存储过程。程序片段如下： CASE WHENstr='M' THEN SET sex='男'; WHENstr='F' THEN SET sex='女'; ELSE SET sex='无'; END CASE; （3）循环语句 MySQL支持3条用来创建循环的语句：while、repeat和loop语句。在存储过程中可以定义0个、1个或多个循环语句。 ● WHILE语句语法格式为： [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label] 说明：语句首先判断search_condition是否为真，不为真则执行statement_list中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。begin_label和end_label是WHILE语句的标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们的名字必须是相同的。 例: 创建一个带WHILE循环的存储过程。 DELIMITER $$ CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT5; WHILE v1 > 0 DO SET v1 = v1-1; END WHILE; END $$ DELIMITER ; ● repeat语句格式如下： [begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label] 说明：REPEAT语句首先执行statement_list中的语句，然后判断search_condition是否为真，为真则停止循环，不为真则继续循环。REPEAT也可以被标注。 例: 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下： REPEAT v1=v1-1; UNTIL v1
作者 tianyazaiheruan bitsCN.com