Proiectarea bazelor de date
# 15
Adrian Runceanuwww.runceanu.ro/adrian
2016
PL/SQLTipul de date LOB (Large Object)
Curs 15
Tipul de date LOB
(Large Object)
14.12.2016Proiectarea bazelor de date 2
Cuprins
LARGE OBJECT (LOB)
1. Folosirea tipului de date Large
Object (LOB)
2. Gestionarea BFILE
3. Inregistrari definite de utilizatori
14.12.2016Proiectarea bazelor de date 3
1. Folosirea tipului de date LARGE
OBJECT (LOB)
Vom studia:
Descrierea tipurilor de date LOB si folosirea
acestora
Diferentierea LOB-urilor interne si externe
Compararea tipurilor de date LONG si LOB
Crearea si pastrarea tipurilor de date LOB
Mutarea datelor de la LONG la LOB
14.12.2016Proiectarea bazelor de date 4
Sa presupunem ca dorim sa adaugam
coloane noi in tabela emp pentru a stoca
pentru un angajat: ◦ fotografia de pe insigna
◦ un raport anual de evaluare a performantei
◦ si un clip video al angajatului de la ultimul picnic
al companiei
Aceste noi date necesita un spatiu de
stocare foarte mare in baza de date, mult
mai mult decat puteti specifica cu
VARCHAR2 sau coloana RAW.
Avem nevoie de tipuri de date suplimentare
pentru coloane pentru a stoca valori mai
mari de date.
14.12.2016Proiectarea bazelor de date 5
Coloane noi pentru emp
Departamentul HR (de angajari)
doreste sa adauge o coloana care
contine toate evaluarile anuale de
performanta
◦ Stocata in format text, ar trebui sa fie
cautata intr-un mod similar cu Google.
◦ Sa presupunem ca o pagina este
redactata cu 100×100 caractere (10 KB)
si pot fi permise pana la 100 pagini (1 MB)
14.12.2016Proiectarea bazelor de date 6
Coloane noi pentru emp
Departamentul de securitate doreste
sa adauge o coloana care contine o
fotografie pentru insignele de
identificare
◦ Dimensiunile sunt mici – corespunzator
pasaportului sau permisului de conducere
◦ Fisierele JPEG care contin fotografiile
sunt fiecare de aproximativ 15 KB
14.12.2016Proiectarea bazelor de date 7
Coloane noi pentru emp
Departamentul publicitate a luat
videoclipuri de la ultima ceremonie de
acordare a premiilor si doreste sa
adauge scurte clipuri video cu
angajatii care primesc premii.
◦ Acestea sunt stocate pe DVD-uri.
Majoritatea sunt sub 5 minute.
14.12.2016Proiectarea bazelor de date 8
Aveti nevoie de tipuri de date LOB
pentru coloane
Problema◦ In SQL cea mai mare coloana de tip
character are 4096 de bytes.
◦ Nu este nici un tip de date specific pentru
MP3, JPEG, EXE etc.
◦ Ce se intampla daca este necesara
stocarea unui obiect mai mare de 4 KB?
14.12.2016Proiectarea bazelor de date 9
Solutia
◦ Tipurile Large Object (LOB) abordeaza
toate aceste probleme
◦ Acestea pot stoca ORICE de orice tip
◦ Un singur camp LOB intr-o tabela poate fi
pana la 4 GB in versiunea 9i si pana la
128 TB in versiunea 10g
14.12.2016Proiectarea bazelor de date 10
Cele doua modalitati de a stoca LOB
Modalitatea veche
◦ Exista doua tipuri depasite de date: LONG
si LONG RAW
◦ Ar trebui ca aceste tipuri de date sa nu
mai fie folosite.
14.12.2016Proiectarea bazelor de date 11
LOB-urile se prezinta in 4 tipuri:
1. CLOB (Character Large Objects)
2. BLOB (Binary Large Objects)
3. BFILE (Binary Files)
4. NCLOB (National Character Set Large
Objects)
14.12.2016Proiectarea bazelor de date 12
Noua modalitate
1. CLOB (Character Large Objects) – cum ar fi CV-uri,
articole de text, fisiere de cod sursa
2. BLOB (Binary Large Objects) – cum ar fi sunet
(MP3), fotografii (JPEG, BMP), formate proprietate
(PDF, DOC, XLS) si executabile (EXE, DDL)
3. BFILE (Binary Files) – exact ca si BLOB dar
stocarea se face in afara bazei de date, de multe ori
pe suporturi speciale (CD, DVD, HD-DVD)
4. NCLOB (National Character Set Large Objects) –
utilizat cu alfabete multioctet
14.12.2016Proiectarea bazelor de date 13
Noua modalitate
Modalitatea veche
LONG◦ Pana la 2 GB
◦ Inlocuit de CLOB
LONG RAW◦ Pana la 2 GB
◦ Inlocuit de BLOB
si BFILE
14.12.2016Proiectarea bazelor de date 14
CLOB◦ Pana la 4 GB sau 128 TB
◦ Inlocuieste LONG
BLOB si BFILE◦ Pana la 4 GB sau 128 TB
◦ Inlocuiesc LONG RAW
◦ In interiorul sau in afara
bazei de date
Noua modalitate
Avantaje
O tabela poate contine doar o coloana
LONG sau LONG RAW, dar oricate coloane
LOB este nevoie.
Datele CLOB si BLOB sunt stocate in baza
de date (LOB-uri interne).
BFILE-urile sunt stocate in afara bazei de
date (LOB-uri externe) in fisiere speciale.
14.12.2016Proiectarea bazelor de date 15
Noua modalitate
Convertirea LONG la CLOB
Puteti converti coloanele de tip LONG
la CLOB (si cele de tip LONG RAW la
BLOB) folosind ALTER TABLE.
ALTER TABLE table_name
MODIFY (long_col_name {CLOB |
BLOB});
14.12.2016Proiectarea bazelor de date 16
Convertirea LONG la CLOB
De exemplu, pentru a converti coloana
RESUMES a tabelei emp de la LONG
la CLOB, scrieti:
ALTER TABLE emp
MODIFY (resumes CLOB );
14.12.2016Proiectarea bazelor de date 17
Convertirea LONG la CLOB
De asemenea, puteti converti explicit
valorile datelor folosind functiile SQL:
TO_CLOB converteste LONG,
VARCHAR2 si CHAR la CLOB
TO_BLOB converteste LONG RAW si
RAW la BLOB
TO_CHAR converteste CLOB la CHAR
si VARCHAR2
14.12.2016Proiectarea bazelor de date 18
Convertirea LONG la CLOB
Serverul Oracle poate si va executa
conversii implicite daca este necesar,
dar este mai bine sa realizam conversii
explicite.
Exemple:
Adaugarea unei coloane CLOB
tabelei emp
ALTER TABLE emp
ADD (resumes CLOB);
14.12.2016Proiectarea bazelor de date 19
Convertirea LONG la CLOB
Completarea coloanei
DECLARE
v_value VARCHAR2 := 'This is a value';
BEGIN
UPDATE emp
SET resumes = TO_CLOB(v_value); -- explicit
UPDATE emp
SET resumes = v_value; -- implicit
END;
14.12.2016Proiectarea bazelor de date 20
1. Coloana CLOB
Doar text
◦ Fara fonturi, fara scris aldin, fara scris italic, nici
un fel de formatare
◦ Util pentru stocarea scripturilor: XML
HTML
DDL
PL/SQL
si a altor coduri sursa pentru programe
◦ Pot folosi toate functiile incorporate SQL pentru
caractere, cum ar fi SUBSTR, LENGTH
14.12.2016Proiectarea bazelor de date 21
2. Coloana BLOB
Poate stoca absolut orice (de exemplu
fisiere ZIP, EXE, DLL)
Cateva tipuri de fisiere, cum ar fi PDF,
BMP, GIF, JPEG, MP3 si WAV (de
obicei cele cunoscute de browser-ele
Web) pot fi afisate de Oracle Academy
Express.
14.12.2016Proiectarea bazelor de date 22
Cum si unde sunt stocate datele LOB?
Spre deosebire de alte tipuri de date in care
valoarea coloanei este stocata in linie ca
parte a randului de date principal, o valoare
a coloanei LOB este stocata intr-o zona
separata a bazei de date cu un pointer catre
aceasta de la randul principal al tabelei.
Spunem ca valorile datelor LOB sunt stocate
out-of-line (in afara liniei).
Pointerul din randul principal este numit
localizator.
14.12.2016Proiectarea bazelor de date 23
Adaugarea unei coloane LOB unei
tabele
ALTER TABLE emp ADD (annual_evals
CLOB);
ALTER TABLE emp ADD (badge_photo
BLOB);
Cand o coloana LOB este creata ca parte
a unei instructiuni CREATE TABLE sau
ALTER TABLE, coloana nu contine date.
Aceasta inseamna ca datele din coloana
nu exista si localizatorul este NULL.
14.12.2016Proiectarea bazelor de date 24
Initializarea unei coloane LOB
Deoarece o coloana LOB este formata
din doua parti (localizatorul care indica
catre valoare), trebuie sa initializati
localizatorul inainte sa introduceti
valorile datelor:
UPDATE emp
SET annual_evals = EMPTY_CLOB(),
badge_photo = EMPTY_BLOB();
14.12.2016Proiectarea bazelor de date 25
EMPTY_CLOB si EMPTY_BLOB sunt functii
SQL incorporate cum ar fi UPPER,
TO_CHAR, etc. cu exceptia faptului ca ca
pot fi folosite doar in instructiunile DML
deoarece acestea modifica tabela.
Functiile aloca spatiu initial in alta parte a
bazei de date pentru a pastra valoarea
datelor si actualizeaza localizatorul pentru a
indica catre acest spatiu.
14.12.2016Proiectarea bazelor de date 26
Initializarea unei coloane LOB
Umplerea unei coloane CLOB cu date
Odata ce o coloana LOB a fost initializata,
puteti introduce valorile datelor folosind
instructiunile standard DML:
UPDATE emp
SET annual_evals = 'Evaluation Date: 14
September 2011. Performance Rating:
Good ... '
WHERE employee_id = 100;
Aceasta metoda in doua etape (initializarea
si apoi popularea) este necesara deoarece
datele sunt stocate out-of-line si nu le puteti
accesa deloc pana nu este creat un pointer
care sa lege coloana cu datele (initializarea
localizatorului).14.12.2016
Proiectarea bazelor de date 27
Citirea datelor CLOB din tabela
Puteti utiliza SELECT pentru o coloana
CLOB ca pentru orice alta coloana:
SELECT annual_evals
FROM emp
WHERE employee_id = 100;
14.12.2016Proiectarea bazelor de date 28
Citirea datelor CLOB din tabela
Dar aceste valori pot fi foarte mari.
Citirea intregii valori CLOB de 4 GB
necesita mult timp si foloseste multa
memorie.
Si poate oricum doriti sa vedeti doar o
parte a valorii:
SELECT SUBSTR(annual_evals, 2000, 1000)
FROM emp
WHERE employee_id = 100;
14.12.2016Proiectarea bazelor de date 29
Actualizarea datelor CLOB
Nu pot fi folosite functiile SQL, cum ar fi
un SUBSTR intr-o instructiune
UPDATE:
UPDATE emp
SET substr(annual_evals,2001,8) =
'NEW TEXT'
WHERE employee_id = 100;
14.12.2016Proiectarea bazelor de date 30
In schimb, trebuie sa folositi pachetul PL/SQL DBMS_LOB.
Si acest lucru nu-l puteti face direct intr-o instructiune SQL
DML.
Puteti sa faceti aceasta doar din interiorul unui bloc
PL/SQL.
DECLARE
v_lobloc CLOB; -- this will store the LOB locator
v_new_text VARCHAR2(32767) := 'NEW TEXT';
v_amount INTEGER;
v_offset INTEGER;
BEGIN
SELECT annual_evals INTO v_lobloc
FROM emp
WHERE employee_id = 100 FOR UPDATE;
v_offset := DBMS_LOB.GETLENGTH(v_lobloc) + 2;
v_amount := LENGTH(v_new_text);
DBMS_LOB.WRITE(v_lobloc,v_amount,v_offset,v_new
_text);
END;14.12.2016
Proiectarea bazelor de date 31
Umplerea (popularea unei coloane
CLOB) cu o valoare mare folosind
DBMS_LOB
Deja ati invatat ca puteti umple o
coloana CLOB cu o instructiune DML
UPDATE:
UPDATE emp
SET annual_evals = 'Evaluation Date:
14 September 2011. Performance
Rating: Good ... '
WHERE employee_id = 100;
14.12.2016Proiectarea bazelor de date 32
Dar ce se intampla daca valoarea este
mare?
Un literal de tip character intr-o instructiune
SQL nu poate avea o dimensiune de 4 GB.
Din nou puteti folosi DBMS_LOB pentru a
incarca valoarea cate o bucata o data.
DECLARE
v_lobloc CLOB; -- this will store the LOB
locator
v_text VARCHAR2(32767);
v_length INTEGER;
v_offset INTEGER;
14.12.2016Proiectarea bazelor de date 33
BEGIN
SELECT annual_evals INTO v_lobloc
FROM emp
WHERE employee_id = 100 FOR UPDATE;
FOR i IN 1..3 LOOP
v_text := 'The next piece of text
number ' || i;
v_offset :=
DBMS_LOB.GETLENGTH(v_lobloc)+ 2;
v_length := LENGTH(v_text);
DBMS_LOB.WRITE(v_lobloc,v_length,v_off
set,v_text);
END LOOP;
END;
14.12.2016Proiectarea bazelor de date 34
Citirea datelor din coloana BLOB folosind DBMS_LOB
Datele BLOB nu pot fi afisate in Oracle Academy
Express, dar puteti vedea ca datele exista cautand si
afisand lungimea lor.
DECLARE
CURSOR country_curs IS
SELECT country_id, country_name, flag
FROM wf_countries
WHERE country_name LIKE 'A%';
v_length NUMBER;
BEGIN
FOR country_rec IN country_curs LOOP
v_length :=
DBMS_LOB.GETLENGTH(country_rec.flag);
DBMS_OUTPUT.PUT_LINE(country_rec.country_id ||' '
||country_rec.country_name||' '||v_length);
END LOOP;
END;
14.12.2016Proiectarea bazelor de date 35
Se vor afisa urmatoarele:
672 Antarctica 0
20 Arab Republic of Egypt 489
297 Aruba 604
1268 Antiqua and Barbuda 769
54 Argentine Republic 1270
1264 Anguilla 1431
Statement processed.
14.12.2016Proiectarea bazelor de date 36
Pachetul DBMS_LOB
Ati vazut si utilizat cateva proceduri si functii
din pachetul DBMS_LOB, dar sunt mult mai
multe!
Sunt grupate in doua categorii:◦ Evoluatori – modifica valorile LOB (APPEND,
COPY, ERASE, TRIM, WRITE, FILEOPEN,
FILECLOSE)
◦ Observatori – citesc valorile LOB
(FILEGETNAME, GETLENGTH, READ, SUBSTR,
INSTR, FILEEXISTS, FILEISOPEN)
Unele dintre acestea (FILEEXISTS,
FILEOPEN, FILECLOSE, FILEGETNAME si
FILEISOPEN) pot fi folosite doar cu BFILE.14.12.2016
Proiectarea bazelor de date 37
LOB-uri temporare
Uneori este necesar sa actualizam
aproape toata sau chiar toata valoarea LOB
care este foarte mare.
Pentru a face acest lucru mai rapid, putem
utiliza un LOB temporar.
Un LOB temporar este un LOB a carui valoare
este stocata in memorie, nu in baza de date Aceasta valoare este de obicei o copie temporara a
unei valori LOB reale (permanente) stocate intr-o
coloana a tabelei
Modificarea LOB-ului temporar este mai rapida
deoarece realizarea modificarilor in memorie este este
intotdeauna mai rapida decat scrierea pe disk.
LOB-urile temporare pot fi CLOB sau BLOB, dar nu
BFILE
14.12.2016Proiectarea bazelor de date 38
LOB-uri temporare
Pentru a crea si a sterge LOB-urile
temporare sunt folosite doua
proceduri:
1. DBMS_LOB.CREATETEMPORARY
2. DBMS_LOB.FREETEMPORARY
14.12.2016Proiectarea bazelor de date 39
Exemplu:
DECLARE
v_clob CLOB;
BEGIN
/* Citirea localizatorului pentru valoarea LOB a tabelei */
SELECT annual_evals INTO v_clob
FROM emp
WHERE employee_id = 100;
/* Asocierea de memorie pentru LOB-ul temporar si
copierea valorii */
DBMS_LOB.CREATETEMPORARY(v_clob);
/* Acum putem folosi valoarea LOB-ului temporar value
cu DBMS_LOB.READ, DBMS_LOB.WRITE etc. */
…
/* Stergerea LOB-ului temporar la sfarsit eliberand
memoria */
DBMS_LOB.FREETEMPORARY(v_clob);
END;
14.12.2016Proiectarea bazelor de date 40
Cuprins
LARGE OBJECT (LOB)
1. Folosirea tipului de date Large
Object (LOB)
2. Gestionarea BFILE
3. Inregistrari definite de utilizatori
14.12.2016Proiectarea bazelor de date 41
2. Gestionarea BFILE
BFILE este un tip de date LOB a carui valoare
este stocata in afara bazei de date.
Este necesar sa studiem noi modalitati de creare
si gestionare.
Un BFILE este asemanator unui CLOB sau BLOB
cu exceptia faptului ca valoarea este stocata in
afara bazei de date, intr-un fisier special.
Baza de date retine un pointer catre fisierul
extern.
Datele pot fi text (ca la CLOB) sau multimedia (ca
la BLOB).
Fisierul extern poate fi pe un disk obisnuit de
calculator sau pe CD sau DVD.14.12.2016
Proiectarea bazelor de date 42
Deoarece datele BFILE sunt stocate in afara
bazei de date:
Pot fi citite dar nu pot fi modificate; prin
urmare acestea trebuie sa fie create in afara
Oracle
Nu pot fi acordate asupra lor privilegii
obisnuite ale bazei de date
Nu se pot folosi asupra lor instructiuni SQL
obisnuite
Toate accesarile asupra datelor se fac prin
intermediul pachetului DBMS_LOB
14.12.2016Proiectarea bazelor de date 43
Prin ce difera BFILE de CLOB si BLOB?
Cand folosim un BFILE?
In cazul in care media exista deja (de
exemplu CD sau DVD), atunci de ce
sa mai facem recopierea in baza de
date (intr-o coloana BLOB)?
Daca media este deja de tipul read-
only (de exemplu CD sau DVD),
atunci folosirea unui BFILE doar in
citire nu este o problema.
14.12.2016Proiectarea bazelor de date 44
Cand sa nu folosim un BFILE?
Daca nu se potriveste! Pentru Oracle
9i si 10g limita este 4 GB
Cat de mare este un DVD? Pana la
4,7 GB.
Cat de mare este un Blu-ray sau HD-
DVD? Intre 15 si 50 GB.
14.12.2016Proiectarea bazelor de date 45
Un nou obiect al bazei de date:
DIRECTORUL
Aveti nevoie de o modalitate de specificare a
directoarelor (folderelor) sistemului de
operare care contin datele BFILE si de
asemenea de controlare a privilegilor: ce
utilizatori Oracle au permisiunea de a citi
datele BFILE.
Pentru a face acest lucru creati un
DIRECTOR.
Un DIRECTOR este un pointer de la baza de
date catre directorul sistemului de operare
(folder Windows) unde sunt stocate datele
BFILE.14.12.2016
Proiectarea bazelor de date 46
Creati un director pentru a indica catre
o locatie externa unde sunt stocate
datele BFILE, apoi permiteti tuturor
sa-l foloseasca.
CREATE DIRECTORY movie_dir AS
'c:\mymovies';
GRANT READ ON DIRECTORY
movie_dir TO PUBLIC;
14.12.2016Proiectarea bazelor de date 47
Crearea si gestionarea directoarelor
Crearea si gestionarea directoarelor
Sa presupunem ca mai tarziu mutati
fisierele video intr-o locatie diferita.
Trebuie sa actualizati pointerul
director.
ALTER DIRECTORY movie_dir AS
'c:\latermovies';
14.12.2016Proiectarea bazelor de date 48
Spre deosebire de majoritatea obiectelor
din baza de date, directoarele nu apartin
nici unei scheme, deci nu este nici o
vizualizare USER_DIRECTORIES in
dictionar.
In loc de aceasta, folositi
ALL_DIRECTORIES.
SELECT directory_name, directory_path
FROM all_directories;14.12.2016
Proiectarea bazelor de date 49
Vizualizarea directoarelor in Data Dictionary
Adaugati coloana ca pe orice alta coloana.
ALTER TABLE emp ADD (movie BFILE);
Apoi o completati cu o valoare localizator
care indica catre un fisier anume intr-un
director specific, folosind folosind pointerul
director creat anterior.
Acest lucru se face utilizand functia
incorporata BFILENAME intr-un bloc
PL/SQL.
14.12.2016Proiectarea bazelor de date 50
Adaugarea si umplerea unei coloane BFILE pentru o tabela
DECLARE
v_locator BFILE; --1
BEGIN
v_locator := BFILENAME('MOVIE_DIR','titanic.avi'); --2
IF DBMS_LOB.FILEEXISTS(v_locator) = 1 THEN --3
DBMS_LOB.FILEOPEN(v_locator);
UPDATE emp SET movie = v_locator --4
WHERE employee_id = 100;
DBMS_LOB.FILECLOSE(v_locator);
ELSE
RAISE_APPLICATION_ERROR (-20210,'This BFILE
does not exist');
END IF;
END;
14.12.2016Proiectarea bazelor de date 51
Citirea localizatorului si valorilor datelor BFILE
Puteti citi valoarea localizatorului folosind
DBMS_LOB.FILEGETNAME:
DECLARE
v_locator BFILE;
v_directory VARCHAR2(30);
v_filename VARCHAR2(50);
BEGIN
SELECT movie INTO v_locator
FROM emp
WHERE employee_id = 100;
DBMS_LOB.FILEGETNAME(v_locator,v_directory,v_fil
ename);
DBMS_OUTPUT.PUT_LINE(v_directory||'
'||v_filename);
END;
Valorile datelor se citesc folosind DBMS_LOB.READ.
Unele tipuri de fisiere cum ar fi BLOB nu pot fi afisate in
fereastra de comenzi SQL din Application Express.14.12.2016
Proiectarea bazelor de date 52
Cuprins
LARGE OBJECT (LOB)
1. Folosirea tipului de date Large
Object (LOB)
2. Gestionarea BFILE
3. Inregistrari definite de utilizatori
14.12.2016Proiectarea bazelor de date 53
Stiti deja sa declarati si sa folositi structurile
record PL/SQL care corespund datelor
preluate de cursor utilizand atributul
%ROWTYPE.
Dar daca doriti sa creati si sa folositi o
structura inregistrare care corespunde unui
rand dintr-o tabela, unei vizualizari, unui
join a catorva tabele, mai degraba decat un
cursor?
Sau care nu corespunde nici unui obiect in
baza de date?
14.12.2016Proiectarea bazelor de date 54
Un scenariu problema
Tabela emp contine 11 coloane:
EMPLOYEE_ID, FIRST_NAME,…,
MANAGER_ID, DEPARTMENT_ID.
Aveti nevoie sa scrieti urmatorul cod in
subprogramul PL/SQL:
SELECT *
FROM emp
Nu este nevoie sa declarati si sa folositi un
cursor.
Cate variabile scalare este nevoie sa
declarati pentru a pastra valorile din
coloane?14.12.2016
Proiectarea bazelor de date 55
CREATE OR REPLACE PROCEDURE query_one_emp
(p_emp_id IN emp.employee_id%TYPE)
IS
v_employee_id emp.employee_id%TYPE;
v_first_name emp.first_name%TYPE;
... -- seven more scalar variables here
v_manager_id emp.manager_id%TYPE;
v_department_id emp.department_id%TYPE;
BEGIN
SELECT employee_id, first_name, ..., department_id
INTO v_employee_id, v_first_name, ...,
v_department_id
FROM emp
WHERE employee_id = p_emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN ...;
END;
14.12.2016Proiectarea bazelor de date 56
Cum putem furniza rezultatele mediului apelant?
CREATE OR REPLACE PROCEDURE
query_one_emp
(p_emp_id IN emp.employee_id%TYPE,
p_first_name OUT emp.first_name%TYPE,
... – seven more OUT parameters here
p_manager_id OUT emp.manager_id%TYPE,
p_department_id OUT emp.department_id%TYPE)
IS
v_employee_id emp.employee_id%TYPE;
v_first_name emp.first_name%TYPE;
…
Din fericire nu este nevoie sa faceti toate acestea, ci
declarati si folositi o inregistrare PL/SQL.
14.12.2016Proiectarea bazelor de date 57
CREATE OR REPLACE PROCEDURE query_one_emp
(p_emp_id IN emp.employee_id%TYPE,
p_emp_record OUT emp%ROWTYPE)
IS
BEGIN
SELECT * INTO p_emp_record
FROM emp
WHERE employee_id = p_emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Nothing selected.');
END;
Puteti folosi %ROWTYPE cu tabele la fel cum puteti
cu cursorii.
Si daca o coloana se adauga sau se elimina intr-o
tabela, nici o schimbare nu este necesara la procedura.
14.12.2016Proiectarea bazelor de date 58
Inregistrari PL/SQL
O inregistrare PL/SQL este un tip de date
compus format dintr-un grup de elemente in
relatie unele cu altele, stocate ca si campuri,
fiecare cu propriul nume si propriul tip de
date.
Puteti referi intreaga inregistrare prin nume
si/sau individual campurile prin numele
acestora.
Prin folosirea %ROWTYPE se declara
implicit o inregistrare ale carei campuri se
potrivesc coloanelor corespunzatoare prin
nume si tip. 14.12.2016
Proiectarea bazelor de date 59
Puteti referi campurile individuale
prefixand denumirea campului cu
numele inregistrarii.
... IF p_emp_record.salary > 25000 THEN
RAISE_APPLICATION_ERROR(-20104,
'This employee earns too much!');
END IF; ...
14.12.2016Proiectarea bazelor de date 60
Definirea propriilor voastre inregistrari
Ce se intampla daca procedura voastra
extrage din mai multe tabele?
Puteti declara propriile structuri de tip record
care contin ce campuri doriti.
Inregistrarile PL/SQL:
Trebuie sa contina una sau mai multe
componente (campuri) de orice tip scalar sau
compus
Nu sunt asemanatoare randurilor dintr-o
tabela a bazei de date
Li se pot atribui valori initiale si pot fi definite
ca NOT NULL
O inregistrare poate fi parte componenta a
altei inregistrari (inregistrari imbricate)14.12.2016
Proiectarea bazelor de date 61
Crearea unei inregistrari PL/SQL definite
de utilizator
O structura de tip inregistrare este un tip de
date compus.
Declarati mai intai tipul si apoi declarati una
sau mai multe variabile de acest tip.
TYPE type_name IS RECORD
(field_declaration[,field_declaration]...);
identifier type_name;
field_declaration poate fi de orice tip de date
PL/SQL inclusiv %TYPE, %ROWTYPE si
RECORD.
14.12.2016Proiectarea bazelor de date 62
Exemplu
TYPE person_type IS RECORD
(first_name emp.first_name%TYPE,
last_name emp.last_name%TYPE,
gender VARCHAR2(6));
TYPE employee_type IS RECORD
(job_id VARCHAR2(10),
salary number(8,2),
person_data person_type);
person_rec person_type;
employee_rec employee_type;
…
IF person_rec.last_name ... END IF;
employee_rec.person_data.last_name := ...;
14.12.2016Proiectarea bazelor de date 63
Unde pot fi declarate si folosite tipurile de date si
inregistrarile?
Sunt variabile compuse si pot fi declarate oriunde se
declara in mod obisnuit variabilele scalare: ◦ in blocuri anonime
◦ proceduri
◦ functii
◦ specificatii de pachet (globale)
◦ corpuri de pachet (locale)
◦ declansatoare, etc.
Domeniul lor de aplicare si vizibilitatea respecta
aceleasi reguli ca si pentru variabilele scalare.
De exemplu puteti declara un tip in specificatia unui
pachet.
Inregistrarile bazate pe acel tip pot fi declarate si
folosite oriunde in pachet si in mediul apelant.
14.12.2016Proiectarea bazelor de date 64
Întrebări?
14.12.2016Proiectarea bazelor de date 65
Top Related