of {$slidecount} ½ {$title} ATZJG.NET {$author}

首页






MySQL 存储过程
MySQL 初步


Haifeng Xu


(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

References: 刘增杰、张少军 《MySQL 5.5 从零开始学》

http://forums.mysql.com/

MySQL 5.1 Reference Manual

目录

mysql> CREATE DATABASE IF NOT EXISTS testDB5;
mysql> USE testDB5;
mysql> CREATE TABLE t (s1 INT);
mysql> INSERT INTO t VALUES (5);

自选分隔符

自选分隔符

MySQL 客户端默认使用分号 ";" 作为 SQL 语句之间的分隔符. 但在存储过程中, 这会带来麻烦. 因为存储过程中有许多语句, 它们本身是用分号分隔开的. 因此要选一个程序中不太经常用的符号. 可以选择双斜杠 "//", 或者竖线 "|", "@" 等等.

mysql> DELIMITER //

如果以后需要恢复使用 ";" 作分隔符, 则输入下面的语句即可(注意空格):

mysql> DELIMITER ; 

查看 MySQL 中的存储过程或函数

查看 MySQL 中的存储过程或函数

语法是

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'];
mysql> show procedure status like '%'\G;
*************************** 1. row ***************************
                  Db: movies
                Name: MeanVar
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2017-12-20 19:18:15
             Created: 2017-12-20 19:18:15
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.04 sec)

ERROR:
No query specified

我们切换到 movies 数据库,

mysql> use movies;
Database changed
mysql> show tables;
+------------------+
| Tables_in_movies |
+------------------+
| movieexec        |
| movies           |
| moviestar        |
| starsin          |
| studio           |
+------------------+
5 rows in set (0.00 sec)

SHOW CREATE PROCEDURE 查看其中所建的存储过程 MeanVar:

mysql> show create procedure MeanVar\G;
*************************** 1. row ***************************
           Procedure: MeanVar
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `MeanVar`(
    IN s CHAR(15),
    OUT mean REAL,
    OUT variance REAL
)
BEGIN

DECLARE newLength INTEGER;
DECLARE movieCount INTEGER;

DECLARE Not_Found CONDITION FOR SQLSTATE '02000';

DECLARE MovieCursor CURSOR FOR
    SELECT length FROM Movies WHERE studioName = s;

    SET mean = 0.0;
    SET variance = 0.0;
    SET movieCount =0;
    OPEN MovieCursor;
    movieLoop: LOOP
        FETCH FROM MovieCursor INTO newLength;
        IF Not_Found THEN LEAVE movieLoop;
END IF;
        SET movieCount = movieCount + 1;
        SET mean = mean + newLength;
        SET variance = variance + newLength * newLength;
    END LOOP;
    SET mean = mean / movieCount;
    SET variance = variance / movieCount - mean * mean;
    CLOSE MovieCursor;
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

MySQL 中查看存储过程和函数的语句是:

SHOW CREATE {PROCEDURE | FUNCTION} sp_name;

MySQL 中的存储过程和函数的信息存储在 information_schema 数据库的 Routines 表中, 因此也可以这样查看:

SELECT * FROM information_schema.Routines;

实验

mysql> SELECT * FROM information_schema.Routines\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: BandW
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: movies
            ROUTINE_NAME: BandW
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: tinyint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 3
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: tinyint(1)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: IF NOT EXISTS(  SELECT * FROM Movies WHERE year=y AND studioName = s)
THEN RETURN TRUE;
ELSEIF 1<=  (SELECT COUNT(*) FROM Movies WHERE year=y AND
  studioName=s AND genre='comedy')
THEN RETURN TRUE;
ELSE RETURN FALSE;
END IF
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2017-12-20 17:15:00
            LAST_ALTERED: 2017-12-20 17:15:00
                SQL_MODE: NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
      DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
           SPECIFIC_NAME: MeanVar
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: movies
            ROUTINE_NAME: MeanVar
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN

DECLARE newLength INTEGER;
DECLARE movieCount INTEGER;

DECLARE Not_Found CONDITION FOR SQLSTATE '02000';

DECLARE MovieCursor CURSOR FOR
    SELECT length FROM Movies WHERE studioName = s;

    SET mean = 0.0;
    SET variance = 0.0;
    SET movieCount =0;
    OPEN MovieCursor;
    movieLoop: LOOP
        FETCH FROM MovieCursor INTO newLength;
        IF Not_Found THEN LEAVE movieLoop;
