存储过程与存储函数

最新推荐文章于 2025-04-25 09:53:28 发布

2401_88481074

最新推荐文章于 2025-04-25 09:53:28 发布

阅读量1.9k

点赞数 79

分类专栏： mysql 文章标签：大数据

本文链接：https://blog.youkuaiyun.com/2401_88481074/article/details/145109474

版权

mysql 专栏收录该内容

14 篇文章

订阅专栏

存储过程的创建于调用

理解

存储过程（ Stored Procedure ）一组经过预先编译的 SQL 语句的封装。

和视图、函数的对比：

存储过程：可以没有返回值

存储函数：一定有返回值

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1、没有参数（无参数无返回）

2、仅仅带 IN 类型（有参数无返回）

3、仅仅带 OUT 类型（无参数有返回）

4、既带 IN 又带 OUT（有参数有返回）

5、带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

创建存储过程

语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名参数类型,...)

[characteristics ...]

BEGIN

存储过程体

END

格式：

DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名参数类型,...)

[characteristics ...]

BEGIN

sql语句1;

sql语句2;

END $

DELIMITER ;

DELIMITER表示定义一个结束符，注意不要定义/为结束符，它是转义字符

类型一：无参数无返回值

#举例1：创建存储过程select_all_data()，查看 employees 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()

BEGIN

        SELECT * FROM employees;

END $

DELIMITER ;       

#存储过程的调用

CALL select_all_data();

类型二：带out类型

#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms“输出

DELIMITER $

CREATE PROCEDURE show_min_salary02(OUT ms DOUBLE)

BEGIN

      SELECT MIN(salary) INTO ms

      FROM employees;

END $

DELIMITER ;  

#调用

CALL show_min_salary02(@ms);  

#查看变量值

SELECT @ms;

类型三：带in类型

#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname

#输入员工姓名。

DELIMITER $

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(25))

BEGIN  

      SELECT salary

      FROM employees

      WHERE last_name=empname;

END $

#调用方式1：

CALL show_someone_salary('Abel');

#调用方式2：

SET @empname:='Abel';

CALL show_someone_salary(@empname);

类型四：带in和out

#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname

#输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER $

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(25),OUT empsalary DOUBLE)     

BEGIN

       SELECT salary INTO empsalary

       FROM employees

       WHERE last_name=empname;

END $

DELIMITER ;      

#调用方式1：

CALL show_someone_salary2('Abel',@empsalary);

SELECT @empsalary;

#调用方式2：

SET @empname='Abel';

CALL show_someone_salary2(@empname,@empsalary);

SELECT @empsalary;

类型五：带INOUT

#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员

#工姓名，输出领导的姓名。

DELIMITER $

CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))

BEGIN        

       SELECT last_name INTO empname

       FROM employees

       WHERE employee_id=(

                          SELECT manager_id

                          FROM employees

                          WHERE last_name=empname);

END $                         

DELIMITER ;

#调用方式1

SET @empname:='Abel';

CALL show_mgr_name(@empname);

SELECT @empname;

调用格式

1、调用in模式的参数：

CALL sp1('值');

2、调用out模式的参数：

SET @name;

CALL sp1(@name);

SELECT @name;

3、调用inout模式的参数：

SET @name=值;

CALL sp1(@name);

SELECT @name;

存储函数的创建于调用

语法格式：

Delimiter $

CREATE FUNCTION 函数名(参数名参数类型,...)

RETURNS 返回值类型

[characteristics ...]

BEGIN

函数体 #函数体中肯定有 RETURN 语句

END

Delimiter $

说明：

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。

2、RETURNS type 语句表示函数返回数据的类型；

RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。

3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同

4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

注意：

若在创建存储函数中报错“ you might want to use the less safe

log_bin_trust_function_creators variable ”，有两种处理方法：

方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA |

MODIFIES SQL DATA}”

方式2：

SET GLOBAL log_bin_trust_function_creators = 1;---------创建函数前执行此语句，保证函数的创建会成功

调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

格式：

SELECT 函数名(实参列表)

代码举例：

#创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型

#为字符串型。

DELIMITER $

CREATE FUNCTION email_by_id(emp_id INT)

RETURNS VARCHAR(50)

       DETERMINISTIC

       CONTAINS SQL

       READS SQL DATA

BEGIN

       RETURN(SELECT email

       FROM employees

       WHERE employee_id=emp_id);

END $

DELIMITER ;

#调用方式1

SELECT email_by_id(102);              

#调用方式2：

SET @emp_id=101;

SELECT email_by_id(@emp_id);

对比存储函数和存储过程

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时