|
“Don't walk behind
me, I may not lead. Don't walk in front of me, I may not follow.
Just walk beside me and be my friend.” Albert Camus (1913 -
1960) (attributed) |
Read
first then play the video:
PLS003(VIDEO)-Explicit
Cursor Handling
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 the iself directory. And login to “sqlplus” as "iself/schooling."
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 the "iself"
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
|
“The first step to
getting the things you want out of life is this: Decide what you
want.” Ben Stein |
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)
|