|
“A successful
individual typically sets his next goal somewhat but not too
much above his last achievement. In this way he steadily raises
his level of aspiration.” Kurt Lewin (1890 - 1947) |
Read
first then play the video:
PLS006(VIDEO)-Cursor
Parameter
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 the iself directory as “test_for_loop3.”
Go to “MS-DOS.” Change
directory to the iself directory. And login to “SQLPLUS” as “iself/schooling.”
Open the notepad editor. Open the “test_for_loop2” file from the
iself 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 the iself
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!
|
“Eighty percent of
success is showing up.” Woody Allen |
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.
|
