优秀的编程知识分享平台

网站首页 > 技术文章 正文

一文学会MYSQL数据库的存储过程和函数

nanyue 2024-07-29 01:07:39 技术文章 4 ℃

什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

ps:存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

存储过程特性

  1. 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
  2. 函数的普遍特性：模块化，封装，代码复用；
  3. 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

MySQL存储过程的创建

1、语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

DELIMITER //
 CREATE PROCEDURE myproc(OUT s int)
 BEGIN
 SELECT COUNT(*) INTO s FROM students;
 END
 //
DELIMITER ;

MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

2、参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

  • IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
  • OUT:该值可在存储过程内部被改变，并可返回
  • INOUT:调用时指定，并且可被改变和返回

其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

技巧：创建存储过程时，系统默认指定CONTAINS SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为NO SQL。而且，存储过程中最好在COMMENT部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。

存储过程实例

下面创建一个名为num_from_employee的存储过程。

上述存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录，并用COUNT(*)计算d_id值相同的记录的条数，最后将计算结果存入count_num中。执行结果如下：

执行完毕后，没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程，数据库中会执行存储过程中的SQL语句。

说明：MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来 结束。为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。

函数

在MySQL中，创建存储函数的基本形式如下：

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body

其中，sp_name参数是存储函数的名称；func_parameter表示存储函数的参数列表；RETURNS type指定返回值的类型；characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：param_name type

其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是MySQL数据库的任意数据类型。

函数实例

下面创建一个名为name_from_employee的存储函数。

上述存储函数的名称为name_from_employee；该函数的参数为emp_id；返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录，并将该记录的name字段的值返回。执行结果如下：

结果显示，存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。

变量的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。

1、定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

DECLARE var_name[,...] type [DEFAULT value]

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

下面定义变量my_sql，数据类型为INT型，默认值为10。

DECLARE my_sql INT DEFAULT 10 ;

2、为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

SET var_name = expr [, var_name = expr] ...

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

下面为变量my_sql赋值为30。

SET my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

SELECT col_name[,…] INTO var_name[,…] FROM table_name WEHRE condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。

SELECT d_id INTO my_sql FROM employee WEHRE id=2 ;

MySQL存储过程写法总结

1、创建无参存储过程。

create procedure product()
begin
 select * from user;
end;

一条简单的存储过程创建语句，此时调用的语句为：

call procedure();

2、创建有参存储过程

有参的存储包括两种参数，

一个是传入参数;

一个是传出参数;

例如一个存储过程：

create procedure procedure2(
out p1 decimal(8,2),
out p2 decimal(8,2),
in p3 int
)
begin
select sum(uid) into p1 from user where order_name = p3;
select avg(uid) into p2 from user ;
end ;

从上面sql语句可以看出，p1和p2是用来检索并且传出去的值，而p3则是必须有调用这传入的具体值。

具体调用过程：

call product(); //无参

call procedure2(@userSum,@userAvg,201708); //有参

当用完后，可以直接查询userSum和userAvg的值：

select @userSum, @userAvg;

结果如下：

3、删除存储过程

语法：drop procedure procedure_name;

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~

Tags：

猜你喜欢

最近发表
标签列表