所有知識體系文章，GitHub已收錄，歡迎Star！再次感謝，愿你早日進入大廠！

GitHub地址： https://github.com/Ziphtracks/JavaLearningmanual

MySQL存盤程序

一、存盤程序

1.1 什么是存盤程序

存盤程序（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 陳述句集，它存盤在資料庫中，一次編譯后永久有效，用戶通過指定存盤程序的名字并給出引數（如果該存盤程序帶有引數）來執行它，存盤程序是資料庫中的一個重要物件，在資料量特別龐大的情況下利用存盤程序能達到倍速的效率提升

1.2 資料庫存盤程序程式

當我們了了解存盤程序是什么之后，就需要了解資料庫中存在的這三種型別的資料庫存盤型別程式，如下：

存盤程序： 存盤程序是最常見的存盤程式，存盤程序是能夠接受輸入和輸出引數并且能夠在請求時被執行的程式單元，

存盤函式： 存盤函式和存盤程序很相像，但是它的執行結果會回傳一個值，最重要的是存盤函式可以被用來充當標準的 SQL 陳述句，允許程式員有效的擴展 SQL 語言的能力，

觸發器： 觸發器是用來回應激活或者觸發資料庫行為事件的存盤程式，通常，觸發器用來作為資料庫操作語言的回應而被呼叫，觸發器可以被用來作為資料校驗和自動反向格式化，

注意：其他的資料庫提供了別的資料存盤程式，包括包和類，目前MySQL不提供這種結構，

1.3 為什么要使用存盤程式

雖然目前的開發中存盤程式我們使用的并不是很多，但是不一定就否認它，其實存盤程式會為我們使用和管理資料庫帶來了很多優勢：

使用存盤程式更加安全，

存盤程式提供了一種資料訪問的抽象機制，它能夠極大的改善你的代碼在底層資料結構演化程序中的易維護性，

存盤程式可以降低網路擁阻，因為屬于資料庫服務器的內部資料，這相比在網上傳輸資料要快的多，

存盤程式可以替多種使用不同構架的外圍應用實作共享的訪問例程，無論這些構架是基于資料庫服務器外部還是內部，

以資料為中心的邏輯可以被獨立的放置于存盤程式中，這樣可以為程式員帶來更高、更為獨特的資料庫編程體驗，

在某些情況下，使用存盤程式可以改善應用程式的可移植性，（在另外某些情況下，可移植性也會很差！）

這里我大致解釋一下上述幾種使用存盤程式的優勢：

我們要知道在Java語言中，我們使用資料庫與Java代碼結合持久化存盤需要引入JDBC來完成，會想到JDBC，我們是否還能想起SQL注入問題呢？雖然使用PreparedStatement解決SQL注入問題，那就真的是絕對安全嗎？不，它不是絕對安全的，

這時候分析一下資料庫與Java代碼的連接操作流程，在BS結構中，一般都是瀏覽器訪問服務器的，再由服務器發送SQL陳述句到資料庫，在資料庫中對SQL陳述句進行編譯運行，最后把結果通過服務器處理再回傳瀏覽器，在此操作程序中，瀏覽器對服務器每發送一次對資料庫操作的請求就會呼叫對應的SQL陳述句編譯和執行，這是一件十分浪費性能的事情，性能下降 了就說明對資料庫的操作效率低 了，

還有一種可能是，在這個程序中進行發送傳輸的SQL陳述句是對真實的庫表進行操作的SQL陳述句，如果在發送傳輸的程序中被攔截了，一些不法分子會根據他所攔截的SQL陳述句推斷出我們資料庫中的庫表結構，這是一個很大的安全隱患 ，

關于可維護性的提高，這里模擬一個場景，通常資料庫在公司中是由DBA來管理的，如果管理資料庫多年的DBA辭職了，此時資料庫會被下一任DBA來管理，這里時候問題來了，資料庫中這么多的資料和SQL陳述句顯然對下一任管理者不太友好，就算管理多年的DBA長時間不操作查看資料庫也會忘記點什么東西，所以，我們在需要引入存盤程式來進行SQL陳述句的統一撰寫和編譯，為維護提供了便利 ，（其實我覺得這個例子并不生動合理，但是為了大家能理解，請體諒！）

