MySQL数据库-存储过程和函数-创建存储过程

创建存储过程

存储过程和函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户器端和服务端的数据传输。

创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下：

CREATE PROCEDURE procedure_name([proc_param[,…]])

routine_body

在上述语句中，参数procedure_name表示所要创建的存储过程名字，参数proc_param表示存储过程的参数，参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。

提示：在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。

proc_param中每个参数的语法形式如下：

[IN|OUT|INOUT] param_name type

在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。

例如：我们可以创建一个存储过程让表中的数据指定删除。

代码示例：

mysql> create table student(id int,name varchar(64));    #创建学生表
mysql> insert into student values(1,'Mr.Wang'),(2,'Mr.Li'),(3,'Mr.Shi'),(4,'Mr.Zhang');    #插入多条数据
mysql> select*from student;    #查看数据
mysql> delimiter $    #替换结束符为'$'
mysql> create procedure proc_delete_stu(IN sid int)
    -> begin
    -> delete from student where id=sid;
    -> end;
    -> $    #创建存储过程实现sid所对应班级字段id相同的数据
mysql> delimiter ;    #替换结束符为';'
mysql> call proc_delete_stu(1);    调用存储过程
mysql> select*from student;    #与上面作对比

 我们创建好学生表后并添加多条数据后，下面我们创建存储过程。

 在创建存储过程之前我们应该替换结束符为'$'后再创建存储过程按sid删除student表中对应的数据，最终再替换结束符';'，用call调用刚刚创建好的存储过程。

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

定义变量

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

DECLARE var_name[,…] type [DEFAULT value]

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

例如我们要定义一个整形变量，默认值为1代码如下：

declare id int default 10;

为变量赋值

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

SET var_name=expr[,var_name=expr]…

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

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

SELECT col_name[,…] INTO var_name[,…]

FROM table_name WHERE condition

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

例如：我们创建存储过程可以通过id实现查找出所对应的个人信息。

代码示例：

mysql> delimiter $    #替换结束符为'$'
mysql> create procedure query_stu_info(in sid int,out cname varchar(64),out cid int)
    -> begin
    -> declare temp_name varchar(64);    #定义变量用来保持字段name
    -> declare temp_int int;     #定义变量用来保持字段id
    -> select id into temp_int from student where id=sid;
    -> select name into temp_name from student where id=sid;
    -> set cname=temp_name,cid=temp_int;    #赋值给函数的参数
    -> end;
    -> $
mysql> delimiter ;    #替换结束符为';'
mysql> call query_stu_info(2,@name,@cid);    #调用存储过程并传参
mysql> select @cid,@name;    #显示这两个参数

 同样还是先替换结束符为'$'，然后再创建存储过程其实就像函数一样参数是3个第一个参数sid是你要查询的id后面两个是出去的cname保存查出来的姓名，cid保存id序号，接着在函数体里面定义两个变量同样分别保存sid在student表中所对应的id和name字段的值，用select语句查出后赋值给刚定义的变量，再赋值为函数的参数，最后再改变替换符为';'，再用call调用函数并传参。