Mysql 存款和储蓄进程实例详解

存款和储蓄进度和函数是预先经过编写翻译并积存在数据库中的1段SQL语句的集中,存款和储蓄和和函数的分别在于函数必须有重返值,而存款和储蓄进程并未有,存款和储蓄进程的参数能够使用IN、OUT、INOUT类型,而函数的参数只好是IN类型。此次博客就来说一下囤积进程,MySQL版本:

概念规则和管理

 条件的定义和拍卖能够用来定义在管理进程中遇见难点时相应的管理步骤。

 语法如下:

条件定义:
DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
    |mysql_error_code
条件处理:
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE|EXIT|UNDO

condition_value:
     SQLSTATE [VALUE] sqlstate_value
     |condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

举个例证吗!
明天有表如下:

mysql> select * from student;
+-----+--------+----------+--------+
| sid | gender | class_id | sname  |
+-----+--------+----------+--------+
|   1 | 男     |        1 | 李杰   |
|   2 | 女     |        1 | 钢蛋   |
|   3 | 男     |        1 | 张三   |
|   4 | 男     |        1 | 张一   |
|   5 | 女     |        1 | 张二   |
|   6 | 男     |        1 | 张四   |
|   7 | 女     |        2 | 铁锤   |
|   8 | 男     |        2 | 李三   |
|   9 | 男     |        2 | 李一   |
|  10 | 女     |        2 | 李二   |
|  11 | 男     |        2 | 李四   |
|  12 | 女     |        3 | 如花   |
|  13 | 男     |        3 | 刘三   |
|  14 | 男     |        3 | 刘一   |
|  15 | 女     |        3 | 刘二   |
|  16 | 男     |        3 | 刘四   |
|  17 | 男     |        1 | 刘一   |
+-----+--------+----------+--------+
17 rows in set (0.00 sec)

(一)当未有进展标准化管理的时候:

mysql> delimiter $$
mysql> create procedure student_insert()
    -> begin
    -> set @x=1;
    -> insert into student(sid,gender,class_id,sname) values(18,'男',1,'frank');
    -> set @x=2;
    -> insert into student(sid,gender,class_id,sname) values(1,'男',1,'coco');
    -> set @x=3;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call student_insert();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select @x;
+------+
| @x   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

从下面的例证能够看到,当插入sid=一,主键重复了,间接退出了,并不曾推行余下的语句,所以@x的值为贰。

 (二)能够对主键重复进行拍卖:

mysql> delimiter $$
mysql>
mysql>
mysql> create procedure student_insert()
    -> begin
    -> declare continue handler for sqlstate '23000' set @x2=1;
    -> set @x=1;
    -> insert into student(sid,gender,class_id,sname) values(19,'男',1,'jack');
    -> set @x=2;
    -> insert into student(sid,gender,class_id,sname) values(1,'男',1,'bob');
    -> set @x=3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call student_insert;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x,@x2;
+------+------+
| @x   | @x2  |
+------+------+
|    3 |    1 |
+------+------+
1 row in set (0.00 sec)

此次在调用存款和储蓄进程的时候,并未报错,而是在境遇主键重复的时候,会设置定义的continue去施行,所以两次三番向下试行。

condition_value的值能够是由此declare定义的condition_name,能够是SQLSTATE的值恐怕mysql_error_code的值会在是SQLWA纳瓦拉NING、NOT
FOUND、SQLEXCEPTION,这一个一个值是三种概念好的错误系列,分别代表分歧的意义:

SQLWA凯雷德NING:是对具备以0一开头的SQLSTATE代码的笔记

NOT FOUND是对所有以02早先的SQLSTATE代码的笔记

SQLEXCEPTION是对负有未有被SQLWALX570NING或然NOT
FOUND捕获的SQLSTATE代码的笔记。

以上的declare continue handler for sqlstate ‘22000’ set
@x二=壹;也得以用以下两种方法来写:

