认识MySQL存储过程

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

特点

能完成较复杂的判断和运算
可编程行强，灵活
SQL编程的代码可重复使用
执行的速度相对快一些
减少网络之间的数据传输，节省开销

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

存储过程的创建和调用

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。

MySQL存储过程 DELIMITER的作用

声明语句结束符,可以自己定义

改变执行语句的分号，可以改成 DELIMITER // 或者 DELIMITER $$ ,mysql的默认语句结束符是 ;

MySQL存储过程,DELIMITER的用法

delimiter $$ //声明$$为语句结束符  mysql的默认语句结束符是 ; 分号，修改结束符的符号,可以在存储过程中写多条语句

create procedure test()
begin 
select * from test;
select * from salary;
end $$ //使用$$代表的意思是这个存储过程声明完毕,然后使用了一开始声明的结束符

delimiter ; //然后又把声明语句结束符改回了分号,这样存储过程的两条语句都会执行

注意:如果不重新声明语句结束符,在mysql的命令行中敲完一行(select * from test;) 回车后这个命令会直接查询出test表的内容,如果你写完全部再敲命令也只会查询出最后一个 ; 的执行结果 salary表的信息,而不会继续创建存储过程,所以需要重新声明语句结束符例如上面例子的 $$ 这样在敲完(select * from test;)的分号后,就不会直接查询出内容。最后在将语句结束符重新定义为 ;

创建存储过程

创建存储过程举例说明

DELIMITER $$
CREATE PROCEDURE insert_student(IN id INT,IN name VARCHAR(20),IN sex VARCHAR(3))
BEGIN
INSERT INTO xs VALUES(id,name,sex);
END $$
DELIMITER;

调用存储过程

CALL insert_student(1,"陈","男");
CALL insert_student(2,"撒","女");
//调用完成后向student表插入 了两条数据

存储过程的变量

先通过一个简单的例子来学习变量的声明和赋值

delimiter && //声明语句结束符
create procedure test2()
begin
  declare username varchar(32) default '';	//使用 declare语句声明一个变量
  set username='xiaoxiao';	//使用set语句给变量赋值
  select name into username from employees where employeeid=0001;//将employees表中id=0001的姓名赋值给username
  select username;	//返回变量
end && //结束
delimiter ; //修改成默认的

概括

变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用；
变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等；
变量可以通过set来赋值，也可以通过select into的方式赋值；
变量需要返回，可以使用select语句，如：select 变量名。

变量的作用域

存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。
需要多个块之间传值，可以使用全局变量，即放在所有代码块之前
传参变量是全局的，可以在多个块之间起作用

存储过程的参数

基本语法

delimiter &&
create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end &&
delimiter ;

存储过程的参数类型有：IN,OUT,INOUT，

存储过程的传出参数IN

delimiter &&
create procedure test3(userId int)  //userId int 就是传入参数的名称和类型
begin
	declare username varchar(32) default '';
	declare ordercount int default 0;
	select name into username from users where id=userId;
	select username;
end &&
delimiter ;

传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。
IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
如果调用存储过程中需要修改和返回值，可以使用OUT类型参数

存储过程的传出参数OUT

delimiter &&
create procedure test4(in userId int,out username varchar(32))
begin
	select name into username from users where id=userId;
end &&
delimiter ;

传出参数：在调用存储过程中，可以改变其值，并可返回;
out是传出参数，不能用于传入参数值;
调用存储过程时，out参数也需要指定，但必须是变量，不能是常量;
如果既需要传入，同时又需要传出，则可以使用INOUT类型参数;

存储过程的可变参数INOUT

delimiter &&
create procedure test5(inout userId int,inout username varchar(32))
begin
    set userId=2;
    set username='';
    select id,name into userId,username from users where id=userId;
end &&
delimiter ;

可变变量INOUT:调用时可传入值，在调用过程中，可修改其值，同时也可返回值;
INOUT参数集合了IN和OUT类型的参数功能;
INOUT调用时传入的是变量，而不是常量;

存储过程条件语句

条件语句基本结构

if() 
then...
else...
end if;