講了很多存盤程式的優勢演變程序，其核心就是： 需要將編譯好的一段或多段SQL陳述句放置在資料庫端的存盤程式中，以便解決以上問題并方便開發者直接呼叫，

二、存盤程序的使用步驟

2.1 存盤程序的開發思想

存盤程序時資料庫的一個重要的物件，可以封裝SQL陳述句集，可以用來完成一些較復雜的業務邏輯，并且可以入參（傳參）、出參（回傳引數），這里與Java中封裝方式十分相似，

而且創建時會預先編譯后保存，開發者后續的呼叫都不需要再次編譯，

2.2 存盤程序的優缺點

存盤程序使用的優缺點其實在1.3中的優勢中說到了，這里我簡單羅列一下存盤程序的優點與缺點，

優點：

在生產環境下，可以通過直接修改存盤程序的方式修改業務邏輯或bug，而不用重啟服務器，

執行速度快，存盤程序經過編譯之后會比單獨一條一條編譯執行要快很多，

減少網路傳輸流量，

便于開發者或DBA使用和維護，

在相同資料庫語法的情況下，改善了可移植性，

缺點：

程序化編程，復雜業務處理的維護成本高，

除錯不便，

因為不同資料庫語法不一致，不同資料庫之間可移植性差，

2.3 MySQL存盤程序的官方檔案

英語好或者有能力的小伙伴可以去參考一下官方檔案，如果不參考官方檔案，沒關系，我在下面也會詳細講述MySQL存盤程序的各個知識點，

1https://dev.mysql.com/doc/refman/5.6/en/preface.html

2.3 存盤程序的使用語法

1create PROCEDURE 程序名( in|out|inout 引數名 資料型別 , ...)
2begin
3    sql陳述句;
4end;
5call 程序名(引數值);

in是定義傳入引數的關鍵字，out是定義出參的關鍵字，inout是定義一個出入引數都可以的引數，如果括號內什么都不定義，就說明該存盤程序時一個無參的函式，在后面會有詳細的案例分析，

注意： SQL陳述句默認的結束符為;，所以在使用以上存盤程序時，會報1064的語法錯誤，我們可以使用DELIMITER關鍵字臨時宣告修改SQL陳述句的結束符為//，如下：

1-- 臨時定義結束符為"//"
2DELIMITER //
3create PROCEDURE 程序名( in|out 引數名 資料型別 , ...)
4begin
5    sql陳述句;
6end//
7-- 將結束符重新定義回結束符為";"
8DELIMITER ;

例如： 使用存盤程序來查詢員工的工資（無參）

注意： 如果在特殊的必要情況下，我們還可以通過delimiter關鍵字將;結束符宣告回來使用，在以下案例中我并沒有這樣將結束符宣告回原來的;，在此請大家注意~

為什么我在這里提供了drop（洗掉）呢？

是因為我們在使用的時候如果需要修改存盤程序中的內容，我們需要先洗掉現有的存盤程序后，再creat重新創建，

 1# 宣告結束符為//
 2delimiter //
 3
 4# 創建存盤程序(函式)
 5create procedure se()
 6begin
 7    select salary from employee;
 8end //
 9
10# 呼叫函式
11call se() //
12
13# 洗掉已存在存盤程序——se()函式
14drop procedure if exists se //

三、存盤程序的變數和賦值

3.1 區域變數

宣告區域變數語法： declare var_name type [default var_value];

賦值語法：

注意： 區域變數的定義，在begin/end塊中有效，

使用set為引數賦值

 1# set賦值
 2
 3# 宣告結束符為//
 4delimiter //
 5
 6# 創建存盤程序
 7create procedure val_set()
 8begin
 9    # 宣告一個默認值為unknown的val_name區域變數
10    declare val_name varchar(32) default 'unknown';
11    # 為區域變數賦值
12    set val_name = 'Centi';
13    # 查詢區域變數
14    select val_name;
15end //
16
17# 呼叫函式
18call val_set() //
19

使用into接收引數

 1delimiter //
 2create procedure val_into()
 3begin
 4    # 定義兩個變數存放name和age
 5    declare val_name varchar(32) default 'unknown';
 6    declare val_age int;
 7    # 查詢表中id為1的name和age并放在定義的兩個變數中
 8    select name,age into val_name,val_age from employee where id = 1;
 9    # 查詢兩個變數
