oracle-notiuni generale

www.cartiaz.ro – Carti si articole online gratuite de la A la Z

ORACLE - NOŢIUNI GENERALE

Oracle nu este un program, ci o suită de programe şi conţine un DBMS (DataBase Management System), serverul Oracle.

Din cadrul mulţimii aplicaţiilor Oracle vom utiliza pentru început SQL*Plus, un instrument care permite o utilizare directă şi interactivă a limbajului SQL pentru a accesa serverul Oracle.

Observaţie: Oracle NU este Case Sensitive – nu face diferenţă între litere mici şi majuscule.

Instrucţiuni ORACLE

Principalele instrucţiuni (comenzi ORACLE) pot fi grupate astfel:

Instrucţiuni pentru manipularea datelorSELECT – selecţia datelor din tabeleINSERT – adăugarea unei înregistrări într-o tabelăDELETE – ştergerea înregistrărilor dintr-o tabelăUPDATE – modificarea valorilor unor câmpuri dintr-o tabelă

Instrucţiuni pentru definirea bazei de dateCREATE TABLE – crearea structurii unei tabele şi adăugarea acesteia în baza de dateDROP TABLE – ştergerea fizică a unei tabele dintr-o bază de dateALTER TABLE – modificarea structurii unei tabeleCREATE VIEW – crearea unei tabele virtualeDROP VIEW – ştergerea unei tabele virtuale

Instrucţiuni pentru accesul la baza de dateGRANT – acordarea unor drepturi pentru utilizatoriREVOKE – revocarea unor drepturi acordate unor utilizatori

Instrucţiuni specifice tranzacţiilorCOMMIT – marchează sfârşitul unei tranzacţiiROLLBACK – abandonează tranzacţia în curs

CREAREA TABELELOR

O tabelă poate avea până la maxim 254 de coloane (câmpuri, atribute). Comanda care permite crearea unei tabele este:

CREATE TABLE <nume_tabela> (<camp1> <tip1> (<lung1> [,<zec1>]) [DEFAULT <val1>]

[<regula1_camp1> […]], …)

CONSTRÂNGERI (REGULI)

Regulile, pentru fi recunoscute (în special pentru fi activate, dezactivate) sunt prefixate astfel:pk_ PRIMARY KEYun_ UNIQUE

1

http://www.cartiaz.ro/


nn_ NOT NULLck_ CHECKfk_ FOREIGN KEY

1. REGULI DE VALIDARE

La nivel de câmp – constrângeri aplicate unei singure coloane

[CONSTRAINT <nume>]NOT NULLUNIQUE|PRIMARY KEY|REFERENCES <tabela parinte> (<campi>)CHECK <conditie>

La nivel de tabel – constrângeri la nivel de mai multe coloane (câmpuri)

[CONSTRAINT <nume>]UNIQUE|PRIMARY KEY|(<campi>) [, <campj>…])FOREIGN KEY (<campi [,…]>) REFERENCES <tabela parinte> (<campk>)

2. INTEGRITATE REFERENŢIALĂ

Se referă la operaţiile principale efectuate asupra unei tabele din cele 2 aflate în legătură: Adăugarea unei înregistrări în tabelul copil; Ştergerea unei înregistrări din tabela părinte; Modificarea unor date din tabela părinte.

TIPURI DE DATE ÎN ORACLE

CHAR(n) şiruri de caractere de lungime fixă n, maxim 2000 octeţi. Ca să ajungă la dimensiunea specificată, întotdeauna este completat în partea din dreapta cu spaţii, deci poate consuma din memorie;

• Exemplu: CHAR(15)

VARCHAR2(n) şiruri de caractere de lungime variabilă până la 4000 octeţi, nu se completează cu spaţii nimic, se stochează doar cât a fost completat din şir;

• Exemplu: VARCHAR2(10)

LONG şiruri de caractere de lungime variabilă de până la 2 GB; NUMBER(n) numere întregi cu până la 38 de cifre NUMBER (n,m) numere reale în virgulă fixă; pot conţine date de tip întreg sau real,

n = numărul total de cifre, m= numărul de cifre la dreapta punctului zecimal. Valorile maxime pentru n =38, m de la −84 până la +127.

• Exemple: NUMBER(8), NUMBER(5,2)Observaţie: NUMBER(5,2) nu poate conţine ceva mai mare decât 999.99 fără a apărea o eroare. Tipurile de date derivate din NUMBER sunt INT[EGER], DEC[IMAL], SMALLINT şi REAL.

NUMBER numere reale în virgulă mobilă; DATE date calendaristice. Formatul predefinit este: DD-MMM-YY.

• Exemple: ’13-OCT-94’, ’07-JAN-98’

2



RAW(n) stochează date binare de lungime variabilă de maxim 2000 octeţi utilizate pentru obiecte create cu alte aplicaţii

