Oracle® Database Express Edition 2 Day Plus PHP Developer Guide 10g Release 2 (10.2) Part Number B25317-01 |
|
|
View PDF |
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.
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:
In a browser, enter the URL for your Oracle Database Express Edition home page:
http://localhost:8080/htmldb
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.
In the Home page, click the arrow on the SQL icon, move the mouse over SQL Commands, and click Enter Command:
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:
A message similar to the following appears in the Results section below the text area where the command was entered:
Click the Logout link to terminate the HTMLDB session.
In the Logout Confirmation page, click the Login link:
In the Oracle Database XE Login page, enter hr
in the Username and Password fields. Click Login:
In the Home page, click the arrow on the SQL icon, move the mouse over SQL Commands, and click Enter Command:
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:
In the results window, confirm that the function is created:
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> </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:
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:
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:
In the Results section, confirm that the package specification is successfully created:
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:
In the Results section, confirm that the package body is successfully created:
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.
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 records remuneration is displayed in the last column:
In the SQL Commands page, to log out of the HR database session, click the Logout link.