存盤程序

介紹

存盤程序是事先經過編譯并存盤在資料庫中的一段SQL陳述句的集合，呼叫存盤程序可以簡化應用開發人員的很多作業，減少資料在資料庫和應用服務器之間的傳輸，對于提高資料處理的效率是有好處的，

存盤程序思想上很簡單，就是資料庫SQL語言層面的代碼封裝與重用，類似于函式或者說API，封裝了系列操作，暴露介面給你進行操作，

• 特點
  封裝，復用
  可以接收引數，也可以回傳資料
  減少網路互動，效率提升

基本語法

• 創建

  CREATE PROCEDURE 存盤程序名稱([引數串列])
  BEGIN
      -- SQL陳述句
  END;
  

• 呼叫

  CALL 名稱([引數]);
  

• 查看

  -- 查詢指定資料庫的存盤程序及狀態資訊
  SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '資料庫名稱';
  -- 查詢某個存盤程序的定義
  SHOW CREATE PROCEDURE 存盤程序名稱;
  

• 洗掉

  DROP PROCEDURE [IF EXISTS] 存盤程序名稱;\
  

注意：在命令列中，執行創建存盤程序的SQL陳述句時，需要通過關鍵字 delimiter 指定SQL陳述句的結束符，

代碼演示：

-- 存盤程序基本語法

-- 創建
CREATE PROCEDURE p1()
BEGIN
	SELECT COUNT(*) FROM emp;
END;

-- 如果在命令列執行的話，上面陳述句會出錯，因為遇到第一個分號就結束了，
-- 所以需要用到 delimiter
-- delimiter $$ --表示指定 $$ 為結束符（記得之后再使用delimiter改回來）

-- CREATE PROCEDURE p1()
-- BEGIN
-- 	SELECT COUNT(*) FROM emp;
-- END;$$
-- 這樣就可以解決

-- 呼叫
CALL p1();

-- 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'hsp_db02';
SHOW CREATE PROCEDURE p1;

-- 洗掉
DROP PROCEDURE IF EXISTS p1;

變數

• 系統變數
  系統變數是MySQL服務器提供，不是用戶定義的，屬于服務器層面，分為全域變數（GLOBAL）、會話變數（SESSION），

  • 查看系統變數

    -- 查看所有系統變數
    SHOW [SESSION|GLOBAL] VARIABLES;
    -- 可以通過LIKE模糊匹配方式查找變數、
    SHOW [SESSION|GLOBAL] VARIABLES LIKE '......';
    -- 查看指定變數的值
    SELECT @@[SESSION|GLOBAL] 系統變數名;
    

  • 設定系統變數

    SET [SESSION|GLOBAL] 系統變數名 = 值;
    SET @@[SESSION|GLOBAL]系統變數名 = 值;
    

  注意：

  1. 如果沒有指定是session或global，則默認是session，會話變數，
  2. mysql服務重新啟動后，所設定的全域引數會失效，要想不失效，可以在/etc/my.cnf中配置

  代碼演示：

  -- 變數：系統變數
  -- 查看系統變數
  show variables; -- 所有系統變數
  show session variables; -- 會話變數
  show global variables; -- 全域變數
  
  show session variables like 'auto%';
  show global variables like 'auto%';
  
  select @@global.autocommit;
  select @@session.autocommit;
  
  -- 設定系統變數
  set session autocommit = 0; -- 設定為0，代表關閉了當前會話的事務的自動提交
  set global autocommit = 0; -- 當服務器重啟后，這個引數又會初始化為默認值，想要不失效，需要在etc/my.cnf中配置
  

• 用戶定義變數
  用戶定義變數 是用戶根據需要自己定義的變數，用戶不用提前宣告，在用的時候直接用 "@變數名"使用就可以，其作用域為當前連接（會話），

  • 賦值

    SET @var_name = expr[,@var_name = expr]...;
    SET @var_name := expr[,@var_name := expr]...;
    

    SELECT @var_name := expr[,@var_name := expr]...;
    SELECT 欄位名 INTO @var_name FROM 表名;
    

  • 使用

    SELECT @var_name;
    

  注意：用戶定義的變數是無需對其進行宣告或初始化的，只不過獲取到的值為NULL，
  代碼演示：

  -- 變數：用戶變數
  -- 賦值
  SET @myname = 'itcast';
  SET @myage := 10; -- 推薦
  set @mygender := '男', @myhobby := 'java';
  
  SELECT @mycolor := 'red';
  SELECT COUNT(*) into @mycount FROM demo;
  
  -- 使用
  SELECT @myname, @myage, @mygender, @myhobby;
  SELECT @mycolor, @mycount;
  
  SELECT @abc; -- NULL
  