#捕获mysql-error-code
declare continue handler for 1062 set @x2=1;
#事先定义condition_name
declare duplicatekey condition for sqlstate '23000';
declare continue handler for duplicatekey set @x2=1;
#捕获sqlexception
declare continue handler for sqlexception set @x2=1;

 

光标的接纳

在仓库储存进度和函数中,能够动用光标对结果实行巡回的拍卖,语法如下:

声明光标:
DECLARE cursor_name CURSOR FOR select_statement
OPEN光标:
OPEN cursor_name
FETCH光标:
FETCH cursor_name INTO var_name[,var_name]...
CLOSE光标:
CLOSE cursor_name

举例:

抑或以users表为例,好吧。上午。。不早了,未完待续….

光标的运用

在蕴藏进程和函数中,能够采纳光标对结果举行巡回的拍卖,语法如下:

声明光标:
DECLARE cursor_name CURSOR FOR select_statement
OPEN光标:
OPEN cursor_name
FETCH光标:
FETCH cursor_name INTO var_name[,var_name]...
CLOSE光标:
CLOSE cursor_name

举例:

现今有表如下,分别求id为一依然id为贰的num的和:

mysql> select * from testcursor;
+------+------+
| id   | num  |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    2 |    5 |
|    2 |    6 |
|    2 |    7 |
+------+------+
6 rows in set (0.00 sec)

创建存储进程:

CREATE PROCEDURE numsum()
BEGIN
    DECLARE i_id INT;
    DECLARE i_num INT;
    DECLARE cursor_sum CURSOR FOR SELECT id,num FROM testcursor;
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cursor_sum;
    SET @x1=0;
    SET @x2=0;
    OPEN cursor_sum;
    REPEAT 
        FETCH cursor_sum INTO i_id,i_num;
            IF i_id = 1 THEN
                SET @x1=@x1+i_num;
            ELSE
                SET @x2=@x2+i_num;
            END IF;
    UNTIL 0 END REPEAT;
END $$
delimiter ;

测试:

mysql> call numsum();
Query OK, 0 rows affected (0.00 sec)

mysql> select @x1,@x2;
+------+------+
| @x1  | @x2  |
+------+------+
|    9 |   18 |
+------+------+
1 row in set (0.00 sec)

 

光标的使用

在存款和储蓄进程和函数中,能够采取光标对结果开始展览巡回的拍卖,语法如下:

声明光标:
DECLARE cursor_name CURSOR FOR select_statement
OPEN光标:
OPEN cursor_name
FETCH光标:
FETCH cursor_name INTO var_name[,var_name]...
CLOSE光标:
CLOSE cursor_name

举例:

明天有表如下,分别求id为一可能id为二的num的和:

mysql> select * from testcursor;
+------+------+
| id   | num  |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    2 |    5 |
|    2 |    6 |
|    2 |    7 |
+------+------+
6 rows in set (0.00 sec)

开创存款和储蓄进程:

CREATE PROCEDURE numsum()
BEGIN
    DECLARE i_id INT;
    DECLARE i_num INT;
    DECLARE cursor_sum CURSOR FOR SELECT id,num FROM testcursor;
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cursor_sum;
    SET @x1=0;
    SET @x2=0;
    OPEN cursor_sum;
    REPEAT 
        FETCH cursor_sum INTO i_id,i_num;
            IF i_id = 1 THEN
                SET @x1=@x1+i_num;
            ELSE
                SET @x2=@x2+i_num;
            END IF;
    UNTIL 0 END REPEAT;
END $$
delimiter ;

测试:

mysql> call numsum();
Query OK, 0 rows affected (0.00 sec)

mysql> select @x1,@x2;
+------+------+
| @x1  | @x2  |
+------+------+
|    9 |   18 |
+------+------+
1 row in set (0.00 sec)

 

