explicit cursors
select * from employees
where department_id=30;
----------------------
--create cursor to print employee_id, first_name for department_id =30
--method 1
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name FROM employees
where department_id=30;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
BEGIN
OPEN c_emp_dept30;
loop
fetch c_emp_dept30 into v_empno, v_first_name;
exit when c_emp_dept30%notfound;
dbms_output.put_line(v_empno||' '||v_first_name); -- the exit should before output
end loop;
close c_emp_dept30;
END;
----------------------------------------------------
--method 2
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name FROM employees
where department_id=30;
v_emp_rec employees%rowtype;
BEGIN
OPEN c_emp_dept30;
loop
fetch c_emp_dept30 into v_emp_rec.employee_id,v_emp_rec.first_name ;
exit when c_emp_dept30%notfound;
dbms_output.put_line(v_emp_rec.employee_id||' '||v_emp_rec.first_name);
end loop;
close c_emp_dept30;
END;
----------------------------------------------------
--method 3
--pl/sql records based on cursor, this is the 3rd method for creating pl/sql records
-- 1 is programmer, 2 is %rowtype, 3 is cursor records
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name FROM employees
where department_id=30;
v_emp_rec c_emp_dept30%rowtype;
BEGIN
OPEN c_emp_dept30;
loop
fetch c_emp_dept30 into v_emp_rec;
exit when c_emp_dept30%notfound;
dbms_output.put_line(v_emp_rec.employee_id||' '||v_emp_rec.first_name);
end loop;
close c_emp_dept30;
END;
------------------------------------------------------
--update the salaries for employees in dept30 using cursor ( +100 for each one )
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name,salary FROM employees
where department_id=30;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
v_salary employees.salary%type;
BEGIN
OPEN c_emp_dept30;
loop
fetch c_emp_dept30 into v_empno, v_first_name,v_salary;
exit when c_emp_dept30%notfound;
update employees
set salary=salary+100
where employee_id=v_empno;
end loop;
close c_emp_dept30;
commit;
END;
---------
select * from employees
where department_id=30;
Explicit Cursor Attributes
--using c_emp%notfound, c_emp%isopen,c_emp%rowcount
DECLARE
CURSOR c_emp is
SELECT employee_id,first_name FROM employees;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
BEGIN
if c_emp%isopen then
null;
else
open c_emp;
end if;
dbms_output.put_line('the counter for cursor now is '||c_emp%rowcount);
loop
fetch c_emp into v_empno, v_first_name;
exit when c_emp%notfound or c_emp%rowcount>10 ;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
dbms_output.put_line('the counter for cursor now is '||c_emp%rowcount);
close c_emp;
END;
---------------------------
FOR LOOP Cursor
--basic loop cursor
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name FROM employees
where department_id=30;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
BEGIN
OPEN c_emp_dept30;
loop
fetch c_emp_dept30 into v_empno, v_first_name;
exit when c_emp_dept30%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close c_emp_dept30;
END;
--------------------------
--we will use same example above but using for loop cursor
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name FROM employees
where department_id=30;
BEGIN
for i in c_emp_dept30
loop
dbms_output.put_line(i.employee_id||' '||i.first_name);
end loop;
END;
-----------------------
--another way is use the select inside the for
DECLARE
BEGIN
for i in ( SELECT employee_id, first_name FROM employees
where department_id=30)
loop
dbms_output.put_line(i.employee_id||' '||i.first_name);
end loop;
END;
Cursor with Parameters
DECLARE
CURSOR c_emp_dept(v_dept number) --here we defined the parameter without size
is
SELECT employee_id, first_name FROM employees
where department_id=v_dept;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;
BEGIN
OPEN c_emp_dept(10);
dbms_output.put_line('dept 10 contains:');
loop
fetch c_emp_dept into v_empno, v_first_name;
exit when c_emp_dept%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close c_emp_dept;
OPEN c_emp_dept(20);
dbms_output.put_line('dept 20 contains:');
loop
fetch c_emp_dept into v_empno, v_first_name;
exit when c_emp_dept%notfound;
dbms_output.put_line(v_empno||' '||v_first_name);
end loop;
close c_emp_dept;
END;
-------------------------------------------------------
-- we will do the same exmple using the for loop cursor
DECLARE
CURSOR c_emp_dept(v_dept number) --here we defined the parameter without size
is
SELECT employee_id id , first_name FROM employees
where department_id=v_dept;
BEGIN
dbms_output.put_line('dept 10 contains:');
for i in c_emp_dept (10)
loop
dbms_output.put_line(i.id||' '||i.first_name);
end loop;
dbms_output.put_line('dept 20 contains:');
for j in c_emp_dept (20)
loop
dbms_output.put_line(j.id||' '||j.first_name);
end loop;
END;
FOR UPDATE Clause & CURRENT OF Clause
--this a normal select
select * from employees
where employee_id in (100,200)
order by 1;
/*when you put for update, then no one can do any DML(I,U,D)
for theses records, untill you finish your trans ( commit, update )
after you execute the next statement try to open another session
and do like
update
employees
set salary = salary+100;
*/
select * from employees
where employee_id in (100,200)
order by 1
for update;
commit;
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name,salary FROM employees
where department_id=30
for update;
BEGIN
for i in c_emp_dept30
loop
update employees
set salary=salary+1
where employee_id=i.employee_id;
end loop;
commit;
END;
---
select * from employees
where department_id=30;
-----------
---current of ------
DECLARE
CURSOR c_emp_dept30 is
SELECT employee_id, first_name,salary FROM employees
where department_id=30
for update;
BEGIN
for i in c_emp_dept30
loop
update employees
set salary=salary-1
where current of c_emp_dept30;
end loop;
commit;
END;
---
select * from employees
where department_id=30;
No comments:
Post a Comment