6 Executing Stored Procedures and Functions

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

Login to your Oracle database as system.

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

grant create procedure to hr;

Login to your HR sample schema as hr.

Open SQL Worksheet 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;

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/* .

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

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>';
    }

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

In the Departments page, click Show Employees.

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

Open SQL Developer and login to your HR sample schema as hr.

Open SQL Worksheet 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:

In SQL Worksheet 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:

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.

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

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

In the Departments page, click Next to navigate to the Marketing department page.

In the Marketing department page, click Show Employees.

In the Employees page for the Marketing department, the employee pages displays as previously: