PL/SQL 存儲(chǔ)過(guò)程

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

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

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

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

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

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

• 函數(shù) - 這些子程序返回單個(gè)值; 主要用于計(jì)算和返回值。
• 存儲(chǔ)過(guò)程(程序) - 這些子程序不直接返回值; 主要用于執(zhí)行動(dòng)作。

本章將主要介紹PL/SQL中的存儲(chǔ)過(guò)程。在下一章介紹討論P(yáng)L/SQL函數(shù)。

PL/SQL子程序的部分

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

創(chuàng)建存儲(chǔ)過(guò)程

可使用CREATE OR REPLACE PROCEDURE語(yǔ)句來(lái)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程。 CREATE OR REPLACE PROCEDURE語(yǔ)句的簡(jiǎn)化語(yǔ)法如下：

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

其中，

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

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

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

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

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

執(zhí)行獨(dú)立程序

獨(dú)立的存儲(chǔ)程序可以通過(guò)兩種方式調(diào)用 -

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

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

EXECUTE greetings;
SQL

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

SQL> EXECUTE greetings;
Hello World!

PL/SQL 過(guò)程已成功完成。

SQL>
Shell

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

BEGIN 
   greetings; 
END; 
/
SQL

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

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

PL/SQL 過(guò)程已成功完成。

SQL>
Shell

刪除獨(dú)立存儲(chǔ)過(guò)程

使用DROP PROCEDURE語(yǔ)句刪除獨(dú)立存儲(chǔ)過(guò)程。刪除程序的語(yǔ)法是 -

DROP PROCEDURE procedure-name;
SQL

可以使用以下語(yǔ)句刪除greetings存儲(chǔ)過(guò)程程序 -

DROP PROCEDURE greetings;
SQL

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

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

IN和OUT模式 - 示例1

假設(shè)以下存儲(chǔ)過(guò)程需要求出兩個(gè)值中的最小值。這里，存儲(chǔ)過(guò)程兩個(gè)輸入的數(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('兩個(gè)數(shù)：12, 35中的最小值是 : ' || c); 
END; 
/
SQL

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

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

IN和OUT模式 - 示例2

此過(guò)程計(jì)算傳遞值的值的平方。此示例顯示了如何使用相同的參數(shù)來(lái)接受值，然后返回另一個(gè)結(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

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

傳遞參數(shù)的方法

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

• 位置符號(hào)
• 命名符號(hào)
• 混合符號(hào)

位置符號(hào)

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

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

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

命名符號(hào)

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

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

混合符號(hào)

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

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

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

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

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