|
Explicit Cursor Handling
Hands-On
Introduction
In this
Hands-On, you will declare a cursor to list
the department name (dname), total number of
employees (ttemp), total salary (ttsal), and
average salary (avsal) for each department
from the department table and employee
table.
Then, you
print all department name with their total
number of employees (For example: ACCOUNTING
has 3 employees) for each department. You
use the notepad editor.
Go to
�MS-DOS.� Change directory to your
directory. And login to �sqlplus� as
"oracle/learning."
Declare
Variables (Naming convention)
Open the
notepad editor, write a PL/SQL block to
print all the department names with their
total number of employees. Declare a record
type to have four items: Department name,
total number of employees, total salary, and
average salary. Follow the naming convention
to start a type name with "t_", a variable
name with "v_", a cursor name with "c_", and
a parameter name with "p_".
Declare
Explicit Cursor
The explicit
cursor will be defined in the declaration
section. Once you define an explicit cursor,
you should open it the PL/SQL body, fetch
the cursor one at a time, exit from the loop
if you have one, and at the end close the
cursor.
Declare a
cursor to list the department name, total
number of employees, total salary, and
average salary from the department and
employee table order by the department name.
OPEN, FETCH,
LOOP, and EXIT a Cursor (Simple loop)
In the body or
execution section, open the cursor. Make a
simple loop. In the loop, read a record one
at a time using fetch statement. Make sure
to exit from the loop. Use the "dbms_output"
package to print the department name and
their total number of employees. End the
loop and then close the cursor. Make it easy
to read.
(Notepad)
>> DECLARE
-- Declare a
variable for a cursor.
TYPE t_ds IS RECORD (
dname dept.dname%type,
ttemp number(3),
ttsal number(8,2),
avsal number(8,2));
-- define
department statistics
CURSOR c_ds is
select dname,
count (*) ttemp,
sum(sal) ttsal, avg(sal)
avsal
from dept d,
emp e
where d.deptno
= e.deptno
group by dname
order by 1;
--
define a variable for cursor
v_ds t_ds;
BEGIN
--
open the cursor
OPEN c_ds;
--
start loop
LOOP
--read
a record
FETCH c_ds INTO v_ds;
-- exit from
loop
EXIT WHEN
c_ds%notfound;
-- list dept.
name
dbms_output.put_line
(v_ds.dname ||
� has � ||
v_ds.ttemp || �
employees.�);
-- end the
loop
END LOOP;
CLOSE c_ds;
END;
/
Save a PL/SQL
block
Save the file
in your directory as "test_fetch_cursor.sql."
Go to �SQLPLUS.�
Get the file. Compile and run the PL/SQL
block.
SQL> get c:
test_fetch_cursor.sql
SQL> /
You should not
have any error messages in the compilation.
If you have try to correct your errors
before to get to next step.
Run a PL/SQL
block
Set the
serveroutput to on. Then run the file.
SQL> set
serveroutput on
SQL> @test_fetch_cursor
Questions:
Q: Describe
that why do we need to use a solid naming
convention in our PL/SQL program.
Q: What is the
explicit cursor in the PL/SQL language?
Q: What are
the differences between the explicit and
implicit cursors?
Q: Where do
you declare an explicit cursor in the PL/SQL
language?
Q: Where do
you declare an implicit cursor in the PL/SQL
language?
Q: What is a
simple loop in the PL/SQL language?
Q: How do you
open an explicit cursor in the PL/SQL
language?
Q: What does
the FETCH statement in the Oracle PL/SQL
language?
Q: How do you
terminate from a simple loop in the PL/SQL
language?
Q: How do you
OPEN or CLOSE a cursor in the PL/SQL
language?
Q: Declare a
cursor to list the department name (dname),
total number of employees (ttemp), total
salary (ttsal), and average salary (avsal)
for each department from the department
table and employee table order by the
department name.
Write all
department name with their total number of
employees for each department using the
notepad editor.
For example:
ACCOUNTING has 3 employees.
(Note: Don�t
use the ttemp, ttsal, and avsal item at this
time) |