LOB(large object) obiecte de dimensiuni mari – tehnologia OLE.

Observaţii: Se preferă tipul NUMBER, deşi pentru unele câmpuri, de exemplu un cod client, este

preferabilă utilizarea tipului INTEGER. În Oracle nu există tipul de câmpuri Logical (doar în PL/SQL se poate declara o variabilă

ca fiind de tip Boolean). Aceasta poate fi simulată totuşi, utilizându-se fie CHAR(1) – utilizându-se caracterele T, F – fie NUMBER(1) – utilizându-se valorile 1, 0.

Atâta timp cât nu există reguli care să restricţioneze valorile posibile ale unui atribut, acesta poate avea valoarea specială de NULL. Această valoare este diferită de numărul 0 şi de şirul nul ‘’.

UNIQUE, în ORACLE specifică faptul că nu pot exista două valori ale aceluiaşi câmp. Dacă nu este specificată condiţia NOT NULL pentru o coloană, valoarea NULL a

atributului este permisă iar constrângerea UNIQUE nu este violată. Atenţie, faţă de constrângerea UNIQUE, un câmp pe care este creată PRIMARY KEY (o

cheie primară) nu permite valoarea de NULL.

Pentru a crea o tabelă trebuie vute în vedere următoarele: Care sunt atributele fiecărei înregistrări? De ce tip de date vom alege câmpurile? Care câmp va fi ales pentru crea o cheie primară? Care coloane permit (sau nu) valori NULL? Care coloane permit (sau nu) duplicări ale valorilor? Există valori predefinite pentru anumite coloane care permit valori NULL? Ce restricţii vor fi setate pentru fiecare câmp în parte?

Exemplu:

DROP TABLE facturi;DROP TABLE clienti;

CREATE TABLE clienti (Codcl NUMBER (4) CONSTRAINT pk_clienti_codcl PRIMARY KEYCONSTRAINT ck_clienti_codcl CHECK (codcl>1000),dencl VARCHAR2 (20) CONSTRAINT ck_clienti_dencl CHECK (SUBSTR(dencl,1,1)=UPPER(SUBSTR(dencl,1,1))),localitatea VARCHAR2(30) CONSTRAINT nn_clienti_localit NOT NULL);

CREATE TABLE facturi (nrfact NUMBER (8) CONSTRAINT pk_facturi_nrfact PRIMARY KEYCONSTRAINT ck_facturi_nrfact CHECK (nrfact>=10000000 AND nrfact<=99999999),datfact DATE DEFAULT sysdate,codcl NUMBER (4) CONSTRAINT ck_facturi_codcl CHECK (codcl>1000) CONSTRAINT fk_facturi_clienti REFERENCES clienti (codcl), observatii VARCHAR2(50));

Observaţii:1. Declararea unei chei străine se realizează prin clauza REFERENCES (se creează relaţia

între cele două tabele).2. Se pot folosi operatorii logici de AND, OR.3. Se pot folosi funcţiile UPPER, INITCAP, LTRIM, STR, SUBSTR.

LOWER(şir)- transformă caracterele şirului în litere mici;

3



UPPER(şir) - transformă caracterele şirului în majuscule; INITCAP(şir) –transformă primul caracter al fiecărui cuvânt din şir în majusculă ; CONCAT(şir1,şir2) – concatenează cele două şiruri; e echivalent cu operatorul de

concatenare(||); SUBSTR(şir,m[,n]) – extrage din şirul de caractere un subşir de lungime m

începând de la poziţia n; LENGTH(şir) – returnează lungimea unui şir de caractere; INSTR(şir1,şir2[,n[,m]]) – returnează poziţia celei de-a m-a apariţii a lui şir2 în

şir1 începând cu poziţia n (implicit m=n=1), iar dacă nu se găseşte întoarce valoarea 0; REPLACE(şir,a[,b]) – înlocuieşte în şirul de caractere secvenţa a prin secvenţa b,

iar dacă lipseşte b, atunci are loc ştergerea lui a; LTRIM(şir) / RTRIM(şir) – şterge spaţiile libere din stânga şirului de caractere,

respectiv din dreapta lui; LPAD(şir1,n,şir2) / RPAD(şir1,n,şir2) – şir1 este completat la stânga, respectiv la

dreapta, până la lungimea n, prin şir2; SOUNDEX(şir) – reprezentarea fonetică a şirului de caractere. ABS(n) – valoarea absolută a numărului; CEIL(n) – cel mai mic întreg mai mare sau egal cu n; FLOOR(n) – cel mai mare întreg mai mic sau egal cu n; MOD(m,n) – restul împărţirii lui m prin n; SQRT(m) – rădăcină pătrată din m (null, dacă m<0); ROUND(n[,m]) – numărul n este rotunjit la m poziţii zecimale; TRUNC(n[,m]) – numărul n este trunchiat la m poziţii zecimale; LN(x) – implementează funcţia logaritmică ln(x)(logaritm natural); EXP(x) – implementează funcţia exponenţială; POWER(m,n) - implementează funcţia putere (m la puterea n); SIGN(n) - implementează funcţia semn asociată lui n; GREATEST(m,n) – cel mai mare dintre numerele m şi n; LEAST(m,n) – cel mai mic dintre numerele m şi n;

