Examine the following snippet of PL/SQL code:
View the exhibit for table description of EMPLOYEES table. The EMPLOYEES table has 200 rows.
Identify open statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as ‘ST_CLERK’ and salary greater than 3000.
A. OPEN c1 (NULL, 3000);
B. OPEN c1 (emp_job, 3000);
C. OPEN c1 (3000, emp_salary);
D. OPEN c1 (‘ST_CLERK’, 3000)
E. OPEN c1 (EMP_job, emp_salary);
b d e
I think correct answer is D.
Tested with below code:
set serveroutput on;
declare
emp_job employees.job_id%type:=’ST_CLERK’;
emp_salary employees.salary%type:=3000;
my_record employees%rowtype;
cursor c1 (job varchar2, max_wage number) is
select * from employees
where job_id=job and
salary>max_wage;
begin
OPEN c1(‘ST_CLERK’,3000); — open the cursor before fetching
LOOP
FETCH c1 INTO my_record; — fetches 2 columns into variables
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Job ‘||my_record.job_id||’ has salary ‘||my_record.salary);
END LOOP;
CLOSE c1;
end;
/
Job ST_CLERK has salary 3200
Job ST_CLERK has salary 3300
Job ST_CLERK has salary 3300
Job ST_CLERK has salary 3600
Job ST_CLERK has salary 3200
Job ST_CLERK has salary 3500
Job ST_CLERK has salary 3100
PL/SQL procedure successfully completed.
Tested all of B, D, E and they are all correct.