Mysql之存储过程和函数
Mysql之存储过程和函数
https://blog.51cto.com/13266497/2161027
https://blog.csdn.net/u012326462/article/details/83445404
存储过程就是一条或多条SQL语句的集合,可视为批文件,但是其作用不仅用于批处理。
存储程序分为:1、存储过程 2、函数
使用Call语句来调用存储过程,只能用输出变量返回值。
一、创建存储过程
语法:
create procedure sp_name(proc_parameter)
[characteristics……] routine_body
创建存储函数名为sp_name,存储过程的名为:proc_parameter
指定存储参数列表为:
[IN | OUT | INOUT] param_name type
其中IN表示输入参数,OUT表示输出参数,INOUT表示即可输入也可输出
param_name表示参数名称
type 表示参数类型,该类型可以是Mysql数据库中的任意类型。
characteristics 指定存储过程的特性,有以下取值:
LANGUAGE SQL:说明routine_body部分是由SQL语句组成,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值。
[NOT] DETERMINISTIC:指明存储过程执行的结果是否正确。
1. DETERMINISTIC表示结果是正确的。每次执行存储过程时,相同输入会得到相同的输出。
2. NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL |REDAS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。
1. CONTAINS SQL:表示子程序包含SQL语句,但是不包含读写数据的语句。
2. NO SQL:表示子程序不包含SQL语句。
3. REDAS SQL DATA :说明子程序包含数据的语句。
4. MODIFIES SQL DATA:表明子程序包含写数据的语句。默认为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER}:指明谁有权限来执行。
1. DEFINER表示只有定义者才能执行。
2. INVOKER表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER
COMMENT ‘string’:注释信息,可以用来描述存储过程或函数。
routine_body是SQL代码的内容。通常用begin……end表示SQL代码的开始和结束。
编写存储过程并不是简单的事情,可能存储过程中需要复杂的SQL语句,并且要创建存储过程的权限;但是使用存储过程将简化操作,减少冗余的操作步骤,同时,还可以减少操作过程中的失误、提高效率,因此存储过程非常的有用,而且应该尽量学会使用。
例1:
mysql> delimiter // # 定义SQL语句的结束符号为//,使用这条命令时,应该避免(‘\’)字符,因为反斜线是Mysql的转意符。
mysql> create procedure p1()
-> begin
-> select * from t;
-> end //
mysql> delimiter ;
mysql> show procedure status \G # 查看存储过程信息
mysql> call p1 # 读取这个存储过程
例2:
mysql> delimiter //
mysql> create procedure p2(n int)
-> begin
-> select * from t where id = n;
-> end //
mysql> delimiter ;
mysql> show procedure status \G
mysql> call p2(1) # 需要带入取值
例3:
mysql> create database db_proc;
mysql> use db_proc
mysql> CREATE TABLE `proc_test` (
-> `id` tinyint(4) NOT NULL AUTO_INCREMENT,
-> `username` varchar(20) NOT NULL,
-> `password` varchar(20) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
mysql> delimiter //
mysql> create procedure mytest(in name varchar(20),in pwd varchar(20))
-> begin
-> insert into proc_test(username,password) values(name,pwd);
-> end //
mysql> delimiter ;
mysql> call mytest('lxq','password') ;
mysql> select * from proc_test; # 验证插入了数据
************************
mysql存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT
Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数
表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT 输出参数
该值可在存储过程内部被改变,并可返回
INOUT 输入输出参数
调用时指定,并且可被改变和返回
IN参数例子:
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
-> BEGIN
-> SELECT p_in; # 查询输入参数
-> SET p_in=2; # 修改
-> select p_in; #查看修改后的值
-> END //
mysql> DELIMITER ;
执行结果:
mysql> set @p_in=1;
mysql> call sp_demo_in_parameter(@p_in);
mysql> select @p_in;
以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值
OUT参数例子
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
-> BEGIN
-> SELECT p_out; # 查看输出参数
-> SET p_out=2; # 修改参数值
-> SELECT p_out; # 看看有否变化
-> END //
mysql> DELIMITER ;
执行结果:
mysql> SET @p_out=1;
mysql> CALL sp_demo_out_parameter(@p_out);
mysql> SELECT @p_out;
p_out在存储过程中被修改,直接影响@p_out的值
INOUT参数例子:
mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
mysql> DELIMITER ;
执行结果:
set @p_inout=1;
call sp_demo_inout_parameter(@p_inout);
select @p_inout;
***********************************************************************************************************
二、特定异常
在MySQL中,特定异常需要特定处理。这些异常可以联系到错误,以及子程序中的一般流程控制。定义异常是事先定义程序执行过程中遇到的问题,异常处理定义了在遇到问题时对应当采取的处理方式,并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。
1 异常定义
1.1 语法
DECLARE condition_name CONDITION FOR [condition_type];
1.2 说明
condition_name 参数表示异常的名称;
condition_type 参数表示条件的类型,condition_type由SQLSTATE [VALUE] sqlstate_value|mysql_error_code组成:
sqlstate_value和mysql_error_code都可以表示MySQL的错误;
sqlstate_value为长度为5的字符串类型的错误代码;
mysql_error_code为数值类型错误代码;
1.3 示例
定义“ERROR 1148(42000)”错误,名称为command_not_allowed。可以有以下两种方法:
# 方法一:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
# 方法二:使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
2 自定义异常处理
2.1 异常处理语法
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement
2.2 参数说明
handler_type: CONTINUE|EXIT|UNDO
handler_type为错误处理方式,参数为3个值之一;
CONTINUE表示遇到错误不处理,继续执行;
EXIT表示遇到错误时马上退出;
UNDO表示遇到错误后撤回之前的操作,MySQL暂不支持回滚操作;
condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
condition_value表示错误类型;
SQLSTATE [VALUE] sqlstate_value为包含5个字符的字符串错误值;
condition_name表示DECLARE CONDITION定义的错误条件名称;
SQLWARNING匹配所有以01开头的SQLSTATE错误代码;
NOT FOUND匹配所有以02开头的SQLSTATE错误代码;
SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码;
mysql_error_code匹配数值类型错误代码;
2.3 异常捕获方法
方法一:捕获sqlstate_value异常
这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02",执行CONTINUE操作,并输出"NO_SUCH_TABLE"信息
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
方法二:捕获mysql_error_code异常
这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146,执行CONTINUE操作,并输出"NO_SUCH_TABLE"信息;
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
方法三:先定义条件,然后捕获异常
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
方法四:使用SQLWARNING捕获异常
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
方法五:使用NOT FOUND捕获异常
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
方法六:使用SQLEXCEPTION捕获异常
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';定义条件和处理程序:
mysql> create table test.t(s1 int,primary key(s1));
mysql> delimiter //
mysql> create procedure handlerdermo()
-> begin
-> declare CONTINUE HANDLER FOR SQLSTATE '23000' set @x2 = 1;
-> set @x = 1;
-> insert into test.t values (1);
-> set @x = 2;
-> insert into test.t values (1);
-> set @x = 3;
-> end //
mysql> delimiter ;
mysql> call handlerdermo();
mysql> select @x;
mysql> select * from test.t;
三、函数
函数的作用:提高代码的复用率
函数可以调用函数中的方法来实现某些功能
利用now()来实现空参数函数:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-08-16 18:19:09 |
+---------------------+
mysql> select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
+------------------------------------------------------+
| date_format(now(),'%Y年%m月%d号 %H点%i分%s秒') |
+------------------------------------------------------+
| 2018年08月16号 18点19分57秒 |
+------------------------------------------------------+
------------------------------------------------------------------------------------
mysql> create function my_time() returns varchar(50)
-> return date_format(now(),'%Y-%m-%d %H-%i-%s');
Query OK, 0 rows affected (0.00 sec)
mysql> select my_time();
+---------------------+
| my_time() |
+---------------------+
| 2018-08-16 18-22-10 |
+---------------------+
函数分为空参数函数和传参函数
注意:函数必需要有返回值类型用returns描述
returns后面跟的是函数体
如果函数体只有单条就直接描述
函数体如果有多条 在returns后面 begin开始 函数体结束后要写end结束
end之前一定要确定返回值
-----------------------------------------------------------------------------------------------
创建传参函数:
mysql> CREATE FUNCTION cont_AVG(num1 int,num2 int) RETURNS decimal(8,2)
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)
mysql> select cont_AVG(2,2);
+---------------+
| cont_AVG(2,2) |
+---------------+
| 2.00 |
+---------------+
1 row in set (0.00 sec)
mysql> select cont_AVG(3,2);
+---------------+
| cont_AVG(3,2) |
+---------------+
| 2.50 |
+---------------+
创建给stu表添加用户的多函数体传参函数:
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> select * from stu;
Empty set (0.00 sec)
mysql> delimiter //
mysql> create function adduse(u_id int unsigned,u_name varchar(10))
-> returns int unsigned
-> begin
-> insert stu values(u_id,u_name);
-> return last_insert_id();
-> end //
mysql> delimiter ;
mysql> select adduse(1,'zs');
+----------------+
| adduse(1,'zs') |
+----------------+
| 0 |
+----------------+
1 row in set (0.02 sec)
mysql> select adduse(2,'ls');
+----------------+
| adduse(2,'ls') |
+----------------+
| 0 |
+----------------+
1 row in set (0.01 sec)
mysql> select adduse(3,'ww');
+----------------+
| adduse(3,'ww') |
+----------------+
| 0 |
+----------------+
1 row in set (0.02 sec)
mysql> select * from stu;
+----+------+
| id | name |
+----+------+
| 1 | zs |
| 2 | ls |
| 3 | ww |
+----+------+
3 rows in set (0.00 sec)
方法一:使用临时表批量更新表数据 (注意操作用户需要有mysql的创建临时表权限)
delimiter $$
# 删除 已有的 存储过程
DROP PROCEDURE IF EXISTS update_user_account_method;
# 创建新的存储过程
CREATE PROCEDURE update_user_account_method()
-- 批量更新HIK+手机号
BEGIN
# 批量更新sx_为xs_
UPDATE sys_user t
SET t.account = REPLACE (t.account,'sx_','xs_');
# 创建临时表
-- 不存在则创建临时表
create temporary table if not exists tmp(id bigint(20) primary key,ac varchar(20) not null) ;
-- 使用前先清空临时表
truncate table tmp;
# 查询HIK的账户数
SET @num=(SELECT COUNT(1) FROM sys_user WHERE account like concat('%','HIK','%'));
# HIK的账户数大于0则执行
if @num>0 then
insert into tmp(id,ac)
SELECT user_id,random_num
FROM (
SELECT user_id,concat('xs_',FLOOR(RAND()*50000000 + 50000000)) AS random_num
FROM sys_user WHERE account like concat('%','HIK','%')
) AS ss
WHERE random_num NOT IN (SELECT account FROM sys_user);
end if;
# 根据临时表更新表格对应的字段
UPDATE sys_user , tmp
SET sys_user.account = tmp.ac
WHERE sys_user.user_id = tmp.id;
# 使用后删除临时表
drop table tmp;
END $$
delimiter ;
# 执行存储过程
CALL update_user_account_method;
# 删除 已有的 存储过程
DROP PROCEDURE update_user_account_method;
方法二:使用游标批量更新表数据
delimiter //
# 删除已有的存储过程
DROP PROCEDURE IF EXISTS update_user_info;
-- 定义更新数据表存储过程
CREATE PROCEDURE update_user_info()
BEGIN
-- 定义存储过程变量
DECLARE userIds BIGINT(20);
DECLARE accounts VARCHAR(32);
DECLARE stopCur INT DEFAULT 0;
-- 定义游标(更新指定部分数据)
DECLARE cur CURSOR FOR (SELECT user_id,account FROM sys_user ORDER BY user_id LIMIT 60,3);
-- 定义游标结束,当遍历完成时,将stopCur设置为null ,也可以写成 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stopCur = null;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopCur = null;
-- 开游标
OPEN cur;
-- 游标向下走一步,将查询出来的两个值赋给定义的两个变量
FETCH cur INTO userIds,accounts;
-- 循环体
WHILE( stopCur IS NOT NULL) DO
-- 更新对应关系表数据
UPDATE driver SET email=accounts WHERE user_id = userIds ;
-- 游标向下走一步
FETCH cur INTO userIds,accounts;
END WHILE;
-- 关闭游标
CLOSE cur;
END //
delimiter;
# 执行存储过程
CALL update_user_info;
# 删除 已有的 存储过程
DROP PROCEDURE update_user_info;
---------------------
作者:珠穆雪峰
来源:CSDN
原文:https://blog.csdn.net/qq_20086125/article/details/78760290