10    select val_name,val_age;
11end //
12
13call val_into() //
14

3.2 用戶變數

用戶自定義用戶變數，當前會話（連接）有效，與Java中的成員變數相似，

語法： @val_name

注意： 該用戶變數不需要提前宣告，使用即為宣告，

 1delimiter //
 2create procedure val_user()
 3begin
 4    # 為用戶變數賦值
 5    set @val_name = 'Lacy';
 6end //
 7
 8# 呼叫函式
 9call val_user() //
10
11# 查詢該用戶變數
12select @val_name //

3.3 會話變數

會話變數是由系統提供的，只在當前會話（連接）中有效，

語法： @@session.val_name

1# 查看所有會話變數
2show session variables;
3# 查看指定的會話變數
4select @@session.val_name;
5# 修改指定的會話變數
6set @@session.val_name = 0;

這里我獲取了一下所有的會話變數，大概有500潭訓話變數的記錄，等我們深入學習MySQL后，了解了各個會話變數值的作用，可以根據需求和場景來修改會話變數值，

1delimiter //
2create procedure val_session()
3begin
4    # 查看會話變數
5    show session variables;
6end //
7
8call val_session() //
9

3.4 全域變數

全域變數由系統提供，整個MySQL服務器內有效，

語法： @@global.val_name

1# 查看全域變數中變數名有char的記錄
2show global variables like '%char%' //
3# 查看全域變數character_set_client的值
4select @@global.character_set_client //

3.5 入參出參

入參出參的語法我們在文章開頭已經提過了，但是沒有演示，在這里我將演示一下入參出參的使用，

語法： in|out|inout 引數名資料型別 , ...

in定義出參；out定義入參；inout定義出參和入參，

出參in

使用出參in時，就是需要我們傳入引數，在這里可以對參入的引數加以改變，簡單來說in只負責傳入引數到存盤程序中，類似Java中的形參，

 1delimiter //
 2create procedure val_in(in val_name varchar(32))
 3begin
 4    # 使用用戶變數出參(為用戶變數賦引數值)
 5    set @val_name1 = val_name;
 6end //
 7
 8# 呼叫函式
 9call val_in('DK') //
10
11# 查詢該用戶變數
12select @val_name1 //

入參out

在使用out時，需要傳入一個引數，而這個引數相當于是回傳值，可以通過呼叫、接收來獲取這個引數的內容，簡單來說out只負責作回傳值，

 1delimiter //
 2# 創建一個入參和出參的存盤程序
 3create procedure val_out(in val_id int,out val_name varchar(32))
 4begin
 5    # 傳入引數val_id查詢員工回傳name值(查詢出的name值用出參接收并回傳)
 6    select name into val_name from employee where id = val_id;
 7end //
 8
 9# 呼叫函式傳入引數并宣告傳入一個用戶變數
10call val_out(1, @n) //
11
12# 查詢用戶變數
13select @n //

入參出參inout

inout關鍵字，就是把in和out合并成了一個關鍵字使用，被關鍵字修飾的引數既可以出參也可以入參，

 1delimiter //
 2create procedure val_inout(in val_name varchar(32), inout val_age int)
 3begin
 4    # 宣告一個a變數
 5    declare a int;
 6    # 將傳入的引數賦值給a變數
 7    set a = val_age;
 8    # 通過name查詢age并回傳val_age
 9    select age into val_age from employee where name = val_name;
10    # 將傳入的a與-和查詢age結果字串做拼接并查詢出來(concat——拼接字串)
11    select concat(a, '-', val_age);
12end //
13
14# 宣告一個用戶變數并賦予引數為40
15set @ages = '40' //
16# 呼叫函式并傳入引數值
17call val_inout('Ziph', @ages) //
18# 執行結果
19# 40-18

四、存盤程序中的流程控制

4.1 if 條件判斷（推薦）

擴展： timestampdiff(unit, exp1, exp2)為exp2 - exp1得到的差值，而單位是unit，（常用于日期）

擴展例子： select timestampdiff(year,’2020-6-6‘,now()) from emp e where id = 1;

