存储过程
1.存储过程的创建和调用
存储过程就是具有名字的一段代码，完成一个特定的功能。存储过程保存在数据字典中
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
其中： proc_parameter: [ IN | OUT | INOUT ] param_name type
type: Any valid MySQL data 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
例1：创建一个存储过程，删除给定球员参加的所有比赛
DELIMITER //
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
BEGIN
DELETE FROM matches
WHERE playerno = p_playerno;
END//
DELIMITER ; -- 将语句的结束符号恢复为分号 ;注意有一个空格
默认情况下，存储过程和默认数据库相关联。如果想指定过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀
在定义过程时，使用DELIMITER // 命令将语句的结束符号从分号 ; 临时改为两个$$。这使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释
调用存储过程：call sp_name;
例2：删除8号 的所有比赛
call delete_matches(8);
案例：
DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x +1; UNTIL @x > p1 END REPEAT;
END//
DELIMITER ;
CALL dorepeat(1000);
SELECT @x;
注意：
@变量名字的作用
存储过程中定义，存储过程中进行处理，加减乘除
存储过程外面可以访问这个@变量，这个变量保存了存储过程中对这个变量处理后的值。
@x 用户变量，存储过程外面可以访问，一直被保存
直接使用set @x=0；就可以生成这么一个变量，不需要数据类型。
存储过程需要是活的，即
1.输入不同的参数，执行不同的操作
2.处理不同的行
3.处理的行数不一样等等
2.存储过程的参数
存储过程可以有0个或多个参数。参数有3种模式：
IN：输入参数，表示调用者向过程传入值。可以是字面量或变量
OUT：输出参数，表示过程向调用者传出值。只能是变量
INOUT：输入输出参数，既表示调用者向过程传入值，又表示过程向调用者传出值。只能是变量
如果过程没有参数，也必须在过程名后面写上小括号
确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理
案例：
1.IN参数
DELIMITER //
CREATE PROCEDURE dorepeat(int p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x +1; UNTIL @x > p1 END REPEAT;
END//
DELIMITER ;
CALL dorepeat(1000);
SELECT @x;
2.OUT参数
2.1
DELIMITER //
CREATE PROCEDURE simpleproc(OUT paraml INT)
BEGIN
select count(*) into paraml from players;
END//
DELIMITER ;
CALL simpleproc(@a);
SELECT @a;
2.2
DELIMITER //
CREATE PROCEDURE simpleproc3()
BEGIN
Select count(*) into paraml from players;
END//
DELIMITER ;
CALL simpleproc();
SELECT @paraml;
3.INOUT参数
DELIMITER //
CREATE PROCEDURE simpleproc1(INOUT paraml INT)
BEGIN
Select count(*) into paraml from players where playerno<=paraml;
END//
DELIMITER ;
Set @a=10;
CALL simpleproc1(@a);
SELECT @a;
注意：
IN参数：
数值
赋了值的变量
用@变量时，需要先给变量赋值或者直接用数值；
OUT参数：
变量就可以，可以没有赋值（调用的时候直接用@变量名，不用先set值）
用@变量时，不需要给变量赋值；
INOUT参数：
只能是赋了值的变量
用@变量时，需要先给变量赋值；
一个存储过程如果想有返回值
1.OUT参数
2.INOUT参数
3.在存储过程内部定义用户变量@变量名字
建议
返回值使用OUT参数
输入值使用IN参数
3.存储过程体
存储过程体包含了在过程调用时必须执行的语句。过程体总是以begin开始，以end结束。在过程体中，可以写各种语句，例如dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
语句块可以嵌套。例如：
BEGIN
BEGIN
BEGIN
statements;
END;
END;
END
每个嵌套块及其中的每条语句，必须以分号结束。表示过程体结束的begin-end块，则不需要分号。 begin-end块又叫做复合语句（compound statement）
注意：1.begin end之间的n条语句属于一条语句，一个整体。
2.begin end成对出现
可以为语句块添加一个标签。语法：
[begin_label:] BEGIN
[statement_list]
END [end_label]
例如：
label1: BEGIN
label2: BEGIN
label3: BEGIN
statements;
END label3 ;
END label2;
END label1
标签有两个作用：
1、增强代码的可读性
2、某些语句，例如leave和iterate语句，需要这些名字
4.变量
1.用户变量@
随处可以定义，随处可以使用
不定义可以直接使用
（存储过程）内部定义，（存储过程）外部可见
定义用户变量：set @a=0；
2.局部变量，只有名字，没有@符号
先定义，再使用
只在存储过程内部可见
只能紧跟着begin定义
每一个局部变量只能在紧跟着的begin中间使用
外层看不见内层
内层可以看见外层
平行互相看不见
定义局部变量：declare a int； set a=0；
@b与b不一样，一个全局一个局部。
在存储过程内部，使用局部变量，不要使用用户变量
局部变量的定义：
1.DECLARE 变量名字 类型 default
2.局部变量的定义需要紧挨着begin
局部变量
在过程体中，可以声明局部变量，用来临时保存一些值。语法：
DECLARE var_name [, var_name] … type [DEFAULT value]
该语句在begin end块中必须是第一条语句
如果没有使用default关键字指定默认值，则其值为null
例：
DECLARE num1 DECIMAL(7,2);
DECLARE alpha1 VARCHAR(20);
DECLARE num2,num3 INTEGER;
例3：创建过程，为局部变量指定默认值，并调用该过程
DELIMITER //
CREATE PROCEDURE test(OUT num1 INTEGER)
BEGIN
DECLARE num2 INTEGER DEFAULT 100;
SET num1 = num2;
END//
DELIMITER ;
调用过程：
CALL test(@num);
SELECT @num;
默认值除了字面量，还可以是复杂的表达式，包括标量子查询
例4：创建过程，使用所有球员的数量来初始化一个变量
DELIMITER //
CREATE PROCEDURE test1(OUT num1 INTEGER)
BEGIN
DECLARE num2 INTEGER DEFAULT (SELECT count(*) FROM players);
SET num1 = num2;
END//
DELIMITER ;
调用过程：
CALL test1(@num);
SELECT @num;
变量的作用域
变量的作用域就是变量能正常使用而不出错的程序块的范围。在嵌套块的情况下，在外部块中声明的变量可以在内部块中直接使用，而在内部块中声明的变量只能在内部块中使用
在下图中，变量v2只能用在
内部块b2中，因此，块b3中
的set语句和最后一条set语句
都是错误的
不要混淆局部变量和用户变量
两个区别：
1）局部变量的前面没有@符号
2）当begin-end块处理完后，局部变量就消失了，而用户变量存在于整个会话之中
用户变量可以用在存储过程的内部和外部，而局部变量在过程外是没有意义的
注意：MySQL不支持数组作为局部变量
案例：
1.
delimiter //
CREATE PROCEDURE sp2 (x int)
BEGIN
DECLARE newname VARCHAR(5);
DECLARE xid INT;
DECLARE done TINYINT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT xname, id FROM table1 where id>x;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH FROM cur1 INTO newname, xid;
IF done THEN LEAVE read_loop; END IF;
SELECT newname;
END LOOP;
CLOSE cur1;
END//
delimiter ;
call sp2(1);
SET语句
语法：
SET variable_assignment [, variable_assignment] ...
其中，variable_assignment:
user_var_name = expr |
[GLOBAL | SESSION] system_var_name = expr |
[@@global. | @@session. | @@] system_var_name = expr
用来给用户变量、系统变量赋值，也可以给过程中的局部变量赋值，
局部变量，只有begin end之间可以使用，而且需要declare定义
案例：
delimiter $$
CREATE PROCEDURE test2()
BEGIN
DECLARE num2 INTEGER DEFAULT (SELECT count(*) FROM PLAYERS);
SET @num = num2;
END$$
delimiter ;
mysql> select @num;
流程控制语句
条件控制
CASE
IF
循环控制
ITERATE
LEAVE
LOOP
REPEAT
RETURN
WHILE
MySQL 不支持FOR循环
IF语句
语法：
IF search_condition1 THEN statement_list
[ELSEIF search_condition2 THEN statement_list] ...
[ELSE statement_list]
END IF;
语义：如果条件search_condition1为true，则执行相应的then子句后面的语句列表statement_list 。如果条件search_condition1不为true，则判断ELSEIF子句中的条件search_condition2是否为true，如果为true，则执行相应的then子句后面的语句列表statement_list 。如果所有的条件都不为true，则执行ELSE子句后面的语句。
注意：then后面可以写一个begin end。
例5：创建过程，判断两个输入参数哪一个大
DELIMITER $$
CREATE PROCEDURE difference(
IN p1 INTEGER,
IN p2 INTEGER,
OUT p3 INTEGER)
BEGIN
IF p1 > p2 THEN
SET p3 = 1;
ELSEIF p1= p2 THEN
SET p3 = 2;
ELSE
SET p3 = 3;
END IF;
END$$
DELIMITER ;
IF条件中允许包含标量子查询
例6：创建过程，表示出players表和penalties表哪一个行数更多
DELIMITER //
CREATE PROCEDURE tennis.largest(OUT t char(10))
BEGIN
IF (SELECT count(*) FROM players) > (SELECT count(*) FROM penalties) THEN
SET t = 'players';
ELSEIF (SELECT count(*) FROM players) = (SELECT count(*) FROM penalties) THEN
SET t = 'equal';
ELSE
SET t = 'penalties';
END IF;
END//
DELIMITER ;
案例：
DELIMITER $$
CREATE FUNCTION simplecompare(n int, m int)
RETURNS VARCHAR(20)
BEGIN
DECLARE s VARCHAR(20);
If n>m then set s=’>’;
Elseif n=m then set s=’=’;
Else set s=’<’;
End if;
Set s=concat(n,’ ’,s,’ ’,m);
Return s;
END$$
DELIMITER ;
CASE语句
两种形式：
1）、simple case：CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
语义： case_value是一个表达式。该值和每个when子句中的when_value值进行相等比较。如果和某个when子句中的when_value值相等，则执行相应的then子句后面的语句statement_list。如果没有when_value值相等，则执行else子句后面的statement_list
案例：
DELIMITER $$
CREATE PROCEDURE p(x INT)
BEGIN
DECLARE v INT DEFAULT 1;
set v=x;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
END$$
DELIMITER ;
mysql> call p(2);
+------+
| v |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call p(3);
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
2）、searched case：常用的
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;
语义：对于每个when子句，判断后面的布尔表达式search_condition 是否为true。如果某个when子句的条件为true，则执行相应的then子句后面的语句statement_list。如果所有的when子句的条件都不为true，则执行else后面的语句statement_list
注意：如果在case中，没有一个when子句的比较结果为true，并且没有写else部分，那么就抛出异常：‘Case not found for CASE statement’
statement_list如果有多条语句，使用begin…end块包围起来（复合语句）
例7：改写例5，使用case语句
DELIMITER //
CREATE PROCEDURE difference1(
IN p1 INTEGER,
IN p2 INTEGER,
OUT p3 INTEGER)
BEGIN
CASE
WHEN p1 > p2 THEN
SET p3 = 1;
WHEN p1= p2 THEN
SET p3 = 2;
ELSE
SET p3 = 3;
END CASE;
END//
DELIMITER ;
扫一扫
1491
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
