【详细】MySQL之存储过程

一、概述

存储过程是一组为了完成某项特定功能的sql语句集，其实质上就是一段存储在数据库中的代码，他可以由声明式的sql语句（如CREATE,UPDATE,SELECT等语句）和过程式sql语句（如IF…THEN…ELSE控制结构语句）组成。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

二、优缺点

优点

1、存储过程可以用流程控制语言编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算; 2、存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，以后的操作中只需要从高速缓冲器中调用已编译好的二进制代码执行即可，从而提高了系统性能。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

三、基本语法

1、创建

CREATE [DEFINER = { user | CURRENT_USER }] 　PROCEDURE 存储过程名 ([proc_parameter[,...]]) [characteristic ...] routine_body [begin_label:] BEGIN 　　[statement_list] 　　　　…… END [end_label] -- proc_parameter: [ IN | OUT | INOUT ] 参数名 参数类型 IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量） 　　OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量） 　　INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量） -- 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

示例

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> 　　DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter;　　#将语句的结束符号恢复为分号

2、查看

select name from mysql.proc where db='数据库名'; -- 查看库中所有的存储过程 show procedure status where db='数据库名'; -- 查看存储过程详情

3、删除

DROP PROCEDURE '数据库名'

四、应用示例

1、IN 输入参数

-- 创建存储过程 DELIMITER $$ -- 自定义结束符 CREATE PROCEDURE INPARAM_PRO(IN PARAM_IN INT) BEGIN SELECT PARAM_IN; SET PARAM_IN = 2; SELECT PARAM_IN; END$$ DELIMITER; -- 调用存储过程时，IN参数可以是变量 SET @PARAM_IN = 1; -- 全局变量 CALL INPARAM_PRO(@PARAM_IN); SELECT @PARAM_IN; -- 也可以是常量 CALL INPARAM_PRO(1); -- 结果 PARAM_IN 1 PARAM_IN 2 PARAM_IN # @PARAM_IN为全局变量,存储过程中的PARAM_IN是局部变量,不会影响@PARAM_IN的值 1

2、OUT输出参数

-- 创建 DELIMITER $$ -- 自定义结束符 CREATE PROCEDURE OUTPARAM_PRO(OUT PARAM_OUT VARCHAR(100)) BEGIN SELECT PARAM_OUT; SET PARAM_OUT = 'LOVE'; SELECT PARAM_OUT; END$$ DELIMITER; -- 调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量； SET @PARAM_OUT = 'hello'; -- 全局变量 CALL OUTPARAM_PRO(@PARAM_OUT); -- 结果 PARAM_OUT -- 因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的结果1为null NULL PARAM_OUT LOVE

3、INOUT输入输出参数

-- 创建 DELIMITER $$ CREATE PROCEDURE INOUTPARAM_PRO(INOUT PARAM_INOUT VARCHAR(100)) BEGIN SELECT PARAM_INOUT; SET PARAM_INOUT = 'QQ'; SELECT PARAM_INOUT; END$$ DELIMITER; -- 调用，同OUT，参数也只能时变量 SET @PARAM_INOUT = 'WEIXIN'; CALL INOUTPARAM_PRO(@PARAM_INOUT); -- 结果 PARAM_INOUT WEIXIN PARAM_INOUT QQ

4、声明变量

变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用； 存储过程中变量的作用域，作用范围在begin和end块之间，end结束变量的作用范围即结束 变量可以通过set来赋值，也可以通过select into的方式赋值 传参变量是全局的，可以在多个块之间起作用

-- 创建 DELIMITER $$ CREATE PROCEDURE TEST_PRO() BEGIN DECLARE final_var VARCHAR(225); -- 声明变量 SELECT CONCAT('Hello',' ', ' MySQL') into final_var; SELECT final_var; END$$ DELIMITER; -- 调用 CALL TEST_PRO(); -- 结果 final_var Hello MySQL

5、存储过程中使用流程语句

if-then-else 语句 -- 创建 DELIMITER $$ CREATE PROCEDURE TEST_PRO(IN SCORE INTEGER) BEGIN DECLARE SCORE_LEVEL VARCHAR(20) DEFAULT ''; IF SCORE<60 THEN SET SCORE_LEVEL = 'C'; ELSEIF SCORE>=90 THEN SET SCORE_LEVEL = 'A'; ELSE SET SCORE_LEVEL = 'B'; END IF; SELECT SCORE_LEVEL; END$$ DELIMITER; -- 调用 SET @SCORE = 80; CALL TEST_PRO(@SCORE); -- 结果 SCORE_LEVEL B CASE语句 -- 类似于java的swith case -- 创建 DELIMITER // CREATE PROCEDURE TEST_PRO(IN NUM INTEGER, OUT VAR VARCHAR(10) DEFAULT 'true') BEGIN CASE NUM WHEN NUM=0 THEN SET VAR = 'true'; ELSE SET VAR = 'false'; END CASE; SELECT VAR; END// DELIMITER; -- 调用 SET @NUM = 1; SET @VAR = ''; CALL TEST_PRO(@NUM, @VAR); -- 结果 VAR false LOOP循环语句 -- LOOP循环类似于java的for循环 -- 建个测试表，使用loop插入数据 CREATE TABLE `loop_test` ( `id` int(11) NOT NULL, `test_column` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 创建存储过程 DELIMITER // CREATE PROCEDURE TEST_PRO() BEGIN DECLARE i int default 1; loop_test: LOOP INSERT INTO loop_test VALUES(i, CONCAT('test', i)); SET i = i+1; IF i > 10 THEN LEAVE loop_test; END IF; END LOOP loop_test; END// DELIMITER; -- 调用 CALL TEST_PRO(); -- 结果 id test_column 1 test1 2 test2 3 test3 4 test4 5 test5 6 test6 7 test7 8 test8 9 test9 10 test10 REPEAT循环语句 -- 类似于java的do while -- 创建 DELIMITER // CREATE PROCEDURE TEST_PRO() BEGIN DECLARE i int default 0; REPEAT SELECT i; SET i = i+1; UNTIL i > 10 END REPEAT; END// DELIMITER; -- 调用 CALL TEST_PRO(); -- 结果 输出i从1到10 WHILE 语句 -- 创建 DELIMITER // CREATE PROCEDURE TEST_PRO() BEGIN DECLARE i INT DEFAULT 5; DECLARE COUNT INT DEFAULT 0; WHILE i > 0 DO SET COUNT = COUNT + i; SET i = i-1; END WHILE; SELECT COUNT; END// DELIMITER; -- 调用 CALL TEST_PRO(); -- 结果 COUNT 15