mysql 存储过程和函数

1.使用优点

1.提高代码重用性
2.简化操作
3.减少编译次数，减少与服务器的连接次数

一般插入、删除成千上万数据才会使用。

#存储过程

2.用户函数

1. 创建

delimiter $$//修改分解符
create procedure 存储过程名 [参数列表]
begin
方法体
end;
$$

参数列表包含三部分

• 参数模式 IN/OUT/INOUT
• 参数名
• 参数类型

2. 调用

如果存储过工程只有一句话 begin end可以省略

3.实例

1. 登录的存储过程

delimiter $$
create procedure myp3(in username varchar(30), in password varchar(20))
begin
declare result int(20) default 0;
	select count(*) into @result
	from user
	where user.username = username
	and user.password =password;
	select if(result > 0, '成功', '失败');
end $$
delimiter ;
call myp3("fuzekun", "123456");

2. 调用带参数的

call myp3("ga", @test)
select @test;

3. 删除评论时候级联修改表

DROP PROCEDURE if EXISTS mdftag;
CREATE PROCEDURE mdftag()
BEGIN
DECLARE t_id, cnt int;
DECLARE done int DEFAULT 0;
DECLARE f CURSOR FOR SELECT COUNT(tag_id) AS count, tag_id FROM article_tag GROUP BY tag_id;#游标中存储数量和id
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#先删除article_tag表中文章已经被删掉的行
 DELETE FROM article_tag WHERE article_tag.id not in (SELECT tmp.id FROM(
 	SELECT ag.id FROM article_tag AS ag, article AS a WHERE a.id = ag.article_id
 	)as tmp
);
#同时修改comment的数量
DELETE FROM `comment` WHERE `comment`.article_id not in (SELECT id from article);
#然后修改tag表中的数量
OPEN f;																						#打开游标
while done != 1 DO
	FETCH f INTO cnt, t_id;													#获取游标中信息
	UPDATE tag SET tag.count = cnt where id = t_id; #更新表
end WHILE;
CLOSE f;
#最后删除tag中已经为0的标签的数量
DELETE FROM tag WHERE tag.id not in (SELECT tag_id FROM article_tag);

end

4. 登录记录日志的登录存储过程

#用户登陆的producer
drop procedure if EXISTS login_test;

create procedure login_test(in username varchar(255),in psw varchar(255), in tkt VARCHAR(255), out st int)

BEGIN
 declare state int default 0, s_id int default 0;
 #选出的数目大于一state为整数
 select id, COUNT(*) into s_id, state from user where name = username and password = psw;
 if state > 0 THEN
 #选出的id用来插入日志表
 insert into login_ticket(expired, user_id, status, ticket) values (NOW(), s_id, state, tkt);
 select id into st from login_ticket where ticket = tkt;
 ELSE
 set st = 0;
 end IF;
end

4.使用游标的存储过程

删除表中的全部索引

DELIMITER $$
create procedure(dbname varchar(20), tbname varchar(20))
begin
declare _index varchar(20) default -;
declare _cur cursor for select index_name from information_schema.statistics where table schama=dbname and table_name=tbname and seq_in_index=1 and index_name<>PRIMARY;
#1.找到所有的索引并放入游标
open _cur;
fetch _cur into _index;
while `_index` <>do
	set @str=concat("drop index `_index` on `tbname`);#2.1编写sql语句
	prepare sql_str from @str;						 #2.2预编译sql语句
	execute sql_str;								 #2.2执行sql语句
	deallocate prepare sql_str;
	set `_index`;									 #index设成默认值
	fetch _cur into index;							 #循环
end while
close _cur
end$$
	

3.系统函数

1. 字符函数

#1.length
select length("john");
#2.concat 字符串连接
select concat (lastname, "_”, first_name) as 姓名 from user;
#3.upper, lower
select concat(upper(last_name), lower(first_name)) as 姓名 from user;
#4.substr substring
注意:索引从1开始
select substr('hello world', 7) as out_put;
select substr('hello world', 1, 3);

#案例
select concat(upper(substr(last_name,1,1)), '_', lower(substr(last_name, 2))) as out_put  from user;

#5.instr 返回子串第一次出现的索引，如果找不到返回0
select instr('test', 'es') as out_put;

#6.trim去除空格
select length(trim('   张'))as out_put;
select trim('aa', 'aaaaa张') as out_put;
#7.lpad rpad 用指定的字符实现左,右填充指定长度
select lpad('你好', 2, '*') as out_put;#左填充2个*
select rpad('你好', 3, '/') as out_put;#右填充3个/
# 8.replace 替换,用后边的替换前边的
select replace ('nihao', 'ni', 'ta');

2. 数学函数

# 1.round四舍五入
select round(-1.55);
select round(1.567,2); #保存两位小数
# 2. ceil向上取整, floor向下取整
select ceil(-1.02);
select floor(1.2);
# 3. truncate 截断
select truncate(1.434, 1);
# 4. mod 取余
select mod(10, -3);
select 10 % 3;

3. 日期函数

# 1.返回当前时间
select now()
# 2.返回日期,时间
select curdate
select curtime
# 3. 指定部分的 年 月 日 时 分 秒
select year(NOW()) AS 年;
select month(now()) as 月;
# 4.str_to_date 将字符串转成时间
select str_to_date('1998-3-2', '%Y-%c-%d') as out_put;
%Y 2020年
%y 20年
%c 表示 1 2 .. 12
%m 表示 01 02  .. 12
%d 天
%h 12小时
%H 24小时
%i 分钟
%s 秒

# 5.date_format
select date_format(now(), '%Y年%m月%d日') as out_put;

4. 其他函数

select version();
select database();
select user();

5. 流程控制函数

# if函数
if (10 > 5, '大于', '小于' )#相当于三元运算符
#case函数
case 要判断的字段或者表达式
when 常量1 then 要显示的值或者查询的语句1
when 常量2 then要显示的值或者查询的语句2
else 要显示的值或者查询的语句n
end as 别名

#case 用法二
case 要判断的字段或者表达式
when 条件1 then 要显示的值或者查询的语句1
when 条件2 then要显示的值或者查询的语句2
else 要显示的值或者查询的语句n
end as 别名