存储过程

1.1 Demo001
CREATE PROCEDURE procedure001 name 存储过程名
(IN parameter001 INTEGER) parameters 参数
BEGIN start of block 语句块头
DECLARE variable001 CHAR(10); variables 变量声明
IF parameter001 = 17 THEN start of IF  If 条件开始
SET variable001 = 'birds'; assignment 赋值
ELSE
SET variable001 = 'beasts'; assignment 赋值
END IF ; IF 结束
INSERT INTO table001 VALUES(variable001); statement SQL语句
END 语句块结束

1.2 选择分隔符
DELIMITER//
DELIMITER;//

1.3 调用存储过程
CALL P001()//

1.4 特征句子
LANGUAGE SQL 存储过程使用SQL语言编写的
NOT DETERMINISTIC 这里一个确定过程的定义就是那个每次输入一样输出也一样的程序。
SQL SECURITY DEFINER 在调用时检查创建过程用户的权限，(检查创建这的权限，不检查执行者用户权限）
SQL SECURITY INVOKER 在上边的一步还要检查调用存储过程的权限。

1.5 Parameters 参数
CREATE PROCEDURE P005
([IN] name data-type)

CREATE PROCEDURE P006
(OUT name data-type)

CREATE PROCEDURE P007
(INOUT name data-type)

1.6 复合语句
CREATE PROCEDURE P007()
BEGIN
SET @a = 5;
SET @b = 5;
INSERT INTO table WHERE s1 >=@b;
SELECT S1*@a FROM table WHERE S1 >= @b;
END;//

1.7 Variables 变量
CREATE PROCEDURE P8()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 5;
INSERT INTO table VALUES (a);
SELECT S1 *A FROM table WHERE S1 >= b;
END;//

CREATE PROCEDURE P009()
BEGIN
DECLARE a,b INT DEFAULT 5;
INSERT INTO table VALUES(a);
SELECT S1*a FROM table WHERE S1 >=b;
END;//

CREATE PROCEDURE P001()
BEGIN
DECLARE X1 CHAR(5) DEFAULT 'outer';
BEGIN
DECLARE X1 CHAR(5) DEFAULT 'inner';
SELECT X1;
END;
SELECT X1;
END;//

1.8 条件式 和 IF-THEN-ELSE
CREATE PROCEDURE P001(IN parameter001 INT)
BEGIN
DECLARE variable001 INT ;
SET variable001 = parameter001 + 1;
IF variable001 = 0 THEN
INSERT INTO table VALUES (17);
END IF;
IF parameter001 = 0 THEN 
UPDATE table SET S1 = S1 + 1 ;
ELSE
UPDATE table SET s1 = s1 +2;
END IF;
END;//

1.9 CASE 指令 (类似JAVA的 switch-case)
CREATE PROCEDURE P001(IN parameter001 INT)
BEGIN
DECLARE variable001 int;
SET variable001 = parameter001 + 1;
CASE
WHEN 0 THEN INSERT INTO table VALUES(17);
WHEN 1 THEN INSERT INTO table VALUES(18);
ELSE INSERT INTO table VALUES(19);
END CASE;
END;//

2.0 Loops 循环语句
WHILE ... END WHILE
LOOP ... END LOOP
REPEAT ... END REPEAT
GOTO

WHILE ... END WHILE

CREATE PROCEDURE P001()
BEGIN
DECLARE v INT;
SET V = 0;
WHILE v < 5 DO
INSERT INTO table VALUES(V);
SET V = V + 1;
END WHILE;
END;//

REPEAT ... END REPEAT(先执行程序，再判断条件。和JAVA的do...while相似)

CREATE PROCEDURE P001()
BEGIN
DECLARE V INT;
SET V = 0;
REPEAT
INSERT INTO table VALUES(V);
SET V = V + 1;
UNTIL V >=5
END REPEAT;
END;//

LOOP ... END LOOP(不需要初始条件，不需要结束条件)

CREATE PROCEDURE P001()
BEGIN
DECLARE V INT;
SET V = 0;
loop_label:LOOP
INSERT INTO table VALUES (V);
SET V = V + 1;
IF V >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END;//

2.1 Labels 标号(可以在 BEGIN WHILE REPEAT LOOP )
CREATE PROCEDURE P001()
label_1:BEGIN
label_2:WHILE 0 = 1 
DO LEAVE label_2
END WHILE label_2;
label_3：REPEAT LEAVE label_3;
UNTIL 0 = 0
END REPEAT label_3;
label_4:LOOP 
LEAVE label_4;
END LOOP label_4;
END label_1;//

2.2 跳出和标号(LEAVE 语句使程序跳出复杂的复合语句)

CREATE PROCEDURE P001 (parameter001 CHAR)
label_1:BEGIN
label_2:BEGIN
label_3:BEGIN
IF parameter001 IS NOT NULL THEN 
IF parameter001 = 'a' THEN 
LEAVE label_1;
ELSE BEGIN
IF parameter001 = 'b' THEN
LEAVE label_2;
ELSE
LEAVE label_3;
END IF;
END;
END IF;
END IF;
END;
END;
END;//

2.3 迭代(相当于JAVA的continue)

CREATE PROCEDURE P001 ()
BEGIN
DECLARE V = INT;
SET V = 0;
loop_label:LOOP
IF V = 3 THEN
SET V = V + 1;
ITERATE loop_label;
END IF;
INSERT INTO table VALUES(V);
SET V = V + 1;
IF V >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END;//