6 Showing Equipment Records by Using a REF CURSOR

Introduction to PL/SQL Packages and Package Bodies

A PL/SQL package stores related items as a single logical entity. A package is composed of two distinct pieces:

• The package specification defines what is contained in the package; it is analogous to a header file in a language such as C++. The specification defines all public items. The specification is the published interface to a package.

• The package body contains the code for the procedures and functions defined in the specification, and the code for private procedures and functions that are not declared in the specification. This private code is only visible within the package body.

The package specification and body are stored as separate objects in the data dictionary and can be seen in the user_source view. The specification is stored as the PACKAGE type, and the body is stored as the PACKAGE BODY type.

While it is possible to have a specification without a body, as when declaring a set of public constants, it is not possible to have a body with no specification.

Introduction to PL/SQL Stored Procedures

A stored procedure is a named set of PL/SQL statements designed to perform an action. Stored procedures are stored inside the database. They define a programming interface for the database rather than allowing the client application to interact with database objects directly. Stored procedures are typically used for data validation or to encapsulate large, complex processing instructions that combine several SQL queries.

Stored functions have a single return value parameter. Unlike functions, procedures may or may not return values.

Introduction to REF CURSORs

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database.

REF CURSORs have the following characteristics:

• A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR to access it.

• A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Data is not retrieved until the user attempts to read it.

• A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.

• A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.

• A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

The Equipment Table

This manual's example scenario is that AnyCo Corp issues each employee various pieces of equipment to do their job. An EQUIPMENT table will hold the equipment names and which employee it was issued to.

In SQL*Plus connect as the HR user and run the following script:

sqlplus hr/welcome@localhost

CREATE TABLE equipment(
    id          NUMBER PRIMARY KEY,
    employee_id REFERENCES employees(employee_id) ON DELETE CASCADE,
    equip_name  VARCHAR2(20) NOT NULL);
 
CREATE SEQUENCE equipment_seq;
CREATE TRIGGER equipment_trig BEFORE INSERT ON equipment FOR EACH ROW
BEGIN
    :NEW.id := equipment_seq.NEXTVAL;
END;
/

The PL/SQL sequence and trigger assign a unique key to each new equipment record as it is inserted.

If you run these statements in a SQL editor, such as in NetBeans, omit the trailing slash ('/') in the CREATE TRIGGER statement. The slash is SQL*Plus's end-of-statement indicator and is not part of the statement that is run by the database.

Create some sample data:

-- Sample Data
INSERT INTO equipment (employee_id, equip_name) VALUES (100, 'pen');
INSERT INTO equipment (employee_id, equip_name) VALUES (100, 'telephone');
INSERT INTO equipment (employee_id, equip_name) VALUES (101, 'pen');
INSERT INTO equipment (employee_id, equip_name) VALUES (101, 'paper');
INSERT INTO equipment (employee_id, equip_name) VALUES (101, 'car');
INSERT INTO equipment (employee_id, equip_name) VALUES (102, 'pen');
INSERT INTO equipment (employee_id, equip_name) VALUES (102, 'paper');
INSERT INTO equipment (employee_id, equip_name) VALUES (102, 'telephone');
INSERT INTO equipment (employee_id, equip_name) VALUES (103, 'telephone');
INSERT INTO equipment (employee_id, equip_name) VALUES (103, 'computer');
INSERT INTO equipment (employee_id, equip_name) VALUES (121, 'computer');
INSERT INTO equipment (employee_id, equip_name) VALUES (180, 'pen');
INSERT INTO equipment (employee_id, equip_name) VALUES (180, 'paper');
INSERT INTO equipment (employee_id, equip_name) VALUES (180, 'cardboard box');
COMMIT;

In SQL*Plus create a procedure as HR:

CREATE OR REPLACE PROCEDURE get_equip(eid_p IN NUMBER, RC OUT SYS_REFCURSOR) AS
BEGIN
    OPEN rc FOR SELECT   equip_name
                FROM     equipment
                WHERE    employee_id = eid_p
                ORDER BY equip_name;
END;
/

In PHP this procedure can be called by running an anonymous PL/SQL block:

BEGIN get_equip(:id, :rc); END;

The :id bind variable is used similarly to binds shown before. It passes a value from a PHP variable into the database for the WHERE clause of get_equip(). The bind variable :rc is different and will hold the query results returned from equip_name() as explained in a few moments.

Calling the REF CURSOR in PHP

To display an employee's list of equipment create a new PHP file ac_show_equip.php:

<?php
 
/**
 * ac_show_equip.php: Show an employee's equipment
 * @package ShowEquipment
 */
 
session_start();
require('ac_db.inc.php');
require('ac_equip.inc.php');
 
$sess = new \Equipment\Session;
$sess->getSession();
if (!isset($sess->username) || empty($sess->username)
        || !isset($_GET['empid'])) {
    header('Location: index.php');
    exit;
}
$empid = (int) $_GET['empid'];
 
$page = new \Equipment\Page;
$page->printHeader("AnyCo Corp. Show Equipment");
$page->printMenu($sess->username, $sess->isPrivilegedUser());
printcontent($sess, $empid);
$page->printFooter();
 
