Explicit Cursors with Variables [message #3051] |
Mon, 02 September 2002 12:57 |
Tom
Messages: 67 Registered: June 1998
|
Member |
|
|
Hi I am currently studying up on explicit cursors.
THis is the scenario. You are the DBA of a small Database. You want to automate your activities with PL/SQL.
You want to find the info ob all the employees in a certain department by department name. The Department field is in the emp table and the dept table, the dept table has the department name... So I am assuming they want us to pull the info from the dept table to get the deptno field then pull all the employees from the emp table. However we only want to see thier name and thier salary.. Here is the PL/SQL block
ACCEPT p_dname PROMPT "Enter the Department Name: "
DECLARE
r_emp emp%ROWTYPE;
CURSOR c_dept (p_dname VARCHAR2) is
SELECT *
FROM emp
WHERE deptno =
(SELECT deptno
FROM dept
WHERE dname = UPPER('&p_dname'));
BEGIN
OPEN c_dept(r_emp.deptno); -- THIS IS MY TROUBLE --
LOOP
FETCH c_dept INTO r_emp;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.put_line('EMPLOYEE ' || r_emp.ename || ' has a
salary of ' || r_emp.sal);
END LOOP;
CLOSE c_dept;
END;
/
This line is where I am having Trouble.... I do not understand what its doing.. I know how cursors with variables work.. But why is this pulling from the emp.deptno department... Well I wrote this code another wayand it works too.. But im having trouble understanding how the above part pulls it in.. I mean the above PL/SQL Block works.. I just dont know what this line is doing OPEN c_dept(r_emp.deptno);
I know it is opening the cursor... and usually the variable is enclosed. but the variable is a deptno... soo this too me makes absolutely no sense.... someone please help
|
|
|
Re: Explicit Cursors with Variables [message #3054 is a reply to message #3051] |
Mon, 02 September 2002 13:14 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
I realize you are studying explicit cursors, but CURSOR FOR loops are faster and easier to code.
In your code, you are trying to open a cursor by passing in a variable that is not initialized. Not only that, but your cursor is expecting a department name, not a dept_no.
The approach here would be to pass the department name into the cursor.
declare
cursor csr_emp(c_dname in dept.dname%type) is
select *
from emp
where deptno = (SELECT deptno
FROM dept
WHERE dname = UPPER(c_dname));
r_emp emp%rowtype;
begin
open csr_emp('&p_dname');
loop
fetch csr_emp into r_emp;
exit when csr_emp%notfound;
dbms_output.put_line('EMPLOYEE ' || r_emp.ename || ' has a salary of ' || r_emp.sal);
end loop;
close csr_emp;
end;
With a CURSOR FOR loop, this is just:
declare
cursor csr_emp(c_dname in dept.dname%type) is
select *
from emp
where deptno = (SELECT deptno
FROM dept
WHERE dname = UPPER(c_dname));
begin
for r in csr_emp('&p_dname') loop
dbms_output.put_line('EMPLOYEE ' || r_emp.ename || ' has a salary of ' || r_emp.sal);
end loop;
end;
|
|
|