PL/SQL Notes 2
Before you start
See the notes on page 1.
Read only one record from the database
If you are sure that you are only reading one record
from the database, you can read it directly into variables
as shown in this example.
DECLARE
my_name varchar2(30);
my_age REAL;
BEGIN
SELECT sname, age
INTO my_name, my_age
FROM sailors
WHERE sailors.sid = 1;
DBMS_OUTPUT.PUT_LINE('Found name = ' || my_name || ', age = ' || my_age);
END;
Read only one record from the database -- use %TYPE
You can use %TYPE to declare variables that are the same
type as database table fields.
DECLARE
my_name sailors.sname%TYPE;
my_age sailors.age%TYPE;
BEGIN
SELECT sname, age
INTO my_name, my_age
FROM sailors
WHERE sailors.sid = 1;
DBMS_OUTPUT.PUT_LINE('Found name = ' || my_name || ', age = ' || my_age);
END;
Using PL/SQL to read and process table data -- One Field
In Oracle PL/SQL, cursors are used to help read
and process data from tables.
This example uses a cursor to read one field from a table.
DECLARE
CURSOR sailors_cursor IS
SELECT DISTINCT sname
FROM reserves r, sailors s
WHERE (day - TO_DATE('04-MAY-06') > 0) AND
r.sid = s.sid;
current_sname sailors.sname%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sailors who have reserved boats after May 4, 2006:');
OPEN sailors_cursor;
LOOP
FETCH sailors_cursor INTO current_sname;
EXIT WHEN sailors_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(current_sname);
END LOOP;
CLOSE sailors_cursor;
END;
Cursor Attributes
In the above example, you saw that the %NOTFOUND cursor attribute
was used as the condition for the EXIT WHEN loop.
There are several additional cursor attributes that can be useful:
- %NOTFOUND -- was any data found or changed (TRUE or FALSE)
- %ROWCOUNT -- how many rows were found or affected (number)
- %FOUND -- was any row data found or affected (TRUE or FALSE)
- %ISOPEN -- is the cursor open (TRUE or FALSE)
Using PL/SQL to read and process table data -- Whole Rows
This example uses a cursor to read an entire row from a table.
DECLARE
CURSOR sailors_cursor IS
SELECT sname, day
FROM reserves r, sailors s
WHERE (day - TO_DATE('04-MAY-06') > 0) AND
r.sid = s.sid;
sailors_row sailors_cursor%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sailors who have reserved boats after May 4, 2006:');
DBMS_OUTPUT.PUT_LINE('Name' || ' ' || 'Reservation Date');
OPEN sailors_cursor;
LOOP
FETCH sailors_cursor INTO sailors_row;
EXIT WHEN sailors_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(sailors_row.sname || ' ' || sailors_row.day);
END LOOP;
CLOSE sailors_cursor;
END;
Cursors with Parameters
Cursors can also be passed parameters to make them more flexible.
DECLARE
CURSOR sailors_cursor (lowid in NUMBER, highid in NUMBER) IS
SELECT DISTINCT sname
FROM reserves r, sailors s
WHERE (lowid < s.sid) AND (highid < s.sid) AND
r.sid = s.sid;
current_sname sailors.sname%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sailors with sids between 2 and 5 who have reserved boats:');
OPEN sailors_cursor(2, 5);
LOOP
FETCH sailors_cursor INTO current_sname;
EXIT WHEN sailors_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(current_sname);
END LOOP;
CLOSE sailors_cursor;
END;
References and Links
|