SGBD7 - PLSQL pachete
-
Upload
rosu-lucian -
Category
Documents
-
view
49 -
download
2
description
Transcript of SGBD7 - PLSQL pachete
-
Sisteme de Gestiune a Bazelor de Date 7-1
CUPRINS
7. PL/SQL Pachete ................................................................................................................... 2
7.1. Definirea pachetelor ....................................................................................................... 3
7.1.1. Specificaia pachetului ............................................................................................. 6
7.1.2. Corpul pachetului ..................................................................................................... 6
7.1.3. Instanierea pachetului ............................................................................................. 7
7.2. Modificare,tergerea i modificarea pachetelor .............................................................. 8
7.3. Pachete predefinite ....................................................................................................... 11
7.3.1. Pachetul STANDARD ............................................................................................. 12
7.3.2. Pachetul DBMS_OUTPUT ..................................................................................... 13
7.3.3. Pachetul DBMS_JOB ............................................................................................. 13
7.3.4. Pachetul UTL_FILE ............................................................................................... 15
7.3.5. Pachetul DBMS_SQL ............................................................................................. 16
7.3.6. Pachetul DBMS_DDL ............................................................................................ 19
Bibliografie ........................................................................................................................... 22
-
Sisteme de Gestiune a Bazelor de Date 7-2
7. PL/SQL Pachete
Un pachet (package) permite ncapsularea ntr-o unitate logic a:
o constantelor i variabilelor;
o tipurilor i excepiilor;
o cursoarelor;
o procedurilor i funciilor.
Pachetele sunt:
o uniti de program compilate;
o obiecte ale bazei de date care grupeaz tipuri, obiecte i subprograme PL/SQL
avnd o legtur logic ntre ele.
Ce fel de subprograme integrm ntr-un pachet?
Importana pachetelor
Atunci cnd este refereniat un pachet (atunci cnd este apelat pentru prima dat o
construcie a pachetului), ntregul pachet este ncrcat n SGA (zona global sistem) i
este pregtit pentru execuie.
Plasarea pachetului n SGA reprezint avantajul vitezei de execuie, deoarece server-ul
nu mai trebuie s aduc informaia despre pachet de pe disc, aceasta fiind deja n
memorie.
o Apelurile ulterioare ale unor construcii din acelai pachet, nu solicit operaii I/O
de pe disc.
o De aceea, ori de cte ori apare cazul unor proceduri i funcii nrudite care trebuie
s fie executate mpreun, este convenabil ca acestea s fie grupate ntr-un pachet
stocat.
o n memorie exist o singur copie a unui pachet (pentru toi utilizatorii).
Subprogramelor overload pot deveni stocate prin intermediul pachetelor.
Diferena fa de subprograme
Spre deosebire de subprograme, pachetele nu pot:
o fi apelate;
o transmite parametri;
o fi imbricate.
-
Sisteme de Gestiune a Bazelor de Date 7-3
7.1. Definirea pachetelor
Un pachet conine dou pari, fiecare fiind stocat separat n dicionarul datelor:
o specificaia (package specification)
este partea vizibil a pachetului, adic interfaa cu aplicaiile sau cu alte
uniti program;
poate conine declaraii de tipuri, constante, variabile, excepii, cursoare i
subprograme (care vor fi vizibile altor aplicaii);
se declar prima (naintea corpului pachetului).
o corpul (package body)
este partea ascuns a pachetului, mascat de restul aplicaiei;
conine codul care implementeaz obiectele definite n specificaie (cursoarele
i subprogramele) i, de asemenea, obiecte i declaraii proprii;
poate conine obiecte publice (declarate n specificaie) sau private (nu sunt
declarate n specificaie).
Fig. 7.1. Pachete PL/SQL
Variabil_1
Declaraie Subprogram A Specificaia Pachetului
Corpul Pachetului
Variabil_2
Definiie Subprogram A
BEGIN ... END;
Variabil_3
Definiie Subprogram B
-
Sisteme de Gestiune a Bazelor de Date 7-4
Definirea unui pachet se realizeaz n dou etape:
o crearea specificaiei pachetului;
o crearea corpului pachetului.
Se pot defini pachete care cuprind doar partea de specificaie?
Se pot defini pachete care cuprind doar corpul pachetului?
Ce situaii impun definirea att a specificaiei, ct i a corpului pachetului?
Corpul pachetului poate fi schimbat fr a modifica specificaia acestuia?
Dac specificaia este schimbat, aceasta invalideaz automat corpul
pachetului, deoarece corpul depinde de specificaie.
Specificaia i corpul pachetului sunt uniti compilate separat.
Corpul poate fi compilat doar dup ce specificaia a fost compilat cu succes.
Pentru a crea un pachet n schema personal este necesar privilegiul sistem CREATE
PROCEDURE, iar pentru a crea un pachet n alt schem este necesar privilegiul
sistem CREATE ANY PROCEDURE.
Un pachet definit este disponibil pentru utilizatorul care l-a creat sau orice utilizator
cruia i s-a acordat privilegiul EXECUTE asupra pachetului respectiv.
-
Sisteme de Gestiune a Bazelor de Date 7-5
Sintaxa:
CREATE PACKAGE nume_pachet
{IS | AS} specificaia pachetului
-- declaraii de tipuri i obiecte publice,
-- specificaii de cursoare i subprograme
END [nume_pachet];
/
CREATE PACKAGE BODY nume_pachet
{IS | AS} -- corpul pachetului
/* declaraii de obiecte i tipuri private,
corpurile cursoarelor i subprogramelor precizate
n specificaie */
[BEGIN
/* instruciuni de iniializare, executate o singur
dat, atunci cnd pachetul este invocat prima
dat n sesiunea utilizatorului */
]
END [nume_pachet];
/
Procesul de creare a specificaiei i corpului unui pachet urmeaz acelai algoritm ca
cel ntlnit n crearea subprogramelor PL/SQL independente:
o sunt verificate erorile sintactice i semantice, iar modulul este depus n dicionarul
datelor;
o sunt verificate instruciunile SQL individuale, adic dac obiectele referite exist
i dac utilizatorul le poate accesa;
o sunt comparate declaraiile de subprograme din specificaia pachetului cu cele din
corpul pachetului (dac au acelai numr i tip de parametri).
Orice eroare detectat la compilarea specificaiei sau a corpului pachetului este
marcat n dicionarul datelor.
Dup ce specificaia i corpul pachetului sunt compilate, acestea devin obiecte n
schema curent.
o n vizualizarea USER_OBJECTS din dicionarul datelor, vor aprea dou noi linii:
OBJECT_TYPE OBJECT_NAME
PACKAGE nume_pachet
PACKAGE BODY nume_pachet
-
Sisteme de Gestiune a Bazelor de Date 7-6
7.1.1. Specificaia pachetului
Specificaia unui pachet cuprinde declararea procedurilor, funciilor, constantelor,
variabilelor i excepiilor care pot fi accesibile aplicaiilor, adic declararea obiectelor
de tip PUBLIC din pachet.
o Acestea pot fi utilizate n comenzi sau proceduri care nu aparin pachetului.
Este necesar privilegiul EXECUTE asupra pachetului.
Variabilele declarate n specificaia unui pachet sunt globale pachetului i
sesiunii.
Acestea sunt iniializate (implicit) cu valoarea NULL, evident dac nu este
specificat explicit o alt valoare.
Sintaxa:
CREATE [OR REPLACE] PACKAGE [schema.]nume_pachet
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
specificaie_PL/SQL;
o Specificaie_PL/SQL poate include declaraii de tipuri, variabile, cursoare,
excepii, funcii, proceduri etc.
n seciunea declarativ, un obiect trebuie declarat nainte de a fi referit.
o Opiunea OR REPLACE este specificat dac exist deja corpul pachetului.
o Clauzele IS i AS sunt echivalente, dar dac se folosete PROCEDURE BUILDER
este necesar opiunea IS.
o Clauza AUTHID specific faptul ca subprogramele pachetului se execut cu
drepturile proprietarului (implicit) sau ale utilizatorului curent.
De asemenea, aceast clauz precizeaz dac referinele la obiecte sunt
rezolvate n schema proprietarului subprogramului sau a utilizatorului curent.
7.1.2. Corpul pachetului
Corpul unui pachet conine codul PL/SQL pentru obiectele declarate n specificaia
acestuia i obiectele private pachetului.
De asemenea, corpul poate include o seciune declarativ n care sunt specificate
definiii locale de tipuri, variabile, constante, proceduri i funcii locale.
o Obiectele private sunt vizibile numai n interiorul corpului pachetului i pot fi
accesate numai de ctre funciile i procedurile din pachetul respectiv.
-
Sisteme de Gestiune a Bazelor de Date 7-7
Corpul pachetului este opional i nu este necesar s fie creat dac specificaia
pachetului nu conine declaraii de proceduri sau funcii.
Ordinea n care subprogramele sunt definite n interiorul corpului pachetului
este important.
O variabil trebuie declarat nainte de a fi referit de alt variabil sau
subprogram, iar un subprogram privat trebuie declarat sau definit nainte de a fi
apelat de alte subprograme.
Sintaxa:
CREATE [OR REPLACE] PACKAGE BODY [schema.]nume_pachet
{IS | AS}
corp_pachet;
7.1.3. Instanierea pachetului
Un pachet este instaniat atunci cnd este apelat prima dat.
o Pachetul este citit de pe disc, depus n memorie i este executat codul compilat al
subprogramului apelat.
o n acest moment, memoria este alocat tuturor variabilelor definite n pachet.
n multe cazuri, atunci cnd pachetul este instaniat prima dat ntr-o sesiune, sunt
necesare anumite iniializri.
o Aceasta se realizeaz prin adugarea unei seciuni de iniializare (opional) n
corpul pachetului seciune ncadrat ntre cuvintele cheie BEGIN i END.
o Seciunea conine un cod de iniializare care este executat atunci cnd pachetul este
invocat pentru prima dat.
Referina la o declaraie sau la un obiect specificat n pachet se face prefixnd numele
obiectului cu numele pachetului.
o n corpul pachetului, obiectele din specificaie sunt referite fr a specifica numele
pachetului.
-
Sisteme de Gestiune a Bazelor de Date 7-8
7.2. Modificarea, tergerea i utilizarea pachetelor
Modificarea pachetului
Dac se dorete modificarea sursei pachetului, atunci utilizatorul poate recrea pachetul
(cu opiunea OR REPLACE) pentru a-l nlocui pe cel existent.
Schimbarea corpului pachetului nu impune recompilarea construciilor
dependente.
Schimbrile n specificaia pachetului necesit recompilarea fiecrui
subprogram stocat care refereniaz pachetul.
Folosind comanda ALTER PACKAGE se pot recompila explicit specificaia
pachetului, corpul pachetului sau ambele module.
o Recompilarea explicit elimin necesitatea recompilrii implicite a pachetului la
run-time.
o Deoarece ntr-un pachet toate obiectele sunt stocate ca o unitate, comanda ALTER
PACKAGE determin recompilarea tuturor obiectelor pachetului.
o Dac n timpul recompilrii apar erori, atunci este ntors un mesaj, iar pachetul
devine invalid.
Pentru a consulta mesajele erorilor se poate utiliza comanda SQL*Plus SHOW
ERRORS.
Funciile i procedurile dintr-un pachet nu pot fi recompilate individual folosind
comenzile ALTER FUNCTION sau ALTER PROCEDURE.
Sintaxa:
ALTER PACKAGE [schema.]nume_pachet
COMPILE {PACKAGE | SPECIFICATION | BODY};
o Clauza PACKAGE determin recompilarea att a specificaiei, ct i a corpului
pachetului.
Este opiune implicit.
o Clauza SPECIFICATION determin recompilarea specificaiei pachetului.
Dup modificarea specificaiei unui pachet se poate dori recompilarea acesteia,
pentru a verifica dac apar erori de compilare.
-
Sisteme de Gestiune a Bazelor de Date 7-9
Atunci cnd se recompileaz specificaia pachetului, baza de date invalideaz
orice obiect local care depinde de acea specificaie (ca de exemplu, proceduri
care invoc proceduri sau funcii din pachet).
Corpul pachetului depinde de asemenea de specificaie. n lipsa unei recompilri
explicite, baza de date va recompila implicit obiectele dependente.
o Clauza BODY determin doar recompilarea corpului pachetului.
Recompilarea corpului pachetului nu invalideaz obiectele care depind de
specificaie.
tergerea pachetului
Sintaxa:
DROP PACKAGE [BODY] [schema.]nume_pachet;
o Dac n cadrul comenzii apare opiunea BODY, atunci este ters doar corpul
pachetului.
o Dac se omite opiunea BODY, atunci sunt terse att specificaia, ct i corpul
pachetului.
Dac pachetul este ters, toate obiectele dependente de acesta devin invalide. Dac
este ters numai corpul, toate obiectele dependente de acesta rmn valide. n schimb,
nu pot fi apelate subprogramele declarate n specificaia pachetului, pn cnd nu este
recreat corpul pachetului.
Pentru ca un utilizator s poat terge un pachet trebuie ca pachetul s aparin
schemei utilizatorului sau utilizatorul s posede privilegiul sistem DROP ANY
PROCEDURE.
Utilizarea pachetului
Se realizeaz n funcie de mediul (SQL sau PL/SQL) care solicit un obiect din
pachetul respectiv.
o Referirea unui obiect din pachet se realizeaz prefixnd numele acestuia cu numele
pachetului.
o De exemplu, invocarea unei proceduri definite ntr-un pachet se realizeaz n:
PLSQ/SQL, prin comanda
nume_pachet.nume_procedur[(list_argumente)];
SQL*Plus, prin comanda:
EXECUTE nume_pachet.nume_procedur [(list_argumente)]
-
Sisteme de Gestiune a Bazelor de Date 7-10
Exemplul 7.1 vezi curs-
Exemplul 7.2 vezi curs-
Comenzile LCD - COMMIT, ROLLBACK, SAVEPOINT
Un trigger nu poate apela un subprogram care conine comenzile COMMIT,
ROLLBACK, SAVEPOINT. Prin urmare, pentru flexibilitatea apelului de ctre
trigger-i a subprogramelor coninute n pachete, niciun subprogram al pachetului
nu trebuie s conin aceste comenzi.
ntr-un pachet nu pot fi referite variabile gazd.
ntr-un pachet sunt permise declaraii forward.
Invalidarea modulelor PL/SQL
Dac un subprogram independent apeleaz un subprogram definit ntr-un pachet,
atunci apar urmtoarele situaii:
- atunci cnd corpul pachetului este modificat, dar specificaia acestuia nu,
subprogramul care refer o construcie a pachetului rmne valid;
- dac specificaia pachetului este modificat, atunci subprogramul care refer o
construcie a pachetului, precum i corpul pachetului devin invalide.
Dac un subprogram independent referit de un pachet se modific, atunci ntregul
corp al pachetului devine invalid, dar specificaia pachetului rmne valid.
Exemplul 7.3 vezi curs- - -
Exemplul 7.4 vezi curs- - -
Exemplul 7.5 vezi curs- - -
Exemplul 7.6 vezi curs-
Un cursor declarat n specificaia unui pachet este un tip de variabil global
i respect aceleai reguli privind persistena ca i celelalte variabile.
Statusul unui cursor nu este definit de o singur valoare (ca n cazul
variabilelor), ci din urmtoarele atribute:
- %ISOPEN (dac este deschis sau nchis);
- %ROWCOUNT (dac este deschis, numrul de linii ncrcate);
- %FOUND sau %NOTFOUND (dac ultimul FETCH a avut succes).
-
Sisteme de Gestiune a Bazelor de Date 7-11
7.3. Pachete predefinite
PL/SQL conine o serie de pachete predefinite utile n dezvoltarea aplicaiilor.
Pachetele predefinite adaug noi funcionaliti limbajului, protocoale de comunicaie,
acces la fiierele sistemului etc.
Exemple de pachete predefinite:
o STANDARD
definete mediul PL/SQL
conine funciile predefinite
o DBMS_STANDARD
faciliti ale limbajului utile pentru interaciunea aplicaiei cu server-ul Oracle
o DBMS_OUTPUT
permite afiarea de informaii
o DBMS_DDL
permite accesarea anumitor comenzi LDD din PL/SQL; n plus, ofer operaii
speciale de administrare
o DBMS_JOB
permite planificarea i gestiunea job-urilor
o DBMS_SQL
ofer o interfa pentru a putea utiliza SQL dinamic
o DBMS_PIPE
permite operaii de comunicare ntre dou sau mai multe sesiuni conectate la
aceeai instan Oracle
o DBMS_LOCK
este utilizat pentru a cere, a modifica sau a elibera blocrile din baza de date
permite folosirea exclusiv sau partajat a unei resurse
o DBMS_MVIEW / DBMS_SNAPSHOT
permite exploatarea vizualizrilor materializate
o DBMS_UTILITY
ofer utiliti DBA, permite analiza obiectelor unei scheme, verific dac
server-ul lucreaz n mod paralel etc.
o DBMS_LOB
ofer mecanisme de acces i prelucrare a datelor de tip LOB
permite compararea datelor de tip LOB, adugarea de date la un LOB, copierea
datelor dintr-un LOB n altul, tergerea unor poriuni din date LOB,
deschiderea, nchiderea i regsirea de informaii din date BFILE etc.
-
Sisteme de Gestiune a Bazelor de Date 7-12
o UTL_FILE
permite citirea/scrierea din/n fiierele text ale sistemului de operare
o UTL_MAIL
permite crearea i trimiterea unui e-mail
o UTL_HTTP
permite utilizarea protocolului HTTP pentru a accesa date de pe Internet
o UTL_TCP
permite aplicaiilor PL/SQL s comunice cu server-e externe utiliznd
protocolul TCP/IP
7.3.1. Pachetul STANDARD
Este un pachet predefinit fundamental n care se declar tipurile, excepiile,
subprogramele care sunt utilizabile automat n programele PL/SQL.
Conine funciile predefinite (de exemplu, UPPER, ABS, TO_CHAR etc.).
Coninutul acestui pachet este vizibil tuturor aplicaiilor.
Pentru referirea componentelor acestui pachet nu trebuie utilizat prefixarea cu
numele pachetului.
Exemplul 7.7 vezi explicaii curs -
SELECT STANDARD.ABS(-1), ABS(-1)
FROM DUAL;
Se pot defini funcii cu acelai nume ca i cele predefinite?
Funcia din exemplul 7.8 se poate defini?
Exemplul 7.8 vezi explicaii curs
CREATE OR REPLACE FUNCTION ABS(x NUMBER)
RETURN VARCHAR2
IS
BEGIN
IF X
-
Sisteme de Gestiune a Bazelor de Date 7-13
7.3.2. Pachetul DBMS_OUTPUT
Trimite mesaje text dintr-un bloc PL/SQL ntr-o zon privat de memorie, din care
mesajele pot fi afiate pe ecran.
Pachetul este utilizat preponderent:
o n timpul testrii i depanrii programelor;
o pentru a afia mesaje i rapoarte n SQL*DBA sau SQL*Plus;
o pentru a urmrii paii de execuie a unui program.
Subprograme definite n pachet:
o PUT depune text n buffer (pe o singur linie);
o NEW_LINE trimite coninutul buffer-ului pe ecran (adaug n buffer un sfrit de
linie);
o PUT_LINE depune text n buffer (PUT) i trimite coninutul buffer-ului pe ecran
(NEW_LINE);
o GET_LINE citete din buffer o singur linie;
o GET_LINES citete din buffer mai multe linii (le depune ntr-o colecie);
o DISABLE dezactiveaz apelurile procedurilor PUT, NEW_LINE, PUT_LINE,
GET_LINE, GET_LINES i elimin informaia depus n buffer;
o ENABLE activeaz apelurile procedurilor PUT, NEW_LINE, PUT_LINE,
GET_LINE, GET_LINES i permite specificarea dimensiunii buffer-ului.
Exemplul 7.9 vezi curs
Exemplul 7.10 vezi curs
Exemplul 7.11 vezi curs
7.3.3. Pachetul DBMS_JOB
Pachetul DBMS_JOB este utilizat pentru planificarea programelor PL/SQL n vederea
execuiei.
Cu ajutorul acestui pachet:
o se pot executa programe PL/SQL la momente determinate de timp;
o se pot terge sau suspenda programe din lista de planificri n vederea execuiei;
o se pot rula programe de ntreinere a sistemului n perioadele de timp n care acesta
este mai puin solicitat (de exemplu, noaptea) etc.
-
Sisteme de Gestiune a Bazelor de Date 7-14
Subprograme definite n pachet:
o SUBMIT adaug un nou job n coada de ateptare;
o REMOVE terge un job specificat din coada de ateptare;
o RUN execut imediat un job specificat;
o BROKEN dezactiveaz execuia unui job i l seteaz broken (implicit, orice job
este not broken, iar un job marcat broken nu se execut);
o WHAT descrie un job specificat;
o NEXT_DATE specific momentul urmtoarei execuii a unui job;
o INTERVAL specific intervalul de timp scurs dintre dou execuii consecutive ale
unui job;
o CHANGE modific argumentele WHAT, NEXT_DATE, INTERVAL.
Fiecare dintre subprogramele pachetului are argumente specifice.
o Procedura SUBMIT are ca argumente:
JOB de tip OUT, un identificator pentru job (BINARY_INTEGER);
WHAT de tip IN, codul PL/SQL care va fi executat ca un job (VARCHAR2);
NEXT_DATE de tip IN, data urmtoarei execuii a job-ului (implicit este
SYSDATE);
INTERVAL de tip IN, funcie care furnizeaz intervalul dintre execuiile job-
ului (VARCHAR2, implicit este null);
NO_PARSE de tip IN, variabil logic care indic dac job-ul trebuie analizat
gramatical (BOOLEAN, implicit este FALSE).
Exemplul 7.12 vezi curs
Vizualizarea DBA_JOBS din dicionarul datelor furnizeaz informaii referitoare la
starea tuturor job-urilor din coada de ateptare.
Vizualizarea DBA_JOBS_RUNNING conine informaii despre job-urile care sunt n
curs de execuie.
Exemplul 7.13
SELECT JOB, LOG_USER, NEXT_DATE, BROKEN, WHAT
FROM DBA_JOBS;
SELECT *
FROM DBA_JOBS_RUNNING;
-
Sisteme de Gestiune a Bazelor de Date 7-15
7.3.4. Pachetul UTL_FILE
- Pachetul UTL_FILE permite programului PL/SQL scrierea n fiiere text definite la
nivelul sistemului de operare, respectiv citirea din aceste fiiere.
- Tipuri de date definite pachetul UTL_FILE
o FILE_TYPE
- Specificaia tipului:
TYPE file_type IS RECORD (
id BINARY_INTEGER,
datatype BINARY_INTEGER);
- Subprograme definite n pachetul UTL_FILE
o funcii
FOPEN
- Deschide un fiier i ntoarce un handler care va fi utilizat n urmtoarele
operaii I/O.
- Specificaia funciei:
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN file_type;
- Parametrul open_mode este un string care specific pentru ce operaii a fost
deschis fiierul: r (read text), w (write text), a (append text), rb (read byte
mode), wb (write byte mode) sau ab (append byte mode).
IS_OPEN
- ntoarce valoarea TRUE dac fiierul este deschis, altfel ntoarce FALSE.
- Specificaia funciei:
UTL_FILE.IS_OPEN(file IN FILE_TYPE)
RETURN BOOLEAN;
o proceduri
GET_LINE
- Citete o linie din fiierul deschis pentru citire i o plaseaz ntr-un buffer de
tip ir de caractere.
PUT i PUT_LINE
- Permit scrierea textului din buffer n fiierul deschis pentru scriere sau
adugare.
-
Sisteme de Gestiune a Bazelor de Date 7-16
PUTF
- Este asemntoare funciei printf().
- Este o procedur PUT cu format.
NEW_LINE
- Scrie n fiier un caracter sfrit de linie specific fiierelor sistemului de
operare.
FCLOSE
- nchide un fiier
FCLOSEALL
- nchide toate handler-urile fiierului deschis.
Utilizarea componentelor acestui pachet pentru procesarea fiierelor sistemului de
operare poate declana excepii, printre care:
o INVALID_PATH numele sau locaia fiierului sunt invalide;
o INVALID_MODE parametrul OPEN_MODE (prin care se specific dac fiierul
este deschis pentru citire, scriere, adugare) este invalid;
o INVALID_FILEHANDLE handler-ul de fiier obinut n urma deschiderii este
invalid;
o INVALID_OPERATION operaie invalid asupra fiierului;
o READ_ERROR o eroare a sistemului de operare a aprut n timpul operaiei de
citire;
o WRITE_ERROR o eroare a sistemului de operare a aprut n timpul operaiei de
scriere;
o INTERNAL_ERROR o eroare nespecificat a aprut n PL/SQL.
Exemplul 7.14 vezi curs
7.3.5. Pachetul DBMS_SQL
Permite utilizarea dinamic a comenzilor SQL n proceduri stocate sau n blocuri
anonime.
o Comenzile dinamice nu sunt ncorporate n programul surs, ci sunt depuse n
iruri de caractere.
O comand SQL dinamic este o instruciune SQL care conine variabile ce se pot
schimba n timpul execuiei.
o De exemplu, pot fi utilizate instruciuni SQL dinamice pentru:
a crea o procedur care opereaz asupra unui tabel al crui nume nu este
cunoscut dect n momentul execuiei;
-
Sisteme de Gestiune a Bazelor de Date 7-17
a scrie i executa o comand LDD;
a scrie i executa o comand GRANT, ALTER SESSION etc.
n PL/SQL comenzile date ca exemplu anterior nu pot fi executate static.
Pachetul DBMS_SQL permite, de exemplu, ca ntr-o procedur stocat s fie
utilizat o comand DROP TABLE.
Utilizarea pachetului DBMS_SQL pentru a executa comenzi LDD poate genera
interblocri. De exemplu, pachetul este utilizat pentru a terge o procedur care
ns este utilizat.
SQL dinamic suport toate tipurile de date SQL, dar nu suport tipurile de date
specifice PL/SQL.
Orice comand SQL trebuie s treac prin anumite etape (unele putnd fi evitate):
o analizarea sintactic;
o validarea;
o asigurarea c toate referinele la obiecte sunt corecte;
o asigurarea c exist privilegiile referitoare la acele obiecte (parse);
o obinerea de valori pentru variabilele de legtur din comanda (binding variables);
o executarea comenzii (execute);
o selectarea linilor rezultatului;
o ncrcarea liniilor rezultatului (fetch).
Dintre subprogramele pachetului DBMS_SQL, care permit implementarea etapelor
amintite anterior, se remarc:
o OPEN_CURSOR (deschide un nou cursor, adic se stabilete o zon de memorie
n care este procesat comanda SQL);
o PARSE (stabilete validitatea comenzii SQL, adic se verific sintaxa instruciunii
i se asociaz cursorului deschis);
o BIND_VARIABLE (leag valoarea dat de variabila corespunztoare din comanda
SQL analizat)
o EXECUTE (execut comanda SQL i ntoarce numrul de linii procesate);
o FETCH_ROWS (regsete o linie pentru un cursor specificat, iar pentru mai multe
linii folosete un LOOP);
o CLOSE_CURSOR (nchide cursorul specificat).
-
Sisteme de Gestiune a Bazelor de Date 7-18
Exemplul 7.15 vezi curs
Exemplul 7.16 vezi curs
SQL Dinamic nativ
Comanda de baz utilizat pentru procesarea dinamic nativ a comenzilor SQL i a
blocurilor PL/SQL este EXECUTE IMMEDIATE, care are urmtoarea sintax:
EXECUTE IMMEDIATE ir_dinamic
[[BULK COLLECT] INTO {def_variabila [, def_variabila ] |
record} ]
[USING [IN | OUT | IN OUT] argument_bind
[, [IN | OUT | IN OUT] argument_bind ] ]
[ {RETURNING | RETURN}
[BULK COLLECT] INTO argument_bind [, argument_bind ] ];
o ir_dinamic este un ir de caractere care reprezint o comand SQL (fr caracter de
terminare ;) sau un bloc PL/SQL (fr caracter de terminare /).
o def_variabila reprezint variabila n care se stocheaz valoarea coloanei selectate.
o record reprezint nregistrarea n care se depune o linie selectat.
o argument_bind, dac se refer la valori de intrare (IN) este o expresie (comand SQL
sau bloc PL/SQL), iar dac se refer la valori de ieire (OUT) este o variabil ce va
conine valoarea selectat de comanda SQL sau de blocul PL/SQL.
o Clauza INTO este folosit pentru cereri care ntorc o singur linie, iar clauza USING
pentru a reine argumentele de legtur.
o Pentru procesarea unei cereri care ntoarce mai multe linii sunt necesare instruciunile
OPENFOR, FETCH i CLOSE.
o Prin clauza RETURNING sunt precizate variabilele care conin rezultatele.
Exemplul 7.17 vezi curs
Exemplul 7.18 vezi curs
Exemplul 7.19 vezi curs
Exemplul 7.20 vezi curs
-
Sisteme de Gestiune a Bazelor de Date 7-19
SQL Dinamic nativ versus pachetul DBMS_SQL
Pentru execuia dinamic a comenzilor SQL n PL/SQL exist dou tehnici:
o utilizarea pachetului DBMS_SQL;
o SQL dinamic nativ.
Dac s-ar face o comparaie ntre SQL dinamic nativ i funcionalitatea pachetului
DBMS_SQL, se poate sublinia c SQL dinamic nativ:
o este mai uor de utilizat;
o solicit mai puin cod;
o este mai rapid;
o poate ncrca liniile direct n nregistrri PL/SQL;
o suport toate tipurile acceptate de SQL static n PL/SQL, inclusiv tipuri definite de
utilizator.
Fa de SQL dinamic nativ pachetul DBMS_SQL:
o suport comenzi SQL mai mari de 32 KB;
o permite ncrcarea nregistrrilor (procedura FETCH_ROWS);
o accept comenzi cu clauza RETURNING pentru reactualizarea i tergerea de linii
multiple;
o suport posibilitile oferite de comanda DESCRIBE (procedura
DESCRIBE_COLUMNS);
o analizeaz validitatea unei comenzi SQL o singur dat (procedura PARSE),
permind ulterior mai multe utilizri ale comenzii pentru diferite mulimi de
argumente.
7.3.6. Pachetul DBMS_DDL
Permite accesul la anumite comenzi LDD care pot fi folosite n subprograme PL/SQL
stocate.
o De exemplu, prin intermediul acestui pachet pot utilizate n PL/SQL comenzile
ALTER sau ANALYZE.
Procedura ALTER_COMPILE
Permite recompilarea programului modificat (procedur, funcie, declanator, pachet,
corp pachet).
-
Sisteme de Gestiune a Bazelor de Date 7-20
Sintaxa
ALTER_COMPILE (tip_obiect, nume_schema, nume_obiect);
Instruciune echivalent SQL
ALTER PROCEDURE | FUNCTION | PACKAGE [nume_schema.]nume
COMPILE [ PACKAGE | SPECIFICATION | BODY ];
Vezi Curs SGBD6 PL/SQL - Exemplul 6.19 (recompilare subprograme invalide)
Procedura ANALYZE_OBJECT
Permite colectarea statisticilor pentru obiecte de tip table, cluster sau index care vor fi
utilizate pentru optimizarea planului de execuie a comenzilor SQL care acceseaz
obiectele analizate.
o De exemplu, despre un tabel se pot obine urmtoarele informaii: numrul de linii,
numrul de blocuri, lungimea medie a unei linii, numrul de valori distincte ale
unei coloane, numrul elementelor null dintr-o coloan, distribuia datelor
(histograma) etc.
Sintaxa
ANALYZE_OBJECT (tip_obiect, nume_schema, nume_obiect,
metoda, numr_linii_estimate, procent, opiune_metoda,
nume_partiie);
o Metoda poate fi COMPUTE, ESTIMATE sau DELETE.
DELETE determin tergerea statisticilor din dicionarul datelor referitoare la
obiectul analizat.
COMPUTE calculeaz statisticile referitoare la un obiect analizat i le depune
n dicionarul datelor.
ESTIMATE estimeaz statistici.
o Dac nume_schema este null, atunci se presupune c este vorba de schema curent.
o Dac tip_obiect este diferit de table, index sau cluster, se declaneaz o eroare.
o Parametrul procent reprezint procentajul liniilor de estimat i este ignorat dac
este specificat numrul liniilor de estimat (numr_linii_estimate). Implicit, ultimele
patru argumente ale procedurii au valoarea null.
o Argumentul opiune_metoda poate avea forma:
[FOR TABLE] [FOR ALL INDEXES] [FOR ALL [INDEXED] COLUMNS] [SIZE n]
-
Sisteme de Gestiune a Bazelor de Date 7-21
Pentru metoda ESTIMATE trebuie s fie prezent una dintre aceste opiuni.
Instruciune echivalent SQL
ANALYZE TABLE | CLUSTER | INDEX
[nume_schema.]nume_obiect [metoda]
STATISTICS [SAMPLE n] [ROWS | PERCENT]]
Exemplul 7.21 vezi curs
-
Sisteme de Gestiune a Bazelor de Date 7-22
Bibliografie
1. Programare avansat n Oracle9i, I. Popescu, A. Alecu, L. Velcescu, G. Florea
(Mihai), Ed. Tehnic (2004)
2. Oracle Database PL/SQL Language Reference 11g Release 2, Oracle Online
Documentation (2012)
3. Oracle Database SQL Language Reference11g Release 2, Oracle Online
Documentation (2012)
4. Oracle Database 11g: PL/SQL Fundamentals, Student Guide, Oracle University
(2009)