03-存储过程的创建

9.2 存储过程的创建

本节视频教学录像：6分钟

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下。

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

下面对创建存储过程各部分语法进行详细说明。

⑴CREATE PROCEDURE 为创建存储过程的关键字。

⑵Sp_name 为存储过程的名称，默认为存储过程与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

⑶proc_parameter指定存储过程的参数列表，列表形式如下。

[IN|OUT|INOUT] param_name type

其中，IN表示输入参数；OUT表示输出参数；INOUT表示既可以输入也可以输出；param_name表示参数名称，type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

⑷characteristics 指定存储过程的特性，有以下的取值。

①LANGUAGE SQL ：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。

②[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的，每次执行存储过程时，相同的输入会得到相同的输出；[NOT] DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC。

③CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL语句的限制。

●.CONTAINS SQL：表明子程序包含SQL语句，但是不包含读写数据的语句。

●.NO SQL：表明子程序不包含SQL语句。

●.READS SQL DATA：说明子程序包含读数据的语句。

●.MODIFIES SQL DATA：表明子程序包含写数据的语句。

默认情况下，系统会指定为CONTAINS SQL。

④SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。DEFINER 表示只有定义者才能执行；INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。

⑤COMMENT 'string' ：注释信息，可以用来描述存储过程或函数。

⑸routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

本章的例子基于雇员表emp,表中的字段分别为empno(雇员编号）、empname（雇员姓名）、empsex(雇员性别)、empage（雇员年龄）、dno(雇员所在的部门编号)，如下所示。

mysql> create table emp -> (empno int primary key, -> empname varchar(20), -> empsex char(2), -> empage int, -> dno int); Query OK, 0 rows affected (0.81 sec)

【范例9-1】

创建存储过程，查询每个部门的雇员人数。

mysql> create procedure pro_emp() -> begin -> select count(eno)as c_emp -> from emp -> group by dno; -> end//

实现过程如下。

⑴创建存储过程名称为pro_emp，该存储过程没有参数，但后面的（）必须加上。

⑵使用BEGIN和END来定义了存储过程体。

⑶存储过程是一个简单的SELECT语言，查询不同部门的人数。

【范例9-2】

创建存储过程，查询某个部门的雇员信息。

mysql> create procedure dept_emp(in deptno int) -> begin -> select* -> from emp -> where dno=deptno; -> end// Query OK, 0 rows affected (0.00 sec)

实现过程如下。

(1)创建了存储过程名称为dept_emp。

(2)该存储过程有一个输入参数deptno，该参数用来保存查询的某个部门编号。

(3)在存储过程体中使用where dno=deptno条件语句查询输入的某个部门的雇员信息。

【范例9-3】

创建存储过程，查询女雇员的人数，要求输出人数。

mysql> delimiter // mysql> create procedure count_emp(out cc int) -> begin -> select count(empno)into cc -> from emp -> where empsex='女'; -> end//

实现过程如下。

(1)创建了存储过程count_emp，参数cc为输出类型,接收女雇员的人数。

(2) select count(empno) into cc中的into 语句实现了将count(empno)统计信息赋值给输出参数cc。SQL变量名不能和列名一样。如果SELECT…INTO语句中包含一个对列的引用，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

注意 由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其他参数，可在参数名之前使用关键词 OUT或INOUT。

以上三个例子比较简单，在存储过程体中只有简单的查询语句，只是利用这3个例子来了解存储过程的创建语法，熟悉存储过程中的参数及其类型，当然存储过程体也可以是很多语句的复杂组合，完成更为复杂的操作。