MySQL——存储过程和函数

心已赠人 2023-07-16 03:45 25阅读 0赞

## 存储过程（Procedure） ##

**一、创建**

create procedure 存储过程名(参数模式 参数名 参数类型)
    begin
    		存储过程体
    end

注意：  
1.参数模式：in、out、inout，其中in可以省略

*  in：该参数可以作为输入，也就是该参数需要调用方传入值
 *  out：该参数可以作为输出，也就是该参数可以作为返回值
 *  inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.存储过程体的每一条sql语句都需要用分号结尾  
3.如果存储过程体仅仅只有一句话，begin end可以省略  
4.存储过程的结尾可以使用 delimiter 重新设置

**二、调用**

call 存储过程名(实参列表)

举例：  
调用in模式的参数：call sp1（‘值’）;  
调用out模式的参数：set @name; call sp1(@name);select @name;  
调用inout模式的参数：set @name=值; call sp1(@name); select @name;  
**三、查看**

show create procedure 存储过程名;

SHOW CREATE PROCEDURE  myp2;

**四、删除**

drop procedure 存储过程名;

DROP PROCEDURE p1;

**五、举例**  
例：插入到admin表中五条记录

#空参情况
    DELIMITER $
    CREATE PROCEDURE myp1()
    BEGIN
    	INSERT INTO admin(username,`password`) 
    	VALUES('john1','0000'),
    		  ('lily','0000'),
    		  ('rose','0000'),
    		  ('jack','0000'),
    		  ('tom','0000');
    END $
    
    #调用
    CALL myp1()$

例：创建存储过程实现 根据女神名，查询对应的男神信息

#带in模式参数
    CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
    BEGIN
    	SELECT bo.*
    	FROM boys bo
    	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
    	WHERE b.name=beautyName;
    	
    
    END $
    
    #调用
    CALL myp2('柳岩')$

例：根据输入的女神名，返回对应的男神名

#带out模式参数
    CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
    BEGIN
    	SELECT bo.boyname INTO boyname
    	FROM boys bo
    	RIGHT JOIN
    	beauty b ON b.boyfriend_id = bo.id
    	WHERE b.name=beautyName ;
    	
    END $

例：传入a和b两个值，最终a和b都翻倍并返回

#带inout模式参数
    CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
    BEGIN
    	SET a=a*2;
    	SET b=b*2;
    END $
    
    #调用
    SET @m=10$
    SET @n=20$
    CALL myp8(@m,@n)$
    SELECT @m,@n$

## 函数（Function） ##

*  函数体中仅有一句话，则可以省略begin end
 *  使用 delimiter语句设置结束标记

**一、创建**

create function 函数名(参数名 参数类型) returns  返回类型
    begin
    	函数体
    end

注意：函数体中肯定需要有return语句  
**二、调用**

select 函数名(实参列表);

**三、查看**

show create function 函数名;

SHOW CREATE FUNCTION myf3;

**四、删除**

drop function 函数名；

DROP FUNCTION myf3;

**五、举例**  
1.无参有返回  
例：返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT
    BEGIN
    
    	DECLARE c INT DEFAULT 0;#定义局部变量
    	SELECT COUNT(*) INTO c#赋值
    	FROM employees;
    	RETURN c;
    	
    END $
    
    SELECT myf1()$

2.有参有返回  
例：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
    	SET @sal=0;#定义用户变量 
    	SELECT salary INTO @sal   #赋值
    	FROM employees
    	WHERE last_name = empName;
    	
    	RETURN @sal;
    END $
    
    SELECT myf2('k_ing') $

例：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
    	DECLARE sal DOUBLE ;
    	SELECT AVG(salary) INTO sal
    	FROM employees e
    	JOIN departments d ON e.department_id = d.department_id
    	WHERE d.department_name=deptName;
    	RETURN sal;
    END $
    
    SELECT myf3('IT')$

例：创建函数，实现传入两个float，返回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
    BEGIN
    	DECLARE SUM FLOAT DEFAULT 0;
    	SET SUM=num1+num2;
    	RETURN SUM;
    END $
    
    SELECT test_fun1(1,2)$

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

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>关键字</th> 
   <th>调用语法</th> 
   <th>返回值</th> 
   <th>应用场景</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>FUNCTION</td> 
   <td>SELECT 函数()</td> 
   <td>只能是一个</td> 
   <td>一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个</td> 
  </tr> 
  <tr> 
   <td>存储过程</td> 
   <td>PROCEDURE</td> 
   <td>CALL 存储过程()</td> 
   <td>可以有0个或多个</td> 
   <td>一般用于更新</td> 
  </tr> 
 </tbody> 
</table>