MySQL高级语句-存储过程

一只余
82 阅读4分钟

一 . 存储过程

存储过程是一组为了完成特定功能的SQL语句集合。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

1.存储过程的优点：

  • 1、执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
  • 2、SQL语句加上控制语句的集合，灵活性高
  • 3、在服务器端存储，客户端调用时，降低网络负载
  • 4、可多次重复被调用，可随时修改，不影响客户端调用
  • 5、可完成所有的数据库操作，也可控制数据库的信息访问权限

2 . 创建存储过程步骤

DELIMITER $$	            #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()      #创建存储过程，过程名为Proc，不带参数  
-> BEGIN		            #过程体以关键字 BEGIN 开始
-> select * from Store_Info;  #过程体语句SQL语句
-> END $$			   #过程体以关键字 END 结束
DELIMITER ;			 #将语句的结束符号恢复为分号

delimiter $$
use yhy  #运行库
mysql> create procedure proc1()
    -> begin
    -> create table ky32 (id int,name varchar(10),age int,sex char(2),primary key (id));
    -> insert into ky32 values (1,'cyw',23,'男');
    -> insert into ky32 values (2,'ylc',24,'男');
    -> insert into ky32 values (3,'lzq',50,'男');
    -> insert into ky32 values (4,'yhy',18,'女');
    -> select * from ky32;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

image.png

查看

 格式
 SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息
 show procedure
 例子 show create procedure  proc1\G 查看创建的SQL语句内容

image.png

 show tables;
查看是否有创建的表是否存在
 ##调用存储过程##
 CALL Proc;

image.png

3. 删除存储过程

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

DROP PROCEDURE IF EXISTS Proc（存储过程名）;	
#仅当存在时删除，不添加 IF EXISTS 时，如果指定的过程不存在，则产生一个错误

二 . 存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是自变量或变量） OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量） INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1.IN 输入参数

模版

DELIMITER $$				
CREATE PROCEDURE Proc1(IN inname CHAR(16)) 		
-> BEGIN					
-> SELECT * FROM Store_Info WHERE Store_Name = inname;
-> END $$					
DELIMITER ;					

CALL Proc1('Boston');

实验操作

mysql> delimiter $$
mysql> use yhy
Database changed
mysql> create procedure proc2(in input_name varchar(10))
    -> begin
    -> select * from ky32 where name = input_name;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure proc2\G$$
  #查看存储过程

mysql> delimiter ;
mysql> call proc('yhy');

此实验相当于我调用了yhy这个名字传输给input_name varchar(10)这个变量， 在select语句中可以看出input_name这个语句作为条件去使用。

image.png

image.png

2. OUT 输出参数

模版

delimiter $$
mysql> create procedure proc3(in myname char(10), out outname int)
    -> begin
    -> select sales into outname from t1 where name = myname;
    -> end $$
delimiter ;
call proc3('yzh', @out_sales);
select @out_sales;

操作

select *from ky32;
delimiter $$
mysql> create procedure proc5(in input_name varchar(10), out output_sex t_age int)
    -> begin
    -> select sex into output_sex from ky32 where name=input_name;
    -> select age into output_age from ky32 where name=input_name;
    -> end$$
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter ;
mysql> call proc5('ylc',@out_sex,@out_age);
Query OK, 1 row affected (0.00 sec)

mysql> select @out_sex;

image.png

继续调用输出过程

 call proc5('yhy',@out_sex,@out_age);
 select @out_sex,@out_age;

image.png

3. INOUT 输入输出参数

  • 输出和输入的数据类型要一样

  • inout 既做输入也做输出，输入相当于把输入的值赋给变量；输出into指定一个变量，也就是说输入用这个变量 输出还用这个变量，先用输入得值给其赋值，并且将执行的结果输出到输入输出的这个变量中

案例1

set @inout_var = 2;
select @inout_var;

delimiter $$
mysql> create procedure proc6(inout myvar int)
    -> begin
    -> select age into myvar from ky32 where id =myvar;
    -> end$$

delimiter ;
call proc6(@inout_var);
select @inout_var;

image.png

image.png

set @inout_var = 4;
select @inout_var;
call proc6(@inout_var);
select @inout_var;

image.png

案例2：输出名字查看男女

原表数据

image.png

mysql> delimiter $$
mysql> create procedure proc7(inout my_var varchar(10))
    -> begin
    -> select sex into my_var from ky32 where name = my_var;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

select意义： 根据"name"列的值与变量匹配，将匹配到的记录中的"sex"值赋给变量

image.png

set @inout_var = 'cyw';
select @inout_var;
call proc7(@inout_var);
调用存储过程
select @inout_var;
查看sex"值

image.png

IN OUT配合输出不同的数据类型

三 . 存储过程控制语句

（1）条件语句if-then-else ···· end if

模版

DELIMITER $$  
CREATE PROCEDURE proc2(IN pro int)  
-> begin 
-> declare var int;  
-> set var=pro*2;   
-> if var>=10 then 
-> update t set id=id+1;  
-> else 
-> update t set id=id-1;  
-> end if;  
-> end $$

案例

原图片

image.png

要求添加一行爱好进去

alter table ky2 add hobby varchar()

image.png

实现结果：判断大于20岁的人喜欢钱，小于20岁的人喜欢玩；

 mysql> delimiter $$
mysql> create procedure proc8(in input_age int)
    -> begin
    -> if input_age >20 then
    -> update ky32 set hobby = 'money' where age > 20;
    -> else
    -> update ky32 set hobby = 'play' where age <= 20;
    -> end if;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

image.png

mysql> show create procedure proc8;

image.png

调用结果

image.png

image.png

案例2

创建一个比赛组表，男生女生自动分配男女队伍。

create table football_match (name varchar(10),sex char(1),fb_group char(3));

insert into football_match values ('ylc','男','男子组');

select * from football_match;

image.png

image.png

mysql> delimiter $$
mysql> create procedure proc9(in input_name varchar(10),in input_sex char(1))
    -> begin
    -> if input_sex = '男' then
    -> insert into football_match values (input_name, input_sex,'男子组');
    -> else
    -> insert into football_match values (input_name, input_sex,'女子组');
    -> end if;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

image.png

调用结果

call proc9('fsj','男')；
select * from football_match;
call proc9('yhy','女')
select * from football_match;

image.png

（2）循环语句while ···· end while

模版

DELIMITER $$  
CREATE PROCEDURE proc3()
-> begin 
-> declare var int(10);  
-> set var=0;  
-> while var<6 do  
-> insert into t values(var);  
-> set var=var+1;  
-> end while;  
-> end $$

案例

使用while循环语句自增长10行

image.png

mysql> delimiter $$

mysql> create procedure proc10()
    -> begin
    -> declare i int;
    -> set i=1;
    -> while i <= 10
    -> do
    -> insert into t1 values (i);
    -> set i = i+1;
    -> end while;
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

image.png

调用存储过程

mysql> call proc10;
mysql> select * from t1;

image.png

案例2：

一次性添加10000行数据，并且数据中有两个字段，一个为学生id，一个学生名字为student 达到下图效果

idname
1student1
2student2
nstudentn
delimiter $$
create procedure proc11() 
begin
declare i int;
set i = 1;
create table t2 (id int,name varchar(20));
while i <= 10000 
do 
insert into t2 values (i,concat('student',i));
set i = i+1;
end while;
end$$

mysql> delimiter ;
mysql> call proc11;

mysql> select count(*) from  t2;
#查看总行数

image.png

avatar
文章
阅读
粉丝