积累进度和函数是刚开始阶段经过编写翻译并蕴藏在数据库中的1段SQL语句的联谊,存款和储蓄和和函数的分别在于函数必须有再次回到值,而存款和储蓄进程未有,存储进度的参数能够运用IN、OUT、INOUT类型,而函数的参数只可以是IN类型。存款和储蓄进程再简单题来讲,就是为其后的选择而保留的一条或多条MySQL语句的会见。可将其视为批件,尽管它们的效果不止限于批管理。以我之见,
存款和储蓄进程就是有业务逻辑和流程的聚焦, 能够在蕴藏进程中创立表,更新数据,
删除等等。此次博客就来说一下囤积进程

 流程序调控制

 mysql帮助的流程序调控制有:IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHILE语句。

 1.IF

 语法如下:

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]...
    [ELSE statement_list]
END IF

比方:求七个数的最大值

DELIMITER $$
CREATE PROCEDURE comp(IN n1 INT,IN n2 INT)
BEGIN
    SET @res=0;
    IF n1 > n2 THEN
        SET @res=n1;
    ELSEIF n1 = n2 THEN
        SET @res=n1;
    ELSE
        SET @res=n2;
    END IF;
END $$
DELIMITER ;

测试:

mysql> call comp(100,2);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> call comp(100,100);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

2.CASE语句

 语法如下:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE
或者:
CASE 
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE

将以上例子使用case来落到实处:

DELIMITER $$
CREATE PROCEDURE comp1(IN n1 INT,IN n2 INT)
BEGIN
    SET @res=0;
    CASE  
        WHEN n1>n2 THEN 
            SET @res=n1;
        WHEN n1=n2 THEN 
            SET @res=n1;
        ELSE 
            SET @res=n2;
    END CASE;
END $$
DELIMITER ;

测试:

mysql> call comp1(10,2);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> call comp1(100,100);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> call comp1(2,11);
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

3.LOOP和LEAVE语句

 LOOP可以兑现简单的巡回,常常和LEAVE一齐行使,LOOP语法如下:

[begin_label:]LOOP
    statement_list
END LOOP[end_label]

这段日子有表如下:

mysql> select * from userinfo;
+----+--------+--------+
| id | uname  | passwd |
+----+--------+--------+
|  1 | alex   | 123    |
|  2 | frank  | 123    |
|  3 | rose   | 312    |
|  4 | tom    | qqq    |
|  5 | jack   | qwer   |
|  6 | coco   | 123    |
|  7 | lancer | 123    |
+----+--------+--------+
7 rows in set (0.00 sec)

运用循环向当中插入100行数据:

DELIMITER $$
CREATE PROCEDURE userinset()
BEGIN
    SET @x=0;
    ins: LOOP    --标签为ins
        SET @x=@x+1;
        IF @x=100 THEN   
            LEAVE ins;   --当@x=100的时候,则退出循环
        END IF;
        INSERT INTO userinfo(uname,passwd) values('test','123');
    END LOOP ins;
END $$
DELIMITER ;

测试:

mysql> call userinset();
Query OK, 0 rows affected (0.17 sec)
mysql> select count(1) from userinfo;
+----------+
| count(1) |
+----------+
|      106 |
+----------+
1 row in set (0.00 sec)

行数扩展到了十陆行,表示成功。

 4.ITERATE语句

 必须在循环中选用,成效是跳过当前轮回的剩下的讲话,直接进入下1轮循环,相当于部分高端语言中的continue。

今昔有表如下:

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from info;
Empty set (0.00 sec)

只向表中插入奇数行:

delimiter $$
CREATE PROCEDURE inserinfo()
BEGIN
    set @x=0;
    ins: LOOP
        SET @x=@x+1;
        IF @x=10 THEN
        LEAVE ins;
        ELSEIF mod(@x,2)=0 THEN
        ITERATE ins;
        END IF;
        INSERT INTO info(id,name) VALUES(@x,'test');
    END LOOP ins;
END $$
delimiter ;

测试:

mysql> call inserinfo();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    3 | test |
|    5 | test |
|    7 | test |
|    9 | test |
+------+------+
5 rows in set (0.00 sec)

5.REPEAT语句

有规范的循环调节语句,当满意条件的时候退出循环,语法如下:

[begin_label:]REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

举个例子:再在地点例子中插入拾行:

