专栏首页码猿技术专栏Mysql存储过程和存储函数

Mysql存储过程和存储函数

文章目录

1. Mysql存储过程和存储函数

1.1. 存储过程的好处

1.2. 存储函数

1.2.1. 创建存储函数

1.2.1.1. 指定参数

1.2.2. 调用存储函数

1.2.3. 删除存储函数

1.2.4. 查看存储函数状态

1.2.5. 查看存储函数的定义

1.2.6. 修改存储函数

1.3. 变量的使用

1.3.1. 定义变量

1.3.2. 定义用户变量

1.3.3. 为变量赋值

1.3.4. 实例

1.3.4.1. 在存储函数中使用

1.3.4.2. 在存储过程中使用

1.4. 注释

1.5. 流程控制标签的使用

1.5.1. IF - THEN - ELSEIF - ELSE -ENDIF

1.5.2. CASE - WHEN - THEN - ELSE - END CASE

1.5.3. LOOP - ENDLOOP

1.5.4. LEAVE

1.5.5. ITERATE

1.5.6. REPEAT

1.5.7. WHILE

1.6. 存储过程

1.6.1. 创建存储过程

1.6.2. 参数

1.6.3. 过程体

1.6.4. 实例

1.6.5. 调用存储过程

1.6.6. 查看存储过程的状态

1.6.7. 查看存储过程的定义

1.6.8. 删除存储过程

1.7. 游标cursor

1.7.1. 声明游标

1.7.2. 打开游标

1.7.3. 使用游标获取一行数据

1.7.4. 关闭游标

1.7.5. 实例

1.8. 存储过程和存储函数的区别

1.9. 总结

1.10. 参考文章

Mysql存储过程和存储函数

存储过程的好处

  • 增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
  • 标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
  • 较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
  • 减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。
  • 作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

存储函数

创建存储函数

  • 在Mysql中有许多已经存在的存储函数,比如CONCAT(..),LENGTH(str)。但是我们也可以自己定义存储函数。
  • 格式如下:
delimiter //    -- 指定分割符
	create function fun_name()
	returns type     -- type是执行存储函数返回的类型
	begin 
	-- 执行其他的语句
	return ();   -- 返回的数据
	end
	//

delimiter ;  -- 指定创建结束
  • returns type : 指定存储函数返回的类型,比如returns char(50),returns int
  • 存储函数有且只有一个返回值
  • return () : 存储函数的返回值,这里的返回值类型需要和returns type中的类型一致,如果不一致会强制转换
    • return (select name from user where id=1);
  • 下面我们创建一个存储函数,返回user表中的id=1name
delimiter //
	create function selectUserById()
	returns varchar(50) 
	begin 
	return (select name from user where id=1);
	end
	//
	delimiter ;

指定参数

  • 在存储函数创建的时候还可以指定参数,这个参数是用户调用的时候输入的。
  • 存储函数中的参数默认是IN参数,而存储过程中的参数可以是INOUTINOUT
  • 直接使用parameter 类型指定即可,如果有多个参数可以使用,分割
  • 在调用的时候直接使用select funName(parmeter1,....);即可
delimiter //
	create function selectUserById(uid int)
	returns varchar(50) 
	begin 
	return (select name from user where id=uid);
	end
	//
	delimiter ;

调用存储函数

  • 存储函数是依赖数据库的,因此我们需要在指定的数据库中调用,或者前面指定数据库的名称
    • select selectUserById(); : 直接在存储函数所在数据库中调用
    • select dbName.selectUserById(); : 直接使用数据库的名称调用

删除存储函数

  • drop function selectUserById; :直接在存储函数所在数据库中直接删除存储函数
  • drop function dbName.selectUserById; :使用数据库名称删除存储函数

查看存储函数状态

  • 格式:show function status [like pattern] : 查看存储函数的状态
    • show function status \G : 查看所有的存储函数状态,\G是一种特定格式的输出
    • show function status like 'select%'\G :查看select开头的存储函数状态,\G是一种特定格式的输入。

查看存储函数的定义

  • 格式:show create function dbName.funName
    • show create function test.selectUserById \G; :查询test数据库中的存储函数selectUserById的定义,\G是一种特定的输出格式

修改存储函数

变量的使用

  • 变量的作用范围是begin.....end程序中

定义变量

  • 格式:declare var_name,.... type [default value]
    • declare age int default 22 :定义一个局部变量age,类型为int,默认值为22
    • declare var1,var2,var3 int : 定义三个局部变量,类型为int
  • 全部变量的声明一定要在赋值的前面,否则报错

定义用户变量

  • 用户变量以@开头
  • set @pin=10

