MySQL 存储过程

一、引言

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

  1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
  2. 存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
  3. 存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
  4. 存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
  5. 存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

二、关于 MySQL 的存储过程

MySQL在5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

三、创建 MySQL 存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
  • DEFINER: 默认为当前用户,如果指定 user 格式为 'user_name'@'host_name',MySQL 5.0.3后在 SQL SECURITY INVOKER 模式(默认为 DEFINER )下运行该存储过程需要有相应EXECUTE权限(存储过程有关的权限有三种:ALTER ROUTINE 编辑或删除存储过程;CREATE ROUTINE 建立存储过程;EXECUTE 运行存储过程 )
  • sp_name:存储过程的名称,可以使用修改
  • proc_parameter:参数列表,格式为 ( [ IN | OUT | INOUT ] param type) ,例如(IN param1 int)
  • characteristic:存储特性,包含有:
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
  • routine_body:SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。

创建存储过程实例:

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

 此存储过程名称为 simpleproc,有一个输出参数 param1 类型为int。Select 语句查询表 into 的记录并存入变量 param1 并输入。

  • DELIMITER:需要注意的是 delimiter //delimiter ; DELIMITER 声明分隔符的意思,MySQL 默认的分隔符是; ,上例如果没有声明分隔符为 / /编译器就会将存储过程的一部分当作 SQL 语句执行,导致错误。在存储过程定义前,将 MySQL 的分隔符申明为// (或其他字符,如 && ),在创建完成后,重新恢复 MySQL 的默认分隔符。
  • IN, OUT, INOUT:IN 表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回;OUT:该值可在存储过程内部被改变,并可返回;INOUT:调用时指定,并且可被改变和返回
    • IN 参数实例
      mysql > DELIMITER //
      
      mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
           -> BEGIN   
           -> SELECT p_in;   
           -> SET p_in=2;   
           -> SELECT p_in;   
           -> END;   
           -> //
      Query OK, 0 rows affected (0.00 sec)
        
      mysql > DELIMITER ;
      mysql > SET @p_in=1;  
      mysql > CALL demo_in_parameter(@p_in);  
      +------+  
      | p_in |  
      +------+  
      |   1  |   
      +------+  
       
      +------+  
      | p_in |  
      +------+  
      |   2  |   
      +------+  
       
      mysql> SELECT @p_in;  
      +-------+  
      | @p_in |  
      +-------+  
      |  1    |  
      +-------+  

      以上可以看出,p_in 虽然在存储过程中被修改,但并不影响 @p_id 的值

    • OUT 参数实例
      mysql > DELIMITER //  
      
      mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
           -> BEGIN 
           -> SELECT p_out;  
           -> SET p_out=2;  
           -> SELECT p_out;  
           -> END;  
           -> //  
      Query OK, 0 rows affected (0.00 sec)
      
      mysql > DELIMITER ;
      mysql > SET @p_out=1;  
      mysql > CALL sp_demo_out_parameter(@p_out);  
      +-------+  
      | p_out |   
      +-------+  
      | NULL  |   
      +-------+  
       
      +-------+  
      | p_out |  
      +-------+  
      |   2   |   
      +-------+  
       
      mysql> SELECT @p_out;  
      +-------+  
      | p_out |  
      +-------+  
      |   2   |  
      +-------+  

      以上可以看出,p_out 无法传入存储过程内部

    • INOUT 实例
      mysql > DELIMITER //
      
      mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
           -> BEGIN 
           -> SELECT p_inout;  
           -> SET p_inout=2;  
           -> SELECT p_inout;   
           -> END;  
           -> //  
      Query OK, 0 rows affected (0.00 sec)
      
      mysql > DELIMITER ;
      mysql > SET @p_inout=1;  
      mysql > CALL demo_inout_parameter(@p_inout) ;  
      +---------+  
      | p_inout |  
      +---------+  
      |    1    |  
      +---------+  
       
      +---------+  
      | p_inout |   
      +---------+  
      |    2    |  
      +---------+  
       
      mysql > SELECT @p_inout;  
      +----------+  
      | @p_inout |   
      +----------+  
      |    2     |  
      +----------+ 

      以上可以看出,p_inout 能被出入存储过程,且 p_inout 值的改变也影响 @p_inout 的值。

  • DECLARE:变量定义,格式为 variable_name [,variable_name...] datatype [DEFAULT value];。例如:
    • DECLARE l_int int unsigned default 4000000;
    • DECLARE l_numeric number(8,2) DEFAULT 9.95;  
    • DECLARE l_date date DEFAULT '1999-12-31';  
    • DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
  • 变量赋值:
    • SET variable_name = expression ...。例如 SET p_inout=2;  
    • 使用 SELECT…INTO 语句,SELECT 查询列或复合函数 INTO 变量名 FROM TABLE;。例如 SELECT COUNT(*) INTO param1 FROM t;

四、修改 MySQL 存储过程

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

MySQL 存储过程的只允许修改 名称(proc_name)和特性(characteristic),如果需要修改其他的内容必须删除重建。
修改存储过程实例:(存储过程 simpleproc 已在上文中创建)

mysql> ALTER PROCEDURE simpleproc 
    -> MODIFIES SQL DATA 
    -> SQL SECURITY INVOKER ; 
Query OK, 0 rows affected (0.00 sec) 

三、调用 MySQL 存储过程

call sp_name();  
  • sp_name:存储过程的名称,括号内包含存储过程的参数

四、查看 MySQL 存储过程

  1. 查看存储过程
    select name from mysql.proc where db=’db_name’;

    or

    select routine_name from information_schema.routines where routine_schema='db_name';

    or

    show procedure status where db='db_name';
  2. 查看存储过程详细
    SHOW CREATE PROCEDURE

五、删除 MySQL存储过程

DROP PROCEDURE proc_name

删除存储过程一般使用 drop procedure,也可以类似上文中查看,从表中删除存储过程。

 

posted @ 2014-11-06 15:14  TannerBG  阅读(333)  评论(0编辑  收藏  举报