PHP調用Oracle存儲過程

jopen 12年前發布 | 71K 次閱讀 PHP PHP開發

存儲過程是實際位于 Oracle 中的程序。大多數存儲過程都是用 PL/SQL 編寫的，在 Oracle 數據庫 10g 第 2 版和更高版本中，您可以用 Java、.NET 或其他語言將它們編寫為外部過程。存儲過程通常將一系列相關操作組成一個 API。存儲過程執行的操作包括由 SQL 語句以及 PL/SQL 語句執行的操作，SQL 語句用于獲取和修改數據，PL/SQL 語句將對這些數據進行相應操作，如執行某些數學運算、對值進行詳細驗證值以及處理錯誤條件。它們降低了調用程序與數據庫之間的“往返”次數并簡化了客戶端中的數據管理邏輯，從而有利于提高性能。如果考慮一下管理表之間的多對多關系通常需要的代碼，則會發現對現有數據執行更新通常涉及三個不同的查詢。通過將該進程封裝在單個存儲過程中，將減少客戶端與數據庫之間的通信量，而通常需要在客戶端代碼分多個步驟執行的操作將減化為一個數據庫調用。 PHP OCI8 擴展支持對存儲過程的調用，您可以將參數綁定到過程語句（與將參數綁定到普通的 SQL 語句方法相同），并可以訪問結果游標和 Oracle 集合。本方法文檔中提供了存儲過程的常見操作示例。存儲過程輸入和輸出調用 Oracle 存儲過程時，所有輸入和輸出數據均以參數形式傳遞給過程。如果您習慣于使用某些參數調用 PHP 函數并讓它返回一個值的過程，那么起初您可能對此感到有些迷惑不解，但通過示例卻可以一目了然。

一，假設有以下存儲過程簽名：

sayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

　調用此過程時，第一個參數名將包含一個在調用時提供的輸入值，而 greeting 將由該過程填充，作為一個“返回”值，在該過程完成后使用。閱讀規范 PL/SQL 編程不是本方法文檔的范疇，但您需要對存儲過程有一個大致的了解并能夠閱讀接口規范，但不必深究。對于存儲過程的源代碼，開始都需要先定義接受的參數，例如：

PROCEDURE edit_entry(

　　status_out OUT NUMBER,

　　status_msg_out OUT VARCHAR2,

　　id_inout IN OUT INTEGER,

　　title_in IN VARCHAR2,

　　text_out OUT CLOB,

　　categories_in IN list_of_numbers

　　);

　　該過程名為 edit_entry。圓括號中定義了可以傳遞給該過程的各參數（由逗號分隔）。每個參數中，你會看到用于在該過程內部引用其值的名稱（不需要在 PHP 腳本中使用同一名稱）、參數的模式（如下所示）以及該參數的類型。對于該示例中的第一個參數：

status_out OUT NUMBER,

　　內部名稱為 status_out，模式為 OUT，類型為 NUMBER（它是一個原生的 Oracle 數據類型）。后面有一個 id_inout 參數。

　id_inout IN OUT INTEGER,

　　它的模式為 IN OUT，類型為 INTEGER。最后是 categories_in 參數：

categories_in IN list_of_numbers

　　此處的類型是由用戶定義的（稍后將對該類型進行詳細介紹）。參數模式參數模式描述了數據從調用方到過程的“流”向：

　　IN – 該模式的參數由調用方提供。

　　OUT – 參數可以由過程分配值并返回至調用方。

　　IN OUT – 參數可以在兩個“方向”使用；即，調用方可以為該參數提供值，而過程也可以修改參數值。

