存儲過程和函數

張哥同步視頻：https://edu.csdn.net/course/play/7940

本章簡介

我們已經學習了PL/SQL語言、程序結構、流程控制、異常處理等知識。但是到目前為止，所創建的PL/SQL塊都是匿名的，每次執行時都需要被重新編譯且沒有被存儲在數據庫中，不能被其他的PL/SQL塊使用。為了讓編寫的PL/SQL語句塊成為數據庫的存儲單元并能夠共享、實現代碼重用，必須要使用程序包、過程和函數對象。

本章將學習過程、函數和程序包等概念。過程與函數是命名的PL/SQL塊，可以被編譯后存儲在數據庫中，以備執行，其他PL/SQL塊可以按名稱來調用。實際應用中，可以將商業邏輯、企業規則寫成過程或函數保存到數據庫中，以便共享。過程與函數也被稱為子程序，因為它們是獨立的、能夠被父程序調用。其中，過程一般用于執行一個指定的操作，而函數一般用于計算并返回一個值。程序包用于將邏輯相關的PL/SQL塊或元素組織在一起，作為一個完整的單元存儲在數據庫中，用名稱來標識程序包。程序包具有面向對象的程序設計語言的特點，是對PL/SQL塊或元素的封裝。它類似于Java語言中的類，其中的變量相當于類中的成員變量，過程和函數相當于類中的方法。

核心技能部分

5.1?子程序簡介

子程序是指被命名的PL/SQL塊，這種塊可以帶有參數，可以在不同應用中多次調用。PL/SQL有兩種類型的子程序：過程和函數。其中，過程用于執行特定操作，而函數則用于返回特定數據。通過將商業邏輯和企業規則集成到PL/SQL子程序中，可以簡化客戶端應用的開發和維護，提高應用的性能。

5.2?過程

5.2.1?創建過程

過程一般用于執行一個指定的操作，可以將常用的特定操作封裝成過程。

語法：

Create ?[or ??replace] ?procedure ?procedure_name

(argument1 ??[mode1] ?datatype1, argument2 ?[mode2] ?datatype2,…)

is???[as]

聲明部分

Begin

執行部分

Exception

異常處理部分

End;

上述語法中，procedure_name用于指定過程名稱，argument1、argument2等則用于指定過程的參數，IS或AS用于開始一個PL/SQL塊。當指定參數數據類型時，不能指定其長度。另外，創建過程時，既可以指定輸入參數（IN），又可以指定輸出參數（OUT）及輸入輸出參數（IN OUT）。通過在過程中使用輸入參數，可以將應用環境的數據傳遞到執行部分。通過使用輸出參數，可以將執行部分的數據傳遞到應用環境。定義子程序參數時，如果不指定參數模式，則默認輸入參數；如果需要定義輸出參數，則必須指定OUT關鍵字；如果需要定義輸入輸出參數，則必須指定IN OUT關鍵字。以下通過示例說明創建過程和使用各種參數模式的方法。接下來我們看一下過程的創建。

1.創建無參過程

以下通過

更新員工的薪水

刪除表中重復記錄

為例，說明創建該種過程的方法。

過程pro_update_rec創建之后可以進行調用，在sql

/

plus環境中可以使用call或者exec兩個命令。

如果不使用

exec

或者

call

命令的話，我們也可以使用

pl/sql

塊的方式來調用。

2.創建in參數過程

創建過程時，可以使用輸入參數將應用程序的數據傳遞到過程中。當為過程定義參數時，如果不指定參數模式，則默認為輸入參數。另外，可以使用IN關鍵字顯式地定義輸入參數。下面以查詢指定編號的員工為例說明創建該過程的方法,

該過程pro_query_emp有一個in參數v_no。當調用該過程時，由于該參數沒有默認值，所以必須提供數值

3.創建out參數過程

過程不僅可以用于執行特定操作，還可以輸出數據。在過程輸出數據時，需要使用OUT或IN OUT參數來完成。當定義輸出參數時，必須提供OUT關鍵字。以下通過創建用于輸出雇員名的過程為例，說明創建帶有OUT參數的過程的方法

，由于在創建pro_querysal_emp時，沒有為參數param_empno指定參數模式，所以該參數是輸入參數；參數param_sal指定了OUT關鍵字，所以這個參數是輸出參數。當在應用中調用該過程時，必須定義變量接收輸出參數的數據。

4.創建in ?out參數過程

