存储过程

最新推荐文章于 2024-04-08 19:16:39 发布

LiShiBaoGod

最新推荐文章于 2024-04-08 19:16:39 发布

阅读量287

点赞数

分类专栏： mysql 文章标签：存储过程

本文链接：https://blog.csdn.net/weixin_44126769/article/details/90703754

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1，什么是存储过程
类似于shell脚本，把多条sql语句放在一起执行，实现特定的功能
2，存储过程的优点
（1）增强语言的功能和灵活性：存储过程可以用控制语句编写（例如循环控制语句），有很强的灵活性，可以完成复杂的判断和较复杂的运算。
（2）标准组件式编程：应用程序调用的是存储过程的名字，对存储过程的中sql语句进行更改，不影响应用程序对存储过程的调用
(3).较快的执行速度：存储在数据库中的存储过程，经过第一次编译后再次调用不需要再次编译
（4）减少网络流量：当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
（5）增强安全性：
1）网络中传送的是调用语句，存储过程中的sql语句不在网络中传输，增加了安全性；
2）可以对执行某一存储过程的权限进行限制，避免了非授权用户对数据的访问，保证了安全性
3，有时为什么不使用存储过程
（1）可移植性差，a服务器的存储不一定适合b服务器，所以a的存储过程不一定能在b服务器上使用
（2）对于简单的SQL语句，存储过程没什么优势，比如一条sql语句，没必要做一个存储过程
（3）如果存储过程中不一定会减少网络传输，就不需要用
（4）如果只有一个用户使用数据库，那么存储过程对安全也没什么影响。
如果有多个用户的话，创建存储过程需要考虑机密数据不允许访问，
如果有只有一个用户的话，创建存储过程不需要考虑机密数据的问题了。
（5）在大并发量访问的情况下，不宜写过多涉及运算的存储过程，因为运算消耗资源（内存，cpu）
（6）业务逻辑复杂时，特别是涉及到对很大的表进行操作的时候，不如在前端先简化业务逻辑
（7）团队开发时需要先统一标准，否则后期维护成本大（事先商量好要不要存储过程）
4，定义存储过程
语法：

 create procedure 过程名（参数1，参数2，...）
		 begin
		 sql语句；
		 end

注意：创建存储过程之前我们必须修改mysql语句默认结束符 ; 要不然我们不能创建成功
使用delimiter可以修改执行符号
DELIMITER是定界符/分割符的意思，因为MySQL默认以";“为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

delimiter语法：
   delimiter 新分隔符  可以是%或//
   修改分隔符
    mysql>use book;
 mysql> delimiter %  这样结束符就为%
 创建一个简单的存储过程
 mysql> create procedure selCg()
     -> begin
     -> select * from category;
     -> end %
 mysql> delimiter ;   #写完存储过程，恢复分隔符

5，调用存储过程

 语法：
 call 过程名（参数1，参数2）;
 mysql> call selCg() ;

在这里插入图片描述
6，存储过程参数类型
6.1， In参数传入参数
特点：读取外部变量值，且有效范围仅限存储过程内部
6.1.1、创建一个简单的存储过程,传入in参数

mysql> delimiter //
mysql> create procedure xx(in abc int)
    -> begin
    -> select abc;
    -> set abc=2;
    ->  select abc;
    ->  end //
mysql> delimiter ;  #使用完马上恢复默认的分隔符
直接出入参数
mysql> call xx(6);

6.2、 mysql定义变量方法

语法格式：  set  @字段名=值
mysql> set @num=1；
把变量传到存储过程
mysql> call xx(@num);

6.2.1、定义一个存储过程 getOneBook，当输入某书籍 id 后，可以调出对应书籍记录

 mysql>delimiter //
 mysql> create procedure getOneBook(in b int)  # b是参数名
 -> begin
 -> select  bid,bname,price from books where bId=b;
 -> end //
 mysql> delimiter ;    #恢复默认分隔符
 mysql> call getOneBook(3);  #调用存储过程，输入参数id=3

6.3、 Out参数传出参数
特点：不读取外部变量值，在存储过程执行完毕后保留新值

 mysql> delimiter //
 mysql> create procedure pout(out p_out int)
     -> begin
     -> select p_out;
     -> set p_out=2;
     -> select p_out;
     -> end //
 mysql> delimiter ;
 mysql> set @p_out=1;   #设置变量值
 mysql> call pout(@p_out);  #调用存储过程，传入变量参数
注意：不能传入数值
  
 mysql> select @p_out;   #我们@p_out变量的值变成2了

在这里插入图片描述
In传入参数,是外部将值传给存储过程来使用的,而out传出参数是为了让存储过程的执行结果回传给调用他的程序来使用的.

在book表里使用out参数

mysql> use book;
mysql> delimiter //
mysql> create procedure demo(out pa varchar(200))  #创建demo存储过程



>begin 
>select bname into @pa from books where bid=3; #查询id为3的图书保存到@pa
>select @pa as '图书名';                     #查看@pa
 >end //
mysql> delimiter ;      #修改回默认分隔符
mysql> call demo(@a);  #调用存储过程
mysql> select @a;      #查看变量@a的值，发现是空的
 +------+
 | @a   |
 +------+
 | NULL |
 +------+
 1 row in set (0.00 sec)
mysql> select @pa;    #查看@pa的值，发现它把值保存到我们开始定义的@pa变量里了

6.4、 Inout参数
特点：读取外部变量，在存储过程执行完后保留新值<类似银行存款>
传进来，又传出去。

