首页 > 数据库 >面试再问MySQL存储过程和触发器就把这篇文章给他

面试再问MySQL存储过程和触发器就把这篇文章给他

时间：2023-07-04 19:24:03浏览次数：44  
标签：触发器 -- empno uname Delimiter 篇文章 MySQL my select

Mysql存储过程及触发器trigger

 

存储过程

一、一个简单的存储过程

1,一个简单的存储过程

	delimiter $$
	create procedure testa()
	begin
	 Select * from emp;
	 Select * from dept;
	 End;
	$$;
	delimiter ;
	-- 调用存储过程
	call testa();

存储过程的结构组成：

1，创建格式:create procedure 存储过程名

2，包含一个以上代码块，代码块使用begin和end之间

3，在命令行中创建需要定义分隔符 delimiter $$

2,存储过程的特点

1，能完成复杂的判断和运算

2，可编程性强，灵活

3，SQL编程的代码可重复使用

4，执行速度相对快

5，减少网络之间数据传输，节省开销

二、存储过程变量

1，存储过程中的变量

需求：编写存储过程，使用变量取empno=7369的用户名

Delimiter $$;
Create procedure testa();
BEGIN 
	DECLARE my_uname varchar(32) default ""; -- 定义变量my_uname
 SET my_uname='smith'; -- 为变量my_uname赋值
 -- 查询empno=7369的用户名，并将值赋给my_uname
 select ename into my_uname from emp where empno=7369; -- 为变量赋值
 select my_uname;-- 返回my_uname的值
END;
$$;
Delimiter ;

特点：

1，变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用。

2，变量具有数据类型和长度，与mysql的SQL数据类型保持一致，还能指定默认值、字符集和排序规则等。

3，变量可以通过set来赋值，也可以通过select into的方式赋值。

4，变量需要返回，可以使用select语句，如：select 变量名

2，存储过程变量应用示例

需求：统计表emp、dept的行数和emp表中最早，最晚的入职日期。

Delimiter $$;
Create procedure stats_emp();
BEGIN
-- 统计emp和dept表中的记录数
 BEGIN
 DECLARE emp_sum int default 0;
 DECLARE dept_sum int default 0;
 select count(*) into emp_sum from emp; 
 select count(*) into dept_sum from dept; 
 select emp_sum,dept_sum;
 END;
-- 统计最早、最晚入职日期
 BEGIN
 DECLARE max_time TIMESTAMP ;
 DECLARE min_time TIMESTAMP;
 select max(hiredate),min(hiredate) into max_time,min_time from emp;
 select max_time,min_time; 
	END;
END
$$;
Delimiter ;

三、存储过程的参数

1，存储过程的传入参数IN

需求：编写存储过程，传入empno,返回该用户的ename.

Delimiter $$;
Create procedure test_param(IN my_empno int);
--------
BEGIN
 DECLARE my_ename varchar(32) default '';
 select ename into my_ename from emp where empno=my_empno;
 select my_ename;
END;
$$
Delimiter ;
-- 调用
Call test_param(7369);

提示：

1，传入参数：类型为IN,表示该参数的值必须在调用存储过程时指定，如果不显示指定为IN，那么默认就是IN类型。

2，IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回。

3，如果调用存储过程中需要修改和返回值，可以使用OUT类型参数。

2，存储过程的传出参数OUT

需求：调用存储过程时，传入empno,返回该用户的ename。

Delimiter $$;
create procedure test_param(IN my_empno int,OUT my_ename varcahr(32));
--------
BEGIN
 select ename into my_ename from emp where empno=my_empno;
 select my_ename;
END;
$$
Delimiter ;
-- 调用
Set @uname=’’;
Call test_param_out(7369,@uname);

提示：

1，传出参数：在调用存储过程中，可以改变其值，并可返回。

2，OUT是传出参数，不能用于传入参数值。

3，调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量。

4，如果既需要传入，同时又需要传出，则可以使用INOUT类型参数。

3,存储过程的可变参数INOUT

需求：调用存储过程时，参数my_empno和my_ename,既是传入，也是传出参数。