創建過程時，不僅可以指定IN和OUT參數，還可以指定IN OUT參數。IN OUT參數也稱為輸入輸出參數，當使用此參數時，在調用過程之前需要通過變量給參數傳遞數據；調用結束后，Oracle會通過該變量將過程結果傳遞給應用。以下通過計算兩個數值相除結果的過程proc_compute為例，說明在過程中使用IN OUT參數方法，

在過程proc_testinout中，param_num為輸入輸出參數。當在應用中調用該過程時，必須提供變量臨時存放數值，在運算結束之后會

將

將結果存放到這個變量中。

5.2.2?使用過程時多參傳遞

在調用帶有參數的子程序時，傳遞給形式參數（形參）的參數被稱為實際參數（實參）。在過程內部，通過形參引用這些實參的值。為形參傳遞變量和數據可以采用位置傳遞、名稱傳遞和組合傳遞3種方法。如果在定義參數時帶有默認值，則在調用子程序時可以不為該形參提供數值。為說明多個參數傳遞的問題，我們編寫一個計算3個整數和的過程。

1.按位置傳遞

按位置傳遞是指在調用時按參數的排列順序依次寫出實參的名稱，將形參與實參關聯起來進行傳遞。在這種方法中，形參與實參的名稱是相互獨立的、沒有關系的，次序才重要。如果更新了一個過程的形參的次序，則對應該過程的所有調用都必須進行相應的更新，所以會增加維護應用程序的難度。

2.按名稱傳遞

按名稱傳遞是指在調用時按照形參與實參的名稱寫出實參所對應的形參，將形參與實參關聯起來進行傳遞。在這種方法中，形參與實參的名稱是相互獨立、沒有關系的，名稱的對應關系很重要，但次序不重要。它比按位置傳遞方法在書寫上要復雜，如果只更新了一個過程的形參的次序，則不需要對該過程的任何調用進行任何更新。但如果更新了一個過程的形參的名稱，則對該過程的所有調用都必須進行相應的更新，會增加維護應用程序的難度。名稱傳遞在調用子程序時指定參數，并使用關聯符號“=>”為其提供相應的數值或變量。

3.組合傳遞

根據應用的需要，可以將按位置傳遞、按名稱傳遞兩種方法在同一調用中混合使用。但前面的實參必須使用按位置傳遞方法，而后面其余的實參則可以使用按名稱傳遞的方法。。

5.3?函數

函數用于返回特定數據，如果在應用程序中經常需要通過執行SQL語句來返回特定數據，可以基于這些操作創建特定的函數。使用函數不僅可以簡化客戶端應用程序的開發和維護，還可以提高應用程序的執行性能。

語法:

Create ?[or ?replace] function ?function_name

(argument1 ?[mode1] ?datatype1,

A

a

rgument2 ?[dode2] ??datatype2,

……)

Return ?datatype

is|as

聲明部分

Begin

執行部分

Exception

異常處理部分

End;

在上述語法中，

上述語法中，

function_name用于指定函數名稱，

argLlmentl

argument1

、

argLlment2

argument

2

等用于指定函數的參數。當指定參數數據類型時，不能指定其長度。RETURN子句用于指定函數返回值的數據類型，is或as用于開始一個PL/SQL塊。當創建函數時，函數頭部必須帶有RETURN子句，在函數體內至少需要包含一條RETURN語句。另外，當創建函數時，既可以指定輸入參數?(IN)，又可以指定輸出參數?(OUT)及輸入和輸出參數?(IN OUT)。以下通過示例來說明函數的用法。

5.3.1?創建和使用函數

當創建函數時，函數既可以帶有參數，又可以不帶參數。以下通過創建用于產生一個隨機數的函數為例，說明創建無參函數的方法

函數創建完之后，我么可以通過一個pl/sql塊調用

在圖4.1.15所示代碼中，num用來接收函數的返回值。函數的使用除了在pl/sql塊中調用外，還可以在sql語句中使用

函數像過程一樣可以有參數，創建函數時也可以指定in、out、in out參數。我們在使用函數的時候傳入參數即可。

5.3.2?函數和過程的比較

過程與函數有許多相同的功能及特性，主要包括以下4個方面：

(1)都使用IN模式的參數傳入數據、OUT模式的參數返回數據。

(2)輸入參數都可以接收默認值，都可以傳值。

(3)調用時的實參都可以使用位置表示法或名稱表示法。

(4)都有聲明部分、執行部分和異常處理部分。

