Memorarea unui tip de data în baza de date:
In exemplele următoare se folosesc tipurile de dată descrise în continuare (memorate în baza de date):
create or replace type TPunct as object( -- un punct din plan x number, y number, order member function MaiMic(p TPunct) return number, -- punctele se ordoneaza de la stanga la dreapta si de jos in sus member function tochar(r number) return varchar2 -- furnizeaza un tag svg pentru afisarea punctului ca un cerc de raza r ); / Create or replace type body TPunct is order member function MaiMic(p TPunct) return number is begin if self.x<p.x then return -1; elsif self.x>p.x then return 1; elsif self.y<p.y then return -1; elsif self.y>p.y then return 1; else return 0; end if; end; -- final metoda member function tochar(r number) return varchar2 is begin return '<circle cx="' || to_char(x) || '" cy="' || to_char(y) || '" r="' || to_char(r) || '"/>'; end; -- final metoda end; -- final obiect
Exemplu de utilizare în iSQL*Plus
set serveroutput on Declare p1 TPunct:=TPunct(10,20); p2 TPunct:=TPunct(20,30); Begin dbms_output.enable; dbms_output.put_line('p1: ' || p1.tochar(3)); dbms_output.put_line('p2: ' || p2.tochar(3)); dbms_output.put_line('p1,p2:' || To_char(p1.MaiMic(p2))); dbms_output.put_line('p2,p1:' || To_char(p2.MaiMic(p1))); dbms_output.put_line('p1,p1:' || To_char(p1.MaiMic(p1))); end;
create or replace type TSegment as object( -- segment in plan e1 TPunct, -- prima extremitate a segmentului e2 TPunct, -- a doua extremitate a segmentului map member function Lungime return number, -- lungimea segmentului member function Unghi return number, -- unghiul cu axa Ox member function tochar return varchar2 -- furnizeaza un tag svg pentru afisare ); / create or replace type body TSegment is map member function Lungime return number is begin return sqrt((e1.x-e2.x)*(e1.x-e2.x)+(e1.y-e2.y)*(e1.y-e2.y)); end; -- final Lungime member function Unghi return number is -- unghiul cu axa Ox, intre 0 si 360 grade x number; y number; a number; begin x:=e2.x-e1.x; y:=e2.y-e1.y; a:=atan2(y,x); -- unghiul este in radiani a:=a*57.29578; -- 57.29578=180/3.1415926; -- atan2(y,x) are valori in (-pi, pi) -- atan2(1,1)=> 45 -- atan2(-1,1)=> -45, ar trebui 360-45=315 -- atan2(1,-1)=> 135 -- atan2(-1,-1)=> -135, ar trebui 180+45=360-135=225 -- test: -- select atan2(1,1)*57.29578, atan2(-1,-1)*57.29578, atan2(-1,1)*57.29578, atan2(1,-1)*57.29578 from dual; if y<0 then a:=a+360; end if; return a; end; -- final Unghi member function tochar return varchar2 is begin return '<line x1="' || to_char(e1.x) || '" y1="' || to_char(e1.y) || '" x2="' || to_char(e2.x) || '" y2="' || to_char(e2.y) || '"/>'; end; -- final tochar end; -- final obiect
Exemplu de utilizare în iSQL*Plus
set serveroutput on Declare s1 TSegment:=TSegment(TPunct(10,10),TPunct(100,100)); s2 TSegment:=TSegment(TPunct(10,20),TPunct(150,60)); s3 TSegment:=TSegment(TPunct(30,20),TPunct(150,100)); Begin dbms_output.enable; dbms_output.put_line('s1: ' || s1.tochar); dbms_output.put_line('s2: ' || s2.tochar); dbms_output.put_line('s3: ' || s3.tochar); dbms_output.put_line('s1.Lungime:' || to_char(s1.Lungime(), '999,999.99')); dbms_output.put_line('s2.Lungime:' || to_char(s2.Lungime(),'999,999.99')); dbms_output.put_line('s3.Lungime:' || to_char(s3.Lungime(),'999,999.99')); dbms_output.put_line('s1.Unghi:' || to_char(s1.Unghi(),'999,999.99')); dbms_output.put_line('s2.Unghi:' || to_char(s2.Unghi(),'999,999.99')); dbms_output.put_line('s3.Unghi:' || to_char(s3.Unghi(),'999,999.99')); end;
create or replace type TMultimeP is table of TPunct; -- multime (tabel) de puncte
create or replace type TMultimeS is table of TSegment; -- multime (tabel) de segmente
Pentru informaţii despre tipurile definite de utilizator se pot folosi view-urile sistem:
-- tipurile definite de utilizator select type_name,typecode,attributes,methods from user_types; -- atributele unui tip obiect dat select attr_name,attr_type_name,length,precision from user_type_attrs where type_name='STUDENT' order by attr_no; -- metodele unui tip obiect dat (si numarul de parametri pentru metode) select method_name,parameters from user_type_methods where type_name='STUDENT' order by method_no; -- parametrii unei metode select param_name,param_type_name from user_method_params where type_name='STUDENT' and method_name='TIPBURSA' order by param_no; -- specificatia unui tip obiect select text from user_source where name='PERSOANA' and type='TYPE' order by line; -- corpul obiectului (partea de implementare) select text from user_source where name='PERSOANA' and type='TYPE BODY' order by line; -- tipul elementelor unei colectii select elem_type_name from user_coll_types where type_name='TMULTIMEP';
Crearea unui tabel:
CREATE TABLE nume_tabel( def_coloana [, def_coloana]...[, proprietati_tabel] ) clauze;
nume_coloana tipScalar | nume_coloana tipObiect | nume_coloana tipColectieTabel | nume_coloana REF tipObiect"Clauze" (de la CREATE TABLE) poate fi o listă de elemente separate cu "," sau spaţiu de forma următoare, pentru fiecare coloană de tip colecţie:
NESTED TABLE nume_coloana STORE AS nume_tabelUn tabel inclus (ce memorează valorile unei coloane de tip colecţie) se poate gestiona numai prin intermediul tabelului părinte.
CREATE TABLE nume_tabel OF tipObiect
create table Puncte (cod number(4), punct TPunct); create table Segmente (cod number(4), segment TSegment);
insert into Puncte(cod, punct) values (1, TPunct(1,1)); insert into Puncte(cod, punct) values (2, TPunct(1,5)); insert into Puncte(cod, punct) values (3, TPunct(-1,1)); insert into Puncte(cod, punct) values (4, TPunct(1,-1)); insert into Puncte(cod, punct) values (5, TPunct(1,5)); insert into Segmente(cod, segment) values(1, TSegment(TPunct(1,1), TPunct(1,5))); insert into Segmente(cod, segment) values(2, TSegment(TPunct(-1,1), TPunct(1,-5))); insert into Segmente(cod, segment) values(3, TSegment(TPunct(1,-1), TPunct(11,5))); insert into Segmente(cod, segment) values(4, TSegment(TPunct(1,10), TPunct(1,15)));
select * from puncte; select * from puncte order by punct; select punct,count(*) as nr from puncte group by punct; select * from Segmente; select * from Segmente order by segment;
select cod, segment, s.segment.e1, s.segment.e1.x, s.segment.e2, s.segment.lungime(), s.segment.unghi() from segmente s;
create table MultPuncte (cod number(4), puncte TMultimeP) nested table puncte store as TabPuncte; create table MultSegmente (cod number(4), segmente TMultimeS) nested table segmente store as TabSegmente;
insert into MultPuncte (cod, puncte) values (1, TMultimeP(TPunct(1,1),TPunct(1,5),TPunct(-1,1),TPunct(1,-1))); insert into MultPuncte (cod, puncte) values (2, TMultimeP(TPunct(-1,-1),TPunct(10,5))); insert into MultPuncte (cod, puncte) values (3, TMultimeP(TPunct(0,0))); insert into MultPuncte (cod, puncte) values (4, TMultimeP()); insert into MultPuncte (cod) values (5); insert into MultSegmente Values (1,TMultimeS(TSegment(TPunct(0,0), TPunct(2,5)), TSegment(TPunct(1,0), TPunct(4,4)), TSegment(TPunct(0,1), TPunct(6,11)), TSegment(TPunct(0,-1), TPunct(6,-10)))); insert into MultSegmente Values (2,TMultimeS(TSegment(TPunct(10,0), TPunct(12,5)), TSegment(TPunct(11,0), TPunct(14,4)), TSegment(TPunct(10,1), TPunct(16,11)), TSegment(TPunct(10,-1), TPunct(16,-10)), TSegment(TPunct(2,1), TPunct(6,6)))); insert into MultSegmente Values (3,TMultimeS()); insert into MultSegmente Values (4,TMultimeS());
Valori posibile în tabelul MultPuncte:
cod | puncte | ||||||||||
1 |
|
||||||||||
2 |
|
||||||||||
3 |
|
||||||||||
4 |
|
||||||||||
5 |
select * from MultPuncte;
select puncte from MultPuncte;
Pentru ultima instructiune rezultatul va fi o multime de colectii:
TMultimeP(TPUNCT(1, 1), TPUNCT(1, 5), TPUNCT(-1, 1), TPUNCT(1, -1)) TMultimeP(TPUNCT(-1, -1), TPUNCT(10, 5)) TMultimeP(TPUNCT(0, 0)) TMultimeP()
O singură valoare pentru puncte se obţine astfel:
select puncte from MultPuncte where cod=1;
Această colecţie se poate folosi ca o sursă de înregistrări dacă se foloseşte funcţia
the sau funcţia table (începând cu versiunea 9 a sistemului Oracle).
Această funcţie converteşte colecţia într-un tabel, pentru a se putea efectua gestiunea datelor
cu instrucţiuni SQL.
table (select puncte from MultPuncte where cod=1)
Este necesar ca valoarea argumentului din funcţie să
existe, deci colecţia inclusă să fie construită, chiar dacă nu are înregistrări. Pentru valorile
introduse în exemplele de mai sus, linia cu "cod=5" are valoarea null pentru "puncte", deci
table (select puncte from MultPuncte where cod=5)
va furniza o eroare la instrucţiunile de gestiune a datelor incluse.
O eroare apare şi dacă argumentul acestor funcţii (table, the) conţine mai mult de un element.
Exemple:
insert into table(select puncte from MultPuncte where cod=1) values(10,13); insert into table(select puncte from MultPuncte where cod=1) values(TPunct(12,15)); update MultPuncte set puncte=TmultimeP() where cod=5; insert into table(select puncte from MultPuncte where cod=5) values(1,13); update MultPuncte set puncte=null where cod=5; insert into table(select segmente from MultSegmente where cod=3) values(TSegment(TPunct(3,3),TPunct(4,4))); insert into table(select segmente from MultSegmente where cod=3) values(TPunct(-3,-3),TPunct(-4,-4)); update table(select segmente from MultSegmente where cod=3) m set m.e1.x=m.e1.x+10 where m.e1.x < 0; insert into MultSegmente(cod) values(10); update MultSegmente set segmente=TMultimes() where cod=10; insert into table (select segmente from MultSegmente where cod=10) values(tsegment(Tpunct(1,0), TPunct(1,1)));
select * from table(select puncte from MultPuncte where cod=1); select x from table(select puncte from MultPuncte where cod=1); select e1, e2, s.lungime(), s.unghi() from table (select segmente from MultSegmente where cod=1) s order by 1; select count(*), sum(s.lungime()), min(e1), max(e1), max(TSegment(e1,e2)) from table (select segmente from MultSegmente where cod=1) s; -- numarul de segmente, suma lungimilor segmentelor, -- punctul minim, punctul maxim, segmentul cel mai mare -- din inregistrarea cu codul 1 select * from MultSegmente s; select count(*) from table (select segmente from MultSegmente where cod=1); select cod, (select count(*) from table (select segmente from MultSegmente where cod=s.cod) ) nr from MultSegmente s; -- pentru fiecare inregistrare din MultSegmente se cere: -- codul, numarul de segmente din colectia asociata
select * from MultSegmente;
se furnizează înregistrările din tabelul MultSegmente. In fiecare linie apare şi o colecţie "segmente".
Ea se poate folosi ca sursă de informaţie în clauza FROM dacă se utilizează funcţia TABLE.
Această colecţie se schimbă de la linie la linie, deci în FROM sursa este dinamică, depinde de linia curentă
din tabelul sursă. Putem avea instrucţiunea:
select a.cod,b.e1,b.e2 from MultSegmente a, Table(a.segmente) b;
Pentru o înregistrare din "a" se determină un tabel "Table(a.segmente) b" şi se face un
produs cartezian între aceste două surse (nu apare un operator de join între cele două surse).
CREATE TABLE nume_tabel OF tip_obiect
Exemple:
create table PuncteOb of TPunct; create table SegmenteOb of TSegment;
insert into PuncteOb values (TPunct(1,1)); insert into PuncteOb values (TPunct(1,5)); insert into PuncteOb values (TPunct(-1,1)); insert into PuncteOb values (TPunct(1,-1)); insert into SegmenteOb values (TSegment(TPunct(0,0), TPunct(2,5))); insert into SegmenteOb values (TSegment(TPunct(1,0), TPunct(4,4))); insert into SegmenteOb values (TSegment(TPunct(0,1), TPunct(6,11)));
select * from PuncteOb;
furnizează două coloane: X, Y (atributele obiectului TPunct), deci nu se furnizează obiecte.
select * from PuncteOb order by 1;
furnizează înregistrările ordonate după prima coloană: X
select * from SegmenteOb; select * from SegmenteOb order by 1;
Pentru a obţine obiecte, se poate folosi un constructor pentru tipul obiect care generează înregistrările din tabel:
select TPunct(x,y) from PuncteOb; select TSegment(E1,E2) from SegmenteOb; select TSegment(E1,E2), E1, E2 from SegmenteOb order by 1;
Un efect identic (obţinerea de obiecte) se poate obţine prin folosirea funcţiei "value":
select value(p) from PuncteOb p; select value(s) from SegmenteOb s;
Operatorii multiset folosesc doi operanzi de tip colecţie (pot fi rezultatul unei instrucţiuni
select dintr-un tabel inclus) cu elemente de acelaşi tip şi furnizează un rezultat de tip colecţie.
Sintaxa:
operand MULTISET {UNION | INTERSECT | EXCEPT} [{ALL | DISTINCT }] operand
Cu ALL se iau toate elementele din rezultatul operatorului cerut: reuniune, intersecţie, diferenţă, iar cu DISTINCT
se iau numai elementele distincte.
Exemplu cu bloc PL/SQL
set serveroutput on DECLARE TYPE colectie IS TABLE OF VARCHAR2(10); c1 colectie ; c2 colectie ; c3 colectie ; Procedure ListaElemente(c in colectie) IS i INTEGER; BEGIN FOR i IN 1..c.COUNT LOOP DBMS_OUTPUT.PUT(c(i) || ' '); END LOOP; END; BEGIN c1:= colectie ('A', 'B', 'C', 'A', 'B'); c2:= colectie ('B', 'C', 'D', 'B'); c3:= c1 MULTISET UNION ALL c2; DBMS_OUTPUT.enable; DBMS_OUTPUT.PUT('UNION ALL: '); ListaElemente(c3); DBMS_OUTPUT.PUT_LINE(' '); c3:= c1 MULTISET UNION DISTINCT c2; DBMS_OUTPUT.PUT('UNION DISTINCT: '); ListaElemente(c3); DBMS_OUTPUT.PUT_LINE(' '); c3:= c1 MULTISET INTERSECT ALL c2; DBMS_OUTPUT.PUT('INTERSECT: '); ListaElemente(c3); DBMS_OUTPUT.PUT_LINE(' '); c3:= c1 MULTISET INTERSECT DISTINCT c2; DBMS_OUTPUT.PUT('INTERSECT DISTINCT: '); ListaElemente(c3); DBMS_OUTPUT.PUT_LINE(' '); c3:= c1 MULTISET EXCEPT ALL c2; DBMS_OUTPUT.PUT('EXCEPT ALL: '); ListaElemente(c3); DBMS_OUTPUT.PUT_LINE(' '); c3:= c1 MULTISET EXCEPT DISTINCT c2; DBMS_OUTPUT.PUT('EXCEPT DISTINCT: '); ListaElemente(c3); DBMS_OUTPUT.PUT_LINE(' '); END;
Exemplu:
select puncte from multpuncte where cod=1; select (select puncte from multpuncte where cod=2) as x from dual; -- in select poate apare si o colectie select (select puncte from multpuncte where cod=1) multiset union all (select puncte from multpuncte where cod=2) from dual;
Funcţia cast permite:
CAST(expresie AS tip_valoare)
CAST(MULTISET(instr_select) AS tip_colectie)
Exemplu:
select punct from puncte; -- s-au folosit instructiunile: -- create table Puncte (cod number(4), punct TPunct); -- create or replace type TMultimeP is table of TPunct; select cast(multiset(select punct from puncte) as TMultimeP) from dual;
-- instructiunile de creare folosite: -- create or replace type TMultimeS is table of TSegment; -- create table MultSegmente (cod number(4), segmente TMultimeS);
Prin:
select cod, segmente from MultSegmente s;
se obţin toate înregistrările din tabelul MultSegmente.
Pentru a transforma valoarea coloanei "segmente" (dintr-o anumită înregistrare) într-un tabel,
de unde să se poată selecta o extremitate, se poate folosi:
table(select segmente from MultSegmente where cod=1)
iar din aceasta sursă de date se poate selecta "E1", care pentru o singură înregistrare este:
select a.e1 from table(select segmente from MultSegmente where cod=1) a;
iar pentru întreaga sursă:
select s.cod, (select a.e1 from table(select segmente from MultSegmente where cod=s.cod) a) from MultSegmente s;
care generează eroare deoarece al doilea element selectat nu are o singură valoare
(se obţine eroarea: ORA-01427: single-row subquery returns more than one row)
Deoarece rezultatul:
(select a.e1 from table(select segmente from MultSegmente where cod=s.cod) a) (*)
nu este o expresie, acest rezultat nu poate să fie inclus într-o instrucţiune select, deci în acest caz trebuie o conversie la o colecţie:
cast(multiset(*) As TMultimeP)
In final se obţine instrucţiunea:
select s.cod, cast(multiset(select a.e1 from table(select segmente from MultSegmente where cod=s.cod) a) as tmultimep) as lista from MultSegmente s;
Pentru a determina ambele extremităţi ale segmentelor, pentru o singură înregistrare (cu cod=1) din tabelul MultSegmente
trebuie să folosim instrucţiunea următoare:
select e1 from table(select segmente from MultSegmente where cod=1) union select e2 from table(select segmente from MultSegmente where cod=1);
în locul subinterogării cu aliasul "a" de mai sus:
select s.cod, cast(multiset( select e1 from table(select segmente from MultSegmente where cod=1) union select e2 from table(select segmente from MultSegmente where cod=1) ) as tmultimep) as lista from MultSegmente s;
Inainte de versiunea Oracle 12c această operaţie nu se poate realiza deoarece "multiset"
nu accepta reuniri de mulţimi (apare eroarea "ORA-03001: unimplemented feature").
Pentru a rezolva această problemă, în versiunile precedente la 12c, vom crea un view:
create or replace view VExtremitati as select cod, a.e1 as e from MultSegmente s, table(s.segmente) a union select cod, a.e2 from MultSegmente s, table(s.segmente) a;
In final, rezolvarea problemei se poate face prin instrucţiunea:
select s.cod, cast(multiset(select e from VExtremitati where cod=s.cod) as TMultimeP) as lista from (select cod from MultSegmente) s;
O referinţă este un pointer (adresă) la un obiect memorat într-un tabel de obiecte.
O coloană de acest tip de dată se declară prin:
nume_coloana REF Tip_obiect
Valoarea unei astfel de coloane va fi un identificator de obiect dintr-un tabel.
Pentru crearea unei referinţe la un obiect (valoare care va fi memorată în tabel) se poate folosi funcţia:
REF(p)
unde p este obiectul la care se doreşte obţinerea referinţei.
Cu funcţia:
DEREF(s)
se obţine obiectul precizat de referinţa s.
Exemple de folosire:
create table SegmenteR (e1 ref TPunct, e2 ref TPunct); insert into SegmenteR (e1) select ref(p) from puncteob p where p.x=-1; insert into SegmenteR select ref(p),ref(q) from puncteob p, puncteob q where p.x < q.y; select * from SegmenteR; select deref(s.e1),deref(s.e2) from SegmenteR s;
Obs. Dacă se schimbă obiectele sursă la care se face referire într-un tabel cu coloane ref, atunci se văd schimbările prin folosirea funcţiei deref.