MySQL-StoredProcedure-Translation-Cn

Transcription

1 MySQL 5.0 存 儲 過 程 MySQL 5.0 新 特 性 系 列 第 一 部 分 MySQL 技 術 白 皮 書 Peter Gulutzan March, 2005 翻 譯 : 陳 朋 奕 西 安 電 子 科 技 大 學 ( 聲 明 : 屬 於 個 人 翻 譯, 不 涉 及 任 何 商 業 目 的, 支 援 國 內 MySQL 發 展, 請 轉 載 時 注 明 出 處, 謝 謝 ) 第 1 頁

2 Table of Contents 目 錄 ( 目 錄 不 做 翻 譯 了, 因 為 基 本 都 是 專 有 名 詞 ) Introduction...3 A Definition and an Example...3 Why Stored Procedures...4 Why MySQL Statements are Legal in a Procedure Body...8 Characteristics Clauses...10 Parameters...13 The New SQL Statements...15 Scope...16 Loops...21 Error Handling...29 Cursors...35 Security...41 Functions...43 Metadata...44 Details...48 Style...52 Tips when writing long routines...63 Bugs...64 Feature Requests...65 Resources...65 Conclusion...66 About MySQL...66 第 2 頁

3 Introduction 本 書 是 為 需 要 瞭 解 5.0 版 本 新 特 性 的 MySQL 老 用 戶 而 寫 的 簡 單 的 來 說 是 介 紹 了 存 儲 過 程 觸 發 器 視 圖 資 訊 架 構 視 圖, 這 是 介 紹 MySQL 5.0 新 特 性 叢 書 的 第 一 集 希 望 這 本 書 能 像 內 行 專 家 那 樣 與 您 進 行 對 話, 用 簡 單 的 問 題 例 子 讓 你 學 到 需 要 的 知 識 為 了 達 到 這 樣 的 目 的, 我 會 從 每 一 個 細 節 開 始 慢 慢 的 為 大 家 建 立 概 念, 最 後 會 給 大 家 展 示 較 大 的 實 用 用 例, 在 學 習 之 前 也 許 大 家 會 認 為 這 個 用 例 很 難, 但 是 只 要 跟 著 課 程 去 學, 相 信 很 快 就 能 掌 握 Conventions and Styles 約 定 和 編 程 風 格 每 次 我 想 要 演 示 實 際 代 碼 時, 我 會 對 mysql 用 戶 端 的 螢 幕 就 出 現 的 代 碼 進 行 調 整, 將 字 體 改 成 Courier, 使 他 們 看 起 來 與 普 通 文 本 不 一 樣 在 這 裏 舉 個 例 子 : mysql> DROP FUNCTION f; Query OK, 0 rows affected (0.00 sec) 如 果 實 例 比 較 大, 則 需 要 在 某 些 行 和 段 落 間 加 注 釋, 同 時 我 會 用 將 <-- 符 號 放 在 頁 面 的 右 邊 以 表 示 強 調 例 如 : mysql> CREATE PROCEDURE p () -> -> /* This procedure does nothing */ <-- -> END;// Query OK, 0 rows affected (0.00 sec) 有 時 候 我 會 將 例 子 中 的 "mysql>" 和 "->" 這 些 系 統 顯 示 去 掉, 你 可 以 直 接 將 代 碼 複 製 到 mysql 用 戶 端 程 式 中 ( 如 果 你 現 在 所 讀 的 不 是 電 子 版 的, 可 以 在 mysql.com 網 站 下 載 相 關 腳 本 ) 所 以 的 例 子 都 已 經 在 Suse 9.2 Linux Mysql 公 共 版 上 測 試 通 過 在 您 閱 讀 本 書 的 時 候, Mysql 已 經 有 更 高 的 版 本, 同 時 能 支 持 更 多 OS 了, 包 括 Windows,Sparc,HP-UX 因 此 這 裏 的 例 子 將 能 正 常 的 運 行 在 您 的 電 腦 上 但 如 果 運 行 仍 然 出 現 故 障, 可 以 諮 詢 你 認 識 的 資 深 Mysql 用 戶, 以 得 到 長 久 的 支 持 和 幫 助 A Definition and an Example 定 義 及 實 例 存 儲 過 程 是 一 種 存 儲 在 書 庫 庫 中 的 程 式 ( 就 像 正 規 語 言 裏 的 副 程 式 一 樣 ), 準 確 的 來 說, MySQL 支 援 的 routines( 常 式 ) 有 兩 種 : 一 是 我 們 說 的 存 儲 過 程, 二 是 在 其 他 SQL 語 句 中 可 以 返 回 值 的 函 數 ( 使 用 起 來 和 Mysql 預 裝 載 的 函 數 一 樣, 如 pi()) 我 在 本 書 裏 面 會 更 經 常 使 用 存 儲 過 程, 因 為 這 是 我 們 過 去 的 習 慣, 相 信 大 家 也 會 接 受 第 3 頁

4 一 個 存 儲 過 程 包 括 名 字, 參 數 列 表, 以 及 可 以 包 括 很 多 SQL 語 句 的 SQL 語 句 集 在 這 裏 對 局 部 變 數, 異 常 處 理, 迴 圈 控 制 和 IF 條 件 句 有 新 的 語 法 定 義 下 面 是 一 個 包 括 存 儲 過 程 的 實 例 聲 明 : ( 譯 注 : 為 了 方 便 閱 讀, 此 後 的 程 式 不 添 任 何 中 文 注 釋 ) CREATE PROCEDURE procedure1 /* name 存 儲 過 程 名 */ (IN parameter1 INTEGER) /* parameters 參 數 */ /* start of block 語 句 塊 頭 */ DECLARE variable1 CHAR(10); /* variables 變 數 聲 明 */ IF parameter1 = 17 THEN /* start of IF IF 條 件 開 始 */ SET variable1 = 'birds'; /* assignment 賦 值 */ ELSE SET variable1 = 'beasts'; /* assignment 賦 值 */ /* end of IF IF 結 束 */ INSERT INTO table1 VALUES (variable1);/* statement SQL 語 句 */ END /* end of block 語 句 塊 結 束 */ 下 面 我 將 會 介 紹 你 可 以 利 用 存 儲 過 程 做 的 工 作 的 所 有 細 節 同 時 我 們 將 介 紹 新 的 資 料 庫 對 象 觸 發 器, 因 為 觸 發 器 和 存 儲 過 程 的 關 聯 是 必 然 的 Why Stored Procedures 為 什 麼 要 用 存 儲 過 程 由 於 存 儲 過 程 對 於 MySQL 來 說 是 新 的 功 能, 很 自 然 的 在 使 用 時 你 需 要 更 加 注 意 畢 竟, 在 此 之 前 沒 有 任 何 人 使 用 過, 也 沒 有 很 多 大 量 的 有 經 驗 的 用 戶 來 帶 你 走 他 們 走 過 的 路 然 而 你 應 該 開 始 考 慮 把 現 有 程 式 ( 可 能 在 伺 服 器 應 用 程 式 中, 用 戶 自 定 義 函 數 (UDF) 中, 或 是 腳 本 中 ) 轉 移 到 存 儲 過 程 中 來 這 樣 做 不 需 要 原 因, 你 不 得 不 去 做 存 儲 過 程 是 已 經 被 認 證 的 技 術! 雖 然 在 Mysql 中 它 是 新 的, 但 是 相 同 功 能 的 函 數 在 其 他 DBMS 中 早 已 存 在, 而 它 們 的 語 法 往 往 是 相 同 的 因 此 你 可 以 從 其 他 人 那 裏 獲 得 這 些 概 念, 也 有 很 多 你 可 以 諮 詢 或 者 雇 用 的 經 驗 用 戶, 還 有 許 多 第 三 方 的 文 檔 可 供 你 閱 讀 存 儲 過 程 會 使 系 統 運 行 更 快! 雖 然 我 們 暫 時 不 能 在 Mysql 上 證 明 這 個 優 勢, 用 戶 得 到 的 體 驗 也 不 一 樣 我 們 可 以 說 的 就 是 Mysql 伺 服 器 在 緩 存 機 制 上 做 了 改 進, 就 像 Prepared statements( 預 處 理 語 句 ) 所 做 的 那 樣 由 於 沒 有 編 譯 器, 因 此 SQL 存 儲 過 程 不 會 像 外 部 語 言 ( 如 C) 編 寫 的 程 式 運 行 起 來 那 麼 快 但 是 提 升 速 度 的 主 要 方 法 卻 在 於 能 否 降 低 網 路 資 訊 流 量 如 果 你 需 要 處 理 的 是 需 要 檢 查 迴 圈 多 語 句 但 沒 有 用 戶 交 互 的 重 複 性 任 務, 你 就 可 以 使 用 保 存 在 伺 服 器 上 的 存 儲 過 程 來 完 成 這 樣 在 執 行 任 務 的 每 一 步 時 伺 服 器 和 用 戶 端 之 間 就 沒 那 麼 多 的 資 訊 來 往 了 存 儲 過 程 是 可 複 用 的 組 件! 想 像 一 下 如 果 你 改 變 了 主 機 的 語 言, 這 對 存 儲 過 程 不 會 產 生 影 響, 因 為 它 是 資 料 庫 邏 輯 而 不 是 應 用 程 式 存 儲 過 程 是 可 以 移 植 的! 當 你 用 SQL 編 寫 存 儲 過 程 時, 你 就 知 道 它 可 以 運 行 在 Mysql 支 援 的 任 何 平 臺 上, 不 需 要 你 額 外 添 加 運 行 環 境 包, 也 不 需 要 為 程 式 在 作 業 系 統 中 執 行 設 置 許 可, 或 者 為 你 的 不 同 型 號 的 電 腦 配 置 不 同 的 包 這 就 是 與 Java C 或 PHP 等 外 部 語 言 相 比 使 用 SQL 語 句 的 優 勢 不 過, 使 用 外 部 語 言 常 式 的 好 處 還 是 很 好 的 選 擇, 它 們 只 是 沒 有 以 上 的 優 點 而 已 第 4 頁

