chapter12_数据库编程_3_存储过程

存储过程

(1) 存储过程是一个程序代码，存储在数据库中

(2) 作用

1° 接受输入参数，并以输出参数的格式向调用过程返回多个值

2° 包含用于在数据库中执行操作(包括调用其他过程)的编程语句

3° 向调用过程返回状态值，指明成功或失败

(3) 优点

1° 存储过程已经在服务器中存储，执行效率更高

2° 存储过程和视图等类似，是一种可以授权的数据库对象

3° 用户可以被授予权限来执行存储过程，而不必直接对存储过程中引用的对象具有权限

4° 加强安全性

5° 可以进行模块化设计

6° 允许延迟绑定，创建引用尚不存在的表的存储过程，直到第一次执行该存储过程时再编译

7° 减少通信流量

因此，应用程序中应尽量使用存储过程完成对数据库的操作

MYSQL中的存储过程

(1) 创建存储过程

  CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

其中

  proc_parameter: [ IN | OUT | INOUT ] param_name type

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

  routine_body:
      Valid SQL routine statement

示例1

  CREATE PROCEDURE simpleproc (OUT param1 INT)
  BEGIN
      SELECT COUNT(*) INTO param1 FROM t;
  END

示例2

  CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
  BEGIN

      SELECT VERSION() INTO ver_param;
      SET incr_param = incr_param + 1;
  END;

(2) By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it

(3) To invoke a stored procedure, use the CALL statement.调用一个存储过程，使用CALL()

示例

  mysql> SET @increment = 10;
  mysql> CALL p(@version, @increment);
  mysql> SELECT @version, @increment;

  +------------------+------------+
  | @version         | @increment |
  +------------------+------------+
  | 5.7.20-debug-log |         11 |
  +------------------+------------+

(4) CREATE PROCEDURErequire the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value, as described later in this section

(5) If the routine name is the same as the name of a built-in SQL function, a syntax error occurs unless you use a space between the name and the following parenthesis when defining the routine or invoking it later. For this reason, avoid using the names of existing SQL functions for your own stored routines

(6) The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.

(7) Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.

(8)

1° An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns;

2° An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns;

3° An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.

(9) If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an OUT or INOUT parameter;

If you are calling the procedure from within a trigger, you can also pass NEW.col_name as an OUT or INOUT parameter.

(10) The routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT or INSERT, or a compound statement written using BEGIN and END. Compound statements can contain declarations, loops, and other control structure statements

(11) MySQL permits routines to contain DDL statements, such as CREATE and DROP. MySQL also permits stored procedures (but not stored functions) to contain SQL transaction statements such as COMMIT. .

(12) Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE.

(13) USE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name.

(14) 删除存储过程： DROP PROCEDURE dbname.proname;

(15) 返回多个结果集：只SELECT，不赋值给变量

示例

  USE temp;

  DROP FUNCTION IF EXISTS get_result_set;

  DELIMITER //

  CREATE PROCEDURE get_result_set()
  BEGIN

      SELECT id FROM t1;
      SELECT i FROM t2;
  END; //

  DELIMITER ;

此时调用 CALL get_result_set()会返回两个结果集（一般这种返回多结果集的都是在编程语言中进行处理的，例如JDBC）

存储过程也可以使用权限管理，使用存储过程的权限是EXECUTE(没验证过)

  GRANT EXECUTE ON db_name.table_name TO user_name;

MYSQL存储过程示例

procedure_simpleproc.sql

  USE temp;

  DELIMITER //

  CREATE PROCEDURE simpleproc (OUT param1 INT)
  BEGIN
      SELECT COUNT(*) INTO param1 FROM t1;
  END;//

  DELIMITER ;

procedure_p.sql

  USE temp;

  DELIMITER //

  CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
  BEGIN
      SELECT VERSION() INTO ver_param;
      SET incr_param = incr_param + 1;
  END;//

  DELIMITER ;


  /* mysql> use temp;
  Database changed
  mysql> SET @increment = 10;
  Query OK, 0 rows affected (0.00 sec)

  mysql> call p(@version, @increment);
  Query OK, 1 row affected (0.00 sec)

  mysql> select @version, @increment;
  +----------+------------+
  | @version | @increment |
  +----------+------------+
  | 5.6.14   |         11 |
  +----------+------------+ */

procedure_GetResultSets.sql

  USE temp;

  DROP FUNCTION IF EXISTS get_result_set;

  DELIMITER //

  CREATE PROCEDURE get_result_set()

  BEGIN
      SELECT id FROM t1;
      SELECT i FROM t2;
  END; //

  DELIMITER ;