存储过程
- 语句结构
CREATE
[DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name([proc_paramter[, ...]])
[characteristic ...]
routine_body
/*
说明:
参数
proc_parameter:
[ IN | OUT | INOUT ] param_name type
特性
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
执行体
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
*/
- 样例
- 创建存储过程
DELIMITER || -- 将语句结束符临时调整为 ||
DROP PROCEDURE IF EXISTS `insertIDs`; -- 若原有存储过程 insertIDs 则进行删去
CREATE PROCEDURE `insertIDs`(IN num INT, OUT result INT) -- 创建存储过程 insertIDs,入参 num,出参 result
myLable:BEGIN -- 定义语句块标签 myLabel
DECLARE i INT DEFAULT 0; -- 声明局部变量 i
myWhile:WHILE i < num DO -- while 循环体 myWhile
SET i = i + 1; -- 对变量 i 进行赋值
INSERT INTO my_table (id) VALUES (i);
END WHILE myWhile;
SET result = i;
END || -- 结束 myLabel 语句块
DELIMITER ;
- 查看存储过程
SHOW PROCEDURE STATUS; -- 查看所有存储过程列表
SHOW PROCEDURE STATUS WHERE db = '数据库名';
SHOW CREATE PROCEDURE 数据库.存储过程名
SHOW CREATE PROCEDURE insertIDs; -- 查看指定存储过程的定义
SELECT name FROM mysql.proc WHERE db='数据库名';
SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
- 运行存储过程
CALL insertIDs(29364);
-- 查看运行进程列表:正在运行中
SHOW PROCESSLIST;
- 删除存储过程
DROP PROCEDURE insertIDs;
- 详解
-
循环体
-- WHILE WHILE 循环条件 DO -- 循环体内容 END WHILE -- REPEAT REPEAT -- 循环体内容 UNTIL 终止条件 END REPEAT -- LOOP my_loop_label:LOOP -- ... ... LEAVE my_loop_label; -- 结束循环 -- ... ... END LOOP
-
ITERATE 迭代(重新从指定的标签开始继续执行)
DELIMITER // CREATE PROCEDURE proc10() BEGIN DECLARE v INT; SET v = 10; LOOP_LABEL:LOOP IF v = 3 THEN SET v = v + 1; ITERATE LOOP_LABEL; -- 继续从 LOOP_LABEL 标签处开始执行代码(如果 v = 3 则跳过) END IF INSERT INTO t VALUES(v); SET v = v + 1; IF v >= 5 THEN LEAVE LOOP_LABEL; END IF; END LOOP END; //
-
判断
-- IF IF 条件 THEN -- ... ... END IF; IF 条件1 THEN -- ... ... ELSEIF 条件2 THEN -- ... ... ELSE -- ... ... END IF; -- CASE SET var = 1; CASE var THEN WHEN 0 THEN -- ... ... WHEN 1 THEN -- ... ... ELSE -- ... ... END CASE; CASE WHEN var = 0 THEN -- ... ... WHEN var > 0 THEN -- ... ... WHEN var < 0 THEN -- ... ... ELSE -- ... ... END CASE
- 变量声明
DECLARE varName,[varName...] 数据类型 [DEFAULT value]; -- 例 DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31'; DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
- 变量赋值
SET 变量名 = 表达式值, [变量名 = 表达式值 ...] -- 例: -- 用户变量 SELECT 'Hello World' INTO @x; SELECT @x; -- Hello World SET @y = 'Goodbye Cruel World'; SELECT @y; -- Goodbye Cruel World SET @z = 1 + 2 + 3; SELECT @z; -- 6 -- 存储过程中变量 DECLARE v INT; SET v = 100;
-
自定义函数
- 语句结构
CREATE [AGGREGATE] FUNCTION function_name([parameter_name type, parameter_name type...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
-
样例
- 创建
DELIMITER // CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) BEGIN DELETE FROM son WHERE id = uid; RETURN (SELECT COUNT(id) FROM son); END // DELIMITER ;
- 调用
SELECT deleteById(12);
- 删除
DROP FUNCTION deleteById;
存储过程与自定义函数的区别
- 存储过程实现的过程要复杂一些,而函数的针对性较强
- 存储过程可以有多个返回值,而自定义函数只有一个返回值
- 存储过程一般独立的来执行,而函数往往是作为其他 SQL 语句的一部分来使用
参考
- https://www.runoob.com/w3cnote/mysql-stored-procedure.html
- https://jingyan.baidu.com/article/ac6a9a5ed537902b653eac12.html
- https://www.cnblogs.com/caoruiy/p/4486249.html
- https://www.cnblogs.com/caoruiy/p/4485273.html
编辑记录
- 06/01/2019 Saturday 19:42