Crearea unei functii

CREATE [OR REPLACE] FUNCTION  nume_functie [(…)] RETURN return_type {IS|AS}
corpul_functiei;
RETURN expr;

In interiorul corpului functiei  declaratia  RETURN este utilizatã  pentru a intoarce  controlul programului apelant impreuna cu o valoare.
• o functie este foarte  asemanatoare unei proceduri.
• ambele pot fi  stocate in baza de date sau declarate  in interiorul unui bloc (o functie locala)
• functia trebuie sa intoarca o valoare  (sau poate intoarce mai multe valori prin intermediul parametrilor precum procedurile) -- ideal este  sa intoarca doar o sigura valoare - pentru celelalte situatii - mai multe valori respectiv nici o valoare, se vor folosi procedurile

Urmatoarele  doua exemple aratã diferenta dintre  proceduri si functii in modul lor de declarare si  apelare.

 Se creazã o  procedura ‘adauga_student’ si se memoreaza in baza de date

CREATE OR REPLACE PROCEDURE adauga_student( p_nume student.nume%TYPE,
               p_prenume student.prenume%TYPE,
               p_specializare student.specializare%TYPE DEFAULT ‘calculatoare’) AS
BEGIN
              INSERT INTO student
                     VALUES(student_sequence.nextval, p_nume, p_prenume, p_specializare);
              COMMIT;
END adauga_student;

Pentru a testa procedura “adauga_student”, in alt bloc  PL/SQL ,aceasta va fi apelata astfel:

BEGIN
           adauga_student(‘Marinescu’, ‘Dan’);
END;

Se  va crea functia ‘Grupa_completa’ si se va memora in baza de date.

CREATE OR REPLACE FUNCTION Grupa_completal(
                            p_specializare  grupe.specializare%TYPE,
                            p_curs grupe.curs%TYPE) RETURN BOOLEAN IS
                            v_curStudent NUMBER;
                            v_maxStudent NUMBER;
                            v_returnValue BOOLEAN;
BEGIN
                           SELECT cur_student, max_student INTO v_curStudent, v_maxStudent
                           FROM grupe
                           WHERE specializare = p_specializare AND curs = p_curs;

                           IF v_curStudent >= v_maxStudent
                                 v_returnValue = TRUE;
                          ELSE v_returnValue = FALSE;

                          RETURN v_returnValue;
END Grupa_completa;

In alt bloc  PL/SQL se va apela funcxtia dupa cum urmeaya:

BEGIN
        IF Grupa_completa(‘calculatoare’, ‘Oracle’) THEN
           DBMS_OUTPUT.PUT_LINE(‘grupa este completa’);
        ELSE DBMS_OUTPUT.PUT_LINE(‘grupa nu este completa’);
END;

Exemplu de functie locala

DECLARE
        CURSOR c_totistudentii IS
              SELECT nume, prenume
              FROM student;
        v_formatareNume VARCHAR2(50);

       -- definitia functiei va fi facuta mai tarziu    – toate variabilele trebuie definite  inainte
       FUNCTION formatNume(p_nume IN VARCHAR2, p_prenume IN VARCHAR2)
                    RETURN VARCHAR2 IS
       BEGIN
                    RETURN p_nume || ‘ ‘ || p_prenume;
       END formatNume;

-- Inceputul blocului principal
BEGIN
          FOR v_studentRecord IN c_totiStudentii
          LOOP
                    v_formatareNume := formatNume(v_studentRecord.nume,
                                                                                           v_studentRecord.prenume);
                                   INSERT INTO temp_table(fname) VALUES (v_formatareNume);
         END LOOP;
         COMMIT;
END;

Apelul procedurilor stocate  de la prompt-ul  SQL*PLUS
In mod uzual, procedurile si functiile sunt  apelate in cadrul sectiunilor  de executabile sau  de manipulare a exceptiilor. La nevoie, acestea pot fi apelate  din interfata SQL *PLUS cu ajutorul comenzii EXECUTE:

EXECUTE nume_procedura(arg1, arg2, ..)

Localizarea subprogramelor

• subprogramele pot fi  stocate in dictionarul de  date (in forma de cod obiect- numit si p-cod) sau in sectiunea declarativa a unui bloc (caz in care se numesc  subprograme locale)
• informatii referitoare la acestea se gasesc in : user_objects, user_source, user_errors

Proceduri/Functii si  dependente
• Procedurile/functiile sunt dependente de un tabel daca acceseaza  tabelul respectiv. Aceasta inseamna  ca  daca structura tabelului  este modificata  toate proceurile respectiv toate functiile dependente  trebuie sa fie  recompilate  prin utilizarea comenzii  ALTER.

Comenzi corespunzatoare procedurilor si functiilor
Recompilarea unei proceduri:
ALTER PROCEDURE nume_proc COMPILE

Eliminarea unei  proceduri/functii:
DROP PROCEDURE nume_proc;
DROP FUNCTION nume_function;

Pentru a  permite  unui user sa execute o  procedura :
GRANT EXECUTE ON nume_proc TO nume_user;