5 存 儲 過 程 將 被 保 存! 如 果 你 編 寫 好 了 一 個 程 式, 例 如 顯 示 銀 行 事 物 處 理 中 的 支 票 撤 銷, 那 想 要 瞭 解 支 票 的 人 就 可 以 找 到 你 的 程 式 它 會 以 源 代 碼 的 形 式 保 存 在 資 料 庫 中 這 將 使 資 料 和 處 理 資 料 的 進 程 有 意 義 的 關 聯 這 可 能 跟 你 在 課 上 聽 到 的 規 劃 論 中 說 的 一 樣 存 儲 過 程 可 以 移 植!Mysql 完 全 支 持 SQL 2003 標 準 某 些 資 料 庫 ( 如 DB2 Mimer) 同 樣 支 援 但 也 有 部 分 不 支 援 的, 如 Oracle SQL Server 不 支 援 我 們 將 會 給 予 足 夠 幫 助 和 工 具, 使 為 其 他 DBMS 編 寫 的 代 碼 能 更 容 易 轉 移 到 Mysql 上 Setting up with MySQL 5.0 設 置 並 開 始 MySQL 5.0 服 務 通 過 mysql_fix_privilege_tables 或 者 ~/mysql-5.0/scripts/mysql_install_db 來 開 始 MySQL 服 務 作 為 我 們 練 習 的 準 備 工 作 的 一 部 分, 我 假 定 MySQL 5.0 已 經 安 裝 如 果 沒 有 資 料 庫 管 理 員 為 你 安 裝 好 資 料 庫 以 及 其 他 軟 體, 你 就 需 要 自 己 去 安 裝 了 不 過 你 很 容 易 忘 掉 一 件 事, 那 就 是 你 需 要 有 一 個 名 為 mysql.proc 的 表 在 安 裝 了 最 新 版 本 後, 你 必 須 運 行 mysql_fix_privilege_tables 或 者 mysql_install_db( 只 需 要 運 行 其 中 一 個 就 夠 了 ) 不 然 存 儲 過 程 將 不 能 工 作 我 同 時 啟 用 在 root 身 份 後 運 行 一 個 非 正 式 的 SQL 腳 本, 如 下 : mysql>source/home/pgulutzan/mysql- 5.0/scripts/mysql_prepare_privilege_tables_for_5.sql Starting the MySQL Client 啟 動 MySQL 用 戶 端 這 是 我 啟 動 mysql 用 戶 端 的 方 式 你 也 許 會 使 用 其 他 方 式, 如 果 你 使 用 的 是 二 進 位 版 本 或 者 是 Windows 系 統 的 電 腦, 你 可 能 會 在 其 他 子 目 錄 下 運 行 以 下 程 式 : pgulutzan@mysqlcom:~> /usr/local/mysql/bin/mysql --user=root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: alpha-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 在 演 示 中, 我 將 會 展 示 以 root 身 份 登 陸 後 的 mysql 用 戶 端 返 回 的 結 果, 這 樣 意 味 著 我 有 極 大 的 特 權 Check for the Correct Version 核 對 版 本 為 了 確 認 使 用 的 MySQL 的 版 本 是 正 確 的, 我 們 要 查 詢 版 本 我 有 兩 種 方 法 確 認 我 使 用 的 是 5.0 版 本 : SHOW VARIABLES LIKE 'version'; or SELECT VERSION(); 第 5 頁

6 例 如 : mysql> SHOW VARIABLES LIKE 'version'; Variable_name Value version alpha-debug row in set (0.00 sec) mysql> SELECT VERSION(); VERSION() alpha-debug row in set (0.00 sec) 當 看 見 數 字 '5.0.x' 後 就 可 以 確 認 存 儲 過 程 能 夠 在 這 個 用 戶 端 上 正 常 工 作 The Sample "Database" 示 例 資 料 庫 現 在 要 做 的 第 一 件 事 是 創 建 一 個 新 的 資 料 庫 然 後 設 定 為 默 認 資 料 庫 實 現 這 個 步 驟 的 SQL 語 句 如 下 : CREATE DATABASE db5; USE db5; 例 如 : mysql> CREATE DATABASE db5; Query OK, 1 row affected (0.00 sec) mysql> USE db5; Database changed 在 這 裏 要 避 免 使 用 有 重 要 資 料 的 實 際 的 資 料 庫 然 後 我 們 創 建 一 個 簡 單 的 工 作 表 實 現 這 個 步 驟 的 SQL 語 句 如 下 : mysql> CREATE DATABASE db5; Query OK, 1 row affected (0.01 sec) mysql> USE db5; Database changed mysql> CREATE TABLE t (s1 INT); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t VALUES (5); Query OK, 1 row affected (0.00 sec) 你 會 發 現 我 只 在 表 中 插 入 了 一 列 這 樣 做 的 原 因 是 我 要 保 持 表 的 簡 單, 因 為 在 這 裏 並 不 需 要 展 示 查 詢 資 料 的 技 巧, 而 是 教 授 存 儲 過 程, 不 需 要 使 用 大 的 資 料 表, 因 為 它 本 身 已 經 夠 複 雜 了 第 6 頁

7 這 就 是 示 例 資 料 庫, 我 們 將 從 這 個 名 字 為 t 的 只 包 含 一 列 的 表 開 始 Pick a Delimiter 選 擇 分 隔 符 號 現 在 我 們 需 要 一 個 分 隔 符 號, 實 現 這 個 步 驟 的 SQL 語 句 如 下 : DELIMITER // 例 如 : mysql> DELIMITER // 分 隔 符 號 是 你 通 知 mysql 用 戶 端 你 已 經 完 成 輸 入 一 個 SQL 語 句 的 字 元 或 字 串 符 號 一 直 以 來 我 們 都 使 用 分 號 ;, 但 在 存 儲 過 程 中, 這 會 產 生 不 少 問 題, 因 為 存 儲 過 程 中 有 許 多 語 句, 所 以 每 一 個 都 需 要 一 個 分 號 因 此 你 需 要 選 擇 一 個 不 太 可 能 出 現 在 你 的 語 句 或 程 式 中 的 字 串 作 為 分 隔 符 號 我 曾 用 過 雙 斜 杠 //, 也 有 人 用 豎 線 我 曾 見 過 在 DB2 程 式 中 使 符 號 的, 但 我 不 喜 歡 這 樣 你 可 以 根 據 自 己 的 喜 好 來 選 擇, 但 是 在 這 個 課 程 中 為 了 更 容 易 理 解, 你 最 好 選 擇 跟 我 一 樣 如 果 以 後 要 恢 復 使 用 ; ( 分 號 ) 作 為 分 隔 符 號, 輸 入 下 面 語 句 就 可 以 了 : "DELIMITER ;//". CREATE PROCEDURE Example 創 建 程 式 實 例 CREATE PROCEDURE p1 () SELECT * FROM t; // 也 許 這 是 你 使 用 Mysql 創 建 的 第 一 個 存 儲 過 程 假 如 是 這 樣 的 話, 最 好 在 你 的 日 記 中 記 下 這 個 重 要 的 里 程 碑 CREATE PROCEDURE p1 () SELECT * FROM t; // <-- SQL 語 句 存 儲 過 程 的 第 一 部 分 是 CREATE PROCEDURE : CREATE PROCEDURE p1 () SELECT * FROM t; // <-- 第 二 部 分 是 過 程 名, 上 面 新 存 儲 過 程 的 名 字 是 p1 Digression: Legal Identifiers 題 外 話 : 合 法 識 別 字 的 問 題 存 儲 過 程 名 對 大 小 寫 不 敏 感, 因 此 P1 和 p1 是 同 一 個 名 字, 在 同 一 個 資 料 庫 中 你 將 不 能 給 兩 個 存 儲 過 程 取 相 同 的 名 字, 因 為 這 樣 將 會 導 致 重 載 某 些 DBMS 允 許 重 載 (Oracle 支 持 ), 但 是 MySQL 不 支 持 ( 譯 者 話 : 希 望 以 後 會 支 持 吧 ) 你 可 以 採 取 資 料 庫 名. 存 儲 過 程 名 這 樣 的 折 中 方 法, 如 db5.p1 存 儲 過 程 名 可 以 分 開, 它 可 以 包 括 空 白 字 元, 其 長 度 限 制 為 64 個 字 元, 但 注 意 不 要 使 用 MySQL 內 建 函 數 的 名 字, 如 果 這 樣 做 了, 在 調 用 時 將 會 出 現 下 面 的 情 況 : 第 7 頁

8 mysql> CALL pi(); Error 1064 (42000): You have a syntax error. mysql> CALL pi (); Error 1305 (42000): PROCEDURE does not exist. 在 上 面 的 第 一 個 例 子 裏, 我 調 用 的 是 一 個 名 字 叫 pi 的 函 數, 但 你 必 須 在 調 用 的 函 數 名 後 加 上 空 格, 就 像 第 二 個 例 子 那 樣 CREATE PROCEDURE p1 () SELECT * FROM t; // <-- 其 中 () 是 參 數 列 表 CREATE PROCEDURE 語 句 的 第 三 部 分 是 參 數 列 表 通 常 需 要 在 括 弧 內 添 加 參 數 例 子 中 的 存 儲 過 程 沒 有 參 數, 因 此 參 數 列 表 是 空 的 所 以 我 只 需 要 鍵 入 空 括 弧, 然 而 這 是 必 須 的 CREATE PROCEDURE p1 () SELECT * FROM t; // <-- "SELECT * FROM t;" 是 存 儲 過 程 的 主 體 然 後 到 了 語 句 的 最 後 一 個 部 分 了, 它 是 存 儲 過 程 的 主 體, 是 一 般 的 SQL 語 句 過 程 體 中 語 句 "SELECT * FROM t;" 包 含 一 個 分 號, 如 果 後 面 有 語 句 結 束 符 號 (//) 時 可 以 不 寫 這 個 分 號 如 果 你 還 記 得 我 把 這 部 分 叫 做 程 式 的 主 體 將 會 是 件 好 事, 因 為 (body) 這 個 詞 是 大 家 使 用 的 技 術 上 的 術 語 通 常 我 們 不 會 將 SELECT 語 句 用 在 存 儲 過 程 中, 這 裏 只 是 為 了 演 示 所 以 使 用 這 樣 的 語 句, 能 在 調 用 時 更 好 的 看 出 程 式 是 否 正 常 工 作 Why MySQL Statements are Legal in a Procedure Body 什 麼 MySQL 語 句 在 存 儲 過 程 體 中 是 合 法 的? 什 麼 樣 的 SQL 語 句 在 Mysql 存 儲 過 程 中 才 是 合 法 的 呢? 你 可 以 創 建 一 個 包 含 INSERT, UPDATE, DELETE, SELECT, DROP, CREATE, REPLACE 等 等 的 語 句 你 唯 一 需 要 記 住 的 是 如 果 代 碼 中 包 含 MySQL 擴 充 功 能, 那 麼 代 碼 將 不 能 移 植 在 標 準 SQL 語 句 中 : 任 何 資 料 庫 定 義 語 言 都 是 合 法 的, 如 : CREATE PROCEDURE p () DELETE FROM t; // SET COMMIT 以 及 ROLLBACK 也 是 合 法 的, 如 : CREATE PROCEDURE p () = 5; // MySQL 的 附 加 功 能 : 任 何 資 料 操 作 語 言 的 語 句 都 將 合 法 CREATE PROCEDURE p () DROP TABLE t; // MySQL 擴 充 功 能 : 直 接 的 SELECT 也 是 合 法 的 : CREATE PROCEDURE p () SELECT 'a'; // 順 便 提 一 下, 我 將 存 儲 過 程 中 包 括 DDL 語 句 的 功 能 稱 為 MySQL 附 加 功 能 的 原 因 是 在 SQL 標 准 中 把 這 個 定 義 為 非 核 心 的, 即 可 選 元 件 第 8 頁