delimiter $$
CREATE PROCEDURE inserinfo2()
BEGIN
    DECLARE x INT DEFAULT 9;
    ins: REPEAT
       SET x=x+1;
       INSERT INTO info(id,name) VALUES(x,'test');
    UNTIL x>18 END REPEAT;

END $$
delimiter ;

测试:

mysql> call inserinfo2();
Query OK, 1 row affected (0.03 sec)
mysql> select count(1) from info;
+----------+
| count(1) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

6.WHILE语句

WHILE是满足条件才实施。

语法如下:

[begin_lable:]WHILE search_condition DO
    statement_list
END WHILE [end_label]

以上的事比即使用while来促成如下:

delimiter $$
CREATE PROCEDURE inserinfo2()
BEGIN
    DECLARE x INT DEFAULT 9;
    ins: WHILE X<=18 DO
       SET x=x+1;
       INSERT INTO info(id,name) VALUES(x,'test');
    END WHILE;
END $$
delimiter ;

那边就不在敖述了。

 

MySQL之存储进程,mysql存款和储蓄进程

积存进程和函数是事先经过编写翻译并蕴藏在数据库中的1段SQL语句的晤面,存款和储蓄和和函数的界别在于函数必须有重回值,而存储进程未有,存款和储蓄进程的参数可以行使IN、OUT、INOUT类型,而函数的参数只可以是IN类型。此次博客就来说一下仓库储存进度,MySQL版本:

mysql> select VERSION(); --select调用函数
+-----------+
| VERSION() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

 

概念规则和拍卖

 条件的定义和拍卖能够用来定义在管理进度中相丧命题时相应的拍卖步骤。

 语法如下:

条件定义:
DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
    |mysql_error_code
条件处理:
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE|EXIT|UNDO

condition_value:
     SQLSTATE [VALUE] sqlstate_value
     |condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

接二连三用users表例如吗!
现行反革命有数据如下:

图片 1

(一)当未有开始展览标准管理的时候:

#delimiter $$
create procedure user_insert()
begin
set @x=1;
insert into users(id,gender,name) values(1,'男','常贵');
set @x=2;
insert into users(gender,name) values('女','大脚');
set @x=3;
END #$$

图片 2

地点的例证能够见到,当插入id=1,主键重复了,直接退出了,并未实践余下的讲话,所以@x的值为一。

图片 3

(二)能够对主键重复进行处理:

#delimiter $$
create procedure user_insert2()
begin
declare continue handler for sqlstate '23000' set @x2=1;
set @x=1;
insert into users(id,gender,name) values(3,'男','jack');
set @x=2;
insert into users(id,gender,name) values(1,'男','mary');
set @x=3;
end #$$
#delimiter ;

调用call user_insert2();

图片 4

这一次在调用存款和储蓄进程的时候,并从未报错,而是在遇见主键重复的时候,会安装定义的continue去试行,所以一而再向下实施。

condition_value的值能够是通过declare定义的condition_name,能够是SQLSTATE的值只怕mysql_error_code的值会在是SQLWA福特ExplorerNING、NOT
FOUND、SQLEXCEPTION,那些二个值是三种概念好的错误种类,分别表示差异的含义:

SQLWA中华VNING:是对富有以01初始的SQLSTATE代码的笔记

NOT FOUND是对持有以02方始的SQLSTATE代码的笔记

SQLEXCEPTION是对具有未有被SQLWA卡宴NING或然NOT
FOUND捕获的SQLSTATE代码的笔记。

如上的declare continue handler for sqlstate ‘二2000’ set
@x二=一;也足以用以下二种方法来写:

#捕获mysql-error-code
declare continue handler for 1062 set @x2=1;
#事先定义condition_name
declare duplicatekey condition for sqlstate '23000';
declare continue handler for duplicatekey set @x2=1;
#捕获sqlexception
declare continue handler for sqlexception set @x2=1;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

CopyRight © 2015-2020 金沙中心城 All Rights Reserved.
网站地图xml地图