|
More Resources by
Google: |
|
|
|
|
Topics: Hands-On 04 - Explicit Cursor Handling
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 using the
notepad editor. (Note: We don’t use the ttemp, ttsal, and avsal item in
this Hands-On)
The Core Subjects are:
1- Implicit vs. explicit cursors
2- Declaring explicit cursors
3- Using explicit cursor.
a- Open Cursor
b- Fetch Cursor
c- Close Cursor
d- Declare variable
3- Using simple loop
a- Exit statement.
Go to “MS-DOS.” Change directory to the iself directory. And login to
“sqlplus” as "iself/schooling."
=
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 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.
Declare a variable for a cursor.
>> declare
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;
-- 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 loop;
close c_ds;
end;
/
=
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.
=
Save the file in the "iself" directory as "test_fetch_cursor.sql."
=
Go to “SQLPLUS.”
Get the file.
Compile and run the PL/SQL block.
No error messages in the compilation.
Set the serveroutput to on.
Then run the file.
>> set serveroutput on
>> @test_fetch_cursor
Now you should practice this over and over, until you become a master at it.
Good Luck!
|