شرح بالعربي كامل عن Cursors في ال pl

oracle data base
cursor for oracle free for all

Declaring the Cursor

ϝΎΜϣ
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM employees;
CURSOR dept_cursor IS
SELECT *
FROM departments
WHERE location_id = 170;
BEGIN
ϝϭΪΠϟ΍ Ϧϣ last_name ΩϮϤόϟ΍ ϭ employee_id ΩϮϤόϟ΍ ΕΎϧΎϴΑ ωΎΟήΘγϹ ϪϔϳήόΗ Ϣϓ emp_cursor ήηΆϤϟ΍ ˬ ϖΑΎδϟ΍ ϝΎΜϤϟ΍
ϒχϮϤϟΎΑ ΔλΎΨϟ΍ departmens ϝϭΪΠϟ΍ ϲϓ ϲΘϟ΍ ΕΎϧΎϴΒϟ΍ Ϟϛ ωΎΟήΘγϹ ϪϔϳήόΗ ϢΗ dept_cursor ήηΆϤϟ΍ Ϛϟάϛ employees
Ϣϗέ
ήΧ΁ ϝΎΜϣ
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM employees;
BEGIN

Opening the Cursor
ϢΘϳϭ ˬ ϪΠΗΎϨϟ΍ ϑϮϔμϟ΍ ΔϋϮϤΠϣ ΪϳΪΤΗ ϢΘϳ ΔϠΣήϤϟ΍ ϩάϫ ϲϓ ˬ cursor ήηΆϤϟ΍ ϊϣ ϖϓήϤϟ΍ ϡϼόΘγϻ΍ άϴϔϨΘϟ OPEN ΓέΎΒόϟ΍ ϡΪΨΘδϧ
ϝϭϻ΍ ϒμϟ΍ ϞΒϗ ήηΆϤϟ΍ ϊοϭ
ϲϫ ήηΆϤϟ΍ ΢Θϔϟ ΔϣΎόϟ΍ Δϐϴμϟ΍
OPEN cursor_name ;
ϥ΍ ΚϴΣ
Ε΍ήϴϐΘϤϟ΍ ϒϳήόΗ ΔϘτϨϣ ϲϓ ϪϔϳήόΗ ϢΗ ϱάϟ΍ ήηΆϤϟ΍ Ϣγ· Ϯϫ cursor_name
ΔϴϟΎΘϟ΍ ΕΎϴϠϤόϟΎΑ ϡϮϘΗ ΔϳάϴϔϨΗ ΓέΎΒϋ ϲϫ OPEN ΓέΎΒόϟ΍
ΎϫϭΪΣ ϊϗϮΘϳ ϲΘϟ΍ ΕΎϧΎϴΒϟ΍ ΕΎΠϟΎόϤϟ Γήϛ΍άϟ΍ ϲϓ ΔΣΎδϣ ΪϳΪΤΘΑ ϡϮϘΗ ˱ΎΗήηΎΒϣ
ΎϫάϴϔϨΗ Ω΍ήϤϟ΍ SELECT ˰ϟ΍ ΔϠϤΟ ΪϳΪΤΗ
ΎϫΪϳΪΤΗ ϢΗ ϲΘϟ΍ Γήϛ΍άϟ΍ ϲϓ ΎϬόοϭϭ ˬϡϼόΘγϻ΍ ϲϓ ϲΘϟ΍ ΔϠΧΪϤϟ΍ Ε΍ήϴϐΘϤϟ΍ ΪϳΪΤΗ
ϡϼόΘγϻ΍ ϲϓ ΚΤΒϟ΍ ρϭήη ϲϓϮΘδΗ ϲΘϟ΍ ϑϮϔμϟ΍ ΔϋϮϤΠϣ ϲϫϭ ˬΔτθϨϟ΍ ΔϋϮϤΠϤϟ΍ ΪϳΪΤΗϭ active set ˯Ύθϧ·
ϚϟΫ Ϧϋ ˱ϻΪΑ ˬ OPEN ΓέΎΒόϟ΍ άϴϔϨΗ ΔσΎγϮΑ ΎϬΟ΍ήΨΘγ· ϭ΃ ΎϬϋΎΟήΘγ· ϢΘϳ Ϧϟ ΔτθϨϟ΍ ΔϋϮϤΠϤϟ΍ ϲϓ ϲΘϟ΍ ϑϮϔμϟ΍
ϑϮϔμϟ΍ Ν΍ήΨΘγϹ FETCH ΓέΎΒόϟ΍ ϡΪΨΘδϧ
active set ϲϓ ϝϭϻ΍ ϒμϟ΍ ϞΒϗ ήηΆϤϟ΍ ϊοϭ
ΔϴλΎΨϟ΍ ϡ΍ΪΨΘγ· ϚϧΎϜϣΈΑ ΊτΧ Ϟ΋Ύγέ ϱ΍ PL/SQL ϊΟήϳ ϦϠϓ ήηΆϤϟ΍ ΢Θϓ ΪϨϋ ϑϮϔλ ϱ΃ ϡϼόΘγϻ΍ ϊΟήϳ Ϣϟ ΍Ϋ· ΔψΣϼϣ
ΔόΟήϤϟ΍ ϑϮϔμϟ΍ ΩΪϋ ΪϳΪΤΘϟ SQL%ROWCOUNT

