MySQL數據庫之存儲過程 procedure

前言:

stored procedure 完成特定功能的SQL語句集,存儲在數據庫中,經過第一次編譯之後再次調用不需要編譯(效率較高)

1、存儲過程與函數的區別

1.1、相同點

  • 都是為瞭可重復地執行操作數據庫的SQL語句集合
  • 都是一次編譯,多次執行

1.2、不同點

  • 標識符不同,函數function 過程 procedure
  • 函數中有返回值,且必須返回,而過程沒有返回值
  • 過程無返回值類型,不能將結果直接賦值給變量;函數有返回值類型,調用時,除瞭在select中,必須將返回值賦值給變量
  • 函數可以再select語句中直接使用,而過程不能

2、存儲過程的操作

2.1、創建過程

基本語法:

create procedure 過程名字([參數列表])
bengin
    過程體
end
結束符

如果隻有隻有一條指令可以省略begin和end

create procedure my_pro1()
select * from my_student;

過程基本上可以完成函數對應的所有功能:

-- 修改語句結束符
delimiter $$
-- 創建過程
create procedure my_pro2()
begin
    -- 求1到100之間的和
    -- 創建局部變量
    declare i int default 1;
    -- declare sum int default 0;
    -- 會話變量
    set @sum = 0;
    -- 開始循環獲取結果
    while i <= 100 do
        -- 求和
        set @sum = @sum + i;
        set i = i + 1;
    end while;

    -- 顯示結果
    select @sum;
end
$$
delimiter ;

2.2、查看過程

-- 查看所有存儲過程
show procedure status [like 'pattern'];
-- 查看過程的創建語句
show create procedure 過程名字\G

2.3、調用過程

過程沒有返回值

基本語法:

call 過程名([實參列表]);
-- eg:
call my_pro2();
+------+
| @sum |
+------+
| 5050 |
+------+

2.4、刪除過程

基本語法:

drop procedure 過程名;

3、存儲過程的形參類型

存儲過程的參數和函數一樣,需要制定其類型

但是存儲過程對參數還有額外的要求,自己的參數分類:

  • in:(值傳遞)參數從外部傳入,在過程內部使用,可以是直接數據,也可以是保存數據的變量
  • out:(引用傳遞)參數在過程中賦值,傳入必須是變量,如果有外部數據,會被清空為null
  • inout:(引用傳遞)數據可以從外部傳入過程內部使用,同時內部操作之後,又回將數據返回給外部

代碼示例:

-- 創建3個會話變量
set @var1 = 1;
set @var2 = 2;
set @var3 = 3;
-- 查詢會話變量
select @var1, @var2, @var3;
+-------+-------+-------+
| @var1 | @var2 | @var3 |
+-------+-------+-------+
|     1 |     2 |     3 |
+-------+-------+-------+
1 row in set (0.00 sec)
-- 修改語句結束符
delimiter $$
-- 定義過程
create procedure my_pro3(in a int, out b int, inout c int)
begin
    -- 查看傳入的3個數據值
    select a, b, c;
    -- +------+------+------+
    -- | a    | b    | c    |
    -- +------+------+------+
    -- |    1 | NULL |    3 |
    -- +------+------+------+
    -- 修改3個變量值
    set a = 10;
    set b = 20;
    set c = 30;
    select a, b, c;
    -- +------+------+------+
    -- | a    | b    | c    |
    -- +------+------+------+
    -- |   10 |   20 |   30 |
    -- +------+------+------+
    -- 查看會話變量
    select @var1, @var2, @var3;
    -- +-------+-------+-------+
    -- | @var1 | @var2 | @var3 |
    -- +-------+-------+-------+
    -- |     1 |     2 |     3 |
    -- +-------+-------+-------+
    -- 修改會話變量
    set @var1 = 'a';
    set @var2 = 'b';
    set @var3 = 'c';
    select @var1, @var2, @var3;
    -- +-------+-------+-------+
    -- | @var1 | @var2 | @var3 |
    -- +-------+-------+-------+
    -- | a     | b     | c     |
    -- +-------+-------+-------+
end
$$
delimiter ;
-- 調用過程
call my_pro3(@var1, @var2, @var3);

-- 再次查看會話變量
mysql> select @var1, @var2, @var3;
+-------+-------+-------+
| @var1 | @var2 | @var3 |
+-------+-------+-------+
| a     |    20 |    30 |
+-------+-------+-------+

分析:

  • 1、實參傳入過程之後,實際上沒有改變外部變量的值,而是把值給瞭形參,out類型不能接收外部變量的值,默認為null
  • 2、當過程執行到end 的時候,如果是out或inout變量,會將形參的值重新賦值給實參變量

推薦閱讀: