MySQL 5.6存储过程和函数语法的简单介绍

MySQL支持存储的例程（存储过程和存储函数）

存储的例程是一组可以存储在服务器中的SQL语句。完成此操作后，客户无需继续重新发出各个语句，而是可以引用存储的例程。

首先先进行存储的例程语法学习：

使用create procedure和create function语句创建存储的例程。

使用call语句调用过程，并且只能使用输出变量传回值。

可以像其他任何函数一样从语句内部调用一个函数（即，通过调用函数名称），并且可以返回标量值。

存储例程的主体可以使用复合语句

可以使用drop procedure和drop function语句删除存储的例程

可以使用alter procedure和alter function语句对其进行更改

简单演示：

-- 声明结束符，因为mysql默认使用“；”作为结束符，而在存储过程中，会使用“；”作为一段语句的结束，导致“；”使用冲突
delimiter $$
CREATE PROCEDURE hello_procedure()
BEGIN 
  SELECT 'hello procedure';
END $$
 
CALL hello_procedure()

一、变量和赋值

对于存储过程中的变量，可以类比java中的局部变量和成员变量的声明和使用；

1、局部变量：

用户自定义，在begin/end块中有效

语法：

声明变量： declare var_name type [default 'unkown'];

举例：declare nickname varchar(32);

set 赋值

-- 声明结束符，因为mysql默认使用“；”作为结束符，而在存储过程中，会使用“；”作为一段语句的结束，导致“；”使用冲突
delimiter $$
create PROCEDURE sp_var01()
BEGIN
 -- 局部变量（需要声明） 字符类型如果为char或者varchar，一定要表明单位长度，int可以不写
 DECLARE nickname VARCHAR(32) DEFAULT 'SF';
 
 select nickname;
 
 -- set赋值
 set nickname := 'UG';
 
 select nickname;
 
END $$
 
-- 使用call调用该函数
call  sp_var01()$$
 
-- 使用drop删除该函数，若想要修改该存储函数，mysql只能drop之后重新create
drop PROCEDURE sp_var01;

into 赋值

-- 首先创建一张表
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
  `id` int(11) DEFAULT NULL,
  `dep_name` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES ('1', '开发');
INSERT INTO `departments` VALUES ('2', '测试');
INSERT INTO `departments` VALUES ('3', '产品');
INSERT INTO `departments` VALUES ('4', '运维');
 
 
-- 创建存储函数
delimiter $$
create PROCEDURE sp_var01_into()
BEGIN
 -- 声明局部变量
 DECLARE dept_name VARCHAR(32) DEFAULT 'unkown';
 DECLARE dept_no int DEFAULT 0;
 
 -- select 查询要给局部变量赋值的属性 into 直接进行赋值
 SELECT d.id,d.dep_name into dept_no,dept_name from departments d where d.id = 1;
 
 select dept_no,dept_name;
END $$
 
call  sp_var01_into()$$
 
drop PROCEDURE sp_var01_into;

2、用户变量

用户自定义，当前会话（连接）有效

语法：

@var_name

不需要提前声明，使用即声明

set 赋值

delimiter $$
create PROCEDURE sp_var02()
BEGIN
	set @nick_name = 'WR';
END $$
 
CALL sp_var02 $$
select @nick_name $$

into 赋值

delimiter $$
create PROCEDURE sp_var_into()
BEGIN
	select d.dep_name into @deptname from departments d where d.id = 1;
END $$
 
CALL sp_var_into $$
select @deptname $$

 

3、会话变量

由系统提供，整个mysql服务器有效

语法：

@@global.var_name

4、全局变量

由系统提供，整个Mysql服务器有效

语法：

@@global.var_name

二、入参出参

-- 语法

in | out | inout param_name type

举例

-- int 类型演示
delimiter $$
create PROCEDURE sp_param01(in age int)
BEGIN
	set @user_age = age;
end $$
call sp_param01(10) $$
SELECT @user_age $$
 
delimiter $$
create PROCEDURE sp_param03(in `name` VARCHAR(32))
BEGIN
	set @user_name = `name`;
end $$
call sp_param03('liman') $$
SELECT @user_name $$
 
 
 
 
-- out 类型，只负责输出！
-- 需求：输出传入的地址字符串对应的部门编号。
delimiter $$
create PROCEDURE sp_param02(in deptname VARCHAR(32),out dept_no int(11))
BEGIN
	SELECT d.id into dept_no from departments d where d.dep_name = deptname;
	-- 此处强调，要么表起别名，要么入参名不与字段名一致
