MySQL儲存過程和函式

變數

系統變數

變數由系統提供，不是使用者定義的，屬於伺服器層面

1. 檢視所有系統變數

SHOW GLOBAL|SESSION VARIABLES;

2. 檢視滿足條件的部分系統變數

SHOW GLOBAL|SESSION VARIABLES LIKE '%char%'

3. 檢視指定的某一個系統變數

SELECT @@global|session.系統變數名;

4. 更新系統變數

SET GLOBAL|SESSION 系統變數名=值;
# 或者
SET @@global|session.系統變數名=值;

全域性變數

作用域：伺服器每次重啟將為所有全域性變數賦初始值，針對所有會話有效，但不能跨重啟

1. 檢視所有全域性變數

SHOW GLOBAL VARIABLES;

2. 檢視滿足條件的部分全域性變數

SHOW GLOBAL VARIABLES LIKE '%char%'

3. 檢視指定的某一個全域性變數

SELECT @@global.全域性變數名;

4. 更新全域性變數

SET GLOBAL 全域性變數名=值;
# 或者
SET @@global.全域性變數名=值;

會話變數

作用域：伺服器每次重啟將為所有全域性變數賦初始值，針對單次會話有效

1. 檢視所有會話變數

SHOW SESSION VARIABLES;

2. 檢視滿足條件的部分會話變數

SHOW SESSION VARIABLES LIKE '%char%'
SHOW VARIABLES LIKE '%char%'

3. 檢視指定的某一個會話變數

SELECT @@session.會話變數名;

4. 更新會話變數

SET SESSION 會話變數名=值;
# 或者
SET @@session.會話變數名=值;

自定義變數

使用者變數

作用域與會話變數相同，只針對當前會話，可以被應用於BEGIN END裡面，也可以被用於其他位置

1. 宣告變數並初始化

SET @使用者變數名=值
# 或者
SET @使用者變數名 := 值
# 或者
SELECT @使用者變數名 := 值

2. 賦值

SET @使用者變數名=值
# 或者
SET @使用者變數名 := 值
# 或者
SELECT @使用者變數名 := 值

案例

SET @name='John'
SET @name=100

或者通過SELECT INTO

SELECT 欄位 INTO 變數名 FROM TABLE WHERE...

3. 使用

SELECT @使用者變數名；

案例

SET @count=1
SELECT COUNT(*) INTO @count FROM employee;
SELECT @count;

區域性變數

區域性變數僅僅在定義它的BEGIN END中有效

1. 宣告

DECLARE 變數名 型別;
DECLARE 變數名 型別 DEFAULT 值;

2. 賦值

SET 區域性變數名=值;
SET 區域性變數名:=值;
SELECT @區域性變數名:=值;

或則

SELECT 欄位 INTO 區域性變數名 FROM 表名;

這個程式碼可以直接執行：

SET @a=1;
SET @b=2;
SET @sum=@a+@b;
SELECT @sum

這個程式碼無法直接執行，只有放在BEGIN END中才能執行：

DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE sum INT;
SET sum=m+n;
SELECT sum;

儲存過程

一組預先編譯好的SQL語句的集合，可以提高程式碼的重用性、簡化操作、減少編譯次數並且減少了和資料庫伺服器建立連線的次數，提高效率。

1. 建立儲存過程的格式

CREATE PROCEDURE 儲存過程名(引數列表)
BEGIN
  儲存過程體（一組SQL）;
END

其中引數列表包括三部分，分散式引數模式 引數名 引數型別，如：IN stu_name VARCHAR(255)

引數的模式分為：

• IN：輸入引數

• OUT：輸出引數

• INOUT：輸入輸出引數

2. 要求

如果儲存過程只有一句話，可以省略BEGIN END，儲存過程體中的每一句SQL都要求在結尾處加分號，在結尾可以使用DELIMETER重新設定

3. 呼叫方法

CALL 儲存過程名(實參列表);

案例1（無參的儲存過程）：

DELIMITER $
CREATE PROCEDURE my_p1()
BEGIN
  INSERT INTO student(stu_name, age)
  VALUES('John',12),('Jerry',11),('Tom', 11),('Rose', 10),('Jack',13);
END $
DELIMITER ;

CALL my_p1();

案例2（帶一個或者多個IN引數的儲存過程）：

DELIMITER $
CREATE PROCEDURE my_p2(IN name VARCHAR(255))
BEGIN
  SELECT bo.*
  FROM boys bo
  RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
  WHERE b.name=name;
END $

CREATE PROCEDURE my_p3(IN username VARCHAR(255), IN password VARCHAR(255))
BEGIN
  DECLARE result VARCHAR(20) DEFAULT '';
  SELECT COUNT(*) INTO result
  FROM user
  WHERE user.username=username AND user.password=password;
  SELECT result;
END $
DELIMITER ;;

CREATE PROCEDURE my_p3_other(IN username VARCHAR(255), IN password VARCHAR(255))
BEGIN
  DECLARE result VARCHAR(20) DEFAULT '';
  SELECT COUNT(*) INTO result
  FROM user
  WHERE user.username=username AND user.password=password;
  SELECT IF(result >0, '成功','失敗');
END $

DELIMITER ;;

CALL my_p2('柳巖');
CALL my_p3('admin', 'passwd');
CALL my_p3_other('admin','password');

案例3（帶OUT模式的儲存過程）：

DELIMITER $
CREATE PROCEDURE my_p5(IN b_name VARCHAR(255), OUT boy_name VARCHAR(255))
BEGIN
  SELECT bo.boyName INTO boy_name
  FROM boys bo
  INNER JOIN beauty b ON bo.id=b.boyfriend_id
  WHERE b.name=b_name;