Exemple: LOWER(‘Curs SQL’) -> curs sql UPPER(‘Curs SQL’) -> CURS SQL INITCASE(‘SQL’) -> Sql CONCAT('Buna', 'Dimineata') -> BunaDimineata SUBSTR('Caractere',1,3) -> Car LENGTH('Sir') -> 3 INSTR('Complex', 'p') -> 4 LPAD(sal,10,'*') -> ******5000 ROUND(45.926, 2) -> 45.93 TRUNC(45.926, 2) -> 45.92 MOD(1600, 300) -> 100

4. Se poate folosi operatorul IN în cazul unei condiţii CHECK, (incluziune într-o mulţime):Exemplu:sex CHAR(1) DEFAULT ‘B’

CONSTRAINT ck_sex CHECK (sex IN (‘F’,’B’))Sau:

4



regiune VARCHAR2(15) DEFAULT 'Banat'CONSTRAINT ck_regiune CHECK (regiune IN ('Banat', 'Transilvania', 'Dobrogea', 'Oltenia', 'Muntenia', 'Moldova'))

5. Pentru date calendaristice, pentru a transforma un şir de caractere într-o dată calendaristică vom utiliza funcţia TO_DATE.

Exemplu:Datadoc>=TO_DATE(’01/01/2006’, ’Dd/Mmm/yyyy’)

6. Pentru diferite modalităţi de scriere a unui câmp folosind sau nu majuscule se foloseşte regula de validare CHECK:

Majuscule, pentru câmpul Nume:nume VARCHAR2(20) CONSTRAINT ck_nume CHECK (nume=RTRIM(UPPER(nume))) Prima literă majusculă, restul litere mici, pentru câmpul Prenume:prenume VARCHAR2(20) CONSTRAINT ck_prenume

CHECK (prenume=LTRIM(INITCAP(prenume)))

Pentru a rezolva problema unicităţii valorilor câmpului pe care s-a declarat un index de tip Primary Key se pot folosi secvenţele.

O secvenţă este un obiect ORACLE care generează numere întregi după un model specificat. Secvenţele de numere sunt utilizate se obicei pentru câmpurile pe care sunt setaţi indecşi de tip primar key sau unique. Comportarea implicită a unei secvenţe este incrementarea valorii curente cu 1, pentru a trece la valoarea următoare. Vom crea în continuare o secvenţă care va fi utilizată mai târziu pentru a adăuga înregistrări în tabela Carti.

CREATE TABLE carti (idC INTEGER CONSTRAINT pk_carti_idc PRIMARY KEY,......);CREATE SEQUENCE carti_seq;

SELECT carti_seq.CURRVAL FROM DUAL;SELECT carti_seq.NEXTVAL FROM DUAL;

INSERT INTO carti VALUES(carti_seq.NEXTVAL, 'Oliver Twist', 'Charles Dickens', '12-SEP-1839');

Un alt exemplu:CREATE SEQUENCE seq_marca INCREMENT BY 1MINVALUE 1010 MAXVALUE 5555 NOCYCLE NOCACHE ORDER;

CREATE TABLE PERSONAL (MARCA INTEGER CONSTRAINT PK_PERSONAL_MARCA PRIMARY KEY CONSTRAINT NN_PERSONAL_MARCA NOT NULL,NUMEPREN VARCHAR2(40) CONSTRAINT UN_PERSONAL_NUMEPREN UNIQUE CONSTRAINT NN_PERSONAL_NUMEPREN NOT NULL,COMPART VARCHAR2(5) DEFAULT 'PROD' CONSTRAINT NN_PERSONAL_COMPART NOT NULL,DATASV DATE DEFAULT SYSDATE,SALARORAR NUMBER(16,2) DEFAULT 4.5,SALARORARCO NUMBER(16,2) DEFAULT 4,COLABORATOR CHAR(1) DEFAULT 'N' CONSTRAINT NN_PERSONAL_COLABORATOR NOT NULL);

INSERT INTO personal (marca, numepren,compart, datasv,salarorar,salarorarco) VALUES (seq_marca.NextVal,'Marin', 'Prod', date'2007-01-12', 3.4,2.5);

INSERT INTO personal (marca, numepren, compart, datasv, salarorar, salarorarco) VALUES (seq_marca.NextVal,'Pop', 'Ion', date'2007-11-12', 6.7,2.1);