为变量赋值

  • 格式:set var1=value1,[var2=value2,....]
    • set age=33; : 设置age的值为33
    • set var1=22,var2=33: 同时设置多个值
declare var1,var2,var3 int;
set var1=22,var2=33;
set var3=var1+var2;
  • 使用select col_name[,...] into var_name[,....] table_expr : 使用select查询得到的结果赋值给变量
    • 这个select把选定的列的值直接赋值给对应位置的变量
    • table_expr: 可以是表的查询条件,其中包含from 表名
declare uname varchar(10);  -- 定义变量uname
declare uage int;    -- 定义变量uage
select name,age into uname,uage from user where id=1;  -- 将id=1的用户姓名和年龄赋值给变量

实例

在存储函数中使用

  • 在存储函数中定义局部变量,并且获取输出
delimiter //
	create function selectUserById(uid int)
	returns varchar(50) 
	begin 
	declare uname varchar(50);
	select name into uname from user where id=uid;
	return uname;
	end
	//
	delimiter ;

在存储过程中使用

delimiter //
	create procedure selectUserById(IN uid int)
		begin
			declare offest,count int;   -- 定义偏移量
			set offest=0,count=2;       -- 赋值
			if uid is not null          -- 如果uid不为null,按照id查询
				then select * from user where id=uid;  -- 按照id查询
				else select * from user limit offest,count;  -- 否则uid为null,按照分页查询前面两个
			end if;
		end 
		//
delimiter ;
  • call selectUserById(1); : 查询id=1的用户信息
  • call selectUserById(null); :查询所有的用户信息,显示前面两个

注释

  • MySQL存储过程可使用两种风格的注释:
    • 双杠:–,该风格一般用于单行注释
    • C风格: 一般用于多行注释

流程控制标签的使用

  • beginend之间使用

IF - THEN - ELSEIF - ELSE -ENDIF

  • 格式
begin
	if expression   -- 判断条件
    	then .... ;   -- 条件成立执行
    	elseif .....;   -- 其他条件
    	else ..... ;  -- 条件相反执行
    endif;   		  -- 结束if
end
  • 可以不是成对出现,比如只有if,或者if-else
  • 如果没有else,那么可以省略,比如if - then - endif
  • 判断相等使用=
  • 实例
delimiter //
	create procedure selectUserById(IN uid int)
		begin
			declare offest,count int;   -- 定义偏移量
			set offest=0,count=2;       -- 赋值
			if uid is not null          -- 如果uid不为null,按照id查询
				then select * from user where id=uid;  -- 按照id查询
				else select * from user limit offest,count;  -- 否则uid为null,按照分页查询前面两个
			end if;
		end 
		//
delimiter ;

CASE - WHEN - THEN - ELSE - END CASE

  • 这个和java中的switch-case-default相似
  • 格式:
case expr
	when value1 then ....;
	when value2  then .....;
	when......;
	....
	else  .......;
end case;
  • 实例
    • 创建一个存储过程,使用case
delimiter //
	create procedure deleteUserById(IN uid int)
		begin
			case uid   -- uid做选择
				when 1   -- uid==1
					then delete from user where id=1;   
				when 2   -- uid==2
					then delete from user where id=2;
				else   
					delete from user;	  -- 删除全部
			end case;
		end;
		//
delimiter ;

LOOP - ENDLOOP

  • LOOP只是创建一个循环执行的过程,并不进行条件判断,这个和while不一样,不需要判断条件,如果不跳出,那么将会永远的执行的下去。但是我们可以使用leave跳出循环
  • 格式:
[LOOP_LABEL]:LOOP
	statement;
END LOOP [LOOP_LABEL];
  • 实例
    • 执行这个语句可以插入9条数据,如果i>=10跳出循环
delimiter //
	create procedure insertUserByName(IN uname varchar(50))
		begin	
			declare i int default 0;
			add_loop:loop  -- 开始循环
				set i=i+1;  -- id++操作
				insert into user(name) values(uname);  -- 插入语句
				if i>=10
					then leave add_loop;   -- 使用leave跳出循环
				end if;
			end loop add_loop;   -- 结束循环
		end
		//
delimiter ;

LEAVE

  • 和循环一起使用,用于退出循环控制,见上面的例子

ITERATE

  • 格式:iterate label
  • iterate只可以出现在LOOPREPEATWHIE语句内,表示再次循环的意思,label表示循环的标志
  • 实例
    • 如果p<10重复执行p++
delimiter //
	create procedure doiterate()
		begin	
			declare p int default 0;  -- 定义局部变量
			my_loop:loop
				set p=p+1;  -- p++
                if p<10 
                	then iterate my_loop;  -- 继续执行前面的循环的语句,p++
                elseif p>20
                	then leave my_loop;
                end if
        			select "p在10到20之间"   -- 输出语句
			end loop my_loop;
		end
		//