　參數項是必選項。從 PHP 調用過程時，必須將 PHP 變量綁定到它定義的所有參數。您不必向 PHP 變量分配值，即使它們是輸入參數 – 如果未向標量類型分配值，Oracle 將把它視為 NULL 值。值得注意的是，存儲過程可以在 Oracle 中“重載”。換言之，可以有兩個名稱相同但參數簽名不同的過程。將依據 PHP 變量綁定到的參數的數目和類型來決定要調用哪個過程。復雜類型存儲過程使用的參數并不只局限于 VARCHAR2 和 INTEGER 等標量類型。也可以傳遞并接收復雜的數據類型，如值列表或與從表中選擇的行集相對應的結果游標。一般說來，如果存在要迭代的數據行，則您將通常會收到從存儲過程返回的游標，而如果您需要傳入值列表，則通常將使用集合。以下示例通過 PHP 演示了這些復雜類型。調用方與定義方權限。Oracle 對“調用方”（執行存儲過程的用戶）和定義方（以其身份執行 CREATE PROCEDURE 語句的用戶）進行了區分。默認情況下，存儲過程是以定義方的權限執行的，即使調用方是不同的用戶。這意味著表的所有訪問權限（例如，在過程中的訪問權限）將由定義方的權限控制，因此調用方只需要執行過程的權限而非它使用的表的權限。可以在過程定義中用關鍵字 AUTHID CURRENT_USER 更改此模型。設置該指令后，執行存儲過程時所需的權限將在運行時依據執行該過程的當前用戶來決定。該方法的一個用途是測試一個修改表數據但實際上不修改實時數據的過程。這種情況下，調用方在他們自己的模式中定義一個表（該表與從他們需要執行的過程中訪問的表同名），而過程依據本地表而非提供給定義方的表執行。從 PHP 中調用存儲過程對于要從 PHP 中執行以調用過程的 SQL 語句而言，您將通常在 Oracle BEGIN ...END; 塊（稱作匿名塊）中嵌入調用。例如：

<?php

　　// etc.

　　//$sql = 'BEGIN sayHello(:name, :message); END;';

　　//然后，通過調用 oci_bind_by_name() 將參數綁定到 PHP 變量。 如果使用以下 DDL 語句定義了 sayHello

　　//：

　　//CREATE OR REPLACE PROCEDURE

　　//sayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

　　//AS

　　//BEGIN

　　//greeting := 'Hello ' || name;

　　//END;

　　//

　　//注意，您可以使用 SQL*Plus 命令行運行上面的語句。將該語句保存到文件 (SAYHELLO.SQL)。接下來，使用  

     //SQL*Plus 登錄：

　//　$ sqlplus username@SID

　//　然后，使用 START 命令創建該過程：

　//　SQL> START /home/username/SAYHELLO.SQL

　//　以下 PHP 腳本調用該過程：

　　

　　$conn = oci_connect('SCOTT','TIGER') or die;

　　$sql = 'BEGIN sayHello(:name, :message); END;';

　　$stmt = oci_parse($conn,$sql);

　　// Bind the input parameter

　　oci_bind_by_name($stmt,':name',$name,32);

　　// Bind the output parameter

　　oci_bind_by_name($stmt,':message',$message,32);

　　// Assign a value to the input

　　$name = 'Harry';

　　oci_execute($stmt);

　　// $message is now populated with the output value

　　print "$message\n";

　　?>

　Blog 示例程序包。為演示調用存儲過程方面的某些技巧，您將在此處使用以下名為 blog 的程序包，該程序包提供了一個 API，用于獲取和修改假設的網志應用程序中的條目。程序包用于通過其自身的作用域將過程、函數和數據封裝在其自身的命名空間內部，并使它們獨立于全局數據庫命名空間中的其他過程。調用程序包中的過程時，將使用句號來分隔程序包名稱與過程名稱。可以使用以下語句指定 blog 程序包：

CREATE OR REPLACE PACKAGE blog AS

　　TYPE cursorType IS REF CURSOR RETURN blogs%ROWTYPE;

　　/*

　　Fetch the latest num_entries_in from the blogs table, populating

　　entries_cursor_out with the result

　　*/

　　PROCEDURE latest(

　　num_entries_in IN NUMBER,

　　entries_cursor_out OUT cursorType

　　);

　　/*

　　Edit a blog entry.If id_inout is NULL, results in an INSERT, otherwise

　　attempts to UPDATE the existing blog entry. status_out will have the value

　　1 on success, otherwise a negative number on failure with status_msg_out

　　containing a description

　　categories_in is a collection where list_of_numbers is described by

　　TYPE list_of_numbers AS VARRAY(50) OF NUMBER;

　　*/

　　PROCEDURE edit_entry(

　　status_out OUT NUMBER,

　　status_msg_out OUT VARCHAR2,

　　id_inout IN OUT INTEGER,

　　title_in IN VARCHAR2,

　　text_out OUT CLOB,

　　categories_in IN list_of_numbers

　　);

　　END blog;

　　/

