Baze Date Lab-Tot

Gheorghe Hazi

Baze de date. Lucrări practice 5

PREFAŢĂ

Un server de baze de date este cheia rezolvării problemelor de gestiune a informaţiilor. În general, un server trebuie sa asigure gestiunea unei cantităţi mari de date intr-un mediu utilizator în aşa fel ca mai mulţi utilizatori sa poată accesa concurent aceleaşi date. Această acţiune trebuie sa fie însoţită de o performanta crescută. Serverul de baze de date trebuie sa împiedice accesul neautorizat şi furnizează soluţii eficiente pentru refacerea in caz de defecţiune. Serverul Oracle furnizează soluţii efective si eficiente cu următoarele caracteristici: • Mediu client / server (procesare distribuita) pentru a lua toate avantajele

unui sistem dat sau a unei reţele, Oracle permite ca procesarea sa fie împărţită intre serverul de baze de date şi programul client. Calculatorul care rulează sistemul de gestiune a bazelor de date manipulează toate responsabilităţile serverului de baze de date in timp ce staţia de lucru rulează aplicaţii concentrate pe interpretarea şi afişarea datelor.

• Gestiunea bazelor de date mari şi a spaţiului - Oracle suportă baze de date mari, potenţial de dimensiuni de ordinul zeci-sute de terabyte. Pentru a face eficientă folosirea echipamentelor hardware costisitoare, permite control complet al folosirii spaţiului

• Mai mulţi utilizatori concurenţi ai bazei de date, Oracle suportă un număr mare de utilizatori concurenţi care execută o varietate de aplicaţii ce operează cu aceleaşi date. Aceasta minimizează conflictul dintre date si garantează concurenta datelor.

• Performantă înaltă la procesarea tranzacţiilor - Oracle menţine caracteristicile anterioare cu un mare grad de performanţă al întregului sistem. Utilizatorii bazei de date nu suferă scăderea performantelor de procesare.

• Înalta disponibilitate - în anumite locaţii Oracle lucrează 24 ore pe zi fără limitarea utilizării bazei de date. Căderea parţială a sistemului nu întrerupe utilizarea bazei de date

• Disponibilitatea controlului - Oracle poate controla selectiv disponibilitatea bazei de date la nivelul bazei de date sau la nivelul sub bazei. De exemplu: un administrator poate dezactiva folosirea unei aplicaţii specifice astfel ca datele aplicaţiei sa poată fi reîncărcate fără a afecta alte aplicaţii.

Gheorghe Hazi


• Deschidere, standard industrial - Oracle este deschis la standardele industriale acceptate pentru limbajul de acces la date, sistemul de operare, interfaţa utilizator, protocoalele de comunicaţie în reţea. Este un sistem deschis care protejează investiţia cumpărătorului.

Versiunea Oracle server 10 este certificata de US National Institute of Standard and Technology 100% impreuna cu standardul ANSI / ISO SQL92. Oracle satisface toate cerinţele standardului US Governement IS FIPS / 27-2. Oracle suporta standardul Simple Network Management Protocol (SNMP) pentru gestiunea sistemului. Acest protocol permite administrarea sistemelor heterogene cu o singură interfaţă administrator. • Gestionarea securităţii - pentru protecţia împotriva accesului neautorizat la

bazele de date si la utilizarea lor, Oracle furnizează o caracteristica de securitate pentru a limita şi monitoriza accesul la date. Aceasta caracteristica face gestiunea uşoară chiar daca proiectul de accesare a datelor este complex.

• Integritatea bazelor de date - Oracle introduce integritatea datelor, “reguli de lucru” care dictează standardele pentru datele acceptate. Ca rezultat, costul codificării în gestiunea căutării în mai multe baze de date este eliminat.

• Sistem distribuit - pentru reţele, medii distribuite, Oracle combină datele localizate fizic pe diferite calculatoare într-o bază de date logică ce poate fi accesata de toţi utilizatorii reţelei. Sistemele distribuite au acelaşi grad de transparenţă pentru utilizatori şi consistenţă a datelor ca şi a sistemelor distribuite, pe deasupra primeşte avantajele gestiunii bazelor de date locale. Oracle oferă opţiuni heterogene care permit utilizatorilor să acceseze transparent date din anumite baze de date non-Oracle.

• Portabilitate - softul Oracle este proiectat să lucreze sub diferite sisteme de operare. Aplicaţiile dezvoltate pentru Oracle pot fi portate pe oricare sistem de operare cu modificări puţine sau deloc.

• Compatibilitate - softul Oracle este compatibil cu standardele industriale, incluzând majoritatea sistemelor de operare standard. Aplicaţiile dezvoltate pentru Oracle pot fi folosite virtual în oricare sistem de operare cu modificări puţine sau deloc.

• Conectibilitate - Oracle permite diferitelor tipuri de calculatoare şi sisteme de operare să partajeze informaţiile în reţea.

• Mediu replicat - Oracle permite replicarea grupurilor de tabele şi a obiectelor în locaţii multiple.

Prezentul îndrumar se adresează studenţilor care urmează cursuri de

administrare a bazelor de date. Lucrarea cuprinde lucrări pentru configurarea şi administrarea bazei de date, dar şi iniţiere în dezvoltarea aplicaţiilor PL SQL.

Gheorghe Hazi


CUPRINS

LUCRAREA 1. INTERFEŢE DE ADMINISTRARE A BAZEI DE DATE ORACLE............................................................................................................ 11

1. Scopul lucrării .............................................................................................. 11

2. Instalarea clientului de Oracle ..................................................................... 11 2.1. Ştergerea unui client Oracle .................................................................. 11 2.2. Paşii de instalare .................................................................................... 11

3. Oracle Enterprise Manager (OEM).............................................................. 18

4. iSQL Plus ..................................................................................................... 19

LUCRAREA 2. ORACLE SQL PLUS............................................................ 21

1. Scopul lucrării .............................................................................................. 21

2. Lansarea SQL Plus şi conectarea la baza de date ........................................ 21

3. Comenzi SQL Plus....................................................................................... 21

4. Elaborarea unui script SQL Plus pentru obţinerea unui raport.................... 29

LUCRAREA 3. UTILIZAREA SQL ÎN ORACLE ....................................... 31

1. Scopul lucrării .............................................................................................. 31

2. Lansarea iSQL Plus şi conectarea la baza de date ....................................... 31

3. Instrucţiunea SELECT................................................................................. 34

4. Instrucţiunile INSERT, UPDATE, DELETE .............................................. 36

LUCRAREA 4. CREAREA UNEI BAZE DE DATE.................................... 47

1. Scopul lucrării .............................................................................................. 47

2. Date preliminare........................................................................................... 47

3. Paşii de lucru................................................................................................ 48

4. Vizualizarea scripturilor şi editarea acestora ............................................... 55

LUCRAREA 5. MANAGEMENTUL SPAŢIILOR DE STOCARE DIN BAZA DE DATE ............................................................................................... 56

1. Scopul lucrării .............................................................................................. 56

2. Stocarea datelor în baza de date Oracle ....................................................... 56

3. Tablespace şi fişierele de date ..................................................................... 57

4. Crearea şi obţinerea de informaţii despre tablespace .................................. 58

Gheorghe Hazi


LUCRAREA 6. MANAGEMENTUL OBIECTELOR DINTR-O SCHEMĂ (PARTEA I-A) ................................................................................................... 65

1. Scopul lucrării .............................................................................................. 65

2. Stocarea obiectelor în baza de date Oracle .................................................. 65

3. Accesarea obiectelor unui utilizator ............................................................ 66

4. Crearea şi modificarea unei tabele.............................................................. 69

5. Restricţii în tabele ........................................................................................ 71

5. Acţiuni în machete tabele OEM................................................................... 74

6. Ştergerea tabelelor ....................................................................................... 74

7. Indecşi .......................................................................................................... 76

8. Lucrări de efectuat ....................................................................................... 77

LUCRAREA 7. MANAGEMENTUL OBIECTELOR DINTR-O SCHEMĂ (PARTEA II-A). ADMINISTRAREA UTILIZATORILOR........................ 80

1. Scopul lucrării .............................................................................................. 80

2. Vederi în baza de date Oracle ...................................................................... 80

3. Secvenţe ....................................................................................................... 81

4. Administrarea utilizatorilor.......................................................................... 83 4.1. Definiţii.................................................................................................. 83 4.2. Conturi ale utilizatorilor ........................................................................ 83 4.3. Conturi predefinite ................................................................................ 84 4.4. Crearea unui utilizator ........................................................................... 84 4.5. Modificarea stării unui utilizator........................................................... 85 4.7. Roluri ..................................................................................................... 90 4.8. Profiluri ................................................................................................. 92 4.9. Asignarea de cote în tablespace pentru utilizatori................................. 94

5. Lucrări de efectuat ....................................................................................... 94

LUCRAREA 8. INTRODUCERE ÎN PL/SQL (PARTEA I-A).................... 98

1. Scopul lucrării .............................................................................................. 98

2. Definirea mediului PL/SQL......................................................................... 98

3.Tipuri de blocuri ........................................................................................... 99

4. Crearea şi testarea unui bloc anonim ......................................................... 100

5. Declararea variabilelor............................................................................... 101

6. Scrierea codului în PL/SQL....................................................................... 105

7. Imbricarea blocurilor ................................................................................. 106

Gheorghe Hazi


8. Recomandări privind realizarea blocurilor PL/SQL.................................. 107

9. Utilizarea instrucţiunilor SQL în blocuri PL/SQL .................................... 108

10. Cursoare ................................................................................................... 111

11. Lucrări de efectuat ................................................................................... 112

LUCRAREA 9. INTRODUCERE ÎN PL/SQL (PARTEA II-A) ................ 115

1. Scopul lucrării ............................................................................................ 115

2. Structuri de control în PL/SQL.................................................................. 115

3. Structuri repetitive în PL/SQL................................................................... 119

4. Utilizarea cursoarelor în PL/SQL .............................................................. 122

5. Lucrări de efectuat ..................................................................................... 129

LUCRAREA 10. INTRODUCERE ÎN PL/SQL (PARTEA III-A) ............ 132

1. Scopul lucrării ............................................................................................ 132

2. Tratarea excepţiilor .................................................................................... 132

3. Proceduri şi funcţii stocate......................................................................... 142

4. Utilizarea instrucţiunii PL/SQL EXECUTE IMMEDIATE...................... 145


LUCRAREA 11. IMPORTUL ŞI EXPORTUL DATELOR DIN BAZA DE DATE ORACLE.............................................................................................. 148

1. Scopul lucrării ............................................................................................ 148

2. Încărcarea datelor din surse externe........................................................... 148 2.1. SQLLoader .......................................................................................... 148 2.2. Încărcarea datelor cu EM .................................................................... 155

3. Exportul datelor din baza de date Oracle................................................... 160 3.1. Utilizarea EXPDP (Data Pump EXPORT) ......................................... 160 3.2. Exportul datelor cu EM ....................................................................... 165

4. Importul datelor în baza de date Oracle..................................................... 166 4.1. Importul datelor cu IMPDP (Data Pump IMPORT) ........................... 166 4.2. Importul datelor cu EM ....................................................................... 168


LUCRAREA 12. SALVAREA DATELOR ÎN ORACLE........................... 173

1. Scopul lucrării ............................................................................................ 173

2. Facilităţi pentru salvarea şi restaurarea datelor ......................................... 173

3. Concepte privind salvarea şi restaurarea datelor ...................................... 174

Gheorghe Hazi


4. Configurarea bazei de date pentru salvare şi restaurare ............................ 175

5. Tipuri de salvări ......................................................................................... 176

6. Realizarea salvărilor cu EM....................................................................... 177


BIBLIOGRAFIE .............................................................................................. 183

Gheorghe Hazi


LUCRAREA 1. INTERFEŢE DE ADMINISTRARE A BAZEI DE DATE ORACLE

1. Scopul lucrării Lucrarea are drept scop acomodarea studenţilor cu principalele unelte de administrare Oracle în scopul obţinerii de informaţii despre baza de date, a modificării structurii sau a reconfigurării acesteia.

2. Instalarea clientului de Oracle Studenţii vor instala pe staţiile lor software client al bazei de date Oracle. Acesta oferă unelte pentru administrarea bazei de date şi pentru dezvoltarea de aplicaţii în diverse medii de programare.

2.1. Ştergerea unui client Oracle Dacă pe staţie este instalat deja un client Oracle, ştergerea acestuia, pentru un sistem WINDOWS, se face în următorii paşi:

• Ştergerea înregistrărilor din registry: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE De notat că dacă sunt instalate şi alte componente Oracle (de exemplu module de dezvoltare), atunci trebuie şterse numai cheile aferente bazei de date. Se va şterge numai grupul KEY_Ora_Client10g_home1 sau KEY_Ora_Db10g_home1. • Ştergerea inventarului modulelor instalate din C:\Program Files\Oracle. • Ştergerea fizică a fişierelor de pe disc (de regulă foldere de tipul

D:\oracle\product\10.2.0\client_1).

2.2. Paşii de instalare Paşii de instalare sunt daţi mai jos: A. Se lansează setup.exe din kitul de instalare client, localizat pe server.

Se apasă Next.

Gheorghe Hazi


B. Se apasă butonul Product Languages şi se selectează Romanian, pe lângă English. Se selectează varianta de instalare Administrator şi se apasă Next

C. Se specifică locul de instalare. Se va lăsa locul de instalare selectat de modulul de instalare. Se apasă Next.

D. Se verifică compatibilitatea cu sistemul de operare şi existenţa resurselor necesare (memorie, spaţiu pe disc). Trebuie ca la toate verificările să nu apară nici o problemă. Se apasă Next.

E. Se afişează sumarul modulelor care vor fi instalate. Se apasă Install. F. Se intră in faza de instalare propriu-zisă. Se aşteaptă câteva minute. G. Se trece automat la faza de configurare a conectării la baza de date

Oracle.

Gheorghe Hazi


Gheorghe Hazi


H. Se apasă Continuare

I. Se tastează Continuare la opţiunea de localizare a bazei de date (Local Naming).

Gheorghe Hazi


J. Se tastează numele serviciului de bază de date pe server. Acesta va fi comunicat de către administratorul serverului. Se tastează Continuare.

K. Se selectează protocolul TCP şi se apasă Continuare.

Gheorghe Hazi


L. Se tastează numele serverului sau adresa IP (ca în figură). Se apasă Continuare.

M. Se execută testul de conectare la baza de date. Implicit se lansează testul cu utilizatorul system şi parola manager. Se va schimba utilizatorul în scott şi parola tiger. Se apasă Continuare.

N. Se introduce numele serviciului sub care va fi recunoscută în viitor baza

de date. Numele tastat va fi numele studentului. Se apasă Continuare.

Gheorghe Hazi


3. Oracle Enterprise Manager (OEM) Există două variante ale acestui puternic modul de administrare. O variantă

client server, asemănătoare cu cea din versiunile anterioare (Oracle 9, Oracle 8), şi o variantă în tehnologie NET.

A. Varianta internet se lansează cu c-da: http://nume_server:1158/em

Se introduc numele utilizatorului şi parola date la pasul precedent. După lansare se afişează informaţii generale despre baza de date, ca în figura de mai jos.

Se vor nota următoarele informaţii:

• Dimensiune memorie (total SGA componente: Shared Pool, Buffer Cache, Large Pool, Java Pool, PGA)

• Fişiere de date şi gradul lor de umplere • Tabelele din schema utilizatorului HR

B. Varianta client. Se lansează din START> PROGRAMS>

OracleClient10g_home1> Oracle Enterprise Manager Este o variantă care nu oferă toate facilităţile variantei internet însă, în

multe privinţe, mai ales în ceea ce priveşte manipularea obiectelor, este mult mai facilă.

http://nume_server:1158/em�

Gheorghe Hazi


Mai întâi este necesară detectarea bazei de date la care se face conectarea. Acest lucru se face prin tastarea numelui serverului (sau adresa IP) şi a numelui bazei de date.

In acest caz interogarea se face printr-o interfaţă de tip Windows Explorer. Se vor obţine informaţiile precizate la pct. A şi se vor compara informaţiile obţinute.

4. iSQL Plus Este un modul de acces la o bază de date prin tehnologia internet. Lansarea se face cu c-da: http://nume_server:5560/isqlplus.

http://nume_server:5560/isqlplus�

Gheorghe Hazi


Se introduc numele utilizatorului, parola şi şirul de conectare.

Se execută următoarele interogări ale vederilor bazei de date: select * from v$sga select * from v$datafile select * from dba_tables where owner='HR'

Gheorghe Hazi


LUCRAREA 2. ORACLE SQL PLUS

1. Scopul lucrării Lucrarea are drept scop însuşirea regulilor de utilizare a mediului Oracle SQL Plus. Vor fi însuşite cunoştinţe legate de administrarea bazei de date cât şi cunoştinţe în legătură cu dezvoltarea aplicaţiilor, în mod deosebit, elaborarea şi testarea instrucţiunilor SQL.

2. Lansarea SQL Plus şi conectarea la baza de date Se lansează din STARTUP> Progarms> Oracle – OraClient10g_home1>

Application Development> SQL Plus . După lansare se cere utilizatorul, parola şi şirul de conectare. Se vor introduce utilizatorul şi parola comunicate de administratorul bazei de date.

3. Comenzi SQL Plus Comenzile scurte SQL Plus sunt următoarele: Home Top of screen buffer

End Bottom of screen buffer

Page Up Previous screen page

Gheorghe Hazi


Page Down Next screen page

Ctrl+Page Up Show page on left of current screen page

Ctrl+Page Down

Show page on right of current screen page

Alt+F3 Find

F3 Find next

Ctrl+C Cancels the data fetch operation following command execution.

Ctrl+C Copies text - when no operations are running.

Ctrl+V Paste text

Shift+Del Clear the screen and the screen buffer Comenzile SQL Plus sunt: @url|fişier [arg] - Execută instrucţiuni SQL Plus aflate în fişierul specificat. Fişierul poate fi local sau de pe web şi are extensia implicită SQL. @@ url|fişier [arg] – Idem. Fişierele apelate din scriptul specificat sunt căutate în acelaşi folder. / - Execută ultima c-dă SQL sau bloc PL/SQL ACCEPT variabilă [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE] Citeşte o line de la tastatură şi o stochează îtr-o variabilă. Exemplu : accept nr number format '99' prompt 'Introduceti nr: ' A[PPEND] text Adaugă text la sfârşitul liniei curente din buffer. ARCHIVE LOG {LIST | STOP} | {START | NEXT | ALL |întreg} [TO destinaţie] Porneşte sau opreşte arhivarea automată a fişierelor jurnal. Arhivează manual sau afişează informaţii despre arhivare. ATTR[IBUTE] [tip_nume.atribut_nume [opţiuni ...]]

Gheorghe Hazi


unde opţiuni poate fi ALI[AS] alias CLE[AR] FOR[MAT] format LIKE {tip_nume.atribute_nume | alias} ON | OFF BRE[AK] [ON element_raport [acţuiune [acţiune]]] ... Specifică unde şi cum se fac modificări într-un raport. Unde element_raport are sintaxa {coloană|expr|ROW|REPORT Şi acţiune poate fi: [SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]] BTI[TLE] [printspec [text | variabilă] ...] | [ON | OFF] Plasează şi formatează titlul specificat la sfârşitul fiecărei pagini din raport sau listează valoarea curentă. C[HANGE] separator vechi [separator [nou [separator]]] Schimbă textul în linia curentă din bufer. CL[EAR] opţiune ... Unde opţiune poate fi. BRE[AKS] BUFF[ER] COL[UMNS] COMP[UTES] SCR[EEN] SQL TIMI[NG] Resetează sau şterge opţiuni ale mediului SQL Plus. COL[UMN] [{coloană | expr} [optiuni ...]] unde opţiunile pot fi: ALI[AS] alias CLE[AR] ENTMAP {ON | OFF} FOLD_A[FTER] FOLD_B[EFORE] FOR[MAT] format

Gheorghe Hazi


HEA[DING] text JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]} LIKE {expr | alias} NEWL[INE] NEW_V[ALUE] variabilă NOPRI[NT] | PRI[NT] NUL[L] text OLD_V[ALUE] variabilă ON | OFF WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] Specifică caracteristicile de afişare pentru coloana dată sau listează caracteristicile de afişare pentru o coloană sau pentru toate coloanele. COMP[UTE] [functie [LAB[EL] text] ... OF {expr | coloană | alias} ... ON {expr | coloană | alias | REPORT | ROW} ...] Calculează şi afişează linii de sumar utilizând mai multe funcţii standard (suam, medie..), pe un set de linii de date, sau listează definiţiile COMPUTE. CONN[ECT] [{ logon | / } [AS {SYSOPER | SYSDBA}]] CONN[ECT] [{logon | / | proxy} [AS {SYSOPER | SYSDBA}]] unde logon are sintaxa: utilizator[/parolă] [@şir_de_conectare] unde proxy are sintaxa: proxy utilizator[username] [/parolă] [@şir_de_conectare] Conectează un utilizator la baza de date. DEF[INE] [variabilă] | [variabilă = text] Defineşte o variabilă de susbstituţie şi atribuie acesteia un şir de caractere. Variabila se va utiliza sub forma &variabilă. DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST] Şterge una sau mai multe linii din buffer. n şterge linia n. n m şterge de la n lsa m.

Gheorghe Hazi


n * şterge de la linia n la linia curentă. n LAST şterge de la n până la ultima linie * şterge linia curentă * n ştege de la linia curentă până la n. * LAST şterge de la linia curentă până la ultima. LAST şterge ultima linie. DESC[RIBE] {[schema.]object[@db_link]} Listează coloanele pentru o tabelă, vedre sau sinonim sau specificaţiile unei proceduri. DISC[ONNECT] Comite modificările utilizatorului curent şi închide sesiunea. ED[IT] [file_name[.ext]] Lansează editorul de texte al sistemului (notepad.exe) şi editează ultima c-dă SQL sau fişierul specificat. EXEC[UTE] procedură Execută o procedură PL/SQL. EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] Termină sesiunea SQL PLUS şi redă controlul sistemului de opeare. GET [FILE] file_name[.ext] [LIST | NOLIST] Încarcă un fişier în buferul SQL PLUS. HELP | ? [topic] Obţine ajutor pentru SQL Plus. HO[ST] [command] Execută o c-dă a sistemului de operare fără ieşirea din SQL Plus. I[NPUT] [text] Adaugă una sau mai multe noi linii după linia curentă. L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST] Listează una sau mai multe linii din buffer.

Gheorghe Hazi


Semnificaţia mărimilor este la fel ca la c-da DEL. PASSW[ORD] [utilizator] Permite modificarea parolei. PAU[SE] text Afişează textul când se aşteaptă ENTER de la tastatură. Pentru a activa pauza se utilizează SET PAUSE ON. PRI[NT] [variabilă ...] Afişează valoarea variabilelor de legătură din PL/SQL. PRO[MPT] [text] Afişează mesajul la ecran. RECOVER Execută recuperarea datelor după un incident media. Se va analiza separat. REM[ARK ] Introduce comentarii într-un script. REPF[OOTER] [PAGE] [printspec [text | variable] ...] | [ON | OFF] unde printspec reprezintă una sau mai multe din clauzele de mai jos folosite pentru a plasa textul formatat. COL n S[KIP] [n] TAB n LE[FT] CE[NTER] R[IGHT] BOLD FORMAT text Introduce un text la sfârşitul fiecărui raport sau afişează acest text. REPH[EADER] [PAGE] [printspec [text | variable] ...] | [ON | OFF] Introduce un text la începutul fiecărui raport sau afişează acest text.

Gheorghe Hazi


R[UN ] Listează şi execută c-da SQL sau blocul PL/SQL din buffer. SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] Salvează buferul SQL Plus într-un fişier al sistemului de operare. SET Configurează mediul PL/SQL în sesiunea curentă. SHO[W] opţiune Unde opţiune poate fi: system_variable ALL BTI[TLE] ERR[ORS] [ { FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS } [schema.]name] LNO PARAMETERS [nume_paramnetru] PNO RECYC[LEBIN] [nume_original] REL[EASE] REPF[OOTER] REPH[EADER] SGA SPOO[L] SQLCODE TTI[TLE] USER XQUERY Afişează valoarea variabilelor de sistem SQL Plus sau caracteristici ale mediului SQL Plus. SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]] Opreşte normal sau forţat o baza de date curentă.

Gheorghe Hazi


SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT] Trimite rezultatul interogărilor SQL într-un fişier al sistemului de operare şi, opţional, la imprimantă. STA[RT] {url | file_name[.ext] } [arg...] Execută un script SQL sau PL/SQL. Scriptul poate fi descărcat şi de pe internet. STARTUP opţiuni | opţiuni_upgrade Unde opţiunile au următoarea sintaxă: [FORCE] [RESTRICT] [PFILE=fişier] [QUIET] [ MOUNT [nume_BD] | [ OPEN [opţiuni_deschidere] [nume_BD] ] | NOMOUNT ] Opţiunile de deschidere au următoarea sintaxă: READ {ONLY | WRITE [RECOVER]} | RECOVER Opţiunile upgrade au sintaxa: [PFILE=fişier] {UPGRADE | DOWNGRADE} [QUIET] Porneşte o bază de date Oracle cu câteva opţiuni, incluzând montarea şi deschiderea bazei de date. STORE SET file_name[.ext] [ CRE[ATE | REP[LACE] | APP[END]] Salvează atributele mediului SQL Plus într-un fişier (script) al sistemului de operare. TIMI[NG] [START text | SHOW | STOP] Înregistrează timpul scurs de la start până la stop sau afişează numărul timer-rilor activi. UNDEF[INE] variabilă ... Şterge o variabilă de substituţie. TTI[TLE] [printspec [text | variable] ...] [ON | OFF] Unde printspec este una din clauzele de formatare: BOLD CE[NTER] COL n FORMAT text LE[FT] R[IGHT]

Gheorghe Hazi


S[KIP] [n] TAB n Plasează şi formatează titulul fiecărei pagini ale unui raport sau afişează definiţiile curente. VAR[IABLE] [variable [type] ] Defineşte o variabilă de legătură care poate fi referită în module PL/SQL.

4. Elaborarea unui script SQL Plus pentru obţinerea unui raport Se va crea scriptul salariati.sql cu următorul conţinut: conn hr/hr@ub set echo off rem set termout off set heading on set feedback off set pause off set colsep '|' set pagesize 50 set linesize 102 set headsep '!' col marca FORMAt A7 jus Center hea '| Marca' col nume format A20 jus center hea 'Nume' col job format A32 jus center hea 'Job' tru col salariu format '999999' jus center hea 'Salariu' col compartiment format A17 jus center hea 'Compartiment' wrap col sef format A14 jus center hea ' Sef |' spool D:\salariati.txt ttitle col 30 'L I S T A P E R S O N A L' RIGHT "Pag:" format '9' sql.pno skip 1 col 30 "----------------------------" skip 2 '----------------------------------------------------------------------------------------------' btitle col 1 '----------------------------------------------------------------------------------------------' BREAK ON REPORT SKIP 1 COMPUTE SUM LABEL '| TOTAL' OF SALARIU ON REPORT select '| '||to_char(a.employee_id,'999') AS Marca,

Gheorghe Hazi


a.first_name||' '||a.last_name as Nume, c.job_title as Job, a.salary as Salariu, d.department_name as Compartiment, rpad(nvl(b.last_name,' '),12)||' |' as sef from employees a, employees b, jobs c, departments d where b.employee_id(+)=a.manager_id and c.job_id=a.job_id and d.department_id=a.department_id order by a.last_name / spool off set pau on

Se analizează conţinutul acestui script pentru a înţelege funcţionarea lui. Se va lansa acest script în execuţie cu c-da sql:

SQL> @nume_fişier_script Se va obţine un raport cu lista personalului din datele stocate în schema utilizatorului HR. Se modifică raportul astfel încât să apară numai salariaţii cu salariul mai mare de 7000. Se modifică raportul astfel încât sortarea salariaţilor să fie în ordinea descrescătoare a salariului. Se completează raportul cu o coloană care să indice localitatea de domiciliu a fiecărei persoane.

Gheorghe Hazi


LUCRAREA 3. UTILIZAREA SQL ÎN ORACLE

1. Scopul lucrării Lucrarea are drept scop însuşirea modului de scriere a instrucţiunilor SQL în ORACLE. Vor fi însuşite cunoştinţe legate de instrucţiunile principale SQL: SELECT, INSERT, UPDATE, DELETE.

2. Lansarea iSQL Plus şi conectarea la baza de date Se lansează din Internet Explorer cu c-da: http://192.168.4.45:5560/isqlplus Conectarea se va face cu utilizatorul: hr, parola hr, şir de conectare: ubc. Baza de date este în schema HR şi cuprinde următoarele tabele:

Denumire Conţinut COUNTRIES Nomenclator ţări DEPARTMENTS Nomenclator departamente EMPLOYEES Tabela personal JOBS Nomenclator funcţii JOB_HISTORY Istoric funcţii pe salariaţi LOCATIONS Lista localităţi unde compania are subunităţi REGIONS Lista regiuni (continente)

Structura tabelelor este următoarea: COUNTRIES

Câmp Semnificaţie Restricţii Tip camp COUNTRY_ID Cod ţară NOT NULL, PK CHAR(2) COUNTRY_NAME Nume ţară VARCHAR2(40) REGION_ID Cod regiune FK REGIONS NUMBER

http://192.168.4.45:5560/isqlplus�

Gheorghe Hazi


DEPARTMENTS

Câmp Semnificaţie Restricţii Tip camp

DEPARTMENT_ID Cod dep. NOT NULL, PK NUMBER(4)

DEPARTMENT_NAME Nume dep. NOT NULL VARCHAR2(30)

MANAGER_ID Cod şef dep. FK EMPLOYEES NUMBER(6)

LOCATION_ID Cod localiatate

FK LOCATIONS NUMBER(4)

EMPLOYEES


EMPLOYEE_ID Cod pers. (marca) NOT NULL , PK NUMBER(6)

FIRST_NAME Nume VARCHAR2(20) LAST_NAME Prenume NOT NULL VARCHAR2(25)

EMAIL NOT NULL , UNIQUE KEY VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20) HIRE_DATE Data angajării NOT NULL DATE