end $$
 
-- 测试
delimiter ;
set @dept_no = 7;
call sp_param02("开发",@dept_no);
select @dept_no;
 
 
-- inout类型
delimiter $$
create PROCEDURE sp_param04(inout `name` VARCHAR(32))
	BEGIN
		set `name` = CONCAT('hello ',`name`);
end $$
delimiter ;
set @user_name = '小明';
call sp_param04(@user_name);
SELECT @user_name;

三、流程控制--判断

if -- 语法
IF search_condition THEN statement_list 
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

举例： 

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(50) NOT NULL AUTO_INCREMENT,
  `ename` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `job` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `mgr` int(50) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `comm` varchar(25) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `deptno` int(50) NOT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;
 
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', 'smith', 'clerk', '7902', '1980-12-07 12:42:26', '8000.00', null, '1');
INSERT INTO `emp` VALUES ('2', 'martmin', 'saleman', '1630', '1981-10-14 12:43:24', '10000.00', null, '2');

-- 前置知识点：timestampdiff(unit,exp1,exp2)取差值，单位是unit
select TIMESTAMPDIFF(YEAR,e.hiredate,now()) from emp e where e.empno = '2'

delimiter //
 
CREATE PROCEDURE sp_hire ()
BEGIN
  DECLARE result VARCHAR (32) ;
  DECLARE years int;
  select TIMESTAMPDIFF(YEAR,e.hiredate,now()) into years from emp e where e.empno = '2';
 
	IF years > 40 THEN
		SET result = '元老' ;
	ELSEIF years > 38 THEN
		SET result = '老员工' ;
	ELSE
		SET result = '新手' ;
	END IF ; 
	SELECT result ;
END//
 
delimiter ;
 
CALL sp_hire();

case 语法：此语法是不仅可以用在存储过程，查询语句也可以用！

语法一（类比java的switch）
CASE case_value
        when when_value then statement_list
        [when when_value then statement_list] ...
        [else statement_list]
END CASE

 语法二：
CASE 
    when search_condition then statement_list
    [when search_condition then statement_list]
END CASE

举例： 

-- 需求：入职年限年龄<=38 是新手  >38<=40老员工 >40元老
-- 语法一：
-- 1.创建函数
delimiter $$
create PROCEDURE sp_hire_case_in(in year INT)
BEGIN
	DECLARE result VARCHAR(32);
	DECLARE message VARCHAR(64);
	CASE year
		when 40
			then 
					set result = '元老';
					set message = '老爷爷';
		when 38
			then 
					set result = '老员工';
					set message = '油腻中年人';
		ELSE  set result = '新手';
					set message = '萌新';
  end CASE;
  SELECT result,message;
end $$
delimiter;
 
-- 2.调用函数
call sp_hire_case_in(40);
 
-- 3.删除函数
drop PROCEDURE sp_hire_case_in;
 
-- 语法二：
-- 1.创建函数
delimiter $$
create PROCEDURE sp_hire_case()
BEGIN
	DECLARE result VARCHAR(32);
	DECLARE message VARCHAR(64);
	CASE
		when TIMESTAMPDIFF(YEAR,'2001-01-01',now()) > 40
			then 
					set result = '元老';
					set message = '老爷爷';
		when TIMESTAMPDIFF(YEAR,'2001-01-01',now()) > 38
			then 
					set result = '老员工';
					set message = '油腻中年人';
		ELSE  set result = '新手';
			  set message = '萌新';
     end CASE;
     SELECT result,message;
end $$
delimiter;
 
-- 2.调用函数
call sp_hire_case();
 
-- 3.删除函数
drop PROCEDURE sp_hire_case;
 
 
 

四、 流程控制-循环

LOOP 语法：
[begin_label:]LOOP
        statement_list
END LOOP [end_label]

举例：
    需要说明，loop是死循环，需要手动退出循环，我们可以使用leave来退出
    可以把leave看成我们Java中的break;
    与之对应的，就有iterate(继续循环),类比java中的continue

死循环处理解决方法：

-- 如有死循环处理，可以通过下面的命令查看并结束
 
show processlist;
 
kill  id;

实例： 

-- 需求：循环打印到1到10
-- leave控制循环的退出
delimiter $$
CREATE PROCEDURE sp_flow_loop()
begin 
	DECLARE c_index int DEFAULT 1;
  DECLARE result_str VARCHAR(256) default '1';
  cnt:LOOP
 
		if c_index >= 10
			then leave cnt;
		end if;
 
		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
 
  end loop cnt;
  select result_str;
    