9 在 過 程 體 中 有 一 個 約 束, 就 是 不 能 有 對 常 式 或 表 操 作 的 資 料 庫 操 作 語 句 例 如 下 面 的 例 子 就 是 非 法 的 : CREATE PROCEDURE p1 () CREATE PROCEDURE p2 () DELETE FROM t; // 下 面 這 些 對 MySQL 5.0 來 說 全 新 的 語 句, 在 過 程 體 中 是 非 法 的 : CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER. 不 過 你 可 以 使 用 "CREATE PROCEDURE db5.p1 () DROP DATABASE db5//", 但 是 類 似 "USE database" 語 句 也 是 非 法 的, 因 為 MySQL 假 定 默 認 資 料 庫 就 是 過 程 的 工 作 場 所 Call the Procedure 調 用 存 儲 過 程 1. 現 在 我 們 就 可 以 調 用 一 個 存 儲 過 程 了, 你 所 需 要 輸 入 的 全 部 就 是 CALL 和 你 過 程 名 以 及 一 個 括 弧 再 一 次 強 調, 括 弧 是 必 須 的 當 你 調 用 例 子 裏 面 的 p1 過 程 時, 結 果 是 螢 幕 返 回 了 t 表 的 內 容 mysql> CALL p1() // s row in set (0.03 sec) Query OK, 0 rows affected (0.03 sec) 因 為 過 程 中 的 語 句 是 "SELECT * FROM t;" 2. 其 他 實 現 方 式 mysql> CALL p1() // 和 下 面 語 句 的 執 行 效 果 一 樣 : mysql> SELECT * FROM t; // 所 以, 你 調 用 p1 過 程 就 相 當 於 你 執 行 了 下 面 語 句 : "SELECT * FROM t;". 好 了, 主 要 的 知 識 點 創 建 和 調 用 過 程 方 法 已 經 清 楚 了 我 希 望 你 能 對 自 己 說 這 相 當 簡 單 但 是 很 快 我 們 就 有 一 系 列 的 練 習, 每 次 都 加 一 條 子 句, 或 者 改 變 已 經 存 在 的 子 句 那 樣 在 寫 複 雜 部 件 前 我 們 將 會 有 很 多 可 用 的 子 句 第 9 頁

10 Characteristics Clauses 特 徵 子 句 1. CREATE PROCEDURE p2 () LANGUAGE SQL <-- NOT DETERMINISTIC <-- SQL SECURITY DEFINER <-- COMMENT 'A Procedure' <-- SELECT CURRENT_DATE, RAND() FROM t // 這 裏 我 給 出 的 是 一 些 能 反 映 存 儲 過 程 特 性 的 子 句 子 句 內 容 在 括 弧 之 後, 主 體 之 前 這 些 子 句 都 是 可 選 的, 他 們 有 什 麼 作 用 呢? 2. CREATE PROCEDURE p2 () LANGUAGE SQL <-- NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A Procedure' SELECT CURRENT_DATE, RAND() FROM t // 很 好, 這 個 LANGUAGE SQL 子 句 是 沒 有 作 用 的 僅 僅 是 為 了 說 明 下 面 過 程 的 主 體 使 用 SQL 語 言 編 寫 這 條 是 系 統 默 認 的, 但 你 在 這 裏 聲 明 是 有 用 的, 因 為 某 些 DBMS(IBM 的 DB2) 需 要 它, 如 果 你 關 注 DB2 的 相 容 問 題 最 好 還 是 用 上 此 外, 今 後 可 能 會 出 現 除 SQL 外 的 其 他 語 言 支 援 的 存 儲 過 程 3. CREATE PROCEDURE p2 () LANGUAGE SQL NOT DETERMINISTIC <-- SQL SECURITY DEFINER COMMENT 'A Procedure' SELECT CURRENT_DATE, RAND() FROM t // 下 一 個 子 句,NOT DETERMINISTIC, 是 傳 遞 給 系 統 的 資 訊 這 裏 一 個 確 定 過 程 的 定 義 就 是 那 些 每 次 輸 入 一 樣 輸 出 也 一 樣 的 程 式 在 這 個 案 例 中, 既 然 主 體 中 含 有 SELECT 語 句, 那 返 回 肯 定 是 未 知 的 因 此 我 們 稱 其 NOT DETERMINISTIC 但 是 MySQL 內 置 的 優 化 程 式 不 會 注 意 這 個, 至 少 在 現 在 不 注 意 第 10 頁

11 4. CREATE PROCEDURE p2 () LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER <-- COMMENT 'A Procedure' SELECT CURRENT_DATE, RAND() FROM t // 下 一 個 子 句 是 SQL SECURITY, 可 以 定 義 為 SQL SECURITY DEFINER 或 SQL SECURITY INVOKER 這 就 進 入 了 許 可 權 控 制 的 領 域 了, 當 然 我 們 在 後 面 將 會 有 測 試 許 可 權 的 例 子 SQL SECURITY DEFINER 意 味 著 在 調 用 時 檢 查 創 建 過 程 用 戶 的 許 可 權 ( 另 一 個 選 項 是 SQL SECURITY INVOKER) 現 在 而 言, 使 用 SQL SECURITY DEFINER 指 令 告 訴 MySQL 伺 服 器 檢 查 創 建 過 程 的 用 戶 就 可 以 了, 當 過 程 已 經 被 調 用, 就 不 檢 查 執 行 調 用 過 程 的 用 戶 了 而 另 一 個 選 項 (INVOKER) 則 是 告 訴 伺 服 器 在 這 一 步 仍 然 要 檢 查 調 用 者 的 許 可 權 5. CREATE PROCEDURE p2 () LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT 'A Procedure' <-- SELECT CURRENT_DATE, RAND() FROM t // COMMENT 'A procedure' 是 一 個 可 選 的 注 釋 說 明 最 後, 注 釋 子 句 會 跟 程 序 定 義 存 儲 在 一 起 這 個 沒 有 固 定 的 標 準, 我 在 文 中 會 指 出 沒 有 固 定 標 準 的 語 句, 不 過 幸 運 的 是 這 些 在 我 們 標 準 的 SQL 中 很 少 6. CREATE PROCEDURE p2 () LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '' SELECT CURRENT_DATE, RAND() FROM t // 上 面 過 程 跟 下 面 語 句 是 等 效 的 : CREATE PROCEDURE p2 () SELECT CURRENT_DATE, RAND() FROM t // 特 徵 子 句 也 有 預 設 值, 如 果 省 略 了 就 相 當 於 : LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''. 第 11 頁

12 一 些 題 外 話 : 調 用 p2()// 的 結 果 mysql> call p2() // CURRENT_DATE RAND() row in set (0.26 sec) Query OK, 0 rows affected (0.26 sec) 當 調 用 過 程 p2 時, 一 個 SELECT 語 句 被 執 行 返 回 我 們 期 望 獲 得 的 亂 數 : 不 會 改 變 的 sql_mode mysql> set sql_mode='ansi' // mysql> create procedure p3()select'a' 'b'// mysql> set sql_mode=''// mysql> call p3()// 'a' 'b' ab MySQL 在 過 程 創 建 時 會 自 動 保 持 運 行 環 境 例 如 : 我 們 需 要 使 用 兩 條 豎 線 來 連 接 字 串 但 是 這 只 有 在 sql mode 為 ansi 的 時 候 才 合 法 如 果 我 們 將 sql mode 改 為 non-ansi, 不 用 擔 心, 它 仍 然 能 工 作, 只 要 它 第 一 次 使 用 時 能 正 常 工 作 Exercise 練 習 Question 問 題 如 果 你 不 介 意 練 習 一 下 的 話, 試 試 能 否 不 看 後 面 的 答 案 就 能 處 理 這 些 請 求 創 建 一 個 過 程, 顯 示 `Hello world` 用 大 約 5 秒 時 間 去 思 考 這 個 問 題, 既 然 你 已 經 學 到 了 這 裏, 這 個 應 該 很 簡 單 當 你 思 考 問 題 的 時 候, 我 們 再 隨 機 選 擇 一 些 剛 才 講 過 的 東 西 復 習 :DETERMINISTIC ( 確 定 性 ) 子 句 是 反 映 輸 出 和 輸 入 依 賴 特 性 的 子 句 調 用 過 程 使 用 CALL 過 程 名 ( 參 數 列 表 ) 方 式 好 了, 我 猜 時 間 也 到 了 Answer 答 案 好 的, 答 案 就 是 在 過 程 體 中 包 含 "SELECT 'Hello, world'" 語 句 第 12 頁

