Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

11.19.1 Cursor FOR LOOPS

The cursor FOR LOOP requires use of SQL and a PL/SQL FOR LOOP. This is a simple approach to querying the database. You can construct a cursor for loop using any valid SQL statement.

The syntax is:

FOR your_name IN (SELECT rest of SQL statement LOOP your_name.column_name is defined here END LOOP;

The following block selects all student names and the sum of their parking tickets. The LOOP temporary variable, REC, only exists for the duration of the LOOP. Within the loop we can access columns in the query through the record, REC.

BEGIN FOR rec IN (SELECT student_name, sum(amount) parking_ticket_total FROM students a, student_vehicles b, parking_tickets c WHERE a.student_id = b.student_id AND b.state=c.state and b.tag_no=c.tag_no GROUP BY student_name) LOOP dbms_output.put_line (rec.student_name' 'rec.parking_ticket_total); END LOOP; END;

The aggregate function, SUM, requires a column alias to resolve an attribute name for the record. Literals and expressions must have a column alias. That alias is then used to select the component value from the record.

If the SQL query has a zero result set (i.e., no rows are returned), the loop exits gracefully. There is no exception condition raised.

The cursor can be declared in the declarative part. The loop references the cursor variable.

DECLARE CURSOR C1 IS SELECT student_name, sum(amount) parking_ticket_total FROM students a, student_vehicles b, parking_tickets c WHERE a.student_id = b.student_id AND b.state=c.state and b.tag_no=c.tag_no GROUP BY student_name; BEGIN FOR rec IN C1 LOOP dbms_output.put_line (rec.student_name' 'rec.parking_ticket_total); END LOOP; END;

11.19.2 Select When Expecting a Single Row

You can expect a single row when the query includes a primary key. The possibility does exist that no row is returned ”this would occur if no row exists with that primary key value.

If no rows are returned the NO_DATA_FOUND exception is raised. If there should be multiple rows, the exception TOO_MANY_ROWS is raised.

The only way this would occur would be if the primary key constraint was disabled and duplicate data was loaded.

The following is a stored procedure that selects a single row. If more than one row is returned the exception is raised and caught. If no rows are returned the exception is raised and caught.

CREATE OR REPLACE PROCEDURE get_student_major (v_student_id IN students.student_id%TYPE, v_name OUT students.student_name%TYPE) IS BEGIN SELECT student_name INTO v_name FROM students WHERE student_id = v_student_id; exception WHEN TOO_MANY_ROWS THEN dbms_output.put_line('TMR error'); WHEN NO_DATA_FOUND THEN dbms_output.put_line('NDF error'); END;

11.19.3 Inserts and Updates

Inserts and update statements in PL/SQL are very similar to executing SQL in an interactive environment like SQL*Plus. The function SQL%ROWCOUNT is useful after update statements.

The following function updates professor salaries and returns the number of rows updated. The type of the return argument is NATURAL because the number of updated rows will always be zero or greater.

CREATE OR REPLACE FUNCTION update_salaries(new_sal IN professors.salary%TYPE) RETURN NATURAL IS BEGIN UPDATE professors2 SET salary = new_sal; RETURN SQL%ROWCOUNT; END update_salaries;

Use %ROWTYPE to pass multiple components to a subprogram. This procedure inserts a student and accepts a %ROWTYPE as a single parameter. The STUDENT_ID is not included in the original record. That is generated as a sequence number. This procedure inserts the student and returns the STUDENT_ID as part of the record.

A letter "A" is a prefix for the sequence number. The sample data from Chapter 4 includes student ID numbers that begin with a letter. This is in keeping with that convention.

CREATE OR REPLACE PROCEDURE add_student(rec IN OUT students%ROWTYPE) IS BEGIN SELECT 'A'students_pk_seq.nextval INTO rec.student_id FROM dual; INSERT INTO students (student_id, student_name, college_major, status, state, license_no) VALUES (rec.student_id, rec.student_name, rec.college_major, rec.status, rec.state, rec.license_no); END add_student;

This procedure does an insert. Why does it need to return the record? Why can't REC be IN rather than IN OUT?

It depends on the agreement between the programmer of ADD_STUDENT and those who call this procedure ”that may be the same programmer. The calling procedure may need the STUDENT_ID to insert a record in the child table. One option is to pass STUDENT_ID back to the caller using the same record. The IN OUT mode allows the caller to get the STUDENT_ID that was added to the record by this procedure.

An alternative is for the procedure to insert the student and return the STUDENT_ID from a function. This would be a slightly different calling interface. In either case the caller has the new STUDENT_ID for additional use. The following is an interface for inserting a student and a student vehicle. Refer to the DDL in Chapter 4 that shows the data model. In this model STUDENT_VEHICLES is a child to STUDENTS.

A specification for adding student information is the following. This is a package specification that adds a student and adds a student vehicle.

CREATE OR REPLACE PACKAGE students_pkg IS FUNCTION add_student(rec IN students%ROWTYPE) RETURN students.student_id%TYPE; PROCEDURE add_vehicle(rec IN student_vehicles%ROWTYPE); END;

The user of this package must first call ADD_STUDENT and then call ADD_VEHICLE. Prior to calling ADD_VEHICLE, the STUDENT_ID must be added to the vehicle record ”below this is the assignment prior to calling ADD_VEHICLE. This enforces referential integrity ”the STUDENT_ID column is a foreign key to the STUDENTS table.

The user of the preceding package would have PL/SQL logic similar to the following.

DECLARE student students%ROWTYPE; vehicle student_vehicles%ROWTYPE; BEGIN student.student_name := 'Jack'; student.college_major := 'HI'; student.status := 'Degree'; student.state := 'CA'; student.license_no := 'MV-232-14'; student.student_id := students_pkg.add_student(student); vehicle.state := 'CA'; vehicle.tag_no := 'CA-1234'; vehicle.vehicle_desc := 'Mustang'; vehicle.parking_sticker := 'A-101'; vehicle.student_id := student.student_id; students_pkg.add_vehicle(vehicle); END;

The body for the STUDENTS_PKG is shown next .

CREATE OR REPLACE PACKAGE BODY students_pkg IS FUNCTION add_student(rec IN students%ROWTYPE) RETURN students.student_id%TYPE IS ID students.student_id%TYPE; BEGIN SELECT 'A'students_pk_seq.nextval INTO ID FROM dual; INSERT INTO students (student_id, student_name, college_major, status, state, license_no) VALUES (ID, rec.student_name, rec.college_major, rec.status, rec.state, rec.license_no); RETURN ID; END add_student; PROCEDURE add_vehicle(rec IN student_vehicles%ROWTYPE) IS BEGIN INSERT INTO student_vehicles (state, tag_no, vehicle_desc, student_id, parking_sticker) VALUES (rec.state, rec.tag_no, rec.vehicle_desc, rec.student_id, rec.parking_sticker); END add_vehicle; END students_pkg;

11.19.4 Explicit Cursors

Explicit cursors follow a DO-WHILE-DO loop model; that is:

OPEN cursor LOOP FETCH a record EXIT WHEN no row returned. Process this fetched row. END LOOP; CLOSE cursor

The cursor-for loop, discussed previously, is a simple approach to querying the database. The cursor-for loop generally has better performance than explicit cursors. However, an explicit cursor may be more appropriate for a particular algorithm. The following discussion shows the main features of an explicit cursor. Then an example is shown.

An explicit cursor requires a cursor definition. This is a SQL statement and a cursor variable. A cursor record structure is declared. The datatype of the cursor record is derived from the cursor definition.

The following PL/SQL block declares a cursor that joins tables STATE_LOOKUP and STUDENTS. To make this interesting, the following features are incorporated into the PL/SQL block:

• REPLACE and NVL functions are used. These functions replace a dash with a space in the student license number. Also, the string "None" is replaced with a NULL should there be no license. A string "N/A" replaces NULL if there is no state.

• The SQL statement in the cursor uses an outer join. This is necessary to include students who have a NULL state column value. Not all students have a license; hence their STATE value is NULL. The STUDENTS is joined with STATE_LOOKUP on the STATE column. The plus operator (+) is appended to the STUDENTS table in the FROM clause.

• The rows are fetched and copied into a global temporary table. After the script, the table is queried for output. The script could use DBMS_OUTPUT, but there is a buffer limitation to DBMS_OUTPUT. The temporary table is more appropriate for spooling large amounts of data.

DROP TABLE TEMP; CREATE GLOBAL TEMPORARY TABLE temp (name VARCHAR2(10), state VARCHAR2(15), license VARCHAR2(20)); DECLARE CURSOR student_cursor IS SELECT a.student_name, NVL(b.state_desc, 'N/A') state_desc, NVL(REPLACE(a.license_no,'-',' '), 'None') Lic FROM students a, state_lookup b WHERE a.state = b.state(+); student_cursor_rec student_cursor%ROWTYPE; BEGIN OPEN student_cursor; LOOP FETCH student_cursor INTO student_cursor_rec; EXIT WHEN student_cursor%NOTFOUND; INSERT INTO temp VALUES (student_cursor_rec.student_name, student_cursor_rec.state_desc, student_cursor_rec.lic); END LOOP; CLOSE student_cursor; END; SELECT * FROM TEMP;

This PL/SQL block exits the loop on the evaluation of: %NOTFOUND. This cursor attribute is TRUE or FALSE and should be checked after each fetch. An explicit cursor has the following attributes:

A cursor can be parameter driven. The following block declares a cursor that joins the STUDENTS and STATE_LOOKUP table, but only for students with a particular STATUS. That STATUS is determined when the cursor is opened. This example opens the cursor using a literal string, "Degree."

DECLARE CURSOR student_cursor (v_student_status students.status%type) IS SELECT a.student_name, NVL(b.state_desc, 'N/A') state_desc, NVL(REPLACE(a.license_no,'-',' '), 'None') Lic FROM students a, state_lookup b WHERE a.state = b.state(+) AND a.status = v_student_status; student_cursor_rec student_cursor%ROWTYPE; BEGIN OPEN student_cursor('Degree'); LOOP FETCH student_cursor INTO student_cursor_rec; EXIT WHEN student_cursor%NOTFOUND; INSERT INTO temp VALUES (student_cursor_rec.student_name, student_cursor_rec.state_desc, student_cursor_rec.lic); END LOOP; CLOSE student_cursor; END;