delimiter ;

REPEAT

  • 这个也是循环语句,相当于do-while
  • 格式:
[repeat_loop]: repeat
	statement_list;
	until exper    -- 没有分号
	end repeat;
  • 实例
delimiter //
	create procedure dorepeat()
		begin	
			declare p int default 0;  -- 定义局部变量
			my_loop:repeat
				set p=p+1;
				select p;
				until p>10  -- 当p>10的时候循环结束
			end repeat my_loop;
		end
		//
delimiter ;

WHILE

  • 这个和REPEAT不同,先进行判断,然后才执行语句
  • 格式:
[while_label]:while expr do
	statement_list;
	end while [while_lable];
  • 实例
delimiter //
	create procedure dowhile()
		begin	
			declare p int default 0;  -- 定义局部变量
			my_loop:while p<10  do   -- 满足条件才执行
				set p=p+1;   -- p++
			end while my_loop;    -- 结束循环
		end
		//
delimiter ;

存储过程

  • 存储过程没有返回值

创建存储过程

  • 格式:
delimiter //
create procedure p_name([IN,OUT,INOUT]parameter 类型.....)
	begin
	-- 执行功能
	end
	//
delimiter ;

参数

  • 存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用”,”分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:
    • IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
    • OUT:该值可在存储过程内部被改变,并可返回
    • INOUT:调用时指定,并且可被改变和返回

过程体

  • 过程体的开始与结束使用BEGINEND进行标识。

实例

  • 定义一个根据id查询的查询用户信息的存储过程,这里的id是由用户输入的,因此可以使用IN参数
delimiter //
	create procedure selectUserById(IN uid int)
		begin
			select * from user where id=uid;
		end
		//
delimiter ;

调用存储过程

  • 格式:call procedure_name(...)
    • call selectUserById(1); : 直接在当前的数据库中调用存储过程selectUserById
    • call db_name.selectUsrById(1) : 指定数据库的名字调用

查看存储过程的状态

  • 格式:show procedure status like pattern \G
    • show procedure status like "select%"\G : 查看select开头的存储过程状态
    • show procedure status \G : 查看所有的存储过程状态
  • 查询的结果如下:
*************************** 1. row ***************************
                  Db: test   -- 数据库名称
                Name: selectUserById     -- 存储过程的名字
                Type: PROCEDURE  
             Definer: root@localhost
            Modified: 2018-06-25 22:25:44
             Created: 2018-06-25 22:25:44
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

查看存储过程的定义

  • 格式:show create procedure db.pro_name
    • show create procedure test.selectUserById\G : 查询数据库test中存储过程的定义
  • 返回的结果如下:
*************************** 1. row ***************************
           Procedure: selectUserById
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `selectUserById`(IN uid int)
begin
select * from user where id=uid;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

删除存储过程

  • 格式drop procedure pro_name
    • drop procedure selectUserById : 删除当前数据库的selectUserById的存储过程
    • drop procedure test.selectUserById;: 删除test数据库的selectUserById的存储过程

游标cursor

  • 在面对大量的数据的时候,游标能够一行一行的读取数据

声明游标

  • 格式:declare cursor_name cursor for select_statement
    • cursor_name : 游标的变量名称
    • select_statement :表示select语句,用于返回一个结果集给游标
  • 比如: declare users cursor for select name,age from user;

打开游标

  • 格式:open cursor_name;
    • open users

使用游标获取一行数据

  • 格式:fetch cursor_name into var_name[,var_name,...]
    • cursor_name:表示游标的名称
    • var_name : 表示将select语句查询到的一行信息存入到该参数中,var_name必须在声明游标之前定义好
  • 比如: fetch user into uname,uage

关闭游标

  • 格式:close cursor_name

实例

  • 使用游标获取user表中的一行数据
delimiter //
    create procedure selectOneUser()
        begin
        	declare uname varchar(50);  -- 定义uname存储
        	declare uage int;   -- 定义uage存储
        	declare users cursor for select name,age from user; -- 声明游标
        	open users;      -- 打开游标
        	fetch users into uname,uage;  -- 获取一行数据到存储到uname和uage中
        	select uname as name,uage as age;    -- 输出一行的结果
        	close users;    -- 关闭游标
        end
        //
delimiter ;

call selectOneUser();  -- 调用存储过程,此时只是输出第一行的数据
  • 使用循环获取所有的数据
    • 这里使用循环获取,首先需要使用select count(*)获取总数
