PL/SQL 存儲過程

在本章中，我們將討論PL/SQL中的存儲過程。 子程序是執(zhí)行特定任務(wù)的程序單元/模塊。 這些子程序組合起來形成更大的程序。這種做法被稱為“模塊化設(shè)計”。 子程序可以被稱為調(diào)用程序的另一個子程序或程序調(diào)用。

可以在以下幾個地方中創(chuàng)建一個子程序 -

• 在模式(schema)級別中
• 一個程序包中
• 在PL/SQL塊中

在模式(schema)級別中，子程序是一個獨立的子程序。它是使用CREATE PROCEDURE或CREATE FUNCTION語句創(chuàng)建的。它存儲在數(shù)據(jù)庫中，可以使用DROP PROCEDURE或DROP FUNCTION語句進行刪除。

在包中創(chuàng)建的子程序是打包的子程序。它存儲在數(shù)據(jù)庫中，只有當使用DROP PACKAGE語句刪除程序包時，才能將其刪除。我們將在“PL/SQL程序包”一章中討論程序包的應(yīng)用。

PL/SQL子程序被命名為可以使用一組參數(shù)調(diào)用的PL/SQL塊。 PL/SQL提供兩種子程序 -

• 函數(shù) - 這些子程序返回單個值; 主要用于計算和返回值。
• 存儲過程(程序) - 這些子程序不直接返回值; 主要用于執(zhí)行動作。

本章將主要介紹PL/SQL中的存儲過程。在下一章介紹討論PL/SQL函數(shù)。

PL/SQL子程序的部分

每個PL/SQL子程序都有一個名稱，也可能有一個參數(shù)列表。 像匿名PL/SQL塊一樣，命名塊也將具有以下三個部分 -

創(chuàng)建存儲過程

可使用CREATE OR REPLACE PROCEDURE語句來創(chuàng)建一個存儲過程。 CREATE OR REPLACE PROCEDURE語句的簡化語法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;
SQL

其中，

• procedure-name是要創(chuàng)建的存儲過程的名稱。
• [OR REPLACE]選項允許修改現(xiàn)有的過程。
• 可選參數(shù)列表包含參數(shù)的名稱，模式和類型。IN表示將從外部傳遞的值，OUT表示將用于返回過程外的值的參數(shù)。
• procedure-body包含可執(zhí)行部分。
• 使用AS關(guān)鍵字而不是IS關(guān)鍵字來創(chuàng)建存儲過程。

例子以下示例演示如何創(chuàng)建一個簡單的存儲過程，執(zhí)行時它只顯示字符串“Hello World！”在屏幕上。

SET SERVEROUTPUT ON SIZE 99999;
CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

-- 執(zhí)行存儲過程
exec greetings;
-- 或者
EXECUTE greetings;
SQL

當使用SQL提示符執(zhí)行上述代碼時，它將產(chǎn)生以下結(jié)果 -

執(zhí)行獨立程序

獨立的存儲程序可以通過兩種方式調(diào)用 -

• 使用EXECUTE關(guān)鍵字
• 從PL/SQL塊調(diào)用過程的名稱

可以使用EXECUTE關(guān)鍵字調(diào)用名為“greetings”的存儲過程如下 -

EXECUTE greetings;
SQL

上述調(diào)用將顯示結(jié)果為 -

SQL> EXECUTE greetings;
Hello World!

PL/SQL 過程已成功完成。

SQL>
Shell

該過程也可以從另一個PL/SQL塊調(diào)用，例如 -

BEGIN 
   greetings; 
END; 
/
SQL

執(zhí)行上面示例代碼，得到以下結(jié)果 -

SQL> BEGIN
  2     greetings;
  3  END;
  4  /
Hello World!

PL/SQL 過程已成功完成。

SQL>
Shell

刪除獨立存儲過程

使用DROP PROCEDURE語句刪除獨立存儲過程。刪除程序的語法是 -

DROP PROCEDURE procedure-name;
SQL

可以使用以下語句刪除greetings存儲過程程序 -

DROP PROCEDURE greetings;
SQL

PL/SQL子程序中的參數(shù)模式

下表列出了PL/SQL子程序中的參數(shù)模式 -

IN和OUT模式 - 示例1

假設(shè)以下存儲過程需要求出兩個值中的最小值。這里，存儲過程兩個輸入的數(shù)字使用IN模式，并使用OUT模式參數(shù)返回最小值。

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 12; 
   b:= 35; 
   findMin(a, b, c); 
   dbms_output.put_line('兩個數(shù)：12, 35中的最小值是 : ' || c); 
END; 
/
SQL

當上述代碼在SQL提示符下執(zhí)行時，它會產(chǎn)生以下結(jié)果 -

兩個數(shù)：12, 35中的最小值是 : 12
Shell

IN和OUT模式 - 示例2

此過程計算傳遞值的值的平方。此示例顯示了如何使用相同的參數(shù)來接受值，然后返回另一個結(jié)果。

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  
BEGIN 
   a:= 11; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
/
SQL

當上述代碼在SQL提示符下執(zhí)行時，它會產(chǎn)生以下結(jié)果 -

傳遞參數(shù)的方法

實際參數(shù)(實參)可以通過三種方式傳遞 -

• 位置符號
• 命名符號
• 混合符號

位置符號

在位置符號中，可以調(diào)用存儲過程如下 -

findMin(a, b, c, d);
SQL

在位置符號中，第一個實際參數(shù)代替第一個形式參數(shù); 第二個實際參數(shù)代替第二個形式參數(shù)，依此類推。 因此，a代替x，b代替y，c代替z，d代替m。

命名符號

在命名符號中，實際參數(shù)與使用箭頭符號(=>)的形式參數(shù)相關(guān)聯(lián)。調(diào)用存儲過程如下所示 -

findMin(x => a, y => b, z => c, m => d);
SQL

混合符號

在混合符號表示中，可以在過程調(diào)用中混合使用符號; 然而，位置符號應(yīng)在命名符號之前。

以下調(diào)用存儲過程的方式是合法的 -

findMin(a, b, c, m => d);
SQL

但是，以下這種是不合法的：

findMin(x => a, b, c, d);