解釋擴展例子： 查詢員工表中id為1員工的年齡，exp2就可以為該員工的出生年月日，并以年為單位計算，

語法：

1IF 條件判斷 THEN 結果
2    [ELSEIF 條件判斷 THEN 結果] ...
3    [ELSE 結果]
4END IF

舉例： 傳入所查詢的id引數查詢工資標準（s<=6000為低工資標準；6000=15000為高工資標準）

 1delimiter //
 2create procedure s_sql(in val_id int)
 3begin
 4    # 宣告一個區域變數result存放工資標準結果
 5    declare result varchar(32);
 6    # 宣告一個區域變數存放查詢得到的工資
 7    declare s double;
 8    # 根據入參id查詢工資
 9    select salary into s from employee where id = val_id;
10    # if判斷的使用
11    if s <= 6000 then
12        set result = '低工資標準';
13    elseif s <= 10000 then
14        set result = '中工資標準';
15    elseif s <= 15000 then
16        set result = '中上工資標準';
17    else
18        set result = '高工資標準';
19    end if;
20    # 查詢工資標準結果
21    select result;
22end //
23
24# 呼叫函式，傳入引數
25call s_sql(1);

4.2 case條件判斷

關于case陳述句，不僅僅在存盤程序中可以使用，MySQL基礎查詢陳述句中也有用到過，相當于是Java中的switch陳述句，

語法：

 1# 語法一
 2CASE case_value
 3    WHEN when_value THEN 結果
 4    [WHEN when_value THEN 結果] ...
 5    [ELSE 結果]
 6END CASE
 7
 8# 語法二(推薦語法)
 9CASE
10    WHEN 條件判斷 THEN 結果
11    [WHEN 條件判斷 THEN 結果] ...
12    [ELSE 結果]
13END CASE

舉例：

 1# 語法一
 2delimiter //
 3create procedure s_case(in val_id int)
 4begin
 5    # 宣告一個區域變數result存放工資標準結果
 6    declare result varchar(32);
 7    # 宣告一個區域變數存放查詢得到的工資
 8    declare s double;
 9    # 根據入參id查詢工資
10    select salary into s from employee where id = val_id;
11    case s
12        when 6000 then set result = '低工資標準';
13        when 10000 then set result = '中工資標準';
14        when 15000 then set result = '中上工資標準';
15        else set result = '高工資標準';
16    end case;
17    select result;
18end //
19
20call s_case(1);
21
22# 語法二(推薦)
23delimiter //
24create procedure s_case(in val_id int)
25begin
26    # 宣告一個區域變數result存放工資標準結果
27    declare result varchar(32);
28    # 宣告一個區域變數存放查詢得到的工資
29    declare s double;
30    # 根據入參id查詢工資
31    select salary into s from employee where id = val_id;
32    case
33        when s <= 6000 then set result = '低工資標準';
34        when s <= 10000 then set result = '中工資標準';
35        when s <= 15000 then set result = '中上工資標準';
36        else set result = '高工資標準';
37    end case;
38    select result;
39end //
40
41call s_case(1);

4.3 loop回圈

loop為死回圈，需要手動退出回圈，我們可以使用leave來退出回圈

可以把leave看成Java中的break；與之對應的，就有iterate（繼續回圈）也可以看成Java的continue

語法：

1[別名:] LOOP
2    回圈陳述句
3END LOOP [別名]

注意：別名和別名控制的是同一個標簽，

示例1： 回圈列印1~10（leave控制回圈的退出）

注意：該loop回圈為死回圈，我們查的1~10數字是i，在死回圈中設定了當大于等于10時停止回圈，也就是說先后執行了10次該回圈內的內容，結果查詢了10次，生成了10個結果（1~10），

 1delimiter //
 2create procedure s_loop()
 3begin
 4    # 宣告計數器
 5    declare i int default 1;
 6    # 開始回圈
 7    num:
 8    loop
 9        # 查詢計數器記錄的值
10        select i;
11        # 判斷大于等于停止計數
12        if i >= 10 then
13            leave num;
14        end if;
15        # 計數器自增1
16        set i = i + 1;
17    # 結束回圈
18    end loop num;
19end //
20
21call s_loop();

列印結果：

