(2021.12.19 Sat)
SQL語(yǔ)句需要先編譯再執(zhí)行厘线，存儲(chǔ)過(guò)程(stored procedure)是完成了特定功能的SQL語(yǔ)句集合投放，經(jīng)過(guò)編譯保存在數(shù)據(jù)庫(kù)中。用戶可以通過(guò)名字調(diào)用和執(zhí)行存儲(chǔ)過(guò)程挣磨，并可以傳遞參數(shù)金蜀，和返回?cái)?shù)據(jù)。

存儲(chǔ)過(guò)程是可編程的函數(shù)吃靠，可以簡(jiǎn)單理解為是在數(shù)據(jù)庫(kù)中實(shí)現(xiàn)的面向?qū)ο笾械姆椒ā?/p>

創(chuàng)建存儲(chǔ)過(guò)程

創(chuàng)建過(guò)程偽代碼

CREATE PROCEDURE <procedure_name>([[IN | OUT | INOUT] arg_name arg_type[, arg_name1 arg_type1])
BEGIN
        <SQL_declaration_section>
        <SQL_execution_section>
END

對(duì)于MySQL硫眨，還需要加入DELIMITER關(guān)鍵詞

DELIMITER &&
CREATE PROCEDURE <procedure_name>([[IN | OUT | INOUT] arg_name arg_type[, arg_name1 arg_type1])
BEGIN
        <SQL_declaration_section>
        <SQL_execution_section>
END &&
DELIMITER;

說(shuō)明：

• procedure_name: 過(guò)程名
• IN | OUT | OUTIN: 參數(shù)傳遞方式，IN表示調(diào)用者向過(guò)程傳入的參數(shù)巢块，OUT表示過(guò)程返回給調(diào)用者的參數(shù)/結(jié)果礁阁，OUTIN表示向過(guò)程傳遞變量也從過(guò)程返回變量。
• arg_name & arg_type：傳入或傳出的變量名與類型
• BEGIN...END：過(guò)程主體
• declaration_section：主體的聲明變量部分
• execution_section：函數(shù)執(zhí)行部分代碼

聲明部分

聲明部分的格式如下

DECLARE <var_name> <var_type> [DEFAULT <default_value>];

DECLARE net_pay FLOAT(9,2) DEFAULT 0;
DECLARE id_name INT DEFAULT 0;
DECLARE dt DATETIME DEFAULT '2021-12-19 11:41:30';
DECLARE vv VARCHAR(255) DEFAULT 'This is a stored procedure demo.';

執(zhí)行部分格式

執(zhí)行部分即SQL的指令夕冲。除了常規(guī)的SQL命令氮兵，可使用IF-ELSE，CASE-WHEN歹鱼，WHILE-DO泣栈，REPEAT-UNTIL-END，LOOP-END語(yǔ)句弥姻。這些循環(huán)或判斷結(jié)構(gòu)可結(jié)合LEAVE指令用來(lái)結(jié)束結(jié)構(gòu)南片。同時(shí)注意到，BEGIN-END結(jié)構(gòu)可以嵌套庭敦。

BEGIN-END結(jié)構(gòu)可以被貼標(biāo)簽疼进。

<label>: BEGIN
              <sql_statement>;
END <label>;

如

label_1: BEGIN
           DECLARE var1 int DEFAULT 0;
           SELECT var1;
END label_1;

判斷和循環(huán)語(yǔ)句的格式

WHILE-DO...END-WHILE

DELIMITER //
CREATE PROCEDURE proc1 ()
     BEGIN
          DECLEAR var INT;
          SET var = 7;
          WHILE var < 6 DO
               INSERT INTO specific_table VALUES (var);
               SET var = var + 1;
          END WHILE;
     END;
//
DELIMITER;

REPEAT...UNTIL...END REPEAT，該命令與WHILE不同的是先運(yùn)行秧廉，后檢查運(yùn)行條件伞广。

DELIMITER //
CREATE PROCEDURE proc2 ()
     BEGIN
          DECLEAR var INT;
          SET var = 5;
          REPEAT 
               INSERT INTO specific_table VALUE (var);
               SET var = 6;
               UNTIL var >= 7;
           END REPEAT;
     END;
//
DELIMITER;

LOOP

DELIMITER//
CREATE PROCEDURE proc ()
     BEGIN
          DECLEAR var INT;
          SET var = 4;
          loop_label: LOOP
               INSERT INTO specific_table VALUES (var);
               SET var = var + 1;
               IF var >= 6
                    LEAVE loop_label;
               END IF;
          END LOOP;
     END;
//
DELIMITER

IF-ELSE-THEN語(yǔ)句

DELIMITER //
CREATE PROCEDURE proc (IN para INT)
     BEGIN
          DECLEAR var INT;
          SET var = para + 6;
          IF para > 0 THEN
               INSERT INTO specific_table VALUES (var);
          ELSE
               INSERT INTO specific_table VALUES (var-para);
          END IF;
     END;
//
DELIMITER;

CASE-WHEN-THEN-ELSE語(yǔ)句

DELIMITER //
CREATE PROCEDURE proc4 (IN parameter INT)
     BEGIN
          DECLARE var INT;
          SET var=parameter+1;
          CASE var
          WHEN 0 THEN
               INSERT INTO t VALUES (17);
          WHEN 1 THEN
               INSERT INTO t VALUES (18);
          ELSE
               INSERT INTO t VALUES (19);
          END CASE ;
     END ;
//
DELIMITER ;

ITERATE迭代通過(guò)引用復(fù)合語(yǔ)句的label，來(lái)從新開始復(fù)合語(yǔ)句疼电。

#ITERATE
DELIMITER //
  CREATE PROCEDURE proc()
  BEGIN
    DECLARE v INT;
    SET v=0;
    LOOP_LABLE:LOOP
      IF v=3 THEN
        SET v=v+1;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
        LEAVE LOOP_LABLE;
      END IF;
    END LOOP;
  END;
  //
DELIMITER ;

變量作用域

分隔符DELIMITER

注意DELIMITER//和DELIMITER;兩句嚼锄，MySQL默認(rèn)以";"為分隔 符，如果沒(méi)有聲明分割符蔽豺，編譯器會(huì)把存儲(chǔ)過(guò)程當(dāng)成SQL語(yǔ)句進(jìn)行處理区丑，則存儲(chǔ)過(guò)程的編譯過(guò)程會(huì)報(bào)錯(cuò)，所以要事先用DELIMITER關(guān)鍵字聲明當(dāng)前段分隔符修陡，這樣MySQL才會(huì)將";"當(dāng)做存儲(chǔ)過(guò)程中的代碼沧侥，不會(huì)執(zhí)行這些代碼，用完了之后要把分隔符還原魄鸦。

調(diào)用存儲(chǔ)過(guò)程

在mysql console中調(diào)用格式如下

mysql -> CALL proc_name();

如果調(diào)用時(shí)需要傳入?yún)?shù)或傳出參數(shù)宴杀，需要提前聲明，注意在console中變量名前需要加@拾因。
變量賦值部分的格式如下

