MySQL中存儲過程的詳細詳解
更新時間：2022年06月26日 16:15:23 作者：星辰與晨曦
存儲過程就是一條或者多條SQL語句的集合,可以視為批文件,它可以定義批量插入的語句,也可以定義一個接收不同條件的SQL,下面這篇文章主要給大家介紹了關于MySQL中存儲過程的相關資料,需要的朋友可以參考下
概述
由MySQL5.0 版本開始支持存儲過程。
如果在實現(xiàn)用戶的某些需求時，需要編寫一組復雜的SQL語句才能實現(xiàn)的時候，那么我們就可以將這組復雜的SQL語句集提前編寫在數(shù)據(jù)庫中，由JDBC調用來執(zhí)行這組SQL語句。把編寫在數(shù)據(jù)庫中的SQL語句集稱為存儲過程。
存儲過程：（PROCEDURE）是事先經(jīng)過編譯并存儲在數(shù)據(jù)庫中的一段SQL語句的集合。調用存儲過程可以簡化應用開發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫和應用服務器之間的傳輸，對于提高數(shù)據(jù)處理的效率是很有好處的。
就是數(shù)據(jù)庫 SQL 語言層面的代碼封裝與重用。
存儲過程就類似于Java中的方法，需要先定義，使用時需要調用。存儲過程可以定義參數(shù)，參數(shù)分為IN、OUT、INOUT類型三種類型。
- IN類型的參數(shù)表示接受調用者傳入的數(shù)據(jù)；
- OUT類型的參數(shù)表示向調用者返回數(shù)據(jù)；
- INOUT類型的參數(shù)即可以接受調用者傳入的參數(shù)，也可以向調用者返回數(shù)據(jù)。
優(yōu)點
- 存儲過程是通過處理封裝在容易使用的單元中，簡化了復雜的操作。
- 簡化對變動的管理。如果表名、列名、或業(yè)務邏輯有了變化。只需要更改存儲過程的代碼。使用它的人不用更改自己的代碼。
- 通常存儲過程都是有助于提高應用程序的性能。當創(chuàng)建的存儲過程被編譯之后，就存儲在數(shù)據(jù)庫中。
但是，MySQL實現(xiàn)的存儲過程略有所不同。
MySQL存儲過程是按需編譯。在編譯存儲過程之后，MySQL將其放入緩存中。
MySQL為每個連接維護自己的存儲過程高速緩存。如果應用程序在單個連接中多次使用存儲過程，則使用編譯版本，否則存儲過程的工作方式類似于查詢。 - 存儲過程有助于減少應用程序和數(shù)據(jù)庫服務器之間的流量。
因為應運程序不必發(fā)送多個冗長的SQL語句，只用發(fā)送存儲過程中的名稱和參數(shù)即可。 - 存儲過程度任何應用程序都是可重用的和透明的。存儲過程將數(shù)據(jù)庫接口暴露給所有的應用程序，以方便開發(fā)人員不必開發(fā)存儲過程中已支持的功能。
- 存儲的程序是安全的。數(shù)據(jù)庫管理員是可以向訪問數(shù)據(jù)庫中存儲過程的應用程序授予適當?shù)臋嘞?，而不是向基礎數(shù)據(jù)庫表提供任何權限。
缺點
- 如果使用大量的存儲過程，那么使用這些存儲過程的每個連接的內存使用量將大大增加。
此外，如果在存儲過程中過度使用大量的邏輯操作，那么CPU的使用率也在增加，因為MySQL數(shù)據(jù)庫最初的設計就側重于高效的查詢，而不是邏輯運算。 - 存儲過程的構造使得開發(fā)具有了復雜的業(yè)務邏輯的存儲過程變得困難。
- 很難調試存儲過程。只有少數(shù)數(shù)據(jù)庫管理系統(tǒng)允許調試存儲過程。不幸的是，MySQL不提供調試存儲過程的功能。
- 開發(fā)和維護存儲過程都不容易。
開發(fā)和維護存儲過程通常需要一個不是所有應用程序開發(fā)人員擁有的專業(yè)技能。這可能導致應用程序開發(fā)和維護階段的問題。 - 對數(shù)據(jù)庫依賴程度較高，移值性差。
MySQL存儲過程的定義
存儲過程的基本語句格式
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE 數(shù)據(jù)庫名.存儲過程名([in變量名 類型,out 參數(shù) 2，...])
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
[DECLARE 變量名 類型 [DEFAULT 值];]
存儲過程的語句塊;
END$$
DELIMITER ;
● 存儲過程中的參數(shù)分別是 in，out，inout三種類型；
- in代表輸入?yún)?shù)（默認情況下為in參數(shù)），表示該參數(shù)的值必須由調用程序指定。
- ou代表輸出參數(shù)，表示該參數(shù)的值經(jīng)存儲過程計算后，將out參數(shù)的計算結果返回給調用程序。
- inout代表即時輸入?yún)?shù)，又是輸出參數(shù)，表示該參數(shù)的值即可有調用程序制定，又可以將inout參數(shù)的計算結果返回給調用程序。
● 存儲過程中的語句必須包含在BEGIN和END之間。
● DECLARE中用來聲明變量，變量默認賦值使用的DEFAULT，語句塊中改變變量值，使用SET 變量=值；
存儲過程的使用
定義一個存儲過程
DELIMITER $$
CREATE
PROCEDURE `demo`.`demo1`()
-- 存儲過程體
BEGIN
-- DECLARE聲明 用來聲明變量的
DECLARE de_name VARCHAR(10) DEFAULT '';
SET de_name = "jim";
-- 測試輸出語句（不同的數(shù)據(jù)庫，測試語句都不太一樣。
SELECT de_name;
END$$
DELIMITER ;
調用存儲過程
CALL demo1();
定義一個有參數(shù)的存儲過程
先定義一個student數(shù)據(jù)庫表：
現(xiàn)在要查詢這個student表中的sex為男的有多少個人。
DELIMITER $$
CREATE
PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
-- 存儲過程體
BEGIN
-- 把SQL中查詢的結果通過INTO賦給變量
SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
SELECT s_count;
END$$
DELIMITER ;
調用這個存儲過程
-- @s_count表示測試出輸出的參數(shù)
CALL demo2 ('男',@s_count);
定義一個流程控制語句 IF ELSE
IF 語句包含多個條件判斷，根據(jù)結果為 TRUE、FALSE執(zhí)行語句，與編程語言中的 if、else if、else 語法類似。
DELIMITER $$
CREATE
PROCEDURE `demo`.`demo3`(IN `day` INT)
-- 存儲過程體
BEGIN
IF `day` = 0 THEN
SELECT '星期天';
ELSEIF `day` = 1 THEN
SELECT '星期一';
ELSEIF `day` = 2 THEN
SELECT '星期二';
ELSE
SELECT '無效日期';
END IF;
END$$
DELIMITER ;
調用這個存儲過程
CALL demo3(2);
定義一個條件控制語句 CASE
case是另一個條件判斷的語句，類似于編程語言中的 choose、when語法。MySQL 中的 case語句有兩種語法格式。
第一種
DELIMITER $$
CREATE
PROCEDURE demo4(IN num INT)
BEGIN
CASE -- 條件開始
WHEN num<0 THEN
SELECT '負數(shù)';
WHEN num>0 THEN
SELECT '正數(shù)';
ELSE
SELECT '不是正數(shù)也不是負數(shù)';
END CASE; -- 條件結束
END$$
DELIMITER;
調用這個存儲過程
CALL demo4(1);
2.第二種
DELIMITER $$
CREATE
PROCEDURE demo5(IN num INT)
BEGIN
CASE num -- 條件開始
WHEN 1 THEN
SELECT '輸入為1';
WHEN 0 THEN
SELECT '輸入為0';
ELSE
SELECT '不是1也不是0';
END CASE; -- 條件結束
END$$
DELIMITER;
調用此函數(shù)
CALL demo5(0);
定義一個循環(huán)語句 WHILE
DELIMITER $$
CREATE
PROCEDURE demo6(IN num INT,OUT SUM INT)
BEGIN
SET SUM = 0;
WHILE num<10 DO -- 循環(huán)開始
SET num = num+1;
SET SUM = SUM+num;
END WHILE; -- 循環(huán)結束
END$$
DELIMITER;
調用此函數(shù)
-- 調用函數(shù) CALL demo6(0,@sum); -- 查詢函數(shù) SELECT @sum;
定義一個循環(huán)語句 REPEAT UNTLL
REPEATE…UNTLL 語句的用法和 Java中的 do…while 語句類似，都是先執(zhí)行循環(huán)操作，再判斷條件，區(qū)別是REPEATE 表達式值為 false時才執(zhí)行循環(huán)操作，直到表達式值為 true停止。
-- 創(chuàng)建過程
DELIMITER $$
CREATE
PROCEDURE demo7(IN num INT,OUT SUM INT)
BEGIN
SET SUM = 0;
REPEAT-- 循環(huán)開始
SET num = num+1;
SET SUM = SUM+num ;
UNTIL num>=10
END REPEAT; -- 循環(huán)結束
END$$
DELIMITER;
調用此函數(shù)
CALL demo7(9,@sum); SELECT @sum;
定義一個循環(huán)語句 LOOP
循環(huán)語句，用來重復執(zhí)行某些語句。
執(zhí)行過程中可使用 LEAVE語句或者ITEREATE來跳出循環(huán)，也可以嵌套IF等判斷語句。
LEAVE 語句效果對于Java中的break，用來終止循環(huán)；
ITERATE語句效果相當于Java中的continue，用來跳過此次循環(huán)。進入下一次循環(huán)。且ITERATE之下的語句將不在進行。
DELIMITER $$
CREATE
PROCEDURE demo8(IN num INT,OUT SUM INT)
BEGIN
SET SUM = 0;
demo_sum:LOOP-- 循環(huán)開始
SET num = num+1;
IF num > 10 THEN
LEAVE demo_sum; -- 結束此次循環(huán)
ELSEIF num < 9 THEN
ITERATE demo_sum; -- 跳過此次循環(huán)
END IF;
SET SUM = SUM+num;
END LOOP demo_sum; -- 循環(huán)結束
END$$
DELIMITER;
調用此函數(shù)
CALL demo8(0,@sum); SELECT @sum;
使用存儲過程插入信息
DELIMITER $$
CREATE
PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
BEGIN
-- 聲明一個變量 用來決定這個名字是否已經(jīng)存在
DECLARE s_count INT DEFAULT 0;
-- 驗證這么名字是否已經(jīng)存在
SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;
IF s_count = 0 THEN
INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
SET s_result = '數(shù)據(jù)添加成功';
ELSE
SET s_result = '名字已存在，不能添加';
SELECT s_result;
END IF;
END$$
DELIMITER;
調用此函數(shù)
CALL demo9("Jim","女",@s_result);
再次調用次函數(shù)
CALL demo9("Jim","女",@s_result)
存儲過程的管理
顯示存儲過程
SHOW PROCEDURE STATUS
顯示特定數(shù)據(jù)庫的存儲過程
SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';
顯示特定模式的存儲過程
SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';
顯示存儲過程的源碼
SHOW CREATE PROCEDURE 存儲過程名;
刪除存儲過程
DROP PROCEDURE 存儲過程名;
后端調用存儲過程的實現(xiàn)
在mybatis當中，調用存儲過程
<parameterMap type="savemap" id=“usermap">
<parameter property="name" jdbcType="VARCHAR" mode="IN"/>
<parameter property="sex" jdbcType="CHAR" mode="IN"/>
<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>
<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE">
{call saveuser(?, ?, ?)}
</insert >
調用數(shù)據(jù)庫管理
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("name", "Jim");
map.put("sex","男");
userDao.saveUserDemo(map);
map.get(“result”);//獲得輸出參數(shù)
通過這樣就可以調用數(shù)據(jù)庫中的存儲過程的結果。
總結
到此這篇關于MySQL中存儲過程的文章就介紹到這了,更多相關MySQL存儲過程內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
下面小編就為大家?guī)硪黄猰ysql巡檢腳本(必看篇)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
隨著網(wǎng)站數(shù)據(jù)量與訪問量的增加，MySQL 查詢速度慢與性能差的問題就日漸明顯，這里為大家分享一下解決方法,需要的朋友可以參考下2019-09-09
MySQL中union和order by同時使用的實現(xiàn)方法
下面小編就為大家?guī)硪黄狹ySQL中union和order by同時使用的實現(xiàn)方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-12-12
本文給大家分享的是如何實現(xiàn)mysql增量恢復的場景以及具體實現(xiàn)方法，有需要的小伙伴可以參考下2018-07-07
在生產(chǎn)環(huán)境中，由于業(yè)務的增長或者業(yè)務的拆分，DBA經(jīng)常需要拆庫操作。那么我們常見的拆庫手段有哪些呢2016-07-07
之前在使用MySQL 5.5 Command Line Client時，無論輸入什么密碼，都出現(xiàn)閃退的情況，糾結了半天才找到原因，下面小編給大家分享我的解決方法，感興趣的朋友一起看看吧2016-11-11
這篇文章主要介紹了mysql?or走索引加索引及慢查詢的作用，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-09-09
這篇文章主要介紹了教你如何使用MySQL8遞歸的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-12-12
索引并不是時時都會生效的,在一起情況下將導致索引失效,這篇文章主要給大家介紹了關于MySQL索引失效的幾種情況,文章通過圖文介紹的非常詳細,需要的朋友可以參考下2023-06-06
這篇文章主要介紹了Python 中刪除文件的幾種方法匯總，幫助大家更好的理解和學習使用python，感興趣的朋友可以了解下2021-03-03