end $$
 
delimiter ;
call sp_flow_loop();
 
 
delimiter $$
create PROCEDURE sp_flow_loop02()
begin 
  DECLARE c_index int  DEFAULT 1;
  DECLARE result_str VARCHAR(256) default '1';
  cnt:LOOP
 
		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
		if c_index < 10 then 
			 ITERATE cnt;  -- 约等于java的continue
		end if;
		-- 	下面这句话能否执行到？什么时候执行到
    leave cnt;
 
  end loop cnt;
  select result_str;
END $$
 
delimiter;
call sp_flow_loop02();

REPEAT语法:相当于java中的 DO...WHILE...

[begin_label:]REPEAT
   statement_list
until search_condition  -- 直到... 为止 才退出循环
END REPEAT [end_label]

 

举例：

-- 需求：循环打印1到10
delimiter $$
create PROCEDURE sp_flow_repeat()
BEGIN
	DECLARE c_index int DEFAULT 1;
	-- 	收集结果字符串
	declare result_str VARCHAR(256) DEFAULT '1';
	count_lab:REPEAT
		SET c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
		until c_index >= 10;
end REPEAT count_lab;
select result_str;
end $$
delimiter;
call sp_flow_repeat();

 while:类比Java中的while（）{}

 while语法：
[begin_label:] while search_condition DO
        statement_list
END WHILE [end_label]

-- 需求：循环打印1到10
delimiter $$
create PROCEDURE sp_flow_while()
begin 
	DECLARE c_index int DEFAULT 1;
	-- 收集结果字符串
  DECLARE result_str VARCHAR(256) DEFAULT '1';
 
	while c_index < 10 DO
		set c_index = c_index + 1;
		set result_str = CONCAT(result_str,',',c_index);
	end while;
	SELECT result_str;
end $$
 
delimiter ;
call sp_flow_while();

五、流程控制 -- 退出、继续循环

leave:类比java的Break;

-- 退出leave can be used within begin ... end or loop constructs(LOOP,REPEAT,WHILE).
LEAVE label

ITERATE:类比java的continue;

-- 继续循环 ITERATE can appear only within LOOP,and while statements
ITERATE label

六、游标

用游标得到某一个结果集，逐行处理数据 （不建议用，因为是一行一行进行扫描的，效率比较低）
类比 jdbc的resultSet

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
 
-- 打开语法
OPEN cursor_name
 
-- 取值语法(当fetch 到底 报错 no datas ) 每次只能一个值
FETCH cursor_name into var_name [,var_name] ...
 
-- 关闭语法
CLOSE cursor_name

实例：

-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
drop PROCEDURE if EXISTS sp_create_table;
 
delimiter $$
create PROCEDURE sp_create_table(in dept_name VARCHAR(32))
BEGIN 
  DECLARE emp_no int;
  DECLARE emp_name VARCHAR(32);
  DECLARE emp_sal DECIMAL(7,2); 
 
  DECLARE lp_flag boolean DEFAULT true;
 
  DECLARE emp_cursor cursor FOR  
		SELECT e.empno,e.ename,e.sal from emp e 
		inner join departments d 
		on e.deptno = d.id where d.dep_name = dept_name;
 
  -- handle 句柄
  DECLARE CONTINUE HANDLER for 1329 set lp_flag = false;
 
  OPEN emp_cursor;
 
  emp_loop:LOOP
		FETCH emp_cursor into emp_no,emp_name,emp_sal; -- 一个fetch每次只能取一个值
		if lp_flag then 
			SELECT emp_no,emp_name,emp_sal;
		ELSE
			LEAVE emp_loop;
		END IF;
	END LOOP emp_loop;
  set @end_flag = 'end';
 
  CLOSE emp_cursor;
END $$
delimiter ;
CALL sp_create_table('开发');

特别注意：

在语法中，变量声明、游标声明、handle声明是必须按照先后顺序书写的，否则创建存储过程出错。

七、存储过程中的handle

HANDLER 语法：
DECLARE handler_action HANDLER 
	for condition_value [,condition_value] ...
statement
 
handler_ation:{
	CONTINUE
|EXIT
|UNDO
}
 
conditon_value:{
	mysql_error_code
|SQLSTATE [VALUE] sqlstate_value
|conditon_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
}