INSERT INTO personal (marca, numepren, compart, datasv, salarorar, salarorarco) VALUES (seq_marca.NextVal,'Popescu', 'Diana', date'2008-03-18', 6.9,1.7);

5



Pentru a vizualiza toate secvenţele:SELECT * FROM user_sequences;

Pentru a şterge o secvenţă:drop sequence seq_marca;

6


NOŢIUNI GENERALE

Oracle nu este un program, ci o suită de programe şi conţine un DBMS (DataBase Management System), serverul Oracle.

Din cadrul mulţimii aplicaţiilor Oracle vom utiliza pentru început SQL*Plus, un instrument care permite o utilizare directă şi interactivă a limbajului SQL pentru a accesa serverul Oracle.

Observaţie: Oracle NU este Case Sensitive – nu face diferenţă între litere mici şi majuscule.

Instrucţiuni ORACLE

Principalele instrucţiuni (comenzi ORACLE) pot fi grupate astfel:

Instrucţiuni pentru manipularea datelorSELECT – selecţia datelor din tabeleINSERT – adăugarea unei înregistrări într-o tabelăDELETE – ştergerea înregistrărilor dintr-o tabelăUPDATE – modificarea valorilor unor câmpuri dintr-o tabelă

Instrucţiuni pentru definirea bazei de dateCREATE TABLE – crearea structurii unei tabele şi adăugarea acesteia în baza de dateDROP TABLE – ştergerea fizică a unei tabele dintr-o bază de dateALTER TABLE – modificarea structurii unei tabeleCREATE VIEW – crearea unei tabele virtualeDROP VIEW – ştergerea unei tabele virtuale

Instrucţiuni pentru accesul la baza de dateGRANT – acordarea unor drepturi pentru utilizatoriREVOKE – revocarea unor drepturi acordate unor utilizatori

Instrucţiuni specifice tranzacţiilorCOMMIT – marchează sfârşitul unei tranzacţiiROLLBACK – abandonează tranzacţia în curs

CREAREA TABELELOR

O tabelă poate avea până la maxim 254 de coloane (câmpuri, atribute). Comanda care permite crearea unei tabele este:

CREATE TABLE <nume_tabela> (<camp1> <tip1> (<lung1> [,<zec1>]) [DEFAULT <val1>]

[<regula1_camp1> […]], …)

CONSTRÂNGERI (REGULI)

Regulile, pentru fi recunoscute (în special pentru fi activate, dezactivate) sunt prefixate astfel:pk_ PRIMARY KEYun_ UNIQUEnn_ NOT NULLck_ CHECK

7

fk_ FOREIGN KEY

1. REGULI DE VALIDARE

La nivel de câmp – constrângeri aplicate unei singure coloane

[CONSTRAINT <nume>]NOT NULLUNIQUE|PRIMARY KEY|REFERENCES <tabela parinte> (<campi>)CHECK <conditie>

La nivel de tabel – constrângeri la nivel de mai multe coloane (câmpuri)

[CONSTRAINT <nume>]UNIQUE|PRIMARY KEY|(<campi>) [, <campj>…])FOREIGN KEY (<campi [,…]>) REFERENCES <tabela parinte> (<campk>)

2. INTEGRITATE REFERENŢIALĂ

Se referă la operaţiile principale efectuate asupra unei tabele din cele 2 aflate în legătură: Adăugarea unei înregistrări în tabelul copil; Ştergerea unei înregistrări din tabela părinte; Modificarea unor date din tabela părinte.

TIPURI DE DATE ÎN ORACLE

CHAR(n) şiruri de caractere de lungime fixă n, maxim 2000 octeţi. Ca să ajungă la dimensiunea specificată, întotdeauna este completat în partea din dreapta cu spaţii, deci poate consuma din memorie;

• Exemplu: CHAR(15)

VARCHAR2(n) şiruri de caractere de lungime variabilă până la 4000 octeţi, nu se completează cu spaţii nimic, se stochează doar cât a fost completat din şir;

• Exemplu: VARCHAR2(10)

LONG şiruri de caractere de lungime variabilă de până la 2 GB; NUMBER(n) numere întregi cu până la 38 de cifre NUMBER (n,m) numere reale în virgulă fixă; pot conţine date de tip întreg sau real,

n = numărul total de cifre, m= numărul de cifre la dreapta punctului zecimal. Valorile maxime pentru n =38, m de la −84 până la +127.

• Exemple: NUMBER(8), NUMBER(5,2)Observaţie: NUMBER(5,2) nu poate conţine ceva mai mare decât 999.99 fără a apărea o eroare. Tipurile de date derivate din NUMBER sunt INT[EGER], DEC[IMAL], SMALLINT şi REAL.

NUMBER numere reale în virgulă mobilă; DATE date calendaristice. Formatul predefinit este: DD-MMM-YY.

• Exemple: ’13-OCT-94’, ’07-JAN-98’ RAW(n) stochează date binare de lungime variabilă de maxim 2000 octeţi

