# 【mysql的编程专题④】存储过程 标签（空格分隔）： Mysql --- > 类似函数,但是没有返回值,把sql进行封装，便于多次使用或多种应用程序共享使用.不能用在SQL语句中，只能使用CALL调用; ## 创建存储过程 ### 语法 ```sql CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body ``` > ``sp_name``是存储过程的名称；``proc_parameter``表示存储过程的参数列表； ``characteristic``指定存储过程的特性；``routine_body``参数是SQL代码的内容，多条sql以``;``隔开，可以用``BEGIN…END``来标志SQL代码的开始和结束。 ### 参数 proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。 ```sql [ IN | OUT | INOUT ] param_name type ``` > IN表示输入参数(可以看做声明参数为函数的局部变量,内部做改变外部不被影响)；OUT表示输出参数(调用结束后外部可以访问到这个变量,内部能改变外部的变量)； INOUT表示既可以是输入，也可以是输出; ```sql CREATE PROCEDURE pr_param_in(in id int) BEGIN IF id is not null THEN set id = id + 1; END IF; SELECT id as id_inner; END // mysql> call pr_param_in(10); -- 如果存储过程的输出类型为in的话，不会报错； +----------+ | id_inner | +----------+ | 11 | +----------+ mysql> call pr_param_in(@id); +----------+ | id_inner | +----------+ | 101 | +----------+ 1 row in set Query OK, 0 rows affected mysql> select @id as id_out; -- in是不会影响外部的变量的； +--------+ | id_out | +--------+ | 100 | +--------+ 1 row in set CREATE PROCEDURE pr_param_out (OUT id INT) BEGIN SELECT id AS id_inner_1; -- 如果参数的输出类型为out的话，那外面的int必须是一个变量，并且无论传什么参数进来都为null。 IF id IS NOT NULL THEN SET id = id + 1; SELECT id AS id_inner_2; ELSE SELECT 1 INTO id; END IF; SELECT id as id_inner_3; END // call pr_param_out(10); -- 1411错误：子程序%s的OUT或INOUT参量不是变量。 1414 - OUT or INOUT argument 1 for routine test.pr_param_out is not a variable or NEW pseudo-variable in BEFORE trigger mysql> set @var = 10; Query OK, 0 rows affected mysql> call pr_param_out(@var); +------------+ | id_inner_1 | +------------+ | NULL | +------------+ 1 row in set +------------+ | id_inner_3 | +------------+ | 1 | +------------+ 1 row in set mysql> select @var as id_out; -- 如果参数的输出类型为out的话，传入什么值都会变为NULL，然后在内部都会改变外部变量的值； +--------+ | id_out | +--------+ | 1 | +--------+ 1 row in set CREATE PROCEDURE pr_param_inout (INOUT id INT) BEGIN SELECT id AS id_inner_1; -- 如果参数的输出类型为inout的话，那外面的int必须是一个变量。外面传的变量是能够进来的； IF id IS NOT NULL THEN SET id = id + 1; SELECT id AS id_inner_2; ELSE SELECT 1 INTO id; END IF; SELECT id as id_inner_3; END // mysql> set @var = 100; mysql> call pr_param_inout(@var); +------------+ | id_inner_1 | +------------+ | 100 | +------------+ 1 row in set +------------+ | id_inner_2 | +------------+ | 101 | +------------+ 1 row in set +------------+ | id_inner_3 | +------------+ | 101 | +------------+ 1 row in set mysql> select @var; -- 能够被改变； +------+ | @var | +------+ | 101 | +------+ ``` + type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型; + 参数是可有可无; + 参数名和字段名最好不要一样; ```sql DELIMITER // CREATE PROCEDURE p1() SELECT * FROM t;// -- 如果只有一句sql的话，可以不用写begin和end； ``` ### 存储过程的特性 characteristic参数有多个取值。其取值说明如下： + LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。 + [NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。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'：注释信息。 > 技巧：创建存储过程时，系统默认指定CONTAINS SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为NO SQL。而且，存储过程中最好在COMMENT部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。 ### Example1 ```sql CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) READS SQL DATA COMMENT 'this is a test_pro' BEGIN SELECT COUNT(*) INTO count_num FROM employee WHERE d_id=emp_id ; END ``` > 上述代码中，存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录，并用COUNT(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中,comment为注释; **更多的例子请看流程控制** ## 查看存储过程 ```sql SHOW PROCEDURE STATUS\G *************************** 1. row *************************** Db: test Name: p1 Type: PROCEDURE Definer: root@localhost Modified: 2015-05-01 09:49:20 Created: 2015-05-01 09:49:20 Security_type: DEFINER Comment: character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci ``` ## 删除存储过程 ```sql mysql> drop procedure p1; -- p1 存储过程的名称 Query OK, 0 rows affected ```