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

转载于:https://my.oschina.net/xiaojintao/blog/817362