使用過程和函數的時機通常取決于需要從子程序中返回多少個值以及需要如何使用這些值。一般而言，返回多個值或不返回值時使用過程，只需要返回一個值時使用函數。雖然帶OUT模式的參數的函數也能返回多個值，但是一般都認為這種方法屬于不好的編程習慣或風格。過程一般用于執行一個指定的動作，函數一般用于計算和返回一個值。可以在?SQL語句內部調用函數來完成復雜計算問題，因為函數一定會有一個值通過其名稱返回給調用環境；但不能調用過程，因為過程的返回值與過程的名稱無關。

5.4?包

包?(Package)用于組合邏輯相關的?PL/SQL類型?(例如TABLE類型和?RECORD類型)、PL/SQL項?(例如游標和游標變量)和PL/SQL子程序?(例如過程和函數)。通過使用?PL/SQL包，不僅可以簡化應用設計、提高應用性能，還可以實現信息隱藏、子程序重載等功能。

5.4.1?創建包

包由包規范和包體兩部分組成。當創建包時，需要首先創建包規范，然后創建包體。

1. 創建包規范

實際上，包規范是包與應用程序之間的接口，用于定義包的公用組件，包括常量、變量、游標、過程和函數。在包規范中所定義的公用組件不僅可以在包內引用，而且可以由其他的子程序引用。創建包規范時需要注意，為了實現信息隱藏，不應該將所有組件全部放在包規范處定義，應該只定義公用組件。在SQL?Plus 中創建包規范時，需要使用CREATE PACKAGE命令來完成。

語法：

Create ?[or ?replace] ?package ?package_name

IS|AS

Public ?type ?and ??item ??declarations

Subprogram ???specifications

End ?package_name;

上述語法中，package_name用于指定包名，而以IS或AS開始的部分用于定義公用組件。以下通過創建用于計算圓面積的包dbutil_package為例，說明創建包規范的方法。當定義該包規范時，定義常量pi、公用過程print_area以及公用函數getarea

在執行上述命令后，就會創建包規范dbutil_package，并且定義所有的公用組件。但因為只定義了過程和函數的頭部，沒有編寫過程和函數的執行代碼，所以公用的過程和函數只有在創建了包體之后才能調用。

2.創建包體

包體用于實現包規范所定義的過程和函數。當創建包體時，也可以單獨定義私有組件，包括變量、常量、過程和函數等，但在包體中定義的私有組件只能在包內使用，不能由其他子程序引用。在創建包時，為了實現信息隱藏，應該在包體內定義私有組件。為了實現包規范中所定義的公用過程和函數，必須創建包體。創建包體需要使用命令CREATE PACKAGE BODY來完成。

語法：

Create ?[or ?replace] ?package ?body ?package_name

IS|AS

Private type ?and item ?declarations

Subprogram ?bodies

End package_name;

上述語法中，package_name用于指定包名，由?IS或AS 開始的部分定義私有組件，并實現包規范中所定義的公用過程和函數。此外，包體名稱與包規范名稱必須相同。以下通過實現包規范dbutil_package的公用組件為例，說明創建包體的方法

5.4.2?使用包中組件

包的私有組件只能在包內調用，并且可以直接調用；而包的公用組件既可以在包內調用，又可以在其他應用中調用。但需要注意的是，當在其他應用中調用包的組件時，必須添加包名作為前綴(包名.組件名)，以下將舉例說明調用包組件的方法。

1. 在同一包內調用包的組件

在調用同一包內的其他組件時，可以直接調用，不需要添加包名作為前綴。

2. 調用包的公用變量

當在其他應用中調用包的公用變量時，必須在公用變量名前添加包名作為前綴，并且其數值在當前會話內一直生效

3. 調用包的公用子程序

當在其他應用中調用包的公用過程時，必須在公用過程名前添加包名作為前綴

5.5?子程序和包的管理

5.5.1?子程序的管理

過程與函數被存儲在數據庫中，可以隨時查看源代碼。如果需要，可以在創建過程與函數時隨時查看更加詳細的編譯錯誤信息，不需要的過程與函數可以隨時刪除。

1. 查看子程序的源碼

在創建子程序之后，Oracle會將子程序名及其源代碼信息存放在數據字典中。通過查詢數據字典USER_SOURCE，顯示當前用戶的所有子程序及其源代碼，如圖4.1.21所示。

2. 刪除子程序

如果不再需要某個子程序，可以將其刪除。

5.5.2?包的管理

1. 查看包源代碼

當創建了包之后，Oracle會將包名及其源代碼信息存放到數據字典中。通過查詢數據字典US_ER_SOURCE，顯示當前用戶的包及其源代碼，包代碼的查詢和過程代碼查看區別就在type字段，包的type字段值為”PACKAGE”,包體的type字段值為”PACKAGE BODY”。

