CODEWITHSUNDEEP
oracle-databaseplsql
user4170661
user4170661

Reputation:

Learning PL/SQL cursors

I have to write a pl/sql block that will give me a list of names depending what job_id I enter. I am stuck and can not really understand what is my mistake.

 SET SERVEROUTPUT ON
 DECLARE 

 jobid EMPLOYEES.JOB_ID%type;

 CURSOR mycrs IS
 SELECT LAST_NAME
 FROM EMPLOYEES
 WHERE JOB_ID = jobid;

 names EMPLOYEES.LAST_NAME%TYPE;

 BEGIN

 jobid := &JOB_ID;

 OPEN mycrs;
 FETCH mycrs INTO names;
 DBMS_OUTPUT.PUT_LINE(names);
 CLOSE mycrs;


 END;

Upvotes: 0

Views: 197

Answers (1)

Boneist
Boneist

Reputation: 23588

I expect the issue you're experiencing is that you're only retrieving a single name for a given job id, whereas you're expecting more to be listed.

If so, that's because when you open a cursor and do a fetch as per your example procedure, you're only fetching a single row. In order to retrieve all the rows returned by the cursor, you have to loop through all the rows in the cursor.

Probably the easiest way of doing this in PL/SQL is via a cursor-for-loop, where Oracle handles the opening, fetching and closing of the cursor for you. It also implicitly declares the variable the results are stored into, so you don't even have to worry about that either.

For example:

set serveroutput on

declare 
  jobid employees.job_id%type;

  cursor mycrs is
  select last_name
  from employees
  where job_id = jobid;
begin
  jobid := &job_id; -- assuming employees.job_id has a NUMBER datatype

  for rec in mycrs
  loop
    dbms_output.put_line(rec.last_name);
  end loop;

end;
/

N.B. be aware that in general, if you're looping through a cursor and doing row-by-row DML operations, you should rethink your approach ("slow-by-slow") and turn it into a single DML statement (set based) - it's faster to let Oracle handle the work for all rows at once, rather than forcing it to do things one row at a time.

Upvotes: 1

Related Questions