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 且往表 t 中回圈插入資料
delimiter //
drop procedure if exists a;

create procedure a(in n int)
begin

    declare x int default 1;
    declare y int default 10;
    drop table if exists t;
    create table t(id int(3),age int(3));

while n < 20 do

    insert into t values(x,y);
    set x = x + 1;
    set y = y + 10;
    set n = n + 1;

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

call a(5)        # 呼叫存盤程序

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);

• 通過存盤程序實作批量洗掉資料

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)        # 呼叫存盤程序

標籤：MySQL

上一篇：從零學習資料庫mysql--表的操作

下一篇：【Linux Mysql主從配置】整理主從配置遇到的坑！