MariaDB/MySQL存儲過程和函數

来源:https://www.cnblogs.com/f-ck-need-u/archive/2018/04/03/8710324.html
-Advertisement-
Play Games

本文目錄：1.創建存儲過程、函數　1.1 存儲過程的IN、OUT和INOUT2.修改和刪除存儲過程、函數3.查看存儲過程、函數信息 在MySQL/MariaDB中，存儲過程(stored procedure)、存儲函數(stored function)、觸發器(trigger)、事件(event)統 ...


本文目錄：
1.創建存儲過程、函數
　1.1 存儲過程的IN、OUT和INOUT
2.修改和刪除存儲過程、函數
3.查看存儲過程、函數信息

在MySQL/MariaDB中，存儲過程(stored procedure)、存儲函數(stored function)、觸發器(trigger)、事件(event)統稱為存儲程式(stored programs)。此外，存儲過程和存儲函數還合稱為stored routines。

對於函數來說，除了存儲函數，還有用戶自定義函數(UDF,user defined function)，在MySQL/MariaDB中，用戶自定義函數是存儲函數的擴展，它像一個小程式一樣，需要編譯、安裝後才能運行。這和SQL Server對UDF的定義不同，倒是類似於SQL Server的CLR程式。

雖然存儲過程和存儲函數在功能實現上有些區別，但在使用方法上幾乎一致。

1.創建存儲過程、函數

在MySQL/MariaDB中創建存儲過程、函數的語法如下：其中OR REPLACE是MariaDB 10.1.3版本中才有的，MySQL中不支持OR REPLACE。

-- 創建存儲過程和函數
CREATE [OR REPLACE] PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
CREATE [OR REPLACE] FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type
func_parameter:
    param_name type

type:
    Any valid MySQL data type
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
    Valid SQL routine statement

在MySQL/MariaDB的存儲過程和函數中，允許存在DML和DDL語句。且存儲過程中還允許(存儲函數不允許)使用SQL事務類型的語句，例如提交commit。當然，肯定也支持嵌套其他存儲過程或函數。

存儲過程的參數有三種類型：IN、OUT和INOUT，下文將詳細分析這三種類型參數。

在創建和修改的語法中，需要說明的就是characteristic部分，這部分基本沒什麼用，但可以瞭解下它們是乾什麼的。

  • language sql：表示後面的body部分使用標準SQL語句編寫，這是預設的。該選項的作用是為了以後支持非SQL語句書寫存儲過程和函數的，例如SQL Server中就有使用.NET寫的CLR存儲過程、函數、觸發器等。但目前，這個還沒有任何意義。
  • [not] deterministic：deterministic的意思是確定的。這裡的意思是函數返回值是明確的，而非具有隨機性的值。例如，使用了隨機數，使用了now()這樣的函數等。not deterministic表示返回值是不確定的，這是系統預設值。當優化器知道函數返回值是確定值時，將選擇一個更優化的執行計劃。如果返回值是確定的，卻定義為not deterministic，則性能會降低，如果返回值是不確定的，卻定義為deterministic，則報錯。
  • {contains sql|no sql|reads sql data|modifies sql data}：這些是提供給伺服器的信息類子句，看上去是約束性語句，但MariaDB不會根據它們做任何檢查。也就是說，這些提示符只是給人看的，沒有任何作用。contains sql表示body不包含讀和寫數據的語句，例如SET和DO；no sql表示body不包含SQL語句；reads sql data表示body包含讀數據的語句，但不包含寫數據的語句，如SELECT。modifies sql data表示body包含寫數據的語句，如DELETE/UPDATE；如果沒有指定這些特征值，則使用預設值contains sql。
  • sql security：指明執行該程式時以誰的身份執行。definer表示執行時獲取創建者的許可權，invoker表示以調用者的身份執行，若調用該程式的用戶對程式中涉及的對象沒有對應的許可權則會執行失敗（如lisa用戶有執行存儲過程的許可權，但是沒有讀取存儲過程中涉及的表a的許可權，那麼執行存儲過程時因為讀表失敗而導致執行被拒絕）。預設是definer。
  • comment：程式的註釋信息。

當要調用存儲過程或函數時，可以使用call命令調用存儲過程，如call sp_name();；而函數則可以當作表達式一樣進行調用，例如使用select命令select func();，當作表達式賦值給變數set @a=func()。

需要註意的是，在MySQL/MariaDB中，因為語句的結束符是分號";"，在存儲過程或函數創建過程中直接使用分號會導致語句報錯。所以當存儲過程或存儲函數中包含需要使用分號的語句時，應在創建存儲過程或函數之前使用delimiter命令來暫時改變語句結束符，在創建完畢之後再改回結束符為分號";"。

