Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Arya.

Asked: December 02, 2016 - 5:32 pm UTC

Last updated: December 05, 2016 - 11:08 am UTC

Version: 11

Viewed 1000+ times

You Asked

i have to write a procedure where the procedure takes a parameter say employee_status(Retired/Not-Retired)
based on the parameter i've to use a cursor select employees from mytable who are retired or not retired and loop over all the employees (Retired/Not-Retired).
So what is the best way to implement.

below is the rough picture how i'm going to implement ,so please tell me is it the correct way to implement are not

Procedure process_data(employee_status)
IS
cursor c_REC_EMP;
BEGIN
IF employee_status='RETIRED' THNE
c_REC_EMP is select employess who are retired;
ELSE
c_REC_EMP is select employess who are not retired;
END IF;

FOR REC_PROCESS IN c_REC_EMP
LOOP
do_some_stuff;
END LOOP;

END;

and Chris said...

Just put the flag directly in your SQL query:

create table t (
  id int, 
  status varchar2(10)
);

insert into t values (1, 'RETIRED');
insert into t values (2, 'EMPLOYED');

declare
  stts t.status%type := 'RETIRED';
begin
  for emps in (
    select * from t
    where  status = stts 
  ) loop
    dbms_output.put_line(emps.id);
  end loop;
end;
/

PL/SQL procedure successfully completed.
1

declare
  stts t.status%type := 'EMPLOYED';
begin
  for emps in (
    select * from t
    where  status = stts 
  ) loop
    dbms_output.put_line(emps.id);
  end loop;
end;
/

PL/SQL procedure successfully completed.
2


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library