JOB_ID Cod funcţie NOT NULL, FK JOBS VARCHAR2(10)

SALARY Salariu lunar > 0 NUMBER(8,2) COMMISSION_PCT Comision NUMBER(2,2)

MANAGER_ID Marca şef FK EMPLOYES NUMBER(6)

DEPARTMENT_ID FK DEPARTMENTS NUMBER(4)

JOBS

Câmp Semnificaţie Restricţii Tip camp JOB_ID Cod funcţie NOT NULL, PK VARCHAR2(10) JOB_TITLE Nume NOT NULL VARCHAR2(35)

Gheorghe Hazi


funcţie MIN_SALARY Sal. minim NUMBER(6) MAX_SALARY Sal. maxim NUMBER(6)

JOB_HISTORY


EMPLOYEE_ID Marca NOT NULL, PK,

FK EMPLOYEES

NUMBER(6)

START_DATE Data început NOT NULL,PK < END_DATE DATE

END_DATE Data sfârşit NOT NULL, >START_DATE DATE

JOB_ID Cod funcţie NOT NULL, FK JOBS VARCHAR2(10)

DEPARTMENT_ID Cod dep. FK

DEPARTMENTS

NUMBER(4)

LOCATIONS

Câmp Semnificaţie Restricţii Tip camp LOCATION_ID Cod localitate NOT NULL, PK NUMBER(4) STREET_ADDRESS Adresa VARCHAR2(40) POSTAL_CODE Cod poştal VARCHAR2(12) CITY Oraş NOT NULL VARCHAR2(30)

STATE_PROVINCE Provincie (judeţ) VARCHAR2(25)

COUNTRY_ID Cod ţară FK COUNTRIES CHAR(2)

REGIONS Câmp Semnificaţie Restricţii Tip camp REGION_ID Cod regiune NOT NULL, PK NUMBER REGION_NAME Nume VARCHAR2(25)

Gheorghe Hazi


3. Instrucţiunea SELECT 3.1. Se va afişa lista tabelelor aflate în proprietatea utilizatorului hr cu c-da: SELECT table_name from user_tables

TABLE_NAME COUNTRIES DEPARTMENTS EMPLOYEES JOBS JOB_HISTORY LOCATIONS REGIONS

3.2. Se va afişa structura tuturor tabelelor cu c-da SQLPlus describe 3.3. Se va afişa conţinutul tabelei DEPARTMENTS 3.4. Se va afişa lista următoarelor informaţii din tabela de personal employees: marca (employee_id), nume (last_name), cod funcţie (job_id), data angajării (hire_date).

3.5. Se vor afişa, în mod unic, toate funcţiile utilizate. 3.6. Se vor afişa, concatenat, fiecare salariat şi codul funcţiei: 3.7. Se afişează, toate informaţiile din tabela de salariaţi (employees),

despărţite prin virgulă. Numele coloanei va fi IESIRE. 3.8. Se vor afişa salariaţii cu salariul (salary) cuprins între 12000 şi 25000. 3.9. Se va afişa departamentul şi numele pentru salariatul cu marca 176. 3.10. Se va afişa numele (last_name), cod funcţie (job_id) şi data angajării

pentru salariaţii Matos şi Taylor. Datele vor fi afişate crescător după data angajării.

3.11. Afişează salariaţii din departamentele 20 şi 50, ordonaţi alfabetic. 3.12. Afişează salariaţii cu salariul între 5000 şi 12000 din departamentele

20 şi 50. 3.13. Afişează numele salariaţilor care nu au şef. 3.14. Afişează salariaţii angajaţi în 1994. 3.15. Afişează numele salariaţilor, salariul şi comisionul pentru salariaţii

Gheorghe Hazi


cu comision, descrescător după salariu + comision. 3.16. Afişaţi salariaţii care au salariul mai mare decât o anumită valoare.

Valoarea se va introduce de la tastatură. 3.17. Se vor afişa marca, salariaţii (numele), salariul, departamentul care

sunt în subordinea şefului cu o marcă dată, ordonate după o coloană introdusă de la tastatură.

Se va testa pentru marca şefului 103 şi coloana de sortare salary. 3.18. Idem, dar se vor crea prompturi clare pentru introducerea marca şef

şi coloana sortare. 3.19. Afişează salariaţii care au al treilea caracter în nume (last_name) a. 3.21. Se va afişa data curentă: 3.22. Se afişează marca, numele salariaţilor, salariul actual şi salariul

majorat cu 15.5%, rotunjit. 3.23. Se afişează marca, numele salariaţilor, salariul actual şi salariul

majorat cu 15.5%, rotunjit precum şi valoarea cu care se majorează salariul. 3.24. Să se afişeze numele salariaţilor (numai prima literă mare), lungimea

numelui, pentru salariaţii ai căror nume încep cu J, M, A, ordonat după nume: 3.25. Să se afişeze numele şi numărul de luni lucrate de salariaţi, în

ordinea crescătoare a numărului de luni lucrate. 3.26. Afişează numele salariatului, data angajării şi ziua din săptămână

aferentă. 3.27. Să se afişeze un raport cu numele şi valoarea comisionului pe

salariaţi (în procente). Pentru cei care nu au comision se va afişa „Nu are comision”.

3.28. Să se afişeze numele şi salariul sub formă grafică, prin steluţe (*), în ordinea descrescătoare a acestuia

3.29. Să se afişeze salariul maxim, minim, suma salariilor şi media acestora.

3.30. Idem, însă pe fiecare funcţie în parte. 3.31. Afişează numărul de salariaţi pe fiecare funcţie. 3.32. Determină numărul de şefi din tabela de salariaţi. 3.33. Afişează numărul total de salariaţi angajaţi în perioada 1995-1998,

precum şi în fiecare an. 3.34. Să se afişeze codul localităţii, adresa, oraşul, provincia şi numele

ţărilor din care fac parte localităţile. 3.35. Afişează numele salariaţilor, codul departamentului şi numele

departamentului pentru toţi salariaţii. 3.36. Afişează numele, codul funcţiei, codul departamentului şi numele

acestuia pentru salariaţii din Toronto.

Gheorghe Hazi


3.37. Să se afişeze codul departamentului, numele salariatului şi al tuturor colegilor din departament, sortat după cod departament, nume salariat şi nume coleg.

3.38. Să se afişeze toţi salariaţii angajaţi după salariatul Davies. 3.39. Să se afişeze numele şi data angajării salariaţilor şi al şefilor pentru

care data angajării salariaţilor este mai veche ca a şefilor. 3.40. Să se afişeze toţi salariaţii (marca, nume, salariul) care au salariul

mai mare ca media salariilor, în ordinea crescătoare a salariilor. 3.41. Să se afişeze salariaţii (marca, nume) şi numele departamentului în

care există cel puţin un salariat al cărui nume conţine litera u. 3.42. Să se afişeze salariaţii (nume, salariu) care îi sunt subordonaţi lui

King. 3.43. Să se afişeze departamentele care nu au salariaţi cu funcţia

ST_CLERK.

4. Instrucţiunile INSERT, UPDATE, DELETE Se creează tabela nume_student: CREATE TABLE nume_student (id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL, last_name VARCHAR2(25), first_name VARCHAR2(25), userid VARCHAR2(8), salary NUMBER(9,2))

4.1. Să se insereze 4 înregistrări în tabela nume_student 4.2. Să se scrie un script pentru introducerea automată a înregistrărilor

folosind variabile de substituţie. Câmpul userid va fi generat automat prin concatenarea primului caracter din first_name şi a primelor şapte caractere din last_name.

Se salvează scriptul cu numele insert_sal.sql. Variabilele prefixate cu && rămân în memorie la sfârşitul scriptului

Se inserează încă 2 înregistrări folosind scriptul. 4.3. Se schimă numele (last_name) în HAIDUC, pentru salariatul cu id=3. 4.4. Se cresc salariile cu 20% pentru salariaţii cu salariul < 1300. 4.5. Şterge un salariat din tabela nume_student, după nume şi comite toate

modificările.

Gheorghe Hazi


Răspunsuri: Instrucţiunea SELECT 3.1. Se va afişa lista tabelelor aflate în proprietatea utilizatorului hr cu c-da: SELECT table_name from user_tables

TABLE_NAME COUNTRIES DEPARTMENTS EMPLOYEES JOBS JOB_HISTORY LOCATIONS REGIONS

3.2. Se va afişa structura tuturor tabelelor cu c-da SQLPlus describe, ca în exemplul de mai jos: DESCR employees

Nume Nul? Tip EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)

3.3. Se va afişa conţinutul tabelei DEPARTMENTS SELECT * from departments

Gheorghe Hazi


DEPARTMENT_ID

DEPARTMENT_NAME MANAGER_ID LOCATION_ID

10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700

40 Human Resources 203 2400

50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700

100 Finance 108 1700 ...... 3.4. Se va afişa lista următoarelor informaţii din tabela de personal employees: marca (employee_id), nume (last_name), cod funcţie (job_id), data angajării (hire_date):

SELECT employee_id as Marca, last_name as Nume, job_id as "Cod functie" , hire_date as "Data angajarii" FROM employees

MARCA NUME Cod functie Data angaj 198 OConnell SH_CLERK 21-06-1999 199 Grant SH_CLERK 13-01-2000 200 Whalen AD_ASST 17-09-1987 201 Hartstein MK_MAN 17-02-1996 202 Fay MK_REP 17-08-1997

.... 3.5. Se vor afişa, în mod unic, toate funcţiile utilizate: SELECT DISTINCT job_id FROM employees 3.6. Se vor afişa, concatenat, fiecare salariat şi codul funcţiei: SELECT last_name||' – ' || job_id as "Salariat - functie" FROM employees

Gheorghe Hazi


Salariat - functie OConnell-SH_CLERK Grant-SH_CLERK Whalen-AD_ASST Hartstein-MK_MAN ..... 3.7. Se afişează, toate informaţiile din tabela de salariaţi (employees),

despărţite prin virgulă. Numele coloanei va fi IESIRE: SELECT employee_id || ',' || first_name || ',' || last_name || ',' || email || ',' || phone_number || ','|| job_id || ',' || manager_id || ',' || hire_date || ',' || salary || ',' || commission_pct || ',' || department_id AS IESIRE FROM employees

IESIRE 198,Donald,OConnell,DOCONNEL,650.507.9833,SH_CLERK,124,21-06-1999,2600,,50 199,Douglas,Grant,DGRANT,650.507.9844,SH_CLERK,124,13-01-2000,2600,,50 200,Jennifer,Whalen,JWHALEN,515.123.4444,AD_ASST,101,17-09-1987,4400,,10

…. 3.8. Se vor afişa salariaţii cu salariul (salary) cuprins între 12000 şi

25000: SELECT last_name as Nume, salary as Salariu FROM employees WHERE salary between 12000 and 25000

NUME SALARIU Hartstein 13000 Higgins 12000 King 24000 …. 3.9. Se va afişa departamentul şi numele pentru salariatul cu marca 176: SELECT last_name as Nume, department_id as "Cod departament" FROM employees WHERE employee_id = 176

Gheorghe Hazi


3.10. Se va afişa numele (last_name), cod funcţie (job_id) şi data angajării pentru salariaţii Matos şi Taylor. Datele vor fi afişate crescător după data angajării:

SELECT last_name AS Nume, job_id AS "Cod functie", hire_date AS "Data angajarii" FROM employees WHERE last_name IN ('Matos', 'Taylor') ORDER BY hire_date NUME Cod functie Data angaj

Taylor SH_CLERK 24-01-1998 Matos ST_CLERK 15-03-1998 Taylor SA_REP 24-03-1998

3.11. Afişează salariaţii din departamentele 20 şi 50, ordonaţi alfabetic: SELECT last_name, department_id FROM employees WHERE department_id IN (20, 50) ORDER BY last_name ASC 3.12. Afişează salariaţii cu salariul între 5000 şi 12000 din departamentele

20 şi 50: SELECT last_name "Salariat", salary "Salariu lunar" FROM employees WHERE salary BETWEEN 5000 AND 12000 AND department_id IN (20, 50) 3.13. Afişează numele salariaţilor care nu au şef: SELECT last_name, job_id FROM employees WHERE manager_id IS NULL 3.14. Afişează salariaţii angajaţi în 1994: SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%94' 3.15. Afişează numele salariaţilor, salariul şi comisionul pentru salariaţii

cu comision, descrescător după salariu + comision: SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC, commission_pct DESC

Gheorghe Hazi


3.16. Afişaţi salariaţii care au salariul mai mare decât o anumită valoare. Valoarea se va introduce de la tastatură:

SELECT last_name, salary FROM employees WHERE salary > &limita_sal 3.17. Se vor afişa marca, salariaţii (numele), salariul, departamentul care

sunt în subordinea şefului cu o marcă dată, ordonate după o coloană introdusă de la tastatură:

SELECT employee_id, last_name, salary, department_id FROM employees WHERE manager_id = &mgr_num ORDER BY &order_col Se va testa pentru marca şefului 103 şi coloana de sortare salary. 3.18. Idem, dar se vor crea prompturi clare pentru introducerea marca şef

şi coloana sortare: accept mgr_num number prompt "Marca sef:" accept order_col char prompt "Coloana sortare:" SELECT employee_id, last_name, salary, department_id FROM employees WHERE manager_id = &mgr_num ORDER BY &order_col 3.19. Afişează salariaţii care au al treilea caracter în nume (last_name) a: SELECT last_name FROM employees WHERE last_name LIKE '__a%' 3.20. Afişează salariaţii care au caracterele a şi e în nume (last_name): SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%' 3.21. Se va afişa data curentă: select sysdate from dual 3.22. Se afişează marca, numele salariaţilor, salariul actual şi salariul

majorat cu 15.5%, rotunjit: SELECT employee_id Marca, last_name Nume, salary "Salariu", ROUND(salary * 1.155, 0) "Salariu nou" FROM employees 3.23. Se afişează marca, numele salariaţilor, salariul actual şi salariul

majorat cu 15.5%, rotunjit precum şi valoarea cu care se majorează salariul:

Gheorghe Hazi


SELECT employee_id Marca, last_name Nume, salary "Salariu", ROUND(salary * 1.155, 0) "Salariu Nou", ROUND(salary * 1.155, 0) - salary "Crestere salariu" FROM employees 3.24. Să se afişeze numele salariaţilor (numai prima literă mare), lungimea

numelui, pentru salariaţii ai căror nume încep cu J, M, A, ordonat după nume: SELECT INITCAP(last_name) "Name", LENGTH(last_name) "Length" FROM employees WHERE last_name LIKE 'J%' OR last_name LIKE 'M%' OR last_name LIKE 'A%' ORDER BY last_name 3.25. Să se afişeze numele şi numărul de luni lucrate de salariaţi, în

ordinea crescătoare a numărului de luni lucrate: SELECT last_name as "Nume", ROUND(MONTHS_BETWEEN ( SYSDATE, hire_date)) as "Luni lucrate" FROM employees ORDER BY "Luni lucrate" 3.26. Afişează numele salariatului, data angajării şi ziua din săptămână

aferentă: SELECT last_name AS "Nume" , hire_date as "Data angajarii", TO_CHAR(hire_date, 'DAY') as "Ziua" FROM employees ORDER by "Nume" 3.27. Să se afişeze un raport cu numele şi valoarea comisionului pe

salariaţi (în procente). Pentru cei care nu au comision se va afişa „Nu are comision”:

SELECT last_name as "Nume", NVL(TO_CHAR(commission_pct*100), 'Nu are Comision') as

"Comision" FROM employees 3.28. Să se afişeze numele şi salariul sub formă grafică, prin steluţe (*), în

ordinea descrescătoare a acestuia SELECT last_name as "Nume", rpad(' ', salary/1000+1, '*') as "Salariu" FROM employees ORDER BY salary DESC 3.29. Să se afişeze salariul maxim, minim, suma salariilor şi media

Gheorghe Hazi


acestora: SELECT ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Suma", ROUND(AVG(salary),0) "Media" FROM employees 3.30. Idem, însă pe fiecare funcţie în parte: SELECT job_id, ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Suma", ROUND(AVG(salary),0) "Media" FROM employees GROUP BY job_id 3.31. Afişează numărul de salariaţi pe fiecare funcţie: SELECT job_id as "Cod functie", COUNT(*) as "Număr" FROM employees GROUP BY job_id 3.32. Determină numărul de şefi din tabela de salariaţi: SELECT count(distinct manager_id) FROM employees WHERE manager_id is not null 3.33. Afişează numărul total de salariaţi angajaţi în perioada 1995-1998,

precum şi în fiecare an: SELECT COUNT(*) total, SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1995", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998" FROM employees 3.34. Să se afişeze codul localităţii, adresa, oraşul, provincia şi numele

ţărilor din care fac parte localităţile: SELECT location_id "Cod", street_address "Adresa", city "Oras",

state_province "Provincie", country_name "Tara" FROM locations NATURAL JOIN countries 3.35. Afişează numele salariaţilor, codul departamentului şi numele

departamentului pentru toţi salariaţii: SELECT last_name "Nume", department_id "Cod dep", department_name

"Nume dep" FROM employees

Gheorghe Hazi


JOIN departments USING (department_id) 3.36. Afişează numele, codul funcţiei, codul departamentului şi numele

acestuia pentru salariaţii din Toronto: SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) WHERE LOWER(l.city) = 'toronto' 3.37. Să se afişeze codul departamentului, numele salariatului şi al tuturor

colegilor din departament, sortat după cod departament, nume salariat şi nume coleg:

SELECT e.department_id "Departament", e.last_name "Salariat", c.last_name "Coleg" FROM employees e JOIN employees c ON (e.department_id = c.department_id) WHERE e.employee_id <> c.employee_id ORDER BY e.department_id, e.last_name, c.last_name 3.38. Să se afişeze toţi salariaţii angajaţi după salariatul Davies: SELECT e.last_name, e.hire_date FROM employees e JOIN employees davies ON (davies.last_name = 'Davies') WHERE davies.hire_date < e.hire_date Sau SELECT e.last_name, e.hire_date FROM employees e, employees d WHERE d.last_name = 'Davies' and d.hire_date < e.hire_date 3.39. Să se afişeze numele şi data angajării salariaţilor şi al şefilor pentru

care data angajării salariaţilor este mai veche ca a şefilor: SELECT w.last_name "Nume", w.hire_date "Data an", m.last_name

"Nume sef", m.hire_date "Data sef" FROM employees w JOIN employees m ON (w.manager_id = m.employee_id) WHERE w.hire_date < m.hire_date 3.40. Să se afişeze toţi salariaţii (marca, nume, salariul) care au salariul

mai mare ca media salariilor, în ordinea crescătoare a salariilor: SELECT employee_id, last_name, salary FROM employees

Gheorghe Hazi


WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary 3.41. Să se afişeze salariaţii (marca, nume) şi numele departamentului în

care există cel puţin un salariat al cărui nume conţine litera u: SELECT employee_id, last_name, department_name FROM employees NATURAL JOIN departments WHERE department_id IN (SELECT department_id FROM employees WHERE last_name like '%u%') 3.42. Să se afişeze salariaţii (nume, salariu) care îi sunt subordonaţi lui

King: SELECT last_name, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name = 'King') 3.43. Să se afişeze departamentele care nu au salariaţi cu funcţia

ST_CLERK: SELECT department_id FROM departments MINUS SELECT department_id FROM employees WHERE job_id = 'ST_CLERK'

Instrucţiunile INSERT, UPDATE, DELETE Se creează tabela nume_student: CREATE TABLE nume_student (id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL, last_name VARCHAR2(25), first_name VARCHAR2(25), userid VARCHAR2(8), salary NUMBER(9,2)) 4.1. Să se insereze 4 înregistrări în tabela nume_student (în exemple my_employee)

Gheorghe Hazi


INSERT INTO my_employee VALUES (1, 'IONESCU', 'MIHAI', 'mionescu', 895) INSERT INTO my_employee VALUES (2, 'POPESCU', 'ION', 'ipopescu', 1200) INSERT INTO my_employee VALUES (3, 'CIOBANU', 'PETRU', 'pciobanu', 1100) INSERT INTO my_employee VALUES (4, 'VASILESCU', 'GEORGE', 'gvasile', 1400) 4.2. Să se scrie un script pentru introducerea automată a înregistrărilor

folosind variabile de substituţie. Câmpul userid va fi generat automat prin concatenarea primului caracter din first_name şi a primelor şapte caractere din last_name:

SET ECHO OFF SET VERIFY OFF INSERT INTO my_employee VALUES (&p_id, '&&p_last_name', '&&p_first_name', lower(substr('&p_first_name', 1, 1) || substr('&p_last_name', 1, 7)), &p_salary); SET VERIFY ON SET ECHO ON UNDEFINE p_first_name UNDEFINE p_last_name Se salvează scriptul cu numele insert_sal.sql. Variabilele prefixate cu &&

rămân în memorie la sfârşitul scriptului Se inserează încă 2 înregistrări folosind scriptul. 4.3. Se schimă numele (last_name) în HAIDUC, pentru salariatul cu id=3. UPDATE my_employee SET last_name='HAIDUC' WHERE id=3 4.4. Se cresc salariile cu 20% pentru salariaţii cu salariul < 1300. UPDATE my_employee SET salary=salary*1.2 WHERE salary <1300 4.5. Şterge un salariat din tabela nume_student, după nume şi comite toate

modificările: DELETE from my_employee WHERE last_name='POPESCU' COMMIT

Gheorghe Hazi


LUCRAREA 4. CREAREA UNEI BAZE DE DATE

1. Scopul lucrării Lucrarea are drept scop însuşirea regulilor pentru crearea unei baze de date Oracle. Studenţii vor parcurge paşii de creare asistată a unei baze de date precum şi metode de customizare a unei baze de date. Se va utiliza utilitarul DBCA - Database Configuration Assistant.

2. Date preliminare

Principalele activităţi preliminare sunt: Estimarea spaţiului necesar pentru tabele de date şi indecşi; Stabilirea amplasamentului pe harddiscuri a fişierelor bazei de date; Stabilirea numelui global al bazei de date, prin stabilirea variabilelor de iniţializare DB_NAME – numele local al bazei de date (de exemplu test), DB_DOMAIN – locaţia bazei de date în structura reţelei (de exemplu oracle.ub.ro)

Analiza şi stabilirea datelor din fişierul de iniţializare Stabilirea setului de caractere pentru stocarea datelor. Dacă utilizatori diferiţi utilizează seturi de caractere diferite (de exemplu englez şi român) atunci trebuie ales un set care include toate caracterele. Se recomandă setul AL32UTF8 care acoperă tipurile de date XML.

Stabilirea dimensiunii blocului de date DB_BLOCK_SIZE. Acesta nu poate fi modificat ulterior. La crearea unor tablespace-uri noi poate fi stabilită şi o altă dimensiune a blocului de date.

Stabilirea dimensiunii pentru tablespace sistem SYSAUX. Acesta conţine date auxiliare din tablespace SYSTEM (facilităţi extensii).

Stabilirea unui tablespace implicit pentru utilizatori pentru a evita accesul utilizatorilor la tablespace SYSTEM. Automat se creează tablespace USERS.

Planificarea realizării unui tablespace pentru segmentele UNDO.

Gheorghe Hazi


proiectarea unui sistem de salvări şi restaurări pentru evitarea pierderii datelor.

Studierea opţiunilor de lansare şi oprire a unei instanţe ORACLE.

3. Paşii de lucru Se vor genera scripturile de creare a unei baze de date utilizând DBCA. 1. Se lansează Oracle DBCA din start menu:

2. Se apasă Continuare la pagina de bun venit. Se selectează opţiunea

Create a Database. Se apasă Continuare.

3. Se selectează tipul de bază de date: General Purpose. Se apasă Continuare.

Gheorghe Hazi


Utilizarea altor tipuri de baze de date se face după cum urmează:

• Data Warehouse – pentru stocarea unor informaţii de volum mare şi cu mişcare lentă, ca de exemplu: informaţii de cercetare sau marketing, situaţia plăţilor de taxe şi impozite, caracteristici tehnice ale instalaţiilor

• Transaction Processing – pentru stocarea unor informaţii cu viteză de modificare mare, de volum relativ mic: plăţi pentru magazine, plăţi salarii, gestiunea bancomatelor.

4. Se tastează numele bazei de date:

În cazul bazelor de date accesate via internet se recomandă să se adauge în numele global şi domeniul, de exemplu oracle.ub.ro. 5. Se setează modul în care se face managementul bazei de date: EM

local sau la distanţă. Se apasă Continuare.

Gheorghe Hazi


6. Se setează parolele pentru administratorii bazei de date:

SYS – proprietarul bazei de date cu privilegiul SYSDBA SYSTEM – administrator al bazei de date DBSNMP – utilizator folosit de agentul SNMP (de obicei via OEM) SYSMAN – administrator OEM 7. Se stabileşte mecanismul de administrare a spaţiilor de stocare:

Utilizarea ASM este necesară şi utilă în cazul serverelor cu zeci, sute de

discuri şi permite alocarea automată a spaţiului pe un grup de discuri. 8. Se stabileşte locaţia fişierelor bazei de date:

Gheorghe Hazi


Prin apăsarea butonului File Location Variables se poate vedea care sunt

locaţiile implicite:

9. Se stabileşte locaţia pentru Flash Recovery Area (de preferinţă alt disc

al serverului), spaţiu utilizat pentru backup şi recuperarea datelor.

Nu se activează modul arhivare pentru fişierele jurnal. 10. Se setează generarea schemelor test (Sample schemas)

Gheorghe Hazi


11. Se stabileşte dimensiunea memoriei alocate şi principalele

caracteristici: a. Se stabileşte dimensiunea memoriei alocate ca procent din valoarea memoriei SO (implicit 40%):

Se va vizualiza şi distribuţia memoriei prin apăsarea butonului „Show

Memory Distribution”:

Valoarea SGA Size poate fi stabilită/modificată prin variabila de

iniţializare SGA_TARGET, iar valoarea PGA Size poate fi stabilită/modificată prin variabila de iniţializare PGA_AGGREGATE.

b. Se stabileşte numărul maxim de procese admis în sistem.

Gheorghe Hazi


c. Se stabileşte seturile de caractere ale bazei de date.

Pentru ţara noastră, se recomandă setul de caractere EE8ISO8859P2 sau

EE8MSWIN1250. National Charater Set este o alternativă de utilizare a setului de caractere

care permite stocarea caracterelor Unicode în baza de date (dacă acel caracter nu există deja).

d. Se stabileşte modul de conectare a utilizatorilor la baza de date: alocare

individuală a memoriei sau o coadă de aşteptare:

Gheorghe Hazi


Conectarea se poate face: - Dedicated Server Mode – alocare individuală a memoriei - Shared Server Mode – clienţii utilizează zone comune de memorie

aşteptând la coadă servirea 12. Se editează, ca nume şi loc de stocare, fişierele bazei de date (crearea

de noi fişiere nu este posibilă în varianta în care s-a utilizat un template).

13. Se finalizează procesul stabilind generarea unor scripturi sau crearea

efectivă a bazei de date:

Gheorghe Hazi


Se va seta numai generarea scripturilor pentru crearea bazei de date. Se

afişează un ecran de confirmare la care se apasă OK.

4. Vizualizarea scripturilor şi editarea acestora Sripturile generate se regăsesc în calea

D:\oracle\product\10.2.0\admin\testub\scripts. Se vizualizează scripturile începând cu testub.bat.

Se editează scriptul CloneDBCreation.sql astfel încât dimensiunea tablespace USERS să fie 100 MB: ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HAZI\USERS01.DBF' RESIZE 100M;

Gheorghe Hazi


LUCRAREA 5. MANAGEMENTUL SPAŢIILOR DE STOCARE DIN BAZA DE DATE

1. Scopul lucrării Lucrarea are drept scop însuşirea metodelor şi procedurilor pentru managementul spaţiilor de stocare a datelor din baza de date.

2. Stocarea datelor în baza de date Oracle

Stocarea informaţiilor în baza de date Oracle poate fi privită din două puncte de vedere:

- structura fizică - structura logică Structura fizică conţine fişiere ale sistemului de operare având ca unitate de măsură blocul. Acesta are dimensiuni dependente de sistemul de operare: 4 kB în Windows, 1 kB în sistemele Unix. Structura logică a bazei de date cuprinde unităţi numite tablespace.

