This slide is based on Jeffrey D. Ullman's work, which can be download from his website.
References: 刘增杰、张少军 《MySQL 5.5 从零开始学》
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 ;
语法是
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
我们切换到
mysql> use movies; Database changed mysql> show tables; +------------------+ | Tables_in_movies | +------------------+ | movieexec | | movies | | moviestar | | starsin | | studio | +------------------+ 5 rows in set (0.00 sec)
用
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 中的存储过程和函数的信息存储在
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
我们查看以下数据库
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)
查看以下刚才的存储过程
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 ...]
这里
MySQL 中的用户变量形如
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)