例如：

delimiter $$
create or replace procedure proc()       -- procedure name
begin                                    -- procedure body
    select * from A;
end $$
delimiter ;
--調用存儲過程
call proc();

在上面的語句中，首先定義了結束符為$$；然後判斷了存儲過程proc()是否存在，存在則刪除，之後才開始創建存儲過程。這個存儲過程很簡單，只是一個select語句。創建語句結束之後，再次使用delimiter命令將結束符改回了分號";"。最後使用call命令進行了存儲過程的調用。

以下是一個函數的創建和使用示例：

delimiter $$
create or replace function func1()
    returns int
    return (select count(*) from t1);$$
delimiter ;
set @c=func1();
select func1(),@c;
+---------+------+
| func1() | @c   |
+---------+------+
|       6 |    6 |
+---------+------+

1.1 存儲過程的IN、OUT和INOUT

它們表示的是參數的類型。

IN參數類型表示將調用者給定的值傳遞給存儲過程。存儲過程可能會修改這個值，但是對於調用者來說，在存儲過程返回結果時，所做的修改是不可見的。

OUT參數類型表示將存儲過程的返回值傳遞給調用者。其初始值為NULL，當存儲過程返回時，這個值對調用者來說是可見的。

INOUT參數類型表示由調用者傳遞值給存儲過程，存儲過程可能會修改這個值，當存儲過程返回的時候，所做的修改對調用者來說是可見的。

對於每個OUT或INOUT類型的參數，當調用者在CALL語句中調用存儲過程時，所傳遞的每個用戶變數都可以在存儲過程返回的時候獲取其值。

預設每個參數都是IN。要指定其他類型的參數，可以在參數名前面使用關鍵字OUT或INOUT。

(1).IN參數類型。

IN參數類型是指調用者將某個值傳遞給存儲過程，存儲過程借用這個值來完成某些操作。

以下是IN類型參數的示例。

create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc1(min int,max int)
begin
    select * from t1 where t1.a >= min and t1.a <= max;
end$$
delimiter ;
call proc1(3,5);
+------+
| a    |
+------+
|    3 |
|    4 |
|    5 |
+------+

(2).OUT參數類型。

OUT參數類型是指存儲過程將某個值通過該參數返回給調用者。因此調用者必須傳遞一個用戶變數給存儲過程，用來記錄存儲過程OUT參數的值。這個用戶變數在傳遞給存儲過程之前，可以是一個已賦值的變數，但在傳遞給存儲過程時，將自動初始化為NULL值。

以下是OUT類型參數的示例。在此示例中，傳入@a給proc()，最後將count(*)賦值給out參數cnt，cnt代表的就是傳入參數@a。

delimiter $$
create or replace procedure proc2(out cnt int)
begin
    select count(*) into cnt from A;
end$$
delimiter ;
call proc1(@a);
select @a as a;
     a  
--------
       6

(3).INOUT參數類型。

INOUT參數類型指的是調用者和存儲過程之間傳遞的內容可以互相賦值。INOUT有兩個過程，一個是IN的過程，這個過程是將調用者指定的值傳遞給存儲過程，另一個是OUT的過程，這個過程是存儲過程將某個返回值返回給調用者。因此，調用者傳遞INOUT參數時，也必須傳遞一個用戶變數。但與OUT不同的是，INOUT的用戶變數有初始值，這個初始值會直接應用在存儲過程中。而OUT的用戶變數雖然也有初始值，但這個初始值會自動轉換為NULL。

以下是INOUT參數類型的示例。

/* procedure INOUT */
create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc3(INOUT cnt int,min int,max int)
begin
    if cnt <5 then   /* 直接用cnt這個INOUT參數來判斷大小，因為它繼承了傳遞時的值 */
        select count(*) into cnt from t1 where t1.a >=min and t1.a <=max;
    else
        select count(*) into cnt from t1;
    end if;
end$$
delimiter ;
set @a=3;
set @b=6;
call proc1(@a,3,5);
call proc1(@b,3,5);
select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    3 |    6 |
+------+------+

2.修改和刪除存儲過程、函數

可以使用alter語句修改存儲過程、函數，但alter語句只能修改characteristic部分，不支持對body部分和參數部分修改。若要修改它們，只能先刪除再創建。

-- 修改存儲過程和函數
ALTER {PROCEDURE | FUNCTION} proc_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

