MySQL數據庫之存儲過程 procedure

Posted on by

前言：

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變量，會將形參的值重新賦值給實參變量

推薦閱讀：