mysql存储过程open_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;

```