mysql 8存储过程_mysql8学习笔记18--存储过程和函数

• create procedure用来创建存储过程,create function用来创建函数

• 函数与存储过程最大的区别就是函数调用有返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可

• Definer和sql security子句指定安全环境

• Definder是MySQL的特殊的访问控制手段,当数据库当前没有这个用户权限时,执行存储过程可能会报错

• sql secuirty的值决定了调用存储过程的方式,取值 :definer(默认)或者invoker definer:在执行存储过程前验证definer对应的用户如:cdq@127.0.0.1是否存在,以及是否具有执行存储过程的权限,若没有则报错invoker:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限,若没有则报错

• IN,OUT,INOUT三个参数前的关键词只适用于存储过程,对函数而言所有的参数默认都是输入参数

• IN输入参数用于把数值传入到存储过程中;OUT输出参数将数值传递到调用者,初始值是NULL;INOUT输入输出参数把数据传入到存储过程,在存储过程中修改之后再传递到调用者

• Delimiter命令是改变语句的结束符,MySQL默认的结束符为;号,由于procedure和function中的;号并不代表创建的结束,所以要替换成另外的结束符以便表示创建的结束

• Rontine_body子句可以包含一个简单的SQL语句,也可以包含多个SQL语句,通过begin…end将这多个SQL语句包含在一起

• MySQL存储过程和函数中也可以包含类似create和drop等DDL语句

• Comment子句用来写入对存储过程和函数的注释

• Language子句用来表示此存储过程和函数的创建语言

• 存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果,反之如果是not deterministic则表示相同参数不会是相同结果,默认是not deterministic

• 相关属性短语只有咨询含义,并不是强制性的约束

• Contains sql表明此存储过程或函数不包含读或者写数据的语句,这是默认属性

• NO SQL表示此存储过程或函数不包含SQL语句

• Reads sql data表示此存储过程包含诸如select的查询数据的语句,但不包含插入或删除数据的语句

• Modifies sql data表示此存储过程包含插入或删除数据的语句

1ee6d992e51583ccd310a3dfe5fab167.png

• Drop procedure/function语句用来删除指定名称的存储过程或函数

a44154daaf25f91907b67eb211b16960.png

• If exists关键词用来避免在删除一个本身不存在的存储过程或函数时,MySQL返回错误

• Begin…end语句通常出现在存储过程、函数和触发器中,其中可以包含一个或多个语句,每个语句用;号隔开

cc16fdb0a2ab600f4791fd7755abfa1f.png

创建存储过程:

/*在创建之前如果存在同名存储过程,则删除*/drop PROCEDUREifEXISTS create_student;/*修改分分隔符为;;*/delimiter ;;

create PROCEDURE create_student (OUT new_stuid INT,IN new_name VARCHAR(10),IN birth date,IN addr VARCHAR(50),IN gender VARCHAR(10),IN sub_jct VARCHAR(10))

BEGIN/*为new_stuid声明变量*/DECLARE new_stuidint;

DECLARE stu_sexint;selectmax(stuid) into new_stuid from tb_student2;

set new_stuid= new_stuid +1;casestu_sex

WHEN gender='男生' THEN SET stu_sex=0;

WHEN gender='女生' THEN SET stu_sex=1;else set stu_sex=2;

ENDcase;/*插入数据到tb_student2*/insert INTO tb_student2(stuid,stuname,stusex,stubirth,stuaddr,collid) VALUES(new_stuid,new_name,stu_sex,birth,addr,2);

END;;

delimiter ;

调用存储过程:

mysql> call create_student(@new_stuid,"王超人",'1999-03-04','广东深圳','男生','计算机');

Query OK,1 row affected (0.08sec)

mysql> select@new_stuid;+------------+

| @new_stuid |

+------------+

| NULL |

+------------+

1 row in set (0.00sec)

mysql> select *from tb_student2;+-------+--------------+--------+------------+--------------+--------+

| stuid | stuname | stusex | stubirth | stuaddr | collid |

+-------+--------------+--------+------------+--------------+--------+

| 1001 | 张三 | 0 | 1990-03-04 | ABCabc | 1 |