13 mysql> CREATE PROCEDURE p4 () SELECT 'Hello, world' // Query OK, 0 rows affected (0.00 sec) mysql> CALL p4()// Hello, world Hello, world row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Parameters 參 數 讓 我 們 更 進 一 步 的 研 究 怎 麼 在 存 儲 過 程 中 定 義 參 數 1. CREATE PROCEDURE p5 () CREATE PROCEDURE p5 ([IN] name data-type) CREATE PROCEDURE p5 (OUT name data-type) CREATE PROCEDURE p5 (INOUT name data-type)... 回 憶 一 下 前 面 講 過 的 參 數 列 表 必 須 在 存 儲 過 程 名 後 的 括 弧 中 上 面 的 第 一 個 例 子 中 的 參 數 列 表 是 空 的, 第 二 個 例 子 中 有 一 個 輸 入 參 數 這 裏 的 詞 IN 可 選, 因 為 默 認 參 數 為 IN(input) 第 三 個 例 子 中 有 一 個 輸 出 參 數, 第 四 個 例 子 中 有 一 個 參 數, 既 能 作 為 輸 入 也 可 以 作 為 輸 出 第 13 頁

14 IN example 輸 入 的 例 子 mysql> CREATE PROCEDURE p5(p INT) = p // Query OK, 0 rows affected (0.00 sec) mysql> CALL p5(12345)// Query OK, 0 rows affected (0.00 sec) mysql> row in set (0.00 sec) 這 個 IN 的 例 子 演 示 的 是 有 輸 入 參 數 的 過 程 在 過 程 體 中 我 將 會 話 變 數 x 設 定 為 參 數 p 的 值 然 後 調 用 過 程, 將 傳 入 參 數 p 選 擇 顯 示 會 話 變 證 明 我 們 已 經 將 參 數 值 傳 入 OUT example 輸 出 的 例 子 mysql> CREATE PROCEDURE p6 (OUT p INT) -> SET p = -5 // mysql> CALL p6(@y)// mysql> 這 是 另 一 個 例 子 這 次 的 p 是 輸 出 參 數, 然 後 在 程 序 呼 叫 中 將 p 的 值 傳 入 會 話 變 中 在 過 程 體 中, 我 們 給 參 數 賦 值 -5, 在 調 用 後 我 們 可 以 看 出,OUT 是 告 訴 DBMS 值 是 從 過 程 中 傳 出 的 同 樣 我 們 可 以 用 語 句 = -5;". 來 達 到 同 樣 的 效 果 Compound Statements 複 合 語 句 現 在 我 們 展 開 的 詳 細 分 析 一 下 過 程 體 : CREATE PROCEDURE p7 () = 5; = 5; INSERT INTO t VALUES (@a); SELECT s1 FROM t WHERE s1 END; // /* I won't CALL this. 這 個 語 句 將 不 會 被 調 用 */ 第 14 頁

15 完 成 過 程 體 的 構 造 就 是 /END 塊 這 個 /END 語 句 塊 和 Pascal 語 言 中 的 /END 是 基 本 相 同 的, 和 C 語 言 的 框 架 是 很 相 似 的 我 們 可 以 使 用 塊 去 封 裝 多 條 語 句 在 這 個 例 子 中, 我 們 使 用 了 多 條 設 定 會 話 變 數 的 語 句, 然 後 完 成 了 一 些 insert 和 select 語 句 如 果 你 的 過 程 體 中 有 多 條 語 句, 那 麼 你 就 需 要 /END 塊 了 /END 塊 也 被 稱 為 複 合 語 句, 在 這 裏 你 可 以 進 行 變 數 定 義 和 流 程 控 制 The New SQL Statements 新 SQL 語 句 Variables 變 數 在 複 合 語 句 中 聲 明 變 數 的 指 令 是 DECLARE (1) Example with two DECLARE statements 兩 個 DECLARE 語 句 的 例 子 CREATE PROCEDURE p8 () DECLARE a INT; DECLARE b INT; SET a = 5; SET b = 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */ 在 過 程 中 定 義 的 變 數 並 不 是 真 正 的 定 義, 你 只 是 在 /END 塊 內 定 義 了 而 已 ( 譯 注 : 也 就 是 形 參 ) 注 意 這 些 變 數 和 會 話 變 數 不 一 樣, 不 能 使 用 修 飾 你 必 須 清 楚 的 在 /END 塊 中 聲 明 變 數 和 它 們 的 類 型 變 數 一 旦 聲 明, 你 就 能 在 任 何 能 使 用 會 話 變 量 文 字 列 名 的 地 方 使 用 (2) Example with no DEFAULT clause and SET statement 沒 有 默 認 子 句 和 設 定 語 句 的 例 子 CREATE PROCEDURE p9 () DECLARE a INT /* there is no DEFAULT clause */; DECLARE b INT /* there is no DEFAULT clause */; SET a = 5; /* there is a SET statement */ SET b = 5; /* there is a SET statement */ INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */ 有 很 多 初 始 化 變 數 的 方 法 如 果 沒 有 默 認 的 子 句, 那 麼 變 數 的 初 始 值 為 NULL 你 可 以 在 任 何 時 候 使 用 SET 語 句 給 變 數 賦 值 第 15 頁

16 (3) Example with DEFAULT clause 含 有 DEFAULT 子 句 的 例 子 CREATE PROCEDURE p10 () DECLARE a, b INT DEFAULT 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // 我 們 在 這 裏 做 了 一 些 改 變, 但 是 結 果 還 是 一 樣 的 在 這 裏 使 用 了 DEFAULT 子 句 來 設 定 初 始 值, 這 就 不 需 要 把 DECLARE 和 SET 語 句 的 實 現 分 開 了 (4) Example of CALL 調 用 的 例 子 mysql> CALL p10() // s1 * a rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 結 果 顯 示 了 過 程 能 正 常 工 作 (5) Scope 作 用 域 CREATE PROCEDURE p11 () DECLARE x1 CHAR(5) DEFAULT 'outer'; DECLARE x1 CHAR(5) DEFAULT 'inner'; SELECT x1; END; SELECT x1; END; // 現 在 我 們 來 討 論 一 下 作 用 域 的 問 題 例 子 中 有 嵌 套 的 /END 塊, 當 然 這 是 合 法 的 同 時 包 含 兩 個 變 數, 名 字 都 是 x1, 這 樣 也 是 合 法 的 內 部 的 變 數 在 其 作 用 域 內 享 有 更 高 的 優 先 權 當 執 行 到 END 語 句 時, 內 部 變 數 消 失, 此 時 已 經 在 其 作 用 域 外, 變 數 不 再 可 見 了, 因 此 在 存 儲 過 程 外 再 也 不 能 找 到 這 個 聲 明 了 的 變 數, 但 是 你 可 以 通 過 OUT 參 數 或 者 將 其 值 指 派 給 會 話 變 數 來 保 存 其 值 第 16 頁

17 調 用 作 用 域 例 子 的 過 程 : mysql> CALL p11()// x inner x outer 我 們 看 到 的 結 果 時 第 一 個 SELECT 語 句 檢 索 到 最 內 層 的 變 數, 第 二 個 檢 索 到 第 二 層 的 變 數 Conditions and IF-THEN-ELSE 條 件 式 和 IF-THEN-ELSE 1. 現 在 我 們 可 以 寫 一 些 包 含 條 件 式 的 例 子 : CREATE PROCEDURE p12 (IN parameter1 INT) DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END; // 這 裏 是 一 個 包 含 IF 語 句 的 過 程 裏 面 有 兩 個 IF 語 句, 一 個 是 IF 語 句 END IF, 另 一 個 是 IF 語 句 ELSE 語 句 END IF 我 們 可 以 在 這 裏 使 用 複 雜 的 過 程, 但 我 會 儘 量 使 其 簡 單 讓 你 能 更 容 易 弄 清 楚 2. CALL p12 (0) // 我 們 調 用 這 個 過 程, 傳 入 值 為 0, 這 樣 parameter1 的 值 將 為 0 第 17 頁

18 3. CREATE PROCEDURE p12 (IN parameter1 INT) DECLARE variable1 INT; SET variable1 = parameter1 + 1; <-- IF variable1 = 0 THEN INSERT INTO t VALUES (17); IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END; // 這 裏 變 數 variable1 被 賦 值 為 parameter1 加 1 的 值, 所 以 執 行 後 變 數 variable1 為 1 4. CREATE PROCEDURE p12 (IN parameter1 INT) DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN <-- INSERT INTO t VALUES (17); IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END; // 因 為 變 數 variable1 值 為 1, 因 此 條 件 "if variable1 = 0" 為 假,IF END IF 被 跳 過, 沒 有 被 執 行 5. CREATE PROCEDURE p12 (IN parameter1 INT) DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); IF parameter1 = 0 THEN <-- UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END; // 第 18 頁

19 到 第 二 個 IF 條 件, 判 斷 結 果 為 真, 於 是 中 間 語 句 被 執 行 了 6. CREATE PROCEDURE p12 (IN parameter1 INT) DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; <-- ELSE UPDATE t SET s1 = s1 + 2; END; // 因 為 參 數 parameter1 值 等 於 0,UPDATE 語 句 被 執 行 如 果 parameter1 值 為 NULL, 則 下 一 條 UPDATE 語 句 將 被 執 行 現 在 表 t 中 有 兩 行, 他 們 都 包 含 值 5, 所 以 如 果 我 們 調 用 p12, 兩 行 的 值 會 變 成 6 7. mysql> CALL p12(0)// Query OK, 2 rows affected (0.28 sec) mysql> SELECT * FROM t// s rows in set (0.01 sec) 結 果 也 是 我 們 所 期 望 的 那 樣 CASE 指 令 1. CREATE PROCEDURE p13 (IN parameter1 INT) DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (19); END CASE; END; // 第 19 頁