2. 刪除包

當包不再需要時，可以刪除包。如果只刪除包體，則可以使用?DROP PACHAGE BODY命令；如果同時刪除包規范和包體，則可以使用DROP PACKAGE命令。

??本章總結

??子程序包括過程和函數，過程用于執行特定操作，而函數則用于返回特定數據。

??包?(Package)用于組合邏輯相關的?PL/SQL類型?(例如TABLE類型和?RECORD類型)、PL/SQL項?(例如游標和游標變量)和PL/SQL子程序?(例如過程和函數)。

??包由包規范和包體兩部分組成。當創建包時，需要首先創建包規范，然后創建包體。

任務實訓部分

1：無參存儲過程

訓練技能點

??無參存儲過程的創建和使用

需求說明

向部門表dept中插入兩條部門信息，要求創建存儲過程proc _ insert _ dept實現

實現思路

(1)?創建并執行存儲過程“proc _ insert _ dept”，用于向表中插入數據

（2）

調用存儲過程

2：帶IN和OUT參數的存儲過程

訓練技能點

??帶IN和OUT參數的存儲過程的創建和使用

需求說明

編寫過程，命名為“proc_storage”,向該存儲過程傳入需要出庫的商品編號和出庫數量，執行出庫操作并返回完成狀態、商品名稱、原庫存和現有庫存。其中“

0

0

”表示出庫成功，“1”表示找不到該商品，“2”表示庫存不足。原始數據表tb_storage和tb_shop初始數據如圖?5.2.4所示。

實現思路

（1）?創建存儲過程proc_storage

，

,

該過程包含兩個IN 參數，分別用于接收需要出庫的商品和出庫數量；包含4個OUT參數，分別返回完成狀態、商品名稱、原庫存和現有庫存，如圖5.2.5所示。

(2)?在PL/SQL塊中測試并運行過程

3：帶IN和OUT參數的函數

訓練技能點

??創建和使用帶IN和OUT參數的函數

需求說明

根據雇員名稱查詢雇員所在部門名稱和崗位的函數。

實現思路

（1）?創建函數名“fun_getEmpInfo”,該函數包含一個輸入參數雇員名稱param_ename、一個輸出參數部門名稱param_dname和一個返回值返回崗位信息。

（2）?調用函數

4：包的創建和使用

訓練技能點

??創建和使用包

需求說明

定義包規范emp_package

,

，

然后定義公有變量v_deptno、公用過程proc_add_employee以及公用函數fun_get_sal

，

，

然后根據該包規范定義包體，并定義私有過程fun_validate_deptno來驗證員工是否存在。

實現思路

（1）

創建包規范emp_package

（2）

創建emp_package的包體

（3）

調用包中的變量、過程及函數

鞏固練習

一

．

、

選擇題

1. 下列選項中，必須返回數據的程序單元是?( ?)。

A. 觸發器

B. 函數

C. 過程

D. 包

2. 當創建過程時，可以實現輸出數據的參數有?( ?)。

A. IN參數

B. OUT參數

C. IN OUT參數

D. 任何參數都不能輸出數據

3. 下列選項中，關于子程序組合傳遞方式的描述正確的是?( ?)。

A. 組合傳遞中必須同時包括位置傳遞和名稱傳遞

B. 位置傳遞必須位于名稱傳遞之前

C. 名稱傳遞必須位于位置傳遞之前

D. 以上說法都不正確

4. 下列選項中，關于Oracle程序包的描述正確的是?( ?)。

A. 在包規范部分定義公用組件

B. 在包體部分定義公用組件

C. 在包規范部分實現公用組件

D. 在包體部分實現公用組件

二、上機練習

創建用于操縱ORD 表的包?ORD_PACKAGE，并調用該包的公用過程和函數。當創建包ORD_PACKAGE時，應該實現以下需求：

（1）定義私有函數?fun_valid_cust，檢查客戶號是否存在于

CUSTOMBR表中。如果存在則返回TRUE，否則返回?FALSE。

（2）定義公用過程proc_add_odd，根據輸入的訂單號、預訂日期、客戶號、交付日期、訂單總價為

ORD表增加訂單。如果訂單已經存在，則顯示自定義錯誤消息“ORA-20001：該訂單已經存在!”；如果客戶號不存在，則顯示自定義錯誤消息“ORA-20002：該客戶不存在!”；如果交付日期小于預定日期，則顯示自定義錯誤消息“ORA-20003：交付日期不能小于預訂日期!”。

SQL 面向對象編程

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。