delimiter //
    create procedure selectUsers()
        begin
        	declare uname varchar(50);  -- 定义uname存储
        	declare uage int;   -- 定义uage存储
        	declare total int default 0;  -- 定义count,这个用来统计总数
        	declare i int default 1;  -- 用来循环
        	declare users cursor for select name,age from user; -- 声明游标
        	select count(*) from user into total;   -- 查询总数
        	open users;      -- 打开游标
        	-- 开始循环遍历
    		my_loop:while i<=total do
                set i=i+1;  -- i++
                fetch users into uname,uage;  -- 获取一行数据到存储到uname和uage中
                select uname as name,uage as age;    -- 输出一行的结果
    		end while my_loop;
        	close users;    -- 关闭游标
        end
        //
delimiter ;

call selectUsers();  -- 调用存储过程,获取全部数据
  • 使用HANDLER判断游标是否还有元素
    • continue HANDLER for not found
    • 当游标中没有值的时候就会指定返回的值
delimiter //
    create procedure selectUsers()
        begin
        	declare uname varchar(50);  -- 定义uname存储
        	declare uage int;   -- 定义uage存储
        	declare flag int default 1;  -- 创建结束游标的标志,默认值为1
        	declare users cursor for select name,age from user; -- 声明游标
        	declare continue HANDLER for not found set flag=0;  -- 指定游标结束时的返回值
        	open users;      -- 打开游标
        	my_loop:loop
        		if flag=0  -- 这里使用=,否则报错
        			 then leave my_loop;  -- 跳出循环
        		end if;
        		fetch users into uname,uage;  -- 获取一行数据到存储到uname和uage中
				select uname as name,uage as age;    -- 输出一行的结果
        	end loop my_loop;
        	close users;    -- 关闭游标
        end
        //
delimiter ;

存储过程和存储函数的区别

  1. 存储函数可以使用return返回一个返回值,但是存储过程不能有返回值,如果需要实现返回的功能,可以使用OUT参数实现返回
  2. 存储函数只能有输入参数,而且不能带in, 而存储过程可以有多个in,out,inout参数。
  3. 存储过程中的语句功能更强大,存储过程可以实现很复杂的业务逻辑,而函数有很多限制,如不能在函数中使用insert,update,delete,create等语句;存储函数只完成查询的工作,可接受输入参数并返回一个结果,也就是函数实现的功能针对性比较强。
  4. 存储过程可以调用存储函数。但函数不能调用存储过程。
  5. 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

总结

  1. 存储过程中可以使用call调用其他的存储过程,但是不能使用drop语句删除其他的存储过程
  2. 存储过程的参数不要和数据库表的字段相同,否则将出现无法预料的结果

参考文章

delimiter //
	create function selectUserById()
	returns varchar(50) 
	begin 
	declare uname varchar(50);
	select name into uname from user where id=2;
	return uname;
	end
	//
	delimiter ;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 用户管理模块之用户登录

    爱撒谎的男孩
  • Servlet总结四(过滤器)

    爱撒谎的男孩
  • springBoot整合spring-data-redis

    爱撒谎的男孩
  • day28.Redis【Python教程】

    下载:打开redis官方网站 https://redis.io/,推荐下载稳定版本(stable)

    Java帮帮
  • 【云计算奇妙学习之旅】第五期:存储基础架构

    在上一期中分享虚拟化的第一个重要内容:计算虚拟化。那么,本期就进入第二个重要内容:存储虚拟化。在讲存储虚拟化之前,我们需要先了解存储是什么。

    誉天小鹿
  • 存储04-存储的三种类型:块/文件/对象

    存储按照对外提供服务的方式分为:块存储、文件存储、对象存储。块存储即我们日常说的SAN存储;文件存储即我们日常说的NAS存储;对象存储是最近几年才兴起的一种存储...

    大话IT架构
  • 如何利用公共云存储构建中小企业存储

    中小型企业(SME)的技术需求虽然比大企业的少,但仍然很重要。其中的关键是中小企业的存储。

    CloudBest
  • 深度||全球存储二十年并购回顾,中国存储何时迎来春天?

    1998--2018年是全球存储工业从崛起走向成熟的二十年。回顾这二十年全球存储市场的并购之路,我们发现收购金额超过10亿美金的重要收购超过50个,涵盖了从传统...

    大数据在线
  • 如何利用公共云存储构建中小企业存储

    内部部署的超融合基础设施对于中小企业来说非常适合,这消除了对SAN存储及其相关技能的需求,但是,对于希望实现存储基础设施现代化的中小企业,有许多方法可以从云存储...

    静一
  • 云存储是如何工作的?

    如今,企业越来越多地采用云存储选项,因为它们需要更多的容量、弹性容量以及更好的方式来管理存储成本。事实证明,越来越多的企业数据和云数据难以让IT部门单独使用他们...

    静一

扫码关注云+社区

领取腾讯云代金券