Fiecare tablespace are mai multe blocuri logice Oracle. Variabila de iniţializare DB_BLOCK_SIZE

specifică cât este de mare un bloc logic. El are dimensiunea cuprinsă înre 2 kB şi 32 kB. Dimensiunea implicită este 8 kB. Mărimea blocului logic Oracle este un multiplu al blocului fizic al sistemului de operare. Un număr de blocuri logice Oracle, continue, formează o extensie. Un set de extensii

Figura 1 Structura fizică şi logică a datelor

Gheorghe Hazi


formează un segment. Blocul logic Oracle este unitatea de măsură minimă pentru operaţii I/O. Când o tabelă este creată, este automat creat şi un segment pentru stocarea acesteia. Tablespace este o colecţie de segmente. O tabelă conţine rânduri şi coloane. Se poate întâmpla ca un rând să nu fie stocat în întregime în acelaşi loc.

Blocurile Oracle conţin următoarele: • Block header: Conţine tipul segmentului (de exemplu tabelă sau index), adresa blocului de date, directoarea tabelului, directoarea cu rânduri şi zona de tranzacţii. Headerul începe de la începutul blocului. • Rândurile de date: Valorile actuale ale rândurilor. Datele încep de la sfârşitul rândului. • Spaţiu liber: Este repartizat la mijlocul blocului. Iniţial spaţiul liber este contiguuu. Totuşi, ştergerea şi modificarea înregistrăilor poate fragmenta acest spaţiu.

3. Tablespace şi fişierele de date Tablespace şi fişierele de date prezintă următoarele diferenţe:

• Baza de date Oracle cuprinde una sau mai multe unităţi logice de stoacre numite tablespace, care conţin datele din baza de date.

Figura 2 Stocarea datelor în tabele

Gheorghe Hazi


• Fiecare tablespace conţine unul sau mai multe fişiere numite fişiere de date, care au structura potrivit sistemului de operare.

• Informaţiile din baza de date sunt stocate în fişiere de date care constituie tablespace ale acesteia. De exemplu, cea mai simplă bază de date (în structura Oracle 10g) trebuie să aibă două tablespace – SYSTEM şi SYSAUX – fiecare cu câte un fişier. O altă bază de date poate avea 3 tablespace, fiecare având câte două fişiere de date (în total 6 fişiere de date). O bază de date simplă poate avea până la 65534 de fişiere.

Oracle asigură managemetul automat al următoarelor categorii de fişiere:

• Tablespace-uri • Fişiere jurnal • Fişierele de control • Arhiva fişiere jurnal • Fişiere backup

Dacă parametrul DB_CREATE_FILE_DEST este specificat, crearea unui tablespace se face automat în acest director.

Managementul spaţiului din tablespace poate gestionat în două moduri:

• Locally managed tablespaces – extensiile sunt gestionate în tablespace printr-un bitmap. Fiecare bit corespunde unui bloc sau unui grup de blocuri. Dacă o extensie este alocată sau sau eliberată Oracle schimbă valoarea pentru a indica starea nouă.

• Dictionary-managed tablespaces: - Extensiile sunt sunt gestionate prin dicţionarul bazei de date.

Utilizarea ultimului fel de gestiune este menţinută de Oracle 10g pentru compatibilitate cu versiunile anterioare.

4. Crearea şi obţinerea de informaţii despre tablespace

Prin apăsarea link-ului tablespace se pot obţine multiple informaţii despre spaţiile de stocare.

Gheorghe Hazi


Crearea unui nou tablespace se face prin selecţia butonului Create din

meniul tablespace:

Figura 3 Selecţia modulului tablespace

Figura 4 Activarea fazei de creare a unui

Figura 5 Creare tablespace test

Gheorghe Hazi


După tastarea numelui tablespace trebuie specificat şi fişierul de date aferent:

De asemenea trebuie specificat dimensiunea spaţiului de extensie. Crearea tablespace se poate face şi din SQL*Plus: CREATE SMALLFILE TABLESPACE "TEST" DATAFILE

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HAZI\test' SIZE 100M AUTOEXTEND ON NEXT 100K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

Extensiile în modul locally manager tablespace pot fi alocate în două moduri:

• Automatic – specifică că dimensiunea extensiilor este gestionată de Oracle (nu se poate utiliza pentru tablespace temporar).

• Uniform – Extensiile sunt uniforme, cu valoarea implicită de 1MB. Pentru tablespace temporar extensiile sunt totdeauna de acest fel.

Tablespace în forma preconfigurată a bazei de date (clonare) conţine: • SYSTEM – Este utilizată de Oracle pentru gestionarea bazei de date.

Acest tablespace conţine dicţionarul bazei de date. Obiectele din tablespace sunt în schema SYS şi sunt accesibile numai

Figura 6 Stabilirea

fişierului de date

Figura 7 Tablespace în baza de date

Gheorghe Hazi


administratorilor. • SYSAUX – Este un tablespace auxiliar utilizat, în mod deosebit, de

către Enterprise Manger. • TEMP – este utilizat pentru manevre, în mod deosebit pentru

executarea SQL care cere crearea unor segmente temporare (de exemplu în cazul sortărilor). De obicei este ataşat fiecărui utilizator ca tablespace temporar.

• UNDOTBS1 - Este utilizat pentru stocarea tranzacţiilor nedefinitive. Dacă baza de date are activată Automatic Undo Management trebuie să existe un singur tablespace în acest scop. Este creat o dată cu baza de date.

• USERS – Este utilizat pentru stocarea obiectelor utilizatorilor obişnuiţi. Trebuie să precizăm că pentru SYS şi SYSTEM, tablspace implicit rămâne SYSTEM.

• EXAMPLE – Conţine scheme exemplu care pot fi generate o dată cu crearea bazei de date.

Modificarea unui tablespace se face fie prin comenzi SQL sau prin comenzi OEM.

Modificările posibile sunt: • Redenumirea unui tablespace • Modificarea stării: Read Write, Read Only, Offline. • Redimensionarea tablespace prin adăugarea unui fişier de date sau

prin redimensionarea fişierelor existente.

Figura 8 Editarea stării unui tablespace

Gheorghe Hazi


Se pot afişa informaţiile despre segmentele din tablespace: Sunt arătate toate segmentele din tablespace, eventual filtrate după diverse criterii.

Prin apăsarea butonului Extent Map se afişează o hartă bitmap pentru

tablespace.

Figura 9 Afişarea conţinutului unui tablespace

Figura 10 Segmente într-un tablespace

Gheorghe Hazi


5. Aplicaţii 5.1. Se for efectua aplicaţii cu OEM şi cu SQL Plus. Se lansează OEM cu

c-da: http://192.168.4.45:1158/em. Utilizatorii se vor conecta ca SYS (SYSDBA) cu parola george. 5.2. Se vor vizualiza informaţii despre tablespace EXAMPLE. Administration > Database Administration > Storage > Tablespaces.

a) Care sunt limitele pentru spaţiul disponibil de la care sunt baza de date emite mesaje de atenţionare?

b) Din lista ascunsă Actions se selectează Show Tablespace Contents şi se apasă Go.

c) Câte segmente are tablespace? d) Se filtrează afişarea tabelelor. Ce tabelă ocupă cel mai mare spaţiu? e) Se va afişa harta bitmap a tablespace. f) Se va crea un nou tablespace cu numele nume_student, cu următoarele

caracteristici: Locally managed, permanent, read write, smallfile, 5MB. g) Se va reţine într-un fişier cu extensia SQL c-da de generare a tablespace. h) Se redimensionează fişierul de date al tablespace nume_student la 10M.

Care este c-da SQL? i) Se execută scriptul de mai jos, în care se înlocuieşte student cu numele

studentului. set echo on conn system/george@ubc drop user student cascade; create user student identified by test quota unlimited on test; alter user student temporary tablespace temp; grant connect to student; grant create any table to student; conn student/test@ubc create table x (a char(1000)) tablespace test / insert into x values('a') / insert into x select * from x / insert into x select * from x /

http://192.168.4.45:1158/em�

Gheorghe Hazi


insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / insert into x select * from x / commit / Quit j) Se verifică spaţiul din tablespace test. k) Se şterge utilizatorul student şi obiectele sale. SQL> drop user student cascade;

Se şterge tablespace test cu OEM

Gheorghe Hazi


LUCRAREA 6. MANAGEMENTUL OBIECTELOR DINTR-O SCHEMĂ (PARTEA I-A)

1. Scopul lucrării Lucrarea are drept scop însuşirea metodelor şi procedurilor pentru crearea şi gestionarea obiectelor (tabele, indecşi, vederi, etc.) aflate în proprietatea unui utilizator.

2. Stocarea obiectelor în baza de date Oracle Obiectele din baza de date, în special tabele de date, sunt în gestiunea unui utilizator. Schema reprezintă o colecţie de obiecte care sunt în proprietatea unui singur utilizator. De multe ori, pentru bazele de date de producţie, utilizatorul nu reprezintă o persoană ci o aplicaţie. Obiectele din schemă sunt structuri logice care se referă direct la informaţiile din baza de date. O schemă din baza de date cuprinde structuri cum ar fi: - tabele - vederi - indecşi - secvenţe Obiectele din schemă pot fi create şi manipulate utilizând SQL sau Oracle Enterprise Manager. Dacă utilizaţi OEM, se pot genera automat şi instrucţiunile SQL aferente. Obiectele dintr-o schemă pot fi stocate şi în tablespace diferite. La crearea bazei de date, sunt generate câteva scheme de obiecte:

• schema SYS – Conţine dicţionarul bazei de date şi, se recomandă, ca informaţiile utilizatorilor să nu fie stocate în această schemă

• schema SYSTEM – Conţine tabele suplimentare şi vederi care gestionează informaţii legate de administrarea bazei de date

• schema HR – este o schemă (Human Resources) simplă de resurse umane, utilizată pentru iniţierea utilizatorilor

Gheorghe Hazi


• schema OE – este o schemă (Order Entry) de complexitate medie. O multitudine de tipuri de date sunt disponibile în schema OE. Subschema OC (Online Catalog) este o colecţie de obiecte specifice unei baze de date relaţionale.

• schema QS – este utilizată (Queued Shipping) pentru demonstraţii în modulul Oracle Advanced Queuing.

• schema PM (Product Media) – este dedicată pentru tipurile de date media (imagine, sunet, video).

• schema SH (Sales History) – permite demonstraţii cu date de volum mare.

3. Accesarea obiectelor unui utilizator Obiectele unui utilizator pot fi vizualizate din SQL Plus utilizând vederea

USER_OBJECTS:

OBJECT_NAME OBJECT_ID

OBJECT_TYPE CREATED TIMEST

AMP STATU

S

PICHIU 52652 TABLE 19-03-2008 2008-03-19:09:38:00

VALID

PAL_ALINA 52653 TABLE 19-03-2008 2008-03-19:09:40:41

VALID

NUME_EU 52654 TABLE 19-03-2008 2008-03-19:09:40:58

VALID

NUME_STUDENT 52670 TABLE 19-03-2008 2008-03-19:10:42:11

VALID

CIUMEGU 52658 TABLE 19-03-2008 2008-03-19:09:46:46

VALID

DANILA_ANCA 52685 TABLE Mai comodă este utilizarea OEM:

Gheorghe Hazi


Se ajunge uşor la obiectele dintr-o schemă din zona Schema al OEM,

pagina Database Administration. După selectarea unui tip de obiect, din pagină, se pot afişa obiectele de tipul respectiv al unui utilizator sau se poate căuta un obiect anume. Denumirea obiectelor din baza de date: Obiectele pot fi referite prin utilizarea ghilimelelor ”Obiect”, însă, în acest caz notaţiile sunt senzitive la litere mari şi mici. De exemplu dacă vrem să interogăm tabelul ”Local Temp”: SQL> select * from "Local Temp"; TEMP_DATE LO_TEMP HI_TEMP --------- ---------- ---------- 01-DEC-03 30 41

Dacă comanda este dată altfel, se generează o eroare: SQL> select * from "local temp"; select * from "local temp" * ERROR at line 1:

ORA-00942: table or view does not exist La notaţia fără ghilimele, literele mici sunt convertite în litere mari. Notaţiile fără ghilimele pot conţine şi alte caractere cum ar fi _, $, #. În

notaţia cu ghilimele, numele de obiecte pot conţine caractere speciale, inclusiv spaţiu.

Tipuri de date din tabele Datele din tabelele Oracle pot fi:

• CHAR(size [BYTE|CHAR]) - Şir de caractere de lungime fixă. Lungimea maximă 2000. Lungimea minimă şi implicită 1 caracter.

Figura 1 Vizulizarea obiectelor cu OEM

Gheorghe Hazi


• VARCHAR2(size [BYTE|CHAR]) – Şir de caractere de lungime variabilă, cu lungimea maximă size. Valoarea maximă a lungimii este de 4000 bytes.

• DATE – Câmp de tip dată cu valori între 1 ianuarie 4712 înainte de Cristos şi până la 31 decembrie 9999 după Cristos. Câmpurile de tip dată reţin şi ora, minutul şi secundele.

• NUMBER(p,s) – Câmpuri numerice cu lungimea p şi s zecimale. • BINARY_FLOAT – Câmpuri numerice în virgulă mobilă pe 32 biţi,

ocupând 5 bytes. • BINARY_DOUBLE – Câmpuri numerice în virgulă mobilă pe 64

biţi, ocupând 9 bytes. • FLOAT(p) – Câmpuri în virgulă mobilă cu lungimea binară p.

Implicit p=128 biţi sau 38 cifre zecimale. • INTEGER(p) – este echivalent cu NUMBER(p,0) • NCHAR(size) – Câpuri unicode care permit introducerea

caracterelor naţionale (ş, ţ..). Setul naţional de caractere (specific unei ţări) este stabilit la crearea bazei de date. Lungimea maximă 2000 de caractere.

• NVARCHAR2(size [BYTE|CHAR]) – Câmpuri unicode de lungime variabilă. Lungimea maximă 4000 caractere.

• LONG – Câmp de tip caracter de lungime mare, până la 2 GB sau 231-1 bytes. Acum se recomandă câmpurile tip LOB.

• LONG RAW – Câmp binar nestructurat de până la 2 GB. • RAW(size) – Câmp binar nestructurat de dimensine size, lugimea

maximă fiind de 2000 bytes. • ROWID – adresă pe 64 de bytes (şir de caractere) a unei înregistrări

în tabele. ROWID este şi o pseudocolană în tabele: SQL> 1 select employee_id, last_name, rowid from employees 2 order by last_name; Tastati <ENTER> EMPLOYEE_ID LAST_NAME ROWID ----------- ------------------------- ------------------ 174 Abel AAAMg6AAFAAAABYABK 166 Ande AAAMg6AAFAAAABYABC 130 Atkinson AAAMg6AAFAAAABYAAe 105 Austin AAAMg6AAFAAAABYAAF

Gheorghe Hazi


204 Baer AAAMg6AAFAAAABUAAG 116 Baida AAAMg6AAFAAAABYAAQ • UROWID(size) – adresă pe 64 de bytes în cazul tabelelor organizate

index. Lungimea implicită şi maximă este de 4000 bytes. • BLOB – Câmp (binary large object) binar pentru obiecte de mari

dimensiuni, până la 4 GB. • CLOB – idem, dar variabilă de tip caracter. • NCLOB – idem, dar conţine caractere unicode (naţionale) • BFILE – referinţe la fişiere binare externe de până la 4GB. • TIMESTAMP – memorează câmpuri de tip dată, inclusiv zecimi de

secundă.

4. Crearea şi modificarea unei tabele

Se poate face cu OEM şi prin comenzi SQL, inclusiv prin programe scrise în diverse medii de programare.

Din OEM crearea unei tabele se poate face din pagina schema, Tables.

Paşii de urmat în varianta OEM sunt: 1. Se dă clic pe <Tables>, în regiunea <Schema> din pagina de

administrare OEM. Va apare pagina de tabele. 2. Dacă se cunoaşte numele schemei se introduce numele în câmpul în

câmpul aferent. Dacă nu se apasă pe iconul cu lanternă şi se face browse pe scheme din baza de date.

3. Se apasă <Create>. Se afişează Create Table şi se va alege tipul de organizare.

4. Se acceptă tipul implicit Standard, Heap Organized şi se apasă <Continue>. Apare pagina de creare a tabelei (figura 2).

Figura 2 Crearea unei tabele cu OEM

Gheorghe Hazi


5. Se introduce numele tabelei în câmpul Name. 6. Se introduce schema sau se alege apăsând iconul cu lanternă 7. Se introduce tablespace în câmpul Tablespace sau se alege apăsând

iconul alăturat. 8. În coloanele tabelului se introduc numele câmpurilor şi tipul

acestora. 9. Se apasă <OK>. Se afişează un mesaj indicând crearea cu succes a

tabelei. Se poate utiliza SQL, comandă care poate fi vizualizată şi cu butonul

<Show SQL>: CREATE TABLE HR.CONCEDII (EMPLOYEE_ID NUMBER(6), DATA_INCEPUT DATE, DATA_SFARSIT DATE, CONSTRAINT CONDEDII_PK PRIMARY KEY

(EMPLOYEE_ID) VALIDATE, CONSTRAINT CONDEDII_FK_MARCA FOREIGN KEY (EMPLOYEE_ID) REFERENCES HR.EMPLOYEES

(EMPLOYEE_ID) VALIDATE, CONSTRAINT DATA_INCEPUT_CK CHECK (Data_inceput

between to_date('01-01-1950','DD-MM-YYYY') and to_date('31-12-2150','DD-MM-YYYY')) VALIDATE ,

CONSTRAINT DATA_SFARSIT_CK CHECK (Data_sfarsit between to_date('01-01-1950','DD-MM-YYYY') and to_date('31-12-2150','DD-MM-YYYY')) VALIDATE)

TABLESPACE EXAMPLE Modificarea unei tabele se poate face în acelaşi fel. De exemplu pentru

adăugarea unei coloane: 1. În pagina Table, se selectează tabela care se doreşte a fi moficată şi se

apasă <Edit>. 2. În pagina Edit se apasă Add 5 Table Columns şi vor apare coloane

goale care pot fi adăugate 3. Se introduc coloanele suplimentare 4. Se apasă <Apply> şi va apare un mesaj de confirmare.

Gheorghe Hazi


5. Restricţii în tabele Pentru a înţelege modul de funcţionare al restricţiilor se prezintă tabelele

din schema HR. Restricţiile pot fi:

• NOT NULL – câmpul trebuie completat obligatoriu. În exemplul de mai sus câmpul LAST_NAME are această restricţie.

• UNIQUE key – Câmpul trebuie să aibă o valoare unică în tot tabelul. În exemplul de mai sus, adresa de e-mail are această restricţie.

• PRIMARY KEY – Fiecare tabelă poate să aibă o singură cheie primară. Cheia poate fi formată dintr-una sau mai multe coloane din tabel. Cheia primară trebuie să satisfacă restricţiile NOT NULL şi UNIQUE. De obicei se utilizează indecşi pentru cheia primară.

• FOREIGN KEY – Se utilizează în bazele de date relaţionale, cum este Oracle, pentru tabele care au în comun o coloană. Se utilizează în tabele pentru a se valida că o anumită cheie, de exemplu marca unui salariat, există în tabela în care ea este definită. Tabela în care o cheie este definită se numeşte tabelă părinte, iar cea care o utilizează se numeşte tabel copil. În exemplul de mai sus, în tabela EMPLOYEES, câmpul DEPARTMENT_ID este cheie străină în tabela DEPARTMENTS. În aceeaşi categorie intră şi referirea unei chei copil în aceeaşi tabelă ca şi cheia părinte (self-referential integrity), de exemplu câmpul MANAGER_ID din tabela EMPLOYEES.

Figura 3 Schema HR cu restricţii

Gheorghe Hazi


• CHECK – Se verifică că valorile câmpului se încadrează în anumite limite. De exemplu, în tabela EMPLOYEES s-a impus restricţia SALARY > 0.

Adăugarea de restricţii se poate face cu OEM (figura 4) sau prin SQL.

Paşii de urmat în cazul OEM sunt: 1. Se selectează o tabelă din pagina de tablele şi se apasă <Edit>. 2. Se selectează tabul <Constraints> şi sunt afişate toate restricţiile

definite (figura4) 3. Se selectează tipul de restricţie din lista ascunsă (dreapta sus) şi se

apasă <Add> 4. Se setează restricţia respectivă. Prin SQL, adăugarea restricţiei salariu > 0 în tabela MY_EMPLOYEE: ALTER TABLE HR.MY_EMPLOYEE ADD ( CONSTRAINT SALARY_CK CHECK (salary > 0) VALIDATE ) Nerespectarea restricţiilor la înserarea sau modificarea unei înregistrări

conduce la erori şi la mesaje potrivite. Restricţiile se pot află în una din cele 4 stări din figura 5, în funcţie de

modul în care ele acţionează. ENABLE asigură că orice valoare nouă este verificată la introducere. VALIDATE asigură că toate înregistrările existente în tabelă respectă restricţia.

Figura 4 Adăugarea de restricţii cu OEM

Gheorghe Hazi


• DISABLE NOVALIDATE – Se pot introduce date care nu respectă

restricţiile, însă restricţia nu va putea fi reactivată până când nu sunt corectate înregistrările care o încalcă.

• DISABLE VALIDATE – În această situaţie orice modificare a coloanei respective nu se poate face întrucât aceasta nu este verificată. Se utilizează la ştergerea indecşilor.

• ENABLE NOVALIDATE – verifică datele la introducere sau modificare dar nu asigură că datele existente respectă restricţia.

• ENABLE VALIDATE – starea normală şi implicită a restricţiilor. Totodată, în funcţie de momentul validării, restricţiile pot fi validate:

• DEFERRABLE – validarea se face la comiterea tranzacţiei (COMMIT). Trecerea la această stare se face cu c-da SET CONSTRAINT restricţie | ALL DEFERRED (numai pentru următoarea tranzacţie)

• NOT DEFERRABLE – validarea se face la sfârşitul instrucţiunii SQL

• Dacă restricţia este DEFERRABLE atunci aceasta poate fi INITIALLY IMMEDIATE sau INITIALLY DEFERRED. În cazul INITIALLY IMMEDIATE verifică restricţia la sfârşitul fiecărei instrucţiuni SQL. În cazul INITIALLY DEFERRED restricţia se verifică la sfârşitul tranzacţiei (COMMIT).

Implicit restricţiile sunt ENABLE VALIDATE şi NOT DEFERRABLE. Exemple de adăugare a restricţiilor prin instrucţiuni SQL:

ALTER TABLE countries ADD (UNIQUE(country_name) ENABLE NOVALIDATE);

ALTER TABLE employees ADD CONSTRAINT pk PRIMARY KEY (employee_id);

Figura 5 Starea

restricţiilor

Gheorghe Hazi


Structura unui table (câmpuri, restricţii, indecşi) poate fi văzută din OEM apăsând butonul <View>. Vizualizarea datelor din tabel se face alegând opţiunea <View data> din lista ascunsă Actions.

5. Acţiuni în machete tabele OEM Alte activtăţi care pot fi executate din machete Tabele din OEM sunt:

• Create Like – Se poate crea o tabelă care are aceeaşi structură cu tabela curentă. Se pot efectua modificări în structura tabelei (ştergerea sau adăugarea de coloane) înainte de creare. Este obligatorie modificarea numelor pentru restricţii.

• Generate index – Se utilizează pentru a crea indecşi în tabele. • Generate DDL – Generează instrucţiunea pentru crearea tabelei

respective, existente. Se poate utiliza/modifica pentru alte tabele. • Grant privilege – Când o tabelă este creată, numai utilizatorul care a

creat-o are drepturi asupra ei. Se pot da drepturi utilizatori pentru a efectua citirea sau modificarea datelor din tabelă.

• Show Dependencies – Afişează obiectele de care tabela depinde precum şi obiectele care depind de tabela curentă.

• View Data – Afişează datele din tabelă.

6. Ştergerea tabelelor Se face din OEM cu butonul <Delete With Options> sau cu c-da SQL: DROP TABLE [schema.] table [CASCADE CONSTRAINTS] [PURGE] Ştergerea unui table conduce la ştergerea: • Datelor • Structura tabelei • Trigerele din baza de date aferente (la efectuarea de instrucţiuni DML) • Indecşii aferenţi • Privilegiile aferente Utilizarea clauzei PURGE conduce la ştergerea definitivă a unui tabel. În

lipsa ei tabelul este mutat în coşul de gunoi al bazei de date (Recycle bin). La ştergerea cu OEM clauza nu este utilizată. Pentru a utiliza coşul de gunoi trebuie ca acesta să fie activat:

Gheorghe Hazi


SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 184549376 bytes Fixed Size 1300928 bytes Variable Size 157820480 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320; System altered. SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912; System altered. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered.

Recuperarea unei tabele din coşul de gunoi se face cu c-da: FLASHBACK TABLE <object_name> TO BEFORE DROP

[RENAME TO <new_table_name>]; C-da PURGE RECYCLEBIN curăţă coşul de gunoi. Opţiunea CASCADE CONSTRAINTS permite ştergerea restricţiilor

dependente de tabelul şters. Ştergerea tuturor înregistrărilor dintr-un tabel cu păstrarea structurii

acestuia se face cu c-da: TRUNCATE TABLE [schema.] table

Dacă se dă comanda TRUNCATE, indecşii aferenţi tabelului sunt şi ei trunchiaţi.

Gheorghe Hazi


7. Indecşi Sunt structuri opţionale asociate tabelelor. Sunt creaţi pentru a creşte

viteza de lucru la modificarea sau interogarea tabelelor. Pot fi creaţi pentru unul sau mai multe coloane. După ce au fost creaţi, indecşii sunt întreţinuţi automat de Oracle. Indecşii permit accesul direct la un rând din tabele.

Figura 6 Tabele de

index

Figura 7 Indecşi B-Tree

Gheorghe Hazi


Există două tipuri de indecşi:

- B-tree – în care indecşii sunt stocaţi într-un sistem arborescent (figura 7)

- Bitmap – se foloseşte o hartă bitmap, cu câte un bit pentru fiecare index

Se recomandă indecşii tip bitmap pentru coloanele cu puţine valori distincte, şi indecşii B-Tree în caz contrar. Crearea indecşilor se poate face din OEM prin precizarea ordinii câmpurilor (figura 8). C-da SQL pentru crearea unui index în tablea MY_EMPLOYEE după nume, prenume:

CREATE INDEX "HR"."MY_INDEX" ON "HR"."MY_EMPLOYEE" ("FIRST_NAME", "LAST_NAME")

8. Lucrări de efectuat

