存储过程

存储过程是一种没有直接返回值的子程序。当参数类型为 OUT 时，存储过程也可以给调用者返回值。

存储过程的结构

一个 PL 存储过程的结构如下：

PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...]
    BEGIN    -- 开始执行
      SQL statement; [ SQL statement; ]...
    END;     -- 结束执行

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 }
}

存储过程的创建

存储过程通过 CREATE PROCEDURE 语句创建存储过程。有如下规则：

• DECLARE 模块要放在 BEGIN ... END; 模块中，并且所有定义声明完成后才能定义其他语句。

• 可以定义 DETERMINISTIC / LANGUAGE SQL 等 sp_create_chistic 信息以丰富存储过程的使用。

• 存储过程不存在重载情况。

示例 1：创建不带有 IN 和 OUT 参数的存储过程。

DELIMITER //

CREATE PROCEDURE proc_name()
   BEGIN
     DECLARE var_name VARCHAR(20) DEFAULT 'ZhangSan';  
     SET var_name = 'LiSi';
     SELECT var_name;
   END //
Query OK, 0 rows affected

DELIMITER ;

CALL proc_name();

返回结果如下所示：

+----------+
| var_name | 
+----------+
| LiSi     |
+----------+
1 row in set 

示例 2：创建带有 IN 和 OUT 参数的存储过程。

//创建示例表 emp
CREATE TABLE emp(  
   empno          NUMBER(4,0),  
   empname        VARCHAR(10),  
   job            VARCHAR(10),   
   deptno         NUMBER(2,0),
   salary         NUMERIC  
);
Query OK, 0 rows affected

INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1,15000),(202,'Pat','MK_REP',2,12000),
          (119,'Karen','PU_CLERK', 4,10000),(118,'Guy','PU_CLERK', 4,10000),
         (201,'Michael','MK_MAN',3,9000);
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

DELIMITER //

CREATE PROCEDURE my_proc(IN emp_no INT,OUT emp_count INT)
   BEGIN
     SELECT COUNT(*) INTO emp_count FROM emp WHERE empno=emp_no;
   END //
Query OK, 0 rows affected

DELIMITER ;

//初始化参数
SET @emp_no='200',@emp_count=0;
Query OK, 0 rows affected

//调用存储过程 my_proc
CALL my_proc(@emp_no,@emp_count);

返回结果如下所示：

+-----------+
| emp_count |
+-----------+
|         1 |
+-----------+
1 row in set

继续查看 emp_count 被赋值后的结果：

SELECT @emp_count;

返回结果如下所示：

+------------+
| @emp_count |
+------------+
| 1          |
+------------+
1 row in set

存储过程的调用

已创建的存储过程，需要使用 CALL 语句来调用，但是它不能作为 SQL 表达式的一部分被调用。

//调用存储过程 my_proc
CALL my_proc(@emp_no,@emp_count);
Query OK, 0 rows affected

//查看 emp_count 被赋值后的结果

SELECT @emp_count;

返回结果如下所示：

+------------+
| @emp_count |
+------------+
| 1          |
+------------+
1 row in set