Fetching Data from the Cursor
ϞϘΘϨϳ ˬ ϩΪΣ ϰϠϋ ϒλ Ϟϛ Ν΍ήΨΘγ· ΪόΑ ήΧϵ΍ ϮϠΗ ΪΣ΍ϭ active set ˰ϟ΍ Ϧϣ ϑϮϔμϟ΍ Ν΍ήΨΘγϹ FETCH ΓέΎΒόϟ΍ ϡΪΨΘδϧ
active set ϞΧ΍Ω ϲϟΎΘϟ΍ ϒμϟ΍ ϰϟ· ήηΆϤϟ΍
ϲϫ FETCH ΓέΎΒόϠϟ ΔϣΎόϟ΍ Δϐϴμϟ΍
FETCH cursor_name INTO [variable1, variable2, ...] | record_name];
ϥ΃ ΚϴΣ
ϪϔϳήόΗ ϢΗ ϱάϟ΍ ήηΆϤϟ΍ Ϣγ· Ϯϫ cursor_name
ήϴϐΘϣ ϲϓ ΎϬόοϭ ϢΘϳ ϡϼόΘγϻ΍ Ϧϣ ΎϬΟ΍ήΨΘγ· ϢΗ ϲΘϟ΍ ΔϤϴϘϟ΍ Ϯϫ Variable1 , 2
Ϫϴϓ ΕΎϧΎϴΒϟ΍ ϊοϭ ϢΘϴϟ ϪϔϳήόΗ ϢΗ ϱάϟ΍ ϞΠδϟ΍ ΕΎϧΎϴΑ ωϮϧ Ϧϣ ήϴϐΘϤϟ΍ Ϣγ΍ Ϯϫ record_name
Ε΍ΩΎηέ΍
˱΍Ϊϛ΄Θϣ Ϧϛϭ ˬ SELECT ΓέΎΒόϟ΍ ϊϣ ϖϓ΍ϮΘϳ ΎϤΑ FETCH ΓέΎΒόϠϟ ΔόΑΎΘϟ΍ INTO ΓέΎΒόϟ΍ ϲϓ Ε΍ήϴϐΘϤϟ΍ ΩΪϋ ϊοϮΑ Ϣϗ 
ΕΎϧΎϴΒϟ΍ ωϮϧ ΔϴΣΎϧ Ϧϣ ϪϘϓ΍ϮΘϣ ΎϬϧ΃
SELECT ΔϠϤΠϟ΍ ϲϓ ΓΪϤϋϻ΍ ϊϗ΍Ϯϣ βϔϨΑ Ε΍ήϴϐΘϤϟ΍ ΐΗέ 
FETCH ΓέΎΒόϟ΍ ϞϤϋ
ΎϬϔϳήόΗ ϢΗ ϲΘϟ΍ Ε΍ήϴϐΘϤϟ΍ ϰϟ· ϲϟΎΤϟ΍ ϒμϟ΍ Ϧϣ ΕΎϧΎϴΒϟ΍ ΃ήϘΗ
Ϫ΋΍ήϘϟ΍ ΔϴϠϤϋ ΪόΑ ϲϟΎΘϟ΍ ϒμϟ΍ ϰϟ· ήηΆϤϟ΍ ϙήΤΗ
Fetching Data from the Cursor