מדריך PL-SQL – סמנים (Cursors)

סמן הינו סביבת עבודה ב-PL/SQL, אשר משמש לאיחסון זמני של נתונים אשר הוחזרו משאילתה. ערכה של כל עמודה נשמר ע"י מצביע (Pointer).
ניתן לתפעל ולבצע פעולות מסויימות על כל שדה בפני עצמו.

ישנם שני סוגי סמנים:

• Implicit – אשר נוצרים ע"י מנוע ה-Oracle בעצמו
• Explicit – נוצרים ע"י המשתמש.

סמנים משמשים בדרך כלל כדי לאחסן נתונים שאילתות שהחזירו יותר מאשר ערך יחיד.

מאפייני הסמן:

• %ISOPEN – מחזיר true אם הסמן פתוח, אחרת, מחזיר false
• %FOUND – מחזיר true אם הרשומה יובאה בהצלחה, false אם לא.
• %NOTFOUND – מחזיר true אם הרשומה לא יובאה בהצלחה, false אם כן.
• %ROWCOUNT – מחזיר את מספר הרשומות הנכללות בסמן.

--EXAMPLE OF SQL%FOUND (IMPLICIT CURSORS)
begin
     update employee set salary=salary *0.15
     where emp_code = &emp_code;
     if SQL%found then
          dbms_output.put_line('employee record modified successfully');
     else
          dbms_output.put_line('employee no does not exist');
     end if;
end;

--EXAMPLE FOR SQL%NOTFOUND (IMPLICIT CURSORS)
begin
     update employee set salary = salary*0.15 where emp_code = &emp_code;
     f SQL%notfound then
          dbms_output.put_line('employee no . does not exist');
     else
          dbms_output.put_line('employee record modified successfully');
     end if;
end;

--EXAMPLE FOR SQL%ROWCOUNT (IMPLICIT CURSORS)
declare
     rows_affected char(4);
begin
     update employee set salary = salary*0.15 where job='programmers';
     rows_affected := to_char(SQL%rowcount);
     if SQL%rowcount > 0 then
          dbms_output.put_line(rows_affected || 'employee records modified successfully');
     else
          dbms_output.put_line('There are no employees working as programmers');
     end if;
end;

תפעול הסמן

תפעול הסמן נעשה ע"י שלוש פקודות פשוטות:

• Open – פותחת את הסמן ע"י שאילתה מסויימת
• Close – סוגרת את הסמן
• Fetch – מיבא את ערכי הרשומה הנוכחית לתוך משתנים.

תחביר

DECLARE
    CURSOR <cursor_name> IS <SQL_Statment>;
    -- variables declaration
BEGIN
    OPEN <cursor_name>;
    FETCH <cursor_name> INTO <var1>,<var2>...
    CLOSE <cursor_name>
END;

דוגמאות

--EXPLICIT CURSOR EG
DECLARE
    CURSOR c1 is SELECT * FROM emp;
    str_empno emp.empno%type;
    str_ename emp.ename%type;
    str_job emp.job%type;
    str_mgr emp.mgr%type;
    str_hiredate emp.hiredate%type;
    str_sal emp.sal%type;
    str_comm emp.comm%type;
    str_deptno emp.deptno%type;
    rno number;
BEGIN
    rno := &rno;
    FOR e_rec IN c1
    LOOP
        IF c1%rowcount = rno THEN
            DBMS_OUTPUT.PUT_LINE (str_empno || ' ' || str_ename || ' ' || 
                str_job || ' ' || str_mgr || ' ' || str_hiredate || ' ' || 
             str_sal || ' ' || str_comm || ' ' || str_deptno);
        END IF;
    END LOOP;
END;

--ANOTHER EG DISPLAYING VALUE OF A TABLE
DECLARE
    CURSOR c1 IS SELECT * FROM emp;
    e_rec emp%rowtype;
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO e_rec;
        DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
        DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
END;

-- Display details of Highest 10 salary paid employee

DECLARE
    CURSOR c1 IS SELECT * FROM emp ORDER BY sal DESC;
    e_rec emp%rowtype;
BEGIN
    FOR e_rec IN c1
    LOOP
        DBMS_OUTPUT.PUT_LINE('Number: ' || ' ' || e_rec.empno);
        DBMS_OUTPUT.PUT_LINE('Name : ' || ' ' || e_rec.ename);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || ' ' || e_rec.sal);
        EXIT WHEN c1%ROWCOUNT >= 10;
    END LOOP;
END;

-- EXAMPLE OF CURSOR FOR LOOP
declare 
     cursor c1 is select * from somdutt;
begin
     for outvariable in c1
     loop
          exit when c1%notfound;
          if outvariable.age < 21 then
              dbms_output.put_line(outvariable.age || ' ' || outvariable.name);
          end if;
     end loop;
end;

--ref STRONG CURSORS
DECLARE
    TYPE ecursor IS REF CURSOR RETURN emp%ROWTYPE;
    ecur ecursor;
    e_rec emp%ROWTYPE;
    dn NUMBER;
BEGIN
    dn := &deptno;
    OPEN ecur FOR SELECT * FROM emp WHERE deptno = dn;
    FOR e_rec IN ecur
    LOOP
        DBMS_OUTPUT.PUT_LINE ('Employee No : ' || e_rec.empno);
        DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || e_rec.salary);
    END LOOP;
END;

--REF WEAK CURSORS
DECLARE
    TYPE tcursor IS REF CURSOR;
    tcur tcursor;
    e1 emp%ROWTYPE;
    d1 dept%ROWTYPE;
    tname VARCHAR2(20);
BEGIN
    tname := &tablename;
    IF tname = 'emp' THEN
        OPEN tcur FOR SELECT * FORM emp;
        DBMS_OUTPUT.PUT_LINE ('Emp table opened.');
        close tcur;
        DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
    ELSE IF tname = 'dept' THEN
        OPEN tcur FOR SELECT * FROM dept;
        DBMS_OUTPUT.PUT_LINE ('Dept table opened.');
        close tcur;
        DBMS_OUTPUT.PUT_LINE ('Emp table closed.');
    ELSE
        RAISE_APPLICATION_ERROR (-20004, 'Table name is wrong');
    END IF;
END;

--CURSOR FOR LOOP WITH PARAMETERS
Declare
     Cursor c1(Dno number) is select * from emp where deptno = dno;
begin
     for empree in c1(10) loop;
          dbms_output.put_line(empree.ename);
     end loop;
end;