示例2： 回圈列印1~10（iterate和leave控制回圈）

注意：這里我們使用字串拼接計數器結果，而條件如果用iterate就必須時 i < 10 了！

 1delimiter //
 2create procedure s_loop1()
 3begin
 4    # 宣告變數i計數器
 5    declare i int default 1;
 6    # 宣告字串容器
 7    declare str varchar(256) default '1';
 8    # 開始回圈
 9    num:
10    loop
11        # 計數器自增1
12        set i = i + 1;
13        # 字串容器拼接計數器結果
14        set str = concat(str, '-', i);
15        # 計數器i如果小于10就繼續執行
16        if i < 10 then
17            iterate num;
18        end if;
19        # 計數器i如果大于10就停止回圈
20        leave num;
21    # 停止回圈
22    end loop num;
23    # 查詢字串容器的拼接結果
24    select str;
25end //
26
27call s_loop1();

4.4 repeat回圈

repeat回圈類似Java中的do while回圈，直到條件不滿足才會結束回圈，

語法：

1[別名:] REPEAT
2    回圈陳述句
3UNTIL 條件
4END REPEAT [別名]

示例： 回圈列印1~10

 1delimiter //
 2create procedure s_repeat()
 3begin
 4    declare i int default 1;
 5    declare str varchar(256) default '1';
 6    # 開始repeat回圈
 7    num:
 8    repeat
 9        set i = i + 1;
10        set str = concat(str, '-', i);
11    # until 結束條件
12    # end repeat 結束num 結束repeat回圈
13    until i >= 10 end repeat num;
14    # 查詢字串拼接結果
15    select str;
16end //
17
18call s_repeat();

4.5 while回圈

while回圈就與Java中的while回圈很相似了，

語法：

1[別名] WHILE 條件 DO
2    回圈陳述句
3END WHILE [別名]

示例： 回圈列印1~10

 1delimiter //
 2create procedure s_while()
 3begin
 4    declare i int default 1;
 5    declare str varchar(256) default '1';
 6    # 開始while回圈
 7    num:
 8    # 指定while回圈結束條件
 9    while i < 10 do
10        set i = i + 1;
11        set str = concat(str, '+', i);
12    # while回圈結束
13    end while num;
14    # 查詢while回圈拼接字串
15    select str;
16end //
17
18call s_while();

4.6 流程控制陳述句（繼續、結束）

至于流程控制的繼續和結束，我們在前面已經使用過了，這里再列舉一下，

leave：與Java中break;相似

1leave 標簽;

iterate：與Java中的continue;相似

1iterate 標簽;

五、游標與handler

5.1 游標

游標是可以得到某一個結果集并逐行處理資料，游標的逐行操作，導致了游標很少被使用！

語法：

1DECLARE 游標名 CURSOR FOR 查詢陳述句
2-- 打開語法
3OPEN 游標名
4-- 取值語法
5FETCH 游標名 INTO var_name [, var_name] ...
6-- 關閉語法
7CLOSE 游標名

了解了游標的語法，我們開始使用游標，如下：

示例： 使用游標查詢id、name和salary，

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    # 宣告游標
 9    declare emp_flag cursor for
10    select id, name, salary from employee;
11
12    # 打開
13    open emp_flag;
14
15    # 取值
16    fetch emp_flag into val_id, val_name, val_salary;
17
18    # 關閉
19    close emp_flag;
20
21    select val_id, val_name, val_salary;
22end //
23
24call f();

執行結果：

因為游標逐行操作的特點，導致我們只能使用游標來查詢一行記錄，怎么改善代碼才可以實作查詢所有記錄呢？聰明的小伙伴想到了使用回圈，對，我們試試使用一下回圈，

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    # 宣告游標
 9    declare emp_flag cursor for
10    select id, name, salary from employee;
11
12    # 打開
13    open emp_flag;
14
15    # 使用回圈取值
16    c:loop
17        # 取值
18        fetch emp_flag into val_id, val_name, val_salary;
19    end loop;
20
21    # 關閉
22    close emp_flag;
23
24    select val_id, val_name, val_salary;
25end //
26
27call f();

我們使用回圈之后，發現有一個問題，因為回圈是死回圈，我們不加結束回圈的條件，游標會一直查詢記錄，當查到沒有的記錄的時候，就會拋出例外1329：未獲取到選擇處理的行數，

