MySql存储过程

时间:2024-08-10 13:05:20

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;
```