Skip Headers
Oracle® Database Express Edition 2 Day Plus PHP Developer Guide
10g Release 2 (10.2)

Part Number B25317-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Executing Stored Procedures and Functions

This chapter shows you how to run stored procedures and functions using PHP and Oracle Database Express Edition (Oracle Database XE). It has the following topics:

The Anyco application is extended with a PL/SQL function to calculate remuneration for each employee, and is further extended with a PL/SQL procedure to return a REF CURSOR of employee records.

Using PL/SQL to Capture Business Logic

Oracle PL/SQL procedures and functions enable you to store business logic in the database for any client program to use. They also reduce the amount of data that must be transferred between the database and PHP.

In this section, you will create a PL/SQL stored function to calculate and display the total remuneration for each employee.

To display the total remuneration of each employee, perform the following steps:

  1. In a browser, enter the URL for your Oracle Database Express Edition home page:

    http://localhost:8080/htmldb
    
    
  2. At the login screen, in the Username field enter system, and in the Password field enter manager (or the password you entered at the prompt during configuration of Oracle Database XE). Click Login.

    Description of chap6_stored_proc_linux_001.gif follows
    Description of the illustration chap6_stored_proc_linux_001.gif

  3. In the Home page, click the arrow on the SQL icon, move the mouse over SQL Commands, and click Enter Command:

    Description of chap6_stored_proc_linux_002.gif follows
    Description of the illustration chap6_stored_proc_linux_002.gif

  4. In the SQL Commands page, to assign the create procedure privilege to the HR user, enter the following grant command:

    grant create procedure to hr;
    
    

    Click Run:

    Description of chap6_stored_proc_linux_003.gif follows
    Description of the illustration chap6_stored_proc_linux_003.gif

    A message similar to the following appears in the Results section below the text area where the command was entered:

    Description of chap6_stored_proc_linux_004.gif follows
    Description of the illustration chap6_stored_proc_linux_004.gif

  5. Click the Logout link to terminate the HTMLDB session.

    Description of chap6_stored_proc_linux_005.gif follows
    Description of the illustration chap6_stored_proc_linux_005.gif

  6. In the Logout Confirmation page, click the Login link:

    Description of chap6_stored_proc_linux_006.gif follows
    Description of the illustration chap6_stored_proc_linux_006.gif

  7. In the Oracle Database XE Login page, enter hr in the Username and Password fields. Click Login:

    Description of chap6_stored_proc_linux_007.gif follows
    Description of the illustration chap6_stored_proc_linux_007.gif

  8. In the Home page, click the arrow on the SQL icon, move the mouse over SQL Commands, and click Enter Command:

    Description of chap6_stored_proc_linux_008.gif follows
    Description of the illustration chap6_stored_proc_linux_008.gif

  9. In the SQL Commands page, enter the following text to create a calc_remuneration() function:

    create or replace function calc_remuneration(
      salary IN number, commission_pct IN number) return number is
    begin
      return ((salary*12) + (salary * 12 * nvl(commission_pct,0)));
    end;
    
    

    Click Run:

    Description of chap6_stored_proc_linux_009.gif follows
    Description of the illustration chap6_stored_proc_linux_009.gif

    In the results window, confirm that the function is created:

    Description of chap6_stored_proc_linux_010.gif follows
    Description of the illustration chap6_stored_proc_linux_010.gif

  10. Create the chap6 directory, copy the application files from chap5, and change to the newly created directory:

    On Windows:

    mkdir c:\program files\Apache Group\Apache2\htdocs\chap6
    cd c:\program files\Apache Group\Apache2\htdocs\chap6
    copy ..\chap5\* .
    
    

    On Linux:

    mkdir $HOME/public_html/chap6
    cd $HOME/public_html/chap6
    cp ../chap5/* .
    
    
  11. Edit the anyco.php file. Modify the query in the construct_employees() function to call the PL/SQL function for each row returned:

    $query =
     "SELECT employee_id,
             substr(first_name,1,1) || '. '|| last_name as employee_name,
             hire_date,
             to_char(salary, '9999G999D99') as salary,
             nvl(commission_pct,0) as commission_pct,
             to_char(calc_remuneration(salary, commission_pct),'9999G999D99')
               as remuneration
      FROM employees
      WHERE department_id = :did
      ORDER BY employee_id ASC";
    
    
  12. Edit the anyco_ui.inc file. In the ui_print_employees() function, add a Remuneration column to the table, and modify the foreach loop to display the remuneration field for each employee:

    echo <<<END
       <form method="post" action="$posturl">
       <table>
       <tr>
         <th>&nbsp;</th>
         <th>Employee<br>ID</th>
         <th>Employee<br>Name</th>
         <th>Hiredate</th>
         <th>Salary</th>
         <th>Commission<br>(%)</th>
         <th>Remuneration</th>
       </tr>
    END;
    
        // Write one row per employee
        foreach ($employeerecords as $emp) {
          echo '<tr>';
          echo '<td><input type="radio" name="emprec"
                     value="'.htmlentities($emp['EMPLOYEE_ID']).'"></td>';
          echo '<td align="right">'.htmlentities($emp['EMPLOYEE_ID']).'</td>';
          echo '<td>'.htmlentities($emp['EMPLOYEE_NAME']).'</td>';
          echo '<td>'.htmlentities($emp['HIRE_DATE']).'</td>';
          echo '<td align="right">'.htmlentities($emp['SALARY']).'</td>';
          echo '<td align="right">'.htmlentities($emp['COMMISSION_PCT']).'</td>';
          echo '<td align="right">'.htmlentities($emp['REMUNERATION']).'</td>';
          echo '</tr>';
        }
    
    
  13. Save the changes to your application files. In a browser, enter the following URL to test the application:

    On Windows:

    http://localhost/chap6/anyco.php
    
    

    On Linux:

    http://localhost/~<username>/chap6/anyco.php
    
    
  14. In the Departments page, click Show Employees.

    Description of chap6_stored_proc_test_001.gif follows
    Description of the illustration chap6_stored_proc_test_001.gif

    In the Employees page for the department, the employee remuneration is displayed in the last column:

    Description of chap6_stored_proc_test_002.gif follows
    Description of the illustration chap6_stored_proc_test_002.gif

Using PL/SQL Ref Cursors to Return Result Sets

Data sets can be returned as REF CURSORS from PL/SQL blocks in a PHP script. This can be useful where the data set requires complex functionality.

A REF CURSOR in PL/SQL is a type definition that is assigned to a cursor variable. It is common to declare a PL/SQL type inside a package specification for reuse in other PL/SQL constructs, such as a package body.

In this section, you will use a REF CURSOR to retrieve the employees for a specific department.

To create a PL/SQL package specification and body, with a REF CURSOR to retrieve employees for a specific department, perform the following steps:

  1. In the SQL Commands page, as the HR user, create the following PL/SQL package specification:

    CREATE OR REPLACE PACKAGE cv_types AS
      TYPE empinfotyp IS REF CURSOR;
      PROCEDURE get_employees(deptid in number,
                              employees in out empinfotyp);
    END cv_types;
    
    

    Click Run:

    Description of chap6_refcursor_001.gif follows
    Description of the illustration chap6_refcursor_001.gif

    In the Results section, confirm that the package specification is successfully created:

    Description of chap6_refcursor_002.gif follows
    Description of the illustration chap6_refcursor_002.gif

  2. In the SQL Commands page, as the HR user, create the PL/SQL package body (implementation):

    CREATE OR REPLACE PACKAGE BODY cv_types AS
      PROCEDURE get_employees(deptid in number,
                              employees in out empinfotyp)
      IS
      BEGIN
        OPEN employees FOR
          SELECT employee_id,
            substr(first_name,1,1) || '. '|| last_name as employee_name,
            hire_date,
            to_char(salary, '999G999D99') as salary,
            NVL(commission_pct,0) as commission_pct,
            to_char(calc_remuneration(salary, commission_pct),
                    '9999G999D99') as remuneration
          FROM employees
          WHERE department_id = deptid
          ORDER BY employee_id ASC;
      END get_employees;
    END cv_types;
    
    

    Click Run:

    Description of chap6_refcursor_003.gif follows
    Description of the illustration chap6_refcursor_003.gif

    In the Results section, confirm that the package body is successfully created:

    Description of chap6_refcursor_004.gif follows
    Description of the illustration chap6_refcursor_004.gif

  3. Edit the anyco_db.inc file. Create a new PHP function that calls the PL/SQL packaged procedure:

        // Use ref cursor to fetch employee records
        // All records are retrieved - there is no paging in this example 
        function db_get_employees_rc($conn, $deptid, &$e)
        {
          // Execute the call to the stored procedure
          $stmt = "BEGIN cv_types.get_employees($deptid, :rc); END;";
          $stid = @oci_parse($conn, $stmt);
          if (!$stid) {
            $e = db_error($conn, __FILE__, __LINE__);
            return false;
          }
          $refcur = oci_new_cursor($conn);
          if (!$stid) {
            $e = db_error($conn, __FILE__, __LINE__);
            return false;
          }
          $r = @oci_bind_by_name($stid, ':RC', $refcur, -1, OCI_B_CURSOR);
          if (!$r) {
            $e = db_error($stid, __FILE__, __LINE__);
            return false;
          }
          $r = @oci_execute($stid);
          if (!$r) {
            $e = db_error($stid, __FILE__, __LINE__);
            return false;
          }
          // Now treat the ref cursor as a statement resource
          $r = @oci_execute($refcur, OCI_DEFAULT);
          if (!$r) {
            $e = db_error($refcur, __FILE__, __LINE__);
            return false;
          }
          $r = @oci_fetch_all($refcur, $employeerecords, null, null,
                              OCI_FETCHSTATEMENT_BY_ROW);
          if (!$r) {
            $e = db_error($refcur, __FILE__, __LINE__);
            return false;
          }
          return ($employeerecords);
        }
    
    

    The db_get_employees_rc() function executes the following anonymous (unnamed) PL/SQL block:

    BEGIN cv_types.get_employees($deptid, :rc); END;
    
    

    The PL/SQL statement inside the BEGIN END block calls the stored PL/SQL package procedure cv_types.et_employees(). This returns an OCI_B_CURSOR REF CURSOR bind variable in the PHP variable $refcur.

    The $refcur variable is treated as a statement handle that is used for execute and fetch operations.

  4. Edit the anyco.php file. In the construct_employees() function, remove the query text and the bind arguments. The function becomes:

    function construct_employees()
        {
          $deptid = $_SESSION['deptid'];
          $conn = db_connect($err);
          if (!$conn) {
            handle_error('Connection Error', $err);
          }
          else {
            $emp = db_get_employees_rc($conn, $deptid, $err);
    
            if (!$emp) {
              handle_error('Cannot fetch Employees', $err);
            }
            else {
              $deptname = get_dept_name($conn, $deptid);
    
              ui_print_header('Employees: '.$deptname);
              ui_print_employees($emp, $_SERVER['SCRIPT_NAME']);
              ui_print_footer(date('Y-m-d H:i:s'));
            }
          }
        }
    
    
  5. Save the changes to your application files. In a browser, enter the following URL to test the application:

    On Windows:

    http://localhost/chap6/anyco.php
    
    

    On Linux:

    http://localhost/~<username>/chap6/anyco.php
    
    
  6. In the Departments page, click Next to navigate to the Marketing department page.

    Description of chap6_refcursor_005.gif follows
    Description of the illustration chap6_refcursor_005.gif

  7. In the Marketing department page, click Show Employees.

    Description of chap6_refcursor_006.gif follows
    Description of the illustration chap6_refcursor_006.gif

    In the Employees page for the Marketing department, the employee records remuneration is displayed in the last column:

    Description of chap6_refcursor_007.gif follows
    Description of the illustration chap6_refcursor_007.gif

  8. In the SQL Commands page, to log out of the HR database session, click the Logout link.

    Description of chap6_refcursor_008.gif follows
    Description of the illustration chap6_refcursor_008.gif