存储过程小结

1：基本语法
1
2
3
4
5
6
7
DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;
2：参数 in|out|inout

2.1:IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER //
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
    SELECT p_in;
    SET p_in=2;
    SELECT p_in;
    END;
    //
DELIMITER ;
#调用
SET @p_in=1;
CALL in_param(@p_in);
SELECT @p_in;
#结果
p_in 1
p_in 2
@p_in 1

2.2:OUT:该值可在存储过程内部被改变，并可返回

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#存储过程OUT参数
DELIMITER //
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
    END;
    //
DELIMITER ;
#调用
SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;
#结果
p_out null
p_out 2
@p_out 2

2.3:INOUT:调用时指定，并且可被改变和返回

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#存储过程INOUT参数
DELIMITER //
  CREATE PROCEDURE inout_param(INOUT p_inout int)
    BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
    END;
    //
DELIMITER ;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;
#结果
P_inout 1
P_inout 2
@P_inout 2
3.变量赋值

语法：SET 变量名 = 变量值 [,变量名= 变量值 …]

用户变量

用户变量一般以@开头

注意：滥用用户变量会导致程序难以理解及管理

  • 在MySQL客户端使用用户变量
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT 'Hello World' into @x;
    SELECT @x;
    SET @y='Goodbye Cruel World';
    SELECT @y;
    SET @z=1+2+3;
    SELECT @z;
    #执行结果：
    @x Hello World
    @y Goodbye Cruel World
    @z 6
  • 在存储过程中使用用户变量
    1
    2
    3
    4
    5
    CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,' World');
    SET @greeting='Hello';
    CALL GreetWorld();
    #执行结果：
    coucat(@greeting,' World') Hello world
  • 在存储过程间传递全局范围的用户变量
    1
    2
    3
    4
    5
    6
    CREATE PROCEDURE p1() SET @last_proc='p1';
    CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
    CALL p1();
    CALL p2();
    #执行结果
    Last procedure was p1
    注释

MySQL存储过程可使用两种风格的注释：

双杠：–，该风格一般用于单行注释

C风格： 一般用于多行注释

MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。

4.MySQL存储过程的查询
  • 查询存储过程
    1
    2
    3
    SELECT name FROM mysql.proc WHERE db='数据库名';
    SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
    SHOW PROCEDURE STATUS WHERE db='数据库名';
  • 查看存储过程详细信息
    1
    SHOW CREATE PROCEDURE 数据库.存储过程名;
MySQL存储过程的修改
1
ALTER PROCEDURE 更改用CREATE PROCEDURE

建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
sp_name参数表示存储过程或函数的名称；
characteristic参数指定存储函数的特性。
CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；
NO SQL表示子程序中不包含SQL语句；
READS SQL DATA表示子程序中包含读数据的语句；
MODIFIES SQL DATA表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。
COMMENT 'string'是注释信息。

实例：

1
2
3
4
#将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。
ALTER  PROCEDURE  num_from_employee
  MODIFIES SQL DATA
  SQL SECURITY INVOKER ;
1
2
3
4
#将读写权限改为READS SQL DATA，并加上注释信息'FIND NAME'。
ALTER  PROCEDURE  name_from_employee
  READS SQL DATA
  COMMENT 'FIND NAME' ;
MySQL存储过程的删除
1
DROP PROCEDURE [过程1[,过程2…]]

从MySQL的表格中删除一个或多个存储过程。