Transcript Cursors in Pl/SQL

Cursors in Pl/SQL
Database 1. Practice
Sample Database
• The schema of the sample database is the following:
Drinkers (name, occupation, birthday, salary)
Wines (name, type, country)
Bars (name, street_number, street, city, tel)
Likes (drinker, wine)
Frequents (drinker, wine)
Sells (bar, wine, price)
Cursors – what are they?
• To process an SQL statement, Oracle opens a work area called
private SQL area.
• The private SQL area stores information needed to execute
the SQL statement.
• An identifier called cursor lets you name a SQL statement,
access the information in its private SQL area, and, to some
extent, control its processing.
• For static SQL statements, there are two types of
cursors: implicit and explicit. Oracle implicitly declares a
cursor for all data definition and data manipulation
statements, including SELECT statements (queries) that return
only one row.
• However, for queries that return more than one row, to
process beyond the first row, you must explicitly declare a
cursor.
How to manage cursors?
• In the first step cursors should be declared, i.e.,
– the associated SQL query should be given
– optionally parameters can be introduced
– the return type of the cursor can also be optionally defined.
• Next, the cursor should be opened.
• The tuples of the associated SQL query should be fetched.
• Finally, the cursor should be closed.
Declaration of cursors
• Syntax:
CURSOR name [(parameter[, parameter]... )]
[RETURN row_type] IS sql_query;
• Syntax of a parameter:
name [IN] type [{:= | DEFAULT} expression]
• Note: in the sql_query a parameter can be written wherever
a constant value can be used. Virtually, the parameter is a
reference, nevertheless its value cannot be changed.
Examples for declaration I.
• CURSOR cur_drinkers(p_year INTEGER DEFAULT 1970) IS
SELECT name, salary
FROM Drinkers
WHERE TO_CHAR(birthday, 'YYYY') = p_year;
• CURSOR cur_red_wines RETURN Wines%ROWTYPE IS
SELECT *
FROM Wines
WHERE type = 'red';
Examples for declaration II.
• CURSOR cur_wines(p_price NUMBER)
RETURN cur_red_wines%ROWTYPE IS
SELECT w.*
FROM Wines w JOIN Sells ON name = wine
WHERE price <= p_price;
Opening cursors
• When a cursor is opened, the corresponding SQL query is
executed and the cursor is set to point to the first row of the
result.
• This result is often referred as active set.
• Syntax:
OPEN cursor_name [(parameter[, parameter]... )];
• It goes without saying that the types of the parameters used
in the open statement should match the types of the
parameters of the cursor.
Fetching rows
• Syntax:
FETCH cursor_name
INTO variable[, variable, ...];
• The statement read the values of the current row and moves
the cursor to the next tuple of the active set.
• If the cursor points to the last tuple, then the values of the
variables of the INTO clause do not change.
• To check whether the cursor has reached the last tuple cursor
attributes %FOUND and %NOTFOUND can be used.
Closing cursors
• Syntax:
CLOSE cursor_name;
• The statements dissolves the relationship between the cursor
and the active set.
SET SERVEROUTPUT ON
DECLARE
v_row Drinkers%ROWTYPE;
CURSOR c1 IS
SELECT *
FROM Drinkers
ORDER BY birthday;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_row;
IF c1%ROWCOUNT IN (3, 5) THEN
DBMS_OUTPUT.PUT_LINE(v_row.name);
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
Cursor attributes
• %FOUND: until the cursor is not opened its value is NULL. If a
new row was succesfully fetched its value is set to TRUE,
otherwise to FALSE.
• %NOTFOUND: it is the opposite of %FOUND.
• %ISOPEN: its value is TRUE, if the cursor has been already
opened.
• %ROWCOUNT: before the first fetch its value is 0. After each
successful fetch this value is increased by 1.
Attributes of implicit cursors
• After each INSERT, DELETE, UPDATE, SELECT INTO statement
the system builds up an implicit cursor.
• Implicit cursors have similar attributes to explicit ones
– %FOUND: if the statement to which the implicit cursor refers has
affected at least one row, its value is TRUE, otherwise FALSE
– %NOTFOUND: the opposite of %FOUND
– %ISOPEN: since the system always closes the implicit cursor
automatically, its value is invariably FALSE.
– %ROWCOUNT: in the case of INSERT, DELETE, UPDATE statements it
returns the number of the affected tuples. For SELECT INTO
statements its value can only be 1, since for all other cases the system
throws an exception.
Example
SET SERVEROUTPUT ON
DECLARE
i
NUMBER;
BEGIN
SELECT COUNT(*) INTO i FROM Dual;
DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' ||
SQL%ROWCOUNT);
DELETE FROM Wines WHERE type = 'red';
DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' ||
SQL%ROWCOUNT);
END;
• Note: the implicit cursor always refers to the last executed
DML or SELECT INTO statement.
CURSOR FOR loop
• CURSOR FOR loops offer an alternative syntax, which greatly
leverages the use of cursors.
• Syntax:
FOR loop_variable IN cursor_name [(parameters)] |
(sql_query)
LOOP statement [statement]... END LOOP;
• The type of the loop_variable is automatically set to the type
of the cursor.
• Additionaly, the system automatically
– opens the cursor
– in each iteration fetches the next tuple
– at the end of the loop closes the cursor.
Example for CURSOR FOR LOOP I.
SET SERVEROUTPUT ON
DECLARE
CURSOR c1 IS
SELECT *
FROM Drinkers
ORDER BY birthday;
BEGIN
LOOP v_row in c1
IF c1%ROWCOUNT IN (3, 5) THEN
DBMS_OUTPUT.PUT_LINE(v_row.name);
END IF;
END LOOP;
END;
Example for CURSOR FOR LOOP II.
SET SERVEROUTPUT ON
DECLARE
i
NUMBER:=0;
BEGIN
LOOP v_row in (SELECT *
FROM Drinkers
ORDER BY birthday)
i := i+1;
IF i IN (3, 5) THEN
DBMS_OUTPUT.PUT_LINE(v_row.name);
END IF;
END LOOP;
END;
Exercises
1.
2.
3.
4.
Write a PL/SQL program which displays the name of those
drinkers, who like at least two white wines.
Display the price of the second and fourth most expensive
wines in Joe’s bar.
Create a copy of the Sells table. In this table increase the
price of the Brazilian and Argentinian wines by 2 and 1.
Create a Short_names(abbr, name) table. Insert tuples into
this table s.t. the first field contains an abbreviation of the
name of a drinker which is also given as the value of the
second field. This abbreviation should be constructed from
the original name by keeping only the first four letters. If the
length of a name is less than 5, then the original name
should be kept as an abbreviation.