• 區域變數
  區域變數是根據需要定義在區域生效的變數，訪問之前，需要DECLARE宣告，可以用作存盤程序內的區域變數和輸入引數，區域變數的范圍是在其內宣告的BEGIN ... END塊中，

  • 宣告

    DECLARE 變數名 變數型別[DEFAULT ...];
    

    變數型別就是資料庫欄位型別：INT、BiGINT、CHAR、VARCHAR、DATE、TIME等，

  • 賦值

    SET 變數名 = 值;
    SET 變數名 := 值;
    SELECT 欄位名 INTO 變數名 FROM 表名...;
    

  代碼演示：

  -- 變數：區域變數
  -- 宣告 declare
  -- 賦值
  CREATE PROCEDURE p2()
  BEGIN
      DECLARE stu_count INT DEFAULT 0;
      set stu_count := 100;
      SELECT COUNT(*) into stu_count FROM demo;
      SELECT stu_count;
  END;
  
  CALL p2();
  

if判斷

語法：

IF 條件1 THEN
	......
ELSEIF 條件2 THEN         -- 可選
	......
ELSE                     -- 可選
	......
END IF;

代碼演示：

CREATE PROCEDURE p1()
BEGIN
	DECLARE score INT DEFAULT 58;
    DECLARE `result` VARCHAR(10);
    
    IF score >= 85 THEN
    	SET `result` := '優秀';
    ELSEIF score >= 60 THEN
    	SET `result` := '及格';
    ELSE
    	SET `result` := '不及格';
    END IF;
    
    SELECT `result`;
END;

CALL p1();

引數

用法：

CREATE PROCEDURE 存盤程序名稱([IN/OUT/INOUT 引數名 引數型別])
BEGIN
  -- SQL陳述句
END;

代碼演示：

CREATE PROCEDURE p2(IN score INT, OUT `result` VARCHAR(10))
BEGIN
    IF score >= 85 THEN
    	SET `result` := '優秀';
    ELSEIF score >= 60 THEN
    	SET `result` := '及格';
    ELSE
    	SET `result` := '不及格';
    END IF;
END;

CALL p2(68, @result);
SELECT @result;


CREATE PROCEDURE p3(INOUT score INT)
BEGIN
	set score := score / 2;
END;

SET @score := 120;
CALL p3(@score);

SELECT @score;

case

• 語法一

  CASE case_value
     WHEN when_value1 THEN statement_list1
     [WHEN when_value2 THEN statement_list2]...
     [ELSE statement_list]
  END CASE;
  

• 語法二

  CASE
  	WHEN search_condition1 THEN statement_list1
  	[WHEN search_condition2 THEN statement_list2]
  	[ELSE statement_list]
  END CASE;
  

代碼演示：

CREATE PROCEDURE p4(in month int)
BEGIN
	declare result varchar(10);
    case
    	when month >= 1 and month <= 3 THEN
        	set result := '第一季度';
        when month >= 4 and month <= 6 THEN
        	set result := '第二季度';
        when month >= 7 and month <= 9 THEN
        	set result := '第三季度';
        when month >= 10 and month <= 12 THEN
        	set result := '第四季度';
        else
        	SET result := '非法引數';
        END case;

	SELECT concat('您輸入的月份為：', month, ' 所屬的季度為：', result);
END;

CALL p4(4);

回圈

• 回圈－while
  while回圈是有條件的回圈控制陳述句，滿足條件后在執行回圈體內的SQL陳述句，具體語法如下：

  #先判定條件，如果條件為true，則執行邏輯，否則不執行邏輯
  WHILE 條件 DO
      -- SQL邏輯
  END WHILE;
  

  代碼演示：

  #計算1到n的累加值
  CREATE procedure p5(in n int)
  BEGIN
      DECLARE total int DEFAULT 0;
      WHILE n > 0 do
          set total := total + n;
          set n := n - 1;
  END WHILE;
  SELECT total;
  END;
  
  call p5(10);
  

