在开发过程中,经常会遇到重复使用某一功能的情况,为此,MySQL 引人了存储过程。存储过程就是一条或多条 SQL语句的集合,当对数据库进行一系列复杂操作时,存储过程可以将这些复杂操作封装成一个代码块,以便重复使用,大大减少数据库开发员的工作量。
想要使用存储过程,首先要创建一个存储过程。创建存储过程,需要使用
CREATEPROCEDURE 语句,创建存储过程的基本语法格式如下:
CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics…]routine_body
上述语法格式中,CREATE PROCEDURE 为用来创建存储过程的关键字;sp_name为存储过程的名称;proc_parameter 为指定存储过程的参数列表,该参数列表的形式如下:
[IN|OUT|INOUT]param_name type
上述参数列表的形式中,IN 表示输人参数,OUT 表示输出参数,INOUT 表示既可以输人也可以输出;param_name 表示参数名称;type 表示参数的类型,它可以是 MySQL数据库中的任意类型。
在创建存储过程的语法格式中,characteristics用于指定存储过程的特性,它的取值说明具体如下。
(1)LANGUAGE SQL:说明 routine body 部分是由 SQL语句组成的,当前系统支持的语言为 SQL,SQL是LANGUAGE 的唯一值。
(2)[NOTIDETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出;NOTDETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为 NOT DETERMINISTIC。
(3)(CONTAINS SQLINO SQLIREADS SQL DATAIMODIFIES SQL DATA).指明子程序使用 SQL语句的限制。CONTAINS SQL表明子程序包含 SQL语句,但是不包含读写数据的语句;NO SQL表明子程序不包含 SQL 语句;READS SQL DATA 说明子程序包含读写数据的语句;MODIFIES SQLDATA 表明子程序包含写数据的语句。默认情况下,系统会指定为 CONTAINS SQL。
(4)SQL SECURITY(DEFINERIINVOKER):指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下,系统指定为 DEFINER。
(5)COMMENT'string’:注释信息,可以用来描述存储过程。routime_body 是 SQL 代码的内容,可以用 BEGIN…END 来表示 SQL 代码的开始和结束。接下来通过一个案例演示一下存储过程的创建,首先在数据库 chapter06 中创建表student,创建 student 表的 SQL 语句如下所示:
use chapter06;
CREATE TABLE student(
id INT(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
grade FLOAT,
gender CHAR(2)
);
执行上述 SQL语句创建 student 表,然后使用 INSERT 语句向 student 表中插人4条记录,INSERT语句如下所示:
INSERT INTO student(name,grade,gender)
VALUES('tom',60,'男'),
('jack',70,'男'),
('rose',90,'女'),
('lucy',100,'女');
例如,创建一个查看 student 表的存储过程,其创建语句如下:
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM student;
END;
上述代码创建了一个存储过程 Proc,每次调用这个存储过程的时候都会执行SELECT 语句查看表的内容,代码的执行过程如下:
mysql> DELIMITER //
mysql> CREATE PROCEDURE Proc()
-> BEGIN
-> SELECT * FROM student;
-> END//
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
在上述执行过程中,“DELIMITER //”语句的作用是将 MySQL 的结束符设置为//因为 MySQL默认的语句结束符号为分号" ; ”,为了避免与存储过程中 SQL 语句结束符相冲突,需要使用 DELIMITER 改变存储过程的结束符,并以“END //”结束存储过程存储过程定义完毕后再使用“DELIMITER ;"恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。需要格外注意的是,DELIMITER与要设定的结束符之间一定要有一个空格,否则设定无效。