utilizate pentru obiecte create cu alte aplicaţii LOB(large object) obiecte de dimensiuni mari – tehnologia OLE.

8

Observaţii: Se preferă tipul NUMBER, deşi pentru unele câmpuri, de exemplu un cod client, este

preferabilă utilizarea tipului INTEGER. În Oracle nu există tipul de câmpuri Logical (doar în PL/SQL se poate declara o variabilă

ca fiind de tip Boolean). Aceasta poate fi simulată totuşi, utilizându-se fie CHAR(1) – utilizându-se caracterele T, F – fie NUMBER(1) – utilizându-se valorile 1, 0.

Atâta timp cât nu există reguli care să restricţioneze valorile posibile ale unui atribut, acesta poate avea valoarea specială de NULL. Această valoare este diferită de numărul 0 şi de şirul nul ‘’.

UNIQUE, în ORACLE specifică faptul că nu pot exista două valori ale aceluiaşi câmp. Dacă nu este specificată condiţia NOT NULL pentru o coloană, valoarea NULL a

atributului este permisă iar constrângerea UNIQUE nu este violată. Atenţie, faţă de constrângerea UNIQUE, un câmp pe care este creată PRIMARY KEY (o

cheie primară) nu permite valoarea de NULL.

Pentru a crea o tabelă trebuie vute în vedere următoarele: Care sunt atributele fiecărei înregistrări? De ce tip de date vom alege câmpurile? Care câmp va fi ales pentru crea o cheie primară? Care coloane permit (sau nu) valori NULL? Care coloane permit (sau nu) duplicări ale valorilor? Există valori predefinite pentru anumite coloane care permit valori NULL? Ce restricţii vor fi setate pentru fiecare câmp în parte?

Exemplu:

DROP TABLE facturi;DROP TABLE clienti;

CREATE TABLE clienti (Codcl NUMBER (4) CONSTRAINT pk_clienti_codcl PRIMARY KEYCONSTRAINT ck_clienti_codcl CHECK (codcl>1000),dencl VARCHAR2 (20) CONSTRAINT ck_clienti_dencl CHECK (SUBSTR(dencl,1,1)=UPPER(SUBSTR(dencl,1,1))),localitatea VARCHAR2(30) CONSTRAINT nn_clienti_localit NOT NULL);

CREATE TABLE facturi (nrfact NUMBER (8) CONSTRAINT pk_facturi_nrfact PRIMARY KEYCONSTRAINT ck_facturi_nrfact CHECK (nrfact>=10000000 AND nrfact<=99999999),datfact DATE DEFAULT sysdate,codcl NUMBER (4) CONSTRAINT ck_facturi_codcl CHECK (codcl>1000) CONSTRAINT fk_facturi_clienti REFERENCES clienti (codcl), observatii VARCHAR2(50));

Observaţii:1. Declararea unei chei străine se realizează prin clauza REFERENCES (se creează relaţia

între cele două tabele).2. Se pot folosi operatorii logici de AND, OR.3. Se pot folosi funcţiile UPPER, INITCAP, LTRIM, STR, SUBSTR.

LOWER(şir)- transformă caracterele şirului în litere mici; UPPER(şir) - transformă caracterele şirului în majuscule; INITCAP(şir) –transformă primul caracter al fiecărui cuvânt din şir în majusculă ; CONCAT(şir1,şir2) – concatenează cele două şiruri; e echivalent cu operatorul de

concatenare(||); SUBSTR(şir,m[,n]) – extrage din şirul de caractere un subşir de lungime m

începând de la poziţia n;

9

LENGTH(şir) – returnează lungimea unui şir de caractere; INSTR(şir1,şir2[,n[,m]]) – returnează poziţia celei de-a m-a apariţii a lui şir2 în

şir1 începând cu poziţia n (implicit m=n=1), iar dacă nu se găseşte întoarce valoarea 0; REPLACE(şir,a[,b]) – înlocuieşte în şirul de caractere secvenţa a prin secvenţa b,

iar dacă lipseşte b, atunci are loc ştergerea lui a; LTRIM(şir) / RTRIM(şir) – şterge spaţiile libere din stânga şirului de caractere,

respectiv din dreapta lui; LPAD(şir1,n,şir2) / RPAD(şir1,n,şir2) – şir1 este completat la stânga, respectiv la