• 回圈－repeat
  repeat是有條件的回圈控制陳述句，直到滿足條件的時候退出回圈，具體語法如下：

  #先執行一次邏輯，然后判定是否滿足條件，如果滿足則退出，不滿足，繼續進行下一次回圈
  REPEAT
      -- SQL邏輯
      -- UNTIL 條件
  END REPEAT;
  

  代碼演示：

  #計算1到n的累加值
  CREATE procedure p6(in n int)
  BEGIN
      DECLARE total int DEFAULT 0;
      repeat
           set total := total + n;
           set n := n - 1;
       UNTIL n <= 0
       end REPEAT;
  SELECT total;
  END;
  
  call p6(10);
  

• 回圈－loop
  LOOP實作簡單的回圈，如果不在SQL邏輯中增加退出回圈的條件，可以用其來實作簡單的死回圈，LOOP可以配合以下兩個陳述句使用：

  • LEAVE：配合回圈使用，退出回圈，
  • ITERATE：必須用在回圈中，作用是跳過當前回圈剩下的陳述句，直接進入下一次回圈，

  [begin_label:] LOOP
      -- SQL邏輯
  END LOOP [end_label];
  

  LEAVE label; -- 退出指定標記的回圈體
  ITERATE label; -- 直接進入下一次回圈
  

  代碼演示：

  #計算1到n的累加值
  CREATE procedure p7(in n int)
  BEGIN
      DECLARE total int DEFAULT 0;
  
      `sum`:LOOP
  	    if n <= 0 THEN
      	    Leave `sum`;
          END if;
          set total := total + n;
          set n := n - 1;
      end LOOP sum;
  
      SELECT total;
  END;
  
  call p7(10);
  
  
  #計算1到n中的偶數的累加值
  CREATE procedure p8(in n int)
  BEGIN
      DECLARE total int DEFAULT 0;
      `sum`:LOOP
  	    if n <= 0 THEN
      	    Leave `sum`;
          END if;
  
          IF n % 2 = 1 THEN
      	    set n := n - 1;
      	    iterate `sum`;
          end if;
  
          set total := total + n;
          set n := n - 1;
      end LOOP sum;
  
      SELECT total;
  END;
  
  call p8(10);
  

游標

游標（CURSOR）是用來存盤查詢結果集的資料型別，在存盤程序和函式中可以使用游標對結果集進行回圈的處理，游標的使用包括游標的宣告、OPEN、FETCH 和 CLOSE，其語法分別如下：

• 宣告游標
  DECLARE 游標名稱 CURSOR FOR 查詢陳述句;
• 打開游標
  OPEN 游標名稱;
• 獲取游標記錄
  FETCH 游標名稱 INTO 變數[,變數];
• 關閉游標
  CLOSE 游標名稱

CREATE procedure p1(in uage int)
BEGIN
	DECLARE u_name varchar(32);
    DECLARE u_profession varchar(32);
    DECLARE u_cursor CURSOR for SELECT name, profession FROM `user` where age <= uage;
    -- DECLARE exit handler for sqlstate '02000' CLOSE u_cursor;
    DECLARE exit handler for Not FOUND CLOSE u_cursor;
    
    DROP TABLE if exists user_pro;
    
    CREATE TABLE user_pro(
    	id int PRIMARY key AUTO_INCREMENT,
      	name varchar(32),
      	profession varchar(32)
    );
    
    OPEN u_cursor;
    
    WHILE true do
    	FETCH u_cursor INTO u_name, u_profession;
        INSERT into user_pro VALUES(null, u_name, u_profession);
    end WHILE;
end;

CALL p1(25);

SELECT * from user_pro;

user表：

user_pro表：

條件處理程式-handler

條件處理程式（Handler）可以用來定義在流程控制結構執行程序中遇到問題時相應的處理步驟，具體語法如下：

DECLARE handler_action HANDLER FOR condition_value[,condition_value]... statement;

• handler_action：
  • CONTINUE：繼續執行當前程式
  • EXIT：終止執行當前程式
• condition_value：
  • SQLSTATE sqlstate_value：狀態碼，如02000；
  • SQLWARNING：所有以01開頭的SQLSTATE代碼的簡寫；
  • NOT FOUND：所有以02開頭的SQLSTATE代碼的簡寫；
  • SQLEXCEPTION：所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的簡寫

標籤：MySQL

上一篇：hadoop前期環境搭建

下一篇：返回列表