END IF;
        SET movieCount = movieCount + 1;
        SET mean = mean + newLength;
        SET variance = variance + newLength * newLength;
    END LOOP;
    SET mean = mean / movieCount;
    SET variance = variance / movieCount - mean * mean;
    CLOSE MovieCursor;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2017-12-20 19:18:15
            LAST_ALTERED: 2017-12-20 19:18:15
                SQL_MODE: NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: gbk
    COLLATION_CONNECTION: gbk_chinese_ci
      DATABASE_COLLATION: utf8_general_ci
2 rows in set (0.02 sec)

ERROR:
No query specified

存储过程和函数保存的位置

存储过程和函数保存的位置

我们查看以下数据库 information_schema 中表 Routines 的结构.

mysql> DESC information_schema.Routines;
+--------------------------+---------------------+------+-----+---------------------+-------+
| Field                    | Type                | Null | Key | Default             | Extra |
+--------------------------+---------------------+------+-----+---------------------+-------+
| SPECIFIC_NAME            | varchar(64)         | NO   |     |                     |       |
| ROUTINE_CATALOG          | varchar(512)        | NO   |     |                     |       |
| ROUTINE_SCHEMA           | varchar(64)         | NO   |     |                     |       |
| ROUTINE_NAME             | varchar(64)         | NO   |     |                     |       |
| ROUTINE_TYPE             | varchar(9)          | NO   |     |                     |       |
| DATA_TYPE                | varchar(64)         | NO   |     |                     |       |
| CHARACTER_MAXIMUM_LENGTH | int(21)             | YES  |     | NULL                |       |
| CHARACTER_OCTET_LENGTH   | int(21)             | YES  |     | NULL                |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL                |       |
| NUMERIC_SCALE            | int(21)             | YES  |     | NULL                |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL                |       |
| CHARACTER_SET_NAME       | varchar(64)         | YES  |     | NULL                |       |
| COLLATION_NAME           | varchar(64)         | YES  |     | NULL                |       |
| DTD_IDENTIFIER           | longtext            | YES  |     | NULL                |       |
| ROUTINE_BODY             | varchar(8)          | NO   |     |                     |       |
| ROUTINE_DEFINITION       | longtext            | YES  |     | NULL                |       |
| EXTERNAL_NAME            | varchar(64)         | YES  |     | NULL                |       |
| EXTERNAL_LANGUAGE        | varchar(64)         | YES  |     | NULL                |       |
| PARAMETER_STYLE          | varchar(8)          | NO   |     |                     |       |
| IS_DETERMINISTIC         | varchar(3)          | NO   |     |                     |       |
| SQL_DATA_ACCESS          | varchar(64)         | NO   |     |                     |       |
| SQL_PATH                 | varchar(64)         | YES  |     | NULL                |       |
| SECURITY_TYPE            | varchar(7)          | NO   |     |                     |       |
| CREATED                  | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_ALTERED             | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| SQL_MODE                 | varchar(8192)       | NO   |     |                     |       |
| ROUTINE_COMMENT          | longtext            | NO   |     | NULL                |       |
| DEFINER                  | varchar(77)         | NO   |     |                     |       |
| CHARACTER_SET_CLIENT     | varchar(32)         | NO   |     |                     |       |
| COLLATION_CONNECTION     | varchar(32)         | NO   |     |                     |       |
| DATABASE_COLLATION       | varchar(32)         | NO   |     |                     |       |
+--------------------------+---------------------+------+-----+---------------------+-------+
31 rows in set (0.07 sec)

查看以下刚才的存储过程 MeanVar

mysql> SELECT SPECIFIC_NAME, SQL_DATA_ACCESS, SECURITY_TYPE
    -> FROM information_schema.Routines
    -> WHERE ROUTINE_NAME='MeanVar' AND ROUTINE_TYPE='PROCEDURE';
+---------------+-----------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE |
+---------------+-----------------+---------------+
| MeanVar       | CONTAINS SQL    | DEFINER       |
+---------------+-----------------+---------------+
1 row in set (0.02 sec)

修改存储过程和函数

修改存储过程和函数

语法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

这里 sp_name 指存储过程或函数的名称; characteristic 参数指定存储过程或函数的特性


MySQL 中的变量

MySQL 中的变量

MySQL 中的用户变量形如 @variable_name.

mysql> select * from worker;
+----+---------+
| id | Name    |
+----+---------+
|  1 | Jimy    |
|  2 | Tom     |
|  3 | Kate    |
|  4 | Kavin   |
|  5 | Michael |
|  6 | Nick    |
+----+---------+
6 rows in set (0.01 sec)

mysql> select count(*) into num from worker;
ERROR 1327 (42000): Undeclared variable: num
mysql> select count(*) into @num from worker;
Query OK, 1 row affected (0.00 sec)

mysql> select @num;
+------+
| @num |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

End






Thanks very much!