存儲過程
存儲過程是一條或多條的SQL語句的集合,可以了解為批檔案。建立的存儲過程儲存在資料庫的資料字典中。
1、使用存儲過程的原因
- 将重複性很高的操作,封裝到一個存儲過程中,簡化了對這些SQL的調用
- 批量處理
- 統一接口,確定資料安全
- 相對于Oracle來說,Mysql的存儲過程相對功能較弱,使用較少
2、存儲過程的建立和調用
(1)delimiter$$
- 與存儲過程無關
- delimiter$$ 将标準分隔符 - 分号 (😉 更改為 兩個 $
- 将存儲過程作為整體,而不是一次解釋每條語句
- 告訴mysql解釋器,該段指令已經結束了。預設情況下,delimiter 是分号,在指令行用戶端中,如果有一行指令以分号結束,回車後,mysql将會執行該指令。如果不希望這樣,就可以
,這樣隻有在delimiter$$
出現後,mysql解釋器才會執行 這段語句。$$
(2)建立和調用存儲過程
# 建立存儲過程
delimiter$$
CREATE PROCEDURE 名稱()
BEGIN
語句
END$$
delimiter ;
# 調用存儲過程
CALL 名稱()
如圖:
(3)檢視存儲過程
- 檢視所有存儲過程:
SHOW PROCEDURE STATUS;
- 檢視指定資料庫中的存儲過程:
SHOW PROCEDURE STATUS WHERE DB = ;
- 檢視指定存儲過程源碼 :
SHOW CREATE PROCEDURE show_emp;
- 删除指定存儲過程:
DROP PROCEDURE show_emp;
(4)存儲過程變量
存儲過程變量隻能在 begin end 之間有效
delimiter $$
CREATE PROCEDURE test()
BEGIN
-- 聲明變量,預設為空
DECLARE res VARCHAR(255) DEFAULT '';
-- 聲明兩個變量,類型為int ,預設為 0
DELETE x,y int DEFAULT 0
-- 指派 set 方式
set x = 3;
set y = 4;
DECLARE avgRes DOUBLE DEFAULT 0;
-- 另一種指派方式 into 方式
select avg(salary) into avgRes from emp
end$$
(5)存儲過程參數傳遞
in
用于CALL傳入資料
-- 根據傳入的名稱,擷取對應的資訊
delimiter $$
create PROCEDURE getName(in name VARCHAR(255))
BEGIN
SELECT * FROM emp where ename = name;
end$$
delimiter ;
使用
CALL getName('魯班')
out
用于CALL後輸出資料
-- 傳入姓名,得出薪水
delimiter $$
create procedure getSalary(in name varchar(255),out money int)
BEGIN
select salary into money from emp where ename = name;
end$$
delimiter ;
CALL getSalary('李白',@s) -- @s 代表傳回的數
select @s; -- 也可以寫為 select @s from DUAL
inout
既是輸入,又是輸出
delimiter $$
create procedure test(inout num int,in inc int) -- inout ,既是輸入,也是輸出
BEGIN
set num = num + inc;
end$$
delimiter ;
set @num1 = 20; -- @ 代表位址傳遞
CALL test(@num1, 10);
select @num1;
3、和函數的比較
相同點
- 本質上都是存儲程式。
不同點
- 函數隻能通過return語句傳回單個值或表對象,而存儲過程不允許執行return語句。
- 存儲過程可以通過out參數傳回多個值。
- 存儲過程可以傳回參數,而函數隻能傳回值或者表對象。
- 函數限制比較多,比如不能用臨時表,隻能用表變量.還有一些函數都不可用等。
- 存儲過程可以實作很複雜的業務邏輯;而自定義函數實作的功能針對性比較強,隻完成查詢的工作,可接受輸入參數并傳回一個結果,不能在函數中使用insert,update,delete,create等語句;
- 存儲過程一般是作為一個獨立的部分來執行(call調用)。而函數可以作為查詢語句的一個部分來調用。
- 存儲過程可以調用存儲函數。但函數不能調用存儲過程。