20 如 果 需 要 進 行 更 多 條 件 真 假 的 判 斷 我 們 可 以 使 用 CASE 語 句 CASE 語 句 使 用 和 IF 一 樣 簡 單 我 們 可 以 參 考 上 面 的 例 子 : 2. mysql> CALL p13(1)// Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t// s rows in set (0.00 sec) 執 行 過 程 後, 傳 入 值 1, 如 上 面 例 子, 值 19 被 插 入 到 表 t 中 Question 問 題 問 題 : CALL p13(null) // 的 作 用 是 什 麼? 另 一 個 : 這 個 CALL 語 句 做 了 那 些 動 作? 你 可 以 通 過 執 行 後 觀 察 SELECT 做 了 什 麼, 也 可 以 根 據 代 碼 判 斷, 在 5 秒 內 做 出 Answer 答 案 mysql> CALL p13(null)// Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t// s rows in set (0.00 sec) 答 案 是 當 你 調 用 p13 時,MySQL 插 入 了 另 一 條 包 含 數 值 19 的 記 錄 原 因 是 變 數 variable1 的 值 為 NULL,CASE 語 句 的 ELSE 部 分 就 被 執 行 了 希 望 這 對 大 家 有 意 義 如 果 你 回 答 不 出 來, 沒 有 問 題, 我 們 可 以 繼 續 向 下 走 第 20 頁

21 Loops 迴 圈 語 句 WHILE... END WHILE LOOP... END LOOP REPEAT... END REPEAT GOTO 下 面 我 們 將 會 創 建 一 些 迴 圈 我 們 有 三 種 標 準 的 迴 圈 方 式 :WHILE 迴 圈,LOOP 迴 圈 以 及 REPEAT 迴 圈 還 有 一 種 非 標 準 的 迴 圈 方 式 :GO TO( 譯 者 語 : 最 好 不 要 用 吧, 用 了 就 使 流 程 混 亂 ) WHILE... END WHILE CREATE PROCEDURE p14 () DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; // 這 是 WHILE 迴 圈 的 方 式 我 很 喜 歡 這 種 方 式, 它 跟 IF 語 句 相 似, 因 此 不 需 要 掌 握 很 多 新 的 語 法 這 裏 的 INSERT 和 SET 語 句 在 WHILE 和 END WHILE 之 間, 當 變 數 v 大 於 5 的 時 候 迴 圈 將 會 退 出 使 用 "SET v = 0;" 語 句 使 為 了 防 止 一 個 常 見 的 錯 誤, 如 果 沒 有 初 始 化, 默 認 變 數 值 為 NULL, 而 NULL 和 任 何 值 操 作 結 果 都 為 NULL WHILE... END WHILE example mysql> CALL p14()// Query OK, 1 row affected (0.00 sec) 以 上 就 是 調 用 過 程 p14 的 結 果 不 用 關 注 系 統 返 回 是 "one row affected" 還 是 "five rows affected", 因 為 這 裏 的 計 數 只 對 最 後 一 個 INSERT 動 作 進 行 計 數 第 21 頁

22 WHILE... END WHILE example: CALL mysql> select * from t; // s rows in set (0.00 sec) 調 用 後 可 以 看 到 程 式 向 資 料 庫 中 插 入 了 5 行 REPEAT... END REPEAT CREATE PROCEDURE p15 () DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 END REPEAT; END; // 這 是 一 個 REPEAT 迴 圈 的 例 子, 功 能 和 前 面 WHILE 迴 圈 一 樣 區 別 在 於 它 在 執 行 後 檢 查 結 果, 而 WHILE 則 是 執 行 前 檢 查 ( 譯 者 語 : 可 能 等 同 於 DO WHILE 吧 ) REPEAT... END REPEAT: look at the UNTIL: UNTIL 的 作 用 CREATE PROCEDURE p15 () DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 <-- END REPEAT; END; // 注 意 到 UNTIL 語 句 後 面 沒 有 分 號, 在 這 裏 可 以 不 寫 分 號, 當 然 你 加 上 額 外 的 分 號 更 好 第 22 頁

23 REPEAT... END REPEAT: calling : 調 用 mysql> CALL p15()// Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t// COUNT(*) row in set (0.00 sec) 我 們 可 以 看 到 調 用 p15 過 程 後 又 插 入 了 5 行 記 錄 LOOP... END LOOP CREATE PROCEDURE p16 () DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END LOOP; END; // 以 上 是 LOOP 迴 圈 的 例 子 LOOP 迴 圈 不 需 要 初 始 條 件, 這 點 和 WHILE 迴 圈 相 似, 同 時 它 又 和 REPEAT 迴 圈 一 樣 也 不 需 要 結 束 條 件 LOOP... END LOOP: with IF and LEAVE 包 含 IF 和 LEAVE 的 LOOP 迴 圈 CREATE PROCEDURE p16 () DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN <-- LEAVE loop_label; END LOOP; END; // 在 迴 圈 內 部 加 入 IF 語 句, 在 IF 語 句 中 包 含 LEAVE 語 句 這 裏 LEAVE 語 句 的 意 義 是 離 開 循 環 LEAVE 的 語 法 是 LEAVE 加 迴 圈 語 句 標 號, 關 於 迴 圈 語 句 的 標 號 問 題 我 會 在 後 面 進 一 步 講 解 第 23 頁

24 LOOP... END LOOP: calling : 調 用 mysql> CALL p16()// Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t// COUNT(*) row in set (0.00 sec) 調 用 過 程 p16 後, 結 果 是 另 5 行 被 插 入 表 t 中 Labels 標 號 CREATE PROCEDURE p17 () label_1: label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT; label_4: LOOP LEAVE label_4; END LOOP; END; // 最 後 一 個 迴 圈 例 子 中 我 使 用 了 語 句 標 號 現 在 這 裏 有 一 個 包 含 4 個 語 句 標 號 的 過 程 的 例 子 我 們 可 以 在 WHILE REPEAT 或 者 LOOP 語 句 前 使 用 語 句 標 號, 語 句 標 號 只 能 在 合 法 的 語 句 前 面 使 用 因 此 "LEAVE label_3" 意 味 著 離 開 語 句 標 號 名 定 義 為 label_3 的 語 句 或 複 合 語 句 End Labels 標 號 結 束 符 CREATE PROCEDURE p18 () label_1: label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE label_2; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT label_3 ; label_4: LOOP LEAVE label_4; END LOOP label_4 ; END label_1 ; // 你 也 可 以 在 語 句 結 束 時 使 用 語 句 標 號, 和 在 開 頭 時 使 用 一 樣 這 些 標 號 結 束 符 並 不 是 十 分 有 用 它 們 是 可 選 的 如 果 你 需 要, 他 們 必 須 和 開 始 定 義 的 標 號 名 字 一 樣 當 然 為 了 有 良 好 的 編 程 習 慣, 方 便 他 人 閱 讀, 最 好 還 是 使 用 標 號 結 束 符 第 24 頁

25 LEAVE and Labels 跳 出 和 標 號 CREATE PROCEDURE p19 (parameter1 CHAR) label_1: label_2: label_3: IF parameter1 IS NOT NULL THEN IF parameter1 = 'a' THEN LEAVE label_1; ELSE IF parameter1 = 'b' THEN LEAVE label_2; ELSE LEAVE label_3; END; END; END; END;// LEAVE 語 句 使 程 式 跳 出 複 雜 的 複 合 語 句 ITERATE 迭 代 如 果 目 標 是 ITERATE( 迭 代 ) 語 句 的 話, 就 必 須 用 到 LEAVE 語 句 CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END LOOP; END; // ITERATE( 迭 代 ) 語 句 和 LEAVE 語 句 一 樣 也 是 在 迴 圈 內 部 的 迴 圈 引 用, 它 有 點 像 C 語 言 中 的 Continue, 同 樣 它 可 以 出 現 在 複 合 語 句 中, 引 用 複 合 語 句 標 號,ITERATE( 迭 代 ) 意 思 是 重 新 開 始 複 合 語 句 那 我 們 啟 動 並 觀 察 下 面 這 個 迴 圈, 這 是 個 需 要 迭 代 過 程 的 迴 圈 : 第 25 頁

26 ITERATE: Walking through the loop 深 入 迴 圈 CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END LOOP; END; // 讓 這 個 已 經 定 義 了 標 號 的 迴 圈 運 行 起 來 ITERATE: Walking through the loop CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN <-- SET v = v + 1; ITERATE loop_label; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END LOOP; END; // v 的 值 變 成 3, 然 後 我 們 把 它 增 加 到 4 ITERATE: walking through the loop CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; <-- INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN 第 26 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

27 LEAVE loop_label; END LOOP; END; // 然 後 開 始 ITERATE( 迭 代 ) 過 程 ITERATE: walking through the loop CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END LOOP; END; // 這 裏 的 ITERATE( 迭 代 ) 讓 迴 圈 又 回 到 了 迴 圈 的 頭 部 ITERATE: walking through the loop CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; <-- END LOOP; END; // 當 v 的 值 變 為 5 時, 程 式 將 執 行 LEAVE 語 句 ITERATE: walking through the loop CREATE PROCEDURE p20 () DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; 第 27 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

28 ITERATE loop_label; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END LOOP; END; // <-- LEAVE 的 結 果 就 是 跳 出 迴 圈, 使 運 行 指 令 到 達 複 合 語 句 的 最 後 一 步 GOTO CREATE PROCEDURE p LABEL label_name;... GOTO label_name;... END; MySQL 的 存 儲 過 程 中 可 以 使 用 GOTO 語 句 雖 然 這 不 是 標 準 SQL 語 句, 而 且 在 這 裏 建 立 標 號 的 方 法 也 和 慣 例 中 的 不 一 樣 由 於 為 了 和 其 他 DBMS 相 容, 這 個 語 句 會 慢 慢 被 淘 汰, 所 以 我 們 在 MySQL 參 考 手 冊 中 沒 有 提 及 Grand combination 大 組 合 CREATE PROCEDURE p21 (IN parameter_1 INT, OUT parameter_2 INT) LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER DECLARE v INT; label goto_label; start_label: LOOP IF v = v THEN LEAVE start_label; ELSE ITERATE start_label; END LOOP start_label; REPEAT WHILE 1 = 0 DO END; END WHILE; UNTIL v = v END REPEAT; GOTO goto_label; END;// 上 面 例 子 中 的 語 句 包 含 了 我 們 之 前 講 的 所 有 語 法, 包 括 參 數 列 表, 特 性 參 數,/END 塊 複 合 語 句, 變 數 聲 明,IF,WHILE,LOOP,REPEAT,LEAVE,ITERATE,GOTO 這 是 一 個 荒 謬 的 存 儲 過 程, 我 不 會 運 行 它, 因 為 裏 面 有 無 盡 的 迴 圈 但 是 裏 面 的 語 法 卻 十 分 合 法 這 些 是 新 的 流 程 控 制 和 變 數 聲 明 語 句 下 面 我 們 將 要 接 觸 更 多 新 的 東 西 第 28 頁

