MySQL之存储过程

获得数据库自动生成的主键:

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,存储和和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT类型,而函数的参数只能是IN类型。本次博客就来讲一下存储过程,MySQL版本:

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,存储和和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT类型,而函数的参数只能是IN类型。本次博客就来讲一下存储过程,MySQL版本:

主代码:prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);‘ResultSet.getGeneratedKeys();//得到插入行的主键’

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

建表:

 

 

create table testprimarykey( id int primary key auto_increment, name varchar;

存储过程的操作

语法如下:

创建:
CREATE PROCEDURE sp_name([proc_parameter[,...]])
    [characteristic...] routine_body

proc_parameter:
[IN|OUT|INOUT] param_name type    #type: Any valid MySQL data type
characteristic:
LANGUAGE SQL
|[NOT] DETERMINISTIC|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements

修改:
ALTER PROCEDURE sp_name [characteristic...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'

调用:
CALL sp_name([parameter[,...]])

删除:
DROP PROCEDURE sp_name

查看:
show PROCEDURE STATUS [like 'pattern']
SHOW CREATE PROCEDURE sp_name

MySQL的存储过程和函数中允许包含DDL语句,也允许在存储过程中执行提交或者回滚,但是存储过程和函数不允许执行LOAD
DATA INFILE语句,存储过程和函数可以调用其他的过程或者函数。

插入小知识点@:

1.用户变量:以"@"开始,形式为"@变量名"
用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效。
2.全局变量:定义方式 set GLOBAL 变量名  或者  set @@global.变量名 
对所有客户端生效,只有具有super权限才可以设置全局变量。

现在有表如下:

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)

创建存储过程,传入性别(男或女),显示对应性别的学生id,返回对应性别的人数:

DELIMITER $$
CREATE PROCEDURE myprocedure(IN sex CHAR,OUT num INT)
BEGIN
    SELECT  sid FROM student WHERE gender=sex;
    SELECT FOUND_ROWS() INTO num;   
END $$
DELIMITER ;

调用:

CALL myprocedure('女',@num)

图片 1

查看人数@num:

SELECT @num

图片 2

 

存储过程的操作

语法如下:

创建:
CREATE PROCEDURE sp_name([proc_parameter[,...]])
    [characteristic...] routine_body

proc_parameter:
[IN|OUT|INOUT] param_name type    #type: Any valid MySQL data type
characteristic:
LANGUAGE SQL
|[NOT] DETERMINISTIC|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements

修改:
ALTER PROCEDURE sp_name [characteristic...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY {DEFINAER|INVOKER}|COMMENT 'string'

调用:
CALL sp_name([parameter[,...]])

删除:
DROP PROCEDURE sp_name

查看:
show PROCEDURE STATUS [like 'pattern']
SHOW CREATE PROCEDURE sp_name

MySQL的存储过程和函数中允许包含DDL语句,也允许在存储过程中执行提交或者回滚,但是存储过程和函数不允许执行LOAD
DATA INFILE语句,存储过程和函数可以调用其他的过程或者函数。

插入小知识点@:

1.用户变量:以"@"开始,形式为"@变量名"
用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效。
2.全局变量:定义方式 set GLOBAL 变量名  或者  set @@global.变量名 
对所有客户端生效,只有具有super权限才可以设置全局变量。

现在有表如下:

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)

创建存储过程,传入性别(男或女),显示对应性别的学生id,返回对应性别的人数:

DELIMITER $$
CREATE PROCEDURE myprocedure(IN sex CHAR,OUT num INT)
BEGIN
    SELECT  sid FROM student WHERE gender=sex;
    SELECT FOUND_ROWS() INTO num;   
END $$
DELIMITER ;

调用:

CALL myprocedure('女',@num)

图片 1

查看人数@num:

SELECT @num

图片 2

 

测试:

定义条件和处理

 条件的定义和处理可以用来定义在处理过程中遇到问题时相应的处理步骤。

 语法如下:

条件定义:
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)

(1)当没有进行条件处理的时候:

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=1,主键重复了,直接退出了,并没有执行余下的语句,所以@x的值为2。

 (2)可以对主键重复进行处理:

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的值会在是SQLWARNING、NOT
FOUND、SQLEXCEPTION,这个3个值是3种定义好的错误类别,分别代表不同的含义:

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记

NOT FOUND是对所有以02开头的SQLSTATE代码的速记

SQLEXCEPTION是对所有没有被SQLWARNING或者NOT
FOUND捕获的SQLSTATE代码的速记。

以上的declare continue handler for sqlstate ‘23000’ set
@x2=1;也可以用以下几种方式来写:

#捕获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 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)

(1)当没有进行条件处理的时候:

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=1,主键重复了,直接退出了,并没有执行余下的语句,所以@x的值为2。

 (2)可以对主键重复进行处理:

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的值会在是SQLWARNING、NOT
FOUND、SQLEXCEPTION,这个3个值是3种定义好的错误类别,分别代表不同的含义:

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记

NOT FOUND是对所有以02开头的SQLSTATE代码的速记

SQLEXCEPTION是对所有没有被SQLWARNING或者NOT
FOUND捕获的SQLSTATE代码的速记。

以上的declare continue handler for sqlstate ‘23000’ set
@x2=1;也可以用以下几种方式来写:

#捕获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;

 

相关文章

发表评论

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

*
*
Website