Home » SQL & PL/SQL » SQL & PL/SQL » Explicit Cursors with Variables
Explicit Cursors with Variables [message #3051] Mon, 02 September 2002 12:57 Go to next message
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 Go to previous message
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;
Previous Topic: Re: Database trigger
Next Topic: the name of the aplication
Goto Forum:
  


Current Time: Thu Mar 28 18:33:14 CDT 2024