devbox
我家自动化
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL存储过程常用相关知识_mysql 存储过程 条件

作者：我家自动化 | 2024-07-24 08:00:31

赞

踩

mysql 存储过程 条件

目录

存储过程：

        存储过程的创建

        调用存储过程

        查看存储过程

MySQL变量

        系统变量

        查看系统变量：

        设置系统变量：

       用户变量

        给用户变量赋值

        读取用户变量的值

        局部变量

        变量的声明

        变量的赋值

if语句

参数

case语句

循环

while循环

repeat循环

loop循环

游标

        使用步骤

捕捉异常并处理

存储函数

触发器

        作用

        语法格式

        触发器的NEW和OLD的关键字：

        触发器的优点

        查看触发器

        删除触发器

存储过程：

        存储过程在实际开发中，存储过程还是很少使用的。只有在系统遇到性能瓶颈，在进行优化的时候，对于大数量的应用来说，可以考虑使用一些。

        优点：

        速度快：降低了应用服务器和数据库服务器之间网络通讯的开销。尤其是在数据量庞大的情况下，效果更显著。

        缺点：

        移植性差；编写难度大；维护性差

存储过程的创建

  1. create produce p1()
  2. begin
  3.     select empno,ename,from emp;
  4. end;

调用存储过程

call p1();

查看存储过程

        方法一：查看创建存储过程的语句