// Functions
 
?>

This is similar in structure to ac_emp_list.php. This time the verification test after $sess->getSession() also checks for an employee identifier. This value is passed in as a URL parameter from the printrecords() function in ac_emp_list.php:

...
<a href='ac_show_equip.php?empid=$eid'>Show</a>
...

The identifier value is accessed in ac_show_equip.php through PHP's $_GET superglobal array. If the array entry is not set then the assumption is that ac_show_equip.php was called incorrectly and the user is redirected to the login page, index.php.

The $_GET['empid'] value is cast to an integer to minimize potential SQL injection issues. Although the value will be bound, it is better to consistently filter all user input. If $_GET['empid'] contained alphabetic text for some reason, PHP's casting rules will result in the number 0 being stored in $empid. If the text had a numeric prefix then $empid would be that number, but at least the following text would have been discarded.

Before getting to the main content of the file, add a small helper function getempname() in the Functions section of ac_show_equip.php:

/**
 * Get an Employee Name
 *
 * @param Db $db
 * @param integer $empid
 * @return string An employee name
 */
function getempname($db, $empid) {
    $sql = "SELECT first_name || ' ' || last_name AS emp_name
        FROM employees
        WHERE employee_id = :id";
    $res = $db->execFetchAll($sql, "Get EName", array(array(":id", $empid, -1)));
    $empname = $res[0]['EMP_NAME'];
    return($empname);
}

This takes the employee identifier that the script was invoked for and looks up the matching employee name. An exercise for the reader is to handle the case when the query does not return any rows.

Now add the main printcontent() function to ac_show_equip.php:

/**
 * Print the main body of the page
 *
 * @param Session $sess
 * @param integer $empid Employee identifier
 */
function printcontent($sess, $empid) {
    echo "<div id='content'>\n";
    $db = new \Oracle\Db("Equipment", $sess->username);
    $empname = htmlspecialchars(getempname($db, $empid), ENT_NOQUOTES, 'UTF-8');
    echo "$empname has: ";
 
    $sql = "BEGIN get_equip(:id, :rc); END;";
    $res = $db->refcurExecFetchAll($sql, "Get Equipment List",
            "rc", array(array(":id", $empid, -1)));
    if (empty($res['EQUIP_NAME'])) {
        echo "no equipment";
    } else {
        echo "<table border='1'>\n";
        foreach ($res['EQUIP_NAME'] as $item) {
            $item = htmlspecialchars($item, ENT_NOQUOTES, 'UTF-8');
            echo "<tr><td>$item</td></tr>\n";
        }
        echo "</table>\n";
    }
    echo "</div>";  // content
}

This calls a new method Db::refcurExecFetchAll() which will return an array of records, printed in our traditional loop.

The REF CURSOR bind parameter :rc must be bound specially. Since the bind variable name could be arbitrarily chosen or located anywhere in the statement text, its name is passed separately into refcurExecFetchAll() and it is not included in the array of normal bind variables.

Now create the refcurExecFetchAll() method by editing ac_db.inc.php and adding this to the Db class:

    /**
     * Run a call to a stored procedure that returns a REF CURSOR data
     * set in a bind variable.  The data set is fetched and returned.
     *
     * Call like Db::refcurexecfetchall("begin myproc(:rc, :p); end",
     *                            "Fetch data", ":rc", array(array(":p", $p, -1)))
     * The assumption that there is only one refcursor is an artificial
     * limitation of refcurexecfetchall()
     *
     * @param string $sql A SQL string calling a PL/SQL stored procedure
     * @param string $action Action text for End-to-End Application Tracing
     * @param string $rcname the name of the REF CURSOR bind variable
     * @param array  $otherbindvars Binds. Array (bv_name, php_variable, length)
     * @return array Returns an array of tuples
     */
    public function refcurExecFetchAll($sql, $action, $rcname, 
    $otherbindvars = array()) {
        $this->stid = oci_parse($this->conn, $sql);
        $rc = oci_new_cursor($this->conn);
        oci_bind_by_name($this->stid, $rcname, $rc, -1, OCI_B_CURSOR);
        foreach ($otherbindvars as $bv) {
            // oci_bind_by_name(resource, bv_name, php_variable, length)
            oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
        }
        oci_set_action($this->conn, $action);
        oci_execute($this->stid);
        oci_execute($rc); // run the ref cursor as if it were a statement id
        oci_fetch_all($rc, $res);   
        $this->stid = null;
        return($res);
    }

The REF CURSOR bind parameter in $rcname is bound to a cursor created with oci_new_cursor(), not to a normal PHP variable. The type OCI_B_CURSOR must specified.

After setting the tracing "action" text, the PL/SQL statement is run. In this example it calls get_equip() which opens and returns the cursor for the query. The REF CURSOR in $rc can now be treated like a PHP statement identifier as if it had been returned from an oci_parse() call. It is then fetched from. The query results are returned in $res to the function caller.

Save all files and run the application in a browser. Login as either Simon or Administrator. Click the Show link next to Steven King. The equipment he has is displayed: