MySQL中存儲(chǔ)過程的詳細(xì)詳解
概述
由MySQL5.0 版本開始支持存儲(chǔ)過程。
如果在實(shí)現(xiàn)用戶的某些需求時(shí),需要編寫一組復(fù)雜的SQL語(yǔ)句才能實(shí)現(xiàn)的時(shí)候,那么我們就可以將這組復(fù)雜的SQL語(yǔ)句集提前編寫在數(shù)據(jù)庫(kù)中,由JDBC調(diào)用來(lái)執(zhí)行這組SQL語(yǔ)句。把編寫在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句集稱為存儲(chǔ)過程。
存儲(chǔ)過程:(PROCEDURE)是事先經(jīng)過編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句的集合。調(diào)用存儲(chǔ)過程可以簡(jiǎn)化應(yīng)用開發(fā)人員的很多工作,減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸,對(duì)于提高數(shù)據(jù)處理的效率是很有好處的。
就是數(shù)據(jù)庫(kù) SQL 語(yǔ)言層面的代碼封裝與重用。
存儲(chǔ)過程就類似于Java中的方法,需要先定義,使用時(shí)需要調(diào)用。存儲(chǔ)過程可以定義參數(shù),參數(shù)分為IN、OUT、INOUT類型三種類型。
- IN類型的參數(shù)表示接受調(diào)用者傳入的數(shù)據(jù);
- OUT類型的參數(shù)表示向調(diào)用者返回?cái)?shù)據(jù);
- INOUT類型的參數(shù)即可以接受調(diào)用者傳入的參數(shù),也可以向調(diào)用者返回?cái)?shù)據(jù)。
優(yōu)點(diǎn)
- 存儲(chǔ)過程是通過處理封裝在容易使用的單元中,簡(jiǎn)化了復(fù)雜的操作。
- 簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名、列名、或業(yè)務(wù)邏輯有了變化。只需要更改存儲(chǔ)過程的代碼。使用它的人不用更改自己的代碼。
- 通常存儲(chǔ)過程都是有助于提高應(yīng)用程序的性能。當(dāng)創(chuàng)建的存儲(chǔ)過程被編譯之后,就存儲(chǔ)在數(shù)據(jù)庫(kù)中。
但是,MySQL實(shí)現(xiàn)的存儲(chǔ)過程略有所不同。
MySQL存儲(chǔ)過程是按需編譯。在編譯存儲(chǔ)過程之后,MySQL將其放入緩存中。
MySQL為每個(gè)連接維護(hù)自己的存儲(chǔ)過程高速緩存。如果應(yīng)用程序在單個(gè)連接中多次使用存儲(chǔ)過程,則使用編譯版本,否則存儲(chǔ)過程的工作方式類似于查詢。 - 存儲(chǔ)過程有助于減少應(yīng)用程序和數(shù)據(jù)庫(kù)服務(wù)器之間的流量。
因?yàn)閼?yīng)運(yùn)程序不必發(fā)送多個(gè)冗長(zhǎng)的SQL語(yǔ)句,只用發(fā)送存儲(chǔ)過程中的名稱和參數(shù)即可。 - 存儲(chǔ)過程度任何應(yīng)用程序都是可重用的和透明的。存儲(chǔ)過程將數(shù)據(jù)庫(kù)接口暴露給所有的應(yīng)用程序,以方便開發(fā)人員不必開發(fā)存儲(chǔ)過程中已支持的功能。
- 存儲(chǔ)的程序是安全的。數(shù)據(jù)庫(kù)管理員是可以向訪問數(shù)據(jù)庫(kù)中存儲(chǔ)過程的應(yīng)用程序授予適當(dāng)?shù)臋?quán)限,而不是向基礎(chǔ)數(shù)據(jù)庫(kù)表提供任何權(quán)限。
缺點(diǎn)
- 如果使用大量的存儲(chǔ)過程,那么使用這些存儲(chǔ)過程的每個(gè)連接的內(nèi)存使用量將大大增加。
此外,如果在存儲(chǔ)過程中過度使用大量的邏輯操作,那么CPU的使用率也在增加,因?yàn)镸ySQL數(shù)據(jù)庫(kù)最初的設(shè)計(jì)就側(cè)重于高效的查詢,而不是邏輯運(yùn)算。 - 存儲(chǔ)過程的構(gòu)造使得開發(fā)具有了復(fù)雜的業(yè)務(wù)邏輯的存儲(chǔ)過程變得困難。
- 很難調(diào)試存儲(chǔ)過程。只有少數(shù)數(shù)據(jù)庫(kù)管理系統(tǒng)允許調(diào)試存儲(chǔ)過程。不幸的是,MySQL不提供調(diào)試存儲(chǔ)過程的功能。
- 開發(fā)和維護(hù)存儲(chǔ)過程都不容易。
開發(fā)和維護(hù)存儲(chǔ)過程通常需要一個(gè)不是所有應(yīng)用程序開發(fā)人員擁有的專業(yè)技能。這可能導(dǎo)致應(yīng)用程序開發(fā)和維護(hù)階段的問題。 - 對(duì)數(shù)據(jù)庫(kù)依賴程度較高,移值性差。
MySQL存儲(chǔ)過程的定義
存儲(chǔ)過程的基本語(yǔ)句格式
DELIMITER $$ CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE 數(shù)據(jù)庫(kù)名.存儲(chǔ)過程名([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 值];] 存儲(chǔ)過程的語(yǔ)句塊; END$$ DELIMITER ;
● 存儲(chǔ)過程中的參數(shù)分別是 in,out,inout三種類型;
- in代表輸入?yún)?shù)(默認(rèn)情況下為in參數(shù)),表示該參數(shù)的值必須由調(diào)用程序指定。
- ou代表輸出參數(shù),表示該參數(shù)的值經(jīng)存儲(chǔ)過程計(jì)算后,將out參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序。
- inout代表即時(shí)輸入?yún)?shù),又是輸出參數(shù),表示該參數(shù)的值即可有調(diào)用程序制定,又可以將inout參數(shù)的計(jì)算結(jié)果返回給調(diào)用程序。
● 存儲(chǔ)過程中的語(yǔ)句必須包含在BEGIN和END之間。
● DECLARE中用來(lái)聲明變量,變量默認(rèn)賦值使用的DEFAULT,語(yǔ)句塊中改變變量值,使用SET 變量=值;
存儲(chǔ)過程的使用
定義一個(gè)存儲(chǔ)過程
DELIMITER $$ CREATE PROCEDURE `demo`.`demo1`() -- 存儲(chǔ)過程體 BEGIN -- DECLARE聲明 用來(lái)聲明變量的 DECLARE de_name VARCHAR(10) DEFAULT ''; SET de_name = "jim"; -- 測(cè)試輸出語(yǔ)句(不同的數(shù)據(jù)庫(kù),測(cè)試語(yǔ)句都不太一樣。 SELECT de_name; END$$ DELIMITER ;
調(diào)用存儲(chǔ)過程
CALL demo1();
定義一個(gè)有參數(shù)的存儲(chǔ)過程
先定義一個(gè)student數(shù)據(jù)庫(kù)表:
現(xiàn)在要查詢這個(gè)student表中的sex為男的有多少個(gè)人。
DELIMITER $$ CREATE PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT) -- 存儲(chǔ)過程體 BEGIN -- 把SQL中查詢的結(jié)果通過INTO賦給變量 SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex; SELECT s_count; END$$ DELIMITER ;
調(diào)用這個(gè)存儲(chǔ)過程
-- @s_count表示測(cè)試出輸出的參數(shù) CALL demo2 ('男',@s_count);
定義一個(gè)流程控制語(yǔ)句 IF ELSE
IF 語(yǔ)句包含多個(gè)條件判斷,根據(jù)結(jié)果為 TRUE、FALSE執(zhí)行語(yǔ)句,與編程語(yǔ)言中的 if、else if、else 語(yǔ)法類似。
DELIMITER $$ CREATE PROCEDURE `demo`.`demo3`(IN `day` INT) -- 存儲(chǔ)過程體 BEGIN IF `day` = 0 THEN SELECT '星期天'; ELSEIF `day` = 1 THEN SELECT '星期一'; ELSEIF `day` = 2 THEN SELECT '星期二'; ELSE SELECT '無(wú)效日期'; END IF; END$$ DELIMITER ;
調(diào)用這個(gè)存儲(chǔ)過程
CALL demo3(2);
定義一個(gè)條件控制語(yǔ)句 CASE
case是另一個(gè)條件判斷的語(yǔ)句,類似于編程語(yǔ)言中的 choose、when語(yǔ)法。MySQL 中的 case語(yǔ)句有兩種語(yǔ)法格式。
第一種
DELIMITER $$ CREATE PROCEDURE demo4(IN num INT) BEGIN CASE -- 條件開始 WHEN num<0 THEN SELECT '負(fù)數(shù)'; WHEN num>0 THEN SELECT '正數(shù)'; ELSE SELECT '不是正數(shù)也不是負(fù)數(shù)'; END CASE; -- 條件結(jié)束 END$$ DELIMITER;
調(diào)用這個(gè)存儲(chǔ)過程
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; -- 條件結(jié)束 END$$ DELIMITER;
調(diào)用此函數(shù)
CALL demo5(0);
定義一個(gè)循環(huán)語(yǔ)句 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)結(jié)束 END$$ DELIMITER;
調(diào)用此函數(shù)
-- 調(diào)用函數(shù) CALL demo6(0,@sum); -- 查詢函數(shù) SELECT @sum;
定義一個(gè)循環(huán)語(yǔ)句 REPEAT UNTLL
REPEATE…UNTLL 語(yǔ)句的用法和 Java中的 do…while 語(yǔ)句類似,都是先執(zhí)行循環(huán)操作,再判斷條件,區(qū)別是REPEATE 表達(dá)式值為 false時(shí)才執(zhí)行循環(huán)操作,直到表達(dá)式值為 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)結(jié)束 END$$ DELIMITER;
調(diào)用此函數(shù)
CALL demo7(9,@sum); SELECT @sum;
定義一個(gè)循環(huán)語(yǔ)句 LOOP
循環(huán)語(yǔ)句,用來(lái)重復(fù)執(zhí)行某些語(yǔ)句。
執(zhí)行過程中可使用 LEAVE語(yǔ)句或者ITEREATE來(lái)跳出循環(huán),也可以嵌套IF等判斷語(yǔ)句。
LEAVE 語(yǔ)句效果對(duì)于Java中的break,用來(lái)終止循環(huán);
ITERATE語(yǔ)句效果相當(dāng)于Java中的continue,用來(lái)跳過此次循環(huán)。進(jìn)入下一次循環(huán)。且ITERATE之下的語(yǔ)句將不在進(jìn)行。
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; -- 結(jié)束此次循環(huán) ELSEIF num < 9 THEN ITERATE demo_sum; -- 跳過此次循環(huán) END IF; SET SUM = SUM+num; END LOOP demo_sum; -- 循環(huán)結(jié)束 END$$ DELIMITER;
調(diào)用此函數(shù)
CALL demo8(0,@sum); SELECT @sum;
使用存儲(chǔ)過程插入信息
DELIMITER $$ CREATE PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20)) BEGIN -- 聲明一個(gè)變量 用來(lái)決定這個(gè)名字是否已經(jīng)存在 DECLARE s_count INT DEFAULT 0; -- 驗(yàn)證這么名字是否已經(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;
調(diào)用此函數(shù)
CALL demo9("Jim","女",@s_result);
再次調(diào)用次函數(shù)
CALL demo9("Jim","女",@s_result)
存儲(chǔ)過程的管理
顯示存儲(chǔ)過程
SHOW PROCEDURE STATUS
顯示特定數(shù)據(jù)庫(kù)的存儲(chǔ)過程
SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';
顯示特定模式的存儲(chǔ)過程
SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';
顯示存儲(chǔ)過程的源碼
SHOW CREATE PROCEDURE 存儲(chǔ)過程名;
刪除存儲(chǔ)過程
DROP PROCEDURE 存儲(chǔ)過程名;
后端調(diào)用存儲(chǔ)過程的實(shí)現(xiàn)
在mybatis當(dāng)中,調(diào)用存儲(chǔ)過程
<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 >
調(diào)用數(shù)據(jù)庫(kù)管理
HashMap<String, Object> map = new HashMap<String, Object>(); map.put("name", "Jim"); map.put("sex","男"); userDao.saveUserDemo(map); map.get(“result”);//獲得輸出參數(shù)
通過這樣就可以調(diào)用數(shù)據(jù)庫(kù)中的存儲(chǔ)過程的結(jié)果。
總結(jié)
到此這篇關(guān)于MySQL中存儲(chǔ)過程的文章就介紹到這了,更多相關(guān)MySQL存儲(chǔ)過程內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- Mysql數(shù)據(jù)庫(kù)存儲(chǔ)過程基本語(yǔ)法講解
- MySQL存儲(chǔ)過程輸入?yún)?shù)(in),輸出參數(shù)(out),輸入輸出參數(shù)(inout)
- MySQL一次性創(chuàng)建表格存儲(chǔ)過程實(shí)戰(zhàn)
- MySql存儲(chǔ)表情報(bào)錯(cuò)的排查解決
- MySql存儲(chǔ)過程循環(huán)的使用分析詳解
- MySQL數(shù)據(jù)庫(kù)之存儲(chǔ)過程?procedure
- Mysql怎么存儲(chǔ)json格式數(shù)據(jù)詳解
- MySQL存儲(chǔ)過程及語(yǔ)法詳解
相關(guān)文章
MySQL中union和order by同時(shí)使用的實(shí)現(xiàn)方法
下面小編就為大家?guī)?lái)一篇MySQL中union和order by同時(shí)使用的實(shí)現(xiàn)方法。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來(lái)看看吧2016-12-12Mysql實(shí)現(xiàn)增量恢復(fù)的方法詳解
本文給大家分享的是如何實(shí)現(xiàn)mysql增量恢復(fù)的場(chǎng)景以及具體實(shí)現(xiàn)方法,有需要的小伙伴可以參考下2018-07-07