　該程序包提供了兩個過程：blog.latest（返回包含最新 num_entries 網志條目的結果游標）和 blog.edit_entry（允許插入新的網志條目以及修改現有的網志條目）。如果為 id_inout 參數提供值，則該過程將嘗試更新具有該 id 的相應網志條目。否則，它將插入一個新的網志條目并使用新行的主鍵填充 id_inout。該過程還接受與網志條目的主體相對應的 CLOB 對象以及與該條目歸檔到的類別列表相對應的集合對象。此處引用的集合類型 list_of_numbers

　　由以下語句定義：

CREATE OR REPLACE TYPE list_of_numbers AS VARRAY(50) OF NUMBER;

　　下面顯示了該程序包的主體。您可以通過其中的注釋了解它的功能而不必深入了解 PL/SQL：

CREATE OR REPLACE PACKAGE BODY blog AS

　　/*------------------------------------------------*/

　　PROCEDURE latest(

　　num_entries_in IN NUMBER,

　　entries_cursor_out OUT cursorType

　　) AS

　　BEGIN

　　OPEN entries_cursor_out FOR

　　SELECT * FROM blogs WHERE rownum < num_entries_in

　　ORDER BY date_published DESC;

　　END latest;

　　/*------------------------------------------------*/

　　PROCEDURE edit_entry(

　　status_out OUT NUMBER,

　　status_msg_out OUT VARCHAR2,

　　id_inout IN OUT INTEGER,

　　title_in IN VARCHAR2,

　　text_out OUT CLOB,

　　categories_in IN list_of_numbers

　　AS

　　ENTRY_NOT_FOUND EXCEPTION;

　　entry_found INTEGER := 0;

　　BEGIN

　　/* Default status to success */

　　status_out := 1;

　　/* If id_inout has a value then attempt to UPDATE */

　　IF id_inout IS NOT NULL THEN

　　/* Check the id exists - raise ENTRY_NOT_FOUND if not */

　　SELECT COUNT(*) INTO entry_found

　　FROM blogs b WHERE b.id = id_inout;

　　IF entry_found != 1 THEN RAISE ENTRY_NOT_FOUND; END IF;

　　/* Update the blogs table returning the CLOB field */

　　UPDATE blogs b SET b.title = title_in, b.text = EMPTY_CLOB()

　　WHERE b.id = id_inout RETURNING b.text INTO text_out;

　　/* Remove any existing relationships to categories

　　- new categories inserted below */

　　DELETE FROM blogs_to_categories WHERE blog_id = id_inout;

　　status_msg_out := 'Blog entry ' || id_inout || ' updated';

　　/* id_inout was null so INSERT new record */

　　ELSE

　　INSERT INTO blogs b ( b.id, b.title, b.date_published, b.text )

　　VALUES ( blog_id_seq.nextval, title_in, SYSDATE, EMPTY_CLOB() )

　　RETURNING b.id, b.text INTO id_inout, text_out;

　　status_msg_out := 'Blog entry ' || id_inout || ' inserted';

　　END IF;

　　/* Now handle assignment to categories.

　　Loop over the categories_in collection,

　　inserting the new category assignments */

　　FOR i IN 1 .. categories_in.count

　　LOOP

　　INSERT INTO blogs_to_categories (blog_id,category_id)

　　VALUES (id_inout,categories_in(i));

　　END LOOP;

　　status_msg_out := status_msg_out || ' - added to '

　　|| categories_in.count || ' categories';

　　EXCEPTION

　　/* Catch the exception when id_inout not found */

　　WHEN ENTRY_NOT_FOUND THEN

　　status_out := -1001;

　　status_msg_out := 'No entry found in table blogs with id = '

　　|| id_inout;

　　/* Catch any other exceptions raised by Oracle */

　　WHEN OTHERS THEN

　　status_out := -1;

　　status_msg_out := 'Error:' || TO_CHAR (SQLCODE) || SQLERRM;

　　END edit_entry;

　　END blog;

　　/

　　The underlying table structure the procedures are using is:

　　CREATE SEQUENCE blog_id_seq

　　INCREMENT BY 1;

　　/

　　CREATE TABLE blogs (

　　id NUMBER PRIMARY KEY,

　　title VARCHAR2(200),

　　date_published DATE,

　　text CLOB

　　);

　　/

　　CREATE SEQUENCE category_id_seq

　　INCREMENT BY 1;

　　CREATE TABLE categories (

　　id NUMBER PRIMARY KEY,

　　name VARCHAR2(30) UNIQUE

　　);

　　/

　　CREATE TABLE blogs_to_categories (

　　blog_id INTEGER NOT NULL

　　REFERENCES blogs(id),

　　category_id INTEGER NOT NULL

　　REFERENCES categories(id),

　　PRIMARY KEY (blog_id, category_id)

　　);

　　/

　存儲過程和引用游標看一下 blog.latest 過程，您將看到它返回一個用于迭代 blogs 表行的引用游標。與直接從 SELECT 語句中訪問行相比，在 PHP 中使用游標需要兩個額外的步驟。第一步是使用 oci_new_cursor() 函數（該函數隨后用于綁定到相應的參數）在 PHP 中準備一個游標資源。執行 SQL 語句后，第二步是對游標資源調用 oci_execute()。以下 PHP 腳本演示了該過程：

<?php

　　$conn = oci_connect('SCOTT','TIGER') or die;

　　$sql = 'BEGIN blog.latest(:num_entries, :blog_entries); END;';

　　$stmt = oci_parse($conn, $sql);

　　// Bind the input num_entries argument to the $max_entries PHP variable

　　$max_entries = 5;

　　oci_bind_by_name($stmt,":num_entries",$max_entries,32);

　　// Create a new cursor resource

　　$blog_entries = oci_new_cursor($conn);

　　// Bind the cursor resource to the Oracle argument

　　oci_bind_by_name($stmt,":blog_entries",$blog_entries,-1,OCI_B_CURSOR);

　　// Execute the statement

　　oci_execute($stmt);

　　// Execute the cursor

　　oci_execute($blog_entries);

　　print "The $max_entries most recent blog entries\n";

　　// Use OCIFetchinto in the same way as you would with SELECT

　　while ($entry = oci_fetch_assoc($blog_entries, OCI_RETURN_LOBS )) {

　　print_r($entry);

　　}

　　?>

　存儲過程和 LOB Oracle Long 對象與存儲過程之間可以進行相互傳遞，方法與內部的 SQL 之間進行的相互傳遞幾乎相同。以下示例演示了如何使用 CLOB 調用 blog.edit_entry 過程。該示例未向 id 參數分配值，因此它相當于插入一個新的網志條目：

<?php

　　$conn = oci_connect('SCOTT','TIGER') or die;

　　$sql = 'BEGIN blog.edit_entry(:status, :status_msg, :id, :title, :text, :categories); END;';

　　$stmt = oci_parse($conn,$sql);

　　$title = 'This is a test entry';

　　oci_bind_by_name($stmt,":status",$status,32);

　　oci_bind_by_name($stmt,":status_msg",$status_msg,500);

　　oci_bind_by_name($stmt,":id",$id,32);

　　oci_bind_by_name($stmt,":title",$title,200);

　　// Explained in the next example...(use an empty value for now)

　　$Categories = oci_new_collection($conn,'LIST_OF_NUMBERS');

　　oci_bind_by_name($stmt,':categories',$Categories,32,OCI_B_SQLT_NTY);

　　// Create a new lob descriptor object

　　$textLob = oci_new_descriptor($conn, OCI_D_LOB);

　　// Bind it to the parameter

　　oci_bind_by_name($stmt, ":text", $textLob, -1, OCI_B_CLOB);

　　// Execute the statement but do not commit

　　oci_execute($stmt, OCI_DEFAULT);

　　// The status parameter will be negative if the procedure encountered a problem

　　if ( !$status ) {

　　// Rollback the procedure

　　oci_rollback($conn);

　　die ("$status_msg\n");

　　}

　　// Save the body of the blog entry to the CLOB

　　if ( !$textLob->save('This is the body of the test entry') ) {

　　// Rollback the procedure

　　oci_rollback($conn);

　　die ("Error saving lob\n");

　　}

　　// Everything OK so commit

　　oci_commit($conn);

　　print $status_msg."\n";

　　?>

　正如該腳本所演示的，關鍵問題是如何在使用 LOB 時處理事務。由于更新 LOB 是一個分為兩階段的過程，因此您在此處選擇將所有事務處理委托給 PHP 腳本。注意，默認情況下，Oracle 只允許在任何給定的會話中一次運行一個事務。這意味著從 PHP 調用的過程中發出的 commit 或 rollback 語句將覆蓋對 oci_commit() 或 oci_rollback() 的調用。可以使用匿名事務（使用位于過程定義內部的 pragma PRAGMA AUTONOMOUS_TRANSACTION 啟用）更改此行為。例如，您可以在從其他過程中調用的日志記錄程序包中使用匿名事務；使用這一方法您可以記錄有關存儲過程調用的信息，而不會干擾正在會話中運行的事務。存儲過程和集合集合是一種用于將復雜數據類型傳遞到存儲過程中的機制。在網志應用程序中，可以將網志條目歸檔到多個分類中（與“blogs”表和 “categories”表之間的多對多關系相對應）。必須在數據庫中全局定義 Oracle 中的集合類型，在本示例中，您將使用以下定義：

CREATE OR REPLACE TYPE list_of_numbers AS VARRAY(50) OF NUMBER;

　該定義允許您一次最多向 50 個類別分配一個網志條目，方法是將該類型的實例傳遞給 blog.edit_entry 過程。在 PHP 中，集合由預定義的 PHP 類 OCI-Collection 表示。可以通過調用 oci_new_collection() 函數創建此類實例。OCI-Collection 對象提供了以下方法：

　　append：將元素添加到集合末尾

　　assign：從現有集合中將元素添加到某個集合

　　assignElem：將值分配給集合，并標識應將該元素置于的集合中的索引位置

　　free：釋放與集合句柄關聯的資源

　　getElem：從集合中的特殊索引位置檢索元素

　　max：返回集合中的最大元素數

　　size：返回集合的當前大小

　　trim：從集合末尾刪除一些元素

　　此處，您只希望使用 append 方法，因此可以將類別 ID 列表附加到過程調用。在以下示例中，您將更新在前一個示例中創建的現有網志條目，方法是將它的 ID 傳遞給 blog.edit_entry 過程以及類別 id 列表：

　<?php

　　$conn = oci_connect('SCOTT','TIGER') or die;

　　$sql = 'BEGIN blog.edit_entry(:status, :status_msg, :id, :title, :text, :categories); END;';

　　$stmt = oci_parse($conn, $sql);

　　$id = 1; // ID of the new entry

　　$title = 'This is a test entry (v2)';

　　oci_bind_by_name($stmt,":status",$status,32);

　　oci_bind_by_name($stmt,":status_msg",$status_msg,500);

　　oci_bind_by_name($stmt,":id",$id,32);

　　oci_bind_by_name($stmt,":title",$title,200);

　　$textLob = oci_new_descriptor($conn, OCI_D_LOB);

　　oci_bind_by_name($stmt, ":text", $textLob, -1, OCI_B_CLOB);

　　// Create an OCI-Collection object

　　$Categories = oci_new_collection($conn,'LIST_OF_NUMBERS');

　　// Append some category IDs to the collection;

　　$Categories->append(2);

　　$Categories->append(4);

　　$Categories->append(5);

　　// Bind the collection to the parameter

　　oci_bind_by_name($stmt,':categories',$Categories,-1,OCI_B_SQLT_NTY);

　　oci_execute($stmt, OCI_DEFAULT);

　　if ( !$status ) {

　　oci_rollback($conn);

　　die ("$status_msg\n");

　　}

　　if ( !$textLob->save('This is the body of the test entry [v2]') ) {

　　oci_rollback($conn);

　　die ("Error saving lob\n");

　　}

　　oci_commit($conn);

　　print $status_msg."\n";

　　?>

二，假設有以下存儲過程簽名：

check_login (name  VARCHAR2，pass VARCHAR2)

　其建表等步驟發如上，php腳本如下：

<?php
 function check_login($user, $pass) {
          $conn = ocilogon('user', 'pass', 'database');
          $sql = 'begin :result := test.check_login(:user, :pass); end;';
          $stmt = oci_parse($conn, $sql);

          $result = '';
          oci_bind_by_name($stmt, ':user', $user, 32);
          oci_bind_by_name($stmt, ':pass', md5($pass), 32);
          oci_bind_by_name($stmt, ':result', $result, 10);
          oci_execute($stmt);
          ocilogoff($conn);

          return $result;
 }
 ?>

　結論您現在已經了解了有關如何從 PHP 中調用存儲過程（既包括只涉及標量數據類型的簡單過程，也包含更復雜的使用 LOB、游標和集合的過程）的示例。還對存儲過程的定義進行了足夠的了解，能讀懂它們的 PL/SQL 規范，這樣您就可以從 PHP 中正確地調用它們并綁定相應的類型。

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1354349698304.html

PHP PHP開發

PHP調用Oracle存儲過程

相關經驗

相關資訊

相關文檔

目錄