Delimiter $$;
create procedure test_param_inout(INOUT my_empno int,INOUT my_ename varchar(32));
BEGIN
 set my_empno=7369;
 set my_ename="smith";
 
 select ename,empno into my_ename,my_empno from emp where empno=my_empno;
END;
$$
Delimiter ;
-- 调用
set @uname:='';
set @empno:=7399;
call test_param_inout(@empno,@uname);
select @empno,@uname;

特点：

1，可变变量INOUT,调用时可传入值，在调用过程中，可以修改其值，同时也可以返回值。

2，INOUT 参数集合了IN和OUT类型参数的功能

3，INOUT调用时传入的是变量，而不是常量

四、存储过程条件语句

1，存储过程的条件语句

需求：编写存储过程，如果用户empno是偶数则给出ename,其他情况只返回empno.

Delimiter $$;
create procedure test_if(IN my_empno int);
BEGIN
	DECLARE my_ename VARCHAR(32) default ''; 
 if(my_empno %2=0) then 
 select ename into my_ename from emp where empno=my_empno;
	select my_ename;
 else 
 select my_empno;
 end if;
END;
$$
Delimiter ;
-- 调用
call test_if(7369);

特点：

1，条件语句最基本结构： if() then ... else ... end if;

2,if判断返回逻辑真或者假，表达式可以是任意返回真或假的表达式

2，存储过程的条件语句应用示例

需求：根据用户传入的empno参数判断：

（1）如果用户sal小于2000，则给用户加薪200

（2）如果用户sal小于1000，则给用户加薪500

（3）其他情况加薪100

Delimiter $$;
create procedure test_if_else(IN my_empno int);
BEGIN
	DECLARE my_sal int default 0;
 select sal into my_sal from emp where empno=my_empno;
 if(my_sal<1000) then 
 update emp set sal=sal+500 where empno=my_empno; 
 ELSEIF(my_sal<2000)
 then update emp set sal=sal+200 where empno=my_empno; 
 else update emp set sal=sal+100 where empno=my_empno; 
 end if;
END;
$$
Delimiter ;
-- 调用
call test_if_else(7369);

特点：

多条件判断结构：

If()

Then

...

Else if()

Then

...

Else

...

End if;

五、存储过程循环语句

1，while循环

需求：使用循环语句，向表emp中插入10条empno连续的记录。

Delimiter $$;
create procedure test_while();
BEGIN
 DECLARE i int default 0;
 while(i<10) DO
 BEGIN
 set i=i+1;
 insert into acc(id) values(i);
 END;
 END WHILE;
 
END;
$$
Delimiter ;
-- 调用
call test_while();

特点：

1，while语句最基本结构： while() do begin ... end end while;;

2,while判断返回逻辑真或者假，表达式可以是任意返回真或假的表达式

2,repeat循环语句

需求：使用repeat循环向表acc插入10条id连续的记录

Delimiter $$;
create procedure test_repeat();
BEGIN
 DECLARE i int default 100;
 REPEAT
 BEGIN
 set i=i+1;
 insert into acc(id) values(i);
 END;
 UNTIL i>=110
 END REPEAT;
END;
$$
Delimiter ;
-- 调用
call test_repeat();

特点：

1，repeat语句最基本结构： repeat begin ... end until end repeat;;

2,while判断返回逻辑真或者假，表达式可以是任意返回真或假的表达式

六、存储过程游标的使用

1,什么是游标

需求：编写存储过程，使用游标，把uid为偶数的记录逐一更新用户名。

Delimiter $$;
create procedure test_cursor();
BEGIN
 DECLARE stopflag INT DEFAULT 0; -- 游标停止的标记 0：未停止 1：已停止
 DECLARE my_uname VARCHAR(32) default ''; -- 存储查询出的用户名
 DECLARE uname_cursor CURSOR for select uname from acc where uid%2=0; -- 定义游标uname_cursor，并指定结果集
 DECLARE CONTINUE HANDLER for NOT found set stopflag=1; -- 游标结束后stopflag设置为1
	 open uname_cursor; -- 打开游标
 FETCH uname_cursor into my_uname; -- -- 游标向前走一步，取出一条记录放到my_uname中my_uname
 WHILE(stopflag=0)
 DO
 BEGIN
 update acc set uname=CONCAT(my_uname,"_cur") where uname=my_uname;
 FETCH uname_cursor into my_uname; 
 END;
 end WHILE;
 close uname_cursor;