dreapta, până la lungimea n, prin şir2; SOUNDEX(şir) – reprezentarea fonetică a şirului de caractere. ABS(n) – valoarea absolută a numărului; CEIL(n) – cel mai mic întreg mai mare sau egal cu n; FLOOR(n) – cel mai mare întreg mai mic sau egal cu n; MOD(m,n) – restul împărţirii lui m prin n; SQRT(m) – rădăcină pătrată din m (null, dacă m<0); ROUND(n[,m]) – numărul n este rotunjit la m poziţii zecimale; TRUNC(n[,m]) – numărul n este trunchiat la m poziţii zecimale; LN(x) – implementează funcţia logaritmică ln(x)(logaritm natural); EXP(x) – implementează funcţia exponenţială; POWER(m,n) - implementează funcţia putere (m la puterea n); SIGN(n) - implementează funcţia semn asociată lui n; GREATEST(m,n) – cel mai mare dintre numerele m şi n; LEAST(m,n) – cel mai mic dintre numerele m şi n;

Exemple: LOWER(‘Curs SQL’) -> curs sql UPPER(‘Curs SQL’) -> CURS SQL INITCASE(‘SQL’) -> Sql CONCAT('Buna', 'Dimineata') -> BunaDimineata SUBSTR('Caractere',1,3) -> Car LENGTH('Sir') -> 3 INSTR('Complex', 'p') -> 4 LPAD(sal,10,'*') -> ******5000 ROUND(45.926, 2) -> 45.93 TRUNC(45.926, 2) -> 45.92 MOD(1600, 300) -> 100

4. Se poate folosi operatorul IN în cazul unei condiţii CHECK, (incluziune într-o mulţime):Exemplu:sex CHAR(1) DEFAULT ‘B’

CONSTRAINT ck_sex CHECK (sex IN (‘F’,’B’))Sau:

regiune VARCHAR2(15) DEFAULT 'Banat'CONSTRAINT ck_regiune CHECK (regiune IN ('Banat', 'Transilvania', 'Dobrogea', 'Oltenia', 'Muntenia', 'Moldova'))

5. Pentru date calendaristice, pentru a transforma un şir de caractere într-o dată calendaristică vom utiliza funcţia TO_DATE.

Exemplu:Datadoc>=TO_DATE(’01/01/2006’, ’Dd/Mmm/yyyy’)

6. Pentru diferite modalităţi de scriere a unui câmp folosind sau nu majuscule se foloseşte regula de validare CHECK:

10

Majuscule, pentru câmpul Nume:nume VARCHAR2(20) CONSTRAINT ck_nume CHECK (nume=RTRIM(UPPER(nume))) Prima literă majusculă, restul litere mici, pentru câmpul Prenume:prenume VARCHAR2(20) CONSTRAINT ck_prenume

CHECK (prenume=LTRIM(INITCAP(prenume)))

Pentru a rezolva problema unicităţii valorilor câmpului pe care s-a declarat un index de tip Primary Key se pot folosi secvenţele.

O secvenţă este un obiect ORACLE care generează numere întregi după un model specificat. Secvenţele de numere sunt utilizate se obicei pentru câmpurile pe care sunt setaţi indecşi de tip primar key sau unique. Comportarea implicită a unei secvenţe este incrementarea valorii curente cu 1, pentru a trece la valoarea următoare. Vom crea în continuare o secvenţă care va fi utilizată mai târziu pentru a adăuga înregistrări în tabela Carti.

CREATE TABLE carti (idC INTEGER CONSTRAINT pk_carti_idc PRIMARY KEY,......);CREATE SEQUENCE carti_seq;

SELECT carti_seq.CURRVAL FROM DUAL;SELECT carti_seq.NEXTVAL FROM DUAL;

INSERT INTO carti VALUES(carti_seq.NEXTVAL, 'Oliver Twist', 'Charles Dickens', '12-SEP-1839');

Un alt exemplu:CREATE SEQUENCE seq_marca INCREMENT BY 1MINVALUE 1010 MAXVALUE 5555 NOCYCLE NOCACHE ORDER;

CREATE TABLE PERSONAL (MARCA INTEGER CONSTRAINT PK_PERSONAL_MARCA PRIMARY KEY CONSTRAINT NN_PERSONAL_MARCA NOT NULL,NUMEPREN VARCHAR2(40) CONSTRAINT UN_PERSONAL_NUMEPREN UNIQUE CONSTRAINT NN_PERSONAL_NUMEPREN NOT NULL,COMPART VARCHAR2(5) DEFAULT 'PROD' CONSTRAINT NN_PERSONAL_COMPART NOT NULL,DATASV DATE DEFAULT SYSDATE,SALARORAR NUMBER(16,2) DEFAULT 4.5,SALARORARCO NUMBER(16,2) DEFAULT 4,COLABORATOR CHAR(1) DEFAULT 'N' CONSTRAINT NN_PERSONAL_COLABORATOR NOT NULL);

INSERT INTO personal (marca, numepren,compart, datasv,salarorar,salarorarco) VALUES (seq_marca.NextVal,'Marin', 'Prod', date'2007-01-12', 3.4,2.5);

INSERT INTO personal (marca, numepren, compart, datasv, salarorar, salarorarco) VALUES (seq_marca.NextVal,'Pop', 'Ion', date'2007-11-12', 6.7,2.1);