show create procedure p1;

        方法二：查看information_schema.routines

        讲解：(替换成自己的）

select * from information_schema.routines where routine_name = 'p1';

        通过系统表information_schema.routines可以查看存储过程的在状态信息。

        在mysql中只要创建了一个存储过程对象，在information_schema.routines系统表中就会增加一条记录，这条记录是专门描述存储过程对象的状态

         information_schema.routines这个系统表当中存储的不仅包括存储过程的状态信息，也包括函数对象，触发器对象等的状态信息。

        关键列：information_schema。ROUTINES表中的一些重要的列包括（不仅限以下）：

        SPECIFIC_NAME：存储过程的具体名称，包括该存储过程的名字，参数列表。

        ROUTINE_SCHEMA：存储过程所在的数据库名称。

        ROUTINE_NAME：存储过程的名称。

        ROUTINE_TYPE：PROCEDURE表示是一个存储过程，FUNCTION表示是一个函数。

        ROUTINE_DEFINITION：存储过程的定义语句。

        CREATED：存储过程的创建时间。

        LAST_ALTERED：存储过程的最后修改时间。

        DATA_TYPE：存储过程的返回值类型、参数类型等。

        删除存储过程：

drop procedure if exists p1;

        delimiter命令

       delimiter命令用于改变MySQL解释句的定界符。mySQL默认使用“；”作为语句定界符。而使用delimiter命令可以将“；”更改为其他字符。（以下例子中将以“//”为定界符。

  1. delimiter //
  2.  
  3. CREATE PROCEDURE my_proc ()
  4. BEGIN
  5. SELECT * FROM my_table;
  6. INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
  7. END //

MySQL变量

系统变量

        指在MySQL服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，来满足不同的需求。

        系统变量可以分为：全局（globa）或会话（session）作用域：

        全局作用域：指对所有连接和所有数据库都适用。

        会话作用域：指只对当前连接和当前数据库适用。

        查看系统变量：

  1. show [global|session] variables;
  2.  
  3. show [global|session] variables like '';
  4.  
  5. select @@[global|session.]系统变量名;

        注意：没有指定session或global时，默认时session

        设置系统变量：

  1. set [global | session] 系统变量名 = 值;
  2.  
  3. set @@[global | session.]系统变量名 = 值;

        注意：无论是全局设置还是会话设置，mysql服务重启之后，之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。但是不建议采用修改my.ini这种方式。

  1. 在my.ini修改
  2. [mysqld]
  3. autocommit=0

用户变量

        用户变量用户自定义的变量。只在当前会话有效。所有的用户变量‘@’开始。

        给用户变量赋值

  1. set @name = 'jackson';
  2. set @age := 30;
  3. set @gender := '男', @addr := '黄海';
  4. select @email := 'jackson@123.com';
  5. select sal into @sal from emp where ename ='SMITH';

        读取用户变量的值

select @name, @age, @gender, @addr, @email, @sal;

        注意：mysql中变量不需要声明。直接赋值就行。如果没有声明变量，直接读取该变量，返回null

局部变量

        在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。

        变量的声明

declare 变量名 数据类型 [default ...];

        变量的赋值

  1. set 变量名 = 值;
  2. set 变量名 := 值;
  3. select 字段名 into 变量名 from 表名 ...;

if语句

        语法格式

  1. if 条件 then
  2. ......
  3. elseif 条件 then
  4. ......
  5. elseif 条件 then
  6. ......
  7. else
  8. ......
  9. end if;

        案例：员工月薪sal，超过10000属于“高收入”，5000到10000属于“中收入”，少于5000的属于“低收入”。

  1. create procedure p3()
  2. begin
  3. 	declare sal int default 5000;
  4. 	declare grade varchar(20);
  5. 	if sal > 10000 then
  6.   	set grade := '高收入';
  7. 	elseif sal >= 6000 then
  8.   	set grade := '中收入';
  9. 	else
  10.   	set grade := '低收入';
  11. 	end if;
  12. 	select grade;
  13. end;
  14.  
  15. call p3();

参数

存储过程的参数包括三种形式：

        in：入参（未指定时，默认时in）

        out：出参

        inout：既是入参，又是出参

案例：员工月薪sal，超过10000属于“高收入”，5000到10000属于“中收入”，少于5000的属于“低收入”。

  1. create procedure p4(in sal int, out grade varchar(20))
  2. begin
  3. 	if sal > 10000 then
  4.   	set grade := '高收入';
  5. 	elseif sal >= 6000 then
  6.   	set grade := '中收入';
  7. 	else
  8.   	set grade := '低收入';
  9. 	end if;
  10. end;
  11.  
  12. call p4(5000, @grade);
  13. select @grade;

case语句

        语法格式

        格式一：

  1. case 值
  2. 	when 值1 then
  3. 	......
  4. 	when 值2 then
  5. 	......
  6. 	when 值3 then
  7. 	......
  8. 	else
  9. 	......
  10. end case;

       格式二：

  1. case
  2. 	when 条件1 then
  3. 	......
  4. 	when 条件2 then
  5. 	......
  6. 	when 条件3 then
  7. 	......
  8. 	else
  9. 	......
  10. end case;

        案例：根据不同月份，输出不同的季节。

        方法一：

  1. create procedure mypro(in month int, out result varchar(100))
  2. begin 
  3. 	case month
  4. 		when 3 then set result := '春季';
  5. 		when 4 then set result := '春季';
  6. 		when 5 then set result := '春季';
  7. 		when 6 then set result := '夏季';
  8. 		when 7 then set result := '夏季';
  9. 		when 8 then set result := '夏季';
  10. 		when 9 then set result := '秋季';
  11. 		when 10 then set result := '秋季';
  12. 		when 11 then set result := '秋季';
  13. 		when 12 then set result := '冬季';
  14. 		when 1 then set result := '冬季';
  15. 		when 2 then set result := '冬季';
  16. 		else set result := '非法月份';
  17. 	end case;
  18. end;
  19.  
  20. call mypro(9, @season);
  21. select @season;

        方法二：

  1. create procedure mypro(in month int, out result varchar(100))
  2. begin 
  3. 	case 
  4. 		when month = 3 or month = 4 or month = 5 then 
  5. 			set result := '春季';
  6. 		when  month = 6 or month = 7 or month = 8  then 
  7. 			set result := '夏季';
  8. 		when  month = 9 or month = 10 or month = 11  then 
  9. 			set result := '秋季';
  10. 		when  month = 12 or month = 1 or month = 2  then 
  11. 			set result := '冬季';
  12. 		else 
  13. 			set result := '非法月份';
  14. 	end case;
  15. end;
  16.  
  17. call mypro(9, @season);
  18. select @season;

循环

while循环

        语法格式

  1. while 条件 do
  2. 	循环体;
  3. end while;

        案例：传入一个数字n，计算1~n中所有偶数的和

  1. create procedure mypro(in n int)
  2. begin
  3. 	declare sum int default 0;
  4. 	while n > 0 do
  5.   		if n % 2 = 0 then
  6.     		set sum := sum + n;
  7.   		end if;
  8.   		set n := n - 1;
  9. 	end while;
  10. 	select sum;
  11. end;
  12.  
  13. call mypro(10);

repeat循环

        语法格式

  1. repeat
  2. 	循环体;
  3. 	until 条件
  4. end repeat;

        注意：条件成立时，结束循环。

        案例：传入一个数n，计算1~n中所有偶数的和

  1. create procedure mypro(in n int, out sum int)
  2. begin 
  3. 	set sum := 0;
  4. 	repeat 
  5. 		if n % 2 = 0 then 
  6. 		  set sum := sum + n;
  7. 		end if;
  8. 		set n := n - 1;
  9. 		until n <= 0
  10. 	end repeat;
  11. end;
  12.  
  13. call mypro(10, @sum);
  14. select @sum;

loop循环

        语法格式：

  1. [begin_label:] LOOP
  2.     -- 循环体
  3.     [IF condition THEN
  4.         LEAVE [loop_label];
  5.     END IF;]
  6. END LOOP [loop_label;]
  7.  
  8.  
  9. LEAVE label; -- 退出指定标记的循环体
  10. ITERATE label; -- 直接进入下一次循环

        案例：（输出结果为1~9）

  1. create procedure mypro()
  2. begin 
  3. 	declare i int default 0;
  4.   mylp:loop 
  5. 		set i := i + 1;
  6. 		if i = 5 then 
  7. 			iterate mylp;
  8. 		end if;
  9. 		if i = 10 then 
  10. 		  leave mylp;
  11. 		end if;
  12. 		select i;
  13. 	end loop;
  14. end;

游标

        概述：只想结果集中某条记录的指针，允许程序注意访问结果集中的每条记录，并对其进行逐行操作和处理。

使用步骤

        声明游标语法：declare 游标名称 cursor for 查询语句；

        打开游标语法：open 游标名称；

        通过游标获取数据的语法：fetch 游标名称 into 变量,变量,变量

        关闭游标的语法：close 游标名称；

        案例：从dept表中查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中。

  1. create procedure p7()
  2. begin 
  3.  
  4. 	declare no int;
  5. 	declare name varchar(100);
  6. 	declare dept_cursor cursor for select deptno,dname from dept;
  7.  
  8. 	drop table if exists dept2;
  9. 	create table dept2(
  10. 		no int primary key,
  11. 		name varchar(100)
  12. 	);
  13. 	
  14. 	open dept_cursor;
  15. 	
  16. 	while true do
  17. 		fetch dept_cursor into no, name;
  18. 		insert into dept2(no,name) values(no,name);
  19. 	end while;
  20. 	
  21. 	close dept_cursor;
  22. end;
  23.  
  24. call p7();

        注意：声明局部变量和声明游标有顺序要求，局部变量的声明需要在游标声明之前完成。

捕捉异常并处理

        语法格式

  1. DECLARE ... HANDLER
  2.  
  3. DECLARE handler_action HANDLER FOR condition_value statement;

        DECLARE handler_action：声明一个处理程序。

        condition_value： 是异常的条件值。

        statement：是在捕捉到异常时要执行的SQL语句。

        案例：给上面的游标添加异常处理机制

  1. create procedure P7()
  2. begin 
  3.  
  4. 	declare no int;
  5. 	declare name varchar(100);
  6. 	declare dept_cursor cursor for select deptno,dname from dept;
  7.  
  8. 	declare exit handler for not found close dept_cursor;
  9.  
  10. 	create table dept2(
  11. 		no int primary key,
  12. 		name varchar(100)
  13. 	);
  14. 	
  15. 	open dept_cursor;
  16. 	
  17. 	while true do
  18. 		fetch dept_cursor into no, name;
  19. 		insert into dept2(no,name) values(no,name);
  20. 	end while;
  21. 	
  22. 	close dept_cursor;
  23. end;
  24.  
  25. call p7();

存储函数

        存储函数：带返回值的存储过程。参数只允许是in（但不能写显示的写in）。没有out，也没有inout。

        语法格式：

  1. CREATE FUNCTION 存储函数名称(参数列表) RETURNS 数据类型 [特征]
  2. BEGIN
  3. 	--函数体
  4. 	RETURN ...;
  5. END;

        特征的可取重要值：

        deterministic：用该特征标记该函数为确定性函数

        no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用。

        reads sql data：用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。

        案例：计算1~n的所有偶数之和

  1. -- 删除函数
  2. drop function if exists sum_fun;
  3.  
  4. -- 创建函数
  5. create function sum_fun(n int)
  6. returns int deterministic 
  7. begin 
  8. 	declare result int default 0;
  9. 	while n > 0 do 
  10. 		if n % 2 = 0 then 
  11. 			set result := result + n;
  12. 		end if;
  13. 		set n := n - 1;
  14. 	end while;
  15. 	return result;
  16. end;
  17.  
  18. -- 调用函数
  19. set @result = sum_fun(100);
  20. select @result;

触发器

        触发器：一种数据库对象，可以在特定的数据操作中如插入（insert），更新（update），或删除（delete）触发时自动执行。

作用

        1、强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。

        2、数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。

        3、执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。

语法格式

  1. CREATE TRIGGER trigger_name
  2. BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
  3. BEGIN
  4. -- 触发器执行的 SQL 语句
  5. END;

        trigger_name：触发器的名称

        BEFORE/AFTER：触发器的类型，可以是 BEFORE 或者 AFTER

        INSERT/UPDATE/DELETE：触发器所监控的 DML 调用类型

        table_name：触发器所绑定的表名

        FOR EACH ROW：表示触发器在每行受到 DML 的影响之后都会执行

        触发器执行SQL语句：该语句会在触发器被触发时执行

触发器的NEW和OLD的关键字：

        NEW：在INSERT和UPDATE触发器中引用新插入或更新的行。

        OLD：在UPDATE和DELETE触发器中引用原始被更新或删除的行。

触发器的优点

        数据的完整性：可以用于强制实施业务规则，确保数据的完整性。

        自动化任务：可以用于自动化常见任务，如日志记录、审计等。

        提高性能：可以在数据库层面上执行逻辑，减少了从应用程序到数据库之间的通信开销。

        案例：用于在order表中插入新纪录时更新order_count表中的订单数

  1. CREATE TRIGGER update_order_count
  2. AFTER INSERT ON orders
  3. FOR EACH ROW
  4. BEGIN
  5.     UPDATE order_count
  6.     SET count = count + 1;
  7. END;

查看触发器

show triggers;

删除触发器

DROP TRIGGER IF EXISTS trigger_name;
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/873601
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号