【mysql的编程专题④】存储过程

时间:2022-11-13 16:48:38

类似函数,但是没有返回值,把sql进行封装,便于多次使用或多种应用程序共享使用.不能用在SQL语句中,只能使用CALL调用;

创建存储过程

语法

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部分分别是输入输出类型、参数名称和参数类型。

 [ IN | OUT | INOUT ] param_name type

IN表示输入参数(可以看做声明参数为函数的局部变量,内部做改变外部不被影响);OUT表示输出参数(调用结束后外部可以访问到这个变量,内部能改变外部的变量); INOUT表示既可以是输入,也可以是输出;

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数据库的任意数据类型;
  • 参数是可有可无;
  • 参数名和字段名最好不要一样;
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

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为注释;

更多的例子请看流程控制

查看存储过程

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

删除存储过程

mysql> drop procedure p1; -- p1 存储过程的名称
Query OK, 0 rows affected