מדריך PL-SQL – פונקציות ופרוצדורות

פרוצדורה ופונקציות הינן "תת תוכניות" המורכבות מסדרה של משפטי SQL, המיועדים לבצע משימה מוגדרת.
Stored Procedure הינה בלוק של קוד אשר כבר עבר הידור (Compile) ומאוכסן באחת מטבלאות המערכת של מנוע ה-Oracle.

מבנה הפרוצדורה:

• החלק ההצהרתי – משמש להצהרה על משתנים
• החלק הביצועי – אוסף פקודות ה-SQK לביצוע
• החלק המטפל בשגיאות. חלק זה הינו אופציונאלי.

כאשר אנו קוראים לפרוצדורה, מנוע ה-Oracle מבצע את הפעולות הבאות:

• בדיקת הרשאות המשתמש
• בדיקת הסינטקס של הפרוצדורה
• ביצוע הפרוצדורה

הייתרונות בשימוש ב-Stored Procedures

• אבטחה
• יעילות
• הקצאת זכרון
• שמירה על שלמות המידע

ההבדל העיקרי בין פונקציה ופרוצדורה הוא שהפונקציה חחיבת להחזיר ערך למי שקרא לה.
הפונקציה יכול להחזיר רק ערך אחד ויחיד לבלוק ה-PL/SQL שקרא לה. אם נרצה להחזיר יותר מערך אחד, נצטרך להגדיר לפרוצדורה כמה משתני פלט (OUTPUT). משתני פלט הינם גולבאלים, וניתנים לגישה מכל בלוק PL/SQL.

תחביר

--Syntax for stored procedure: 
CREATE OR REPLACE PROCEDURE [schema] <procedurename>
 (<argument> { IN, OUT, IN OUT} <data_type>, ..) 
 {IS, AS} 
 
--variable declarations; constant declarations; 
BEGIN
     --pl/SQL subprogram body;
     EXCEPTION
              --exception pl/SQL block;
END;



--Syntax for stored function: 
CREATE OR REPLACE FUNCTION[schema] <functionname>
(<argument> IN <data_type>, ..) RETURN <data_type> 
{IS, AS} 
--variable declarations; constant declarations; 
BEGIN
     --pl/SQL subprogram body;
     EXCEPTION
              --exception pl/SQL block;
END;

סוגי הפרמטרים:

• IN – קובע כי בתוך המשתנה חייב להיות מוצב ערך כלשהו בזמן הקריאה לפרוצדורה (זוהי ברירת המחדל)
• OUT – מציין כי הפרוצדורה עצמה חייבת להזין ערך בתוך המשתנה, שייקרא מבלוק ה-PL/SQL שביצע את הקריאה לפרוצדורה.
• IN OUT – הערך של הפרמטר ייקבע לפני הקריאה לפרוצדורה, והפרוצדורה, בתורה, תתן לו ערך חדש שיוחזר לקורא.

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

DROP PROCEDURE <procedurename> 

דוגמאות

--PROCEDURE USING NO ARGUMENT..AND USING CURSOR
CREATE OR REPLACE PROCEDURE P2 IS
     
cursor cur1 is select * from emp;
begin
     for erec in cur1 
     loop
          dbms_output.put_line(erec.ename);
     end loop;
end;

--PROCEDURE USING ARGUMENT
CREATE OR REPLACE PROCEDURE ME( X IN NUMBER) IS
BEGIN
     dbms_output.put_line(x*x);
END;

SQL> exec me(3);

--FUNCTION using argument
CREATE OR REPLACE FUNCTION RMT(X IN NUMBER) RETURN NUMBER IS
BEGIN
     dbms_output.put_line(x*x); 
     --return (x*x);
end;

--(make a block like this to run it.....)
begin
     dbms_output.put_line(rmt(3));
end;

--CREATE A PROCEDURE THAT DELETE ROWS FROM ENQUIRY
--WHICH ARE 1 YRS BEFORE

Create or replace procedure myprocedure is 
begin
     delete from enquiry where enquirydate <= sysdate - 1;
end;

/*CREATE A PROCEDURE THAT TAKES ARGUMENT STUDENT NAME,
AND FIND OUT FEES PAID BY THAT STUDENT*/

CREATE or REPLACE procedure me (namee in varchar) is
     cursor c1 is 
     select a.feespaiddate from feespaid a, enrollment b, enquiry c
     where
     c.enquiryno = b.enquiryno and 
     a.rollno = b.rollno and
     c.fname = namee;

     begin
     for erec in c1
     loop
          dbms_output.put_line(erec.feespaiddate);
     end loop;
end;

--SUM OF 2 NOS
CREATE or replace procedure p1 is
Declare
     a number;
     b number;
     c number;
Begin
      a:=50;
      b:=89;
      c:=a+b;
      dbms_output.put_line('Sum of '||a||' and '||b||' is '||c);
End; 

--DELETION PROCEDURE
create or replace procedure myproc is
begin
     delete from enquiry where fname='somdutt';
end;

--IN and OUT procedure example
Create or replace procedure lest 
( a number, b out number) is
       identify number;
     begin
          select ordid into identity from item where
         itemid = a;
         if identity < 1000 then
              b := 100;
         end if;
end l 

--in out parameter eg
Create or replace procedure sample 
( a in number, b in out number) is
     identity number;

     begin
          select ordid, prodid into identity, b from item where itemid=a;
         if b<600 then
              b := b + 100;
         end if;
     end;

--now procedure is called by passing parameter

declare
     a number;
     b number;
begin
     sample(3000, b)
     dbms_output.put_line('1th value of b is ' || b);
end ;

--SIMILAR EG AS BEFORE
create or replace procedure getsal
( sal1 in out number) is

begin
     select sal into sal1 from emp
     where empno = sal1;
end ;

--now use the above in plSQL block

declare
     sal1 number := 7999;
begin
     getsal(sal1);
     dbms_output.put_line('The employee salary is' || sal1);
end ;