|
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.
Manuscript 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; 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 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! |
|