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

青旅半醒 2022-12-16 06:29 206阅读 0赞

## 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'

![2020102212492813.png][]

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\]

![20201025162427709.png][]

举例：

-- 需求：循环打印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
    }

[2020102212492813.png]: /images/20221123/e778cadeaf53434289236258c5f51507.png
[20201025162427709.png]: /images/20221123/2d539b06eb6f403abf3c57c98e652257.png