Forum Stats

  • 3,759,060 Users
  • 2,251,495 Discussions
  • 7,870,477 Comments

Discussions

In a loop, use a cursor to retrieve the deptNumber and the deptName from the “Department” table, pas

Albert Chao
Albert Chao Member Posts: 63 Green Ribbon
edited Sep 2, 2021 6:51AM in SQL & PL/SQL

set serveroutput on;

declare

deptno hr.departments.department_id%type;

deptname hr.departments.department_name%type;

cursor dept(deptid number) is 

select department_id, department_name from hr.departments where department_id = &deptid;

begin

open dept;

loop

fetch dept into deptno, deptname;

exit when dept%notfound;

dbms_output.put_line('Department no : '||deptno||' Department name : '||deptname);

end loop;

close dept;

end;

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    Accepted Answer

    Here's one way to do this. Code first, and then explanation. This solution uses what is called an implicit cursor for loop.

    begin
     for r in (select department_id, department_name from hr.departments) loop
       dbms_output.put_line('Department no : '|| r.department_id ||
                            ' Department name : '|| r.department_name);
     end loop;
    end;
    /
    

    Neat, eh?

    A construct of the form

    for r in (select ...)loop....endloop;

    is known as an implicit cursor for loop. The cursor, r, has the same signature (column names and data types) as the select statement on which it is based. The server opens the cursor automatically, it fetches it in the for loop, and it closes it when finished - no need for you to write all that code.

    Note that with this approach I don't need to define local variables. If you wanted to use "variables" by the names deptno and deptname (as you have in your code) to reference in other places in additional code, all you need to do is to add column aliases in the select statement, like this:

    begin
     for r in (select department_id as deptno, department_name as deptname from hr.departments)
     loop
       dbms_output.put_line('Department no : '|| r.deptno || ' Department name : '|| r.deptname);
     end loop;
    end;
    /
    


    Of course, this doesn't change the fact that deptno and deptname aren't really "local variables" - they are still the components of r, but they are given the names you prefer. You can only get the values from them, you can't set their values - and they don't survive past the end of the for loop. Which should be enough for your task, which was to "retrieve" the values from the base table.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Your "dept" cursor has a parameter to accept deptid, but you don't pass it one when you open the cursor.

    Instead you are basing your query on a substitution variable (see the community document: PL/SQL 101 : Substitution vs. Bind Variables — oracle-tech)

    That's the obvious issue I can see.

    In future, please ensure you explain what the problem is in the body of your post... and keep the subject line concise and descriptive.

    Formatting your code also helps... when putting text in the editor you'll see a paragraph symbol to the left. Highlight your code, click that paragraph symbol, click the double quotes and choose "code block".

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    Accepted Answer

    Here's one way to do this. Code first, and then explanation. This solution uses what is called an implicit cursor for loop.

    begin
     for r in (select department_id, department_name from hr.departments) loop
       dbms_output.put_line('Department no : '|| r.department_id ||
                            ' Department name : '|| r.department_name);
     end loop;
    end;
    /
    

    Neat, eh?

    A construct of the form

    for r in (select ...)loop....endloop;

    is known as an implicit cursor for loop. The cursor, r, has the same signature (column names and data types) as the select statement on which it is based. The server opens the cursor automatically, it fetches it in the for loop, and it closes it when finished - no need for you to write all that code.

    Note that with this approach I don't need to define local variables. If you wanted to use "variables" by the names deptno and deptname (as you have in your code) to reference in other places in additional code, all you need to do is to add column aliases in the select statement, like this:

    begin
     for r in (select department_id as deptno, department_name as deptname from hr.departments)
     loop
       dbms_output.put_line('Department no : '|| r.deptno || ' Department name : '|| r.deptname);
     end loop;
    end;
    /
    


    Of course, this doesn't change the fact that deptno and deptname aren't really "local variables" - they are still the components of r, but they are given the names you prefer. You can only get the values from them, you can't set their values - and they don't survive past the end of the for loop. Which should be enough for your task, which was to "retrieve" the values from the base table.

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Sep 2, 2021 2:46PM

    Or... am I misreading the question, since your title was truncated? What does the chopped-off word "pas" mean at the end?

    Do you need to pass in the department id as a parameter? Then why would you need to "retrieve" it - since it's given to you (and to the PL/SQL code) from the very beginning? And, in any case, since the department id is primary key in the table, why would you do anything "in a loop"? Given an input id, there should be at most one matching row in the table - so there's no need for loops of any kind.

    Please clarify.

    (In any case - you can add a where clause to the implicit cursor definition; but if in fact the select statement returns at most one row, you shouldn't need a loop - and you shouldn't need a cursor - to begin with.)

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    @mathguy the (truncated) wording of the subject line sounds a little like a homework/assignment question to me. So I'm guessing that the lesson is about using explicit cursor loops and passing values to the cursor.... but that's just a guess. ;)

  • Albert Chao
    Albert Chao Member Posts: 63 Green Ribbon

    @BluShadow @mathguy You guys are rocking. Your guesses are very true. Thanks for the help.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    So, if that's correct and you've got to demonstrate cursors with parameters (and perhaps use a substitution variable to prompt for the required department id initially) then something like this...

    set serveroutput on;
    declare
      cursor dept(deptid number) is 
        select department_id
              ,department_name
        from   hr.departments
        where  department_id = dept.deptid; -- explicit reference to cursor parameter
      result dept%rowtype; -- result of rowtype from the cursor
    begin
      open dept(&deptid); -- substitution variable will prompt for required
                          -- department id and substitute it in the code
                          -- before the code is sent to the database for parsing/compilation
      loop
        fetch dept into result;
        exit when dept%notfound;
        dbms_output.put_line('Department no : '||result.deptno||' Department name : '||result.deptname);
      end loop;
      close dept;
    end;
    
    
    

    Note: I don't have your tables to test it's all syntactically correct, but that's the gist of it.

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    @BluShadow

    Note: I don't have your tables [...]


    You don't have the DEPARTMENTS table in the HR schema? What do you test on?

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    No I don't mathguy. I have a blank database that I have a copy of the old scott schema on, that's all. ;)