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=1的name值

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

复制

指定参数

• 在存储函数创建的时候还可以指定参数，这个参数是用户调用的时候输入的。
• 存储函数中的参数默认是IN参数，而存储过程中的参数可以是IN、OUT、INOUT
• 直接使用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风格： 一般用于多行注释

流程控制标签的使用

• 在begin和end之间使用

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只可以出现在LOOP，REPEAT，WHIE语句内，表示再次循环的意思，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:调用时指定，并且可被改变和返回

过程体

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

实例

• 定义一个根据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. 存储过程的参数不要和数据库表的字段相同，否则将出现无法预料的结果