29 Error Handling 異 常 處 理 後 面 幾 頁 的 資 訊 摘 要 Sample Problem 問 題 樣 例 Handlers 異 常 處 理 器 Conditions 條 件 好 了, 我 們 現 在 要 講 的 是 異 常 處 理 1. Sample Problem: Log Of Failures 問 題 樣 例 : 故 障 記 錄 當 INSERT 失 敗 時, 我 希 望 能 將 其 記 錄 在 日 誌 檔 中 我 們 用 來 展 示 出 錯 處 理 的 問 題 樣 例 是 很 普 通 的 我 希 望 得 到 錯 誤 的 記 錄 當 INSERT 失 敗 時, 我 想 在 另 一 個 檔 中 記 下 這 些 錯 誤 的 資 訊, 例 如 出 錯 時 間, 出 錯 原 因 等 我 對 插 入 特 別 感 興 趣 的 原 因 是 它 將 違 反 外 鍵 關 聯 的 約 束 2. Sample Problem: Log Of Failures (2) mysql> CREATE TABLE t2 s1 INT, PRIMARY KEY (s1)) engine=innodb;// mysql> CREATE TABLE t3 (s1 INT, KEY (s1), FOREIGN KEY (s1) REFERENCES t2 (s1)) engine=innodb;// mysql> INSERT INTO t3 VALUES (5);//... ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails ( 這 裏 顯 示 的 是 系 統 的 出 錯 資 訊 ) 我 開 始 要 創 建 一 個 主 鍵 表, 以 及 一 個 外 鍵 表 我 們 使 用 的 是 InnoDB, 因 此 外 鍵 關 聯 檢 查 是 打 開 的 然 後 當 我 向 外 鍵 表 中 插 入 非 主 鍵 表 中 的 值 時, 動 作 將 會 失 敗 當 然 這 種 條 件 下 可 以 很 快 找 到 錯 誤 號 Sample Problem: Log Of Failures CREATE TABLE error_log (error_message CHAR(80))// 下 一 步 就 是 建 立 一 個 在 做 插 入 動 作 出 錯 時 存 儲 錯 誤 的 表 4. Sample Problem: Log Of Errors CREATE PROCEDURE p22 (parameter1 INT) DECLARE EXIT HANDLER FOR 1216 INSERT INTO error_log VALUES (CONCAT('Time: ',current_date, '. Foreign Key Reference Failure For Value = ',parameter1)); INSERT INTO t3 VALUES (parameter1); END;// 上 面 就 是 我 們 的 程 式 這 裏 的 第 一 個 語 句 DECLARE EXIT HANDLER 是 用 來 處 理 異 常 的 意 思 是 如 果 錯 誤 1215 發 生 了, 這 個 程 式 將 會 在 錯 誤 記 錄 表 中 插 入 一 行 EXIT 意 思 是 當 動 作 成 功 提 交 後 退 出 這 個 複 合 語 句 第 29 頁

30 5. Sample Problem: Log Of Errors CALL p22 (5) // 調 用 這 個 存 儲 過 程 會 失 敗, 這 很 正 常, 因 為 5 值 並 沒 有 在 主 鍵 表 中 出 現 但 是 沒 有 錯 誤 資 訊 返 回 因 為 出 錯 處 理 已 經 包 含 在 過 程 中 了 t3 表 中 沒 有 增 加 任 何 東 西, 但 是 error_log 表 中 記 錄 下 了 一 些 資 訊, 這 就 告 訴 我 們 INSERT into table t3 動 作 失 敗 DECLARE HANDLER syntax 聲 明 異 常 處 理 的 語 法 DECLARE { EXIT CONTINUE } HANDLER FOR { error-number { SQLSTATE error-string } condition } SQL statement 上 面 就 是 錯 誤 處 理 的 用 法, 也 就 是 一 段 當 程 式 出 錯 後 自 動 觸 發 的 代 碼 MySQL 允 許 兩 種 處 理 器, 一 種 是 EXIT 處 理, 我 們 剛 才 所 用 的 就 是 這 種 另 一 種 就 是 我 們 將 要 演 示 的,CONTINUE 處 理, 它 跟 EXIT 處 理 類 似, 不 同 在 於 它 執 行 後, 原 主 程 序 仍 然 繼 續 運 行, 那 麼 這 個 複 合 語 句 就 沒 有 出 口 了 1. DECLARE CONTINUE HANDLER example CONTINUE 處 理 例 子 CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; = 1; INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); = 3; END;// 這 是 MySQL 參 考 手 冊 上 的 CONTINUE 處 理 的 例 子, 這 個 例 子 十 分 好, 所 以 我 把 它 拷 貝 到 這 裏 通 過 這 個 例 子 我 們 可 以 看 出 CONTINUE 處 理 是 如 何 工 作 的 2. DECLARE CONTINUE HANDLER 聲 明 CONTINUE 異 常 處 理 CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; <-- = 1; INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); = 3; END;// 這 次 我 將 為 SQLSTATE 值 定 義 一 個 處 理 程 式 還 記 得 前 面 我 們 使 用 的 MySQL 錯 誤 代 碼 1216 嗎? 事 實 上 這 裏 的 23000SQLSTATE 是 更 常 用 的, 當 外 鍵 約 束 出 錯 或 主 鍵 約 束 出 錯 就 被 調 用 了 第 30 頁

31 3. DECLARE CONTINUE HANDLER CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; = 1; <-- INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); = 3; END;// 這 個 存 儲 過 程 的 第 一 個 執 行 的 語 句 是 = 1" 4. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; = 1; INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); <-- = 3; END;// 運 行 後 值 1 被 插 入 到 主 鍵 表 中 5. DECLARE CONTINUE HANDLER CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; = 1; INSERT INTO t4 VALUES (1); = 2; <-- INSERT INTO t4 VALUES (1); = 3; END;// 然 的 值 變 為 2 6. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; = 1; 第 31 頁

32 INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); <-- = 3; END;// 然 後 程 式 嘗 試 再 次 往 主 鍵 表 中 插 入 數 值, 但 失 敗 了, 因 為 主 鍵 有 唯 一 性 限 制 7. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; <-- = 1; INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); = 3; END;// 由 於 插 入 失 敗, 錯 誤 處 理 程 式 被 觸 發, 開 始 進 行 錯 誤 處 理 下 一 個 執 行 的 語 句 是 錯 誤 處 理 的 語 句,@x2 被 設 為 2 8. DECLARE CONTINUE HANDLER example CREATE TABLE t4 (s1 int,primary key(s1));// CREATE PROCEDURE p23 () DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' = 1; = 1; INSERT INTO t4 VALUES (1); = 2; INSERT INTO t4 VALUES (1); = 3; <-- END;// 到 這 裏 並 沒 有 結 束, 因 為 這 是 CONTINUE 異 常 處 理 所 以 執 行 返 回 到 失 敗 的 插 入 語 句 之 後, 繼 續 執 行 設 定 為 3 動 作 9. DECLARE CONTINUE HANDLER example mysql> CALL p23()// Query OK, 0 rows affected (0.00 sec) @x row in set (0.00 sec) 運 行 過 程 後 我 們 觀 的 值, 很 確 定 的 可 以 知 道 是 3, 觀 的 值, 為 1 從 這 裏 可 以 判 斷 程 式 運 行 無 誤, 完 全 按 照 我 們 的 思 路 進 行 大 家 可 以 花 點 時 間 去 調 整 錯 誤 處 理 器, 讓 檢 查 放 在 語 句 段 的 首 部, 而 不 是 放 在 可 能 出 現 錯 誤 的 地 方, 雖 然 那 樣 看 起 來 程 式 很 紊 亂, 跳 來 跳 去 的 感 覺 但 是 這 樣 的 代 碼 很 安 全 也 很 清 楚 第 32 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

33 1. DECLARE CONDITION CREATE PROCEDURE p24 () DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000'; DECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK; START TRANSACTION; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (1); COMMIT; END; // 這 是 另 外 一 個 錯 誤 處 理 的 例 子, 在 前 面 的 基 礎 上 修 改 的 事 實 上 你 可 給 SQLSTATE 或 者 錯 誤 代 碼 其 他 的 名 字, 你 就 可 以 在 處 理 中 使 用 自 己 定 義 的 名 字 了 下 面 看 看 它 是 怎 麼 實 現 的 : 我 把 表 t2 定 義 為 InnoDB 表, 所 以 對 這 個 表 的 插 入 操 作 都 會 ROLLBACK( 回 滾 ),ROLLBACK ( 回 滾 事 務 ) 也 是 恰 好 會 發 生 的 因 為 對 主 鍵 插 入 兩 個 同 樣 的 值 會 導 致 SQLSTATE 錯 誤 發 生, 這 裏 SQLSTATE 是 約 束 錯 誤 2. DECLARE CONDITION 聲 明 條 件 CREATE PROCEDURE p24 () DECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000'; DECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK; START TRANSACTION; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (1); COMMIT; END; // 這 個 約 束 錯 誤 會 導 致 ROLLBACK( 回 滾 事 務 ) 和 SQLSTATE 錯 誤 發 生 3. DECLARE CONDITION mysql> CALL p24()// Query OK, 0 rows affected (0.28 sec) mysql> SELECT * FROM t2// Empty set (0.00 sec) 我 們 調 用 這 個 存 儲 過 程 看 看 結 果 是 什 麼, 從 上 面 結 果 我 們 看 到 表 t2 沒 有 插 入 任 何 記 錄 全 部 事 務 都 回 滾 了 這 正 是 我 們 想 要 的 4. DECLARE CONDITION mysql> CREATE PROCEDURE p9 () -> -> DECLARE EXIT HANDLER FOR NOT FOUND END; -> DECLARE EXIT HANDLER FOR SQLEXCEPTION END; -> DECLARE EXIT HANDLER FOR SQLWARNING END; -> END;// Query OK, 0 rows affected (0.00 sec) 這 裏 是 三 個 預 聲 明 的 條 件 : NOT FOUND ( 找 不 到 行 ), SQLEXCEPTION ( 錯 誤 ), 第 33 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