END $

CREATE PROCEDURE my_p6(IN b_name VARCHAR(255), OUT boy_name VARCHAR(255), OUT user_cp INT)
BEGIN
  SELECT bo.boyName,bo.userCP INTO boy_name,user_cp
  FROM boys bo
  INNER JOIN beauty b ON bo.id=b.boyfriend_id
  WHERE b.name=b_name;
END $

DELIMITER ;;

# 呼叫
SET @b_name;
SET @user_cp;
CALL my_p5('小昭',@b_name);
CALL my_p6('小昭',@b_name, @user_cp);

案例4（帶INOUT模式的儲存過程）：

DELIMITER $
CREATE PROCEDURE my_p8(INOUT a INT, INOUT b INT)
BEGIN
  SET a=a*2;
  SET b=b*2;
END $
DELIMITER ;;

# 呼叫
SET @m=10;
SET @n=20;
CALL my_p8(@n,@m);
SELECT @m,@n;

練習案例

DELIMITER $
CREATE PROCEDURE test1(IN username VARCHAR(255), IN login_pwd VARCHAR(255))
BEGIN
  INSERT INTO user(user.username, user.password)
  VALUES(username, login_pwd);
END $

CREATE PROCEDURE test2(IN id INT, OUT name VARCHAR(255), OUT phone CHAR(11))
BEGIN
  SELECT b.name,b.phone INTO name,phone
  FROM beauty b
  WHERE b.id=id;
END $

# 比較生日大小
CREATE PROCEDURE test3(IN birthday1 DATETIME, IN birthday2 DATETIME, OUT result INT)
BEGIN
  SELECT DATEIFF(birthday1, birthday2) INTO result;
END $

# 格式化日期
CREATE PROCEDURE test4(IN mydate DATETIME, OUT strDate VARCHAR(50))
BEGIN
  SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
END $


CREATE PROCEDURE test5(IN beautyName VARCHAR(255), OUT str VARCHAR(255))
BEGIN
  SELECT CONCAT(beautyName, ' and ', IFNULL(boyName, 'NULL')) INTO str
  FROM boys bo
  RIGHT JOIN beauty b ON b.boyfriend_id=bo.id
  WHERE b.name=beautyName;
END $

CREATE PROCEDURE test6(IN startIndex INT, IN size INT)
BEGIN
  SELECT * FROM beauty LIMIT startIndex,size;
END $

DELIMITER ;;

刪除儲存過程

DROP PROCEDURE 儲存過程名;

檢視儲存過程

SHOW CREATE PROCEDURE 儲存過程名;

函式

函式類似於儲存過程，但是要求返回值有且僅有一個

CREATE FUNCTION 函式名(引數列表) RETURNS 返回值型別
BEGIN
  # 函式體
END

流程控制結構

分支結構

1. if函式

IF(表示式1,表示式2,表示式3)

如果表示式1成立，則返回表示式2的值，否則返回表示式3的值

2. case結構

CASE 變數|表示式|欄位
  WHEN 要判斷的值1 THEN 返回值或者語句1;
  WHEN 要判斷的值2 THEN 返回值或者語句2;
  WHEN 要判斷的值3 THEN 返回值或者語句3;
  ELSE 要返回的值或者語句N;
END CASE;

或者

CASE
  WHEN 要判斷的條件1 THEN 返回值或者語句1;
  WHEN 要判斷的條件2 THEN 返回值或者語句2;
  WHEN 要判斷的條件3 THEN 返回值或者語句3;
  ELSE 要返回的值或者語句N
END CASE;

CASE語句可以放在任何位置，如果作為獨立的語句，只能放在BEGIN END中。

如果沒有提供最後的ELSE語句，並且所有的WHEN都不滿足，則會返回NULL。

案例

DELIMITER $
CREATE PROCEDURE test_case(IN score INT)
BEGIN
  CASE
    WHEN score >=90 AND score <= 100 THEN SELECT 'A';
    WHEN score >=80 THEN SELECT 'B';
    WHEN score >=60 THEN SELECT 'C';
    ELSE SELECT 'D';
  END CASE;
END $
DELEMITER ;

CALL test_case(95);

3. if結構

IF 條件1 THEN 語句1；
ESLEIF 條件2 THEN 語句2;
[ELSE 語句n;]
END IF;

案例

DELIMITER $
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
  IF score >= 90 AND score<=100 THEN RETURN 'A';
  ELSEIF score >=80 THEN RETURN 'B';
  ELSEIF score >=60 THEN RETURN 'C';
  ELSE RETURN 'D';
  END IF;
END $
DELIMITER ;

迴圈結構

1. WHILE

[標籤] WHILE 迴圈條件 DO
  # 迴圈體
END WHILE [標籤];

案例

DELIMITER $
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  a:WHILE i <= insertCount DO
    INSERT INTO user(username, password) VALUES(CONCAT('Rose',i), 'passwd');
    #  break 的意思
    IF i >= 20 THEN LEAVE a;
    END IF;
    #  continue 的意思
    IF MOD(i,2)!=0 THEN ITERATE a;
    END IF;
    SET i = i + 1;
  END WHILE a;
END $
DELIMITER ;

CALL pro_while(100);

2. LOOP

[標籤] loop
  #  迴圈體;
END LOOP 標籤;

3. repeat

[標籤] repeat
  #  迴圈體
UNTIL 結束迴圈的條件
END REPEAT [標籤]