Example of cursors

declare
num number:='&num';
sal employees.salary%type;
cursor emp_cursor is select distinct salary from employees order by salary desc;
begin
open emp_cursor;
fetch emp_cursor into sal;
while emp_cursor%rowcount <= num and emp_cursor%found loop
insert into top_salaries
values(sal);
fetch emp_cursor into sal;
end loop;
close emp_cursor;
end;


declare
p_deptno number:='&deptno';
cursor emp_cursor is select last_name,salary,manager_id from employees
where department_id=p_deptno;
begin
for emp_rec in emp_cursor loop
if emp_rec.salary <5000 and (emp_rec.manager_id=101 or emp_rec.manager_id=124) then
dbms_output.put_line(emp_rec.last_name||'   '||'due for a raise');
else
dbms_output.put_line(emp_rec.last_name||'   '||'not due for a raise');
end if ;
end loop;
end;


declare
cursor dept_cursor is select department_id,department_name from departments
where department_id<100 order by 1;
cursor emp_cursor(deptno number) is
select last_name,job_id,hire_date,salary from employees
where employee_id<120 and department_id=deptno;
v_did departments.department_id%type;
v_dname departments.department_name%type;
v_lname employees.last_name%type;
v_job employees.job_id%type;
v_date employees.hire_date%type;
v_sal employees.salary%type;
begin
open dept_cursor;
loop
fetch dept_cursor into v_did,v_dname;
exit when dept_cursor%notfound;
dbms_output.put_line('department id is  :  '||v_did||'    '||
'department name is   :  '||v_dname);
end loop;
close dept_cursor;
open emp_cursor(30);
loop
fetch emp_cursor into v_lname,v_job,v_date,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line('last_name  :  '||v_lname||'   '||'job id :  '||v_job
||'   '||'hire date  :  '||v_date||'   '||'salary  :  '||'   '||v_sal);
end loop;
close emp_cursor;
exception
when no_data_found then
dbms_output.put_line('sorry');
end;

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন