MySql存储过程

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 类似break
sql 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 类似循环中使用的continue
sql 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;