多条件语句基本结构

if() then...
elseif() then...
else ...
end if;

条件语句实例练习

delimiter &&
create procedure test6(in userId int)
begin
   declare username varchar(32) default '';
   if(userId%2=0)
   then 
      select name into username from users where id=userId;
      select username;
   else
      select userId;
      end if;
end &&
delimiter ;

多条件语句实例练习

根据用户传入的uid参数判断
如果用户状态status为1，则给用户score加10分；
如果用户状态status为2，则给用户score加20分；
其他情况加30分

delimiter &&
create procedure test7(in userid int)
begin
   declare my_status int default 0;
   select status into my_status from users where id=userid;
   if(my_status=1)
   then 
       update users set score=score+10 where id=userid;
    elseif(my_status=2)
    then 
       update users set score=score+20 where id=userid;
    else 
       update users set score=score+30 where id=userid;
    end if;
end &&
delimiter ;

存储过程循环语句

while语句的基本结构

while(表达式) do 
   ......  
end while;

while语句练习实例

使用循环语句，向表test1(id)中插入10条连续的记录

delimiter &&
create procedure test8()
begin
  declare i int default 0;
  while(i<10) do 
    begin 
        select i;
        set i=i+1;
        insert into test1(id) values(i);
     end;
  end while;
end &&
delimiter ;

存储过程游标的使用

1、什么是游标
游标是保存查询结果的临时区域
2、实例
编写存储过程，使用游标，把users表中 id为偶数的记录逐一更新用户名

delimiter &&
create procedure test9()
begin
        declare stopflag int default 0;
        declare username VARCHAR(32);
        //创建一个游标变量，declare 变量名 cursor ...
        declare username_cur cursor for select name from users where id%2=0;
        //游标是保存查询结果的临时区域
        //游标变量username_cur保存了查询的临时结果，实际上就是结果集
        //当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束
        declare continue handler for not found set stopflag=1;
 
    open username_cur; -- 打开游标
        fetch username_cur into username; //游标向前走一步，取出一条记录放到变量username中
        while(stopflag=0) do //如果游标还没有结尾，就继续
            begin 
               update users set name=CONCAT(username,'_cur') where name=username;//在用户名前拼接'_cur'字符串
               fetch username_cur into username;
            end;
        end while; //结束循环
    close username_cur;	//关闭游标
end &&
delimiter ;

自定义函数(存储函数与存储过程的比较)

引言

存储过程和存储函数类似于面向对象程序设计语言中的方法，可以简化代码，提高代码的重用性。本文主要介绍如何创建存储过程和存储函数，以及存储过程与函数的使用、修改、删除等操作。
函数与存储过程最大的区别是函数必须有返回值，否则会报错

存储过程与存储函数

MySQL中提供存储过程与存储函数机制，我们姑且将存储过程和存储函数合称为存储程序。与一般的SQL语句需要先编译然后立即执行不同，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

　　存储程序就是一条或者多条SQL语句和控制语句的集合，我们可以将其看作MySQL的批处理文件，当然，其作用不仅限于批处理。当想要在不同的应用程序或平台上执行相同的功能一段程序或者封装特定功能时，存储程序是非常有用的。数据库中的存储程序可以看做是面向对编程中面向对象方法，它允许控制数据的访问方式。

存储函数与存储过程有如下区别：

存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out,inout)。
调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。
参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，IN、out和INOUT：

　　　　a. in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

　　　　b. out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

　　　　c. inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用传递,只能传递变量。

//一个简单存储函数
delimiter &&
create function getusername(userid int) returns varchar(32)
    reads sql data  -- 从数据库中读取数据，但不修改数据
    begin
        declare username varchar(32) default '';
        select name into username from users where id=userid;
        return username;
    end;
end &&
delimiter ;

创建函数使用create function 函数名(参数) returns 返回类型；
函数体放在begin和end之间；
returns指定函数的返回值；
函数调用使用select getusername()。

文章作者: Todcsw

文章链接: https://todcsw.github.io/2020/04/07/mysql-mysql-StoredProcedure/

学习 mysql

打赏

微信
支付宝