messDevLop'mnt

PHP에서 Oracle 스토어드 프로시저 호출하기
저자 Harry Fuecks

Oracle/PHP 환경에서의 스토어드 프로시저 활용법을 소개합니다.

아티클 관련 다운로드:  Oracle Database 10g   Zend Core for Oracle  Apache HTTP Server 1.3 and later

게시일: 2005년 11월

sayHello (name IN VARCHAR2, greeting OUT VARCHAR2)

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
);

status_out OUT NUMBER,

id_inout IN OUT INTEGER,

categories_in IN list_of_numbers

• IN—매개변수가 호출 프로그램에 의해 입력됩니다.
• OUT—매개변수의 값이 스토어드 프로시저에 할당되어 호출 프로그램에 반환됩니다.
• IN OUT—매개변수가 “양방향”으로 사용됩니다. 다시 말해, 호출 프로그램이 이 매개변수를 통해 값을 전달할 수도 있고, 스토어드 프로시저가 매개변수의 값을 수정할 수도 있습니다.

<?php
// etc.

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

sayHello 프로시저가 아래 DDL 구문에 의해 정의되었다고 가정해 봅시다:

CREATE OR REPLACE PROCEDURE 
        sayHello (name IN VARCHAR2, greeting OUT VARCHAR2) 
    AS
        BEGIN
            greeting := 'Hello ' || name;
        END;
/

$ sqlplus username@SID

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";
?>

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;
/

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

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;
/

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 테이블의 여러 로우를 반복적으로 조회하기 위해 레퍼런스 커서(reference cursor)가 반환됨을 확인할 수 있습니다.

<?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);
}
?>

아래 예제는 CLOB를 이용하여 blog.edit_entry 프로시저를 호출하는 방법을 보여주고 있습니다. 이 예제에서는 id 매개변수에 값이 할당되지 않았으며, 따라서 새로운 blog 게시물의 INSERT 작업이 수행됩니다:

<?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";
?>

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

• append: 엘리먼트를 컬렉션 마지막 부분에 추가합니다
• assign: 기존 컬렉션의 엘리먼트를 해당 컬렉션에 추가합니다
• assignElem: 컬렉션에 값을 할당하고, 컬렉션에서의 엘리먼트 위치를 지정하기 위해 인덱스를 정의합니다
• free: 컬렉션 핸들(collection handle)에 설정된 리소스를 해제합니다
• getElem: 컬렉션의 특정 인덱스 위치로부터 엘리먼트를 가져옵니다
• max: 컬렉션의 최대 엘리먼트 수를 반환합니다
• size: 컬렉션의 현재 크기를 반환합니다
• trim: 컬렉션의 마지막 부분에서부터 일정한 수의 엘리먼트를 제거합니다

<?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";
?>