引言

存儲過程是 MySQL 中強大的工具，允許用戶封裝復雜的數(shù)據(jù)庫操作，提高可重用性和性能。除了基本特性外，MySQL 還提供了幾個進階特性，進一步增強了存儲過程的功能性。這些特性包括自定義函數(shù)、表值參數(shù)和游標。本文將深入探討這些特性的用法和優(yōu)勢。

自定義函數(shù)

自定義函數(shù)允許用戶在存儲過程中創(chuàng)建自己的函數(shù)，以便重復使用通用邏輯。這些函數(shù)可以接受參數(shù)並返回值。自定義函數(shù)的語法如下：```sqlCREATE FUNCTION function_name (parameter_list) RETURNS return_typeASBEGIN-- 函數(shù)體END```例如，以下自定義函數(shù)計算兩個數(shù)字的加法： sqlCREATE FUNCTION add_numbers (a INT, b INT) RETURNS INTASBEGINRETURN a + b;END```自定義函數(shù)可以在存儲過程中使用，就像內(nèi)置函數(shù)一樣：```sqlSELECT add_numbers(5, 10);```

表值參數(shù)

表值參數(shù)允許存儲過程接受或返回一個表作為參數(shù)。這對於處理大量數(shù)據(jù)或執(zhí)行復雜查詢非常有用。表值參數(shù)的語法如下：```sqlCREATE PROCEDURE procedure_name (IN|OUT|INOUT table_name TABLE)```例如，以下存儲過程使用表值參數(shù)將客戶信息批量插入數(shù)據(jù)庫：```sqlCREATE PROCEDURE insert_customers (IN customers TABLE)ASBEGININSERT INTO customer (name, age, address)SELECT name, age, addressFROM customers;END```

游標

游標是用于遍歷結(jié)果集的機制。它們允許用戶逐行訪問數(shù)據(jù)，并根據(jù)需要執(zhí)行操作。游標的語法如下：```sqlDECLARE cursor_name CURSOR FOR SELECT_statement;OPEN cursor_name;FETCH cursor_name INTO variable_list;CLOSE cursor_name;```例如，以下存儲過程使用游標來打印一個表中的所有客戶信息：```sqlCREATE PROCEDURE print_customersASBEGINDECLARE customer_cursor CURSOR FOR SELECT name, age, address FROM customer;OPEN customer_cursor;FETCH customer_cursor INTO customer_name, customer_age, customer_address;WHILE customer_name IS NOT NULL DOPRINT CONCAT(customer_name, ', ', customer_age, ', ', customer_address);FETCH customer_cursor INTO customer_name, customer_age, customer_address;END WHILE;CLOSE customer_cursor;END```

優(yōu)勢

使用這些進階特性可以帶來以下優(yōu)勢：代碼重用：自定義函數(shù)允許您將通用邏輯封裝為可重用的模塊。批量處理：表值參數(shù)可用于對大量數(shù)據(jù)執(zhí)行高效操作。靈活的遍歷：游標提供了靈活的方式來遍歷結(jié)果集並逐行處理數(shù)據(jù)。提高性能：通過使用這些特性，您可以優(yōu)化存儲過程，減少執(zhí)行時間。

結(jié)論

MySQL 存儲過程中的自定義函數(shù)、表值參數(shù)和游標是強大的工具，可顯著增強存儲過程的功能性。通過使用這些特性，您可以創(chuàng)建更靈活、更有效率的數(shù)據(jù)庫解決方案。

---

mysql 與oracle中的存儲過程及函數(shù)有什么區(qū)別，盡可能詳細哦

本質(zhì)上沒區(qū)別。 只是函數(shù)有如：只能返回一個變量的限制。 而存儲過程可以返回多個。 而函數(shù)是可以嵌入在sql中使用的,可以在select中調(diào)用，而存儲過程不行。 執(zhí)行的本質(zhì)都一樣。 函數(shù)限制比較多，比如不能用臨時表，只能用表變量．還有一些函數(shù)都不可用等等．而存儲過程的限制相對就比較少由于我現(xiàn)在基本上是DBA的工作，因此平時也看一些數(shù)據(jù)庫方面的書籍。 但是我一直對存儲過程和函數(shù)之間的區(qū)別掌握不透。 我向來認為存儲過程可以實現(xiàn)的操作，函數(shù)也一樣可以實現(xiàn)。 最近，剛好大學的老師給我們上SQL-Server的課程，我對這個問題的疑惑終于慢慢解開。 今天晚上順便看了些網(wǎng)上的資料，覺得以下分析比較合理：1. 一般來說，存儲過程實現(xiàn)的功能要復雜一點，而函數(shù)的實現(xiàn)的功能針對性比較強。 2. 對于存儲過程來說可以返回參數(shù)，而函數(shù)只能返回值或者表對象。 3. 存儲過程一般是作為一個獨立的部分來執(zhí)行，而函數(shù)可以作為查詢語句的一個部分來調(diào)用，由于函數(shù)可以返回一個表對象，因此它可以在查詢語句中位于FROM關(guān)鍵字的后面。 4. 當存儲過程和函數(shù)被執(zhí)行的時候，SQL Manager會到procedure cache中去取相應(yīng)的查詢語句，如果在procedure cache里沒有相應(yīng)的查詢語句，SQL Manager就會對存儲過程和函數(shù)進行編譯。 Procedure cache中保存的是執(zhí)行計劃 (execution plan) ，當編譯好之后就執(zhí)行procedure cache中的execution plan，之后SQL SERVER會根據(jù)每個execution plan的實際情況來考慮是否要在cache中保存這個plan，評判的標準一個是這個execution plan可能被使用的頻率；其次是生成這個plan的代價，也就是編譯的耗時。 保存在cache中的plan在下次執(zhí)行時就不用再編譯了。 存儲過程和用戶自定義函數(shù)具體的區(qū)別存儲過程存儲過程可以使得對數(shù)據(jù)庫的管理、以及顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作容易得多。 存儲過程是 SQL 語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。 存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強大的編程功能。 存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。 它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值。 可以出于任何使用 SQL 語句的目的來使用存儲過程，它具有以下優(yōu)點：可以在單個存儲過程中執(zhí)行一系列 SQL 語句。 可以從自己的存儲過程內(nèi)引用其它存儲過程，這可以簡化一系列復雜語句。 存儲過程在創(chuàng)建時即在服務(wù)器上進行編譯，所以執(zhí)行起來比單個 SQL 語句快。 用戶定義函數(shù)函數(shù)是由一個或多個 Transact-SQL 語句組成的子程序，可用于封裝代碼以便重新使用。 Microsoft? SQL Server? 2000 并不將用戶限制在定義為 Transact-SQL 語言一部分的內(nèi)置函數(shù)上，而是允許用戶創(chuàng)建自己的用戶定義函數(shù)。 可使用 CREATE FUNCTION 語句創(chuàng)建、使用 ALTER FUNCTION 語句修改、以及使用 DROP FUNCTION 語句除去用戶定義函數(shù)。 每個完全合法的用戶定義函數(shù)名 (database___name) 必須唯一。 必須被授予 CREATE FUNCTION 權(quán)限才能創(chuàng)建、修改或除去用戶定義函數(shù)。 不是所有者的用戶在 Transact-SQL 語句中使用某個函數(shù)之前，必須先給此用戶授予該函數(shù)的適當權(quán)限。 若要創(chuàng)建或更改在 CHECK 約束、DEFAULT 子句或計算列定義中引用用戶定義函數(shù)的表，還必須具有函數(shù)的 REFERENCES 權(quán)限。 函數(shù)中的有效語句類型包括：DECLARE 語句，該語句可用于定義函數(shù)局部的數(shù)據(jù)變量和游標。 為函數(shù)局部對象賦值，如使用 SET 給標量和表局部變量賦值。 游標操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標。 不允許使用 FETCH 語句將數(shù)據(jù)返回到客戶端。 僅允許使用 FETCH 語句通過 INTO 子句給局部變量賦值。 控制流語句。 SELECT 語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦予函數(shù)的局部變量。 INSERT、UPDATE 和 DELETE 語句，這些語句修改函數(shù)的局部 table 變量。 EXECUTE 語句，該語句調(diào)用擴展存儲過程。 在查詢中指定的函數(shù)的實際執(zhí)行次數(shù)在優(yōu)化器生成的執(zhí)行計劃間可能不同。 示例為 WHERE 子句中的子查詢喚醒調(diào)用的函數(shù)。 子查詢及其函數(shù)執(zhí)行的次數(shù)會因優(yōu)化器選擇的訪問路徑而異

數(shù)據(jù)庫的存儲過程，函數(shù)，觸發(fā)器，游標，視圖，索引等內(nèi)容講解

子程序

子程序包括存儲過程、自定義函數(shù)、游標、觸發(fā)器。 可以被編譯和存儲在數(shù)據(jù)庫中，它具有模塊化、重用性、可維護性、可擴展性、安全性等特點。 其目的是完成特定的功能，能被程序和客戶端工具直接調(diào)用。 子程序也屬于數(shù)據(jù)庫對象，可以被授權(quán)能否執(zhí)行。

存儲過程

存儲過程是一種存儲復雜程序，方便外部程序調(diào)用的數(shù)據(jù)庫對象。 是為了完成某個特定功能的 SQL 語句集合，用戶可以通過存儲過程的名字和參數(shù)進行調(diào)用。 MySQL 從 5.0 版本開始支持存儲過程（Stored Procedure）。

創(chuàng)建和刪除存儲過程

創(chuàng)建存儲過程，其關(guān)鍵語法如下：

CREATE PROCEDURE 存儲過程名([IN|OUT|INOUT] 參數(shù)名 數(shù)據(jù)類型)BEGIN……END

對存儲過程進行參數(shù)定義時，多個參數(shù)用?，?分割，共有三種參數(shù)類型：IN，OUT，INOUT：

IN: 參數(shù)的值必須在調(diào)用存儲過程時指定，在存儲過程中修改該參數(shù)的值不會影響調(diào)用環(huán)境的數(shù)據(jù)值；

OUT: 該值可在存儲過程內(nèi)部被改變，同時引起調(diào)用環(huán)境中數(shù)據(jù)值的改變；（有out需要 @變量 接收）

INOUT: 調(diào)用時指定，兼具?IN?和?OUT?類型參數(shù)的特點。

以?BEGIN?和?END?對過程體的開始和結(jié)束進行標識。

需要強調(diào)一點，MySQL 中存儲過程默認以?;?作為結(jié)束符，如果不改變結(jié)束符，編譯器會把存儲過程當成 SQL 語句進行處理，因此編譯過程會報錯。 所以要事先用?DELIMITER //?聲明當前的分隔符，其目的是讓編譯器把兩個?//?之間的內(nèi)容當作一個存儲過程，使用?DELIMITER ;?則恢復結(jié)束符為?;?。

刪除存儲過程使用?DROP PROCEDURE?語句，其語法如下：

DROP PROCEDURE 存儲過程名;函數(shù)

自定義函數(shù)是一種對 MySQL 的擴展，其用法和內(nèi)置函數(shù)相同。 在前面文章，我們使用的函數(shù)是 MySQL 內(nèi)置函數(shù)（已經(jīng)寫好的），直接調(diào)用即可完成某個特定功能，下面將會介紹 MySQL 自定義函數(shù)。

創(chuàng)建和刪除自定義函數(shù)

創(chuàng)建自定義函數(shù)使用?CREATE FUNCTION?語句，語法如下：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;

MySQL 安裝完成后默認不允許創(chuàng)建自定義函數(shù)，需要在??配置文件中增加?log-bin-trust-FUNCTION-creators=1，然后重啟數(shù)據(jù)庫，使其具有創(chuàng)建函數(shù)的權(quán)限。 也可以在 MySQL 命令行中輸入以下命令來解決。 ?SET GLOBAL log_bin_trust_function_creators = 1;。

刪除自定義函數(shù)使用?DROP FUNCTION?語句，其語法如下：

DROP FUNCTION 函數(shù)名;

需要注意，刪除自定義函數(shù)時，函數(shù)名后面不能加括號

游標

游標（CURSOR）是一個存儲在 MySQL 服務(wù)器上面的數(shù)據(jù)庫查詢機制，類似于數(shù)組的下標。 使用游標后，可以逐步提取查詢結(jié)果。

使用游標需要注意以下幾點：

聲明游標之后，必須先打開游標才能使用；

在游標結(jié)束之后，要關(guān)閉游標。

其使用的基本步驟如下：

聲明游標，其語法如下；

declare 游標名 cursor for select_statement

打開游標

open 游標名

從游標中取值，使用 fetch 進行取值，語法如下：

fetch 游標名 into var1,var2,……

利用fetch將取到的一條記錄中的字段賦值給多個變量。

關(guān)閉游標

close 游標名觸發(fā)器（TRIGGER）

是一種特殊的存儲過程，它在插入、修改或刪除表中的數(shù)據(jù)時觸發(fā)執(zhí)行，擁有更精細、更復雜的數(shù)據(jù)控制能力。 MySQL 從 5.0 版本開始支持觸發(fā)器。

舉個例子，現(xiàn)有用戶表和日志表。 當一個用戶被創(chuàng)建時，我們用日志來記錄用戶的創(chuàng)建過程。 如果不使用觸發(fā)器，則需要手動編寫程序來實現(xiàn)；而一旦使用觸發(fā)器，我們可以在信息插入用戶表后，立刻觸發(fā)對日志表的操作，使其記錄創(chuàng)建用戶的信息。

創(chuàng)建和刪除觸發(fā)器

創(chuàng)建觸發(fā)器的語法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_statement;

參數(shù)解釋：

trigger_name：觸發(fā)器名稱，自己定義；

trigger_time：觸發(fā)時機，只有兩個值，before（某事件之前），after（某事件之后）；

trigger_event：觸發(fā)事件，取值 INSERT（插入）、UPDATE（更新）、DELETE（刪除）；

table_name：需要建立觸發(fā)器的表名；

trigger_statement：觸發(fā)器程序體，一條 SQL 語句或存儲過程等；

查看觸發(fā)器

使用?SHOW TRIGGERS;?命令查看所有觸發(fā)器，因為觸發(fā)器有自己的保存機制，顯示出來的信息量比較大

刪除觸發(fā)器使用的語法如下：

DROP TRIGGER 觸發(fā)器名;視圖

視圖是從一個或多個表中糅合出來的虛擬表。 一個視圖并不包含真實的數(shù)據(jù)，它提供了另一個視角去查看或改變表中的數(shù)據(jù)。

打個比喻：把視圖想象成一扇窗戶，通過窗戶往里看，我們只能看到一部分，而這部分就是數(shù)據(jù)庫系統(tǒng)允許你看到的數(shù)據(jù)。 而不允許你看到的內(nèi)容會被遮擋住，讓你不可見。

使用視圖可以提高我們對數(shù)據(jù)的操作效率，同時增加安全性：

提高效率：將經(jīng)常使用復雜查詢定義為視圖，由于對視圖的權(quán)限、語法解析都會被存儲，就避免了重復解析；

增加數(shù)據(jù)安全性：通過視圖，用戶只能查詢和更改指定的數(shù)據(jù)；

總之，使用視圖的主要作用就是保障數(shù)據(jù)的安全性，同時提高查詢效率。

創(chuàng)建視圖

創(chuàng)建視圖使用的語句是?CREATE View，完整語法看上去比較復雜，大家可以到官網(wǎng)上去查看。

DROP PROCEDURE 存儲過程名;0查詢視圖

視圖是一種虛擬的表，也符合 DQL 操作，視圖的查詢和表的查詢相同，查看 city_VIEW 視圖中的所有數(shù)據(jù)，其 SQL 語句如下：

DROP PROCEDURE 存儲過程名;1刪除視圖

使用?DELETE FROM?子句可以刪除視圖，其語法如下：

DROP PROCEDURE 存儲過程名;2更新視圖數(shù)據(jù)

把 id 為 2 的城市人口更新為 。其 SQL 語句如下：

DROP PROCEDURE 存儲過程名;3

查看視圖和基表，結(jié)果如下：

DROP PROCEDURE 存儲過程名;4

查看基表數(shù)據(jù)如下：

DROP PROCEDURE 存儲過程名;5插入視圖數(shù)據(jù)

給視圖插入數(shù)據(jù)和表插入數(shù)據(jù)一樣，使用?INSERT INTO?語句，我們在視圖中插入一條 id 為 4080，名字為?test?，人口數(shù)量為 500 的記錄，其 SQL 語句如下：

DROP PROCEDURE 存儲過程名;6序列

前面我們在介紹主鍵的時候知道，主鍵必須是唯一的。 為了方便管理主鍵同時滿足主鍵唯一性要求，我們把主鍵設(shè)置為自增長。 實現(xiàn)自增長需要用到序列。

序列就是一組有特定變化規(guī)律的整數(shù)，其最主要的用途就是創(chuàng)建主鍵，確保主鍵的唯一性。 序列是一個數(shù)據(jù)庫對象，獨立于表進行存儲，可以為多個表使用。

目前 MySQL 是不支持類似建表或視圖的方式來直接創(chuàng)建序列對象（Oracle 支持）。 雖然有?auto_increment?來實現(xiàn)自增長，但不能設(shè)置步長、起始值、是否循環(huán)等。 最重要的是，在 MySQL 中一張表只能有一個字段設(shè)置為自增長，如果我們需要兩個或以上的字段實現(xiàn)自增長該怎么辦呢？ 需要做一些間接處理。

新建表時設(shè)置起始值

下面我們在 demo 數(shù)據(jù)庫下新創(chuàng)建一個表 demo2：

DROP PROCEDURE 存儲過程名;7

插入新的值：

DROP PROCEDURE 存儲過程名;8

可以看到起始值為 1000。

創(chuàng)建表后設(shè)置起始值

修改 demo2 表的起始值為 1500：

DROP PROCEDURE 存儲過程名;9

這時如果再插入新值：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;0

可以看到新的 id 起始值已經(jīng)變?yōu)?1500 了 使用?AUTO_INCREMENT?屬性時我們需要注意：

每一個表中只能有一個?AUTO_INCREMENT?列

同時該列必須要有?NOT NULL?屬性來進行約束，在 MySQL 中會自動將?NOT NULL?約束隱式添加到列中。

AUTO_INCREMENT?列必需建立索引，可以為?PRIMARY KEY?或?UNIQUE?索引。

AUTO_INCREMENT?列具有以下屬性：

默認起始值為 1，當在該列中插入?NULL?值或是在?INSERT?語句中忽略該值時，它的起始值將會自增 1。

如果使用?DELETE?語句刪除最后插入的行，則 MySQL 可能會也可能不會重復使用刪除的序列號，具體取決于表的存儲引擎。 如 MySQL 默認的?InnoDB?就不會重用序列號。

如果使用?UPDATE?語句將?AUTO_INCREMENT?列中的值更新為已經(jīng)存在的值，且該列具有唯一索引，則 MySQL 將發(fā)出重復鍵錯誤。

獲得最后生成的序列號可以使用?LAST_INSERT_ID()?函數(shù)。

索引

所有數(shù)據(jù)操作可簡單分為讀操作（獲取數(shù)據(jù)）和寫操作（插入數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)）。 一般情況下，讀寫比例在 10:1 左右，大量的讀操作給數(shù)據(jù)庫性能帶來不小的考驗。 因此，對查詢語句的優(yōu)化是重中之重，優(yōu)化的關(guān)鍵就是利用好索引。

索引在 MySQL 中又叫作?鍵?，英文名?key?，是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。 索引對于性能的提升非常關(guān)鍵，尤其是當表中的數(shù)據(jù)量越來越龐大的時候。 我們前面介紹約束使用到的?primary key?，其實就是一種索引，叫做主鍵索引。

舉個簡單的例子：我們把數(shù)據(jù)庫比作漢語字典，那么索引就是這本字典的音序表，通過音序表可以快速查找到需要的漢字。 索引的目的就是為了提高查詢效率。 在 MySQL 中常用的索引可以分為三類，分別是：普通索引、唯一索引、聯(lián)合索引。

普通索引

普通索引使用關(guān)鍵字 INDEX 定義，根據(jù)建立索引的時機不同，書寫方式有細微差別。分為以下 3 種情況：

創(chuàng)建表的時候創(chuàng)建索引；

創(chuàng)建表后創(chuàng)建索引；

修改表的時候添加索引。

唯一索引

唯一索引不僅加速查找，還具有約束性。

主鍵索引?primary key

唯一鍵索引?unique

聯(lián)合索引

聯(lián)合索引即為索引同時設(shè)置多個字段。

primary key(id,name)，聯(lián)合主鍵索引

index(id,name)，聯(lián)合普通索引 在建表的時候創(chuàng)建索引。其語法如下：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;1

給已經(jīng)存在的表中某字段添加索引，其語法如下：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;2

修改表時創(chuàng)建索引，其語法如下：

ALTER TABLE 表名 ADD CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;1

查看索引，借助表信息查看表中是否存在索引，其語法如下：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;4

查看 teacher 表中的索引，其 SQL 語句如下：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;5

刪除索引，使用的關(guān)鍵字是DROP INDEX … on …，其語法如下：

CREATE FUNCTION 函數(shù)名([變量名1 變量類型1, ……, 變量名n 變量類型n]) RETURNS 數(shù)據(jù)類型BEGINsql語句;RETURN 值;END;6原文：

---

---

相關(guān)標簽： 探索、 存儲過程的進階特性、 MySQL、 探索海洋的歐美動畫片、 自定義函數(shù)、 表值參數(shù)和游標、

上一篇：MySQL存儲過程與觸發(fā)器協(xié)同工作以實現(xiàn)數(shù)據(jù)

下一篇：使用MySQL存儲過程加速數(shù)據(jù)處理批量操作復