1. Se va realiza conectarea la baza de date ca utilizator oracle_student, cu iSQL Plus, cu userul system, parola george ( http://192.168.4.45:5560/isqlplus). Se copiază scriptul lab_06_01.sql de pe calculatorul \\192.168.4.155 pe calculatorul local şi se editează, înlocuindu-se nume_student cu numele dvs.

2. Se execută scriptul lab_06_01.sql.

Figura 8 Crearea indecşi cu OEM

http://192.168.4.45:5560/isqlplus�

Gheorghe Hazi


3. Se lansează OEM ( http://192.168.4.45:1158/em) şi se face conectarea cu utilizatorul SYS şi parola george (AS SYSDBA).

4. Din OEM se va crea o tabelă, în schema nume_student şi tablespace USERS, PRODUCT_MASTER (nomenclator de produse), cu structura:

PRODUCT_ID number(7). Cheie primară. (Nume restricţie: PK_PROD) PRODUCT_NAME varchar2(50) cu restricţia Not NULL CODE varchar2(10) cu restricţia Not NULL. REORDER_THRESHOLD number(5) cu restricţia de tip CHECK ca

numărul să fie pozitiv (Nume restricţie: CHK_REORDER) COST number(5,2) PRICE number(5,2) 5. Se revine în iSQL Plus şi face reconectarea cu utilizatorul

nume_student şi parola student. Se copiază scriptul lab_06_02.sql pe calculatorul local. Se creează tabelul PRODUCT_ON_HAND prin lansarea scripului lab_06_02.sql. Înainte de execuţie scriptul se editează pentru a modifica numele utilizatorului şi a pentru corectarea erorilor.

Structura tabelei este următoarea: PRODUCT_ID number(7), FK în tabela PRODUCT_MASTER, câmpul

PRODUCT_ID. QUANTITY number(5) WAREHOUSE_CITY varchar2(30) LAST_UPDATE date 6. Se creează tabelul OBSOLETE_PRODUCTS cu structura: PRODUCT_ID number(7). Cheie primară. PRODUCT_NAME varchar2(50), cu restricţia NOT NULL CODE varchar2(20) cu restricţia NOTT NULL COST number(5,2) PRICE number(5,2) Având în vedere structura foarte asemănătoare cu tabela

PRODUCT_MASTER se va utiliza SQL generat automat pentru acea tabelă (din OEM), prin Action - Generate DDL. Se copiază DDL în iSQL Plus şi se editează instrucţiunea SQL, după care se execută.

7. În tabela OBSOLETE_PRODUCTS se creează un index OBS_CODE.

http://192.168.4.45:1158/em�

Gheorghe Hazi


Se va alege tipul potrivit de index: B-tree sau bitmap, după câmpul CODE.

8. Se creează un index PROD_NAME_CODE, din câmpurile PRODUCT_NAME şi CODE din tabela PRODUCT_MASTER. Se va utiliza scriptul lab_06_03.sql. Se vor corecta mai întâi erorile din script. Executarea scriptului se va face în iSQL Plus cu utilizatorul nume_student.

9. Se creează un index combinat din câmpurile PRODUCT_ID şi QUANTITY din tabela PRODUCT_ON_HAND. Indexul se va numi POH_PROD_ID_QTY.

10. Se modifică tabela PRODUCT_MASTER prin adăugarea a două coloane PRIMARY_SOURCE şi SECONDARY_SOURCE, ambele de tipul VARCHAR2(50). Care este c-da SQL care realizează acest lucru?

11. Se înserează date în tabelul PRODUCT_MASTER prin execuţia scriptului lab_06_04a.sql din iSQL Plus executat cu userul nume_student. Aveţi răbdare, durează ceva...

12. Se execută scriptul lab_06_04b.sql de 5 ori şi se notează de fiecare dată timpul de execuţie. La sfârşit se face media. Se modifică tablelul PRODUCT_MASTER şi se adaugă un index CODE_INDEX pe câmpul CODE . Se execută iar de 5 ori scriptul, notându-se timpul de execuţie. Se face media şi se compară rezultatele.

Gheorghe Hazi


LUCRAREA 7. MANAGEMENTUL OBIECTELOR DINTR-O SCHEMĂ (PARTEA II-A). ADMINISTRAREA

UTILIZATORILOR

1. Scopul lucrării Lucrarea are drept scop însuşirea metodelor şi procedurilor pentru crearea şi gestionarea vederilor şi a secvenţelor, ca obiecte ale unei baze de date. În partea a doua a lucrării studenţii vor căpăta deprinderi privind gestionarea utilizatorilor şi a drepturilor acestora.

2. Vederi în baza de date Oracle Vederile sunt tabele virtuale care conţin informaţii din una sau mai multe tabele. Ele seamănă cu nişte proceduri stocate care pot conţine interogări complexe. În acest fel se pot ascunde unele coloane din tabele sau se pot interoga datele din baza de date într-o altă formă. Crearea vederilor se poate face, la fel ca şi tabelele, din EM sau utilizând comenzi SQL.

Figura 1 Crearea unei vederi cu EM

Gheorghe Hazi


C-sa SQL echivalentă este: CREATE VIEW "HR"."STAFF_NUME" AS select employee_id,

first_name||' '||last_name as name, salary

from employees where salary>10000; Dacă vederea există se va utilize varianta CREATE OR REPLACE VIEW.

3. Secvenţe Permit generarea automată a unor numere întregi, după o anumită regulă. Secvenţele au un nume care este referit pentru a obţine valorile succesive ale numerelor generate. Secvenţele nu sunt asociate unei anumite tabele sau coloană. Numerele generate pot fi crescătoare sau descrescătoare. Diferenţa dintre două valori succesive poate avea orice valoare şi este stabilită la crearea secvenţei. Dacă limita superioară este limitată se poate seta ciclarea valorilor. Un număr generat este unic, adică el nu va mai fi generat, cu excepţia situaţiei în care plaja de valori este mică şi este setată ciclarea valorilor. Utilizarea unei zone de memorie cache în care se stochează valorile succesive conduce la creşterea vitezei de lucru.

Crearea unei secvenţe se poate face cu EM sau cu comenzi SQL. Comanda SQL este: CREATE SEQUENCE S_MARCA NOCYCLE ORDER CACHE 20 MAXVALUE 30000

MINVALUE 100 INCREMENT BY 1 START WITH 100

Numele secvenţei (S_MARCA în exemplul de mai sus) este necesar pentru utilizarea ei în viitor. Valoarea maximă poate avea valori până la 1027, la secvenţe crescătoare şi -1 pentru secvenţe descrescătoare. Valoarea minimă trebuie să fie mai mică sau egală ca valoarea iniţială. Ea poate fi 1 pentru secvenţele crescătoare şi -1026 la cele descrescătoare. Intervalul dintre două valori succesive poate avea valori de până la 28 de

Gheorghe Hazi


cifre, valoarea implicită fiind 1. Cycle Values indică ciclarea valorilor, începând din nou cu valoarea minimă dacă s-a atins valoarea maximă. Order Values este necesară în cazul Oracle database în varianta Real Application Clusters pentru a se asigura ordinea generării. Alocarea unei zone de memorie cache asigură creşterea vitezei de lucru. Utilizarea secvenţelor în aplicaţii se face prin următoarele două pseudocoloane:

• CURRVAL – returnează valoarea curentă a secvenţei • NEXTVAL – returnează valoarea următoare a secvenţei şi

incrementează contorul acesteia Secvenţele vor fi referite sub forma secvenţă.CURRVAL sau

secvenţă.NEXTVAL. SQL> select s_marca.nextval from dual; Tastati <ENTER> NEXTVAL ---------- 100 SQL> select s_marca.currval from dual; Tastati <ENTER> CURRVAL ---------- 100

Figura 2 Crearea unei

secvenţe cu EM

Gheorghe Hazi


4. Administrarea utilizatorilor

4.1. Definiţii Cont în baza de date – este o metodă de a organiza proprietatea şi accesul la obiectele bazei de date. Parola – este o metodă de autentificare în baza de date Oracle Privilegiu – un drept de a executa anumite instrucţiuni SQL sau de a accesa obiectele aflate în proprietatea unui alt utilizator. Rol – este un grup de privilegii care sunt acordate unui utilizator sau altor roluri. Profil – nume atribuit unui set de limite de a accesa resurse ale bazei de date. Cota – este spaţiul alocat unui utilizator într-un tablespace.

4.2. Conturi ale utilizatorilor Pentru a accesa baza de date, un utilizator trebuie să introducă o nume valid de utilizator şi o parolă. Fiecare utilizator are propriul cont în baza de date. Mai rar, utilizatorii împart în comun un cont în baza de date. În aceste situaţii, sistemul de operare sau aplicaţiile trebuie să asigure securitatea accesului la informaţii. Fiecare utilizator are:

• Un nume de utilizator unic – acesta nu poate depăşi 30 de caractere, nu poate conţine caractere speciale şi trebuie să înceapă cu o literă.

• O metodă de autentificare – uzual autentificarea se face prin parolă, dar există şi alte metode cum ar fi metoda biometrică (amprentă) sau prin card.

• Un tablespace implicit – aici vor fi plasate obiectele utilizatorului. Pentru fiecare tablespace trebuie specificată şi cota la care utilizatorul are dreptul.

• Un tablespace temporar – aici vor fi plasate obiectele temporare. • Profilul utilizatorului – setul de resurse (de ex. alocarea resurselor de

calcul) şi restricţiile legate de parolă (de ex. număr de încercări) ataşate utilizatorului.

• Starea contului – blocat sau deschis.

Gheorghe Hazi


4.3. Conturi predefinite

Există două conturi de administrare predefinite:

• SYS – are toate privilegiile din baza de date cu opţiunea ADMIN OPTION (adică poate acorda privilegiile şi altor utilizatori). Este proprietarul dicţionarului bazei de date aflat în tablespace SYSTEM. Pentru a se conecta ca SYS trebuie precizată obligatoriu şi clauza AS SYSDBA. Numai utilizatorii cu privilegiile SYSDBA şi SYSOPER pot porni sau opri baza de date.

• SYSTEM – are privilegiul DBA. Se recomandă ca operaţiile de rutină să nu se execute sub aceste conturi, ci să

se atribuie altor utilizatori aceste privilegii, pentru a putea fi monitorizate acţiunile asupra bazei de date.

Aceste două conturi nu pot fi şterse.

4.4. Crearea unui utilizator Se poate face cu EM sau prin comenzi SQL. În Enterprise Manager, se poate efectua toate operaţiile în legătură cu un utilizator. Se pot crea, modifica sau şterge utilizatorii bazei de date. Lansarea modulului se face cu Select Administration > Schema > Users & Privileges > Users, cu butonul <Create>. Câmpurile obligatorii sunt marcate cu steluţă.

Figura 3

Crearea unui utilizator cu EM

Gheorghe Hazi


C-da SQL este:

CREATE USER TEST PROFILE DEFAULT IDENTIFIED BY test DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK GRANT CONNECT TO TEST

Dacă nu sunt specificate tablespace şi temporary tablespace atunci acestea primesc valori implicite, definite de sistem (de obicei USERS şi TEMP). Metodele de autentificare sunt:

• Prin parolă – în acest caz autentificarea se face prin baza de date Oracle. Se recomandă ca la crearea unui utilizator să se forţeze Expire Password pentru a-l obliga să o modifice la prima logare. Parola este criptată la transferul ei prin reţea către server, utilizând algoritmul Data Encryption Standard.

• External – autorizarea se face de către sistemul de operare. Dacă se utilizează această metodă, trebuie setat parametrul OS_AUTHENT_PREFIX (implicit OPS$)cu un şir de caractere care va prefixa numele utilizatorilor din baza de date cu drept de acces. Dacă, de exemplu, utilizatorul sistemului de operare cu numele student încearcă să se conecteze la baza de date, aceasta verifică că există utilizatorul OPS$student în baza de date.

• Global – se utilizează împreună cu opţiunea Oracle Advanced Security şi reprezintă o validare mai puternică (poate utiliza amprenta digitală, card sau Oracle Internet Directory.

Administratorii bazei de date trebuie să aibă dreptul de a crea sau şterge fişiere ale sistemului e operare (prin proceduri specifice acestuia).

Pentru operaţii importante în BD (de ex. pornirea şi oprirea BD), se cere şi userul şi parola din sistemul de operare.

4.5. Modificarea stării unui utilizator

Din EM, pagina USERS, se poate face blocarea, deblocarea şi setarea caracteristicilor unui utilizator. Astfel prin lista ascunsa <Actions> se poate bloca sau debloca un utilizator, se poate genera c-da SQL de creare a utilizatorului sau se poate crea un utilizator cu caracteristici similare.

Gheorghe Hazi


Blocarea şi deblocarea unui utilizator se face cu c-zile SQL: ALTER USER HR ACCOUNT LOCK;

ALTER USER HR ACCOUNT UNLOCK; Setarea expirării unei parole se face cu c-da SQL: ALTER USER HR PASSWORD EXPIRE; Modificarea caracteristicilor unui utilizator se poate face din EM şi prin butonul <Edit>. În acest loc se poate modifica şi parola unui utilizator. C-da SQL este: ALTER USER HR IDENTIFIED BY XX; unde XX este parola nouă.

4.6. Privilegii Există două categorii de privilegii:

• System – executarea unor anumite acţiuni în baza de date (de exemplu modificarea stării bazei de date, crearea unui tablespace). Aceste privilegii pot fi acordate de un administrator (DBA) al bazei de date sau de un utilizator care are acest drept explicit. Există peste o sută de privilegii system, multe dintre ele având clauza ANY.

• Obiect – dreptul unui utilizator de a manipula obiectul respectiv (SELECT, UPDATE, DELETE, INSERT). Obiectele pot fi tabele, vederi, secvenţe, proceduri, funcţii, pachete. Drepturile sunt acordate implicit proprietarilor, dar pot fi revocate sau acordate şi altor utilizatori.

Figura 4 Modificarea unui utilizator cu EM

Gheorghe Hazi


Editarea drepturilor system din EM se face prin selectarea tabului <System Privileges>:

Atribuirea de privilegii system se face cu c-da GRANT: GRANT CREATE ANY TABLE TO HR;

System privilege: Create session.

HR

Object privilege: Update employees.

Figura 5 Drepturi ale unui utilizator

Figura 6 Acordarea de privilegii system cu EM

Gheorghe Hazi


Utilizarea clauzei ANY permite ca acea c-dă să fie valabilă pentru orice obiect. Prin bifarea obţiunii Admi Option se permite utilizatorului să acorde acel drept şi altor utilizatori. GRANT CREATE ANY TABLE TO HR WITH ADMIN OPTION; Din motive de securitate, anumite privilegii trebuie acordate numai administratorilor:

• RESTRICTED SESSION – permite conectarea la baza de date dacă aceasta a fost setată în modul RESTRICT:

ALTER SYSTEM ENABLE RESTRICTED SESSION; Sau STARTUP RESTRICT; • SYSDBA şi SYSOPER – permit pornirea, oprirea, recuperarea şi orice

altă activitate de administrare a bazei de date. SYSOPER permite utilizatorilor să efectueze operaţiile de bază, dar fără posibilitatea de a bloca datele utilizatorilor. Aceasta include următoarele privilegii:

- STARTUP and SHUTDOWN - CREATE SPFILE - ALTER DATABASE OPEN/MOUNT/BACKUP - ALTER DATABASE ARCHIVELOG - ALTER DATABASE RECOVER - RESTRICTED SESSION

SYSDBA poate în plus să efectueze restaurări incomplete, şi ştergerea bazei de date. Privilegiul SYSDBA îl are utilizatorul SYS dar poate fi dat şi altor utilizatori.

• DROP ANY obiect – permite ştergerea obiectelor din orice schemă. • CREATE, MANGE, DROP şi ALTER TABLESPACE – ceea ce permite

crearea, ştergerea şi schimbarea atributelor unui tablespace. • CREATE ANY DIRECTORY – permite crearea oricărui director (cu acess

în citire şi scriere) oriunde Oracle are acces. • GRANT ANY OBJECT PRIVILEGE • ALTER DATABASE şi ALTER SYSTEM – permite modificarea stării

bazei de date şi a instanţei care o accesează. Editarea şi adăugarea drepturilor asupra obiectelor din EM se realizează prin

editarea utilizatorului: 1. Din macheta Users se selectează utilizatorul şi se apasă butonul <Edit>. 2. Se selectează tipul de obiect asupra căruia vrem să-i dăm drepturi, din

lista ascunsă Select Object Type.

Gheorghe Hazi


3. Se apasă butonul <Add> pentru a adăuga obiecte de tipul celui selectat (de obicei din schema altui utilizator) asupra cărora să-i acordăm drepturi utilizatorului selectat.

4. Se selectează utilizatorul din schema căruia selectăm obiectele.

5. Se selectează obiectele asupra cărora vrem să acordăm privilegii. 6. Se selectează privilegiile acordate.

7. Se apasă OK. Comenzile SQL corespunzătoare sunt de forma: GRANT ALTER ON SCOTT.DEPT TO HR

Figura 7 Acordarea de privilegii asupra obiectelor cu EM

Figura 8 Selectarea

privilegiilor cu EM

Gheorghe Hazi


GRANT DELETE ON SCOTT.DEPT TO HR GRANT INSERT ON SCOTT.DEPT TO HR GRANT REFERENCES ON SCOTT.DEPT TO HR GRANT SELECT ON SCOTT.DEPT TO HR GRANT UPDATE ON SCOTT.DEPT TO HR GRANT ALTER ON SCOTT.EMP TO HR GRANT DELETE ON SCOTT.EMP TO HR GRANT INSERT ON SCOTT.EMP TO HR GRANT REFERENCES ON SCOTT.EMP TO HR GRANT SELECT ON SCOTT.EMP TO HR GRANT UPDATE ON SCOTT.EMP TO HR Ştergerea sau revocarea unui privilegiu system se face prin mutarea

privilegiilor din coloana dreapta în coloana stânga (figura 6). Din SQL ştergerea privilegiilor system se face cu c-da REVOKE:

REVOKE SELECT ANY TABLE FROM hr; Revocarea privilegiilor obiect se face din macheta Database Instance:

Ubc > Users > Edit User: HR - Object Privileges, prin apăsarea butonului <Delete>. C-zile de revocare a drepturilor asupra obiectelor sunt de forma.

REVOKE SELECT ON SCOTT.EMP FROM hr;

4.7. Roluri Asigură managementul mult mai uşor al restricţiilor. De exemplu, în situaţia

când mai mulţi utilizatori au aceleaşi drepturi, se pot acorda, o singură dată, aceste drepturi unui rol, iar apoi se va acorda acest rol tuturor utilizatorilor din categoria respectivă.

De asemenea, rolurile permit modificarea dinamică a drepturilor utilizatorilor prin modificarea rolurilor. Rolurile se pot activa şi dezactiva modificând în acest fel şi drepturile utilizatorilor. Caracteristicile principale ale rolurilor:

• Privilegiile sunt acordate şi revocate rolurilor, iar utilizatorilor le sunt acordate sau revocate roluri.

• Rolurile pot fi acordate sau revocate ca şi privilegiile de sistem. Un rol poate fi atribuit, la rândul lui şi altui rol.

• Un rol poate conţine atât privilegii sistem cât şi privilegii obiect.

http://hga:1158/em/console/database/instance/sitemap?event=doLoad&target=hazi&type=oracle_database&pageNum=3�

http://hga:1158/em/console/database/databaseObjectsSearch?event=redisplay&otype=USER&target=hazi&type=oracle_database�

Gheorghe Hazi


• Un rol poate fi activat sau dezactivat pentru fiecare utilizator căruia i-a fost atribuit.

• Rolurile nu sunt în proprietatea cuiva şi nu se găsesc într-o schemă. Există o serie de roluri predefinite, date mai jos:

Nume rol Privilegiu

CONNECT CREATE SESSION

RESOURCE CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

SCHEDULER_ ADMIN

CREATE ANY JOB, CREATE EXTERNAL JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULER

DBA Cele mai multe privilegii sau roluri. Nu se atribuie decât administratorilor.

SELECT_ CATALOG_ ROLE

Permite citirea dicţionarului bazei de date.

Un rol este un grup de privilegii (system şi obiect) care sunt acordate utilizatorilor sau altor roluri. Este recomandat ca privilegiile acordate utilizatorilor să fie gestionate prin roluri. Pentru a crea un rol se parcurg următorii paşi: 1. În EM se selectează Administration > Schema > Users & Privileges> Roles. 2. Se apasă butonul <Create>. 3. Se introduce numele rolului şi modul de autentificare la activarea cu c-da SET ROLE. 4. Se atribuie roluri, privilegii sistem şi privilegii obiect ca la orice utilizator.

Gheorghe Hazi


Prin SQL, crearea unui rol se face cu c-zi de forma: CREATE ROLE MY_ROL IDENTIFIED BY test; GRANT CREATE ANY TABLE TO MY_ROL; GRANT MY_ROL TO HR; ALTER USER HR DEFAULT ROLE NONE;

În cazul rolurilor protejate prin parolă (ca în exemplul de mai sus), se recomandă ca acestea să nu fie implicite pentru utilizatori. În acest caz activarea rolurilor se va face prin c-da SET ROLE: SET ROLE MY_ROL IDENTIFIED BY test;

4.8. Profiluri Profilurile sunt configuraţii ataşate unui grup de utilizatori prin care se limitează resursele la care are acces un utilizator. Prin profiluri se gestionează totodată starea conturilor şi impune restricţii legate de acestea (lungimea parolei, termen de expirare, etc). Fiecărui utilizator i se atribuie un singur profil la un moment dat. Dacă se modifică un profil în timp ce utilizatorul este conectat la baza de date, noul profil va fi activat la conectarea următoare. Limitarea resurselor pentru utilizatori nu se poate face dacă variabila de iniţializare RESOURCE_LIMIT nu este setată pe TRUE. Aceasta poate fi setată şi dinamic: ALTER SYSTEM SET RESOURCE_LIMIT=TRUE; Prin profiluri se pot limita următoarele resurce:

• CPU – accesul la timpul de calcul al procesorului. Poate fi limitat timpul pe o sesiune sau pe un apel(solicitare).

• Resurse de reţea/memorie – poate fi limitată durata unei sesiuni (dacă de depăşeşte utilizatorul este deconectat automat), timpii de pauză (în care utilizatorul conectat nu face nimic), numărul maxim de sesiuni concurente (simultane) ale aceluiaşi utilizator, memoria ocupată în SGA pentru procese proprii utilizatorului (cum ar fi sortarea)

• Resurse disc şi I/O – limitează volumul de date schimbat între memorie şi disc pe sesiune sau pe un apel.

Pentru a crea un profil se utilizează EM Administration > Schema > Users & Privileges > Profiles şi se apasă butonul Create.

Gheorghe Hazi


C-da SQL pentru exemplul de mai sus: CREATE PROFILE "PROFIL_TEST" LIMIT CPU_PER_SESSION 6000 CPU_PER_CALL UNLIMITED CONNECT_TIME 120 IDLE_TIME 30 SESSIONS_PER_USER DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT PRIVATE_SGA DEFAULT COMPOSITE_LIMIT DEFAULT PASSWORD_LIFE_TIME DEFAULT PASSWORD_GRACE_TIME DEFAULT PASSWORD_REUSE_MAX DEFAULT PASSWORD_REUSE_TIME DEFAULT PASSWORD_LOCK_TIME DEFAULT FAILED_LOGIN_ATTEMPTS DEFAULT PASSWORD_VERIFY_FUNCTION DEFAULT Se poate creşte securitatea bazei de date prin setarea unor limite pentru parole:

• Număr de zile după care expiră.

Figura 9 Crearea unui

profil cu EM

Gheorghe Hazi


• Număr de zile în care utilizatorul poate modifica parola după expirarea acesteia.

• Numărul de parole diferite ce trebuie utilizate, după care poate fi reutilizată o parolă veche.

• Numărul de zile care o parolă veche poate fi reutilizată. • Se poate utiliza o procedură de verificare a complexităţii (să nu fie

aceeaşi cu numele utilizatorului, să nu fie nume uzuale, etc). • Numărul de încercări la conectare (dacă se dă o parolă greşită) după

care contul se blochează. • Numărul de zile pentru care se blochează contul după eşuarea unei

logări potrivit alineatului precedent. Setările pentru parolă se face tot din pagina EM pentru profiluri, alegând

tab-ul Password. Un exemplu de procedură pentru verificarea complexităţii parolei se găseşte în scriptul <oracle_home>/rdbms/admin/utlpwdmg.sql.

4.9. Asignarea de cote în tablespace pentru utilizatori Este bine ca pentru utilizatori să se aloce o cotă de spaţiu într-un tablespace. Se poate seta cota la: unlimited sau o valoare în kB sau MB. Valoarea alocată poate fi mai mare ca spaţiul actual din tablespace (acesta se poate extinde). Acordarea privilegiului UNLIMITED TABLESPACE trebuie făcută cu prudenţă deoarece utilizatorul cu acest drept are acces în orice tablespace inclusiv SYSTEM şi SYSAUX. Este bine să nu se aloce cote în tablespace SYSTEM şi SYSAUX. Nu este necesară alocarea unei cote în tablespace temporar.


1. Se lansează i*SQLPlus (http://192.168.4.45:5560/isqlplus) şi se încarcă scriptul lab_07_01. Se lansează scriptul şi se introduce numele studentului. Ce face acest script?

2. Se încarcă şi se execută scriptul lab_07_02.sql. Acesta va încărca 1000 de înregistrări în tabela PERS creată cu scriptul de la pct. 1. Este un exemplu de utilizare a unei secvenţe în proceduri PL/SQL pentru crearea cheii primare.

3. Se creează vederea staff în schema nume_student, având la bază

http://192.168.4.45:5560/isqlplus�

Gheorghe Hazi


următoarea interogare: select marca, nume, salariu from pers where salariu>90000

order by salariu desc;

Vederea va fi creată cu EM (http://192.168.4.45:1158/em). Se afişează înregistrările generate de vederea staff atât în EM cât şi în i*SQLPlus.

4. Se creează, cu EM, (Administration > Users & Privileges> Profiles) profilul HR_nume_student şi se setează să accepte cel mult 15 minute timp de pauză. Se vizualizează instrucţiunea SQL care creează profilul.

5. Se verifică parametrul de iniţializare RESOURCE_LIMIT dacă este setat pe TRUE. Dacă nu se setează TRUE. Verificarea se face cu EM Administration > All Initialization Parameters.

6. Cu EM se creează rolul HR_M_nume_student, fără autentificare, care asigură citirea (SELECT) şi modificarea (UPDATE) în tabela PERS din schema nume_student. (Administration > Roles). Se vizualizează c-zile SQL aferente.

CREATE ROLE "HR_M_nume_student" NOT IDENTIFIED GRANT SELECT ON "TEST"."PERS" TO "HR_M_nume_student" GRANT UPDATE ON "TEST"."PERS" TO "HR_M_nume_student" 7. Cu EM se creează rolul HR_SEF_nume_student, fără autentificare,

care să permită înserarea (INSERT) şi ştergerea (DELETE) în tabela PERS. Se atribuie acestui rol şi rolul HR_M_nume_student. Se vizualizează c-zile SQL aferente.

CREATE ROLE "HR_SEF_nume_student" NOT IDENTIFIED GRANT DELETE ON "TEST"."PERS" TO "HR_SEF_ nume_student" GRANT INSERT ON "TEST"."PERS" TO "HR_SEF_ nume_student" GRANT "HR_M_nume_student" TO "HR_SEF_ nume_student" 8. Cu EM(Administration > Users) se creează contul pentru

nume_student_M1, muncitor, cu următoarele specificaţii:

http://192.168.4.45:1158/em�

Gheorghe Hazi


Câmp EM Setare

Name nume_student_M1 Profile HR_nume_student Password Authentication Selectat Password newuser Expire Password now Selectat Roles CONNECT şi

HR_M_nume_student Default Tablespace USERS Temporary Tablespace TEMP

9. Se mai creează şi contul pentru nume_student_M2, muncitor, cu numele. nume_student_M2 Setările sunt aceleaşi ca la pct. 8.

10. Cu EM se creează utilizatorul nume_student_SEF cu setările de la pct. 8, dar cu rolul HR_SEF_nume_student . Se afişează c-da SQL: CREATE USER nume_student_SEF PROFILE HR_nume_student IDENTIFIED BY newuser PASSWORD EXPIRE DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK GRANT CONNECT TO nume_student_SEF GRANT HR_SEF_nume_student TO nume_student_SEF

11. În SQLPlus se face conectarea cu userul nume_student_M2. Întrucât s-a setat parola expirată, se va introduce o parolă nouă: student. Se lasă programul activ, în bară, până la sfârşitul lucrării.

12. În i*SQLPlus se face conectarea cu userul nume_student_M1: DISCONNECT CONN nume_student_M1/newuser@ubc Întrucât s-a setat parola expirată, se va introduce o parolă nouă: student.

Se afişează datele din tabela nume_sudent.pers din schema utilizatorului creat la pct. 1.

SELECT * from nume_sudent.pers; Se modifică salariul pentru marca 467 la valoarea de 34000: UPDATE nume_sudent.pers SET salariu=34000 WHERE marca=467;

Gheorghe Hazi


Se încearcă ştergerea persoanei cu marca 145: DELETE from nume_sudent.pers WHERE marca=145; I*SQL Plus va afişa eroarea: EROARE la linia 1: ORA-01031: privilegii insuficiente

13. Se face conectarea cu userul nume_student_SEF, prin apăsarea butonului <Logout>. Se introduce noua parolă: student. Se şterge persoana cu marca 145: DELETE from nume_sudent.pers WHERE marca=145; Se verifică câte înregistrări au rămas în tabelă: SELECT COUNT(*) FROM nume_sudent.pers; Se restaurează înregistrarea ştearsă cu c-da SQL: ROLLBACK; Se verifică că în tabelă sunt 1000 de înregistrări. a) Unde a fost stocată înregistrarea pe perioada cât a fost ştearsă? b) Utilizatorilor noi nu li s-au dat dreptul la conectare prin GRANT

CONNECT (ca în cazul scriptului lab_07_01) sau prin GRANT CREATE SESSION. Totuşi ei s-au conectat. De ce?

14. Se revine la programul SQL Plus lăsat în bară la pct. 11. Se execută c-da SQL: SELECT * from nume_sudent.pers; Se execută instrucţiunea ? Dacă nu, de ce?

15. Din EM se face ştergerea: - utilizator nume_student (cu toate obiectele din proprietate,

opţiunea CASCADE) - utilizator nume_student_M1 - utilizator nume_student_M2 - utilizator nume_student_SEF - profil HR_nume_student - rol HR_M_nume_student - rol HR_SEF_nume_student

Gheorghe Hazi


LUCRAREA 8. INTRODUCERE ÎN PL/SQL (PARTEA I-A)

1. Scopul lucrării Lucrarea are drept scop însuşirea deprinderilor elementare privind elaborarea procedurilor PL/SQL.

2. Definirea mediului PL/SQL Utilizarea SQL pentru manipularea datelor are anumite limite, mai ales atunci când sunt necesare prelucrări complexe. În acest scop Oracle a creat mediul propriu de dezvoltare a aplicaţiilor, PL/SQL, care permite definirea unor blocuri de prelucrare individuală a înregistrărilor din tabele. PL/SQL este software de dezvoltare a aplicaţiilor care oferă facilităţi avansate cum ar fi încapsularea datelor, interceptarea şi tratarea excepţiilor, utilizând programarea orientată pe obiecte. Oferă toate construcţiile procedurale disponibile în generaţia 3-a de limbaje de programare (3GL).

Un bloc PL/SQL conţine instrucţiuni executate procedural şi instrucţiuni SQL. Motorul PL/SQL transmite procedurile unui modul de execuţie, iar instrucţiunile SQL sunt transmise, individual bazei de date Oracle. Motorul PL/SQL poate fi în baza de date sau poate funcţiona pe un server de aplicaţii

Figura 1 Funcţionarea

mediului PL/SQL

Gheorghe Hazi


separat. Principalele avantaje ale PL/SQL sunt:

• Integrarea construcţiilor procedurale cu SQL • Creşterea performanţelor prin înglobarea într-un singur bloc a mai multor

instrucţiuni SQL. Aplicaţia poate trimite blocul o singură dată către baza de date sau către serverul de aplicaţii, reducând semnificativ traficul în reţea şi numărul de apeluri către baza de date.

• Modularizarea programelor prin utilizarea de blocuri de program care pot fi secvenţiale sau îmbricate.

• Este integrat în uneltele de dezvoltare Oracle, cum sunt Oracle Forms şi Oracle Reports.

• Portabilitate – Modulele PL/SQL pot fi executate pe toate platformele în care sunt instalate baze de date Oracle (WINDOWS, LINUX, UNIX). Se pot crea biblioteci de programe reutilizabile pe orice platformă. Suportă toate tipurile de date din SQL (cu câteva extensii), precum şi toate funcţiile SQL.

• Interceptarea eficientă a erorilor.

3.Tipuri de blocuri

Blocurile pot fi de 3 tipuri: Anonim Procedură Funcţie

Fiecare tip de bloc conţine 3 secţiuni:

• Declaraţii (opţional) – Începe cu cuvântul cheie DECLARE şi se termină la începerea secţiunii executabile. Conţine declaraţii pentru variabile, constante, cursoare, excepţii definite de utilizator.

[DECLARE] BEGIN --instrucţiuni [EXCEPTION] END;

PROCEDURE nume IS BEGIN --instrucţiuni [EXCEPTION] END;

FUNCTION nume RETURN datatype IS BEGIN -instrucţiuni RETURN val; [EXCEPTION] END;

Gheorghe Hazi


• Zona executabilă (obligatorie) – Începe cu cuvântul cheie BEGIN şi se termină cu END urmat de „ ; ”. Conţine instrucţiuni SQL şi instrucţiuni PL/SQL pentru prelucrarea datelor.

• Zona de excepţii (opţional) – Este inclusă în zona executabilă şi începe cu cuvântul cheie EXCEPTION. Specifică acţiunile ce trebuie urmate atunci când se întâlneşte o eroare sau condiţii anormale în zona executabilă.

Blocurile anonime sau fără nume sunt scrise direct în codul executabil şi se execută la întâlnirea lor. Este trimis motorului PL/SQL la execuţie. Se utilizează în scripturi, în trigere Oracle Developer. Ele nu pot fi apelate şi pentru a fi executate de mai multe ori, trebuie să fie rescrise.

Subprogramele de tip procedură sau funcţie pot fi stocate în baza de date (în schema unui utilizator) şi pot fi apelate de mai multe ori cu parametrii de intrare diferiţi.

Din punct de vedere constructiv şi al amplasării lor modulele PL/SQL pot fi:

• Blocuri anonime – incluse în aplicaţii mai mari sau în scripturi executate interactiv. Pot fi executate în SQL*Plus, iSQL*Plus sau în alte medii de dezvoltare.

• Proceduri şi funcţii – blocuri PL/SQL cu nume care pot fi apelate cu parametri actuali, incluse în aplicaţii mai mari utilizate în Oracle Developer.

• Proceduri şi funcţii stocate - idem, dar sunt stocate în baza de date, în schema unui utilizator. Pot fi apelate de utilizatorii care au acest drept (EXECUTE) asupra procedurii respective.

• Pachete – stocate sau nu – conţin un grup de proceduri şi funcţii care rezolvă probleme într-un anumit domeniu.

• Trigere – întâlnite în baza de date sau în Oracle Developer, se lansează în execuţie la apariţia unui eveniment dinainte stabilit (de ex. înserarea unei înregistrări, apăsarea unui buton, etc).

• Obiecte – structuri definite de utilizatori care încapsulează date şi proceduri sau funcţii.

4. Crearea şi testarea unui bloc anonim Blocul se poate crea într-un fişier ASCII, ca şi scripturile. De asemenea ele pot fi scrise direct în iSQL*Plus: Aşa cum se vede din figura 2, înainte de blocul PL/SQL s-a înserat c-sa SQL

Gheorghe Hazi


Plus SET SERVEROUTPUT ON. Acesta permite afişarea mesajelor cu procedura stocată DBMS_OUTPUT.PUT_LINE.

Pachetul DBMS_OUTPUT, permite scrierea de mesaje din blocuri PL/SQL şi este utilizat, preponderent, în faza de testare a blocurilor PL/SQL. Pachetul DBMS_OUTPUT este în schema SYS şi are setat dreptul de execuţie pentru orice utilizator.

Pachetul conţine şi alte proceduri: - DBMS_OUTPUT.ENABLE – permite alocarea buferelor (nu este

necesară dacă se setează SET SERVEROUTPUT ON) - DBMS_OUTPUT.DISABLE – permite afişarea mesajelor (are acelaşi

efect cu SET SERVEROUTPUT OFF) - DBMS_OUTPUT.GET_LINE – citeşte o linie din buffer - DBMS_OUTPUT.GET_LINES – citeşte mai multe linii din buffer - DBMS_OUTPUT.PUT – trimite o porţiune dintr-o linie - DBMS_OUTPUT.NEW_LINE – termină o linie trimisă cu PUT Sintaxa pentru DBMS_OUTPUT.PUT_LINE

DBMS_OUTPUT.PUT_LINE (şir IN VARCHAR2); unde lungimea şirului poate fi de până la 32767 caractere.

5. Declararea variabilelor Sintaxa generală: identificator [CONSTANT] tip_dată [NOT NULL] [:= | DEFAULT expr]; Exemplu:

Figura 2 Crearea unui bloc anonim PL/SQL cu iSQL*Plus

Gheorghe Hazi


DECLARE data_ang DATE; marca NUMBER(2) NOT NULL := 10; locaţie VARCHAR2(13) := 'Bacău'; comision CONSTANT NUMBER := 1400; unde

- identificator – este numele variabilei; - CONSTANT – indică că este vorba de o constantă care nu poate fi

modificată (deci trebuie iniţializată) - tip_dată – tipul de dată (VARCHAR2, NUMBER, etc) - expr – o expresie având acelaşi tip cu tip_dată. Tipurile de variabile pot fi:

• Scalar – variabile simple • Compuse – structură de date (de ex. o înregistrare) • Referinţă – adresă a unei variabile • Large object (LOB) – BLOB, CLOB • Non-PL/SQL - variabile de legătură – definite în alt mediu, de ex.

SQL Plus sau în forme. Variabilele simple pot fi cele utilizate în SQL (a se vedea lucrarea 6), cu

unele extensii: • Variabilele CHAR pot avea lungimi de până la 32767 bytes faţă de

2000 în SQL. • Variabilele VRACHAR2 pot avea lungimi de până la 32767 bytes

faţă de 4000 în SQL. • BOOLEAN – variabile logice cu valori TRUE, FALSE sau NULL. • BINARY_INTEGER, PLS_INTEGER – variabile întregi

(recomandate) cu valori între –2147483647 şi 2147483647 Specific PL/SQL este utilizarea unui tip predefinit %TYPE:

identificator tabelă.coloană%TYPE; de exemplu: emp_lname employees.last_name%TYPE; balance NUMBER(7,2); min_balance balance%TYPE := 1000;

Variabilele de legătură sunt definite în alt loc decât în secţiunea

DECLARE al blocului PL/SQL. În SQL*Plus sau iSQL*Plus definirea unei astfel de variabile se face cu c-da:

Gheorghe Hazi


VARIABLE nume tip_variabilă Un exemplu de utilizare: VARIABLE result NUMBER BEGIN SELECT (SALARY*12) + NVL(COMMISSION_PCT,0) INTO :result

FROM employees WHERE employee_id = 144;

END; / PRINT result

Pentru a afişa o variabilă de legătură în SQL*Plus, se utilizează c-da SQL Plus PRINT. Ea trebuie aşezată în afara blocului întrucât este o c-dă SQL*Plus. Dacă se dă înainte c-da SET AUTOPRINT ON, atunci variabilele de legătură vor fi tipărite automat: VARIABLE emp_salary NUMBER

SET AUTOPRINT ON BEGIN SELECT salary INTO :emp_salary

FROM employees WHERE employee_id = 178;

END; / Pe lângă aceste tipuri de variabile, în mediul SQL*Plus se mai utilizează,

aşa cum am mai văzut, variabile de substituţie. Acestea se utilizează pentru a introduce de la tastatură variabile în timpul execuţiei unui script. Variabilele de substituţie pot fi referite în blocuri PL/SQL. Sunt referite sub forma &variabilă.

VARIABLE emp_salary NUMBER SET AUTOPRINT ON SET VERIFY OFF DECLARE empno NUMBER(6):=&empno; BEGIN SELECT salary INTO :emp_salary FROM employees WHERE employee_id = empno;

Gheorghe Hazi


END; / De asemenea, pentru a avea un prompt explicit, aşa cum s-a mai văzut, se

poate utiliza c-da SQL Plus, ACCEPT: SET VERIFY OFF VARIABLE emp_salary NUMBER ACCEPT empno PROMPT 'Introduceţi marca: ' SET AUTOPRINT ON DECLARE empno NUMBER(6):= &empno;

BEGIN SELECT salary INTO :emp_salary FROM employees

WHERE employee_id = empno; END; /

Variabilele compuse pot fi TABLE, RECORD, NESTED TABLE şi VARRAY. Variabilele de tip TABLE se folosesc pentru a manipula (în memorie) înregistrări din tabele ORACLE. În acest scop se defineşte acest tip de variabilă: DECLARE TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE

INDEX BY PLS_INTEGER; emp_tab EmpTabTyp; BEGIN

/* Citeşte din tabela EMPLOYEES. */ SELECT * INTO emp_tab(100) FROM employees WHERE employee_id = 100;

END; / Se observă utilizarea unei înregistrări cu structura tabelului

EMPLOYESS, utilizând tipul employees%ROWTYPE. Variabilele de tip RECORD se declară similar, dar pot avea o structură

diferită de a unei tabele. DECLARE

Gheorghe Hazi


TYPE DeptRecTyp IS RECORD (deptid NUMBER(4) NOT NULL := 99, dname departments.department_name%TYPE,

loc departments.location_id%TYPE, region regions%ROWTYPE );

dept_rec DeptRecTyp; NESTED TABLE sunt tablouri cu o dimensiune, dar cu număr de

elemente nelimitat, iar VARRAY sunt tablouri cu dimensiune variabilă. Variabilele de tip LOB (LargeOBjects) sunt utilizate pentru a stoca fişiere

de dimensiuni mari care conţin imagini, fotografii, filme, muzică, etc. Ca subtipuri avem CLOB (pentru blocuri de tip caracter), BLOB (pentru blocuri binare) şi BFILE (referinţă la un fişier de pe disc).

6. Scrierea codului în PL/SQL Pentru scrierea codului se folosesc variabile, simboluri, funcţii şi

proceduri, ca în orice limbaj de programare. Lista simbolurilor utilizate în PL/SQL: + Adunare % Indicator de tip (%TYPE, %ROWTYPE) ' Delimitator şir de caractere . selector de componente (de ex. record_pers.marca) / Împărţire ( Delimitator listă expresii ) Delimitator listă expresii : Prefix variabilă de legătură , Separator în listă * Înmulţire " Delimitator pentru identificatori complecşi = Egal (operator relaţional) < Mai mic > Mai mare @ Indicator acces de la distanţă (remote) ; Terminator instrucţiune - Minus := Operator de asignare => Operator de asociere (între parametrii formali şi cei actuali) || Operator de concatenare

Gheorghe Hazi


** Ridicare la putere << Delimitator etichetă (început) >> Delimitator etichetă (sfârşit) /* Delimitator comentarii multilinie (început) */ Delimitator comentarii multilinie (sfârşit) .. operator domenii (de ex. 1..1000) <> Diferit ~= Diferit ^= Diferit != Diferit <= Mai mic sau egal >= Mai mare sau egal -- comentarii pe o singură linie Comentarea codului este recomandată:

SET SERVEROUTPUT ON DECLARE Sal_lunar NUMBER (9,2):=1300; Sal_anual NUMBER (10,2) BEGIN -- Început secţiune executabilă /* Calculează salariul annual pe baza Celui lunar */

Sal_anual := sal_lunar * 12; DBMS_OUTPUT.PUT_LINE(‘Salariu annual: ‘||sal_anual);

END; -- Sfârşit secţiune executabilă / În PL/SQL sunt disponibile toate funcţiile din SQL, cu excepţia funcţiei

DECODE şi a funcţiilor de tip grup (SUM, AVG...) care se pot utiliza numai cu instrucţiuni SQL (nu şi separat).

Datele sunt pot fi convertite explicit (ca şi în SQL) cu funcţiile TO_NUMBER, TO_CHAR, TO_DATE. Există şi conversii implicite: şiruri de caractere – numere şi şiruri de caractere – câmpuri tip dată.

7. Imbricarea blocurilor Blocurile executabile pot fi îmbricate: DECLARE

Gheorghe Hazi


outer_variable VARCHAR2(20):='GLOBAL VARIABLE'; BEGIN DECLARE

inner_variable VARCHAR2(20):='LOCAL VARIABLE'; BEGIN DBMS_OUTPUT.PUT_LINE(inner_variable); DBMS_OUTPUT.PUT_LINE(outer_variable);

END; DBMS_OUTPUT.PUT_LINE(outer_variable); END; / Variabilele declarate în blocuri interioare sunt văzute numai acolo. Cele

declarate în blocurile exterioare pot fi văzute şi în toate blocurile subordinate. Dacă un nume de variabilă este definit atât într-un bloc interior şi în altul

exterior, atunci, la apelare din interior se caută variabila din interior. Totuşi se poate apela o variabilă cu acelaşi nume din exterior dacă se face o etichetare a blocurilor, iar variabilele cu acelaşi nume sunt prefixate cu eticheta blocului.

<<outer>> DECLARE

father_name VARCHAR2(20):='Patrick'; date_of_birth DATE:='20-Apr-1972'; BEGIN

DECLARE child_name VARCHAR2(20):='Mike';

date_of_birth DATE:='12-Dec-2002'; BEGIN DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: ' ||outer.date_of_birth); DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name); DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth); END;

END; /

8. Recomandări privind realizarea blocurilor PL/SQL Se recomandă următoarele moduri de notare:

Gheorghe Hazi


Categorie Notare litere mari sau mici

Exemple

Instrucţiuni SQL Litere mari SELECT, INSERT Cuvinte cheie PL/SQL

Litere mari DECLARE, BEGIN IF, LOOP

Tipuri de date Litere mari NUMBER, VARCHAR2

Identificatori şi variabile

Litere mici sal, v_marca, cod_produs

Tabele din baza de date şi coloane

Litere mici departments, employee_id

De asemenea se recomandă aranjarea rândurilor astfel încât instrucţiunile

repetitive, instrucţiunile scrise pe mai multe rânduri şi zonele îmbricate să fie uşor vizibile.

DECLARE deptno NUMBER(4); location_id NUMBER(4); BEGIN

SELECT department_id, location_id INTO deptno, location_id

FROM departments WHERE department_name= 'Marketing';

... END; /

9. Utilizarea instrucţiunilor SQL în blocuri PL/SQL Toate instrucţiunile SQL pentru manipularea datelor (DML) pot fi

utilizate în PL/SQL, instrucţiunea SELECT având anumite particularităţi. Celelalte instrucţiuni SQL (cum ar fi DDL sau DCL) pot fi executate

folosind SQL dinamic cu c-da EXECUTE IMMEDIATE. Aşa cum am menţionat mai sus instrucţiunea SELECT are o

particularitate:

Gheorghe Hazi


SELECT lista_selecţie INTO {nume_variabiă[,nume_variabiă]...

| nume_record} FROM tabelă [WHERE condiţie]; Se observă că apare (faţă de SQL) clauza INTO prin care se specifică

variabilele în care sunt încărcate informaţiile citite din tabele. Numărul de variabile din caluza INTO trebuie să fie aceleaşi cu cele din clauza SELECT.

SET SERVEROUTPUT ON DECLARE fname VARCHAR2(25); BEGIN SELECT first_name INTO fname FROM employees WHERE employee_id=200; DBMS_OUTPUT.PUT_LINE(' Numele pentru marca 200 este :

'||fname); END; / De precizat că instrucţiunea SELECT trebuie să returneze un singur rând.

Pentru mai multe rânduri se vor utiliza cursoare. Dacă instrucţiunea SELECT nu returnează nici un rând sau returnează mai mule rânduri atunci se generează o excepţie care poate fi interceptată cu excepţiile predefinite NO_DATA_FOUND şi TOO_MANY_ROWS.

Instrucţiunile INSERT, UPDATE şi DELETE au aceeaşi sintaxă ca în SQL cu precizarea că valorile utilizate pot fi variabile sau constante PL/SQL.

Exemplu utilizare instrucţiunea INSERT: BEGIN INSERT INTO employees

(employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES',sysdate, 'AD_ASST', 4000);

END; / Exemplu utilizare instrucţiunea UPDATE: DECLARE

Gheorghe Hazi


sal_increase employees.salary%TYPE := 800; BEGIN UPDATE employees SET salary = salary + sal_increase WHERE job_id = 'ST_CLERK'; END;

/ Exemplu utilizare instrucţiunea DELETE: DECLARE deptno employees.department_id%TYPE := 10; BEGIN DELETE FROM employees WHERE department_id = deptno; END; / Se poate utiliza şi instrucţiunea SQL MERGE care modifică un tabel

pornind de la date din alt tabel. DECLARE empno EMPLOYEES.EMPLOYEE_ID%TYPE := 100; BEGIN MERGE INTO copy_emp c

USING employees e ON (e.employee_id = c.empno)

WHEN MATCHED THEN UPDATE SET

c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, c.phone_number = e.phone_number, c.hire_date = e.hire_date, c.job_id = e.job_id, c.salary = e.salary, c.commission_pct = e.commission_pct, c.manager_id = e.manager_id, c.department_id = e.department_id

WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name,

Gheorghe Hazi


e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);

END; / În exemplul de mai sus sunt modificate şi completate datele din tabela

COPY_EMP cu cheia empno. Pentru înregistrările găsite (după clauza ON) se modifică informaţiile cu UPDATE, iar pentru înregistrările negăsite se înserează datele din tabela employees.

10. Cursoare Cursoarele sunt pointeri către zona cu înregistrări aflate în zona de

manevră ORACLE. Există cursoare implicite create de Oracle şi cursoare explicite, create de utilizatori. Acestea din urmă se utilizează atunci când sunt prelucrate mai multe înregistrări din baza de date (de ex. prin instrucţiunea SELECT).

Pentru toate tipurile de cursoare se pot utiliza atributele acestora: - SQL%FOUND – de tip boolean care returnează TRUE dacă ultima

instrucţiune SQL a prelucrat cel puţin o înregistrare; - SQL%NOTFOUND – idem, dacă nu s-a găsit nici o înregistrare; - SQL%ROWCONT – de tip întreg care arată numărul de înregistrări

afectate de ultima instrucţiune SQL. VARIABLE rows_gas VARCHAR2(30) DECLARE empno employees.employee_id%TYPE := 176; my_num PLS_INTEGER; BEGIN SELECT count(*) INTO my_num FROM employees WHERE employee_id = empno; :rows_gas := (SQL%ROWCOUNT ||' randuri gasite. Prin COUNT '||my_num); END; / PRINT rows_gas

Gheorghe Hazi


11. Lucrări de efectuat 11.1. Să se scrie un bloc PL/SQL care să afişeze mesajul „Hello World”.

Se salvează scriptul cu numele lab_08_01.sql. Execuţia se face cu iSQL*Plus (http://192.168.4.45:5560/isqlplus ).

11.2. Care din următorii identificatori (de variabile) sunt eronate: a. today b. last_name c. today’s_date d. Number_of_days_in_February_this_year e. Isleap$year f. #number g. NUMBER# h. number1to7

11.3. Identificaţi declaraţiile de variabile eronate: a. number_of_copies PLS_INTEGER; b. printer_name constant VARCHAR2(10); c. deliver_to VARCHAR2(10):=Johnson; d. by_when DATE:= SYSDATE+1;

11.4. Examinaţi blocul PL/SQL de mai jos şi bifaţi afirmaţiile bune: SET SERVEROUTPUT ON

DECLARE fname VARCHAR2(20); lname VARCHAR2(15) DEFAULT 'fernandez'; BEGIN DBMS_OUTPUT.PUT_LINE( FNAME ||' '||lname);

END; / a. Blocul este executat cu succes şi se afişează ‘fernandez’. b. Execuţia blocului va genera o eroare datorită faptului că variabila

fname nu este iniţializată. c. Blocul este executat cu succes şi se afişează ‘null fernandez’. d. Se generează o eroare datorită faptului că nu se poate utiliza clauza

DEFAULT pentru o variabilă VARCHAR2. e. Se generează o eroare din cauză că variabila fname nu este declarată.

http://192.168.4.45:5560/isqlplus�

Gheorghe Hazi


11.5. Se încarcă scriptul generat la pct. 11.1. Se modifică secţiunea de declaraţii şi se adaugă variabila today iniţializată cu data curentă (SYSDATE). Se adaugă variabila tomorrow având acelaşi tip cu today. Se va folosi declaraţia de forma %TYPE.

În secţiunea executabilă se iniţializează tomorrow calculată în funcţie de today (adăugându-se o zi). Se afişează today şi tomorrow după „Hello World”. Se execută şi se salvează cu numele lab_08_02.sql.

11.6. Editează scriptul anterior şi adaugă două variabile de legătură procent_baza şi procent_suplimentar. În zona executabilă iniţializează procent_bază cu 45 şi procent_suplimentar cu 12. Afişează variabilele de legătură cu c-da SQL Plus PRINT.

11.7. Să se analizeze scriptul următor: SET SERVEROUTPUT ON DECLARE weight NUMBER(3) := 600; message VARCHAR2(255) := 'Produs 10012'; new_locn VARCHAR2(40):='Asia'; BEGIN DECLARE weight NUMBER(3) := 1; new_locn VARCHAR2(50) := 'Europa'; BEGIN weight := weight + 1; new_locn := 'Western ' || new_locn; message:=message ||’ nu este in stoc’; -- punctul 1 END; weight := weight + 1; message := message || ' este în stoc'; new_locn := 'Western ' || new_locn; -- punctul 2 END; / Care sunt valorile variabilelor weight, message şi new_locn în punctele 1

şi 2 marcate în blocul PL/SQL ? 11.8. Să se scrie şi să se execute un script care să calculeze impozitul

datorat de un salariat din tabela EMPLOYEES calculat ca 16 % din salariu. Se lucrează cu iSQL*Plus conectat ca utilizator HR şi parola HR. De la consolă se

Gheorghe Hazi


va introduce interactiv marca (employee_id) salariatului. Se va utiliza o variabilă de substituţie. Se va afişa salariul şi impozitul salariatului.

11.9. Să se scrie un bloc PL/SQL care să determine codul maxim de departament din tabela hr.my_departments. Se va utiliza o variabilă max_dept declarată în secţiunea DECLARE (tip number). Se va afişa valoarea găsită. Se salvează scriptul cu numele lab_08_03.sql.

11.10. Se modifică scriptul lab_08_03.sql pentru a însera o înregistrare în tabela my_departments. Se declară două noi variabile: una în secţiunea DECLARE a blocului PL/SQL cu numele nume_dep având tipul identic cu câmpul department_name din tabela my_departments. Acesta va avea valoarea ‘Educatie’ precizată în acelaşi loc. Se va defini o variabliă de legătură (înaintea blocului PL/SQL):

VARIABLE cod_dep number Se defineşte noul cod de departament prin adăugarea lui 10 la valoarea

maximă max_dept determinată la pct. 11.10. Rezultatul se memorează în variabila cod_dep.

Se scrie instrucţiunea pentru înserare cu valori numai pentru câmpurile department_id (=cod_dep) şi department_name (=nume_dep).

Se utilizează proprietatea SQL%ROWCOUNT pentru a vedea câte înregistrări s-au înserat.

În script, după / se va însera c-da SELECT pentru a vedea înregistrarea înserată. După execuţie şi validare se salvează scriptul cu numele lab_08_04.sql.

11.11. Se scrie un bloc PL/SQL care să modifice câmpul LOCATION_ID (necompletat la pasul anterior) la valoarea 3000. Se înserează în script, după /, instrucţiunea SELECT de afişare a înregistrării şi instrucţiunea de ştergere a înregistrării înserate.

Gheorghe Hazi


LUCRAREA 9. INTRODUCERE ÎN PL/SQL (PARTEA II-A)

1. Scopul lucrării Lucrarea are drept scop însuşirea deprinderilor privind lucrul cu structuri alternative, bucle şi cursoare.

2. Structuri de control în PL/SQL Structura alternativă IF are următoarea sintaxă: IF condiţie THEN instrucţiuni; [ELSIF condition THEN instrucţiuni;] [ELSE instrucţiuni;] END IF; Unde condiţie reprezintă o expresie logică cu rezultatul TRUE, FALSE sau NULL. Instrucţiunile pot fi orice instrucţiuni executabile PL/SQL, inclusiv alte structuri IF. Numărul de ramuri ELSIF este nelimitat. O condiţie nu este respectată dacă este FLASE sau NULL. END IF marchează sfârşitul structurii şi trebuie terminat obligatoriu cu ;. Exemple:

SET SERVEROUTPUT ON DECLARE varsta_mea number:=31; BEGIN IF varsta_mea < 11 THEN DBMS_OUTPUT.PUT_LINE(' Sunt copil! '); ELSE

DBMS_OUTPUT.PUT_LINE(' Sunt ceva mai mare !'); END IF;

Gheorghe Hazi


END; /

DECLARE varsta_mea number:=31;

BEGIN IF varsta_mea < 11 THEN DBMS_OUTPUT.PUT_LINE(' Sunt un copil ! ');

ELSIF varsta_mea < 20 THEN DBMS_OUTPUT.PUT_LINE(' Sunt tânăr! ');

ELSIF varsta_mea < 30 THEN DBMS_OUTPUT.PUT_LINE(' Mă apropii de 30 de ani!');

ELSIF varsta_mea < 40 THEN DBMS_OUTPUT.PUT_LINE(' Mă apropii de 40 de ani!'); ELSE DBMS_OUTPUT.PUT_LINE(' Sunt mereu tânăr ! '); END IF; END; / Prezenţa valorilor nule într-o expresie conduce la rezultatul fals al unei

expresii logice. SET SERVEROUTPUT ON DECLARE varsta_mea number:=NULL; BEGIN IF varsta_mea < 11 THEN DBMS_OUTPUT.PUT_LINE(' Sunt un copil ! '); ELSE DBMS_OUTPUT.PUT_LINE(' Nu mai sunt copil ! '); END IF; END; / Nu mai sunt copil ! Procedură PL/SQL încheiată cu succes. Instrucţiunea CASE permite, în funcţie de valoarea unui selector să se

realizeze o structură alternativă cu mai multe ramuri. Structura: CASE selector WHEN expresie1 THEN rezultat1

Gheorghe Hazi


WHEN expresie2 THEN rezultat2 ...

WHEN expresieN THEN rezultatN [ELSE rezultatN+1]

END; / Exemple: SET SERVEROUTPUT ON DECLARE nota number(2); calificativ varchar2(30); BEGIN Nota:=NULL; CASE WHEN nota = 10 THEN calificativ:='Excelent'; WHEN nota = 9 THEN calificativ:='Foarte bine'; WHEN nota IN (8,7) THEN calificativ:='Bine'; WHEN nota IN (6,5) THEN calificativ:='Satisfacator'; WHEN nota <= 4 THEN calificativ:='Picat'; ELSE calificativ:='Absent'; END CASE; DBMS_OUTPUT.PUT_LINE('Calificativ obţinut: '||calificativ); END; / Calificativ obţinut: Absent Procedură PL/SQL încheiată cu succes. În această variantă condiţia logică se scrie la fiecare caluză WHEN, iar

instrucţiunea se termină cu END CASE. O altă variantă de utilizare: SET SERVEROUTPUT ON DECLARE nota number(2); calificativ varchar2(30); BEGIN nota:=10; CASE nota WHEN 10 THEN calificativ:='Excelent'; WHEN 9 THEN calificativ:='Foarte bine'; WHEN 8 THEN calificativ:='Bine';

Gheorghe Hazi


WHEN 7 THEN calificativ:='Acceptabil'; WHEN 6 THEN calificativ:='Satisfacator'; WHEN 5 THEN calificativ:='Sfantul cinci'; ELSE calificativ:='La toamna '; END CASE; DBMS_OUTPUT.PUT_LINE('Calificativ obţinut: '||calificativ); END; / Calificativ obţinut: Excelent Procedură PL/SQL încheiată cu succes.

În această variantă fiecare la fiecare WHEN se scrie numai valoarea

variabilei, iar instrucţiunea se termină cu END CASE. Varianta a treia de utilizare: SET SERVEROUTPUT ON DECLARE nota number(2); calificativ varchar2(30); BEGIN nota:=NULL; calificativ:= CASE nota WHEN 10 THEN 'Excelent' WHEN 9 THEN 'Foarte bine' WHEN 8 THEN 'Bine' WHEN 7 THEN 'Acceptabil' WHEN 6 THEN 'Satisfacator' WHEN 5 THEN 'Sfantul cinci' ELSE 'La toamna ' END; DBMS_OUTPUT.PUT_LINE('Calificativ obţinut: '||calificativ); END; / Calificativ obţinut: La toamna Procedură PL/SQL încheiată cu succes. Este cea mai simplă, variabila calificativ fiind scrisă o dată, iar

instrucţiunea se termină cu END. După fiecare clauză WHEN nu se mai scrie ;. În tabelul de mai jos se prezintă rezultatul unei expresii logice în funcţie

de valorile operanţilor, luând în considerare valorile posibile: TRUE, FALSE,

Gheorghe Hazi


NULL, pentru operatorii AND, OR, NOT:

3. Structuri repetitive în PL/SQL În PL/SQL există trei tipuri de structuri repetitive:

• LOOP – execută acţiuni repetitive fără condiţii la începutul structurii sau la sfârşit, însă ieşirea din buclă se face cu clauza EXIT WHEN condiţie.

• FOR LOOP – bucla repetă de un număr definit de ori, pe baza unui contor. • WHILE LOOP – bucla se repetă pe baza unei condiţii verificate la

începutul structurii.

Structura LOOP – END LOOP

LOOP instrucţiune1; . . . EXIT [WHEN condiţie]; END LOOP;

Zona de instrucţiuni cuprinsă între LOOP şi END LOOP se repetă până când condiţia de la WHEN condiţie este satisfăcută. Dacă se trece clauza EXIT fără WHEN condiţie, structura nu mai este repetitivă. Dacă nu se trece clauza EXIT, atunci bucla se repetă de un număr infinit de ori.

Exemplu: DECLARE countryid locations.country_id%TYPE := 'CA';

loc_id locations.location_id%TYPE; counter NUMBER(2) := 1; new_city locations.city%TYPE := 'Montreal';

Gheorghe Hazi


BEGIN SELECT MAX(location_id) INTO loc_id FROM locations WHERE country_id = countryid; LOOP INSERT INTO locations(location_id, city, country_id) VALUES((loc_id + counter), new_city, countryid); counter := counter + 1; EXIT WHEN counter > 3; END LOOP; END;

/ Structura WHILE LOOP – END LOOP

WHILE condiţie LOOP

instrucţiune1; instrucţiune2; . . . END LOOP; Instrucţiunile cuprinse între WHILE şi END LOOP sunt executate atât

timp cât condiţie este satisfăcută. Dacă condiţie este NULL, se trece controlul în afara buclei, ca în situaţia

în care condiţie este FALSE. Exemplu: DECLARE countryid locations.country_id%TYPE := 'CA';

loc_id locations.location_id%TYPE; new_city locations.city%TYPE := 'Montreal';

counter NUMBER := 1; BEGIN SELECT MAX(location_id) INTO loc_id FROM locations

WHERE country_id = countryid; WHILE counter <= 3 LOOP INSERT INTO locations(location_id, city, country_id) VALUES((loc_id + counter), new_city, countryid); counter := counter + 1;

Gheorghe Hazi


END LOOP; END; /

Structura FOR LOOP – END LOOP FOR contor IN [REVERSE] lim_inf..lim_sup LOOP instrucţiune1; instrucţiune2; . . . END LOOP;

unde contor reprezintă variabila contor a structurii, lim_inf, lim_sup – limitele între care ia valori contorul, valori întregi. Contorul nu trebuie declarat ca variabilă în secţiunea DECLARE. Clauza REVERSE conduce la parcurgerea descrescătoare a contorului, începând cu limita superioară. Exemplu:

DECLARE countryid locations.country_id%TYPE := 'CA'; loc_id locations.location_id%TYPE; new_city locations.city%TYPE := 'Montreal'; BEGIN SELECT MAX(location_id) INTO loc_id FROM locations WHERE country_id = countryid; FOR i IN 1..3 LOOP

INSERT INTO locations(location_id, city, country_id) VALUES((loc_id + i), new_city, countryid ); END LOOP; END; /

Contorul poate fi referit numai în interiorul buclei şi nu poate fi modificat. În cazul buclelor îmbricate (suprapuse) buclele pot fi etichetate la fel ca şi blocurile:

BEGIN <<Outer_loop>> LOOP

counter := counter+1; EXIT WHEN counter>10;

Gheorghe Hazi


<<Inner_loop>> LOOP ... EXIT Outer_loop WHEN total_done = 'YES'; -- Ieşire din ambele bucle EXIT WHEN inner_done = 'YES';

-- Ieşire din bucla interioară ...

END LOOP Inner_loop; ...

END LOOP Outer_loop; END; /

4. Utilizarea cursoarelor în PL/SQL Serverul Oracle utilizează o zonă de memorie de lucru (numită zonă privată SQL) pentru a executa instrucţiunile SQL şi pentru a stoca informaţiile procesate. Se poate utiliza un cursor explicit pentru a eticheta zona privată de memorie şi a avea acces la informaţiile stocate. Cursoarele pot fi:

• Implicite – declarate de către PL/SQL pentru toate instrucţiunile DML. • Explicite – declarate de către programator pentru interogările care

returnează mai mult de o înregistrare. Pot fi gestionate prin câteva unelte specifice. În acest fel fiecare înregistrare poate fi prelucrată individual.

Figura 1 Stocarea înregistrărilor în zona de memorie Oracle

Gheorghe Hazi


Setul de înregistrări returnat de o interogare care dă mai multe rânduri se

numeşte set activ. În figura 1 se arată cum un cursor indică setul activ de înregistrări.

În figura 2 se arată etapele de lucru cu un cursor explicit. Pentru fiecare

fază există instrucţiuni specifice PL/SQL. Programul PL/SQL deschide cursorul, procesează înregistrările date de interogare şi închide cursorul. Cursorul marchează poziţia (rândul curent) în setul activ.

Instrucţiunea OPEN execută interogarea asociată cursorului, identifică setul activ şi poziţionează cursorul pe primul rând.

Instrucţiunea FETCH returnează rândul curent şi poziţionează cursorul pe rândul următor. Ea se poate repeta până când nu mai sunt rânduri sau o condiţie specificată este atinsă.

Instrucţiunea CLOSE eliberează cursorul şi resursele cerute de acesta. Declararea unui cursor CURSOR nume_cursor IS

instrucţiune_select; în care instrucţiunea select se scrie fără clauza INTO. Exemplu:

DECLARE cod_loc NUMBER:= 1700;

CURSOR dept_cursor IS SELECT * FROM departments

Figura 2 Fazele de lucru cu un cursor explicit

Gheorghe Hazi


WHERE location_id = cod_loc; Dacă se doreşte prelucrarea într-o anumită ordine, instrucţiunea SELECT se poate completa cu clauza ORDER BY, sau orice opţiunea a acesteia. Deschiderea unui cursor se face cu c-da: OPEN nume_cursor; Deschiderea unui cursor presupune următoarele operaţii:

1. Alocă memorie de dimensiune potrivită. 2. Analizează şi descompune instrucţiunea SELECT. 3. Face legătura între variabilele de memorie şi înregistrare. 4. Identifică setul activ (înregistrările returnate de SELECT) 5. Poziţionează pointerul pe primul rând din setul activ. Dacă instrucţiunea SELECT nu dă nici un rând, ORACLE nu generează o

eroare în cazul cursoarelor explicite. Utilizatorul poate utiliza atributul acestuia nume_cursor%ROWCOUNT pentru a afla câte înregistrări au fost returnate.

Citirea datelor se face cu instrucţiunea FETCH: FETCH nume_cursor INTO lista variabile;

în care lista variabile trebuie să acopere lista câmpurilor din SELECT-ul care defineşte cursorul. Exemplu:

SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees

WHERE department_id =30;

empno employees.employee_id%TYPE; lname employees.last_name%TYPE; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO empno, lname;

EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE( empno ||' '||lname); END LOOP; CLOSE emp_cursor; END;

Gheorghe Hazi


/ 114 Raphaely 115 Khoo 116 Baida 117 Tobias 118 Himuro 119 Colmenares Procedură PL/SQL încheiată cu succes. Închiderea unui cursor se face cu instrucţiunea: CLOSE nume_cursor; Se pot defini înregistrări cu structura dată de SELECT-ul care defineşte

cursorul, utilizând declaraţia %ROWTYPE: DECLARE

CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees

WHERE department_id =30; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor;

LOOP FETCH emp_cursor INTO emp_record; ……… Mai concentrată şi mai uşor de utilizat este varianta CURSOR FOR LOOP: FOR nume_record IN nume_cursor LOOP instrucţiune1; instrucţiune2;

. . . END LOOP; În acest caz, numele înregistrării este definită chiar în structura FOR,

nefiind necesară declaraţia în secţiunea DECLARE. Se execută implicit OPEN, FETCH, EXIT şi CLOSE.

SET SERVEROUTPUT ON DECLARE my_dept departments.department_id%TYPE:=10; CURSOR emp_cursor IS SELECT * FROM employees

Gheorghe Hazi


WHERE department_id=my_dept; BEGIN my_dept:=20; FOR emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(emp_record.employee_id||' ' ||emp_record.first_name||' '||emp_record.last_name||' '

||emp_record.salary); END LOOP; END; / 201 Michael Hartstein 13000 202 Pat Fay 6000 Procedură PL/SQL încheiată cu succes. Atributele cursoarelor explicite sunt: SET SERVEROUTPUT ON DECLARE my_dept departments.department_id%TYPE:=10; CURSOR emp_cursor IS SELECT * FROM employees WHERE department_id=my_dept; nr_rand number(6); BEGIN my_dept:=20; FOR emp_record IN emp_cursor LOOP nr_rand:=emp_cursor%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(emp_record.employee_id||' ' ||emp_record.first_name||' ' ||emp_record.last_name||' '||emp_record.salary);

Gheorghe Hazi


END LOOP; DBMS_OUTPUT.PUT_LINE('Numar randuri prelucrate: ' ||nr_rand); END; / 201 Michael Hartstein 13000 202 Pat Fay 6000 Număr rânduri prelucrate: 2 Procedură PL/SQL încheiată cu succes Există şi posibilitatea să nu mai declarăm cursorul dacă scriem direct

instrucţiunea SQL în ciclul FOR: SET SERVEROUTPUT ON DECLARE my_dept departments.department_id%TYPE:=10; BEGIN my_dept:=20; FOR emp_record IN (SELECT * FROM employees WHERE department_id=my_dept) LOOP DBMS_OUTPUT.PUT_LINE(emp_record.employee_id|| ' '||emp_record.first_name||' '||emp_record.last_name|| ' '||emp_record.salary); END LOOP; END; / 201 Michael Hartstein 13000 202 Pat Fay 6000 Procedură PL/SQL încheiată cu succes Varianta de mai sus are dezavantajul că nu se pot utiliza atributele

cursorului explicit. Se pot utiliza şi cursoare cu parametri sub forma: CURSOR nume_cursor [(nume_parameteru tip_dată, ...)] IS instrucţine_select; Iar la deschiderea cursorului se urilizează sintaxa: OPEN nume_cursor (val_parametru,…);

Exemplu:

SET SERVEROUTPUT ON

Gheorghe Hazi


DECLARE CURSOR emp_cursor (deptno NUMBER) IS

SELECT employee_id, last_name FROM employees WHERE department_id = deptno; dept_id NUMBER;

lname VARCHAR2(15); BEGIN OPEN emp_cursor (10);

... CLOSE emp_cursor; OPEN emp_cursor (20);

... Se pot utiliza cursoare cu parametru şi în varianta FOR: FOR emp_record IN emp_cursor(20) LOOP

În cazul în care prin prelucrare dorim să modificăm înregistrările, se poate utilizeze varianta FOR UPDATE:

SELECT ... FROM ... FOR UPDATE [OF coloană_referinţă][NOWAIT | WAIT n]; Varianta este utilă întrucât se blochează accesul la înregistrări până se termină prelucrarea. În acest fel nu este posibil ca un alt utilizator să modifice înregistrările, iar utilizatorul curent să le vadă pe cele vechi (de la momentul deschiderii cursorului). coloană_referinţă – se trec coloanele care vor fi modificate (opţional) NOWAIT – dacă înregistrările sunt blocate de alt utilizator baza de date emite o eroare WAIT n – se aşteaptă n secunde ca înregistrările să se deblocheze după care se emite un mesaj de eroare De asemenea în combinaţie cu FOR UPDATE este clauza

WHERE CURRENT OF cursor ; care se utilizează în instrucţiunile de modificare sau ştergere pentru a referi înregistrarea curentă din cursor. UPDATE employees

SET salary = ... WHERE CURRENT OF emp_cursor;

Gheorghe Hazi



1. Se execută scriptul lab_09_01.sql care creează un utilizator nume_student, iar în schema acestuia creează o tabelă messages, cu un câmp results varchar2(80). Executarea scriptului se face cu iSQL*Plus ( http://192.168.4.45:5560/isqlplus ). Se scrie un bloc PL/SQL care înserează rânduri în tabela messages, cu numerele de la 1 la 10 exceptând 6 şi 8. Se execută COMMIT înainte de sfârşitul blocului PL/SQL. Se afişează conţinutul tablei cu c-da SQL SELECT, înserată după finalul blocului PL/SQL. Scriptul cu soluţia este dat în lab_09_01_sol.sql.

2. Se execută scriptul lab_09_02.sql care creează în, schema nume_student, tabela emp având conţinutul tabelei hr.employees şi modifică tabela emp adăugând câmpul stars de tip varchar2(50). Se scrie un bloc PL/SQL care să scrie un număr de caractere * în câmpul stars proporţional cu salariul, câte o steluţă pentru fiecare 1000. Modificarea se va face pentru o marcă (employee_id) introdusă de la tastatură. Scriptul se va realiza respectând precizările de mai jos:

a. Marca salariatului se va introduce printr-o variabilă de substituţie marca, utilizând c-da SQL Plus ACCEPT.

b. În secţiunea DECLARE se vor defini variabilele: i. empno – de tipul emp.employee_id care va prelua

valoarea variabilei de substituţie marca. ii. asterisk – de tip emp.stars iniţializat cu NULL care va

conţine şirul de steluţe, proporţional cu salariul. iii. sal – de tip emp.salary care va prelua salariul împărţit la

1000, rotunjit cu 0 zecimale (pentru a vedea câte steluţe se scriu)

c. În secţiunea executabilă a blocului PL/SQL se vor executa următoarele operaţii:

i. Se citeşte salariul (salary) pentru marca empno în variabila sal. Se împarte la 1000 şi se rotunjeşte la valoarea întreagă cea mai apropiată. Se va folosi funcţia NVL pentru a înlocui valoarea salariului cu 0 dacă salary este NULL.

http://192.168.4.45:5560/isqlplus�

Gheorghe Hazi


ii. Într-o buclă FOR se adaugă (prin concatenare) la variabila asterisk atâtea steluţe cât are variabila sal.

iii. Se modifică câmpul stars din tabela emp cu şirul asterisk folosind c-da SQL UPDATE, pentru marca empno.

iv. Se execută COMMIT. d. După blocul PL/SQL se scrie o c-dă SELECT care să afişeze

câmpurile employee_id, salary, stars din tabela emp pentru marca respectivă.

Soluţia problemei este dată în scriptul lab_09_02_sol.sql. 3. Se execută scriptul lab_09_03.sql care creează tabela temp, în schema

nume_student, cu structura: num_stocat number(7,2), char_stocat varchar2(35), date_stocat date. Se va scrie un bloc PL/SQL care să realizeze înserarea unei înregistrări

cu valorile: 117.34, „Acesta este un bloc PL/SQL”, 15-04-2008. Se verifică dacă date_stocat este mai mică decât data curentă şi, în acest caz, se modifică câmpul date_stocat cu data curentă. După blocul PL/SQL se afişează conţinutul tabelei temp cu c-da SQL SELECT.

Soluţia este dată în scriptul lab_09_03_sol.sql. 4. Să se creeze un script cu un bloc PL/SQL care să realizeze o tabelă

top_salariati care să conţină topul cu primii n salarii din tabela emp, din schema nume_student, în ordine descrescătoare. Tabela va conţine numărul curent, numele şi salariul. Întrucât pot exista mai mulţi salariaţi cu acelaşi salariu, se va trece o singură înregistrare pentru un salariu şi anume primul în ordine alfabetică. Precizări privind realizarea scriptului:

a. Să creeze o tabelă top_salariaţi cu structura nr number(3) nume varchar2(25) salariu number(8,2) b. Se va introduce de la tastatură numărul de salariaţi din top

utilizând c-da SQL ACCEPT. c. Se va defini un cursor care să determine primele n salarii din top

şi un cursor cu parametru care să citească numele celor care au acel salariu.

d. Paşii de lucru în blocul PL/SQL: i. Se deschide cursorul pentru salarii

ii. Se citeşte primul salariu

Gheorghe Hazi


iii. Se defineşte o buclă WHILE care să se execute atâta timp cât numărul de salariaţi nu depăşeşte n şi cât timp mai există înregistrări. În această buclă

1. Se citeşte numărul curent de înregistrare (%ROWCOUNT) într-o variabilă de memorie

2. Se deschide cursorul pentru nume cu salariul citit de celălalt cursor.

3. Se citeşte primul nume cu cursorul pentru nume. 4. Se închide cursorul pentru nume. 5. Se înserează o înregistrare în tabela top_salariaţi 6. Se citeşte o nouă înregistrare prin cursorul de salarii

iv. Se închide cursorul de salarii. e. Se afişează, în afara blocului PL/SQL topul salariaţilor folosind

c-da SELECT. Soluţia este dată în scriptul lab_09_04_sol.sql. Se testează

funcţionarea scriptului pentru n=0, n=10, n=400. 5. Să se scrie un bloc PL/SQL care să modifice salariile din tabela, din

schema nume_student.emp după cum urmează: a. Salariile mai mici de 2500 se vor majora cu 20% b. Salariile între 2500 şi mai mici de 5000 se vor majora cu 10%. c. Salariile între 5000 şi mai mici de 10000 se vor majora cu 5%.

Se vor afişa lista (nume, prenume, salariu vechi, salariu nou pentru personalul al cărui salariu a fost majorat. În final se va afişa numărul de salariaţi care beneficiază de creştere şi suma totală necesară pentru majorare. Se va utiliza un cursor cu clauza FOR UPDATE. La finalul blocului PL/SQL se va introduce ROLLBACK. Soluţia este dată în scriptul lab_09_05_sol.sql. 6. Se şterge utilizatorul nume_student şi obiectele sale cu c-da SQL:

DELETE USER nume_student CASCADE.

Gheorghe Hazi


LUCRAREA 10. INTRODUCERE ÎN PL/SQL (PARTEA III-A)

1. Scopul lucrării Lucrarea are drept scop însuşirea deprinderilor privind tratarea excepţiilor în PL/SQL şi crearea procedurilor stocate în baza de date Oracle.

2. Tratarea excepţiilor Excepţiile sunt erori de execuţie a blocurilor PL/SQL care conduc la terminarea anormală a acestora. Ele sunt generate de erori, care depind de valorile datelor prelucrate şi de interacţiunea cu baza de date. Pentru ca o astfel de eroare să nu conducă la pierderea controlului de către blocul PL/SQL, aşa cum am mai arătat, în PL/SQL se defineşte o secţiune specială pentru tratarea acestor erori. De exemplu, la execuţia blocului de mai jos rezultă o eroare:

SET SERVEROUTPUT ON DECLARE

nume VARCHAR2(15); BEGIN

SELECT last_name INTO nume FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John este prenumele lui: ' ||nume); END; /

EROARE la linia 1: ORA-01422: aducerea exactă returnează mai mult decât numărul cerut de linii ORA-06512: la linia 4

Dacă ne uităm în manualul cu codurile de eroare observăm următoarele

Gheorghe Hazi


explicaţii: ORA-01422 – Numărul de înregistrări returnate este mai mare decât numărul de înregistrări aşteptate (una în acest caz). ORA-06512 – Căutarea unui mesaj în stivă dă o eroare neinterceptată. Eroarea este generată de faptul că în tabela employees există trei salariaţi cu prenumele John: SQL> SELECT last_name 2 FROM employees 3 WHERE first_name='John'; Tastati <ENTER> LAST_NAME ------------------------- Chen Russell Seo Pentru interceptarea excepţiei, codul se rescrie astfel:

SET SERVEROUTPUT ON DECLARE

nume VARCHAR2(15); BEGIN

SELECT last_name INTO nume FROM employees WHERE first_name='John'; DBMS_OUTPUT.PUT_LINE ('John este prenumele lui: ' ||nume); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Instrucţiunea SELECT da prea multe inregistrari. Poate utilizati un cursor! ');

END; /

În acest caz, la întâlnirea unei excepţii, se sare la blocul de excepţii şi aceasta este prelucrată aici. O excepţie poate fi ridicată implicit de serverul Oracle sau explicit de către utilizator. Acest ultim mod este util numai pentru o tratare unitară a excepţiilor. O excepţie poate fi captată în secţiunea EXCEPTION din blocul PL/SQL curent sau poate fi interceptată de blocurile apelante ale blocului curent.

Gheorghe Hazi


După modul în care sunt definite şi ridicate, excepţiile pot fi:

• Predefinite în serverul Oracle – aproximativ 20 de erori care au loc în blocurile PL/SQL. Ele sunt ridicate (generate) automat de baza de date.

• Nedefinite în serverul Oracle, dar care se referă la erori ale acestuia şi care au coduri ORA. Se definesc în secţiunea DECLARE, iar baza de date le ridică automat.

• Erori definite de utilizator – Sunt definite şi ridicate de utilizator. Există aplicaţii derivate (cum ar fi Oracle Forms) care au definite propriile

sale excepţii. Interceptarea excepţiilor se realizează în secţiunea EXCEPTION, sintaxa

generală fiind de forma: EXCEPTION WHEN excepţie1 [OR excepţie2 . . .] THEN instrucţiune1; instrucţiune2;

. . . [WHEN excepţie3 [OR excepţie4 . . .] THEN instrucţiune1; instrucţiune2; . . .] [WHEN OTHERS THEN instrucţiune1; instrucţiune2;

. . .]

Figura 1 Interceptarea excepţiilor în

PL/SQL

Gheorghe Hazi


Clauza WHEN OTHERS trebuie specificată la sfârşit, pentru ca ramura să preia variante neanalizate în clauzele WHEN de mai sus. Atunci când este tratată o excepţie, o singură ramură WHEN din secţiunea EXCEPTION va funcţiona.

Interceptarea erorilor predefinite presupune referirea lor în secţiunea

EXCEPTION a blocului PL/SQL. Excepţiile predefinite sunt: Excepţie Cod

ORA Cod SQL

(SQLCODE)Când este ridicată?

ACCESS_INTO_NULL 06530 -6530 Se încearcă asignarea unei valori pentru un obiect neiniţializat (fără alocare de memorie)

CASE_NOT_FOUND 06592 -6592 În instrucţiunea CASE nu nici o clauză WHEN nu este satisfăcută, iar clauza ELSE lipseşte.

COLLECTION_IS_NULL 06531 -6531 Se încearcă execuţia unei metode în cazul unei structuri complexe de date neiniţializate.

CURSOR_ALREADY_OPEN 06511 -6511 Se încearcă deschiderea unui cursor deja deschis. Cursorul trebuie închis înainte de redeschidere.

DUP_VAL_ON_INDEX 00001 -1 Se încearcă înserarea/modificarea unei valori duble într-o coloană cu restricţia UNIQUE.

INVALID_CURSOR 01001 -1001 Se încearcă o operaţie nepermisă (în acel moment) cu un cursor (de ex.

Gheorghe Hazi


închiderea unui cursor nedeschis).

INVALID_NUMBER 01722 -1722 Conversie eronată a unui şir într-o valoare numerică (şirul nu este un număr valid).

LOGIN_DENIED 01017 -1017 Nume utilizator sau parolă eronată la conectarea la o bază de date Oracle.

NO_DATA_FOUND

01403 +100 Instrucţiunea SELECT care returnează un rând nu a găsit nimic.

NOT_LOGGED_ON 01012 -1012 Se încearcă un apel al bazei de date înainte de conectare la aceasta.

PROGRAM_ERROR 06501 -6501 Eroare internă a blocului PL/SQL.

ROWTYPE_MISMATCH 06504 -6504 Variabilă de tip rând incompatibilă cu definiţia cursorului (la o instrucţiune de atribuire).

SELF_IS_NULL 30625 -30625 Se încearcă apelul unei metode al unui obiect neiniţializat.

STORAGE_ERROR 06500 -6500 PL/SQL a depăşit memoria alocată sau memoria deteriorată.

SUBSCRIPT_BEYOND_ COUNT

06533 -6533 S-a referit o dată dintr-o colecţie a cărui index depăşeşte numărul de elemente din colecţie.

SUBSCRIPT_OUTSIDE_ LIMIT

06532 -6532 S-a referit o dată dintr-o colecţie a

Gheorghe Hazi


cărui index are o valoare invalidă (de ex. un număr negativ)

SYS_INVALID_ROWID 01410 -1410 Conversia unui şir de caractere într-o variabilă ROWID eronată.

TIMEOUT_ON_RESOURCE 00051 -51 Depăşirea timpului de acces la o resursă

TOO_MANY_ROWS 01422 -1422 Instrucţiunea SELECT INTO returnează mai mult de o înregistrare

VALUE_ERROR 06502 -6502 Un calcul aritmetic, o conversie sau o constrângere a valorii a generat o eroare.

ZERO_DIVIDE 01476 -1476 A program attempts to divide a number by zero.

01476 -1476 Încercare de a împărţi un număr la zero.

Exemplu: SET SERVEROUTPUT ON DECLARE nume employees.last_name%TYPE; BEGIN SELECT last_name INTO nume FROM employees WHERE employee_id=99; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Marca 99 nu este in tabela

EMPLOYEES !'); DBMS_OUTPUT.PUT_LINE('Cod eroare SQL: '||SQLCODE); DBMS_OUTPUT.PUT_LINE('Mesaj: '||SQLERRM); END; /

Marca 99 nu este in tabela EMPLOYEES !

Gheorghe Hazi


Cod eroare SQL: 100 Mesaj: ORA-01403: nu au fost găsite date Procedură PL/SQL încheiată cu succes. În exemplul de mai sus s-a afişat (în plus) codul de eroare SQL şi mesajul

transmis de baza de date, utilizându-se funcţiile SQLCODE şi SQLERRM. Funcţia SQLCODE dă valoarea:

• 0 - dacă nu s-a întâlnit o eroare • 1 – excepţie definită de utilizator • 100 – excepţia NO_DATA_FOUND • valori negative – alte erori ale bazei de date

Interceptarea excepţiilor non-predefinite în serverul Oracle presupune definirea şi asocierea codului ORA în secţiunea DECLARE şi tratarea ei în secţiunea EXCEPTION.

CONN HR/HR@UBC SET SERVEROUTPUT ON DECLARE insert_excep EXCEPTION; PRAGMA EXCEPTION_INIT (insert_excep, -01400); BEGIN INSERT INTO departments (department_id, department_name) VALUES (280, NULL); EXCEPTION WHEN insert_excep THEN DBMS_OUTPUT.PUT_LINE('Nu s-a putut insera o valoare nula !'); DBMS_OUTPUT.PUT_LINE('Cod eroare SQL: '||SQLCODE); DBMS_OUTPUT.PUT_LINE('Mesaj: '||SQLERRM); END; / Conectat. Nu s-a putut insera o valoare nula ! Cod eroare SQL: -1400 Mesaj: ORA-01400: nu poate fi inserat NULL în ("HR"."DEPARTMENTS"."DEPARTMENT_NAME") Procedură PL/SQL încheiată cu succes. Declararea unei excepţii se face în secţiunea DECLARE utilizând forma:

Gheorghe Hazi


nume_excepţie EXCEPTION; Asocierea erorii la un cod de eroare ORA standard se face cu funcţia

PRAGMA EXCEPTIOM_INIT: PRAGMA EXCEPTION_INIT(nume_excepţie, număr_eroare);

unde număr eroare este numărul ORA care poate fi detectat cu funcţia SQLERRM.

Se recomandă ca în clauza WHEN OTHERS să se intercepteze alte erori care nu au fost prevăzute la elaborarea blocurilor, dar care pot fi adăugate ulterior, la modificarea acestora. Ele vor fi afişate în programul de exploatare sau vor fi scrise într-o tabelă ca în exemplul de mai jos:

DECLARE num_eroare NUMBER; msg_eroare VARCHAR2(100); nume_user VRACHAR2(50); BEGIN

... EXCEPTION ... WHEN OTHERS THEN SELECT user INTO nume_user FROM dual; num_eroare := SQLCODE; msg_eroare := SUBSTR(SQLERRM, 1, 100); INSERT INTO errors VALUES (nume_user,num_eroare, msg_eroare); END; / Declararea şi interceptarea excepţiilor definite de utilizator presupune următoarele:

• Declararea excepţiei în secţiunea DECLARE. • Utilizarea instrucţiunii RAISE pentru ridicarea excepţiei în zona de

execuţie a blocului RAISE nume_excepţie;

unde nume_excepţie este numele declarat al acesteia. • Tratarea excepţiei în secţiunea EXCEPTION ACCEPT deptno number PROMPT 'Introduceti numărul de departament (pentru modificare):' ACCEPT nume char PROMPT 'Introduceti numele (nou) al departamentului:' SET VERIFY OFF

Gheorghe Hazi


SET SERVEROUTPUT ON DECLARE departament_rau EXCEPTION; nume_dep VARCHAR2(20):='&nume'; deptno NUMBER :=&deptno; BEGIN UPDATE departments SET department_name = nume_dep WHERE department_id = deptno; IF SQL%NOTFOUND THEN RAISE departament_rau; END IF; COMMIT; EXCEPTION WHEN departament_rau THEN DBMS_OUTPUT.PUT_LINE('Cod departament eronat ! Mai incercati !'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Cod eroare: '||SQLCODE); DBMS_OUTPUT.PUT_LINE('Mesaj: '||SQLERRM); END; / Dacă o excepţie este ridicată (implicit sau explicit) într-un bloc, iar acesta nu are o secţiune EXCEPTION, atunci excepţia poate fi tratată în blocul superior sau în blocul apelant. DECLARE

. . . no_rows exception;

integrity exception; PRAGMA EXCEPTION_INIT (integrity, -2292); BEGIN FOR c_record IN emp_cursor LOOP BEGIN SELECT ... UPDATE ... IF SQL%NOTFOUND THEN RAISE no_rows; END IF; END;

Gheorghe Hazi


END LOOP; EXCEPTION WHEN integrity THEN ... WHEN no_rows THEN ... END;

/ Pentru interceptarea erorilor într-o manieră proprie, se poate utiliza procedura

RAISE_APPLICATION_ERROR. Aceasta permite transmiterea erorilor din proceduri stocate, dar pot fi folosite şi în alte situaţii pentru transmiterea excepţiilor către procedurile apelante. Sintaxa procedurii: raise_application_error (error_number,message[, {TRUE | FALSE}]); unde

• error_number - reprezintă codul ORA atribuit excepţiei (diferit de cele alocate bazei de date, cu valori între -20000 şi -20999

• message – mesaj alocat excepţiei cu lungimea maximă de 2048 caractere. • TRUE/FALSE – este opţional şi permite adăugarea mesajului la stiva de

mesaje (pentru TRUE) alocată excepţiei (se adună mesaje de la mai multe nivele). Implicit opţiunea este FALSE şi, în acest caz, rămâne mesajul singular pentru eroare, fără a mai prelua mesaje de la alte nivele).

Exemplu: CREATE OR REPLACE PROCEDURE verif_nr_tabele(nume_user IN varchar2, numar OUT pls_integer) IS BEGIN SELECT COUNT(*) INTO numar FROM user_tables; IF numar > 5 THEN RAISE_APPLICATION_ERROR(-20001,'Sunt prea multe tabele in schema!'); END IF; END; / SET SERVEROUTPUT ON DECLARE mare EXCEPTION; PRAGMA EXCEPTION_INIT(mare,-20001); numar PLS_INTEGER; BEGIN

Gheorghe Hazi


verif_nr_tabele('HR',numar); EXCEPTION WHEN mare THEN DBMS_OUTPUT.PUT_LINE('HR are prea multe tabele!'); DBMS_OUTPUT.PUT_LINE('Cod eroare: '||SQLCODE); DBMS_OUTPUT.PUT_LINE('Mesaj :'||SQLERRM); END; / DROP procedure verif_nr_tabele; Procedură creată. HR are prea multe tabele! Cod eroare: -20001 Mesaj :ORA-20001: Sunt prea multe tabele in schema! Procedură PL/SQL încheiată cu succes. Procedură eliminată. Se observă că excepţia ridicată în procedura stocată verif_nr_tabele este

interceptată în blocul PL/SQL apelant. Aici este declarată excepţia şi îi este atribuită un cod ORA.

3. Proceduri şi funcţii stocate Sunt blocuri PL/SQL cu nume şi parametri care sunt stocate în baza de date

în schema unui utilizator. Pot fi executate de cei care au privilegiul obiect EXECUTE.

Diferenţele dintre blocurile fără nume cu care am lucrat până acum şi procedurile şi funcţiile stocate sunt date în tabelul următor:

Blocuri anonime Subprograme

Nu au nume Au un nume

Sunt compilate la execuţie

Sunt compilate la stocare

Nu sunt stocate în baza de date

Sunt stocate în baza de date

Nu pot fi apelate de alte aplicaţii

Pot fi apelate

Gheorghe Hazi


Nu returnează valori Procedurile pot întoarce valori în timp ce funcţiile întorc obligatoriu o valoare

Nu au parametri Pot avea parametri

Crearea unei proceduri are sintaxa: CREATE [OR REPLACE] PROCEDURE nume_procedură

[(argument1 [mod1] tipdată1, argument2 [mod2] tipdată2,

. . .)] IS|AS corp_procedură; unde

• mod1, mod2,.. reprezintă modul de lucru al argumentului respectiv: IN, OUT, IN OUT. Implicit este IN.

• tipdată1, tipdată2,.. – reprezintă tipul variabilei (number, varchra2,..). Un exemplu de procedură stocată este dat mai sus (la pct. 2). Apelul unei proceduri se face ca şi în alte limbaje de programare invocând

numele şi parametri actuali. Se poate face apelul şi din SQL Plus cu sintaxa CALL nume_procedură(argument1, argument2,..); Crearea unei funcţii are sintaxa: CREATE [OR REPLACE] FUNCTION nume_funcţie

[(argument1 [mod1] tipdată1, argument2 [mod2] tipdată2, . . .)]

RETURN tipdată IS|AS Corp_funcţie; Exemplu:

CREATE OR REPLACE FUNCTION verif_sal(empno number) RETURN Boolean IS dept_id employees.department_id%TYPE; sal employees.salary%TYPE; avg_sal employees.salary%TYPE;

BEGIN SELECT salary,department_id

Gheorghe Hazi


INTO sal,dept_id FROM employees WHERE employee_id= empno; SELECT avg(salary) INTO avg_sal FROM employees WHERE department_id=dept_id; IF sal > avg_sal THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;

END; /

SET SERVEROUTPUT ON BEGIN

IF (verif_sal(205) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('Functia da NULL printr-o exceptie!'); ELSIF (verif_sal(205)) THEN DBMS_OUTPUT.PUT_LINE('Salariu > media'); ELSE DBMS_OUTPUT.PUT_LINE('Salariu < media'); END IF; END; / Salariu > media Procedură PL/SQL încheiată cu succes. Pentru aflarea parametrilor unei proceduri se poate utiliza c-da SQL

DESCRIBE: DESCR verif_nr_tabele

PROCEDURE verif_nr_tabele Argument Name Type In/Out Default?

NUME_USER VARCHAR2 IN NUMAR BINARY_INTEGER OUT

Gheorghe Hazi


4. Utilizarea instrucţiunii PL/SQL EXECUTE IMMEDIATE Intrucţiunea permite executarea oricărei c-zi SQL, direct din PL/SQL: Sintaxa: EXECUTE IMMEDIATE cda_dinamică; Sau EXECUTE IMMEDIATE cda_dinamică USING parametru1, parametru2.. În cel de-al doilea caz în şirul cda_dinamică trebuie definiţi parametri de

legătură sub formă :1, :2.. Exemple: BEGIN EXECUTE IMMEDIATE 'DROP TABLE pers'; EXECUTE IMMEDIATE 'CREATE TABLE pers (marca number(3), nume varchar2(30))'; END; / DESCR PERS

Procedură PL/SQL încheiată cu succes. Nume Nul? Tip

MARCA NUMBER(3) NUME VARCHAR2(30)

Alt exemplu: CREATE TABLE pers (marca number(3), nume varchar2(30), salariu

number(5)); SET SERVEROUTPUT ON DECLARE instr_sql varchar2(100); BEGIN INSERT INTO pers VALUES (100,'Ionescu',1000); INSERT INTO pers VALUES (101,'Popescu',5000); instr_sql:='UPDATE pers SET salariu=salariu+:1 WHERE salariu < 2000'; EXECUTE IMMEDIATE instr_sql USING 500; EXCEPTION WHEN OTHERS THEN

Gheorghe Hazi


DBMS_OUTPUT.PUT_LINE('Cod eroare: '||SQLCODE); DBMS_OUTPUT.PUT_LINE('Mesaj: '||SQLERRM); END; / SELECT * FROM PERS; Tabelă creată. Procedură PL/SQL încheiată cu succes.

MARCA NUME SALARIU 100 Ionescu 1500 101 Popescu 5000

5. Lucrări de efectuat 1. Se execută scriptul lab_10_01.sql care creează un utilizator cu numele

dvs., iar în schema acestui utilizator creează tabela pers cu conţinutul tabelei hr.employees. De asemenea se creează tabela messages cu câmpul results VARCHAR2(80)

2. Să se elaboreze un bloc PL/SQL care să realizeze: a. Introducerea interactivă a unui salariu într-o variabilă de substituţie

(&sal) b. Citirea, din tabela pers creată la pct. 1, a numelui persoanei care are

acel salariu c. Dacă se găseşte o singură persoană cu acel salariu atunci se va

însera un rând în tabela messages care să conţină concatenat numele şi salariul sub forma „Ionescu – 6000”.

d. Dacă sunt mai mulţi salariaţi cu salariul respectiv sau dacă nu este nici unul în tabela messages se va însera un rând care să conţină mesajul potrivit. Aceste operaţii vor fi realizate în secţiunea EXCEPTION.

e. Pentru alte erori întâlnite, în afara celor de la litera d, se va însera, în tabela messages, un rând cu mesajul 'Exista erori si nu se poate executa modulul'.

f. Se execută modulul pentru valorile salariului 6000, 7700, 8500. g. O soluţie este dată în fişierul lab_10_02_sol.sql.

3. Să se realizeze un bloc PL/SQL care să însereze o înregistrare în tabela pers. Se va intercepta eroarea bazei de date cu codul ORA-00001, încălcarea restricţiei UNIQUE. Se vor însera câmpurile employee_id,

Gheorghe Hazi


first_name, last_name, email, hire_date, salary, job_id cu valorile 198, 'Mihai', 'Popescu', '[email protected]', '28-04-2008', 7000, 'CLREK'. În secţiunea EXCEPTION la tratarea excepţiei se va afişa numele persoanei care are, deja, marca 198. Pentru alte erori posibile se va afişa codul erorii şi mesajul aferent utilizând funcţiile SQLCODE şi SQLERRM. O soluţie este dată în fişierul lab_10_03_sol.sql.

4. Să se creeze o procedură stocată, cu numele ins_mesaj, care să însereze o înregistrare în tabela messages, textul înserat fiind transmis ca parametru. Se verifică funcţionarea procedurii din iSQL*Plus cu c-da CALL. Un exemplu este dat în fişierul lab_10_04_sol.sql.

5. Se creează un bloc PL/SQL fără nume care să apeleze procedura stocată creată la pct. 4. Textul înserat se va introduce interactiv. Un exemplu este dat în scriptul lab_10_05_sol.sql.

6. Să se creeze o funcţie stocată, nr_ord, care să returneze, pentru o marcă dată, numărul de ordine al salariului propriu, descrescător (nr. 1 ce mai mare salariu). Se testează dintr-un bloc PL/SQL cu marca cerută interactiv. Se testează pentru mărcile: 100, 178, 175 şi 540. O soluţie pentru funcţia nr_ord este dată în scriptul lab_10_06a_sol.sql, iar pentru blocul PL/SQL de apel în scriptul lab_10_06b_sol.sql.

7. Se şterge utilizatorul nume_student şi toate obiectele sale cu c-da DELETE USER

Gheorghe Hazi


LUCRAREA 11. IMPORTUL ŞI EXPORTUL DATELOR DIN BAZA DE DATE ORACLE

1. Scopul lucrării Lucrarea are drept scop însuşirea deprinderilor încărcarea, exportul şi importul datelor din baza de date Oracle.

2. Încărcarea datelor din surse externe Se poate face cu ENTERPRISE MANAGER sau cu utilitarul SQLLoader.

2.1. SQLLoader SQLLoader este un utilitar (SQLLDR.EXE în <Oracle home>\bin) care încarcă date din fişiere externe în baza de date. Realizează următoarele operaţii:

• Încarcă date prin reţea, adică se poate lansa de pe orice calculator client, diferit de serverul Oracle.

• Poate încărca date din mai multe fişiere în aceeaşi sesiune. • Poate încărca date în mai multe tabele în aceeaşi sesiune. • Se specifică caracteristicile datelor. • Se pot filtra datele preluate. • Se pot modifica datele preluate utilizând funcţiile SQL. • Se pot genera chei unice în coloane specificate. • Se utilizează fişiere ale sistemului de operare ca sursă ale datelor. • Se pot prelua date de pe diferiţi suporţi (disc, bandă magnetică, etc) • Se pot prelua date relaţionate (cu restricţii) • Se pot prelua date pe calea convenţională (prin insert – cu mai mute

facilităţi) sau direct (variantă mai rapidă dar cu limitări). Schema de funcţionare este dată în figura 1:

Gheorghe Hazi


Datele sunt încărcate dintr-un fişier de intrare de tip text. Specificaţii privind încărcarea datelor se găsesc în fişierul de control (de obicei extensia CTL). Informaţii privind înregistrările respinse datorită verificărilor făcute de SQLLoader şi de baza de date sunt trecute într-un fişier text (de obicei cu extensia BAD). Înregistrările nescrise în baza de date datorită filtrelor sunt trecute într-un fişier text separat. De asemenea se generează un raport al încărcării într-un fişier jurnal (extensia LOG). Fişierul de control (de tip text) conţine instrucţiuni specifice. Există mai multe secţiuni:

• Secţiunea I-a conţine informaţii despre sesiune cum ar fi: dimensiunea datelor de intrare, înregistrări sărite, fişierul de intrare prin clauza INFILE.

• Secţiunea II-a conţine una sau mai multe clauze INTO TABLE. În fiecare din acestea se precizează informaţii despre tabelele în care se încarcă date (nume, coloane)

• Secţiunea III-a conţine datele de intrare. Sintaxa în fişierul de control este liberă (instrucţiunile se pot întinde pe mai

multe linii). Pot fi utilizate litere mari sau mici, cu excepţia şirurilor incluse între ghilimele duble (”). Exemplu 1 de fişier de control: -- Comentariu LOAD DATA

Figura 1 Schema de funcţionare

SQLLoader

Gheorghe Hazi


INFILE 'exemplu.dat' BADFILE 'exemplu.bad' DISCARDFILE 'exemplu.dsc' REPLACE INTO TABLE emp WHEN (17) = '.' TRAILING NULLCOLS (hiredate SYSDATE, deptno POSITION(1:2) INTEGER EXTERNAL(2) NULLIF deptno=BLANKS, job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS "UPPER(:job)", mgr POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE NULLIF mgr=BLANKS, ename POSITION(34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ename)", empno POSITION(45) INTEGER EXTERNAL TERMINATED BY WHITESPACE, sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,'$99,999.99')", comm INTEGER EXTERNAL (3) ":comm / 100" ) În exemplul de mai sus facem următoarele precizări:

• Prin clauza BADFILE se specifică fişierul în care sunt scrise înregistrările respinse

• Prin clauza DISCARDFILE se specifică fişierul în care sunt scrise înregistrările nepreluate datorită filtrelor impuse

• Clauza APPEND precizează că înregistrările se adaugă la cele existente în tabelă. Pentru adăugarea de înregistrări în tabele goale se utilizează clauza INSERT. Cu clauza REPLACE înregistrările existente sunt şterse şi apoi sunt adăugate înregistrările noi.

• Clauza WHEN permite filtrarea înregistrărilor preluate. În acest caz, se preiau numai rândurile care au ”.” în coloana 57.

• Clauza TRAILING NULLCOLS permite tratarea coloanelor lipsă (după poziţia relativă) ca NULL.

• Următoarele rânduri descriu structura datelor de intrare. Clauza TERMINATED BY WHITESPACE delimitează câmpul cu orice character invizibil (spaţiu, LF, CR, FF, TAB).

Dacă fişierul de date cu numele exemplu.dat are conţinutul:

Gheorghe Hazi


30 CLERK . 100 POPESCU 101 $1,600.00 6 20 SEF . 103 IONESCU 105 $5,600.00 13 21 ING . 100 MIRCEA 101 $6,600.00 15 24 ING 107 ADRIAN 104 $3,600.00 10 21 ING . 100 ASTEFANII 101 6,600.00 15 Atunci c-da de lansare este: D:\>sqlldr test/test@hazi control = exemplu.ctl

Fişierul jurnal obţinut (exemplu.log): SQL*Loader: Release 10.2.0.1.0 - Production on V Mai 9 19:20:46 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Fişier de Control: exemplu.ctl Fişier de Date: exemplu.dat Fişier de înregistrări invalide: exemplu.bad Fişier de înregistrări ignorate: exemplu.dsc (Permite oricâte înregistrări nefiltrate) Număr pentru încărcare: ALL Număr pentru omitere: 0 Erori permise: 50 Vector de legătură: 64 linii, maximum 256000 octeţi Continuare: nimic specificat Încărcarea utilizată: Convenţională Tabela EMP, încărcată când 17:17 = 0X2e(caracter '.') Opţiune activă de inserare pentru această tabelă: REPLACE opţiunea TRAILING NULLCOLS activă Nume Coloană Poziţie Len Term Incl Tip de date ------------------------------ ---------- ----- ---- ---- -------------- HIREDATE SYSDATE DEPTNO 1:2 2 CHARACTER NULL dacă DEPTNO = BLANKS JOB 7:14 8 WHT CHARACTER NULL dacă JOB = BLANKS Şirul SQL pt. coloană : "UPPER(:job)"

Gheorghe Hazi


MGR 28:31 4 WHT CHARACTER NULL dacă MGR = BLANKS ENAME 34:41 8 WHT CHARACTER Şirul SQL pt. coloană : "UPPER(:ename)" EMPNO 45 * WHT CHARACTER SAL 51 * WHT CHARACTER Şirul SQL pt. coloană : "TO_NUMBER(:sal,'$99,999.99')" COMM NEXT 3 CHARACTER Şirul SQL pt. coloană : ":comm / 100" Înregistrarea 4: Nefiltrată - au eşuat toate clauzele WHEN. Înregistrarea 5: Invalidă - Eroare în tabela EMP, coloana SAL. ORA-01722: număr nevalid Tabela EMP: 3 Rânduri încărcate cu succes. 1 Rând neîncărcat datorită datelor eronate. 1 Rând neîncărcat deoarece toate clauzele WHEN au eşuat. 0 Rânduri neîncărcat deoarece toate câmpurile au fost nule. Spaţiu alocat pentru vectorul de legătură: 35328 octeţi (64 linii) Nr. de octeţi citiţi din buffer: 1048576 Total înregistrări logice omise: 0 Total înregistrări logice citite: 5 Total înregistrări logice invalide: 1 Total înregistrări logice nefiltrate: 1 Execuţia a început la V Mai 09 19:20:46 2008 Execuţia s-a terminat la V Mai 09 19:20:47 2008 Timpul trecut a fost: 00:00:00.16 Timpul UC a fost: 00:00:00.09

Fişierul exemplu.bad are conţinutul: 21 ING . 100 ASTEFANII 101 6,600.00 15 iar fişierul exemplu.dsc 24 ING 107 ADRIAN 104 $3,600.00 10

Gheorghe Hazi


În baza de date s-a scris: SQL> select * from emp; Tastati <ENTER> HIREDATE DEPTNO JOB MGR ENAME EMPNO SAL COMM ---------- -------- -------- ---------- -------- ---------- ---------- ------- 09-05-2008 30 CLERK 100 POPESCU 101 1600 ,06 09-05-2008 20 SEF 103 IONESCU 105 5600 ,13 09-05-2008 21 ING 100 MIRCEA 101 6600 ,15 Exemplu 2 de fişier de control (ex.ctl):

LOAD DATA INFILE 'ex.dat' BADFILE 'ex.bad' DISCARDFILE 'ex.dsc' REPLACE INTO TABLE PERS FIELDS TERMINATED BY ',' (marca, nume, salariu)

cu fişierul de date (ex.dat): 100, Popescu Ioan, 600 101, Mircea Octavian, 1700 100, Mihaila Ion, 600 106, Petrescu Andrei, 400 Fişierul jurnal (ex.log) are conţinutul: SQL*Loader: Release 10.2.0.1.0 - Production on V Mai 9 19:58:17 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Fişier de Control: ex.ctl Fişier de Date: ex.dat Fişier de înregistrări invalide: ex.bad Fişier de înregistrări ignorate: ex.dsc (Permite oricâte înregistrări nefiltrate) Număr pentru încărcare: ALL Număr pentru omitere: 0

Gheorghe Hazi


Erori permise: 50 Vector de legătură: 64 linii, maximum 256000 octeţi Continuare: nimic specificat Încărcarea utilizată: Convenţională Tabela PERS, încărcată din fiecare înregistrare logică. Opţiune activă de inserare pentru această tabelă: REPLACE Nume Coloană Poziţie Len Term Incl Tip de date ------------------------ ---------- ----- ---- ---- ------------ MARCA FIRST * , CHARACTER NUME NEXT * , CHARACTER SALARIU NEXT * , CHARACTER Înregistrarea 3: Invalidă - Eroare în tabela PERS. ORA-00001: restricţia unică (TEST.SYS_C006560) nu este respectată Înregistrarea 4: Invalidă - Eroare în tabela PERS. ORA-02290: regulă de constrângere (TEST.SAL_LIM) violată Tabela PERS: 2 Rânduri încărcate cu succes. 2 Rânduri neîncărcat datorită datelor eronate. 0 Rânduri neîncărcat deoarece toate clauzele WHEN au eşuat. 0 Rânduri neîncărcat deoarece toate câmpurile au fost nule. Spaţiu alocat pentru vectorul de legătură: 49536 octeţi (64 linii) Nr. de octeţi citiţi din buffer: 1048576 Total înregistrări logice omise: 0 Total înregistrări logice citite: 4 Total înregistrări logice invalide: 2 Total înregistrări logice nefiltrate: 0 Execuţia a început la V Mai 09 19:58:17 2008 Execuţia s-a terminat la V Mai 09 19:58:17 2008

Gheorghe Hazi


Timpul trecut a fost: 00:00:00.18 Timpul UC a fost: 00:00:00.07 Fişierul cu înregistrări respinse (ex.bad) are conţinutul:

100, Mihaila Ion, 600 106, Petrescu Andrei, 400

2.2. Încărcarea datelor cu EM

Permite încărcarea datelor folosind fişiere de control create potrivit

exemplelor de mai sus sau prin generarea automată a fişierului de control. Exemplificăm utilizarea ultimei variante, pentru exemplul 2 de mai sus (folosim numai fişierul de date ex.dat) ale cărui date vrem să le înserăm în tabela pers creată în schema utilizatorului test cu scriptul:

CREATE TABLE pers (marca number(4) primary key, nume varchar2(40) not null, salariu number(10,2) constraint sal_lim CHECK (salariu >= 500)); Lansarea se face din EM, tabul Maintenance, Load Data from User Files. După selectarea opţiunii, se afişează ecranul din figura 3 în care se bifează

opţiunea Automatically Generate Control File (în cazul nostru). Dacă vrem să utilizăm un fişier de control existent se selectează Use Existing Control File. De

Figura 2 Lansare procedură

încărcare date cu EM

Gheorghe Hazi


asemenea se introduce un user şi o parolă cu drepturi de administrare pe serverul Oracle. De precizat că utilizatorul trebuie să aibă setată opţiunea Log on as a batch job în Adminstrative Tools> Local security settings.

Se apasă <Continue>.

Pasul 1/7 - Se stabileşte locaţia şi numele fişierului de date (poate fi pe

server sau local). De obicei fişierul de date este situat pe calculatorul local. Se modifică sau se lasă locul de stocare a fişierului de manevră (implicit în folderul bazei de date de pe server). Se apasă <NEXT>.

Pasul 2/7 – Se stabileşte tabela în care se înserează datele – în cazul nostru test.pers. Se bifează formatul datelor de intrare (Data File Format): câmpuri delimitate prin virgulă sau câmpuri de lungime fixă. În cazul câmpurile dintr-o înregistrare sunt separate prin virgulă.

În partea de jos a ecranului (figura 5) se afişează (Preview Contents) conţinutul fişierului text cu date de intrare.

Figura 3 Stabilirea

modului de utilizare a

fişierului de control

Figura 4 Stabilirea fişierului de date (pas 1/7)

Gheorghe Hazi


Se apasă <Next>.

Pas 3/7 – Se stabileşte caracterul de delimitare între câmpuri (în cazul

nostru ”,”) şi opţional încadrarea individuală a câmpurilor în fişierul text de intrare. Chiar dacă Field Enclosure este completată câmpurile pot sau nu să fie încadrate de acel simbol. În dreptul câmpurilor pot fi stabilite condiţii NULLIF

Figura 5 Stabilirea tabelei destinaţie (pas 2/7)

Figura 6 Stabilirea caracter delimitare şi restricţii

pentru câmpuri (pas 3/7)

Gheorghe Hazi


sau DEFAULTIF (de exemplu NUME=BLANKS). În partea de jos a ecranului sunt afişate înregistrările ca şi cum ar fi înserate (fără verificarea restricţiilor).

Se apasă <NEXT>. Pas 4/7 – Se stabileşte metoda de încărcare. Metoda convenţională

foloseşte instrucţiunea INSERT. Metoda directă (eventual cu opţiunea Paralell) este mai rapidă însă are anumite restricţii (de exemplu nu se admit filtre prin clauza WHEN). Se recomandă metoda convenţională. Metoda directă se va utiliza după o analiză a funcţionării în cazul concret.

Se apasă <NEXT>.

Figura 7 Stabilirea metodei de încărcare (pas 4/7)

Figura 8 Stabilirea opţiuni pentru încărcare (pas 5/7)

Gheorghe Hazi


Pas 5/7 – Se stabilesc opţiunile pentru încărcare, după cum urmează: - numărul maxim de înregistrări ce vor fi încărcate (opţional) - numărul de înregistrări ignorate de la începutul datelor de intrare

(opţional) - dacă încărcarea se opreşte la numai la erori fatale (nu şi la cele de

atenţionare) - opţional - numărul maxim de erori la care se opreşte încărcarea – implicit 50 - limita maximă (la care procesul se opreşte) de înregistrări ignorate

(datorită filtrelor) - opţional - generarea sau nu a fişierului cu înregistrări respinse – dacă da şi

numele acestuia – în cazul nostru ex.bad - generarea sau nu a fişierului cu înregistrări ignorate – dacă da şi

numele acestuia – în cazul nostru ex.dsc - generarea sau nu a fişierului jurnal – dacă da şi numele acestuia – în

cazul nostru ex.log Se apasă <NEXT>.

Pas 6/7 – Se completează (opţional) numele job-ului în baza de date şi ora de execuţie: imediat sau la data şi ora stabilită. Se apasă <NEXT>.

Figura 9 Stabilire nume job şi programare oră (pas 6/7)

Gheorghe Hazi


Pas 7/7 – Se apasă <Submit job>. Pentru verificare se citesc fişierul jurnal pentru a verifica modul în care s-

a executat lucrarea. Eventual se citesc fişierele cu înregistrări respinse şi ignorate.

3. Exportul datelor din baza de date Oracle Se realizează cu utilitarul EXPDP sau cu EM.

3.1. Utilizarea EXPDP (Data Pump EXPORT) Există mai multe tipuri de export, în funcţie de volumul de date exportat:

• FULL – se exportă întreaga bază de date. Utilizatorul trebuie să aibă privilegiul EXP_FULL_DATABASE.

• SCHEMAS – se exportă datele din schema unui utilizator. Administratorii pot preciza mai multe scheme într-o sesiune.

• TABLES – se exportă una sau mai multe tabele • TABLESPACE – se exportă numai tabelele care sunt conţinute (integral)

în lista de tablespace exportat • TRANSPORT_TABLESPACES – permite transportul unui tablespace de

la o bază de date la alta. Se recomandă ca exportul să fie făcut numai de administratorii bazei de date,

de regulă utilizatorul SYSTEM. Exemple: a) Exportul tabelelor employees şi regions din schema hr: D:\>EXPDP system/george@hazi tables=hr.employees,hr.regions

DUMPFILE=hr_emp.dmp DIRECTORY=DATA_PUMP_DIR Export: Release 10.2.0.1.0 - Production on Vineri, 09 Mai, 2008 22:39:15 Copyright (c) 2003, 2005, Oracle. All rights reserved. Conectat la: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

Production With the Partitioning, OLAP and Data Mining options Pornire "SYSTEM"."SYS_EXPORT_TABLE_01":

Gheorghe Hazi


system/********@hazi tables=hr.employees,hr.regions DUMPFILE=hr_emp.dmp DIRECTORY=DATA_PUMP_DIR

Estimare în curs, utilizând metoda BLOCKS... Procesare a tipului de obiect TABLE_EXPORT/TABLE/TABLE_DATA Estimare totală prin utilizarea metodei BLOCKS: 128 KB Procesare a tipului de obiect TABLE_EXPORT/TABLE/TABLE Procesare a tipului de obiect

TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Procesare a tipului de obiect TABLE_EXPORT/TABLE/INDEX/INDEX Procesare a tipului de obiect

TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Procesare a tipului de obiect

TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Procesare a tipului de obiect TABLE_EXPORT/TABLE/COMMENT Procesare a tipului de obiect

TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Procesare a tipului de obiect TABLE_EXPORT/TABLE/TRIGGER Procesare a tipului de obiect

TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . "HR"."EMPLOYEES" 15.76 KB 107 linii exportate . . "HR"."REGIONS" 5.296 KB 4 linii exportate Tabelul principal "SYSTEM"."SYS_EXPORT_TABLE_01" a fost

încărcat/descărcat cu succes ***********************************************************

***** Setul de fişiere de stocare pentru SYSTEM.SYS_EXPORT_TABLE_01

este:

D:\ORACLE\PRODUCT\10.2.0\ADMIN\HAZI\DPDUMP\HR_EMP.DMP Job-ul "SYSTEM"."SYS_EXPORT_TABLE_01" a fost finalizat cu

succes la 22:39:34 D:\> În comanda de mai sus apar doi parametri specifici: - DUMPFILE – care reprezintă numele fişierului de export, cu extensia

(uzuală) dmp. - DIRECTORY – reprezintă obiectul bazei de date care se referă la o

Gheorghe Hazi


cale unde vor fi stocate fişierele de export. Implicit se utilizează directoarea DATA_PUMP_DIR.

Vizualizarea căii care corespunde cu directoarele poate fi făcută cu c-da SQL: SQL> select * from all_directories; Tastati <ENTER> OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH --------------------------------------------------------------- SYS SUBDIR D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\/2002/Sep SYS XMLDIR D:\oracle\product\10.2.0\db_1\demo\schema\order_entry\ SYS MEDIA_DIR D:\oracle\product\10.2.0\db_1\demo\schema\product_media\ SYS LOG_FILE_DIR D:\oracle\product\10.2.0\db_1\demo\schema\log\ SYS WORK_DIR C:\ADE\aime_10.2_nt_push\oracle/work SYS DATA_FILE_DIR D:\oracle\product\10.2.0\db_1\demo\schema\sales_history\ SYS DATA_PUMP_DIR D:\oracle\product\10.2.0\admin\hazi\dpdump\ SYS ADMIN_DIR C:\ADE\aime_10.2_nt_push\oracle/md/admin b) Exportul tuturor datelor din schema hr: D:\>EXPDP system/george@hazi schemas=hr DUMPFILE=hr.dmp

DIRECTORY=DATA_PUMP_DIR Export: Release 10.2.0.1.0 - Production on Vineri, 09 Mai, 2008 22:52:04

Gheorghe Hazi


Copyright (c) 2003, 2005, Oracle. All rights reserved. Conectat la: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

Production With the Partitioning, OLAP and Data Mining options Pornire "SYSTEM"."SYS_EXPORT_SCHEMA_01":

system/********@hazi schemas=hr DUMPFILE=hr.dmp DIRECTORY=DATA_PUMP_DIR

Estimare în curs, utilizând metoda BLOCKS... Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/TABLE_DATA Estimare totală prin utilizarea metodei BLOCKS: 448 KB Procesare a tipului de obiect SCHEMA_EXPORT/USER Procesare a tipului de obiect SCHEMA_EXPORT/SYSTEM_GRANT Procesare a tipului de obiect SCHEMA_EXPORT/ROLE_GRANT Procesare a tipului de obiect SCHEMA_EXPORT/DEFAULT_ROLE Procesare a tipului de obiect

SCHEMA_EXPORT/TABLESPACE_QUOTA Procesare a tipului de obiect

SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Procesare a tipului de obiect SCHEMA_EXPORT/TYPE/TYPE_SPEC Procesare a tipului de obiect

SCHEMA_EXPORT/SEQUENCE/SEQUENCE Procesare a tipului de obiect SCHEMA_EXPORT/TABLE/TABLE Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/INDEX/INDEX Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Procesare a tipului de obiect SCHEMA_EXPORT/TABLE/COMMENT Procesare a tipului de obiect

SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Procesare a tipului de obiect

SCHEMA_EXPORT/PROCEDURE/PROCEDURE Procesare a tipului de obiect

SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/A

Gheorghe Hazi


LTER_PACKAGE_SPEC Procesare a tipului de obiect

SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Procesare a tipului de obiect SCHEMA_EXPORT/VIEW/VIEW Procesare a tipului de obiect

SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Procesare a tipului de obiect SCHEMA_EXPORT/TABLE/TRIGGER Procesare a tipului de obiect

SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . "HR"."COUNTRIES" 6.085 KB 25 linii exportate . . "HR"."DEPARTMENTS" 6.632 KB 27 linii

exportate . . "HR"."EMPLOYEES" 15.76 KB 107 linii exportate . . "HR"."JOB_HISTORY" 6.585 KB 10 linii exportate . . "HR"."JOBS" 6.609 KB 19 linii exportate . . "HR"."LOCATIONS" 7.710 KB 23 linii exportate . . "HR"."REGIONS" 5.296 KB 4 linii exportate Tabelul principal "SYSTEM"."SYS_EXPORT_SCHEMA_01" a fost

încărcat/descărcat cu succes ***********************************************************

************* Setul de fişiere de stocare pentru

SYSTEM.SYS_EXPORT_SCHEMA_01 este: D:\ORACLE\PRODUCT\10.2.0\ADMIN\HAZI\DPDUMP\HR.DMP Job-ul "SYSTEM"."SYS_EXPORT_SCHEMA_01" a fost finalizat cu

succes la 22:52:33 Se poate utiliza şi un fişier cu parametri sub forma: D:> EXPDP system/george@ubc PARFILE=hr.par

unde hr.par este un fişier text cu conţinutul (de exemplu): QUERY=employees:'"WHERE department_id > 10 AND salary >

10000"' NOLOGFILE=y DIRECTORY=DATA_PUMP_DIR DUMPFILE=HR.DMP În exemplul de mai sus s-a utilizat şi parametrul QUERY care permite

filtrarea datelor exportate.

Gheorghe Hazi


3.2. Exportul datelor cu EM

Se activează din EM, tab-ul Maintenance, Export to Export Files. După activare se alege tipul de export şi se introduce userul şi parola unui

administrator al sistemului de operare de pe server. Se apasă <CONTINUE>. Paşii pentru export sunt: Pas 1/5 - alegerea schemelor (în cazul nostru numai HR) sau a tabelelor

sau a tablespace salvate. Pas 2/5 – Stabilirea opţiunilor: număr procese în paralel (implicit 1),

estimarea opţională a spaţiului necesar pentru fişierul de export, fişierul jurnal al exportului.

Pas 3/5 – Stabilirea DIRECTORY (DATA_PUMP_DIR), a numelui fişierului de export (EXP_HR.DMP) şi, eventual, a dimensiunii maxime a fişierului de export (în MB).

Pas 4/5 – Stabilirea numelui pentru job-ul de salvare, a orei şi datei de execuţie. Job-ul se poate executa şi imediat.

Pas 5/5 – Lansarea în execuţie prin apăsarea butonului <Submit job>.

Figura 10 Exportul datelor cu EM. Stabilirea tipului de export

Gheorghe Hazi


4. Importul datelor în baza de date Oracle În acest paragraf ne referim la importul datelor din fişiere (tip DMP)

create cu EXPDP sau cu EM.

4.1. Importul datelor cu IMPDP (Data Pump IMPORT) Este perechea aplicaţiei EXPDP, care permite importul datelor exportate

cu acest program. Din acest motiv parametrii de funcţionare sunt similari. De exemplu importul tabelei hr.employees în schema utilizatorului test. D:\>IMPDP system/george@hazi DUMPFILE=HR_EMP.DMP

REMAP_SCHEMA=hr:test Import: Release 10.2.0.1.0 - Production on Vineri, 09 Mai, 2008 23:54:28 Copyright (c) 2003, 2005, Oracle. All rights reserved. Conectat la: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

Production With the Partitioning, OLAP and Data Mining options Tabelul principal "SYSTEM"."SYS_IMPORT_FULL_01" a fost

încărcat/descărcat cu succes Pornire "SYSTEM"."SYS_IMPORT_FULL_01":

system/********@hazi DUMPFILE=HR_EMP.DMP REMAP_SCHEMA=hr:test

Procesare a tipului de obiect TABLE_EXPORT/TABLE/TABLE Procesare a tipului de obiect TABLE_EXPORT/TABLE/TABLE_DATA . . "TEST"."EMPLOYEES" 15.76 KB 107 linii

importate Procesare a tipului de obiect

TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Procesare a tipului de obiect TABLE_EXPORT/TABLE/INDEX/INDEX Procesare a tipului de obiect

TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Procesare a tipului de obiect

Gheorghe Hazi


TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Procesare a tipului de obiect TABLE_EXPORT/TABLE/COMMENT Procesare a tipului de obiect

TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT ORA-39083: Crearea tipului de obiect REF_CONSTRAINT a eşuat cu

eroare: ORA-00942: tabelul sau vizualizarea nu există Instrucţiunea SQL eşuată este: ALTER TABLE "TEST"."EMPLOYEES" ADD CONSTRAINT

"EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "TEST"."JOBS" ("JOB_ID") ENABLE

ORA-39083: Crearea tipului de obiect REF_CONSTRAINT a eşuat cu

eroare: ORA-00942: tabelul sau vizualizarea nu există Instrucţiunea SQL eşuată este: ALTER TABLE "TEST"."EMPLOYEES" ADD CONSTRAINT

"EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "TEST"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE

Procesare a tipului de obiect TABLE_EXPORT/TABLE/TRIGGER ORA-39082: Tipul de obiect

TRIGGER:"TEST"."SECURE_EMPLOYEES" a fost creat cu avertismente la compilare

ORA-39082: Tipul de obiect TRIGGER:"TEST"."SECURE_EMPLOYEES" a fost creat cu avertismente la compilare

ORA-39082: Tipul de obiect TRIGGER:"TEST"."UPDATE_JOB_HISTORY" a fost creat cu avertismente la compilare

ORA-39082: Tipul de obiect TRIGGER:"TEST"."UPDATE_JOB_HISTORY" a fost creat cu avertismente la compilare

Procesare a tipului de obiect TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job-ul "SYSTEM"."SYS_IMPORT_FULL_01" a fost finalizat cu 6 erori la 23:54:36

Se observă aici opţiunea REMAP_SCHEMA=hr:test prin care s-a

Gheorghe Hazi


importat tabela employees în schema test. Se observă că la importul în schema test s-au generat erori întrucât nu a

putut realiza relaţiile dintre tabela employees şi celelalte tabele (care nu mai există în schema test).

De precizat că la importul unei tabele în altă schemă, utilizatorul nou (în cazul nostru test) trebuie să aibă cotă alocată pe tablespace din care provine tabela (în cazul nostru EXAMPLES).

4.2. Importul datelor cu EM

Lansarea importului se face din tab-ul Maintenance, Import from Export

Files. După lansare se alege directoarea în care se găseşte fişierul de export (în

cazul nostru DATA_PUMP_DIR) şi numele fişierului de export (EXP_HR.DMP). Se stabileşte tipul de import: tot fişierul, schema, tabele sau tablespace. De asemenea se introduce userul şi parola unui administrator al serverului. În cazul nostru importăm tabela employees din exportul integral HR în schema test.

Se apasă <CONTINUE>. Se citeşte fişierul de import.

Figura 11 Importul datelor cu EM. Stabilirea tipului de import

Gheorghe Hazi


Pas 1- Se alege fişierul care se importă din fişierul de export. Se apasă

<Select> şi apoi <Next>. Pas 2 – Se face remaparea tabelei din schema sursă HR în schema

destinaţie test (figura 13). Se apasă <Next>. Pas 3 – Se stabliesc opţiunile de import. Numărul de procese în paralel

Figura 12 Selecţia tabelei importate din fişierul de export

Figura 13 Remaparea tabelei din schema hr în schema test

Gheorghe Hazi


(implicit 1) şi fişierul jurnal al importului (figura 14). Se apasă <Next>. Pas 4 - Se stabileşte numele job-ului (opţional) şi programarea execuţiei

(figura 15). Pas 5 – Se apasă <Submit job>. După import se verifică fişierul jurnal pentru a vedea dacă nu sunt erori.

În fişierul jurnal vom găsi erori legate de relaţiile tabelei employees care nu au putut să fie restabilite.

Figura 14 Stabilire opţiuni import

Figura 15 Stabilire nume job şi planul de execuţie a importului

Gheorghe Hazi


5. Lucrări de efectuat 5.1. Se lansează în execuţie scriptul lab_11_01.sql. Execuţia se face în

iSQL*Plus (http://192.168.4.45:5560/isqlplus ). Scriptul creează un utilizator nume_student şi, în schema acestuia, o

tabela materiale cu structura: cod number(6) constraint mat_pk primary key, denumire varchar2(40) not null, um char(3) not null, pret number(8,2) not null, stoc number(7,2) 5.2. Se va încărca în tabela materiale din schema nume_student datele din

fişierul text materiale.dat, cu următoarele precizări: - se va utiliza Oracle EM conectat ca system/george – normal - se va genera automat fişierul de control - se va utiliza nume/parolă administrator sever: george/george - fişierul materiale.dat va fi încărcat de pe calculatorul local - se verifică cu atenţie fişierul de intrare pentru a preciza delimitatorii

potriviţi - se va utiliza calea convenţională de încăcare - înregistrările refuzate vor fi scrise în fişierul materiale.bad - înregistrările filtrate vor fi scrise în fişierul materiale.dsc - fişierul jurnal va fi materiale.log - toate fişierele de mai sus vor fi generate în aceeaşi locaţie cu fişierul de

intrare materiale.dat - numele job-ului va fi nume_student_mat Se va analiza dacă sunt înregistrări respinse şi se va relua procesul pentru

a fi încărcate şi eventuale înregistrări rejectate. După fiecare execuţie se va şterge job-ul activat.

Se verifică înregistrările scrise cu SQL Plus sau cu iSQL Plus. 5.3. Se vor exporta toate obiectele utilizatorului nume_student, în

următoarele condiţii: - se va utiliza Oracle EM user: system, parolă: george, normal. - se va utiliza nume/parolă administrator host: george/george - se va depune fişierul de export în directoarea DATA_PUMP_DIR

http://192.168.4.45:5560/isqlplus�

Gheorghe Hazi


- se va crea fişierul log cu numele: nume_student.log în folderul de mai sus

- numele fişierului de export va fi nume_student.dmp - se va atribui numele job-ului în baza de date exp_nume_student - jobul va fi pornit imediat - după execuţie se citeşte fişierul jurnal pentru a vedea modul în care a

fost realizat exportul - se şterge job-ul care a realizat exportul 5.4. Din iSQL Plus se şterge tabela materiale cu c-da SQL: DROP TABLE materiale CASCADE CONSTRAINTS PURGE; Se execută importul tabelei din exportul realizat la pct. 5.3, cu precizările

de mai jos: - se va utiliza Oracle EM user: system, parolă: george, normal. - se va utiliza nume/parolă administrator host: george/george - la Import Type se va selecta Tables - se va crea fişierul log cu numele: imp_nume_student.log în folderul

DATA_PUMP_DIR - se va atribui numele job-ului în baza de date imp_nume_student - jobul va fi pornit imediat - după execuţie se citeşte fişierul jurnal pentru a vedea modul în care a

fost realizat exportul - se verifică din iSQL Plus că tabelul materiale a fost restaurat cu acelaşi

conţinut - se şterge job-ul care a realizat importul - se şterge fişierul de export şi fişierele jurnal

5.5. Cu EM se şterge utilizatorul nume_student şi toate obiectele care îi

aparţin.

Gheorghe Hazi


LUCRAREA 12. SALVAREA DATELOR ÎN ORACLE

1. Scopul lucrării Lucrarea are drept scop însuşirea cunoştinţelor legate de salvarea şi restaurarea datelor din baza de date Oracle. În plus se arată soluţii pentru protecţia datelor în cazul unei erori umane (ştergerea din greşeală a datelor).

2. Facilităţi pentru salvarea şi restaurarea datelor Oracle oferă o serie de facilităţi pentru protecţia datelor în caz de incidente.

În cazul căderii bazei de date, procesul de recuperare are loc la prima repornire a acesteia. Prin procesul de recuperare fişierele de date sunt refăcute într-o stare consistentă, cu toate înregistrările comise până în momentul incidentului. Cea mai frecventă situaţie de recuperare voită (la iniţiativa utilizatorilor) a bazei de date este aceea când se recuperează fişierele bazei de date în urma deteriorării fizice a suportului de stocare a datelor. Pot fi recuperate fişierele de date, fişierul cu parametrii de iniţializare sau fişierele de control. Oracle Flashback este o facilitate suplimentară care permite vizualizarea şi restaurarea datelor la un moment dat (în urmă), cu mai multe unelte:

• Flashback Query – permite interogarea datelor dintr-un moment trecut ca şi cum interogarea ar fi făcută în acel moment.

• Flashback Versions Query - permite vizualizarea tuturor versiunilor unor înregistrări din una sau mai multe tabele, într-un interval de timp specificat.

• Flashback Transaction Query – permite vizualizarea modificărilor dintr-o perioadă dată ca o singură tranzacţie sau prin toate tranzacţiile din acea perioadă.

• Flashback Table – permite refacerea unei tabele într-o stare anerioară. • Flashback Drop – reface o tabelă la ştergerea ei din greşeală (cu DROP

Gheorghe Hazi


TABLE). • Flashback database – este o cale eficientă de refacere a bazei de date

corespunzător unui moment anterioar.

3. Concepte privind salvarea şi restaurarea datelor Prin backup se realizează o simplă copiere a fişierelor de date, fişiere de

control, precum şi fişierele jurnal. Restaurarea backup-ului este procesul invers, de copiere a fişierelor l de salvare (disc sau bandă magnetică) în locaţia lor.

Se poate realiza un backup consistent sau inconsistent. Un backup consistent este acela în care baza de date poate fi deschisă

imediat după restaurarea fişierelor. Realizarea unui astfel de backup presupune că toate modificările înregistrate în fişierele jurnal au fost aplicate fişierelor de date. În acest caz trebuie închisă baza de date şi oprirea instanţei Oracle pentru realizarea backup-ului.

Într-un backup inconsistent fişierele jurnal pot conţine modificări care nu sunt aplicate fişierelor de date. Acest tip de backup poate fi însă realizat cu baza de date deschisă. În schimb, pentru a utiliza backup-ul inconsistent, este necesară lansarea procesului de recuperare a bazei de date ca în situaţia deteriorării fizice a suportului de date. La restaurarea fişierelor jurnal şi a fişierelor de date dintr-un backup, serverul Oracle realizează mai întâi recuperarea datelor (ca în situaţia deteriorării fizice a suportului de date) dacă se încearcă deschiderea bazei de date. Tranzacţiile din fişierele jurnal care nu sunt reflectate în fişierele de date, sunt aplicate acestora. Tranzacţiile necomise sunt anulate printr-un rolback. În acest fel baza de date este adusă într-o stare consistentă înainte de deschidere. Recuperarea la deteriorarea suporţilor fizici de date (recuperare media) poate fi făcută complet sau la un moment în timp. În varianta completă, toate modificările din fişierele jurnal sunt aplicate bazei de date, aceasta revenind la starea ei înaintea incidentului. Baza de date se poate deschide fără pierderea de date. În varianta recuperării la un moment în timp, baza de date va reveni la starea aferentă momentului ales. Se porneşte cu fişierele de date salvate înainte de momentul respectiv, şi se completează cu datele din fişierele jurnal dintre momentul salvării şi momentul ales pentru restaurare. Restaurarea la un moment în timp se mai numeşte şi recuperare incompletă, întrucât nu sunt aplicate toate modificările din fişierele jurnal.

Gheorghe Hazi


Realizarea recuperării media necesită fişierul de control şi fişierele de date restaurate dintr-un backup şi toate datele din fişiere jurnal după momentul salvării. Paşii pentru recuperarea bazei de date în cazul deteriorării fizice a suporţilor de date, sunt arătaţi în figura 1. Aceştia sunt: 1) Fişierele deteriorate sau cele lipsă sunt restaurate dintr-un baxkup. 2) Modificările ulterioare backup-ului din fişierele jurnal sunt aplicate dacă este necesar. În acest timp sunt generate blocuri cu tranzacţii nedefinitive (necomise). 3) Baza de date conţine toate modificările, inclusiv tranzacţiile necomise. 4) Blocurile cu tranzacţiile nedefinitive sunt utilizate pentru a se realiza rollback (anularea tranzacţiilor necomise). 5) Baza de date este recuperată.

4. Configurarea bazei de date pentru salvare şi restaurare Pentru a utiliza la maxim facilităţile Oracle privind automatizarea proceselor de salvare şi recuperare se recomandă următoarele configurări ale bazei de date:

• Utilizarea zonei de stocare pentru salvări (flash recovery area) pentru operaţiile de salvare.

• Lucru cu baza de date în mod ARCHIVELOG (arhivarea fişierelor jurnal) pentru a putea realiza backup online.

Figura 1 Paşii pentru recuperarea bazei de date în cazul unui

incident media

Gheorghe Hazi


• Utilizarea zonei de stocare pentru salvări şi pentru stocarea fişierelor jurnal arhivate.

Dimensiunea zonei de stocare pentru salvări este optimă dacă poate prelua două seturi complete de salvare plus fişierele jurnal necesare pentru restaurare pentru intervalul dintre două salvări. Configurările necesare pentru buna funcţionare a procedurilor de salvare/recuperare se face cu c-zi SQL sau din EM (figura 2). Lansarea paginii de configurare se face din tab-ul Maintenance al EM, Backup/Recovery Settings, Recovery Settings. Aici se fac următoarele configurări:

• Se bifează caseta ARCHIVELOG mode. • Se lasă ca destinaţie a fişierelor jurnal arhivate

USE_DB_RECOVERY_FILE_DEST, cu valoarea uzuală oracle\product\10.2.0/flash_recovery_area. Această cale poate fi modificată.

• Se precizează spaţiul alocat pentru salvări (implicit 2GB). • Dacă se modifică ARCHIVELOG are loc o restartare a bazei de date.

5. Tipuri de salvări

În funcţie de volumul salvat pot fi următoarele tipuri de salvări:

Figura 2 Configurarea zonei de salvare

Gheorghe Hazi


• Salvarea integrală a fişierelor de date. • Incremental – se salvează numai blocurile modificate din fiecare fişier al

bazei de date. În varianta tipică există două niveluri: o 0 – în care salvează toate blocurile, fiind numit punct de start o 1 - executat la intervale regulate şi în care se salvează numai

blocurile modificate. Nivelul 1 poate fi cumulativ, adică se salvează toate blocurile modificate de la ultimul backup de nivel 0 sau incremental, caz în care numai blocurile modificate de la ultimul backup 0 sau 1 sunt salvate.

• Salvarea fişierelor de date, a fişierului de control, a fişierului de iniţializare şi a fişierelor jurnal arhivate. Cu acest tip de salvare baza de date poate fi recuperată complet. Din punct de vedere al modului de stocare a datelor salvate avem

următoarele variante: • salvări directe ale fişierelor bazei de date (copiere fizică) • seturi de salvare cu avantajul reducerii dimensiunii (nu se salvează

blocurile goale)

6. Realizarea salvărilor cu EM Înainte de lansarea sau de programarea backup-ului se realizează configurarea acesteia din tabul Maintenance al EM, Backup/Recovery Settings, Backup Settings. Programarea salvării se face cu opţiunea Schedule Backup.

Figura 3 Tipuri de fişiere de salvare

Gheorghe Hazi


Procesul de salvare şi opţiunile sunt diferite în funcţie de modul de funcţionare a bazei de date: ARCHIVELOG sau NOARCHIVELOG.

7. Lucrări de efectuat 1. Se lansează EM (http://192.168.4.45:1158/em ) şi se conectează cu sys/george as SYSDBA. 2. Se trece baza de date în mod ARCHIVELOG (figura 5). 2.1. Se verifică că salvările se fac în zona implicită pentru acest lucru (flash recovery area) prin existenţa opţiunii 10 din tabelul de mai jos. 2.2. Se bifează Enable Flashback Database. 2.3.Se apasă <APPLY> şi se confirmă. 2.4. Se introduc utilizatorul şi parola pentru administratorul SO (george/george) şi pentru BD (sys/george). Se apsasă OK şi se confirmă restartarea BD.

Figura 4 Macheta programare backup

http://192.168.4.45:1158/em�

Gheorghe Hazi


2.5. După câteva minute se reface logarea în EM apsăsând <Refresh>. 3. Se configurează backup (Backup settings) 3.1. În tabul Device se verifică setările Parallelism=1, iar câmpul Disk Backup Location se lasă gol pentru a utiliza zona implicită de salvare. 3.2. În tabul Policy se bifează Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change. 3.3. Se specifică user/parolă SO BD şi apasă <OK>.

Figura 5 Trecerea BD în mod ARCHIVELOG

Figura 6 Salvarea bazei de

date

Gheorghe Hazi


4. Crearea unui backup pentru întreaga bază de date (Schedule Backup)

4.1. Se bifează Whole Database. 4.3. Se apasă Schedule Customized Backup. 4.4. Se selectează Full Backup, Online Backup, Also back up all archived logs on disk şi se apasă <Next> 4.5. Se reţine că s-au preluat configurările de la pasul 3 şi se apasă iar <Next>. 4.6. Se setează start pe Immediately şi One Time Only. Se apasă iar <Next>. 4.7. Se dă click pe <Submit Job>. 4.8. Se apasă <OK>. 5. Verificarea setului de salvare (Manage Current Backups). 6. Generarea unei erori prin ştergerea unui fişier de date. 6.1. Se opreşte baza de date. 6.2. Din SO se şterge fişierul de date EXAMPLE01.DBF 6.3. Se reporneşte baza de date 6.4. La repornire va apare un mesaj de eroare (lipsa fişierului de date). 6.5. Se apasă <Perform Recovery> 6.6. Se setează Recover to the current time or a previous point-in-time şi se apasă <Perform whole Database Recovery>.

Figura 7 Verificarea setului de salvare

Gheorghe Hazi


6.7. La solicitarea locului de restaurare, se alege : No. Restore the files to the default location. 6.8. Se apasă <Submit>. 6.9. Se apasă <Open Database>. 7. Simulare ştergere din greşeală date.

7.1. Din SQL Plus se afişează timpul curent şi se notează: SQL> select systimestamp from dual; Tastasti <ENTER> SYSTIMESTAMP ------------------------------------ 16-05-2008 10:32:31,578000 +03:00 SQL> 7.2. Din SQL Plus conectat ca HR se şterg înregistrările din tabela

HR.JOB_History şi se face COMMIT. 7.3. Cu EM se editează tabela HR.JOB_HISTORY şi se setează Enable

Row Movement. 7.4. Cu EM se selectează tabela JOB_HISTORY şi din lista ascunsă se lansează Flashback Table. 7.5. Se selectează Flashback to a timestamp şi se setează timpul afişat mai sus (înainte de ştergere). Se apasă <Next> 7.6. La pasul 5 se setează Restrict : Flashback the selected tables only. This can result in inconsistent data. 7.7. Se apasă <Submit>. 7.8. Se verifică din SQL Plus restaurarea datelor. 8. Revenirea la starea anterioară 8.1. Cu EM se şterg seturile de salvare 8.2. Cu SQL Plus se opreşte baza de date SQL> shutdown immediate BD s-a închis. S-a demontat BD. Instanţa ORACLE s-a închis. SQL> 8.3. Se porneşte baza de date faza montare. SQL> startup mount A pornit instanţa ORACLE. Tastasti <ENTER>

Gheorghe Hazi


Total System Global Area 209715200 bytes Fixed Size 1248140 bytes Variable Size 117441652 bytes Database Buffers 88080384 bytes Redo Buffers 2945024 bytes S-a montat BD. SQL> 8.4. Se anulează arhivarea fişierelor jurnal şi falashback. Se deschide baza de date. SQL> alter database flashback off; Bazã de date modificată. SQL> alter database noarchivelog; Bazã de date modificată. SQL> alter database open; Bază de date modificată. SQL>

Gheorghe Hazi


BIBLIOGRAFIE

1. Hazi, Gh. – Baze de date II, Editura Alma Mater Bacău, 2007. 2. ORACLE - Oracle 2 Day DBA, www.oracle.com, 2005 3. ORACLE - Administrator’s Guide, Oracle 10g, www.oracle.com, 2006 4. ORACLE - Oracle® Database Installation Guide 10g Release 2 (10.2)

for Microsoft Windows (32-Bit), www.oracle.com, 2006 5. ORACLE - Oracle® Database 2 Day + Performance Tuning Guide

10g Release 2 (10.2), www.oracle.com, 2006 6. ORACLE - Oracle® Database SQL Reference 10g Release 2 (10.2),

www.oracle.com, 2006 7. ORACLE - SQL*Plus® User’s Guide and Reference Release 10.2,

www.oracle.com, 2006

http://www.oracle.com/�






Baze Date Lab-Tot

Documents

Transcript of Baze Date Lab-Tot