mysql> delimiter //
mysql> create procedure pinout(inout p_inout int)
    -> begin
    -> select p_inout;
    -> set p_inout=2;
    -> select p_inout;
    -> end //

mysql> delimiter ;
mysql> set @p_inout=1;     #设置变量值
mysql> call pinout(@p_inout);   #调用存储过程，传入变量参数

注意：不能传入数值
在这里插入图片描述

查看@p_inout变量
mysql> select @p_inout; #发现变量的值已经变成2，读取写入成功。
在这里插入图片描述

6.5、不加参数的情况
如果在创建存储过程时没有指定参数类型(in,out或者inout)，则需要在调用的时候指定参数值
创建一个循环存储过程，把循环的值插入到t2表中。

mysql> create table t2(id int(11)); 创建表
mysql> delimiter //
mysql> create procedure t2(n1 int)
    -> begin
    -> set @x=0;
    -> repeat set @x=@x+1;  #repeat开始循环，相当于shell中的while循环
    -> insert into t2 values(@x);
    -> until @x>n1
    -> end repeat;       #结束循环
    -> end//
mysql> delimiter ;
mysql> call t2(5); 循环5次
查看t2表中的数据
mysql> select * from t2;

6.6、 declare变量，在存储过程内定义
MySQL中使用declare进行变量定义
变量定义语句：DECLARE variable_name [,variable_name…] datatype [DEFAULT value];
datatype为MySQL的数据类型，如:int, float, date, varchar(length)
变量赋值语句： SET 变量名 = 表达式值 [,variable_name = expression …]
变量赋值可以在不同的存储过程中继承

mysql> delimiter //
mysql> create procedure decl()
    -> begin
    -> declare name varchar(200);      #定义变量
    -> set name=(select bName from books where bId=12); #变量赋值
    -> select name;     #查看变量
    -> end //
mysql> delimiter ;
mysql> call decl;      #调用变量

7、注释
做过开发的都知道，写注释是个利人利己的事情。便于理解维护
MySQL注释有两种风格
“–“：单行注释
“/……/”:一般用于多行注释

8、存储过程流程控制语句
8.1、变量作用域：
内部的变量在其作用域范围内享有更高的优先权，当执行到end变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，因为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。
8.2、两层嵌套
就是一个存储过程里面包含了一个存储过程。

mysql> delimiter //
mysql> create procedure proc3()
    -> begin
    -> declare x1 varchar(5) default 'outer';
    -> begin
    -> declare x1 varchar(5) default 'inner';
    -> select x1;
    -> end;
    -> select x1;
    -> end //
mysql> delimiter ；

8.3、条件语句
8.3.1、 if-then-else语句

mysql> delimiter //
mysql> create procedure proc2(in parameter int)
    -> begin
    -> declare var int;
    -> set var=parameter+1;
    -> if var=0 then
    -> insert into t values(17);
    -> end if;
    -> if patameter=0 then
    -> update t set s1=s1+1;
    -> else
    -> update t set s1=s1+2;
    -> end if;
    -> end //
mysql> delimiter ;

8.3.2、 case语句

mysql> delimiter //
mysql> create procedure proc3(in parameter int)
    -> begin
    -> declare var int;
    -> set var=parameter+1;
    -> case var
    -> when 0 then
    -> insert into t values(17);
    -> when 1 then
    -> insert into t values(18);
    -> else
    -> insert into t values(19);
    -> end case;
    -> end //
mysql> delimiter ;

8.4 循环语句
8.4.1、 while—end while

mysql> delimiter //
mysql> create procedure proc4()
    -> begin
    -> declare var int;
    -> set var=0;
    -> while var<6 do
    -> insert into t values(var);
    -> set var=var+1;
    -> end while;
    -> end //
mysql> delimiter ;

while循环中可以嵌套if流程控制语句，语法：

 while 1 do ....... if **** then break; end while;

8.4.2、 repeat···· end repeat

mysql> delimiter //
mysql> create procedure proc5()
    -> begin
    -> declare v int;
    -> set v=0;
    -> repeat
    -> insert into t values(v);
    -> set v=v+1;
    -> until v>=5
    -> end repeat;
    -> end //

8.4.3、 loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

mysql> delimiter //
mysql> create procedure proc6()
    -> begin
    -> declare v int;
    -> set v=0;
    -> loop_lable:loop
    -> insert into t values(v);
    -> set v=v+1;
    -> if v>=5 then
    -> leave loop_lable;
    -> end if;
    -> end loop
    -> end //
mysql> delimiter ;

9.查看存储过程
9.1 内容：

mysql> show create procedure demo \G

在这里插入图片描述
9.2 状态：

mysql> show procedure status \G  查看所有存储过程

10. 修改存储过程：
使用alter语句修改(一般不建议这样改)
语法：
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic …]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
注释
sp_name参数表示存储过程或函数的名称
characteristic参数指定存储函数的特性
CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；
NO SQL表示子程序中不包含SQL语句
READS SQL DATA表示子程序中包含读数据的语句
MODIFIES SQL DATA表示子程序中包含写数据的语句
SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行
DEFINER表示只有定义者自己才能够执行
INVOKER表示调用者可以执行
COMMENT 'string’是注释信息。

/**/
上面命令的意思就是说只能改名字和定义，不能改里面的内容。要删了重新建
可以通过第三方工具修改
11. 删除存储过程
语法：
方法一：DROP PROCEDURE 存储过程名称

mysql> drop procedure p_inout;

方法二：DROP PROCEDURE IF EXISTS存储过程名称
这个两个语句是用来删除一个存储程序。注意不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

LiShiBaoGod

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录