csci235 spring 2010 tutorials lab 7 with answers!!!
DESCRIPTION
Databases. PL SQL. Implicit and Explicit Cursor.TRANSCRIPT
University of Wollongong in Dubai CSCI 235 – Databases
Tutorial 7 – PL/SQL Programming [2]__________________________________________________________________________________
There are two types of Cursors.
Implicit Cursor: Oracle automatically declares an implicit cursor every time a SQL statement is
executed. The user is unaware of this and cannot control or process the information in an implicit
cursor. The implicit cursor is used to process INSERT, UPDATE, DELETE and SELECT statements.
During the processing of an implicit cursor, oracle automatically performs the OPEN, FETCH and
CLOSE operations.
Explicit Cursor: The program defines an explicit cursor for any query that returns more than one row
of data. This means that the programmer has declared the cursor within the PL/SQL code block. This
declaration allows the application to sequentially process each row of data as the cursor returns it.
The advantage of declaring an explicit cursor over and implicit cursor is that the explicit cursor gives
the programmer more programmatic control. Also, implicit cursors are less efficient than explicit
cursors, so it is harder to trap data errors.
The process of working with an explicit cursor consists of following steps:
1. Declaring the cursor. This initializes the cursor into memory.
2. Opening the cursor. The declared cursor is opened, and memory is allocated.
3. Fetching the cursor. The declared and opened cursor can now retrieve data.
4. Closing the cursor. The declared, opened , and fetched cursor must be closed to release the
memory allocation.
Example:
SET SERVER OUTPUT ON
DECLARE
Cursor c_zip IS SELECT * FROM zipcode;
v_zip c_zip%rowtype;
BEGIN
OPEN c_zip;
LOOP
FETCH c_zip into v_zip;
EXIT WHEN c_zip%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (vr_zip.zip || ‘ ‘ || vr_zip.city || ‘ ‘ || vr_zip.city);
END LOOP;
END;
Cursor Attributes
Cursor Attribute Syntax Descryption%NOTFOUND Cursor_name%NOTFOUND A Boolean that returns true if
the previous FETCH did not return a row and false if it did.
%FOUND Cursor_name%FOUND A Boolean that returns true if the previous FETCH returned a row and false if did not.
%ROWCOUNT Cursor_name%ROWCOUNT The number of records fetched from a cursor at that point in time.
%ISOPEN Cursor_name%ISOPEN A Boolean attribute that returns true if the cursor is opened and false if it is not.
Tasks
Assume we have a student table with columns id, firstname, last name, age, average and gender. Write PL/SQLprograms for following scenarios:
1. Write a program to get all female student information and then shows their firstname, lastname and age.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 Cursor c_stu IS SELECT * FROM student where sex='f';
3 v_stu c_stu%rowtype;
4 BEGIN
5 OPEN c_stu;
6 LOOP
7 FETCH c_stu into v_stu;
8 EXIT WHEN c_stu%NOTFOUND;
9 DBMS_OUTPUT.PUT_LINE (v_stu.first_name || ' ' || v_stu.last_name || ' ' || v_stu.age);
10 END LOOP;
11 CLOSE c_stu;
12 END;
13 /
tooba sheikh 20
hina majeed 19
aisha aleem 22
huma g 25
PL/SQL procedure successfully completed.
2. Write a program which shows the total number of students who are younger than 23.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 Cursor c_stu IS SELECT * FROM student where age<23;
3 v_stu c_stu%rowtype;
4 BEGIN
5 OPEN c_stu;
6 LOOP
7 FETCH c_stu into v_stu;
8 EXIT WHEN c_stu%NOTFOUND;
9 DBMS_OUTPUT.PUT_LINE (c_stu%rowcount);
10 DBMS_OUTPUT.PUT_LINE (v_stu.first_name || ' ' || v_stu.last_name);
11 END LOOP;
12 CLOSE c_stu;
13 END;
14 /
1
tooba sheikh
2
hina majeed
3
aisha aleem
PL/SQL procedure successfully completed.
3. Write a program to show firstname and lastname of students who their average is above 80.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 Cursor c_stu IS SELECT * FROM student where average>80;
3 v_stu c_stu%rowtype;
4 BEGIN
5 OPEN c_stu;
6 LOOP
7 FETCH c_stu into v_stu;
8 EXIT WHEN c_stu%NOTFOUND;
9 DBMS_OUTPUT.PUT_LINE (v_stu.first_name || ' ' || v_stu.last_name);
10 END LOOP;
11 CLOSE c_stu;
12 END;
13 /
tooba sheikh
hina majeed
ahmed k
rehan s
PL/SQL procedure successfully completed.
4. Write a program to find the average age of all students.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 Cursor c_stu IS SELECT * FROM student;
3 total number:=0;
4 v_stu c_stu%rowtype;
5 BEGIN
6 OPEN c_stu;
7 LOOP
8 FETCH c_stu into v_stu;
9 EXIT WHEN c_stu%NOTFOUND;
10 total:=total +v_stu.age ;
11 DBMS_OUTPUT.PUT_LINE (v_stu.first_name || ' ' || v_stu.last_name);
12 END LOOP;
13 DBMS_OUTPUT.PUT_LINE (total/c_stu%rowcount);
14 CLOSE c_stu;
15 END;
16 /
tooba sheikh
hina majeed
aisha aleem
huma g
ahmed k
rehan s
hamza b
24.42857142857142857142857142857142857143
PL/SQL procedure successfully completed.