END;
$$
Delimiter ;
-- 调用
call test_repeat();

特点：

declare uname_cur Cursor for select uname from acc where uid%2=0;

1，游标是保存查询结果的临时内存区域

2，游标变量uname_cur保存了查询的临时结果，实际上就是查询结果集

Declare continue handler for not found set stopflag=1;

3，当游标变量中保存的结果都查询一遍（遍历），到达结尾，把变量stopflag设为1。

4， FETCH uname_cursor into my_uname; -- 游标向前走一步，取出一条记录放到my_uname中

Mysql函数

一、一个简单函数

需求：编写函数，传入一个uid,返回用户的uname

Delimiter $$;
 CREATE FUNCTION f01_simple(my_uid int) RETURNS varchar(32) CHARSET utf8
	BEGIN
 DECLARE my_uname varchar(32) default '';
 select uname into my_uname from acc where uid=my_uid;
	RETURN my_uname;
END
$$
Delimiter ;
-- 调用
Select f01_simple(2);

特点：

1，创建函数使用 create function 函数名(参数) return 返回值

2，函数体放在begin和end之间

3，return 指定函数的返回值

4，函数调用: select 函数名(实参);

二、自定义函数综合应用示例

1，自定义函数示例01

需求：输入用户uid,获得accountid,uid,uname组合的uuid值，作为用户的唯一标识。

Delimiter $$;
 CREATE FUNCTION test_uuid(my_uid int) RETURNS varchar(32) CHARSET utf8
BEGIN
 DECLARE uuid varchar(32) default '';
 select CONCAT(accountid,"_",uid,"_",uname) INTO uuid from acc where uid=my_uid;
 RETURN uuid;
END
$$;
Delimiter ;
-- 调用
Select test_uuid(2);

2，自定义函数示例02

需求：输入用户uid,计算该uid所在帐号下的所有订单的总价。

触发器trigger

场景：通常用 于审计、业务数据完整性

1，什么是触发器

需求：处于审计目的，当有人往表users插入一条记录时，把插入的uid,uname和动作及操作时间记录下来。

Delimiter $$;
CREATE TRIGGER `tr_users_insert` AFTER INSERT ON `users` 
FOR EACH ROW 
	BEGIN
-- New 插入后当前的用户信息
 insert into oplog(uid,uname,action,optime) VALUES(NEW.uid,NEW.uname,'insert',now());
END;
$$;
Delimiter ;

特点：

1，创建触发器使用create trigger 触发器名称。

2，什么时候触发？ after insert on users,处理after还有before ,是在对表操作之前(before)或者之后(after)触发动作的。

3，对什么操作事件触发？After insert on users,操作时间包括insert,update,delete

4,对什么表触发？ after insert on users

5,影响的范围？For each row

触发器：与函数、存储过程一样，触发器是一种对象，它能根据对表的操作事件，触发一些动作，这些动作可以是insert,update,delete等修改操作。

2，生产环境触发器实例

需求：处于审计目的，当删除users表时，记录删除前该记录的主要字段值。

Delimiter $$;
CREATE TRIGGER `tr_user_delete` BEFORE DELETE ON `users` FOR EACH ROW begin 
-- OLD对字段表更新前的数据 
 insert into oplog(uid,uname,action,optime,old_value) values(OLD.uid,OLD.uname,"delete",now(),OLD.regtime);
END;
$$;
Delimiter ;

标签：触发器,--,empno,uname,Delimiter,篇文章,MySQL,my,select
From： https://www.cnblogs.com/nifrecxgh/p/17526776.html

