MySQL 存储过程
```sql
CREATE PROCEDURE myprocedure
(IN para01 INTEGER)
BEGIN
DECLARE var01 CHAR(10);
IF para01 = 17 THEN
SET var01 = 'birds';
ELSE
SET var01 = 'beasts';
END IF;
INSERT INTO table1 VALUES(var01);
END
```
创建实例
```sql
CREATE PROCEDURE p1 /*声明存储过程*/
() /*参数列表*/
SELECT * FROM table_t; /*主体*/
```
调用存储过程
```sql
CALL proc01();
```
特征子句
```sql
CREATE PROCEDURE p2
()
LANGUAGE SQL /*系统默认的，说明主体使用sql语句*/
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'AN EXAMPLE PROCEDURE'
SELECT CURRENT_DATE, RAND() FROM table_t;
```
特征子句反映存储过程的特性，在括号之后，主体之前。
参数
```sql
CREATE PROCEDURE proc
()
...
```
括号里面用于存放参数列表通过IN(入参),OUT(返回值),INOUT(入参和出参相同)
声明变量
```sql
CREATE PROCEDURE p8
()
BEGIN
DECLARE a INTEGER;
DECLARE b INTEGER;
SET a = 5;
SET b = 6;
INSERT INTO table1 VALUES(a);
SELECT s1*a FROM table1 WHERE s1 >= b;
END;
```
指定默认值
```sql
CREATE PROCEDURE p8
()
BEGIN
DECLARE a INTEGER DEFAULT 5;
DECLARE b INTEGER DEFAULT 5;
INSERT INTO table1 VALUES(a);
SELECT s1*a FROM table1 WHERE s1 >= b;
END;
```
作用域
```sql
CREATE PROCEDURE p8
()
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'outer';
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'inner';
SELECT x1;
END;
END;
```
条件表达式
```sql
CREATE PROCEDURE p12
(IN para01 INTEGER)
BEGIN
DECLARE var01 INTEGER;
SET var01 = para01+1;
IF var01 = 0 THEN
INSERT INTO table1 VALUES(17);
END IF;
IF para01 = 0 THEN
UPDATE table1 SET s1 = s1+1;
ELSE
UPDATE table1 SET s1 = s1 +2;
END IF;
END;
```
CASE 指令
```sql
CREATE PROCEDURE p12
(IN para01 INTEGER)
BEGIN
DECLARE var01 INTEGER;
SET var01 = para01 + 1;
CASE var01
WHEN 0 THEN INSERT INTO table1 VALUES(12);
WHEN 1 THEN INSERT INTO table1 VALUES(90);
ELSE INSERT INTO table1 VALUES(80);
END CASE;
END;
```
循环
一种是类似编程语言中的while循环，另一种是do...while循环，还有一种是loop
```sql
CREATE PROCEDURE p12
()
BEGIN
DECLARE var01 INTEGER;
SET var01 = 0;
WHILE var01 < 5 DO
INSERT INTO table1 VALUES(var01);
SET var01 = var01 +1;
END WHILE;
END;
```
```sql
CREATE PROCEDURE p23
()
BEGIN
DECLARE var01 INTEGER;
SET var01 = 0;
REPEAT
INSERT INTO table1 VALUES(12);
SET var01 = var01 + 1;
UNTIL var01 >=5；
END REPEAT;
END;
```
LEAVE 类似breaksql CREATE PROCEDURE p123 () BEGIN DECLARE var01 INTEGER; SET var01 = 0; myloop: LOOP INSERT INTO table1 VALUES(var01); SET var01 = var01 + 1; IF var01 >= 5 THEN LEAVE myloop; END IF; END LOOP; END;
ITERATE 类似循环中使用的continuesql CREATE PROCEDURE p123 () BEGIN DECLARE var01 INTEGER; SET var01 = 0; myloop: LOOP INSERT INTO table1 VALUES(var01); SET var01 = var01 + 1; IF var01 >= 5 THEN ITERATE myloop; END IF; END LOOP; END;
标签
标签增加可读性
```sql
CREATE PROCEDURE p34
()
label_01: BEGIN
label_02: WHILE 0 =1 DO
LEAVE label_02;
END WHILE label_02;
END label_01;
```
异常处理
```sql
CREATE PROCEDURE p22
(IN para01 INTEGER)
BEGIN
DECLARE EXIT HANDLER FOR 1215 /*声明异常处理器*/
INSERT INTO error_log VALUES(error_msg);
INSERT INTO table1 VALUES(para01);
END;
```
异常声明语法：
DECLARE
{EXIT|CONTINUE} //退出还是继续
HANDLER FOR
{error-number|{SQLSTATE error-string}|condition} //异常的原因
SQL statement //捕获异常后执行的sql语句
```sql
CREATE PROCEDURE p34
()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000'
SELECT * FROM table1; /*当出现23000的错误后，会自动执行改行*/
sql...
END;
```
condition 声明，就是给异常处理器命名
```sql
CREATE PROCEDURE p45
()
BEGIN
DECLARE 'Constraint Violation'
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
'Constraint Violation'
ROLLBACK;
START TRANSACTION;
INSERT INTO table1 VALUES(1);
COMMENT;
END;
```
游标
```sql
CREATE PROCEDURE p25
(OUT returnval INTEGER)
BEGIN
DECLARE a,b INTEGER;
DECLARE cur01 CURSOR FOR
SELECT s1 FROM table1;
DECLARE CONTINUE HANDLER FOR
NOT FOUND
SET b = 1;
OPEN cur01;
REPEAT
FETCH cur01 INTO a;
UNTIL b = 1;
END REPEAT;
CLOSE cur01;
SET returnval = a;
END;
```
声明顺序：先声明普通变量，然后声明游标，最后声明异常处理器。
游标的特性
在mysql5.0 中，游标是只读的，而且只能顺序读取，也不能执行更新操作。
函数
```sql
CREATE FUNCTION factorial
(n DECIMAL(3,0))
DETERMINISTIC
BEGIN
DECLARE factorial DECIMAL(20,0) DEFAULT 1;
DECLARE counter DECIMAL(3,0);
SET counter = n;
factorial_loop: REPEAT
SET factorial = factorial * counter;
SET counter = counter + 1;
UNTIL counter =1
END REPEAT;
RETURN factorial;
END;
```