PHPOracle存儲過程

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

　　sayHello (name IN VARCHAR greeting OUT VARCHAR)

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

　　PROCEDURE edit_entry(

　　status_out OUT NUMBER

　　status_msg_out OUT VARCHAR

　　id_inout IN OUT INTEGER

　　title_in IN VARCHAR

　　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 變量綁定到的參數的數目和類型來決定要調用哪個過程 復雜類型 存儲過程使用的參數並不只局限於 VARCHAR 和 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 VARCHAR greeting OUT VARCHAR)

　　AS

　　BEGIN

　　greeting := Hello || name;

　　END;

　　/

　　注意您可以使用 SQL*Plus 命令行運行上面的語句將該語句保存到文件 (SAYHELLOSQL)接下來使用 SQL*Plus 登錄

　　$ sqlplus username@SID

　　然後使用 START 命令創建該過程

　　SQL> START /home/username/SAYHELLOSQL

　　以下 PHP 腳本調用該過程

　　<?php

　　$conn = oci_connect(SCOTTTIGER) or die;

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

　　$stmt = oci_parse($conn$sql);

　　//  Bind the input parameter

　　oci_bind_by_name($stmt:name$name);

　　// Bind the output parameter

　　oci_bind_by_name($stmt:message$message);

　　// 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 entryIf id_inout is NULL results in an INSERT otherwise

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

　　 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() OF NUMBER;

　　*/

　　PROCEDURE edit_entry(

　　status_out OUT NUMBER

　　status_msg_out OUT VARCHAR

　　id_inout IN OUT INTEGER

　　title_in IN VARCHAR

　　text_out OUT CLOB

　　categories_in IN list_of_numbers

　　);

　　END blog;

　　/

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

　　由以下語句定義

　　CREATE OR REPLACE TYPE list_of_numbers AS VARRAY() 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 VARCHAR

　　id_inout IN OUT INTEGER

　　title_in IN VARCHAR

　　text_out OUT CLOB

　　categories_in IN list_of_numbers

　　AS

　　ENTRY_NOT_FOUND EXCEPTION;

　　entry_found INTEGER := ;

　　BEGIN

　　/* Default status to success */

　　status_out := ;

　　/* 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 bid = id_inout;

　　IF entry_found != THEN RAISE ENTRY_NOT_FOUND; END IF;

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

　　UPDATE blogs b SET btitle = title_in btext = EMPTY_CLOB()

　　WHERE bid = id_inout RETURNING btext 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 ( bid btitle bdate_published btext )

　　VALUES ( blog_id_seqnextval title_in SYSDATE EMPTY_CLOB() )

　　RETURNING bid btext 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 unt

　　LOOP

　　INSERT INTO blogs_to_categories (blog_idcategory_id)

　　VALUES (id_inoutcategories_in(i));

　　END LOOP;

　　status_msg_out := status_msg_out || added to

　　|| unt || categories;

　　EXCEPTION

　　/* Catch the exception when id_inout not found */

　　WHEN ENTRY_NOT_FOUND THEN

　　status_out := ;

　　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 := ;

　　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 ;

　　/

　　CREATE TABLE blogs (

　　id NUMBER PRIMARY KEY

　　title VARCHAR()

　　date_published DATE

　　text CLOB

　　);

　　/

　　CREATE SEQUENCE category_id_seq

　　INCREMENT BY ;

　　CREATE TABLE categories (

　　id NUMBER PRIMARY KEY

　　name VARCHAR() 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)

　　);

　　/

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

　　<?php

　　$conn = oci_connect(SCOTTTIGER) or die;

　　$sql = BEGIN bloglatest(:num_entries :blog_entries); END;;

　　$stmt = oci_parse($conn $sql);

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

　　$max_entries = ;

　　oci_bind_by_name($stmt:num_entries$max_entries);

　　// 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_entriesOCI_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 調用 blogedit_entry 過程該示例未向 id 參數分配值因此它相當於插入一個新的網志條目

　　<?php

　　$conn = oci_connect(SCOTTTIGER) or die;

　　$sql = BEGIN blogedit_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);

　　oci_bind_by_name($stmt:status_msg$status_msg);

　　oci_bind_by_name($stmt:id$id);

　　oci_bind_by_name($stmt:title$title);

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

　　$Categories = oci_new_collection($connLIST_OF_NUMBERS);

　　oci_bind_by_name($stmt:categories$CategoriesOCI_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 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() OF NUMBER;

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

　　append將元素添加到集合末尾

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

　　assignElem將值分配給集合並標識應將該元素置於的集合中的索引位置

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

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

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

　　size返回集合的當前大小

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

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

　　<?php

　　$conn = oci_connect(SCOTTTIGER) or die;

　　$sql = BEGIN blogedit_entry(:status :status_msg :id :title :text :categories); END;;

　　$stmt = oci_parse($conn $sql);

　　$id = ; // ID of the new entry

　　$title = This is a test entry (v);

　　oci_bind_by_name($stmt:status$status);

　　oci_bind_by_name($stmt:status_msg$status_msg);

　　oci_bind_by_name($stmt:id$id);

　　oci_bind_by_name($stmt:title$title);

　　$textLob = oci_new_descriptor($conn OCI_D_LOB);

　　oci_bind_by_name($stmt :text $textLob OCI_B_CLOB);

　　// Create an OCICollection object

　　$Categories = oci_new_collection($connLIST_OF_NUMBERS);

　　// Append some category IDs to the collection;

　　$Categories>append();

　　$Categories>append();

　　$Categories>append();

　　// Bind the collection to the parameter

　　oci_bind_by_name($stmt:categories$CategoriesOCI_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 [v]) ) {

　　oci_rollback($conn);

　　die (Error saving lob\n);

　　}

　　oci_commit($conn);

　　print $status_msg\n;

　　?>

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

From:http://tw.wingwit.com/Article/program/Oracle/201311/16569.html