刪除存儲過程和函數的方式很簡單，直接使用drop命令，但要求有alter許可權。

drop {procedure|function} [if exists] sp_name

3.查看存儲過程、函數信息

查看存儲過程和函數的信息。

show {procedure|function} status like 'pattern'; -- 查看routine的基本信息
show create {procedure|function} proc_name;      -- 查看routine的創建語句
show {procedure|function} code routine_name;     -- 查看MariaDB內部是如何操作routine中各語句的

從information_schema.routines中查看存儲過程和函數的信息，這個比show status更詳細一點。

select * from information_schema.routines where routines_name='xxx'\G

這裡說明下show code功能，這個功能必須要在支持debug的MariaDB上才能使用，可以在編譯時使用選項"--with-debug"啟用該功能。

例如：

DELIMITER $$
CREATE PROCEDURE p1 ()
  BEGIN
    DECLARE fanta INT DEFAULT 55;
    DROP TABLE t2;
    LOOP
      INSERT INTO t3 VALUES (fanta);
      END LOOP;
  END$$
delimiter ;
SHOW PROCEDURE CODE p1;
+-----+----------------------------------------+
| Pos | Instruction                            |
+-----+----------------------------------------+
|   0 | set fanta@0 55                         |
|   1 | stmt 9 "DROP TABLE t2"                 |
|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
|   3 | jump 2                                 |
+-----+----------------------------------------+

其中第一列是從0開始的序列值，表示存儲過程中所執行的動作先後順序。第二列是mariadb要執行的動作，這些動作是基於存儲過程中的源語句進行設置的。

 

回到Linux系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到網站架構系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到資料庫系列文章大綱：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
轉載請註明出處：http://www.cnblogs.com/f-ck-need-u/p/8710324.html

註：若您覺得這篇文章還不錯請點擊右下角推薦，您的支持能激發作者更大的寫作熱情，非常感謝！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ubuntu 17.10 安裝後的應用軟體安裝
    僅僅是一些應用安裝，後續將陸續寫一寫開發工具的安裝和配置 [TOC] 安裝 sogou 拼音 1. sudo add apt repository ppa:fcitx team/nightly 2. sudo apt get update 3. sudo apt get install fcitx ...
  • Ubuntu深度學習環境搭建 tensorflow+pytorch
    目前電腦配置：Ubuntu 16.04 + GTX1080顯卡 配置深度學習環境，利用清華源安裝一個miniconda環境是非常好的選擇。尤其是今天發現conda install -c menpo opencv3 一句命令就可以順暢的安裝上opencv，之前自己裝的時候也遇到了很多錯誤。conda ...
  • CentOS安裝redis.tar.gz
    1. # cd /usr/local/src 2. # tar -zxvf redis-3.0.6.tar.gz 3. # cd redis-3.0.6 4.# make 5.# make PREFIX=/usr/local/redis install 6.cd /usr/local 有redis目 ...
  • MongoDB 副本集管理
    一、以單機模式啟動成員節點 有時候出於維護的需要，需要以單機模式啟動某個節點而不是一個副本集成員身份。 1）.首先查詢伺服器命令行參數 2）.關閉當前副本 3）.以單機模式啟動副本 這裡需要指定副本原先的db路徑，為了避免可能的其它連接這裡可以指定一個新的port啟動；也可以直接使用原先的配置文件啟 ...
  • zookeeper集群的搭建
    1、如果要實現文件的拖放就必須安裝lrzsz 2、解壓（看命令就可以） 3、刪除一些不必要的文件 4、修改配置文件 ...
  • YUM本地源製作與yum網路版倉庫
    1、修改本機上的YUM源配置文件，將源指向自己 cd /etc/yum.repos.d/ 備份原有的YUM源的配置文件 rename .repo .repo.bak * rename CentOS-Media.repo.bak CentOS-Media.repo * 修改配置文件 2、掛載光碟 在m ...
  • storm集群架構
    一、storm何許人也？Storm 是Twitter的一個開源框架。Storm一個分散式的、容錯的實時計算系統，它被托管在GitHub上，遵循 Eclipse Public License 1.0。Storm是由BackType開發的實時處理系統，BackType現在已在Twitter麾下。GitH ...
  • redis存入中文，取出來顯示不正常
    問題： 127.0.0.1:6379> set name 張泰松OK127.0.0.1:6379> get name"\xe5\xbc\xa0\xe6\xb3\xb0\xe6\x9d\xbe"set name 是中文，但 get 出來的卻不是中文 解決辦法： 127.0.0.1:6379> quit ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類