6 Executing Stored Procedures and Functions

This chapter shows you how to run stored procedures and functions using PHP and Oracle Database. 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 and can help improve performance.

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:

The PHP application connects to the database as the HR user. You may need to unlock the HR account as a user with DBA privileges. To unlock the HR user:

  1. Open SQL Developer and open a connection to your Oracle database.

  2. Login to your Oracle database as system.

  3. Open SQL Workheet or SQL*Plus and run the following grant statement to assign the create procedure privilege to the HR user:

    grant create procedure to hr;
    
    
    Description of chap6_hrgrantproc.gif follows
    Description of the illustration chap6_hrgrantproc.gif

  4. Login to your HR sample schema as hr.

  5. Open SQL Workheet or SQL*Plus and 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;
    
    
    Description of chap6_hrcalcrem.gif follows
    Description of the illustration chap6_hrcalcrem.gif

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

    On Windows:

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

    On Linux:

    mkdir $HOME/public_html/chap6
    cd $HOME/public_html/chap6
    cp ../chap5/* .
    
    
  7. 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";
    
    
  8. 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>';
        }
    
    
  9. 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
    
    
  10. 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

Query data can be returned as REF CURSORS from PL/SQL blocks and displayed in PHP. This can be useful where the data set requires complex functionality or where you want multiple application programs to use the same query.

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. Open SQL Developer and login to your HR sample schema as hr.

  2. Open SQL Workheet or SQL*Plus and enter the following text to create the cv_types PL/SQL package:

    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_hrcreatepack.gif follows
    Description of the illustration chap6_hrcreatepack.gif

  3. In SQL Workheet enter the following text to create the cv_types PL/SQL package body:

    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_hrcreatebody.gif follows
    Description of the illustration chap6_hrcreatebody.gif

  4. 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 like a statement handle returned by oci_parse(). It is used for execute and fetch operations just as if the SQL query had been done in PHP.

  5. 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'));
        }
      }
    }
    
    
  6. 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
    
    
  7. 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

  8. 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 pages displays as previously:

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