建立預存程序 - ApsaraDB for OceanBase (Deprecated)

概述

預存程序是一條或多條語句的集合，對資料庫進行一系列複雜操作時，預存程序可以在資料庫內將這類複雜操作封裝成一個代碼塊，以便重複使用，從而減少資料庫開發人員的工作量。

預存程序旨在完成特定功能的 SQL 語句集，經編譯建立並儲存在資料庫中，使用者可以通過指定預存程序名並指定所需參數來調用執行，利用預存程序可以加速 SQL 語句的執行。

建立預存程序，即由使用者建立並能夠完成某一特定功能的預存程序，預存程序可以包含參數和傳回值。

預存程序與函數的區別

函數的傳回值顯示具體結果值，而預存程序的傳回值僅指明執行是否成功。
可以直接通過 SELECT語句調用函數，而預存程序的調用需要使用 CALL語句。

預存程序作用

提高應用程式的通用性和可移植性：建立預存程序完成後，可以在程式中被多次調用，無需重新編寫該預存程序的 SQL 語句，且支援修改預存程序，對程式原始碼無影響，從而提高程式的可移植性。
提高 SQL 執行速度：預存程序編譯完成，如果某一個操作包含大量的 SQL 代碼或者分別被執行多次，則使用預存程序比直接使用單條 SQL 語句執行速度更快速。
減輕伺服器的負擔：在進行資料庫物件操作時，如果使用單條調用的方式，則網路上必須傳輸大量的 SQL 語句；如果使用預存程序，則可以直接發送過程的調用命令，從而降低網路負擔。

如上圖所示，建立預存程序包含以下 5 個步驟：

指定預存程序名稱。
添加參數。
確定建立預存程序。
編輯預存程序。
完成建立預存程序。

本文檔以在 ODC 中建立部門預算預存程序（proc_total）為例，預存程序 proc_total 中包含 budget_r 和 budget_s 兩個 INT 類型的輸入參數，計算兩個參數之和。

說明

文中所使用的均為樣本資料，您可根據實際情況對資料進行替換。

操作步驟

步驟一：指定預存程序名稱

在 OceanBase 開發人員中心（OceanBase Developer Center，ODC）單擊串連名進入串連後，在左導覽列中單擊 預存程序 標籤可以查看預存程序列表。在預存程序列表的右上方，單擊+建立預存程序，或是在頂部導覽列中單擊建立> 預存程序 以建立所需的對象。

在 建立預存程序 中，輸入預存程序名稱。

步驟二：添加參數

參數即調用函數時傳入的資訊，需指定參數資訊：
- Oracle 模式：需指定參數的名稱、模式、類型和 預設值。
- MySQL 模式：需指定參數的名稱、模式、類型和長度。

添加函數提供三種功能操作：

功能項	說明
彈窗引導輔助菜單	通過彈窗引導輔助菜單，可添加、刪除、上下移動參數。
單擊表序號	單擊表序號，選中整行，顯示輔助菜單（刪除、上/下移動）。單擊並選中表序號，可拖動整行參數以調整順序。
右鍵操作	拖動滑鼠選中整行，按右鍵滑鼠，進行複製行 / 向下移動一行。選擇儲存格，按右鍵滑鼠，進行複製。

參數中需指定的模式指參數類型

MySQL/Oracle 模式下支援三種參數模式 IN（輸入）、OUT（輸出）、INOUT（輸入輸出）：

CREATE PROCEDURE proc_name ([[IN |OUT |INOUT ] parameter_name parameter_type...])

參數說明

參數	類型
IN	輸入參數。調用預存程序時將參數的值傳入預存程序供執行預存程序時使用。 IN 型別參數一般只用於傳入，在調用預存程序中一般不作修改和返回。
OUT	輸出參數。調用預存程序時，預存程序會忽略輸出參數本身的值並傳入一個空值，執行結束後，輸出參數會被賦予預存程序對其修改的值。一般用輸出參數擷取預存程序的執行結果資料。 OUT 型別參數可以用於調用預存程序中需要修改和傳回值。
INOUT	輸入輸出參數。同時具備輸入參數和輸出參數的功能。

參數設定
屬性
是否必填
預設
模式
名稱
必填
空
Oracle/MySQL
模式
必填
IN
Oracle/MySQL
類型
必填
VARCHAR
Oracle/MySQL
長度
必填
45
MySQL
預設值
非必填
空
Oracle

步驟三：確定建立預存程序

單擊確定，進入 SQL 建立確認頁面。

步驟四：編輯預存程序

在 SQL 建立確認頁面，編輯預存程序語句。

同時，在編輯頁面的工具列中提供了以下功能鍵：

功能	說明
格式化	單擊該按鈕對選中的 SQL 語句或當前 SQL 視窗中的所有 SQL 語句進行統一的格式化（縮排、換行和高亮關鍵字等操作）。
尋找/替換	在尋找框內輸入內容以對指令碼進行檢索，尋找後可在替換框內輸入內容以對尋找內容進行替換。
撤銷	將指令碼回退到上一步操作執行的結果。
重做	執行撤銷操作後，對指令碼重新執行撤銷前的操作。
大小寫	提供全部大寫、全部小寫和首字母大寫三種效果，為指令碼中選中的語句轉換為對應形式。
縮排	提供添加縮排和刪除縮排兩種效果，為指令碼中選中的語句添加或刪除包含的縮排。
注釋	提供添加註釋和刪除注釋兩種效果，將指令碼中選中的語句轉換為注釋或轉換為 SQL 語句。
IN 值轉化	可以將如 A B 的格式轉化為 ('A','B') 的格式。

使用者可以在 SQL 建立確認頁面編輯建立的預存程序語句。文法格式如下：

CREATE PROCEDURE proc_name (
  [proc_parameter[,...]]) 
IS
BEGIN
proc_body:
　　Valid SQL routine statement
END [end_label]

參數說明：

參數	說明
proc_name	預存程序的名稱，預設在當前資料庫中建立。如需在特定資料庫中建立預存程序，需要在名稱前加上資料庫名稱，即 db_name.sp_name。重要名稱應當盡量避免選取與 MySQL 內建函數相同的名稱，否則會發生錯誤。
[proc_parameter[,…] ]	預存程序的參數列表，包括 `[IN\|OUT\|INOUT] parameter_name parameter_type`。其中，`parameter_name`為參數名，`parameter_type`為參數的類型（可以是任何有效 MySQL 資料類型）。當有多個參數時，參數列表中彼此間用逗號分隔。預存程序可以無參數（此時預存程序的名稱後仍需加上一對括弧），也可以有 1 個或多個參數。重要參數名避免與資料表的列名相同，否則預存程序的 SQL 語句會將參數名看作列名，從而可能出錯。
proc_body	預存程序的主體部分，包含在程序呼叫的時候必須執行的 SQL 語句。此部分以 BEGIN開始，END 結束。如預存程序體中只有一條 SQL 語句，則可以省略 BEGIN-END 標誌。

步驟五：完成建立預存程序

單擊建立，完成建立預存程序後，可以如同調用系統內建函數一樣，使用關鍵字 CALL調用使用者自訂的預存程序。

說明

在左側導覽列中，右鍵按一下滑鼠預存程序列表中的預存程序名，通過彈出的管理巨集指令清單（包括查看、建立、編輯、運行、下載、刪除和 重新整理等），可快速管理和操作目標對象。

更多詳情請參見管理預存程序和常用功能簡介。

文法格式：

CALL proc_name ([proc_parameter [,...]]);

樣本：

CALL proc_total (30000, 20000);

ApsaraDB for OceanBase (Deprecated)：建立預存程序

概述