|
Cursor Parameter
Hand-On
Introduction
In this
Hands-On, you use the �test_for_loop2� file
from the previous Hands-On. Then modify the
PL/SQL block that the cursor only calculates
the department statistics for a specific
department. For example the department
number 10. Save the file in your directory
as �test_for_loop3.�
Go to
�MS-DOS.� Change directory to your
directory. And login to �SQLPLUS� as
�oracle/learning� Open the notepad editor.
Open the �test_for_loop2� file from your
directory
In the
declaration section, modify the cursor to
use a cursor parameter to pass the
department number as an input parameter. Add
a WHERE clause condition to query only the
input parameter. Declare the department
number variable.
Defining
parameter in a PL/SQL cursor
In the
execution section, assign number 10 to the
department number variable. Modify the FOR
LOOP statement to pass the department number
as an input parameter.
(Notepad)
>> DECLARE
-- define
department statistics
cursor c_ds (p_deptno
dept.deptno%TYPE) is
select dname,
count (*) ttemp,
sum(sal) ttsal, avg(sal)
avsal
from dept d,
emp e
where d.deptno
= e.deptno
and d.deptno =
p_deptno
group by dname;
--
define deptno variable
v_deptno NUMBER(2);
BEGIN
-- assign
deptno 10
v_deptno :=
10;
-- loop to
read cursor record.
for v_ds in
c_ds (v_deptno) loop
-- insert into
dept_stat
insert into
dept_stat
values (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
end loop;
-- save the
insert transaction.
COMMIT;
END;
/
Save a PL/SQL
block
Save the file
in your directory as �test_for_loop3�.
Run a PL/SQL
block
Go to �sqlplus.�
Query the department statistics table. Then
truncate the dept_stat table in the case
there are records in the table.
SQL> truncate table dept_stat;
Check the
output
Query the
dept_stat table again.
SQL> select * from dept_stat;
No data!
Run the file.
SQL> @test_for_loop3
Query the
dept_stat table.
SQL> select * from dept_stat;
Now, you see
the result here!
Questions:
Q: What is the
cursor parameter in the PL/SQL language?
Q: Where do you define a cursor parameter in
the PL/SQL language?
Q: Write a
PL/SQL block to populate the department
table statistics into the �dept_stat� table
for a specific department.
Statistics
Information:
The
Department Number,
The
total number of employees in each
department,
The
total salary paid in each
department, and
The
average salary paid in each
department.
|