34 SQLWARNING ( 警 告 或 注 釋 ) 因 為 它 們 是 預 聲 明 的, 因 此 不 需 要 聲 明 條 件 就 可 以 使 用 不 過 如 果 你 去 做 這 樣 的 聲 明 :"DECLARE SQLEXCEPTION CONDITION...", 你 將 會 得 到 錯 誤 資 訊 提 示 Cursors 游 標 游 標 實 現 功 能 摘 要 : DECLARE cursor-name CURSOR FOR SELECT...; OPEN cursor-name; FETCH cursor-name INTO variable [, variable]; CLOSE cursor-name; 現 在 我 們 開 始 著 眼 游 標 了 雖 然 我 們 的 存 儲 過 程 中 的 游 標 語 法 還 並 沒 有 完 整 的 實 現, 但 是 已 經 可 以 完 成 基 本 的 事 務 如 聲 明 游 標, 打 開 游 標, 從 游 標 裏 讀 取, 關 閉 游 標 1. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 我 們 看 一 下 包 含 游 標 的 存 儲 過 程 的 新 例 子 2. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; <-- DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 這 個 過 程 開 始 聲 明 了 三 個 變 數 附 帶 說 一 下, 順 序 是 十 分 重 要 的 首 先 要 進 行 變 數 聲 明, 然 後 聲 明 條 件, 隨 後 聲 明 游 標, 再 後 面 才 是 聲 明 錯 誤 處 理 器 如 果 你 沒 有 按 順 序 聲 明, 系 統 會 提 示 錯 誤 資 訊 第 34 頁

35 3. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; <-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 程 式 第 二 步 聲 明 了 游 標 cur_1, 如 果 你 使 用 過 嵌 入 式 SQL 的 話, 就 知 道 這 和 嵌 入 式 SQL 差 不 多 4. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND <-- SET b = 1; <-- OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 最 後 進 行 的 是 錯 誤 處 理 器 的 聲 明 這 個 CONTINUE 處 理 沒 有 引 用 SQL 錯 誤 代 碼 和 SQLSTATE 值 它 使 用 的 是 NOT FOUND 系 統 返 回 值, 這 和 SQLSTATE 是 一 樣 的 5. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; <-- REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 第 35 頁

36 過 程 第 一 個 可 執 行 的 語 句 是 OPEN SELECT s1 FROM t, 返 回 一 個 結 果 集 cur_1, 它 與 SELECT s1 FROM t 語 句 是 關 聯 的, 過 程 將 執 行 6. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; <-- UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 這 裏 第 一 個 FETCH 語 句 會 獲 得 一 行 從 SELECT 產 生 的 結 果 集 中 檢 索 出 來 的 值, 然 而 表 t 中 有 多 行, 因 此 這 個 語 句 會 被 執 行 多 次, 當 然 這 是 因 為 語 句 在 迴 圈 塊 內 7. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; <-- OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 最 後 當 MySQL 的 FETCH 沒 有 獲 得 行 時,CONTINUE 處 理 被 觸 發, 將 變 數 b 賦 值 為 1 8. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; <-- SET return_val = a; END;// 第 36 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

37 到 了 這 一 步 UNTIL b=1 條 件 就 為 真, 迴 圈 結 束 在 這 裏 我 們 可 以 自 己 編 寫 代 碼 關 閉 游 標, 也 可 以 由 系 統 執 行, 系 統 會 在 複 合 語 句 結 束 時 自 動 關 閉 游 標, 但 是 最 好 不 要 太 依 賴 系 統 的 自 動 關 閉 行 為 ( 譯 注 : 這 可 能 跟 Java 的 Gc 一 樣, 不 可 信 ) 9. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; <-- END;// 這 個 常 式 中 我 們 為 輸 出 參 數 指 派 了 一 個 局 部 變 數, 這 樣 在 過 程 結 束 後 的 結 果 仍 能 使 用 10. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// mysql> CALL p25(@return_val)// Query OK, 0 rows affected (0.00 sec) mysql> row in set (0.00 sec) 上 面 是 程 序 呼 叫 後 的 結 果 可 以 看 到 return_val 參 數 獲 得 了 數 值 5, 因 為 這 是 表 t 的 最 後 一 行 由 此 可 以 知 道 游 標 工 作 正 常, 出 錯 處 理 也 工 作 正 常 第 37 頁

38 Cursor Characteristics 游 標 的 特 性 摘 要 : READ ONLY 唯 讀 屬 性 NOT SCROLLABLE 順 序 讀 取 ASENSITIVE 敏 感 在 5.0 版 的 MySQL 中, 你 只 可 以 從 游 標 中 取 值, 不 能 對 其 進 行 更 新 因 為 游 標 是 (READ ONLY) 唯 讀 的 你 可 以 這 樣 做 : FETCH cursor1 INTO variable1; UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1; 游 標 也 是 不 可 以 滾 動 的, 只 允 許 逐 一 讀 取 下 一 行, 不 能 在 結 果 集 中 前 進 或 後 退 下 面 代 碼 就 是 錯 誤 的 : FETCH PRIOR cursor1 INTO variable1; FETCH ABSOLUTE 55 cursor1 INTO variable1; 同 時 也 不 允 許 在 已 打 開 游 標 進 行 操 作 的 表 上 執 行 updates 事 務, 因 為 游 標 是 (ASENSITIVE) 敏 感 的 因 為 如 果 你 不 阻 止 update 事 務, 那 就 不 知 道 結 果 會 變 成 什 麼 如 果 你 使 用 的 是 InnoDB 而 不 是 MyISAM 存 儲 引 擎 的 話, 結 果 也 會 不 一 樣 Security 安 全 措 施 摘 要 Privileges (1) CREATE ROUTINE Privileges (2) EXECUTE Privileges (3) GRANT SHOW ROUTINE? Privileges (4) INVOKERS AND DEFINERS 這 裏 我 們 要 討 論 一 些 關 於 特 權 和 安 全 相 關 的 問 題 但 因 為 在 MySQL 安 全 措 施 的 功 能 並 沒 有 完 全, 所 以 我 們 不 會 對 其 進 行 過 多 討 論 2. Privileges CREATE ROUTINE GRANT CREATE ROUTINE ON database-name. * TO user(s) [WITH GRANT OPTION]; 現 在 用 root 就 可 以 了 在 這 裏 要 介 紹 的 特 權 是 CREATE ROUTINE, 它 不 僅 同 其 他 特 權 一 樣 可 以 創 建 存 儲 過 程 和 函 數, 還 可 以 創 建 視 圖 和 表 Root 用 戶 擁 有 這 種 特 權, 同 時 還 有 ALTER ROUTINE 特 權 2. Privileges EXECUTE GRANT EXECUTE ON p TO peter [WITH GRANT OPTION]; 上 面 的 特 權 是 決 定 你 是 否 可 以 使 用 或 執 行 存 儲 過 程 的 特 權, 過 程 創 建 者 默 認 擁 有 這 個 特 權 3. Privileges SHOW ROUTINE? GRANT SHOW ROUTINE ON db6.* TO joey [WITH GRANT OPTION]; 因 為 我 們 已 經 有 控 制 視 圖 的 特 權 了 :GRANT SHOW VIEW 所 以 在 這 個 基 礎 上, 為 了 保 證 相 容, 日 後 可 能 會 添 加 GRANT SHOW ROUTINE 特 權 這 樣 做 是 不 太 符 合 標 準 的, 在 寫 本 書 的 時 候, 第 38 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

39 MySQL 還 沒 實 現 這 個 功 能 4. Privileges Invokers and Definers 特 權 調 用 者 和 定 義 者 CREATE PROCEDURE p26 () SQL SECURITY INVOKER SELECT COUNT(*) FROM t // CREATE PROCEDURE p27 () SQL SECURITY DEFINER SELECT COUNT(*) FROM t // GRANT INSERT ON db5.* TO peter; // 現 在 我 們 測 試 一 下 SQL SECURITY 子 句 吧 Security 是 我 們 前 面 提 到 的 程 式 特 性 的 一 部 分 你 是 root 用 戶, 將 插 入 權 賦 給 了 peter 然 後 使 用 peter 登 陸 進 行 新 的 工 作, 我 們 看 看 peter 可 以 怎 麼 使 用 存 儲 過 程, 注 意 :peter 沒 有 對 表 t 的 select 權 力, 只 有 root 用 戶 有 5. Privileges Invokers and Definers /* Logged on with current_user = peter */ 使 用 帳 戶 peter 登 陸 mysql> CALL p26(); ERROR 1142 (42000): select command denied to user 'peter'@'localhost' for table 't' mysql> CALL p27(); COUNT(*) row in set (0.00 sec) 當 peter 嘗 試 調 用 含 有 調 用 保 密 措 施 的 過 程 p26 時 會 失 敗 那 是 因 為 peter 沒 有 對 表 的 select 的 權 力 但 是 當 petre 調 用 含 有 定 義 保 密 措 施 的 過 程 時 就 能 成 功 原 因 是 root 有 select 權 力,Peter 有 root 的 權 力, 因 此 過 程 可 以 執 行 第 39 頁