INSERT INTO personal (marca, numepren, compart, datasv, salarorar, salarorarco) VALUES (seq_marca.NextVal,'Popescu', 'Diana', date'2008-03-18', 6.9,1.7);

Pentru a vizualiza toate secvenţele:SELECT * FROM user_sequences;

Pentru a şterge o secvenţă:DROP SEQUENCE seq_marca;

TEMĂ:

Să se scrie secvenţa de program pentru crearea bazei de date Bibliotecă din laboratorul anterior.

11

Introducere în OLAP: Analize unidimensionale - ROLLUP

În cele ce urmează vom prezenta câteva din cele mai importante funcţii analitice, care extind nucleul SQL către probleme legate destul de strâns de ceea ce îndeobşte este cunoscut sub titulatura depozite de date (Data Warehouses). Agregarea simultană a datelor după mai mulţi parametrii (dimensiuni) însoţită de determinarea subtotalurilor aferente face obiectul aşa-numitei analize multidimensionale. Aceasta se bazează pe utilizarea funcţiilor analitice dedicate procesării analitice on-line (On Line Analytical Processing – OLAP) şi care au fost introduse în Oracle începând cu versiunea 8i a acestuia.

Pentru ilustrarea acestor funcţii utilizăm tabela VINZĂRI_CĂRŢI care, pentru o editură, conţine informaţii despre facturile emise - cărţile vândute librăriilor şi/sau distribuitorilor de carte şi presă. Pentru simplificarea discuţiei, tabela este denormalizată, iar singurul element care ne interesează despre fiecare carte este ISBN-ul.

CREATE TABLE vinzari_carti ( NrFact DECIMAL(6) NOT NULL, DataFact DATE NOT NULL, Client VARCHAR(15), Loc VARCHAR(15), Jud CHAR(2), ISBN CHAR(14), Cantit DECIMAL (6), PretUn DECIMAL (8), ValTotala DECIMAL (14) );

Introducerea datelor în tabelă:

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Pop Ion', 'Timisoara', 'TM', '973-585-547-x', 3, 45);



INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Popescu Maria', 'Lugoj', 'TM', '973-585-234-x', 3, 33);



INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values

(123456,date'2008-03-03', 'Ana Mara', 'Jimbolia', 'TM', '973-585-547-x', 13, 45);

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Tara Edgar', 'Jimbolia', 'TM', '973-487-547-x', 11, 15);

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Meca Vasile', 'Jimbolia', 'TM', '973-585-547-x', 200, 45);

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Bec Lavinia', 'Deva', 'HD', '927-345-547-x', 100, 45);

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Werk Dacian', 'Deva', 'HD', '987-545-532-x', 400, 45);

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Miodrag Ana', 'Hunedoara', 'HD', '978-534-523-x', 500, 45);

INSERT INTO vinzari_carti (nrfact, datafact, client, loc, jud, isbn, cantit, pretun) values (123456,date'2008-03-03', 'Varga Teo', 'Hunedoara', 'HD', '922-123-549-x', 250, 45);

Update vinzari_carti set valtotala=cantit*pretun;

ROLLUPPrima funcţie luată în discuţie este ROLLUP. Se foloseşte pentru analiza datelor pe o singură dimensiune, dar la mai multe nivele de detaliere, atunci când în rapoarte sunt necesare subtotaluri incluse în alte subtotaluri, precum şi un total general. ROLLUP este echivalentul opţiunii SUBTOTALS din Excel şi permite afişarea subtotalurilor, a unor funcţii aplicate pe grupe de valori.

Problema căreia vrem să-i dăm răspuns se formulează astfel: Care este totalul vânzărilor de carte, pe clienţi, localităţi şi judeţe, precum şi totalul general?

Astfel de analize sunt cât se poate de frecvente şi necesare pentru toate tipurile de firme. În lipsa unui operator de genul ROLLUP, soluţia necesită reuniunea (UNION).

Subtotaluri obţinute prin reuniune:

Grupare după judeţ, localitate şi client, calcul valoare totală pe client:SELECT jud, loc, client, SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY jud, loc, client ;

Grupare după judeţ, localitate, calcul valoare totală pe localitate:SELECT jud, loc, 'Total localitate', SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY jud, loc ;

Grupare după judeţ, calcul valoare totală pe judeţ:SELECT jud, 'Total judet', ' ', SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY jud ;

Total general:SELECT ' ', 'TOTAL GENERAL ', ' ', SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti ;

Realizarea subtotalurilor cu ajutorul operatorului UNION:

SELECT jud, loc, client, SUM(ValTotala) AS Val_Vinzari FROM vinzari_carti GROUP BY jud, loc, client

UNION SELECT jud, loc, ' Subtotal LOCALITATE ' ||loc, SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY jud, loc

