11.19 Database Access with SQL

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.

SQL%ROWCOUNT

This function evaluates to the number of rows affected by the last INSERT and UPDATE. It equals zero if no rows were changed.

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:

%NOTFOUND	This returns TRUE or FALSE based on the last fetch.
%FOUND	The negation of %NOTFOUND.
%ROWCOUNT	This attribute returns the number of rows fetched so far. It can be called anytime after the first fetch. This attribute also returns the number of rows affected from UPDATE and DELETE statements.
%ISOPEN	Returns TRUE if a cursor is still open.

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;