| 1002 | 季墨 | 0 | 1992-02-02 | 湖南长沙 | 1 |

| 1033 | 王语嫣 | 1 | 1989-12-03 | 四川成都 | 1 |

| 1378 | 纪嫣然 | 1 | 1995-08-12 | 四川绵阳 | 1 |

| 1572 | 张子文 | 0 | 1993-07-19 | 陕西咸阳 | 1 |

| 1954 | 张学友 | 0 | 1994-09-20 | 福建莆田 | 1 |

| 2035 | 东方不败 | NULL | 1988-06-30 | NULL | 2 |

| 3011 | 金大牙 | 0 | 1985-12-12 | 福建莆田 | 3 |

| 3755 | 猪脚 | 0 | 1993-01-25 | NULL | 3 |

| 3923 | 杨不悔 | 1 | 1985-04-17 | 四川成都 | 3 |

| 3924 | 王超人 | 2 | 1999-03-04 | 广东深圳 | 2 |

| 3925 | 王超人2 | 2 | 1999-03-04 | 广东深圳 | 2 |

+-------+--------------+--------+------------+--------------+--------+

12 rows in set (0.00sec)

mysql>

把上面的例子改造成函数

/*在创建之前如果存在同名函数,则删除*/drop FUNCTIONifEXISTS create_student_function;/*修改分分隔符为;;*/delimiter ;;

create FUNCTION create_student_function (new_name VARCHAR(10),birth date,addr VARCHAR(50),gender VARCHAR(10),sub_jct VARCHAR(10)) RETURNS INT

BEGIN/*为new_stuid声明变量*/DECLARE new_stuidint;

DECLARE stu_sexint;selectmax(stuid) into new_stuid from tb_student2;

set new_stuid= new_stuid +1;casestu_sex

WHEN gender='男生' THEN SET stu_sex=0;

WHEN gender='女生' THEN SET stu_sex=1;else set stu_sex=2;

ENDcase;/*插入数据到tb_student2*/insert INTO tb_student2(stuid,stuname,stusex,stubirth,stuaddr,collid) VALUES(new_stuid,new_name,stu_sex,birth,addr,2);

RETURN new_stuid;

END;;

delimiter ;

调用函数

mysql> select create_student("王超人",'1999-03-04','广东深圳','男生','计算机');

ERROR1305 (42000): FUNCTION school.create_student does not exist

mysql> select create_student_function("王超人",'1999-03-04','广东深圳','男生','计算机');+---------------------------------------------------------------------------------------+

| create_student_function("王超人",'1999-03-04','广东深圳','男生','计算机') |

+---------------------------------------------------------------------------------------+

| 3926 |

+---------------------------------------------------------------------------------------+

1 row in set, 8 warnings (0.09sec)

mysql> select create_student_function("王超人",'1999-03-04','广东深圳','男生','计算机');+---------------------------------------------------------------------------------------+

| create_student_function("王超人",'1999-03-04','广东深圳','男生','计算机') |

+---------------------------------------------------------------------------------------+

| 3927 |

+---------------------------------------------------------------------------------------+

1 row in set (0.06sec)

mysql> select create_student_function("王超人",'1999-03-04','广东深圳','男生','计算机');+---------------------------------------------------------------------------------------+

| create_student_function("王超人",'1999-03-04','广东深圳','男生','计算机') |

+---------------------------------------------------------------------------------------+

| 3928 |

+---------------------------------------------------------------------------------------+

1 row in set (0.05sec)

mysql>

查看所有存储例程:

mysql> show procedure status like '%create_student%';+--------+----------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |

+--------+----------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| school | create_student | PROCEDURE | root@% | 2020-08-08 23:08:22 | 2020-08-08 23:08:22 | DEFINER | | utf8 | utf8_general_ci | utf8_bin |

+--------+----------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

1 row in set (0.00sec)

mysql> show function status like '%create%';+--------+-------------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |

+--------+-------------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| school | create_student_function | FUNCTION | root@% | 2020-08-09 16:47:51 | 2020-08-09 16:47:51 | DEFINER | | utf8 | utf8_general_ci | utf8_bin |

+--------+-------------------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

1 row in set (0.01sec)

mysql>

