当前位置:Gxlcms > 数据库问题 > mysql 存储过程和存储函数

mysql 存储过程和存储函数

时间:2021-07-01 10:21:17 帮助过:13人阅读

  •           END 
  • 上述代码中,存储过程名称为num_from_employee;输入变量为emp_id;输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录,并用COUNT(*)计算d_id值相同的记录的条数,最后将计算结果存入count_num中。代码的执行结果如下:

    1. mysql> DELIMITER &&  
    2. mysql> CREATE  PROCEDURE  num_from_employee
      (IN emp_id INT, OUT count_num INT )  
    3.     -> READS SQL DATA  
    4.     -> BEGIN  
    5.     -> SELECT  COUNT(*)  INTO  count_num  
    6.     -> FROM  employee  
    7.     -> WHERE  d_id=emp_id ;  
    8.     -> END &&  
    9. Query OK, 0 rows affected (0.09 sec)  
    10. mysql> DELIMITER ; 

    代码执行完毕后,没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程,数据库中会执行存储过程中的SQL语句。

    说明:MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来    结束。为了避免冲突,首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。

    14.1.2 创建存储函数

    在MySQL中,创建存储函数的基本形式如下:

    1. CREATE FUNCTION sp_name ([func_parameter[,...]])  
    2.         RETURNS type  
    3.         [characteristic ...] routine_body 

    其中,sp_name参数是存储函数的名称;func_parameter表示存储函数的参数列表;RETURNS type指定返回值的类型;characteristic参数指定存储函数的特性,该参数的取值与存储过程中的取值是一样的,请读者参照14.1.1小节的内容;routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。

    func_parameter可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下:

    1. param_name type 

    其中,param_name参数是存储函数的参数名称;type参数指定存储函数的参数类型,该类型可以是MySQL数据库的任意数据类型。

    【示例14-2】 下面创建一个名为name_from_employee的存储函数。代码如下:

    1. CREATE  FUNCTION  name_from_employee (emp_id INT )  
    2.           RETURNS VARCHAR(20)  
    3.           BEGIN  
    4.               RETURN  (SELECT  name  
    5.               FROM  employee  
    6.               WHERE  num=emp_id );  
    7.           END 

    上述代码中,存储函数的名称为name_from_employee;该函数的参数为emp_id;返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录,并将该记录的name字段的值返回。代码的执行结果如下:

    1. mysql> DELIMITER &&  
    2. mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )  
    3.     -> RETURNS VARCHAR(20)  
    4.     -> BEGIN  
    5.     -> RETURN  (SELECT  name  
    6.     -> FROM  employee  
    7.     -> WHERE  num=emp_id );  
    8.     -> END&&  
    9. Query OK, 0 rows affected (0.00 sec)  
    10. mysql> DELIMITER ; 

    结果显示,存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。

      

    14.1.3  变量的使用

    在存储过程和函数中,可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

    1.定义变量

    MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下:

    1. DECLARE  var_name[,...]  type  [DEFAULT value] 

    其中, DECLARE关键字是用来声明变量的;var_name参数是变量的名称,这里可以同时定义多个变量;type参数用来指定变量的类型;DEFAULT value子句将变量默认值设置为value,没有使用DEFAULT子句时,默认值为NULL。

    【示例14-3】 下面定义变量my_sql,数据类型为INT型,默认值为10。代码如下:

    1. DECLARE  my_sql  INT  DEFAULT 10 ; 

    2.为变量赋值

    MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下:

    1. SET  var_name = expr [, var_name = expr] ... 

    其中,SET关键字是用来为变量赋值的;var_name参数是变量的名称;expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开。

    【示例14-4】 下面为变量my_sql赋值为30。代码如下:

    1. SET  my_sql = 30 ; 

    MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下:

    1. SELECT  col_name[,…]  INTO  var_name[,…]  
    2.     FROM  table_name  WEHRE  condition 

    其中,col_name参数表示查询的字段名称;var_name参数是变量的名称;table_name参数指表的名称;condition参数指查询条件。

    【示例14-5】 下面从employee表中查询id为2的记录,将该记录的d_id值赋给变量my_sql。代码如下:

    1. SELECT  d_id  INTO  my_sql  
    2.         FROM  employee  WEHRE  id=2 ; 

     

    14.1.4  定义条件和处理程序

    定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题,并提出解决办法。这样可以增强程序处理问题的能力,避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

    1.定义条件

    MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下:

    1. DECLARE  condition_name  CONDITION  FOR  condition_value  
    2. condition_value:  
    3.       SQLSTATE [VALUE] sqlstate_value | mysql_error_code 

    其中,condition_name参数表示条件的名称;condition_value参数表示条件的类型;sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。

    【示例14-6】 下面定义"ERROR 1146 (42S02)"这个错误,名称为can_not_find。可以用两种不同的方法来定义,代码如下:

    1. //方法一:使用sqlstate_value  
    2. DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  ‘42S02‘ ;  
    3. //方法二:使用mysql_error_code  
    4. DECLARE  can_not_find  CONDITION  FOR  1146 ; 

    2.定义处理程序

    MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下:

    1. DECLARE handler_type HANDLER FOR 
      condition_value[,...] sp_statement  
    2. handler_type:  
    3.     CONTINUE | EXIT | UNDO  
    4. condition_value:  
    5.     SQLSTATE [VALUE] sqlstate_value |
      condition_name  | SQLWARNING  
    6.        | NOT FOUND  | SQLEXCEPTION  | mysql_error_code 

    其中,handler_type参数指明错误的处理方式,该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理,继续向下执行;EXIT表示遇到错误后马上退出;UNDO表示遇到错误后撤回之前的操作,MySQL中暂时还不支持这种处理方式。

    注意:通常情况下,执行过程中遇到错误应该立刻停止执行下面的语句,并且撤回前面的操作。但是,MySQL中现在还不能支持UNDO操作。因此,遇到错误时最好执行EXIT操作。如果事先能够预测错误类型,并且进行相应的处理,那么可以执行CONTINUE操作。

    condition_value参数指明错误类型,该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

    【示例14-7】 下面是定义处理程序的几种方式。代码如下:

    1. //方法一:捕获sqlstate_value  
    2. DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02‘
      SET @info=‘CAN NOT FIND‘;  
    3. //方法二:捕获mysql_error_code  
    4. DECLARE CONTINUE HANDLER FOR 1146 SET @info=‘CAN NOT FIND‘;  
    5. //方法三:先定义条件,然后调用  
    6. DECLARE  can_not_find  CONDITION  FOR  1146 ;  
    7. DECLARE CONTINUE HANDLER FOR can_not_find SET 
      @info=‘CAN NOT FIND‘;  
    8. //方法四:使用SQLWARNING  
    9. DECLARE EXIT HANDLER FOR SQLWARNING SET @info=‘ERROR‘;  
    10. //方法五:使用NOT FOUND  
    11. DECLARE EXIT HANDLER FOR NOT FOUND SET @info=‘CAN NOT FIND‘;  
    12. //方法六:使用SQLEXCEPTION  
    13. DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=‘ERROR‘; 

    上述代码是6种定义处理程序的方法。第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02,执行CONTINUE操作,并且输出"CAN NOT FIND"信息。第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146,执行CONTINUE操作,并且输出"CAN NOT FIND"信息。第三种方法是先定义条件,然后再调用条件。这里先定义can_not_find条件,遇到1146错误就执行CONTINUE操作。第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息。第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出"CAN NOT FIND"信息。第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息。

     

    条件判断 
    IF THEN、ELSEIF、ELSE、END IF

    DELIMITER $$ 

    DROP PROCEDURE IF EXISTS discounted_price$$ 
    CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2)) 
    BEGIN 
    IF (normal_price > 500) THEN 
    SET discount_price = normal_price * .8; 
    ELSEIF (normal_price > 100) THEN 
    SET discount_price = normal_price * .9; 
    ELSE 
    SET discount_price = normal_price; 
    END IF; 
    END$$ 

    DELIMITER ;


    循环 
    LOOP、END LOOP

    DELIMITER $$ 

    DROP PROCEDURE IF EXISTS simple_loop$$ 

    CREATE PROCEDURE simple_loop(OUT counter INT) 
    BEGIN 
    SET counter = 0; 
    my_simple_loop: LOOP 
    SET counter = counter+1; 
    IF counter = 10 THEN 
    LEAVE my_simple_loop; 
    END IF; 
    END LOOP my_simple_loop; 
    END$$ 

    DELIMITER ;


    WHILE DO、END WHILE

    DELIMITER $$ 

    DROP PROCEDURE IF EXISTS simple_while$$ 

    CREATE PROCEDURE simple_while(OUT counter INT) 
    BEGIN 
    SET counter = 0; 
    WHILE counter != 10 DO 
    SET counter = counter+1; 
    END WHILE; 
    END$$ 

    DELIMITER ;


    REPEAT、UNTILL

    DELIMITER $$ 

    DROP PROCEDURE IF EXISTS simple_repeat$$ 

    CREATE PROCEDURE simple_repeat(OUT counter INT) 
    BEGIN 
    SET counter = 0; 
    REPEAT 
    SET counter = counter+1; 
    UNTIL counter = 10 END REPEAT; 
    END$$ 

    DELIMITER ;


    CURSOR用于处理多行记录的查询结果

    DELIMITER $$ 

    DROP PROCEDURE IF EXITS cursor_example$$ 
    CREATE PROCEDURE cursor_example() 
    READS SQL DATA 
    BEGIN 
    DECLARE l_employee_id INT; 
    DECLARE l_salary NUMERIC(8,2); 
    DECLARE l_department_id INT; 
    DECLARE done INT DEFAULT 0; 
    DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees; 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 

    OPEN cur1; 
    emp_loop: LOOP 
    FETCH cur1 INTO l_employee_id, l_salary, l_department_id; 
    IF done=1 THEN 
    LEAVE emp_loop; 
    END IF; 
    END LOOP emp_loop; 
    CLOSE cur1; 
    END$$ 
    DELIMITER ;


    SELECT语句用于存储过程返回结果集

    DELIMITER $$ 
    DROP PROCEDURE IF EXISTS sp_emps_in_dept$$ 
    CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT) 
    BEGIN 
    SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id; 
    END$$ 

    DELIMITER ;


    UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里

    DELIMITER $$ 

    DROP PROCEDURE IF EXITS sp_update_salary$$ 
    CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2)) 
    BEGIN 
    IF in_new_salary < 5000 OR in_new_salary > 500000 THEN 
    SELECT "Illegal salary: salary must be between $5000 and $500, 000"; 
    ELSE 
    UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id; 
    END IF: 
    END$$ 

    DELIMITER ;


    使用CALL调用存储程序

    DELIMITER $$ 

    DROP PROCEDURE IF EXISTS call_example$$ 
    CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20)) 
    NO SQL 
    BEGIN 
    DECLARE l_bonus_amount NUMERIC(8,2); 

    IF employee_type=‘MANAGER‘ THEN 
    CALL calc_manager_bonus(employee_id, l_bonus_amount); 
    ELSE 
    CALL calc_minion_bonus(employee_id, l_bonus_amount); 
    END IF; 
    CALL grant_bonus(employee_id, l_bonus_amount); 
    END$$ 
    DELIMITER ;

     

     

    存储过程和存储函数的区别:

    1、总述
    存储函数和存储过程统称为存储例程(stored routine)。两者的定义语法很相似,但却是不同的内容。
    存储函数限制比较多,比如不能用临时表,只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少。
    一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。


    2、返回值上的不同
    存储函数将向调用者返回一个且仅返回一个结果值。
    存储过程将返回一个或多个结果集(函数做不到这一点),或者只是来实现某种效果或动作而无需返回值。


    3、调用方式上的不同
    存储函数嵌入在sql中使用的,可以在select中调用,就像内建函数一样,比如cos()、hex()
    存储过程只能通过call语句进行调用


    4、参数的不同
    存储函数的参数类型类似于IN参数
    存储过程的参数类型有三种、IN参数、OUT参数、INOUT参数 

     

    参考:http://www.cnblogs.com/exmyth/p/3303470.html

    参考:http://fyb613.blog.163.com/blog/static/325460922010044417672/

    mysql 存储过程和存储函数

    标签:复杂   基本语法   开始   结束   过程   find   临时表   应该   param   

    人气教程排行