UNION SELECT jud, ' Subtotal JUDET '||jud, ' ', SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY jud

UNION SELECT ' ', 'TOTAL GENERAL ', ' ',

SUM(ValTotala) AS Val_Vinzari FROM vinzari_carti ;

Cu operatorul ROLLUP asemenea operaţiuni devin mult mai simple:

Afişarea totalurilor la nivel de localitate şi judeţ:SELECT jud, loc, SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY ROLLUP(jud, loc) ;

Afişarea totalurilor la nvel de localitate, judeţ şi client:SELECT jud, loc, client, SUM(ValTotala) AS Val_Vinzari

FROM vinzari_carti GROUP BY ROLLUP(jud, loc, client) ;

Se calculează valoarea funcţiei SUM pentru atributul cel mai din dreapta al ROLLUP-ului - Client (acesta se consideră a fi primul subtotal), apoi câte un subtotal pentru Loc, pentru Jud şi, în final, totalul general. În total sunt 3 + 1 = 4 nivele de (sub)totalizare, 3 pentru cele trei atribute plus totalul general. În cazul operatorului ROLLUP, un singur argument va determina calculul şi afişarea unui total general. Dacă se utilizează două argumente, primul calculează totalul general iar al doilea calculează şi afişează un subtotal pentru fiecare valoare distinctă a câmpului an. În cazul utilizării a trei argumente, primul determina calcularea unui total general (Grand Total), al doilea calculează media pentru fiecare valoare distinctă a câmpului an, iar al treilea efectuează un subtotal pentru fiecare combinaţie (an, grupă).

Combinarea funcţiilor ROLLUP şi GROUPINGPentru un plus de claritate, formulăm o interogare care să combine Rollup cu Grouping. Funcţia GROUPING are ca argument coloana de grupare, rezultatul întors fiind 1 dacă coloana respectivă este inclusă într-un grup de agregare superior, sau 0 pentru liniile normale (în afara subtotalurilor). Valoarea 1 întoarsă de funcţia GROUPING pentru un atribut indică un subtotal pentru acel atribut, relativ la atributele din stânga, în ordinea declarată în clauza ROLLUP.

SELECT jud, Loc, Client, SUM(ValTotala) AS Val_Vinzari, GROUPING (Jud) AS Grup_Jud, GROUPING (Loc) AS Grup_Loc, GROUPING (Client) AS Grup_Client FROM vinzari_carti GROUP BY ROLLUP(jud, loc, client) ORDER BY jud, loc, client ;

ROLLUP-uri parţiale

Este necesar uneori ca subtotalizarea atributelor de grupare să fie parţială: fie nu interesează totalul general, fie subtotalizarea este necesară numai pentru anumite atribute sau grupuri de atribute. Din datele existente în tabela VINZARI_CARTI se doreşte gruparea pe judeţe, localităţi, clienţi, dar şi pe cărţi (ISBN-uri). Subtotalul, însă, trebuie calculat numai la nivel client şi localitate.

SELECT NVL(jud,' ') AS Judet, CASE WHEN GROUPING(jud)=1 THEN 'total general '

ELSE CASE WHEN GROUPING(loc)=1 THEN 'Subtotal-judet'||jud

ELSE loc END END AS localitate,

CASE WHEN GROUPING(loc)=1 THEN ' 'ELSE CASE WHEN GROUPING(client) =1 THEN ' Subtotal loc. '||loc

ELSE client END END AS Client, CASE WHEN GROUPING(client)=1 THEN ' ' ELSE CASE WHEN GROUPING (ISBN)=1

THEN ' Subtotal -clientul '||client ELSE isbn END

END AS ISBN, SUM(ValTotala) AS Vinzari FROM VINZARI_CARTI GROUP BY jud,loc, ROLLUP(client,isbn) ;

Exerciţii:1. Să se creeze în ORACLE tabela studenţi şi să se insereze înregistrările din figură.2. Să se execute gruparea şi calculul mediei la nivel de an.3. Să se execute gruparea şi calculul mediei la nivel de grupă.4. Să se afişeze o medie generală a tuturor studenţilor.5. Să se execute gruparea şi calculul mediei la nivel de grupă şi an.6. Să se creeze subtotaluri folosind operatorul UNION, calculându-se media la nivel de an şi

media generală.7. Să se creeze subtotaluri folosind operatorul UNION, adăugându-se interogării anterioare

gruparea şi calcularea mediei la nivel de grupa.8. Să se scrie interogarea SQL folosind operatorul ROLLUP, criteriul de grupare fiind doar

anul.9. Să se scrie interogarea SQL folosind operatorul ROLLUP, criteriile de grupare fiind anul

şi grupa.10. Să se scrie interogarea SQL folosind operatorul ROLLUP, criteriile fiind anul, secţia şi

grupa.

oracle-notiuni generale

Documents