SET @<var_name> = <var_value>;
or
SELECT <var_value> INTO @<var_name>;

SET @var = 100; // 賦值
SELECT 'this is a test string' INTO @char_var;
SELECT @var; // 選定和返回?cái)?shù)值變量
SELECT @char_var; // 選定和返回字符變量

注意旺罢，在存儲(chǔ)過(guò)程的執(zhí)行部分調(diào)用時(shí)不需要@符號(hào)斯棒，直接使用變量名即可。在mysql console需要有@標(biāo)識(shí)符主经。

mysql -> SET @var = 1;
mysql -> CALL proc_name(@var);

存儲(chǔ)過(guò)程的查詢

SELECT name FROM mysql.proc WHERE db = <db_name>;
SELECT routine_name FROM information_schema.routines WHERE routine_schema = <db_name>;
SHOW PROCEDURE STATUS WHERE db = <db_name>;

#查看存儲(chǔ)過(guò)程詳細(xì)信息
SHOW CREATE PROCEDURE 數(shù)據(jù)庫(kù).存儲(chǔ)過(guò)程名;

存儲(chǔ)過(guò)程的修改和刪除

ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的預(yù)先指定的存儲(chǔ)過(guò)程，其不會(huì)影響相關(guān)存儲(chǔ)過(guò)程或存儲(chǔ)功能庭惜。

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

變量解釋：

• sp_name參數(shù)表示存儲(chǔ)過(guò)程或函數(shù)的名稱罩驻；
• characteristic參數(shù)指定存儲(chǔ)函數(shù)的特性。
• CONTAINS SQL表示子程序包含SQL語(yǔ)句护赊，但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句惠遏；
• NO SQL表示子程序中不包含SQL語(yǔ)句；
• READS SQL DATA表示子程序中包含讀數(shù)據(jù)的語(yǔ)句骏啰；
• MODIFIES SQL DATA表示子程序中包含寫數(shù)據(jù)的語(yǔ)句节吮。
• SQL SECURITY { DEFINER | INVOKER }指明誰(shuí)有權(quán)限來(lái)執(zhí)行，* DEFINER表示只有定義者自己才能夠執(zhí)行判耕；INVOKER表示調(diào)用者可以執(zhí)行透绩。
  COMMENT 'string'是注釋信息。

刪除

DROP PROCEDURE [proc_name1[, proc_name2...]];