如果我們想辦法指定結束回圈的條件該怎么做呢？

這時候可以宣告一個boolean型別的標記，如果為true時則查詢結果集，為false時則結束回圈，

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    # 宣告flag標記
 9    declare flag boolean default true;
10
11    # 宣告游標
12    declare emp_flag cursor for
13    select id, name, salary from employee;
14
15    # 打開
16    open emp_flag;
17
18    # 使用回圈取值
19    c:loop
20        fetch emp_flag into val_id, val_name, val_salary;
21        # 如果標記為true則查詢結果集
22        if flag then
23            select val_id, val_name, val_salary;
24        # 如果標記為false則證明結果集查詢完畢，停止死回圈
25        else
26            leave c;
27        end if;
28    end loop;
29
30    # 關閉
31    close emp_flag;
32
33    select val_id, val_name, val_salary;
34end //
35
36call f();

上述代碼你會發現并沒有寫完，它留下了一個很嚴肅的問題，當flag = false時候可以結束回圈，但是什么時候才讓flag為false啊？

于是，MySQL為我們提供了一個handler句柄，它可以幫我們解決此疑惑，

handler句柄語法： declare continue handler for 例外 set flag = false;

handler句柄可以用來捕獲例外，也就是說在這個場景中當捕獲到1329：未獲取到選擇處理的行數時，就將flag標記的值改為false，這樣使用handler句柄就解決了結束回圈的難題，讓我們來試試吧！

終極版示例： 解決了多行查詢以及結束回圈問題，

 1delimiter //
 2create procedure f()
 3begin
 4    declare val_id int;
 5    declare val_name varchar(32);
 6    declare val_salary double;
 7
 8    # 宣告flag標記
 9    declare flag boolean default true;
10
11    # 宣告游標
12    declare emp_flag cursor for
13    select id, name, salary from employee;
14
15    # 使用handler句柄來解決結束回圈問題
16    declare continue handler for 1329 set flag = false;
17
18    # 打開
19    open emp_flag;
20
21    # 使用回圈取值
22    c:loop
23        fetch emp_flag into val_id, val_name, val_salary;
24        # 如果標記為true則查詢結果集
25        if flag then
26            select val_id, val_name, val_salary;
27        # 如果標記為false則證明結果集查詢完畢，停止死回圈
28        else
29            leave c;
30        end if;
31    end loop;
32
33    # 關閉
34    close emp_flag;
35
36    select val_id, val_name, val_salary;
37end //
38
39call f();

執行結果：

在執行結果中，可以看出查詢結果以多次查詢的形式，分布顯示到了每一個查詢結果視窗中，

注意： 在語法中，變數宣告、游標宣告、handler宣告是必須按照先后順序書寫的，否則創建存盤程序出錯，

5.2 handler句柄

語法：

1DECLARE handler操作 HANDLER
2    FOR 情況串列...(比如：例外錯誤情況)
3    操作陳述句

注意：例外情況可以寫例外錯誤碼、例外別名或SQLSTATE碼，

handler操作：

CONTINUE： 繼續

EXIT： 退出

UNDO： 撤銷

例外情況串列：

mysql_error_code

SQLSTATE [VALUE] sqlstate_value

condition_name

SQLWARNING

NOT FOUND

SQLEXCEPTION

注意： MySQL中各種例外情況代碼、錯誤碼、別名和SQLSTATEM碼可參考官方檔案：

https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html

寫法示例：

1    DECLARE exit HANDLER FOR SQLSTATE '3D000' set flag = false;
2    DECLARE continue HANDLER FOR 1050 set flag = false;
3    DECLARE continue HANDLER FOR not found set flag = false;

六、回圈創建表

需求： 創建下個月的每天對應的表，創建的表格式為：comp_2020_06_01、comp_2020_06_02、...

描述： 我們需要用某個表記錄很多資料，比如記錄某某用戶的搜索、購買行為（注意，此處是假設用資料庫保存），當每天記錄較多時，如果把所有資料都記錄到一張表中太龐大，需要分表，我們的要求是，每天一張表，存當天的統計資料，就要求提前生產這些表——每月月底創建下一個月每天的表！

