MySQL 存储过程

 1、存储过程

• 什么是存储过程
  • 存储过程是完成特定功能的 sql 语句集合。通过编译后存储在数据库中，通过指定的存储过程名称调用执行它
  • 存储过程 = sql 语句集合 + 控制语句
• 使用存储过程的优点
  • 存储过程创建可以多次调用，不需要重新编写存储过程语句
  • 存储过程支持接收参数，返回输出值　　
  • 存储过程加快程序的运行速度　　
  • 存储过程增加sql语句的功能和灵活性　　
•  创建存储过程
  • 格式如下　　

# 创建存储过程
delimiter // 
create procedure 存储过程名称（[in|out|inout]参数名，数据类型）
begin 
    存储过程体 
end 
//
delimiter ；

call 存储过程名称(参数)    # 调用存储过程

• • delimiter：分隔符，分界符，这里指定的分隔符是 //，可自定义　　　　
  • 创建一个存储过程
    • create procedure 存储过程名称()　
  • begin...end 代表存储过程体的开始和结束
  • 删除一个存储过程
    • drop procedure 存储过程名称　　
  • drop procedure if exists 存储过程名称 　　# 加强代码的健壮性　　
  • 调用一个存储过程　　
    • call 存储过程名称（）　
  • in 参数值在调用时必须指定　　
  • out 参数可以在调用后被返回　　
  • inout 参数调用时指定，并且可以被返回　

# in 输入参数，b 后面括号中接收参数
delimiter //
drop procedure if exists b;

create procedure b(in n int)
begin
    select n;
    set n = 2;
    select n;
end
//
delimiter ;

set @n = 1;
call b(@n);        # 调用存储过程 b

+------+
|   n  |
+------+
|   1  |
+------+

+------+
|   n  |
+------+
|   2  |
+------+

# out 输出参数
# out 是向调用者输出参数，不接收输入的参数
delimiter //
drop procedure if exists b;

create procedure b(out n int)
begin
    select n;
    set n = 2;
    select n;
end
//
delimiter ;

set @n = 1;
call b(@n);        # 调用存储过程 b

+-------+
|   n   |
+-------+
|  NULL |    # 因为 out 是向调用者输出参数，不接收输入的参数，所以存储过程里的 n 为 null
+-------+  
 
+-------+
|   n   |
+-------+
|   2   |
+-------+

# inout 输入参数
delimiter //
drop procedure if exists b;

create procedure b(inout n int)
begin
    select n;
    set n = 2;
    select n;
end
//
delimiter ;

set @n = 1;
call b(@n);        # 调用存储过程 b

+---------+
|    n    |
+---------+
|    1    |
+---------+
 
+---------+
|    n    |
+---------+
|    2    |
+---------+

 

2、实例

• 通过存储过程实现造数据

-- 创建表 t_grade 且往表 t_grade 中循环插入数据

delimiter //    -- 定义结束符
drop procedure if exists insertData;
create procedure insertData(n int)    -- 注意参数必须携带数据类型
begin

    declare x int default 1;        -- 变量在一开始就需要定义
    drop table if exists t_grade;
    create table t_grade(id int(10), grade int(3));

while n < 100000 do

    insert into t_grade values(x, (select floor(rand()*6 + 80)));        -- 随机生成分数
    set x = x + 1;
    set n = n + 1;

end while;

select count(*) from t_grade;
end
//
delimiter ;

call insertData(100);        -- 调用存储过程

delimiter //
drop PROCEDURE if EXISTS i;
CREATE PROCEDURE i(n int)
BEGIN
DECLARE x int DEFAULT 1;
DECLARE y varchar(20) DEFAULT "";
DECLARE z int DEFAULT 22;

while n <=100 DO
    set y = CONCAT("zhangsan",x);
    INSERT into t VALUES(x,y,z);
    set x = x + 1;
    set n = n + 1;
end WHILE;
SELECT COUNT(*) from t;
end
//
delimiter ;

call i(1);

-- create table t_user(id int(6),user_name varchar(20),user_pwd varchar(20),verify varchar(5));

delimiter //

drop procedure if exists insert_user_data;
create procedure insert_user_data(n int)

begin
    declare x int default(select count(*) from t_user);
    if x >= n then
        select count(*) from t_user;

    else
        while x < n do
            insert into t_user values(x+1, CONCAT("user",x+1), "123456", (select substring(rand(),3,6)));
            set x = x + 1;
        end while;
    end if;
end
//

delimiter ;

call insert_user_data(500);

• 通过存储过程实现批量删除数据

delimiter //
drop procedure if exists b;

create procedure b(in n int)
begin
    declare x int default 1;

while n < 10 do 
    delete from t where id = x;
    set n = n + 1;
    set x = x + 1;

end while;
select * from t;
end
//
delimiter ;

call b(0)        # 调用存储过程

• 通过存储过程实现查询数据

delimiter //
drop procedure if exists c;
create procedure c(n int)

BEGIN
drop table if exists dcs;
create table dcs(id int(1),name varchar(10),sex char(2));
alter table dcs change id id int(1) primary key auto_increment;
insert into dcs(name,sex)values('zhangsan1','m'),('lisi1','m');
insert into dcs(name,sex)values('zhangsan2','m'),('lisi2','m');
insert into dcs(name,sex)values('zhangsan3','m'),('lisi3','m');
insert into dcs(name,sex)values('zhangsan4','m'),('lisi4','m');
insert into dcs(name,sex)values('zhangsan5','m'),('lisi5','m');
 
if (n<>0) then
 select * from dcs where id < n;
else
 select * from dcs;
end if;
end
//
delimiter ;

call c(6)        # 调用存储过程