相关文章

  • MacBook的mysql无法连接pycharm问题
    问题1:1018-Can'treaddirof'./luffy/'(errno:13-Permissiondenied)这个错误提示表明在Django应用程序中无法读取目录"./luffy/"，MySQL数据库连接配置不正确或没有足够的权限访问数据库引起的。而我的连接配置是正确的，所以问题是没有足够的权限1.打开终端，用root用户进......
  • MySQL存储之为什么要使用B+树做为储存结构？
    导言：在使用MySQL数据库的时候，我们知道了它有两种物理存储结构，hash存储和B+树存储，由于hash存储使用的少，而B+树存储使用的范围就多些，如InnoDB和MYISAM引擎都是使用的B+树作为存储结构，B+树，顾名思义，它还是树形结构，那么它是怎么演变过来的，那么就需要从数据结构的角度来分析了一.顺......
  • Windows和Linux下mysql新建用户
    Windows下载xampp，同时启动Apache（设端口为8081）和mysql（3306）。启动cmd，mysql-uroot-p登录root权限，密码默认为空。然后设置root密码setpasswordfor'root'@'localhost'=password('123456');flushprivileges;访问localhost:8081/phpmyadmin，用户名root，输入密码即可进入图形界......
  • mysql数据库语法总结--存储过程、函数、视图、触发器、表
    ​概述抽空总结一下mysql的一些概念性内容，涉及存储过程、函数、视图、触发器等。一、查看存储过程、函数、视图、触发器、表1、存储过程select*frommysql.procwheretype='PROCEDURE';showprocedurestatus;showcreateprocedureproc_name;//存储过程定义​编......
  • windows下mysql中binlog日志分析和数据恢复
    1.首先查看是否开启了binlogshowvariableslike'%log_bin%'; 我的已经开启了，如果没开启则开启binlog2.查看有哪些binlog文件和正在使用的binlog文件 查看有哪些binlog文件showbinarylogs;或者showmasterlogs; 查看当前正在使用的是哪一个binlog文件show......
  • kubesphere mysql搭建与测试
    1.搭建kubesphere搭建mysql很简单，直接去应用商店找到mysql安装就可以了。2.搭建情况应用： 服务：工作负载： 容器： 保密字典： 编辑设置找到root密码：xxxxxx3.测试run一个k8smysql客户端bash-5.1#kubectlrun-it--rm--image=mysql:5.7--restart=Nevermysq......
  • 从0开始，手写MySQL数据管理器DM
    文章很长，且持续更新，建议收藏起来，慢慢读！疯狂创客圈总目录博客园版为您奉上珍贵的学习资源：免费赠送:《尼恩Java面试宝典》持续更新+史上最全+面试必备2000页+面试必备+大厂必备+涨薪必备免费赠送:《尼恩技术圣经+高并发系列PDF》，帮你实现技术自由，完成职业升级，薪......
  • textare内容直接存储mysql，怎么原样输出到页面，保留输入的空格 回车换行
    使用thinkcmf时，一个textarea类型的模版变量，配置内容里需要显示换行和多个连续空格。 查看数据库存储json保存数据后是有存储回车换行和多个空格的，只是显示时不显示回车换行，多个连续空格变成了一个空格。使用{$theme_vars.foot1|nb2br}仅能保留回车换行，多个空格只显示一个......
  • CentOS7使用yum安装MySQL8
    1、下载MySQL的repo源wgethttps://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm2、安装MySQL源rpm-Uvhmysql80-community-release-el7-1.noarch.rpm3、安装公钥rpm--importhttps://repo.mysql.com/RPM-GPG-KEY-mysql-2022 4、使用yum安装yum......
  • 如何在Windows上安装MySQL和MySQL Workbench
    如果你想学习MySQL，从一个好的客户端开始非常有帮助——尤其是当你刚刚开始你的旅程时。有很多客户端可以满足您基于MySQL的需求，例如XAMPP、DataGrip等。其中，我更喜欢MySQLWorkbench。顺便说一句，它是完全免费的。在本教程中，我将向您展示如何从头开始安装和配置此MySQL和......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99