預編譯： PREPARE 資料庫物件名 FROM 引數名

執行： EXECUTE 資料庫物件名 [USING @var_name [, @var_name] ...]

通過資料庫物件創建或洗掉表： {DEALLOCATE | DROP} PREPARE 資料庫物件名

關于時間處理的陳述句：

1-- EXTRACT(unit FROM date)               截取時間的指定位置值
2-- DATE_ADD(date,INTERVAL expr unit)     日期運算
3-- LAST_DAY(date)                          獲取日期的最后一天
4-- YEAR(date)                             回傳日期中的年
5-- MONTH(date)                            回傳日期的月
6-- DAYOFMONTH(date)                        回傳日

代碼：

 1-- 思路：回圈構建表名 comp_2020_06_01 到 comp_2020_06_30；并執行create陳述句，
 2delimiter //
 3create procedure sp_create_table()
 4begin
 5    # 宣告需要拼接表名的下一個月的年、月、日
 6    declare next_year int;
 7    declare next_month int;
 8    declare next_month_day int;
 9
10    # 宣告下一個月的月和日的字串
11    declare next_month_str char(2);
12    declare next_month_day_str char(2);
13
14    # 宣告需要處理每天的表名
15    declare table_name_str char(10);
16
17    # 宣告需要拼接的1
18    declare t_index int default 1;
19    # declare create_table_sql varchar(200);
20
21    # 獲取下個月的年份
22    set next_year = year(date_add(now(),INTERVAL 1 month));
23    # 獲取下個月是幾月 
24    set next_month = month(date_add(now(),INTERVAL 1 month));
25    # 下個月最后一天是幾號
26    set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
27
28    # 如果下一個月月份小于10，就在月份的前面拼接一個0
29    if next_month < 10
30        then set next_month_str = concat('0',next_month);
31    else
32        # 如果月份大于10，不做任何操作
33        set next_month_str = concat('',next_month);
34    end if;
35
36    # 回圈操作(下個月的日大于等于1回圈開始回圈)
37    while t_index <= next_month_day do
38
39        # 如果t_index小于10就在前面拼接0
40        if (t_index < 10)
41            then set next_month_day_str = concat('0',t_index);
42        else
43            # 如果t_index大于10不做任何操作
44            set next_month_day_str = concat('',t_index);
45        end if;
46
47        # 拼接標命字串
48        set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
49        # 拼接create sql陳述句
50        set @create_table_sql = concat(
51                    'create table comp_',
52                    table_name_str,
53                    '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
54        # 預編譯
55        # 注意：FROM后面不能使用區域變數！
56        prepare create_table_stmt FROM @create_table_sql;
57        # 執行
58        execute create_table_stmt;
59        # 創建表
60        DEALLOCATE prepare create_table_stmt;
61
62        # t_index自增1
63        set t_index = t_index + 1;
64
65    end while;  
66end//
67
68# 呼叫函式
69call sp_create_table()

七、其他

7.1 characteristic

在MySQL存盤程序中，如果沒有顯示的定義characteristic，它會隱式的定義一系列特性的默認值來創建存盤程序，

LANGUAGE SQL

存盤程序語言，默認是sql，說明存盤程序中使用的是sql語言撰寫的，暫時只支持sql，后續可能會支持其他語言

NOT DETERMINISTIC

是否確定性的輸入就是確定性的輸出，默認是NOT DETERMINISTIC，只對于同樣的輸入，輸出也是一樣的，當前這個值還沒有使用

CONTAINS SQL

提供子程式使用資料的內在資訊，這些特征值目前提供給服務器，并沒有根據這些特征值來約束程序實際使用資料的情況，有以下選擇：

CONTAINS SQL表示子程式不包含讀或者寫資料的陳述句

NO SQL 表示子程式不包含sql

READS SQL DATA 表示子程式包含讀資料的陳述句，但是不包含寫資料的陳述句

MODIFIES SQL DATA 表示子程式包含寫資料的陳述句，

SQL SECURITY DEFINER

MySQL存盤程序是通過指定SQL SECURITY子句指定執行存盤程序的實際用戶，所以次值用來指定存盤程序是使用創建者的許可來執行，還是執行者的許可來執行，默認值是DEFINER

DEFINER 創建者的身份來呼叫，對于當前用戶來說：如果執行存盤程序的權限，且創建者有訪問表的權限，當前用戶可以成功執行程序的呼叫的

INVOKER 呼叫者的身份來執行，對于當前用戶來說：如果執行存盤程序的權限，以當前身份去訪問表，如果當前身份沒有訪問表的權限，即便是有執行程序的權限，仍然是無法成功執行程序的呼叫的，

COMMENT ''

存盤程序的注釋性資訊寫在COMMENT里面，這里只能是單行文本，多行文本會被移除到回車換行等

7.2 死回圈處理

如有死回圈處理，可以通過下面的命令查看并殺死（結束）

1show processlist;
2kill id;

7.3 select陳述句中書寫case

1select 
2    case
3        when 條件判斷 then 結果
4        when 條件判斷 then 結果
5        else 結果
6    end 別名,
7    *
8from 表名;

7.4 復制表和資料

1CREATE TABLE dept SELECT * FROM procedure_demo.dept;
2CREATE TABLE emp SELECT * FROM procedure_demo.emp;
3CREATE TABLE salgrade SELECT * FROM procedure_demo.salgrade;

7.5 臨時表

 1create temporary table 表名(
 2　　欄位名 型別 [約束],
 3　　name varchar(20) 
 4)Engine=InnoDB default charset utf8;
 5
 6-- 需求：按照部門名稱查詢員工，通過select查看員工的編號、姓名、薪資，（注意，此處僅僅演示游標用法）
 7delimiter $$
 8create procedure sp_create_table02(in dept_name varchar(32))
 9begin
10    declare emp_no int;
11    declare emp_name varchar(32);
12    declare emp_sal decimal(7,2);
13    declare exit_flag int default 0;
14
15    declare emp_cursor cursor for
16        select e.empno,e.ename,e.sal
17        from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name;
18
19    declare continue handler for not found set exit_flag = 1;
20
21    -- 創建臨時表收集資料
22    CREATE temporary TABLE `temp_table_emp` (
23        `empno` INT(11) NOT NULL COMMENT '員工編號',
24        `ename` VARCHAR(32) NULL COMMENT '員工姓名' COLLATE 'utf8_general_ci',
25        `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪資',
26        PRIMARY KEY (`empno`) USING BTREE
27    )
28    COLLATE='utf8_general_ci'
29    ENGINE=InnoDB;  
30
31    open emp_cursor;
32
33    c_loop:loop
34        fetch emp_cursor into emp_no,emp_name,emp_sal;
35
36
37        if exit_flag != 1 then
38            insert into temp_table_emp values(emp_no,emp_name,emp_sal); 
39        else
40            leave c_loop;
41        end if;
42
43    end loop c_loop;
44
45    select * from temp_table_emp;
46
47    select @sex_res; -- 僅僅是看一下會不會執行到
48    close emp_cursor;
49
50end$$
51
52call sp_create_table02('RESEARCH');

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/29416.html

標籤：MySQL

上一篇：MySQL 操作已存在的表（ALTER TABLE）

下一篇：Mysql大資料量分頁優化

面試官突然問我MySQL存盤程序，我竟然連基礎都不會！（詳細）

MySQL存盤程序

一、存盤程序

1.1 什么是存盤程序

1.2 資料庫存盤程序程式

1.3 為什么要使用存盤程式

二、存盤程序的使用步驟

2.1 存盤程序的開發思想

2.2 存盤程序的優缺點

2.3 MySQL存盤程序的官方檔案

2.3 存盤程序的使用語法

三、存盤程序的變數和賦值

3.1 區域變數

3.2 用戶變數

3.3 會話變數

3.4 全域變數

3.5 入參出參

四、存盤程序中的流程控制

4.1 if 條件判斷（推薦）

4.2 case條件判斷

4.3 loop回圈

4.4 repeat回圈

4.5 while回圈

4.6 流程控制陳述句（繼續、結束）

五、游標與handler

5.1 游標

5.2 handler句柄

六、回圈創建表

七、其他

7.1 characteristic

7.2 死回圈處理

7.3 select陳述句中書寫case

7.4 復制表和資料

7.5 臨時表