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.

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;