40 Functions 函 數 Summary: 摘 要 CREATE FUNCTION Limitations of functions 函 數 的 限 制 我 們 已 經 很 清 楚 可 以 在 存 儲 過 程 中 使 用 的 元 素 了 下 面 我 要 講 的 是 前 面 沒 有 提 到 的 函 數 CREATE FUNCTION 創 建 函 數 CREATE FUNCTION factorial (n DECIMAL(3,0)) RETURNS DECIMAL(20,0) DETERMINISTIC DECLARE factorial DECIMAL(20,0) DEFAULT 1; DECLARE counter DECIMAL(3,0); SET counter = n; factorial_loop: REPEAT SET factorial = factorial * counter; SET counter = counter - 1; UNTIL counter = 1 END REPEAT; RETURN factorial; END // ( 代 碼 來 源 :"Understanding SQL's stored procedures", 這 裏 只 是 作 為 例 子 引 用 ) 函 數 跟 過 程 很 相 似, 唯 一 需 要 指 出 的 語 法 上 的 不 同 就 是 創 建 函 數 後 必 須 有 RETURN 語 句 返 回 函 數 指 定 的 類 型 值 這 個 例 子 來 自 Jim Melton 的 大 作, 他 是 SQL standard committee 的 成 員, "Understanding SQL's stored procedures" 的 作 者 原 例 在 書 的 223 頁 我 決 定 使 用 這 個 例 子 是 因 為 它 的 規 範 性 2. Examples INSERT INTO t VALUES (factorial(pi)) // SELECT s1, factorial (s1) FROM t // UPDATE t SET s1 = factorial(s1) WHERE factorial(s1) < 5 // 上 面 就 是 我 們 需 要 的 函 數, 把 它 放 到 SQL 語 句 中 跟 其 他 函 數 看 起 來 是 一 樣 的 如 果 能 很 好 的 處 理, 函 數 將 是 美 妙 的, 就 像 樂 曲 中 的 小 調 一 樣 不 過, 它 們 也 有 缺 陷, 那 就 是 不 能 在 函 數 中 訪 問 表, 這 使 它 們 不 如 存 儲 過 程 強 大 3. Limitations 限 制 Illegal: 非 法 聲 明 : ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL LOCK OPTIMIZE REPAIR REPLACE REVOKE ROLLBACK SAVEPOINT 'SELECT FROM table' 'SET system variable' 'SET TRANSACTION' SHOW 'START TRANSACTION' TRUNCATE UPDATE 不 能 訪 問 表 的 限 制 削 弱 了 函 數 的 功 能, 因 此 你 不 能 夠 進 行 資 料 操 作 資 料 描 述 特 權 轉 化 或 是 事 務 控 制 但 我 們 的 工 作 主 要 是 靠 這 些, 也 許 以 後 會 支 援 這 些 特 性 吧 第 40 頁

41 4. Limitations 合 法 聲 明 : ' END' DECLARE IF ITERATE LOOP REPEAT RETURN 'SET declared variable' WHILE 利 用 函 數 你 能 做 的 全 部 就 是 設 置 變 數, 然 後 在 控 制 流 語 句 中 使 用 它 們 實 際 上 這 個 功 能 很 強 大, 但 是 離 人 們 想 要 的 卻 還 很 遠 Metadata 元 數 據 摘 要 : SHOW CREATE PROCEDURE / SHOW CREATE FUNCTION SHOW PROCEDURE STATUS / SHOW FUNCTION STATUS SELECT from mysql.proc SELECT from information_schema 到 這 裏 我 們 已 經 創 建 了 很 多 過 程 了, 它 們 也 都 保 存 在 MySQL 資 料 庫 中 我 們 如 果 要 查 看 MySQL 實 際 上 保 存 了 什 麼 資 訊, 有 四 種 實 現 方 法, 兩 種 使 用 SHOW 語 句, 兩 種 使 用 SELECT 語 句 1. Show mysql> show create procedure p6// Procedure sql_mode Create Procedure p6 CREATE PROCEDURE `db5`.`p6`(out p int) set p = row in set (0.00 sec) 第 一 種 獲 得 元 資 料 資 訊 的 方 法 是 執 行 SHOW CREATE PROCEDURE 這 同 SHOW CREATE TABLE 以 及 其 他 類 似 MySQL 語 句 一 樣 它 並 不 返 回 你 創 建 過 程 時 設 定 的 返 回 值, 但 在 大 部 分 情 況 下 已 經 夠 用 了 2. Show mysql> SHOW PROCEDURE STATUS LIKE 'p6'// Db Name Type Definer db5 p6 PROCEDURE root@localhost row in set (0.01 sec) 第 二 種 獲 得 metadata 資 訊 的 方 法 是 執 行 SHOW PROCEDURE STATUS 這 種 方 法 可 以 返 回 更 多 資 訊 的 細 節 第 41 頁

42 3. SELECT from mysql.proc SELECT * FROM mysql.proc WHERE name = 'p6'// db name type specific_name db5 p6 PROCEDURE p row in set (0.00 sec) 第 三 種 方 法 是 執 行 SELECT 語 句, 它 能 提 供 最 多 的 資 訊 4. SELECT from information_schema: 我 最 喜 歡 的 方 式 第 四 種 方 法 是 "SELECT... FROM information_schema...." 我 特 別 傾 向 使 用 "ANSI/ISO 標 準 " 的 方 式 去 完 成 工 作 我 相 信 這 是 種 好 實 現 方 式, 因 為 其 他 方 式 可 能 會 出 現 錯 誤 當 然 有 不 同 MySQL 用 戶 會 堅 持 不 同 的 觀 點, 也 有 不 同 理 由, 認 真 的 持 懷 疑 態 度 的 看 看 這 些 理 由 1. 其 他 DBMS, 例 如 SQL Server 2000, 使 用 information_schema 只 有 MySQL 有 SHOW 方 式 2. 我 們 訪 問 mysql.proc 的 特 權 是 沒 有 保 障 的, 因 為 我 們 有 訪 問 information_schema 視 圖 的 特 權, 每 個 用 戶 都 有 內 隱 的 對 information_schema 資 料 庫 的 SELECT 特 權 3.SELECT 功 能 很 多, 可 以 計 算 運 算 式, 分 組, 排 序, 產 生 可 以 獲 取 資 訊 的 結 果 集 而 這 些 功 能 SHOW 沒 有 現 在 瞭 解 我 喜 歡 它 的 原 因 了 吧, 那 下 面 我 們 舉 幾 個 簡 單 的 例 子 來 演 示 一 下 首 先 我 會 使 用 SELECT information_schema 來 顯 示 information_schema 常 式 中 有 哪 些 列 mysql> SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_NAME = 'ROUTINES';// TABLE_NAME COLUMN_NAME COLUMN_TYPE ROUTINES SPECIFIC_NAME varchar(64) ROUTINES ROUTINE_CATALOG longtext ROUTINES ROUTINE_SCHEMA varchar(64) ROUTINES ROUTINE_NAME varchar(64) ROUTINES ROUTINE_TYPE varchar(9) ROUTINES DTD_IDENTIFIER varchar(64) ROUTINES ROUTINE_BODY varchar(8) ROUTINES ROUTINE_DEFINITION longtext ROUTINES EXTERNAL_NAME varchar(64) ROUTINES EXTERNAL_LANGUAGE varchar(64) ROUTINES PARAMETER_STYLE varchar(8) ROUTINES IS_DETERMINISTIC varchar(3) ROUTINES SQL_DATA_ACCESS varchar(64) ROUTINES SQL_PATH varchar(64) ROUTINES SECURITY_TYPE varchar(7) ROUTINES CREATED varbinary(19) ROUTINES LAST_ALTERED varbinary(19) ROUTINES SQL_MODE longtext ROUTINES ROUTINE_COMMENT varchar(64) ROUTINES DEFINER varchar(77) rows in set (0.01 sec) 漂 亮 吧? 當 我 們 想 要 查 看 information_schema 視 圖 時, 我 們 從 information_schema 中 select 資 訊, 第 42 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注

43 就 跟 從 TABLES 和 COLUMNS 獲 取 一 樣 獲 取 的 是 元 資 料 的 資 料 元 素 這 裏 我 們 看 到 的 是 我 在 資 料 庫 db6 中 定 義 的 存 儲 過 程 mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES -> WHERE ROUTINE_SCHEMA = 'db6';// COUNT(*) row in set (0.02 sec) 現 在 進 一 步 看 看 我 們 第 一 個 創 建 的 過 程 p1, 我 們 重 新 格 式 mysql 用 戶 端 輸 出 視 窗 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA p19 NULL p19 ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER p19 PROCEDURE NULL ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME SQL select * from t NULL EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC NULL SQL NO SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED CONTAINS SQL NULL DEFINER CREATED LAST_ALTERED SQL_MODE :00: :00:26 ROUTINE_COMMENT DEFINER root@localhost Access control for the ROUTINE_DEFINITION column ROUTINE_DEFINITION 列 的 訪 問 控 制 在 INFORMATION_SCHEMA 中 的 ROUTINE_DEFINITION 列 是 由 過 程 或 函 數 組 成 過 程 體 獲 得 的 這 裏 可 能 會 有 敏 感 資 訊, 因 此 只 對 過 程 創 建 者 可 見 CURRENT_USER <> INFORMATION_SCHEMA.ROUTINES.DEFINER : 如 果 對 它 使 用 SELECT 的 用 戶 不 是 創 建 它 的 用 戶, 那 麼 mysql 將 返 回 NULL 值, 而 不 是 ROUTINE_DEFINITION 列 這 個 檢 查 功 能 在 作 此 書 時 還 沒 實 現 Additional clause in SHOW PROCEDURE STATUS 顯 示 過 程 狀 態 子 句 SHOW PROCEDURE STATUS 中 的 輔 助 子 句 既 然 我 已 經 列 出 INFORMATION_SCHEMA.ROUTINES 中 的 列, 就 可 以 回 去 解 釋 SHOW PROCEDURE STATUS 的 新 細 節, 語 法 是 : SHOW PROCEDURE STATUS [WHERE condition]; 語 句 中 的 條 件 判 斷 和 SELECT 語 句 的 一 樣 : 如 果 為 真, 則 在 輸 出 中 返 回 行 但 這 裏 有 個 需 要 第 43 頁 如 有 錯 誤 請 來 信 chenpengyi_007@163.com, 謝 謝 您 的 支 持 和 閱 讀, 同 時 感 謝 您 對 MySQL 事 業 的 關 注