查看存储例程创建语句

mysql>show create procedure create_student;+----------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |

+----------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| create_student | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `create_student`(OUT new_stuid INT,IN new_name VARCHAR(10),IN birth date,IN addr VARCHAR(50),IN gender VARCHAR(10),IN sub_jct VARCHAR(10))

BEGIN/*为new_stuid声明变量*/DECLARE new_stuidint;

DECLARE stu_sexint;selectmax(stuid) into new_stuid from tb_student2;

set new_stuid= new_stuid +1;casestu_sex

WHEN gender='男生' THEN SET stu_sex=0;

WHEN gender='女生' THEN SET stu_sex=1;else set stu_sex=2;

ENDcase;/*插入数据到tb_student2*/insert INTO tb_student2(stuid,stuname,stusex,stubirth,stuaddr,collid) VALUES(new_stuid,new_name,stu_sex,birth,addr,2);

END| utf8 | utf8_general_ci | utf8_bin |

+----------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

1 row in set (0.00sec)

mysql> show create functioncreate_student_function;+-------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| Function | sql_mode | Create Function | character_set_client | collation_connection | Database Collation |

+-------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| create_student_function | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` FUNCTION `create_student_function`(new_name VARCHAR(10),birth date,addr VARCHAR(50),gender VARCHAR(10),sub_jct VARCHAR(10)) RETURNS int(11)

BEGIN/*为new_stuid声明变量*/DECLARE new_stuidint;

DECLARE stu_sexint;selectmax(stuid) into new_stuid from tb_student2;

set new_stuid= new_stuid +1;casestu_sex

WHEN gender='男生' THEN SET stu_sex=0;

WHEN gender='女生' THEN SET stu_sex=1;else set stu_sex=2;

ENDcase;/*插入数据到tb_student2*/insert INTO tb_student2(stuid,stuname,stusex,stubirth,stuaddr,collid) VALUES(new_stuid,new_name,stu_sex,birth,addr,2);

RETURN new_stuid;

END| utf8 | utf8_general_ci | utf8_bin |

+-------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

1 row in set (0.00sec)

mysql>

存储例程删除

mysql> drop functioncreate_student_function;

Query OK,0 rows affected (0.02sec)

mysql> show function status like '%create%';

Empty set (0.00sec)

mysql>drop procedure create_student;

Query OK,0 rows affected (0.03sec)

mysql> show procedure status like '%create_student%';

Empty set (0.00sec)

mysql>

• 标签label可以加在begin…end语句以及loop, repeat和while语句

• 语句中通过iterate和leave来控制流程,iterate表示返回指定标签位置,leave表示跳出标签

620277017b01a6b9ebcb1a6ff1fab5e7.png

使用label标签

drop PROCEDURE ifEXISTS test_procedure;

delimiter//CREATE procedure test_procedure(in p1 int,out p2 int)

BEGIN

label1:LOOP

set p1= p1 + 1;if p1 < 10 then ITERATE label1;END if;

LEAVE label1;

END LOOP label1;

set p2=p1;

END;//

调试下:

mysql> call test_procedure(5,@p2);

Query OK,0 rows affected (0.00sec)

mysql> select@p2;+------+

| @p2 |

+------+

| 10 |

+------+

1 row in set (0.00sec)

mysql>

Declare语句

• Declare语句通常用来声明本地变量、游标、条件或者handler

• Declare语句只允许出现在begin … end语句中而且必须出现在第一行

• Declare的顺序也有要求,通常是先声明本地变量,再是游标,然后是条件和handler

存储过程中的变量

• 本地变量可以通过declare语句进行声明

• 声明后的变量可以通过select … into var_list进行赋值,或者通过set语句赋值,或者通过定义游标并使用fetch … into var_list赋值

• 通过declare声明变量方法

c37f050dcd22e3022e9e9a975f1bc29c.png

• 使用default指定变量的默认值,如果没有指定默认值则初始值为NULL

• Type指明该变量的数据类型

• 声明的变量作用范围为被声明的begin … end语句块之间

• 声明的变量和被引用的数据表中的字段名要区分开来

变量声明在上面的例子里用到了,游标后面学到再写。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值