CUPRINS Avertisment: Copyright-ul traducerii apartine Catedrei de Calculatoare, Facultatea de Automatica si Calculatoare, Universitatea "Politehnica" din Bucuresti. Orice folosire a acestei traduceri in scopuri comerciale se poate face doar cu acordul posesorului copyright-ului manualului si a posesorului copyright-ului traducerii, in conditiile legii romane in materie de drepturi de autor CAPITOLUL 1 - Introducere in Oracle Ce este Oracle Produsele Oracle SQL, SQL*PLUS si PL/SQL Abordarea relationala CAPITOLUL 2 - Introducere in SQL Privire de ansamblu asupra SQL Trasaturi caracteristice SQL Setul de comenzi SQL Blocul de interogare de baza Operatorii logici Operatori SQL Negarea expresiilor Interogarea datelor cu conditii multiple Tipuri de date caracter si conditii SELECT - Sumar SQL*PLUS Exercitii Solutii CAPITOLUL 3 - Rularea cererilor standard cu variabile substituite Variabile in SQL*PLUS Comanda DEFINE Comanda ACCEPT Exercitii Solutii CAPITOLUL 4 - Functii Introducere Functii numerice si pe caracter Functii imbricate Functii numerice Exercitii Solutii CAPITOLUL 5 - Alte functii singulare aplicate liniilor unei baze de date Functiile de tip data calendaristica

Formate pentru date calendaristice Formatul de date RR ( Oracle 7 ) Functii care accepta orice tip de data la intrare Functii imbricate revizitate Exercitii Solutii CAPITOLUL 6 - Functii de grup Lista functiilor de grup Folosirea functiilor de grup Solutii CAPITOLUL 7 - Extragerea datelor din mai mult de o tabela Join Equi-join Non-Equi-join Reguli pentru join-ul tabelelor Exercitii Solutii CAPITOLUL 8 - Alte metode de join Join extern Join-ul unei tabele cu ea insasi Operatori de multimi Reguli pentru folosirea operatorilor de multimi Exercitii Solutii CAPITOLUL 9 - Subcereri Subcereri ascunse Reguli de scriere a cererilor Subcereri corelate Operatori Exercitii Solutii CAPITOLUL 10 - SQL*PLUS si REPORTING Comenzi SQL si SQL*PLUS Comenzi SET COLUMN - Optiuni Comenzile TTITLE si BTITLE Eliminarea valorilor duplicate si spargerea unui raport in sectiuni Fisierul de comenzi SQL*PLUS Exercitii Solutii CAPITOLUL 11 - Traversarea arborilor Traversarea arborilor

Parcurgerea arborilor Terminologie Chirurgia arborelui CAPITOLUL 12 - Crearea si definirea de structuri tabelare Structuri de date Oracle Limbajul de definire a datelor ( LDD ) Crearea unei tabele Exercitii Solutii CAPITOLUL 13 - Managementul de tabela si dictionarul de date Modificarea unei tabele Alte comenzi LDD - eliminarea unei tabele Dictionarul de date Oracle Tabelele si vederile dictionarului de date Folosirea lui SQL pentru a genera SQL Exercitii Solutii CAPITOLUL 14 - Limbajul de manipulare a datelor Inserarea de noi linii intr-o tabela Inserarea informatiilor de data si timp Copierea de linii din alta tabela Actualizarea liniilor Tranzactii Exercitii Solutii CAPITOLUL 15 - Concurenta si blocarea: o vedere generala Blocari CAPITOLUL 16 - Imagini ( VIEWS ) Ce este o imagine ? Utilizarea unei imagini cu operatii DML Modificarea datelor prin imagini Exercitii Solutii CAPITOLUL 17 - Utilizatorii si securitatea Privilegiile si functiile sistemului Roluri Comanda GRANT Privilegiile unui obiect Comanda REVOKE CAPITOLUL 18 - Obiecte in bazele de date: secventiale si indexate

Generatorul de numere de secventa Indexarea Reguli de folosire a unui index Indexare si imbinare Exercitii Solutii CAPITOLUL 19 - Introducere in PL/SQL Ce este PL/SQL Avantaje ale PL/SQL Structura PL/SQL Facilitati PL/SQL CAPITOLUL 20 - Reprezentarea datelor Sintaxa de baza a PL/SQL Domeniul obiectelor Reguli de vizibilitate Accesarea variabilelor NON PL/SQL Precedenta operatorilor Folosirea PL/SQL in SQL*PLUS Exercitii Solutii CAPITOLUL 21 - Interactiunea cu Oracle Comenzi SQL in PL/SQL Controlul tranzactiilor Introducere in tratarea exceptiilor Identificatori exceptie Functii pentru prinderea erorii Cum se propaga exceptiile? Exercitii Solutii CAPITOLUL 22 - Structuri de control Introducere in expresiile de control ale PL/SQL Instructiunea IF Cicluri si salturi in PL/SQL Instructiunea EXIT Utilizarea ciclurilor FOR pentru controlul iteratiilor Utilizarea ciclurilor WHILE pentru controlul iteratiilor Controlul ciclurilor imbricate Folosirea GOTO si a etichetelor Exercitii Solutii CAPITOLUL 23 - Tipuri de date compuse Inregistrari in PL/SQL Atributul %ROWTYPE Inregistrari definite de utilizatori

CAPITOLUL 24 - Viata secreta a cursoarelor Ce e un cursor? Controlul explicit al cursorului - pasii separati Atribute explicite pentru cursoare Cursoarele si inregistrarile Cursoare cu parametri Citeva cuvinte despre eficienta Exercitii Solutii CAPITOLUL 25 - Mai multe despre exceptii Cind sunt generate exceptiile? Exceptii definite de utilizator Declararea numelor pentru exceptii interne Mai multe despre tratarea exceptiilor Exercitii Solutii ANEXA ANEXA A - Pseudo-coloane SQL ANEXA B - Cuvinte rezervate SQL Cuvinte cheie Cuvinte rezervate PL/SQL ANEXA C - Vedere generala asupra Oracle versiunea 6 ANEXA D - Ordinea de comparatie ANEXA E - Crearea obiectelor unie baze da date DB2 Sintaxa declaratiei SELECT Crearea obiectelor bazei de date Vax Rdb/VMS ANEXA F - Editare ANEXA G - Exportarea continutului unei tabele intr-un fisier Crearea tabelelor ANEXA H - Facilitati ale PL/SQL, versiunea 2 Introducere Subprograme PL/SQL Declararea variabilelor si constantelor Tipuri scalare Date booleene

Cuvintul cheie DEFAULT Tipuri noi de date compuse Inregistrari definite de utilizatori Tabele PL/SQL ANEXA Autorii traducerii C A P I T O L U L 1 INTRODUCERE IN ORACLE Acest capitol introductiv scoate in evidenta facilitatile oferite de Oracle si de produsele sale punandu-le in contextul instrumentelor si tehnologiilor pe care le veti invata in acest curs.Acest capitol explica de asemenea principiile de baza ale bazelor de date relationale. CE ESTE ORACLE? Oracle consta dintr-un set complet de constructori de aplicatii si produse pentru utilizatori,cautand sa asigure solutii complete in tehnologia informatiei. Aplicatiile Oracle sunt portabile peste un numar mare de statii de lucru si sisteme de operare, de la calculatoare personale la procesoare paralele. Oracle este inzestrat cu un flexibil Sistem de Management al Bazelor de Date(DBMS)-Serverul Oracle-pentru stocarea si managementul informatiei utilizate de aplicatii. Ultimul server Oracle ,ORACLE 7,conduce o baza de date cu toate avantajele unei structuri relationale,avand in plus capacitatea de a stoca si executa obiecte de tip baza de date precum proceduri si mecanisme de siguranta. Despre Serverul ORACLE Serverul Oracle cuprinde un DBMS care controleaza: * Stocarea de date in sfera bazelor de date dedicate * Recuperarea de date pentru aplicatii utilizand tehnici de optimizare adecvate * Securitatea bazelor de date si a taskurilor permise pentru anumiti utilizatori * Consistenta si protectia datelor,incluzand arhivarea taskurilor si mecanisme de cautare * Comunicarea si integritatea informatiilor,cand bazele de date sunt distribuite intr-o retea. Aplicatiile Oracle trebuie rulate pe acelasi computer la fel ca si Serverul Oracle. Alternativ,aplicatiile si utilitarele utilizate de ele pot sa fie rulate pe un sistem local pentru utilizator (sistemul 'client'),in timp ce Oracle DBMS ruleaza pe un altul (sistemul 'server').

In acest mediu 'client-server',un numar mare de resurse de calcul pot fi rulate.De exemplu,o aplicatie 'Oracle Forms' poate rula pe un computer personal client,in timp ce accesarea datelor este condusa conventional de un Server Oracle pe un computer central.

PRODUSELE ORACLE

Instrum. CDE ========================== || ------------------ || || | Rapoarte Oracle| || || ------------------ || Oracle Card || ------------------ || || | Grafice Oracle | || Oracle Case || ------------------ || || ------------------ || SQL*Plus || | Forme Oracle | || || ------------------ || Pro*Oracle || ------------------ || || | Cart Oracl | || Text Retrieval || -----------------| || ========================== Alte produse ^ ^ | | | | | | --------------------------------------------------------------| | | | | ORACLE7 SERVER | | | | | --------------------------------------------------------------| | | | | -----------------------------| | | Hard Disk | | | ------------------------------

Optiuni Oracle Server In plus fata de 'Standard Oracle7' care dispune de o multime comprehensiva de facilitati asupra bazelor de date,mai sunt cateva facilitati suplimentare.

Acestea includ: Optiunea procedurala Asigura un "motor" PL/SQL versiunea 2 in interiorul serverului Oracle,care are bilitatea de a stoca proceduri b.d.,functii si mecanism de siguranta pentru utilizarea de catre aplicatii . Optiunea distribuita Suporta tranzactii care actualizeaza informatia din mai multe baze de date dintr-o retea de baze de date distribuita,utilizand executia in doua faze. Paralel Server Option Suporta sisteme cuplate liber. Oracle de incredere ("Trusted Oracle") Pune la dispozitie o multime de facilitati aditionale de inalta securitate.

Produsele Oracle Printre multele dezvoltari de aplicatii si produse pentru utilizatori accesibile in familia Oracle,exista o posibilitate comuna pentru a accesa baza de date. Daca direct sau indirect,aceasta este realizata prin Limbajul cu Structura de Interogare(SQL),va fi un subiect pe care noi il vom trata in acest curs. Produsele Oracle contin: Oracle TextRetrieval O tehnologie care adauga capabilitati de refacere completa a textului intr-o baza de date Oracle. Pro*Oracle O serie de precomilatoare care permit accesul bazelor de date Oracle la limbajele de programare C, Cobol,Fortran,PL/1,Pascal si Ada. Oracle Card O interfata utilizator pentru producerea aplicatiilor cu baze de date usor de folosit ce incorporeaza grafice si facilitati multimedia. Oracle CASE O familie de instrumente care ajuta la analiza , designul si generarea aplicatiilor Oracle. SQL*Plus Un instrument care permite o utilizare directa si interactiva a limbajului SQL pentru a accesa serverul Oracle,utilizand

comenzi ad-hoc sau prin rularea fisierelor de comanda.

Mediul de dezvoltare auxiliar(CDE) Instrumentul de dezvoltare a aplicatiilor principale formeaza un set inchis integrat numit 'Mediul de Dezvoltare Auxiliar'.Elementele de baza ale CDE includ limbajul procedural al lui Oracle,PL/SQL,facilitati grafice si comunicare intre produse.

Principalele instrumente CDE sunt: Oracle Forms V4 Permite ca aplicatii sofisticate bazate pe utilizarea ecranului sa fie construite repede si usor si permite utilizatorului sa ceara si sa manipuleze date intr-o structura cu forma convenabila ,la fel de bine ca afisarea imaginilor vizuale,si facilitati de accesare prevazute de alte produse CDE. Oracle Reports V2 Un puternic instrument de scriere a rapoartelor pentru constructia si executia rapoartelor sofisticate cu fonturi de text si imagini multiple. Oracle Graphics V2 Permite construirea si afisarea aplicatiilor vizuale, incluzand harti,grafice,imagini si desene. Oracle Book V1 Prevede abilitatea de a crea si vizualiza documente care includ hipertexte,sunete digitale si videoclipuri.

SQL,SQL*PLUS si PL/SQL

SQL,SQL*PLUS si PL/SQL sunt facilitatile Oracle care vor fi discutate in acest curs.Este important de inteles in fiecare caz diferentele lor,rolul si locul lor in familia Oracle. *SQL * este limbajul utilizat pentru a accesa o baza de date relationala, inclusiv Oracle. * poate fi utilizat de fiecare instrument Oracle,cand accesul la baza de date este necesar.

*PL/SQL * este limbajul Procedural al lui Oracle pentru scrierea aplicatiilor si pentru manipularea datelor in afara bazei de date. * poate include un subset al comenzilor SQL,cand accesul la baza de date este cerut * este accesibil in fiecare din produsele CDE.De asemenea in insusi serverul Oracle(daca optiunea procedurala este instalata). *SQL*Plus * este un produs Oracle in care limbajele SQL si PL/SQL pot fi utilizate. * de asemenea are propriul limbaj de comanda pentru controlul comportarii produsului si pentru formatarea rezultatelor interogarilor SQL. In concluzie,SQL si PL/SQL sunt limbaje care sunt utilizate intr-un numar de produse Oracle .SQL*PluS este unul din produsele pe care acestea se afla. Noi vom discuta acum principiile de baza ale bazelor de date relationale, pentru care Oracle este un exemplu.

ABORDAREA RELATIONALA -------------------------------------------------| S# SNAME STATUS CITY | |------------------------------------------------| | S1 SMITH 20 LONDON | | S2 JONES 10 PARIS | | S3 BLAKE 30 PARIS | -------------------------------------------------PARTI --------------------------------------------------------| P# PNAME COLOUR WEIGHT CITY | |-------------------------------------------------------| | P1 NUT RED 12 LONDON | | P2 BOLT GREEN 17 PARIS | | P3 SCREW BLUE 17 ROME | | P4 SCREW RED 14 LONDON | --------------------------------------------------------^ coloana

ORDINI ------------------| S# P# QTY | |-----------------| | S1 P1 300 | | S1 P2 200 | rand--------->| S1 P3 400 | | S2 P1 300 |

| S2 P2 400 --|----camp | S3 P3 200 | -------------------

Abordarea relationala Principiile modelului relational au fost pentru prima data expuse de Dr. E. F.Codd,care in iunie 1970 a publicat un articol numit 'Un model relational de date pentru marile banci de date'.In acest articol Dr. Codd a propus modelul 'relational' pentru sistemele de baze de date. Baza de date relationala este perceputa de utilizatorii sai ca o colectie de tabele bidimensionale care sunt usor de inteles.Sunt doar patru concepte de inteles: * * * * tabele coloane randuri campuri

Modelul relational imita procesele unei ramuri a algebrei cunoscuts sub numele de 'Algebra relationala'. Aceste procese implica: * o colectie de obiecte cunoscute sub numele de RELATII * o multime de operatori ce actioneza asupra relatiilor pentru a produce noi relatii. O Relatie poate fi inteleasa ca o Tabela.Modificarea datelor este realizata prin operatiile relationale aplicate asupre tabelelor. RESTRICTIE ------------------------------------------| | | | | | |#########################################| | | | | | | |#########################################| | | |#########################################| | | | | -------------------------------------------

PROIECTIE

------------------------------------------| ### ######## | | ### ######## | | ### ######## | | ### ######## |

| ### ######## | | ### ######## | | ### ######## | | ### ######## | | ### ######## | | ### ######## | | ### ######## | | ### ######## | -------------------------------------------

Operatori relationali Operatorii relationali sunt definiti mai jos: Relatia Descrierea Restrictia este o operatie care preia si afiseaza datele din relatie.Este posibil sa se afiseze toate randurile sau doar randurile care indeplinesc o conditie sau mai multe conditii .Aceasta este de multe ori numita 'submultime orizontala' Proiectia este operatia care afiseaza anumite coloane din relatie si de aceea este numita 'submultime verticala'. Produs este rezultatul obtinut cand randurile a doua multimi de date sunt concatenate conform conditiilor specificate. Join este rezultatul obtinut cand randurile a doua multimi de date sunt concatenate conform conditiilor specificate. Reuniunea afiseaza toate randurile care apar in una ,in cealalta sau in ambele din cele doua relatii. Intersectia afiseaza toate randurile care apar in ambele din cele doua relatii. Diferenta afiseaza randurile care apar numai intr-o singura relatie (SQL utilizeaza operatorul minus ).

PRODUS

-------------------------------------| SMITH | | CLERK | | SMITH | CLERK | | JONES | produs | MANAGER|------>| SMITH | MANAGER | | ADAMS | ---------| | | ----------| JONES | CLERK | | JONES | MANAGER | | | | | ADAMS | CLERK | | ADAMS | MANAGER | ------------------JOIN --------------- ------------------| CLARK | 10 | | 10 | ACCOUNTING | | MILLER | 10 | J | 20 | RESEARCH | | SMITH | 20 |-->| 30 | SALEH | | TURNER | 30 | | 40 | OPERATIONHS| --------------- ------------------------------------------------| CLARK | 10 | 10 | ACCOUNTING| | MILLER| 10 | 10 | ACCOUNTING| | SMITH | 20 | 20 | RESEARCH | | TURNER| 30 | 30 | SALES | -------------------------------

Proprietati ale bazelor de date relationale * O baza de date relationala apare ca o colectie de relatii(tabele) catre utilizator. * Formatul coloanei/randului este familiar si usor pentru vizualizarea datelor . * Exista o multime de operatori pentru partitionarea si combinarea relatiilor(selectia,proiectia,produsul,joinul,uniunea, intersectia, diferenta). * Nu sunt pointeri expliciti;conexiunile sunt facute numai pe baza datelor. * Limbajul utilizat pentru interogarea bazei de date este non-procedural si similar limbii engleze. * Utilizatorul nu specifica calea de acces si nu are nevoie sa stie cum este informatia aranjata fizic. * Comenzile pentru refacerea datelor si acelea pentru realizarea schimbarilor in baza de date sunt incluse intr-un singur limbaj SQL. * Exista o independenta totala a datelor. Proprietatile relatiilor tabelare O singura tabela are urmatoarele proprietati: * * * * * Nu exista randuri duplicate Nu exista nume de coloana duplicate Ordinea randurilor este neimportanta Ordinea coloanalor este neimportanta Valorile sunt atomice(nedecompozabile). C A P I T O L U L 2

INTRODUCERE IN SQL

Acest capitol realizeaza o introducere in limbajul de interogare utilizat pentru a accesa o baza de date Oracle. Multimea comenzilor SQL sunt conturate ca blocuri de interogare de baza.In particular, noi

discutam declaratiile SQL folosite la: * executia calculelor * specificarea alternativa a capetelor de coloana * concatenarea coloanelor * sortarea rindurilor * introducerea criteriilor de cautare. Privire de ansamblu asupra SQL Un sistem de management al bazei de date necesita un limbaj de interogare pentru a permite utilizatorului sa acceseze datele.SQL (limbaj de interogare structurata) este limbajul utilizat de majoritatea sistemelor de baza de date relational. Limbajul SQL a fost dezvoltat intr-un prototip de sistem de management a bazelor de date relationale - System R -de IBM la mijlocul anilor 1970.In 1979, Corporatia Oracle introduce prima implementare a SQL in varianta comerciala. Trasaturi caracteristice SQL * SQL este prezentat in limba engleza. Foloseste cuvintele select, insert,delete ca parti ale setului de comenzi. * SQL este un limbaj neprocedural :specifica ce informatii doresti,nu cum sa le obtii.Cu alte cuvinte SQL nu iti cere sa specifici metoda de acces la date.Toate cererile SQL folosesc optimizarea cererilor- o parte a RDBMS- pentru a determina rapid remedierea datelor specifi- cate.Aceste trasaturi usureaza obtinerea rezultatelor dorite. * Procesarea SQL asupra inregistrarilor nu se poate face decit asupra unei singure inregistrari la un moment dat.Cea mai comuna forma a unui set de inregistrari este un tabel. * SQL poate fi folosit de un sir de utilizatori incluzand DBA, programatori de aplicatii ,personal de management si multe alte tipuri de utilizatori. * SQL pune la dispozitie comenzi pentru o varietate de tascuri incluzand: + date interogate + inserarea,extragerea si stergerea rindurilor intr-un tabel. + crearea,modificarea si stergerea obiectelor de tip baza de date + controlul accesului la baza de date si la obiectele de tip baza de date. + garantarea consistentei bazei de date La inceput sistemele de management a bazei de date a utilizat un limbaj separat pentru fiecare categorie in parte.SQL le-a unificat pe toate acestea intr-un singur limbaj. SQL a devenit un limbaj standard industrial pentru bazele de date relatio- nale . Institutul National American de Standarde(ANSI) a adoptat SQL ca limbaj standard pentru RDBMS in anul 1986.Organizatia Internationala de Standarde (ISO) a adoptat deasemenea SQL ca limbaj standard pentru RDBMS.Toate RDBMS-urile suporta unele forme de SQL orm tSQL vinzatorii de RDBMS intentioneaza sa se alinieze la standardele ANSI.

Setul de comenzi SQL

Comanda + Descriere: SELECT este comanda cea mai utilizata ; este folosita pentru obtinerea datelor din bazele de date INSERT aceste trei comenzi sunt utilizate pentru a introduce noi UPDATE rinduri,pentru a schimba rindurile existente si stergerea DELETE rindurilor nedorite din tabelele bazelor de date respective. (Ele sunt cunoscute in ansamblu ca DML sau comenzi ale limbajului de manipulare a datelor.) CREATE aceste trei comenzi sunt utilizate dinamic pentru a crea , ALTER utiliza si sterge orice structura de date,de exemplu,tabele, DROP expuneri ,indecsi.(Ele sunt cunoscute sub numele colectiv DDL sau comenzi ale limbajelor de definire a datelor). GRANT aceste doua comenzi sunt utilizate pentru a da sau a lua REVOKE drepturile de acces pentru bazele de date si structurile din Oracle. N.B. Acestea sunt o parte a comenzilor SQL. Pentru o lista mai completa de comenzi se recomanda - Manualul de Referinta a Limbajului SQL- . Scrierea comenzilor SQL Cand scriem comenzi SQL ,este important sa ne reamintim cateva reguli simple pentru construirea unor declaratii valide care sunt si usor de citit si de editat: * Comenzile SQL pot fi pe una sau mai multe linii. * Clauzele sunt uzual plasate pe linii separate. * Tabelarea poate fi folosita. * Cuvintele de comanda nu pot fi separate pe mai multe linii. * Comenzile SQL nu sunt 'case sensitive'. * O comanda SQL este introdusa la promptul SQL si liniile subsecventelor sunt numarate. * O singura declaratie poate fi considerata curenta cat timp ea este in buffer si poate fi rulata intr-un numar de moduri : + plasand un punct si virgula(;) la sfarsitul ultimei clauze.

+ plasand un punct si virgula /slash la ultima linie in buffer. + plasand un slash(/) la promptul SQL. + scriind un R[UN] (comanda SQL) la promptul SQL. Fiecare din urmatoarele declaratii sunt valide: SELECT * FROM EMP; SELECT * FROM EMP ; SELECT * FROM EMP; In acest manual comenzile SQL sunt sparte in clauze pentru claritate.

Blocul de interogare de baza

Declaratia SELECT regaseste informatia din baza de date implementand toti operatorii din algebra relationala . In cele mai simple forme trebuie sa contina: 1. O clauza SELECT ,care listeaza coloanele pentru afisare astfel incat este esentiala o Proiectie. 2. O clauza FROM care specifica tabela implicata. Pentru a lista toate numerele departamentelor,numele angajatilor si numarul managerilor in tabela EMP introduceti urmatoarele: SELECT FROM DEPTNO -----20 30 30 20 30 30 10 20 10 30 20 30 20 10 DEPTNO,ENAME,MGR EMP; ENAME MGR ---------- ----SMITH 7902 ALLEN 7698 WARD 7698 JONES 7839 MARTIN 7698 BLAKE 7839 CLARK 7839 SCOTT 7566 KING TURNER 7698 ADAMS 7788 JAMES 7698 FORD 7566 MILLER 7782

De remarcat ca numele coloanelor sunt separate prin spatiu. Este posibil sa selectam toate coloanele din tabela prin specificarea unui asterix ('*') dupa cuvantul SELECT . SELECT FROM EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 ENAME ------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD * EMP; JOB MGR ------- ---CLERK 7902 SALESMAN 7698 SALESMAN 7698 MANAGER 7839 SALESMAN 7698 MANAGER 7839 MANAGER 7839 ANALYST 7566 PRESIDENT SALESMAN 7698 CLERK 7788 CLERK 7698 ANALYST 7566 HIREDATE --------13-JUL-83 15-AUG-83 26-MAR-84 31-OCT-83 05-DEC-83 11-JUN-84 14-MAY-84 05-MAR-84 09-JUL-84 04-JUN-84 04-JUN-84 23-JUL-84 05-DEC-83 SAL COMM DEPTNO --------- ------- -----800.00 20 1,600.00 300.00 30 1,250.00 500.00 30 2,975.00 20 1,250.00 1.400.00 30 2,850.00 30 2,450.00 10 3,000.00 20 5,000.00 10 1,500.00 .00 30 1,100.00 20 950.00 30 3,000.00 20

Alte elemente in clauza SELECT Este posibil sa se includa si alte elemente in clauza SELECT. * Expresii aritmetice * Alias-uri de coloane * Coloane concatenate * Literali Toate aceste optiuni ajuta utilizatorul sa ceara date si sa le manipuleze in functie de scopurile interogarii ; de exemplu,executia calculelor,legarea coloanelor impreuna,sau afisarea sirurilor de litere din text. Expresii aritmetice O expresie este o combinatie de una sau mai multe valori,operatori si functii care sa evalueaza la o valoare. Expresiile aritmetice pot contine nume de coloane ,valori numerice constante si operatori aritmetici: Operatori --------+ * / SELECT FROM Descriere --------adunare scadere inmultire impartire ENAME, SAL*12, COMM EMP;

Daca expresia aritmetica contine mai mult decat un operator, prioritatile sunt *,/,la inceput,apoi +,- (de la stanga la dreapta pentru operatorii de aceeasi prioritate). In urmatorul exemplu,inmultirea (250*12) este evaluata prima;apoi valoarea salariului este adunata la rezultatul multiplicarii(3000). Astfel pentru randul lui SMITH avem :800+3000=3800. SELECT ENAME,SAL + 250 * 12 FROM EMP;

Parantezele pot fi utilizate pntru specificarea ordinii de executie a operatorilor ,daca,de exemplu ,adunarea e dorita inainte de inmultire: SELECT ENAME,(SAL + 250) * 12 FROM EMP; Aliasuri de coloana Cand se afiseaza rezultatul unei interogari,SQL*Plus in mod normal utilizeaza numele coloanelor selectate ca titlu.In multe cazuri acest nume poate fi criptic sau fara inteles. Puteti schimba un titlu de coloana utilizand un 'ALIAS'. Un alias de coloana da unei coloane un nume de titlu alternativ la iesire. Specificati aliasul dupa coloana in lista selectata.Prin default, titlurile alias vor fi fortate la litere mari si nu pot contine blankuri,numai daca aliasul este inclus intre ghilimele(" "). Pentru a afisa titlul de coloana ANNSAL pentru salariul anual insemnand SAL*12,utilizati un alias de coloana: SELECT FROM ENAME,SAL*12 ANNSAL,COMM EMP;

Odata definit ,un alias poate fi utilizat de comenzile SQL care sunt tratate in capitolele 10 si 11. Nota: Intr-o declaratie SQL ,un alis de coloana poate fi utilizat numai in clauza SELECT. Operatorul de concatenare Operatorul de concatenare (||) permite coloanelor sa fie legate cu alte coloane,expresiilor aritmetice sau valorilor constante sa creeze o expresie de caractere.

Coloanele din cealalta parte a operatorului sunt combinate pentru a obtine o singura coloana. Pentru a combina EMPNO si ENAME si sa se dea aliasul EMPLOYEE expresiei, introduceti: SELECT EMPNO||ENAME FROM EMP; EMPLOYEE

EMPLOYEE ------------------------------------------------------7369SMITH 7499ALLEN 7521WARD 7566JONES 7654MARTIN 7698BLAKE 7782CLARK 7788SCOTT 7839KING 7844TURNER 7876ADAMS 7900JAMES 7902FORD 7934MILLER Literali Un literal este orice caracter ,expresie ,numar inclus in lista lui SELECT care nu este un nume de coloana sau un alias de coloana. Un literal in lista lui SELECT este reprezentat de fiecare rand returnat la iesire.Sirurile de literali dintr-un text cu un format oarecare pot fi incluse in rezultatul interogarii si sunt tratate ca o coloana lista selectata. Literalii de tip data calendaristica si caracter pot fi inchisi intre ghilimele simple(');literalii de tip numar nu au nevoie de ghilimele simple('). Urmatoarea declaratie contine literali selectati prin concatenare si printr-un alias de coloana: SELECT FROM EMPNO||'-'||ENAME EMPLOYEE, 'WORKS IN DEPARTMENT', DEPTNO EMP; 'WORKS IN DEPARTMENT' -------------------WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT WORKS IN DEPARTMENT DEPTNO -----20 30 30 20 30 30

EMPLOYEE -----------------7369-SMITH 7499-ALLEN 7521-WARD 7566-JONES 7654-MARTIN 7698-BLAKE

7782-CLARK 7788-SCOTT 7839-KING 7844-TURNER 7876-ADAMS 7900-JAMES 7902-FORD 7934-MILLER Tratarea valorilor nule

WORKS WORKS WORKS WORKS WORKS WORKS WORKS WORKS

IN IN IN IN IN IN IN IN

DEPARTMENT DEPARTMENT DEPARTMENT DEPARTMENT DEPARTMENT DEPARTMENT DEPARTMENT DEPARTMENT

10 20 10 30 20 30 20 10

Daca unui rand ii lipseste o valoare pentru o anumita coloana ,despre acea valoare se spune ca este nula. O valoare nula este o valoare care este sau incorecta,sau necunoscuta,sau inaplicabila.O valoare nula nu este la fel ca 'zero'.Zero este un numar.Valoarea nula ocupa un octet in reprezentarea interna. Valoarea nula este tratata corect de catre SQL. Daca orice valoare de coloana intr-o expresie este nula atunci rezultatul este nul.In urmatoarea declaratie numai Salesman are un rezultat al salariului: SELECT FROM ENAME,SAL*12 + COMM ANNUAL_SAL EMP;

ENAME ANNUAL_SAL --------- ---------SMITH ALLEN 19500 WARD 15500 JONES MARTIN 16400 BLAKE CLARK SCOTT KING TURNER 18000 ADAMS JAMES FORD MILLER

Daca dorim sa obtinem un rezultat pentru toti angajatii,este necesar sa convertim valoarea nula la un numar . Noi folosim functia NVL pentru a converti o valoare nula la o valoare nenula. Folositi functia NVL pentru a converti valoarea nula de la declaratia precedenta la 0. SELECT FROM ENAME, SAL*12+NVL(COMM,0) ANNUAL_SAL EMP;

ENAME ANNUAL_SAL --------- ---------SMITH 9600 ALLEN 19500 WARD 15500 JONES 35700 MARTIN 16400 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 15600

NVL asteapta doua argumente: 1. o expresie 2. o valoare nenula De notat ca puteti folosi functia NVL pentru a converti un numar nul , data calendaristica sau sir de caractere la un alt numar , data calendaristica sau sir de caractere de acceasi lungime si de acelasi tip de date asteptate. NVL(DATECOLUMN,'01-JAN-88') NVL(NUMBERCOLUMN,9) NVL(CHARCOLUMN,'STRING') Prevenirea selectiei rindurilor duplicate Daca nu se indica altfel, SQL*Plus afiseaza rezultatul unei interogari fara eliminarea intrarilor duplicate . Pentru a lista toate numerele de departament din tabela EMP, introduceti: SELECT FROM DEPTNO -----20 30 30 20 30 30 10 20 DEPTNO EMP;

10 30 20 30 20 10 Clauza DISTINCT Pentru eliminarea valorilor duplicate din rezultat, includeti restrictia DISTINCT in comanda SELECT. Pentru a elimina valorile duplicate afisate in exemplul urmator introduceti: SELECT FROM DEPTNO -----10 20 30 DISTINCT EMP; DEPTNO

Coloane multiple pot fi specificate dupa restrictia DISTINCT si restrictia DISTINCt afecteaza toate coloanele selectate. Pentru a afisa valorile distincte ale lui DEPTNO si JOB,introduceti: SELECT FROM DEPTNO -----10 10 10 20 20 20 30 30 30 DISTINCT DEPTNO,JOB EMP; JOB --------CLERK MANAGER PRESIDENT ANALYST CLERK MANAGER CLERK MANAGER SALESMAN

Aceasta afiseaza o lista a tuturor combinatiilor diferite de ocupatie si numere de departamente. De notat ca restrictia DISTINCT poate sa fie referita numai o singura data si trebuie sa urmeze imediat dupa cuvantul de comanda SELECT. Clauza ORDER BY In mod normal ordinea rindurilor intoarse in rezultatul unei cereri

este nedefinita .Clauza ORDER BY poate fi utilizata pentru a sorta rindurile. Daca o folosim, clauza ORDEY BY trebuie sa fie intotdeauna ultima in declaratia SELECT. Pentru a sorta dupa ENAME, introduceti: SELECT ENAME,JOB,SAL*12,DEPTNO FROM EMP ORDER BY ENAME; ENAME --------ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD JOB SAL*12 DEPTNO ------- -------- -----CLERK 13200 20 SALESMAN 19200 30 MANAGER 34200 30 MANAGER 29400 10 ANALYST 36000 20 CLERK 11400 30 MANAGER 35700 20 PRESIDENT 60000 10 SALESMAN 15000 30 CLERK 15600 10 ANALYST 36000 20 CLERK 9600 20 SALESMAN 18000 30 SALESMAN 15000 30

Ordonarea de default a datelor Ordinea sortarii de default este ascendenta: * Valorile numerice cele mai mici primele * Valorile de tip date calendaristice cele mai mici primele. * Valorile de tip caracter in ordinea alfabetica. Inversarea ordinii de default Pentru a inversa aceasta ordine cuvintul de comanda DESC este specificat dupa numele coloanei in clauza ORDER BY. Pentru a inversa ordinea coloanei HIREDATE, deci datele cele mai tirzii sa fie afisate primele,introduceti: SELECT ENAME,JOB,HIREDATE FROM EMP ORDER BY HIREDATE DESC; ENAME --------JAMES KING BLAKE TURNER ADAMS CLARK WARD JOB --------CLERK PRESIDENT MANAGER SALESMAN CLERK MANAGER SALESMAN HIREDATE --------23-JUL-84 09-JUL-84 11-JUN-84 04-JUN-84 04-JUN-84 14-MAY-84 26-MAR-84

SCOTT MARTIN FORD MILLER JONES ALLEN SMITH

ANALYST SALESMAN ANALYST CLERK MANAGER SALESMAN CLERK

05-MAR-84 05-DEC-83 05-DEC-83 21-NOV-83 31-OCT-83 15-AUG-83 13-JUN-83

Ordonarea dupa mai multe coloane Este posibil sa se ordoneze dupa mai multe coloane.Limita este numarul de coloane din tabela.In clauza ORDER BY se specifica coloanele pentru ordonat separate prin virgula.Daca una sau toate coloanele trebuie sa fie inversate specificati DESC dupa fiecare coloana. Pentru a ordona dupa doua coloane si afisa in ordinea inversa a salariului, introduceti: SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO,SAL DESC; DEPTNO -----10 10 10 20 20 20 20 20 30 30 30 30 30 30 JOB -----------PRESIDENT MANAGER CLERK ANALYST ANALYST MANAGER CLERK CLERK MANAGER SALESMAN SALESMAN SALESMAN SALESMAN CLERK ENAME ------KING CLARK MILLER SCOTT FORD JONES ADAMS SMITH BLAKE ALLEN TURNER WARD MARTIN JAMES

Pentru a ordona dupa o coloana nu este necesar sa o avem SELECT-ata. ORDER BY si valorile nule In Oracle7,valorile nule sunt afisate ultimele pentru secventele ascendente si sint raportate primele cind rindurile sunt sortate in ordine descendenta. Atentie: Clauza ORDER BY este utilizata intr-o interogare cind se doreste sa se afiseze rindurile intr-o ordine specifica .Fara clauza ORDER BY randurile sunt returnate intr-o ordine convenita de ORACLE si va trebui sa ne bazam pe el- ordinea determinata fiind consistenta de la cerere la cerere.De notat ca ordinea de afisare a rindurilor nu

influenteaza ordinea interna a rindurilor asa cum sunt stocate in tabela. Clauza WHERE Clauza WHERE corespunde operatorului 'Restrictie' din algebra relationala. Contine o conditie pe care rindurile trebuie sa o indeplineasca in ordinea afisarii lor. Clauza WHERE ,daca este folosita , trebuie sa urmeze clauzei FROM : SELECT FROM WHERE coloane tabela anumite conditii sunt intilnite

Clauza WHERE poate compara valori in coloana ,valori literale,expresii aritmetice sau functii. Clauza 1. Un 2. Un 3. Un WHERE asteapta trei elemente: nume de coloana operator de comparatie nume de coloana, constanta sau lista de valori.

Operatorii de comparatie sunt utilizati in clauza WHERE si pot fi impartiti in doua categorii: logici si SQL. Operatorii logici

Acesti operatori verifica urmatoarele conditii: Operator -------= > >= < < Semnificatie -----------egal cu mai mare decit mai mare sau egal mai mic decit mai mic sau egal

Sirurile de caractere si datele calendaristice in clauza WHERE Coloanele din ORACLE pot avea urmatoarele tipuri:caracter,numar sau data calendaristica. Sirurile de caractere si datele calendaristice trebuie sa fie inchise in ghilimele simple('). trebuie sa se supra- puna cu valoarea coloanei modificate de o functie. Utilizati "Functii pe capitolul 4. din clauza WHERE Sirurile de caractere daca nu, trebuie Caractere" din

Pentru a afisa numele,numerele,ocupatia si departamentele tuturor

functionarilor, introduceti: SELECT FROM WHERE ENAME --------SMITH ADAMS JAMES MILLER ENAME, EMPNO, JOB, DEPTNO EMP JOB = 'CLERK'; EMPNO ----7369 7876 7900 7934 JOB DEPTNO ------- -----CLERK 20 CLERK 20 CLERK 30 CLERK 10

Pentru a gasi toate numele de departamente cu numerele de departament mai mare ca 20 ,introduceti: SELECT FROM WHERE DNAME, DEPTNO DEPT DEPTNO >20;

DNAME DEPTNO ---------- -----SALES 30 OPERATIONS 40 Compararea unei coloane cu alta in cadrul aceluiasi rand Puteti compara o coloana cu o alta coloana in acelasi rand,la fel ca si cu o valoare constanta. De exemplu ,presupunem ca dorim sa obtinem acei angajati al caror comision est mai mare decat salariul lor: SELECT FROM WHERE ENAME -----MARTIN ENAME, SAL, COMM EMP COMM> SAL; SAL -------1,250.00 COMM -------1,400.00

Operatori SQL Sint patru operatori SQL care opereaza pe toate tipuril de date: Operatori SQL Operator -------BETWEEN..AND... Semnificatie -----------intre doua valori(inclusiv)

IN(list) LIKE IS NULL Operatorul BETWEEN

compara cu o lista de valori compara cu un model de tip caracter est o valoare nula

Realizeaza teste pentru valori intre,si inclusiv,o valoare minima si o valoare maxima. Presupunind ca dorim sa vedem angajatii ai caror salariu este intre 1000 si 2000: SELECT FROM WHERE ENAME,SAL EMP SAL BETWEEN 1000 AND 2000;

ENAME SAL ------- ---------ALLEN 1,600.00 WARD 1,250.00 MARTIN 1,250.00 TURNER 1,500.00 ADAMS 1,100.00 MILLER 1,300.00

De notat ca valorile specificate sunt inclusive si ca limita minima trebuie specificata prima. Operatorul IN Testeaza valorile dintr-o lista specificata. Presupunem ca dorim sa gasim angajatii care au unul din cele trei numere de marca(MGR): SELECT FROM WHERE EMPNO ----7369 7788 7876 7902 ENAME ----SMITH SCOTT ADAMS FORD EMPNO, ENAME, SAL, MGR EMP MGR IN (7902,7566,7788); SAL --------800.00 3,000.00 1,100.00 3,000.00 MGR ----7902 7566 7788 7566

Daca, caracterele sau datele calendaristice sunt utilizate,ele trebuie introduse intre ghilimele(' '). Operatorul LIKE Uneori nu se cunosc valorile exacte pe care le cautam.Utilizand operatorul LIKE este posibil sa selectam randurile care se potrivesc cu un model specificat de caractere.Operatia de petter-matching a

caracterelor poate fi asemanata cu o cautare 'wild-card'.Doi simboli se pot utiliza la construirea sirului de cautare. Simbol -----% Reprezentare -----------orice secventa de zero sau mai multe caractere un singur caracter oarecare

Pentru a lista toti angajatii al caror nume incepe cu un S,introduceti: SELECT FROM WHERE ENAME -------SMITH SCOTT Caracterul '_' poate fi utilizat pentru cautarea unui anumit numar de carac- tere. De exemplu pentru a lista toti angajatii care au un nume exact de patru caractere lungime: SELECT FROM WHERE ENAME ----WARD KING FORD Semnele '%' si '_'pot fi utilizate in orice combinatie de caractere. Operatorul IS NULL Operatorul IS NULL face teste specifice pentru valorile care sunt NULL. SELECT FROM WHERE ENAME MGR ------ ---KING ENAME,MGR EMP MGR IS NULL; ENAME EMP ENAME LIKE '____'; ENAME EMP ENAME LIKE 'S%';

Negarea expresiilor

Urmatorii operatori fac teste de negatie:

Operator --------!= ^= < NOT NUMECOL= NOT NUMECOL> Operatori SQL Operator -------NOT BETWEEN NOT IN NOT LIKE IS NOT NULL

Descriere ---------diferit de(VAX,UNIX,PC) diferit de(IBM) diferit de(toate o/s) diferit de mai mic sau egal

Descriere --------nu se afla intre doua valori date nu se afla intr-o lista data de valori diferit de sirul nu este o valoare nula

Pentru a gasi angajatii ai caror salariu nu este intr-un interval,introdu- ceti: SELECT FROM WHERE ENAME --------SMITH JONES BLAKE CLARK SCOTT KING JAMES FORD ENAME,SAL EMP SAL NOT BETWEEN 1000 AND 2000; SAL -------800.00 2,975.00 2,850.00 2,450.00 3,000.00 5,000.00 950.00 3,000.00

Pentru a afla acei angajati a caror meserie nu incepe cu M,introduceti: SELECT FROM WHERE ENAME -------SMITH ALLEN WARD MARTIN SCOTT KING TURNER ADAMS JAMES FORD MILLER ENAME,JOB EMP JOB NOT LIKE 'M%'; JOB -------CLERK SALESMAN SALESMAN SALESMAN ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

Pentru a afla toti angajatii care au un manager(MGR),introduceti: SELECT ENAME,MGR

FROM WHERE ENAME ------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT TURNER ADAMS JAMES FORD MILLER Nota:

EMP MGR IS NOT NULL; MGR ----7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782

Daca o valoare NULL este utilizata intr-o comparatie ,atunci operatorul de comparatie trebuie sa fie IS sau IS NOT NULL. Daca acesti operatori nu sunt uti lizati si valoarea NULL este comparata,atunci rezultatul este intotdeauna FALSE De exemplu, COMM!=NULL este intotdeauna falsa.Rezultatul este fals deoarece o valoare NULL poate sa nu fie egala sau diferita cu orice alta valoare alta decat NULL. De notat ca o astfel de eroare nu este semnalata,rezultatul fiind intotdea- una fals. Interogarea datelor cu conditii multiple Operatorii AND sau OR pot fi utilizati pentru a compune expresii logice. Predicatul AND este adevarat numai daca ambele conditii sunt 'adevarate'; predicatul OR este adevarat daca cel putin una din conditii este 'adevarata'. In urmatoarele doua exemple,conditiile sunt aceleasi,dar predicatele difera Priviti cum rezultatul este dramatic modificat. Pentru a gasi toti functionarii care castiga intre 1000 si 2000,introduceti: SELECT FROM WHERE AND EMPNO ---7876 7934 EMPNO,ENAME,JOB,SAL EMP SAL BETWEEN 1000 AND 2000 JOB = 'CLERK'; JOB -------CLERK CLERK SAL -------1,100.00 1,300.00

ENAME ------ADAMS MILLER

Pentru a afla toti angajatii care sunt si functionari si/sau functionari care castiga intre 1000 si 2000,introduceti:

SELECT FROM WHERE OR EMPNO ----7369 7499 7521 7654 7844 7876 7900 7934 ENAME -------SMITH ALLEN WARD MARTIN TURNER ADAMS JAMES MILLER

EMPNO,ENAME,JOB,SAL EMP SAL BETWEEN 1000 AND 2000 JOB = 'CLERK'; JOB ------CLERK SALESMAN SALESMAN SALESMAN SALESMAN CLERK CLERK CLERK SAL -------800.00 1,600.00 1,250.00 1,250.00 1,500.00 1,100.00 950.00 1,300.00

Puteti combina AND sau OR in acceasi expresie logica. Cand AND sau OR apar in aceeasi clauza WHERE, toti operatorii AND sunt evaluati mai intai si apoi toti operatorii OR. Vom spune ca operatorii AND au o precedenta mai mare decat OR. Deoarece AND are o precedenta mai mare decat OR urmatoarea declaratie SQL intoarce toti managerii cu salarii peste 1500$ si toti vanzatorii. SELECT FROM WHERE AND OR EMPNO ----7499 7521 7566 7654 7698 7782 7844 EMPNO,ENAME,JOB,SAL,DEPTNO EMP SAL> 1500 JOB = 'MANAGER' JOB = 'SALESMAN'; JOB -------SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER SALESMAN SAL DEPTNO -------- -----1,600.00 30 1,250.00 30 2,975.00 20 1,250.00 30 2,850.00 30 2,450.00 10 1,500.00 30

ENAME ------ALLEN WARD JONES MARTIN BLAKE CLARK TURNER

Daca doriti sa selectati toti managerii si vanzatorii cu salarii peste 1500$ puteti introduce: SELECT FROM WHERE AND OR EMPNO ----7499 7566 7698 7782 EMPNO,ENAME,JOB,SAL,DEPTNO EMP SAL >1500 (JOB = 'MANAGER' JOB = 'SALESMAN'); ENAME -----ALLEN JONES BLAKE CLARK JOB --------SALESMAN MANAGER MANAGER MANAGER SAL DEPTNO -------- -----1,600.00 30 2,975.00 20 2,850.00 30 2,450.00 10

Parantezele specifica ordinea in care operatorii vor fi evaluati. In al doilea exemplu operatorul OR este evaluat inaintea operatorului AND. TIPURI DE DATE CARACTER SI CONDITII

WHERE ENAME = 'SCOTT'

ENAME (VARCHAR2) ___________ | | | SCOTT V V| | ___| | SCOTT|____ | | | MILLER | | | ~~~~ ~~ ~~~

Oracle nu face umplerea cu blancuri la compararea cu coloanele VARCHAR2

WHERE ENAME = 'SCOTT'

ENAME (CHAR) -----------| | | SCOTT V V| | ....| | SCOTT|V V| | |. | | MILLER| V| ~~~~ ~~~~ ~~

Oracle face umplerea cu blancuri la compararea cu coloanele CHAR. Tipurile de date caracter si conditii Tipurile de baza ale datelor stocate intr-o tabela oracle sunt:caracter, valoare numerica sau data calendaristica.Vom discuta toate variantele in detaliu mai tarziu.De cate ori rezultatele unei conditii implica date de tip caracter, acestea pot varia in functie de tipul coloanei;ORACLE inzestreaza coloanela cu tipul CHAR pentru valori de lungime fixa si cu tipul VARCHAR2 pentru valori de lungime variabila. Pentru coloanele cu tipul VARCHAR2 ,Oracle nu umple sirul de comparare si de aceea va face o potrivire exacta.In primul exemplu,doar un singur rand este intors pentru conditia: WHERE ENAME = 'SCOTT'

cand un alt rand stocat in coloana ENAMe are mai multe caractere decat sirul de comparat. Pentru coloanele cu tipul CHAR ,oricum,Oracl face umplere cand valorile coloanelor sunt initial stocate,facandu-le pe toate de aceeasi lungime. Aceeasi conditie va intoarce ambele randuri pentru SCOTT ,indiferent de cate spatii de sfarsit au fost adaugate cand valorile au fost stocate in tabela. Oracle umple cu blancuri sirul de comparat in cel deal doilea caz si de aceea spatiile stocate sunt nesemnificative. Precedenta operatorilor Toti operatorii sunt aranjati intr-o ierarhie ceea ce le determina precedenta .Intr-o expresie operatiile sunt executate in ordinea precedentei lor de la mare la mica. Cand operatorii au precedenta egala atunci ei se evalueaza de la stanga la dreapta. 1. Toti operatorii de comparatie si SQL au precedenta egala: =,!=,,=,BETWEEN...AND,IN,LIKE,IS NULL. 2. NOT(pentru a inversa rezultatul unei expresii logice.De ex: WHERE not(sal>2000)) 3. AND 4. OR.

De fiecare data cand sunteti in dubiu despre care dintre doua operatii vor fi executate mai intai cand o expresie este evaluata, sunteti liberi sa utilizati parantezele pentru a clarifica semnificatia dorita si pentru a va asigura ca SQL*Plus face ceea ce doriti. Sa presupunem ca doriti sa gasiti toti managerii, din orice departament,si toti functionarii din departamentul 10: SELECT * FROM EMP WHERE JOB='MANAGER' OR (JOB = 'CLERK' AND DEPTNO = 10);

Parantezele de deasupra sunt necesare, AND are o precedenta mai mare decat OR ,dar ele clarifica semnificatia expresiei.

SELECT-Sumar

Urmatoarele clauze sunt inchise in comanda SELECT: SELECT FROM WHERE ORDER BY SELECT selecteaza cel putin o coloana Alias poate fi folosit pentru coloanele din lista selectata * desemneza toate coloanele DISTINCT poate fi utilizat pentru eliminarea duplicatelor FROM Tabela desemneaza tabela din care provin coloanele WHERE restrictioneaza cererea la randurile care indeplinesc o conditie.Poate contine valori de coloane,expresii si literali AND/OR poate fi utilizat intr-o clauza WHERE pentru a construi conditii mai complexe. AND are prioritate peste OR. () pot fi utilizate pentru a forta prioritatea ORDER BY intotdeauna apare la sfarsit .Specifica ordinea de sortare.Una sau mai multe coloane pot fi specificate aici. ASC ordinea ascendenta este ordinea de sortar ( implicita) si nu trebuie specificat. DESC inverseaza ordinea de sortare de default si trebuie specificat dupa un nume de coloana. [DISTINCT] [*,coloana alias],...] tabela conditie(ii) [coloana,expr] [ASC/DESC];

Clauzele pot fi introduse pe linii separate in buffer si tabelarea este utilizata pentru claritate si in editare.

Logarea la SQL*Plus Aceasta sectiune explica cum sa ne logam la SQL*Plus si liniile de iesire ale tipurilor de comenzi ce pot rezulta in cadrul sau. SQL*Plus

SQL*Plus este un program scris de Corporatia Oracle,ce produce un mediu pentru comenzile SQL ce pot fi tastate direct sau rulate dintr-un fisier de comanda.In plus ,comenzile SQL pot fi derivate.Ele sunt folosite pentru: * formatarea rezultatelor * setarea optiunilor * editarea si stocarea declaratiilor SQL

Odata ce v-ati logat la sistemul vostru de operare,sunt 3 moduri pentru a va loga la SQL*Plus: 1. SQLPLUS + Veti vedea dupa aceasta un mesaj ca acesta: + SQL*Plus: Version 3.1.1 Production on Mon Oct 4 1993 Copyright (c) 1992,Oracle Corporation,California,USA. All rights reserved. Enter Username: + Introduceti numele vostru de user si apasati RETURN: SQL*Plus va afisa promptul :"Enter Password:". Introduceti parola voastra si apasati din nou RETURN. + Pentru protectia voastra ,parola nu va apare pe ecran. SQL*Plus va afisa promptul sau: SQL> + Aceasta indica linia de comanda .Acolo sunt doua feluri de comenzi si puteti introduce pe acesta linie de comanda:comenzi SQL sau comenzi SQL*Plus. 2. SQLPLUS username + Veti fi indemnati sa va dati parola. 3. SQLPLUS username/password + Veti fi logati la SQL*Plus.In acest caz parolz v a fi afisata.

Alegeti metoda pe care o preferati. Editarea declaratiilor SQL utilizand comenzi SQL*Plus 1. Cand veti introduce o comanda SQL,aceasta este stocata intr-o zona de memorie utilizata de buferul SQL si ramane acolo pana ce veti introduce o noua comanda. 2. Daca apasati [RETURN] inainte de a completa o comanda,SQL*Plus va afisa un numar de linie. 3. Terminatorul pentru declaratiile SQL este un ';'. 4. Cat timp declaratia SQL este in bufer,sunt cateva operatii de editare directe care pot fi executate utilizand comenzi SQL*Plus:

Comanda Abrevierea ROL ---------------------------------------------------------------------APPEND text A text adauga 'text' la sfarsitul liniei curente. CHANGE C/old/new schimba vechiul text cu noul text in linia curenta. CHANGE C/text/ sterge 'text'-ul din linia curenta CLEAR BUFFER CL BUFF sterge toate liniile din buferul SQL. DEL sterge linia curenta.

INPUT INPUT LIST LIST n LIST m, n RUN /

I I text L Ln L m n R

insereaza un numar nedefinit de linii. insereaza o linie constituita din 'text'-ul respectiv listeaza toate liniile din buferul SQL. listeaza o linie (specificata de n) listeaza un numar de linii(de la m la n). afiseaza si executa comanda SQL curenta din buffer. executa comanda SQL care este curenta in bufer.

Diversitatea comenzilor SQL*Plus Comenzile SQL (precum SELECT) sunt mijloace de acces la date prin kernelul Oracle.Comenzile SQL sunt utilizate in special pentru controlul mediului, formatarea rezultatelor interogarilor si controlul fisierelor.Comenzile identificate aici sunt amestecate si trebuie sa le folositi in urmatoarele exercitii. Comenzile SQL sunt introduse la promptul SQL>pe o linie ,ele nu vor deschide un buffer. Comanda + Descriere SAVE numefis permite salvarea intr-un fisier a continutului buferului SQL. GET numefis incarca continutul unui fisier salvat in prealabil in buffer. START numefis ruleaza un fisier de comanda salvat in prealabil. Fisierele de comanda sunt tratate in capitolul 10. ED numefis utilizeaza un editor de default pentru a edita continutul unui fisier salvat. EXIT paraseste SQL*Plus.

Capitolul 2 Exercitii-Introducere in SQL

Aceste exercitii intentioneaza sa dezvaluie toate subiectele neobservate la o lectura anterioara.Daca aveti timp incercati intrebarea 13. Workshop 1. Selectati toate informatiile din tabela SALGRADE.

GRADE ----1 2 3 4 5

LOSAL ----700 1201 1401 2001 3001

HISAL ----1200 1400 2000 3000 9999

2. Selectati toate informatiile din tabela EMP. EMPNO ----7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934 ENAME ----SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER JOB ------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK MGR ---7902 7698 7698 7839 7698 7839 7839 7566 7698 7788 7698 7566 7782 HIREDATE -------13-JUN-83 15-AUG-83 26-MAR-84 31-OCT-83 05-DEC-83 11-JUL-84 14-MAY-84 05-MAR-84 09-JUL-84 04-JUN-84 04-JUN-84 23-JUL-84 05-DEC-83 21-NOV-83 SAL COMM DEPTNO -------- ------- -----800.00 20 1,600.00 300.00 30 1,250.00 500.00 30 2,975.00 20 1,250.00 1,400.00 30 2,850.00 30 2,450.00 10 3,000.00 20 5,000.00 10 1,500.00 .00 30 1,100.00 20 950.00 30 3,000.00 20 1,300.00 10

14 inregistrari selectate. 3. Listati toti angajatii care au salariul intre 1000 si 2000. ENAME DEPTNO SAL ----- ------ -------ALLEN 30 1,600.00 WARD 30 1,250.00 MARTIN 30 1,250.00 TURNER 30 1,500.00 ADAMS 20 1,100.00 MILLER 10 1,300.00 6 inregistrari selectate. 4.Listati numerele de departament si numele in ordinea numelor departamentelor. DEPTNO -----10 40 20 30 DNAME ---------ACCOUNTING OPERATIONS RESEARCH SALES

5. Afisati toate tipurile diferite de job-uri. JOB --------ANALYST CLERK MANAGER

PRESIDENT SALESMAN 6. Listati detaliile angajatilor din departamentele 10 si 20 in ordinea alfabetica a numelui. EMPNO ----7876 7782 7902 7566 7839 7934 7788 7369 ENAME ----ADAMS CLARK FORD JONES KING MILLER SCOTT SMITH JOB ----CLERK MANAGER ANALYST MANAGER PRESIDENT CLERK ANALYST CLERK MGR ---7788 7839 7566 7839 HIREDATE --------04-JUN-84 14-MAY-84 05-DEC-83 31-OCT-83 09-JUL-84 7782 21-NOV-83 7566 05-MAR-84 7902 13-JUN-83 SAL COMM DEPTNO -------- ---- -----1,100.00 20 2,450.00 10 3,000.00 20 2,975.00 20 5,000.00 10 1,300.00 10 3,000.00 20 800.00 20

8 inregistrari selectate. 7.Listati numele si ocupatiile tuturor functionarilor in departamentul 20. ENAME -----SMITH ADAMS JOB ----CLERK CLERK

8. Afisati toti angajatii ai caror nume contine TH sau LL in interior. ENAME -----SMITH ALLEN MILLER 9. Listati urmatoarele detalii pentru toti angajatii care au un manager. ENAME -----SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT TURNER ADAMS JAMES FORD MILLER JOB -------CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST SALESMAN CLERK CLERK ANALYST CLERK SAY -------800.00 1,600.00 1,250.00 2,975.00 1,250.00 2,850.00 2,450.00 3,000.00 1,500.00 1,100.00 950.00 3,000.00 1,300.00 13 inregistrari selectate. 10. Afiseaza numele si totalul remuneratiei pentru toti angajatii. ENAME REMUNERATION

------- -----------SMITH 9600 ALLEN 19500 WARD 15500 JONES 35700 MARTIN 16400 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 15600 14 inregistari selectate. 11. Afiseaza toti salariatii care au fost angajati in anul 1983. ENAME DEPTNO HIREDATE ------- ------ --------SMITH 20 13-JUN-83 ALLEN 30 15-AUG-83 JONES 20 31-OCT-83 MARTIN 30 05-DEC-83 FORD 20 05-DEC-83 MILLER 10 21-NOV-830 6 inregistrari selectate. 12. Afisati numele,salariul anual si comisionul pentru toti vanzatorii ai caror salariu lunar este mai mare decat comisionul lor.Iesirea va fi ordonata dupa salariu , cele m ai mari primele. Daca doi sau mai multi angajati au acelasi salariu trebuie sortati dupa nume in odinea celor mai mari salarii. ENAME ANNUAL_SAL COMM ------ ---------- -------ALLEN 19200 300.00 TURNER 18000 .00 WARD 15000 500.00 Incercati-va aptitudinile cu acesta. 13. Selectati informatiile dupa cum sunt selectate. Cine,cand si cum -------------------------------------------------------------------SMITH HAS HELD THE POSITION OF CLERK IN DEPT 20 SINCE 13-JUN-83 ALLEN HAS HELD THE POSITION OF SALESMAN IN DEPT 30 SINCE 15-AUG-83 WARD HAS HELD THE POSITION OF SALESMAN IN DEPT 30 SINCE 26-MAR-84 JONES HAS HELD THE POSITION OF MANAGER IN DEPT 20 SINCE 31-OCT-83 MARTIN HAS HELD THE POSITION OF SALESMAN IN DEPT 30 SINCE 05-DEC-83 BLAKE HAS HELD THE POSITION OF MANAGER IN DEPT 30 SINCE 11-JUN-84 CLARK HAS HELD THE POSITION OF MANAGER IN DEPT 10 SINCE 14-MAY-84 SCOTT HAS HELD THE POSITION OF ANALYST IN DEPT 20 SINCE 05-MAR-84 KING HAS HELD THE POSITION OF PRESIDENT IN DEPT 10 SINCE 09-JUL-84 TURNER HAS HELD THE POSITION OF SALESMAN IN DEPT 30 SINCE 04-JUN-84 ADAMS HAS HELD THE POSITION OF CLERK IN DEPT 20 SINCE 04-JUN-84

JAMES HAS HELD THE POSITION OF CLERK FORD HAS HELD THE POSITION OF ANALYST MILLER HAS HELD THE POSITION OF CLERK

IN DEPT 30 SINCE 23-JUL-84 IN DEPT 20 SINCE 05-DEC-83 IN DEPT 10 SINCE 21-NOV-83

14 inregistrari selectate.

Capitolul 2 - Rezolvari

1. 2. 3.

SELECT FROM SELECT FROM SELECT FROM WHERE

* SALGRADE; * EMP; ENAME, DEPTNO, SAL EMP SAl BETWEEN 1000 AND 2000;

4.

SELECT DEPTNO, DNAME FROM DEPT ORDER BY DNAME; SELECT FROM SELECT FROM WHERE ORDER BY SELECT FROM WHERE AND SELECT FROM WHERE OR SELECT FROM WHERE SELECT FROM SELECT FROM WHERE SELECT FROM DISTINCT JOB EMP; * EMP DEPTNO IN (10,20) ENAME; ENAME,JOB EMP JOB = 'CLERK' DEPTNO = 20; ENAME EMP ENAME LIKE '%TH%' ENAME LIKE '%LL%'; ENAME, JOB, SAL EMP MGR IS NOT NULL; ENAME, SAL*12+NVL(COMM,0) REMUNERATION EMP; ENAME, DEPTNO, HIREDATE EMP HIREDATE LIKE '%83'; ENAME, SAL*12 ANNUAL SAL, COMM EMP

5. 6.

7.

8.

9.

10. 11.

12.

WHERE SAL >COMM AND JOB = 'SALESMAN' ORDER BY SAL DESC, ENAME; 13. SELECT ENAME|| ' HAS HELD THE POSITION OF '|| JOB|| ' IN DEPT '|| DEPTNO|| ' SINCE '|| HIREDATE "Who, what and when" EMP; C A P I T O L U L 3

FROM

Rularea cererilor standard cu variabile substituite.

In acest capitol sunt descrise variabilele substituite.Variabilele substituite sunt folosite in declaratiile SQL si permite utilizarea valorilor specificate la rulare. Variabile in SQL *Plus

In plus fata de variabilele pe care le vom vedea in limbajul PL/SQL, SQL*Plus este inzestrat cu doua tipuri de variabile ale sale: Variabile legate Utilizate pentru stocarea valorilor individuale ce pot fi asignate si citite in timpul rularii(vor fi discutate mai tarziu in acest curs). Variabile substituite Utilizate pentru stocarea partilor de text de comanda ,care sunt "editate" in comenzi inainte de executia lor. Variabile substituite cu un singur ampersand. Puteti utiliza 'variabile substituite' intr-un fisier de comanda sau intr-o declaratie SQL pentru a reprezenta valori ce vor fi aflate la executie. O variabila poate fi vazuta ca un container in care valoarea este stocata temporar. O variabila substituita este prefixata de un singur ampersand(&) si o valoare care ii este asignata. Urmatoarea declaratie obliga utilizatorul sa dea un numar de departament la executie:

SELECT FROM WHERE

EMPNO,ENAME,SAL EMP DEPTNO = &DEPARTAMENT_NUMBER; 10

Enter value for department_number: EMPNO ----7782 7839 7934 ENAME SAL --------- ----------CLARK 2,450.00 KING 5,000.00 MILLER 1,300.00

Exemplul anterior utilizeaza conditia WHERE DEPTNO=10. Cu un singur ampersand utilizatorul este obligat sa dea o valoare de fiecare data cand comanda este executata,deoarece variabila nu este definita si de fiacare data valoarea introdusa nu este salvata . O valoare de tip caracter sau data are nevoie sa fie inchisa in ghilimele simple cand sunt introduse la cerere.Pentru a evita ghilimelele simple ce tre- buie sa fie introduse la executie ,puteti pune variabilele in ghilimele simple. In urmatoarea declaratie ,variabila este pusa intre ghilimele simple, asa ca ghilimele nu mai sunt necesare la introducerea valorii respective:

SELECT FROM WHERE

ENAME,DEPTNO,SAL*12 EMP JOB = ' &ltJOB_TITLE';

Enter value for job_title:MANAGER ENAME ----JONES BLAKE CLARK DEPTNO SAL*12 ------ -------20 35700 30 34200 10 29400

Cat timp variabila este variabila 'nedefinita' ,valoarea va fi ceruta de fiecare data cand declaratia este executata. Este posibil sa fie cerut numele unei coloane sau chiar numele unei tabele in timpul executiei. In urmatorul exemplu vi se cere o expresie aritmetica:

SELECT FROM

DEPTNO, &ltARITHMETIC_EXPRESION EMP;

Enter value for arithmetic_expression:sal/12 DEPTNO -----20 30 30 20 30 30 10 20 10 30 20 30 20 10 SAL/12 ------66.6667 133.333 104.167 247.917 104.167 237.5 204.167 250 416.667 125 91.6667 79.1667 250 108.333

Variabile substituite cu dublu ampersand. Daca variabila este prefixata de dublu ampersand(&&),SQL*Plus va cere valoarea pentru variabila doar o singura data .SQL*Plus stocheaza prima valoare furnizata si o foloseste din nou de fiecare data cand declaratia SQL este rulata

SELECT FROM WHERE

ENAME,DEPTNO,JOB EMP DEPTNO = &&DEPTNO_PLEASE;

Enter value for deptno_please: 10 ENAME DEPTNO JOB ----- ------ -------CLARK 10 MANAGER KING 10 PRESIDENT MILLER 10 CLERK

Puteti folosi comanda SQL*Plus DEFINE pentru a determina daca variabila este deja definita.Daca variabila este deja definita se afiseaza valoarea asignata.

SQL>DEFINE DEFINE DEPTNO_PLEASE = "10" (CHAR)

Comanda DEFINE este de asemenea folosita pentru a crea o variabila utilizator. Substitutia datelor ca o comanda text Cand informatia dintr-o comanda substituita este introdusa intr-o co- manda inaintea executiei sale,variabila poate fi folosita pentru a inzestra orice parte a structurii comenzii exceptand numele insusi al comenzii.De exemplu:

SELECT WHERE

* FROM SALGRADE &CONDITION

Enter value for CONDITION: losal>2000

Variabila CONDITION ,de mai sus,trece intreaga conditie a clauzei WHERE intr-o comanda.Mai departe intregul cod al comenzii(incepand cu cuvantul SELECT) poate fi trecut intr-o variabila. SELECT &THE_REST; Comanda DEFINE O valoare poate fi asignata unei variabile folosind comanda DEF[INE] a lui SQL*PLUS.Valoarea definita trebuie referita intr-o construc- tie SELECT sau un fisier de comenzi , prefixind numele variabilei cu &. Variabilele pot fi golite folosind UNDEF[INE]. In urmatorul exemplu , o variabila a fost definita folosind o expresie aritmetica ce calculeaza remuneratia. In urmatoarele constructii, variabila REM e referita de un numar de ori. Variabila e apoi golita fo- losind UNDEFINE.

SQL>DEFINE SQL&gtSELECT SQL&gtFROM SQL&gtORDER BY SQL &gtundefine

REM ='SAL*12+NVL(COMM,0)' ENAME, JOB, &ltREM EMP &ltREM; REM

ENAME JOB SAL*12+NUL(COMM,0) -----------------------------------------------SMITH CLERK 9600 JAMES CLERK 11400 ADAMS CLERK 13200 WARD SALESMAN 15500 MILLER CLERK 15600 MARTIN SALESMAN 16400 TURNER SALESMAN 18000 ALLEN SALESMAN 19500

CLARK BLAKE JONES SCOTT FORD KING

MANAGER MANAGER MANAGER ANALYST ANALYST PRESIDENT

29400 34200 35700 36000 36000 60000

'' de la expresii sunt optionale , daca expresia nu contine spatii. Ghilimelele pot fi deasemenea folosite si sunt optionale dar folositoare daca expresia contine blancuri sau apostrofi. Pornirea unui fisier cu comenzi care contine substitutii de variabile Presupunind ca doriti o serie de rapoarte care sa listeze angajatii cu functii diferite - de exemplu , una pentru SALESMEN , una pentru CLERKS, una pentru MANAGERS si tot asa.Cunoasteti cum sa folositi substitutia de variabile pentru a obtine aceste rapoarte dintr-o singura constructie SELECT. Cu toate acestea , exista o tehnica alternativa pe care s-o folositi si anume sa scrieti constructia SELECT intr-un fisier si apoi folosind comanda START sa-l executati. Aceasta tehnica impune folosirea unor substitutii speciale de variabile. Exista 9 asemenea variabile si ele au intregii de la 1 la 9 ca nume. Pentru folosirea acestor variabile , puneti un '&' urmat de un intreg (1-9) in comanda SQL.Aceste variabile pot fi folosite de ori de cite ori doriti si in orice ordine . De fiecare data cind comanda e rulata, fiecare '&1' din comanda e inlocuit de primul parametru dupa START fisier, fiecare '&2' e inlo- cuit de al doilea parametru s.a.m.d. Pentru a crea un fisier de comenzi care ia un parametru specificind functia care trebuie afisata , introduceti:

SELECT EMPNO,ENAME,SAL FROM EMP WHERE JOB = ' &lt1' ; SQL&gtSAVE JOB1 SQL*PLUS intoarce mesajul: Created file job1 Apoi rulati comanda cu parametrul 'CLERK'. SQL*PLUS inlocuieste variabila cu valoarea parametrului. SQL> START JOB1 EMPNO ----7369 ENAME ----SMITH CLERK SAL --800

7876 7900

ADAMS JAMES

1100 950

Observati ca nu se pot folosi variabile cind executati o comanda cu RUN. Trebuie sa introduceti comanda in fisier si sa o rulati cu START comanda. Comanda ACCEPT Comanda ACCEPT permite unei variabile sa fie create si unei valori , care e introdusa sa fie memorata in ea. Aceasta variabila poate fi apoi referita in constructia SQL. ACCEPT este folosita deseori intr-un fisier de comenzi . Sunt citeva avantaje rezultate din folosirea lui ACCEPT pentru definirea substitutiei de variabile: * Tipurile de date pot fi verificate * Prompturile pot fi mai explicative * Valorile de raspuns pot fi ascunse

Sintaxa comenzii este: ACC[EPT] variabila [ NUMBER/CHAR] [PROMPT/NOPROMPT 'text'][HIDE]

Sintaxa + Descrierea NUMBER/CHAR determina tipul variabilei.Daca valoarea introdusa este nevalida , va fi afisat un mesaj. PROMPT 'text' afiseaza daca text e specificat NOPROMPT face ACCEPT sa sara o linie asteptind intrarea HIDE suprima raspunsul utilizatorului. folositor pentru parole

Exemple

SQL>ACCEPT SALARY NUMBER PROMPT 'Salary figure :' Salary figure : 30000 SQL&gtACCEPT PASSWORD CHAR PROMPT 'Password :' HIDE Password : SQL&gtACCEPT COMM NUMBER NOPROMPT 500 SQL&gtDEFINE

DEFINE SALARY =30000(NUMBER) DEFINE PASSWORD ="FREEBIES(CHAR) DEFINE COMM =500(NUMBER)

Rezumat

Cind SQL*Plus intilneste &variabila: 1. Daca &variabila este deja definita, definitia deja existenta e folosita. 2. Daca & variabila nu e definita: + Utilizatorul e gata pentru definitie + Definitia furnizata de utilizator e apoi folosita + Definitia furnizata de utilizator e apoi pierduta

Cind SQL Plus intilneste &&variabila : La fel ca pasii 1 si 2 de deasupra cu exceptia faptului ca valoarea variabilei e salvata. Observatie: &1 parametru e tratat ca &&. Pentru a afla daca o variabila e deja definita, folositi comanda DEF[INE]: DEF DEPARTAMENT daca e definit, da definitia lui DEPARTAMENT daca nu specifica 'nedefinit'

Pentru a defini o variabila , se poate folosi deasemenea comanda DEF[INE]. DEF COMM = Ghilimelele nu sunt necesare , dar ajuta daca "NVL (COMM,0)" expresia contine blancuri imbricate sau apostrofi.

Cit timp ramin variabilele definite? Pina cind le UNDEF[ine] sau le redefinim sau pina iesim din SQL*PLUS. Alte 2 modalitati de definire a unei variabile: SQL&gtACCEPT variabila [tip] [PROMPT text][HIDE] SQL&gtCOL[UMN] nume coloana /alias NEW_VALUE variabila (tratata in capitolul 11)

Cap.3 Exercitii -Rulind cereri cu parametrii. Acest exercitiu va ofera oportunitatea sa creati fisiere care pot fi rulate interactiv , si care folosesc substitutii de variabile pentru crearea unor criterii de selectie. TEME 1. Generati o constructie prin care sa afisati angajatii cu date de angajare intre 2 date date. Rulati de 2 ori cererea. Modificati cererea pentru a folosi &&variabila .Rulati-o de citeva ori. Observati diferenta. 2. Obtineti o cerere care sa accepte o functie data. Pentru testare executati cererea de citeva ori. ENAME JOB SAL MGR DEPTINO ---------------------------------------------SCOTT ANALYST 3,000.00 7566 20 FORD ANALYST 3,000.00 7566 20 3. Definiti o variabila reprezentind expresia folosita pentru calculul remuneratiei anuale a angajatilor.Folositi variabile intr-o constructie care gaseste toti angajatii care cistiga 30.000 $ pe an sau mai mult. ENAME SAL*12+NUL(COMM,0) ---------------------------------JONES 35700 BLAKE 34200 SCOTT 36000 KING 60000 FORD 36000

Cap.3 Solutii

1.

SELECT FROM WHERE SELECT FROM WHERE SELECT FROM WHERE

ENAME,HIREDATE EMP HIREDATE BETWEEN'&FIRST_DATE'AND'&LAST_DATE'; ENAME,JOB,SAL,MGR,DEPTNO EMP JOB ='&JOB'; ENAME,&REM EMP &REM >30000; C A P I T O L U L 4

2.

3.

FUNCTII

In acest capitol, sunt introduse functiile.Functiile fac ca cererile de baza sa devina mai puternice si sint folosite pentru manipularea valorilor.Acest capitol acopera functiile numerice si pe caracter. Functiile de conversie si care opereaza pe tipuri de date sunt tratate in cap.5. In final functiile de grup sunt tratate in cap.6. INTRODUCERE Functiile sint folosite pentru manipularea datelor.Ele accepta unul sau mai multe argumente si intorc o valoare.Un argument este o constanta , variabila sau o referire de coloana.Formatul pentru functie este urmatorul: function_name(arg1,arg2,...)

Functiile pot fi folosite pentru: * a calcula * a modifica datele individuale * a manipula iesirea pentru grupuri de siruri * a schimba formatul datelor pentru afisare * a converti tipurile de date

Exista diferite tipuri de functii: * CHARACTER * NUMBER * DATE * CONVERSION * FUNCTION THAT ACCEPT ANY DATA TYPE AS INPUT * GROUP

Unele functii opereaza pe un singur sir , altele pe grupuri de siruri. Cele mai folosite functii sunt prezentate in acest manual. Puteti recurge la SQL*Plus REFERENCE GUIDE pentru lista completa a functiilor. Functiile care se aplica unui singur sir vor fi discutate in capitolul prezent si in cap.5.Functiile de grup vor fi tratate in cap.6. Functii care se aplica unui singur sir. * actioneaza asupra fiecarui sir returnat din cerere * intoarce un rezultat pentru fiecare sir * asteapta unul sau mai multe argumente * pot fi imbricate * pot fi folosite acolo unde se folosesc variabile , coloane sau expresii , de exemplu , in constructiile :SELECT,WHERE,ORDER BY.

Explicatia notatiei: Notatie + Semnificatie:

col un nume de coloana dintr-o baza de date value(valoare) orice valoare literale(caracter/data/numar) n reprezinta un sir 'string' reprezinta caracterul string chars reprezinta un numar de caractere specificate date reprezinta o coloana date sau o valoare date

Functii numerice si pe caracter Acestea accepta la intrare un caracter si poate returna si caracter si valoare numerica. Urmatoarele functii prezentate sunt: LOWER(col/value) forteaza caracterele alfa care sunt scrise cu litere mari sau mixte in caractere scrise cu litere mici.

Pentru a afisa cu litere mici numele departamentului ca si sirul ' SQL COURSE' introduceti: SELECT LOWER (DNAME),LOWER('SQL course') FROM DEPT; LOWER(DNAME) LOWER(SQL COURSE) -----------------------------------research sql course sales sql course operations sql course accounting sql course

UPPER(col/value) forteaza caracterele alfa care sunt scrise cu litere mici , sau o combinatie de litere mici si mari in caractere scrise cu litere mari.

In urmatorul exemplu , functia UPPER e folosita pentru a forta intrarea utilizatorului la litere mari.

SELECT ENAME FROM EMP WHERE ENAME =UPPER('&ENAME'); Enter value for ename :smith ENAME ----SMITH

INITCAP(col/value) forteaza prima litera a fiecarui cuvint in litera mare

Pentru afisarea numelor departamentelor si locatiile , introduceti: SELECT FROM INITCAP(DNAME), INITCAP(LOC) DEPT;

INITCAP(DNAME) INITCAP(LOC) ---------------------------Accounting New York Research Dallas Sales Chicago Operations Boston

CONCAT(char 1,char2) intoarce char1 concatenat cu char2 (Alternativa a operatorului ||)

SELECT CONCAT (ename,job) "JOB" FROM EMP WHERE EMPNO =7900; JOB ----------JAMESCLERK

Functiile LPAD si RPAD aduc sirurile de caractere la o lungime specificata. LPAD(col/value,n,'string') adauga la coloana sau la valoarea literala spre stinga pina la lungimea totala n. Primele spatii sunt umplute cu 'string'. Daca 'string' e omis atunci sunt umplute cu blancuri.

SELECT LPAD(DNAME,20,'*'),LPAD(DNAME,20),LPAD(DEPTNO,20,'.') FROM DEPT; LPAD(DNAME,20,'*') LPAD(DNAME,20) LPAD(DEPTNO,20,'.') -----------------------------------------------------------************RESEARCH RESEARCH.....................20 ***************SALES SALES.....................30 **********OPERATIONS OPERATIONS.....................40 **********ACCOUNTING ACCOUNTING.....................10

Observati ca a doua coloana e completata in stinga cu spatii, implicit , si ca a treia coloana este de tipul numar. RPAD(col/value,n,'string') adauga la coloana sau la valoarea literala spre dreapta pina la lungimea totala n. Ultimele pozitii sunt umplute cu 'string' sau daca acesta e omis cu blancuri.

SELECT RPAD(DNAME,20,'*'),RPAD(DNAME,20),RPAD(DEPTNO,20,'.') FROM DEPT; RPAD (DNAME,20,'*') RPAD(DNAME,20) RPAD(DEPTNO,20,'.') -----------------------------------------------------------RESEARCH************ RESEARCH 20.................. SALES*************** SALES 30.................. OPERATIONS********** OPERATIONS 40.................. ACCOUNTING********** ACCOUNTING 10..................

In acest caz a doua coloana are in dreapta blancuri puse implicit. Urmatoarele functii presupun ca caracterele din string sunt numerotate de la stinga la dreapta , incepind cu 1. SUBSTR(col/value,pos,n) intoarce un string de n caractere lungime dintr-o coloana sau valoare literala , incepind de la pozitia pos. Daca n e omis, e extras sirul din pozitia pos la sfirsit.

Urmatorul exemplu afiseaza urmatoarele "substringuri": * 4 caractere din literalul 'ORACLE' incepind de la al doilea caracter * continutul lui DNAME incepind cu al doilea caracter * 5 caractere din DNAME incepind cu al treilea caracter SELECT SUBSTR('ORACLE',2,4),SUBSTR(DNAME,2), SUBSTR(DNAME,3,5) FROM DEPT;

SUBSTR('ORACLE',2,4) SUBSTR(DNAME,2) SUBSTR(DNAME,3,5) --------------------------------------------------------RACL ESEARCH SEARC RACL ALES LES RACL PERATIONS ERATI RACL CCOUNTING COUNT

Observati ca valorile sint aliniate la stinga.Aceasta deoarece SQL*Plus intotdeauna afiseaza de la stinga, implicit. INSTR(col/value,'string') gaseste pozitia caracterului in care apare prima data 'string'. INSTR(col/value,'string',pos,n) gaseste pozitia caracterului pentru a n-a aparitie a lui string in coloana sau valoarea literala incepind din pozitia pos.

SELECT DNAME,INSTR(DNAME,'A'), INSTR(DNAME,'ES'),INSTR(DNAME,'C',1,2) FROM DEPT; DNAME INSTR(DNAME,'A') INSTR(DNAME,'ES') INSTR(DNAME,'C',1,2) --------------------------------------------------------------------ACCOUNTING 1 0 3 RESEARCH 5 2 0 SALES 2 4 0 OPERATIONS 5 0 0

O folosire obisnuita a lui INSTR este de a determina daca inceputul utilizatorului contine un caracter particular sau mai multe. In exemplul anterior , de exemplu, expresia INSTR(DNAME,'ES') e 0 pentru ACCOUNTING deoarece acesta nu contine sirul dat. LTRIM si RTRIM sterg caracterele specificate din sir. LTRIM (col/value,'char/s') sterge de la stinga prima aparitie a caracterului specificat(sau o combinatie a caracterelor specificate). Daca nu e specificat nici un caracter sterge toate blancurile din stinga.

SELECT DNAME,LTRIM(DNAME,'A'),LTRIM(DNAME,'AS'), LTRIM(DNAME,'ASOP') FROM DEPT; DNAME LTRIM(DNAME,'A') LTRIM(DNAME,'AS') LTRIM(DNAME,'ASOP')

------------------------------------------------------------------RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES LES LES OPERATIONS OPERATIONS OPERATIONS ERATIONS ACCOUNTING CCOUNTING CCOUNTING CCOUTING RTRIM (col/value,'char/s') sterge de la dreapta, aparitiile lui char (sau combinatie de caractere spe- cificate) .Daca nu e specificat 'char/s' atunci sterge blancurile.

SELECT DNAME, RTRIM(DNAME,'G'),RTRIM(DNAME,'GHS') RTRIM(DNAME,'N') FROM DEPT; DNAME RTRIM(DNAME,'G') RTRIM(DNAME,'GHS') RTRIM(DNAME,'N') --------------------------------------------------------------RESEARCH RESEARCH RESEARC RESEARCH SALES SALES SALE SALES OPERATIONS OPERATIONS OPERATION OPERATIONS ACCOUNTING ACCOUNTIN ACCOUNTIN ACCOUNTING

RTRIM poate fi in mod particular folositor in a sterge blancurile de la sfirsit la coloane.De exemplu , presupunind ca in transferul datelor la tabela 'emp' blancurile au fost adaugate neintentionat dupa fiecare ENAME.Urmatoarea comanda va sterge toate blancurile finale: UPDATE EMP SET ENAME =RTRIM(ENAME); Observatie: comanda UPDATE e tratata complet mai tirziu. SOUNDEX(col/value) intoarce un sir de caractere reprezentind pronuntia(sunetul) cuvintului pentru fiecare coloana sau valoare literala.Aceasta functie intoarce o reprezentare fonetica a fiecarui cuvint si ne permite sa comparam cuvinte care sunt scrise diferit, dar se pronunta(suna) la fel.

SELECT FROM WHERE

ENAME, SOUNDEX(ENAME) EMP SOUNDEX(ENAME) = SOUNDEX('FRED');

ENAME SOUNDEX(ENAME) ----------------------------FORD F630

LENGTH(col/value) intoarce numarul de caractere(sau digiti) din coloana sau din valoarea literala.

SELECT LENGTH('SQL COURSE'),LENGTH(DEPTNO),LENGTH(DNAME) FROM DEPT; LENGTH('SQLCOURSE') LENGTH(DEPTNO) LENGTH(DNAME) ------------------------------------------------10 2 8 10 2 5 10 2 10 10 2 10

Observati ca LENGTH la fel ca si functia INSTR intoarce o valoare numerica. Functiile TRANSLATE si REPLACE sunt folosite pentru substitutia caracterelor. TRANSLATE(col/value,from,to) translateaza la iesire caracterele 'from' la cele 'to'.Mai mult de un caracter , se poate potrivi. Toate operatiile lui 'from' sunt inlocuite de corespondentul din 'to'. Daca caracterul corespunzator din 'to'nu e rezervat , caracterul din 'from'e sters(vezi functiile imbricate). SELECT ,ENAME,TRANSLATE(ENAME,'C','P'),JOB TRANSLATE(JOB,'AR','IT') FROM EMP WHERE DEPTNO=10; ENAME TRANSLATE(ENAME,'C','P') JOB TRANSLATE(JOB,'AR','IT') ----------------------------------------------------------------CLARK PLARK MANAGER MINIGET KING KING PRESIDENT PTESIDENT MILLER MILLER CLERK CLETK

REPLACE(col/value,string,replacement_string) intoarce col/value cu orice aparitie a 'string-ului', inlocuita cu repla- cement_string.Daca 'replacement_string' e omis, toate operatiile lui 'string'sunt sterse.Daca sunt emise si 'string' si 'replacement_string'atunci se semnaleaza eroare.

SELECT JOB , REPLACE(JOB,'SALESMAN','SALESPERSON'), ENAME ,REPLACE(ENAME,'CO','PX') FROM EMP;

JOB REPLACE(JOB,'SALESMAN','SALESPERSON') ENAME REPLACE(ENAME,'CO','PX') -------------------------------------------------------------------ANALYST ANALYST SALESMAN SALESPERSON SALESMAN SALESPERSON MANAGER MANAGER SCOTT SPXTT TURNER TURNER ALLEN ALLEN CLARK CLARK

Functia REPLACE suplimenteaza functionalitatea furnizata de functia TRANSLATE.TRANSLATE furnizeaza caractere singulare , unul cite unul , pentru substitutie.REPLACE va permite sa substituiti un sir cu altul ca si sa stergeti un sir (daca nu specificati 'replacement _string'). Observati ca 'string' si 'replacement_string' pot fi de orice lungime. REPLACE poate sa faca si conversia caracter la caracter REPLACE('C', IP). FUNCTII IMBRICATE Functiile singulare aplicate liniilor(sirurilor) pot fi imbricate la orice adincime.Daca functiile sunt imbricate, ele sunt evaluate din interior spre exterior. Sa presupunem ca vreti sa aflati nr. de ori in care un caracter apare intr-un sir.Cum veti proceda? Puteti imbrica functiile LENGHT si TRANSLATE pentru a obtine rezultatul dorit. Urmatorul exemplu va permite sa numarati de cite ori apare S intr-un sir.

SELECT DNAME,LENGTH(DNAME),LENGTH(DNAME) LENGTH(TRANSLATE(DNAME,'AS,'A')) FROM DEPT; DNAME LENGTH(DNAME)LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'AS','A')) -------------------------------------------------------------------------RESEARCH 8 1 SALES 5 2 OPERATIONS 10 1 ACCOUNTING 10 0

Pasii pentru a obtine rezultatul sunt: 1. Folositi LENGTH pentru a identifica nr. de caractere din sir. 2. Apoi folositi TRANSLATE pentru a lua fiecare aparitie a lui S din sir.

SELECT

TRANSLATE(DNAME,'AS','A')

FROM DEPT; TRANSLATE(DNAME,'AS',A') -----------------------REEARCH ALE OPERATION ACCOUNTING 3. Observati ca A e inlocuit cu A si S nu are un caracter corespunzator cu care sa fie inlocuit . S e inlocuit cu nimic - e sters din sir. A serveste de plasare ? 4. Acum, scadeti din lungimea initiala a sirului pe cea din care am scos toate aparitiile lui S. LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'AS','A')) 5. Rezultatul e o valoare reprezentind nr. de aparitii ale lui S in sir.

O tehnica alternativa este de a folosi functia REPLACE. 1. Folositi REPLACE pentru a sterge fiecare aparitie a lui S din sir. Valoarea intoarsa va reprezenta nr. de caractere care au ramas dupa ce S a fost sters. Cu alte cuvinte , o valoare minus nr. de operatii ale lui S in sir. SELECT DNAME, LENGTH(DNAME), LENGTH(REPLACE(DNAME,'S')) FROM DEPT; DNAME LENGTH(DNAME) ENGTH(REPLACE(DNAME,'S')) ---------------------------------------------------ACCOUTING 10 10 RESEARCH 8 7 SALES 5 3 OPERATIONS 10 9 2. Apoi scadeti din lungimea totala , lungimea sirului fara S. SELECT DNAME,LENGTH(DNAME), LENGTH(DNAME) LENGTH(REPLACE(DNAME,'S')) FROM DEPT; DNAME LENGTH(DNAME) LENGTH(DNAME)-LENGTH(REPLACE(DNAME,'S')) ------------------------------------------------------------------ACCOUTING 10 0 RESEARCH 8 1 SALES 5 2 OPERATIONS 10 1 3. Rezultatul e o valoare care reprezinta nr. de operatii ale lui S in sir .

FUNCTII NUMERICE Acestea accepta intrare numerica si intorc ca rezultat un numar. Aceasta sectiune descrie citeva din functiile numerice.

ROUND(col/value,n) rotujeste coloana , expresie sau valoare la n zecimale. Daca n e omis nu are zecimale , daca e negativ , numarul din stinga punctului zecimal e rotunjit.

SELECT

ROUND(45.923,1), ROUND(45.923), ROUND(45.323,1), ROUND(42.323,-1), ROUND(SAL/32,2)

FROM EMP WHERE DEPTNO =10; ROUND(45.923,1)ROUND(45.923)ROUND(45.323,1)Round(42.323,-1)ROUND(SAL/32,2) -----------------------------------------------------------------------45.9 46 45.3 40 76.56 45.9 46 45.3 40 156.23 45.9 46 45.3 40 40.63 TRUNC(col/value,n) trunchiaza coloana sau valoarea la n zecimale, sau daca nu e om is , fara zecimale.Daca n e negativ , numarul din stinga punctului zecimal e trunchiat la zero. SELECT TRUNC(45.923,1), TRUNC(45.923), TRUNC(45.323,1), TRUNC(42.323,-1), TRUNC(SAL/32,2)

FROM EMP WHERE DEPTNO= 10; TRUNC(45.923,1)TRUNC(45.923)TRUNC(45.323,1)TRUNC(42.323,-1)TRUNC(SAL/32,2) ---------------------------------------------------------------------------45.9 45 45.3 40 76.56 45.9 45 45.3 40 156.25 45.9 45 45.3 40 40.62 CEIL (col/value) gaseste cel mai mic intreg mai mare sau egal cu coloana, expresie sau valoare. SELECT CEIL(SAL),CEIL(99.9),CEIL(101.76),CEIL(-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000; CEIL(SAL) CEIL(99.9) CEIL(101.26) CEIL(-11.1) ------------------------------------------------3000 100 102 -11 5000 100 102 -11 3000 100 102 -11 FLOOR (col/value) gaseste cel mai mare intreg mai mic sau egal cu coloana expresie sau valoare. SELECT FLOOR(SAL), FLOOR(99.9),FLOOR(101.76),FLOOR(-11.1) FROM EMP WHERE FLOOR(SAL) BETWEEN 3000 AND 5000; FLOOR (SAL) FLOOR(99.9) FLOOR(101.76) FLOOR(-11.1)

----------------------------------------------------3000 99 101 -12 5000 99 101 -12 3000 99 101 -12 POWER(col/value,n) ridica coloana, expresia sau valoarea la puterea n. Poate fi negativa. SELECT FROM WHERE SAL,POWER(SAL,2), POWER(SAL,3), POWER(50,5) EMP DEPTNO=10;

SAL POWER(SAL,2) POWER(SAL,3) POWER(50,5) ---------------------------------------------2450.00 6002500 14706125000 312500000 5000.00 25000000 125000000000 312500000 1300.00 1690000 2197000000 312500000 EXP(n) SELECT FROM EXP(4) -----54.59815 SQRT(col/value) gaseste radacina patrata a coloanei sau valorii. Daca col/value e NULL sau negativ atunci e intors rezultatul NULL. intoarce e ridicat la puterea n e=2.71828183. EXP(4) DUAL;

SELECT SAL, SQRT(SAL),SQRT(40), SQRT(COMM) FROM EMP WHERE COMM>0; SAL SQRT(SAL) SQRT(40) SQRT(COMM) ----------------------------------------------1600.00 40 6.32455532 17.3205081 1250.00 35.3553391 6.32455532 22.3606798 1250.00 35.3553391 6.32455532 37.4165739 SIGN(col/value) intoarce -1 daca e coloana, expresie sau valoare e un nr. negativ , intoarce 0 daca e zero,+1 daca e nr.pozitiv. SELECT SAL -COMM,SIGN(SAL-COMM),COMM-SAL, SIGN(COMM-SAL) FROM EMP WHERE DEPTNO=30; SAL-COMM SIGN(SAL-COMM) COMM-SAL SIGN(COMM-SAL) --------------------------------------------------1300 1 -1300 -1 750 1 -750 -1 - 150 -1 150 1 1500 1 -1500 -1 Frecvent , functia SIGN este folosita pentru a testa daca o valoare este mai mica ca , mai mare ca sau egala cu a doua valoare.

Urmatorul exemplu tipareste toti angajatii al caror salariu este mai mare decit comisionul lor. SELECT ENAME ,SAL,COMM FROM EMP WHERE SIGN (SAL-COMM)=1; ENAME SAL COMM ----------------------------ALLEN 1600 300 WARD 1250 500 TURNER 1500 0 ABS(col/value) gaseste valoarea absoluta a coloanei sau valorii.

SELECT SAL,COMM,COMM-SAL,ABS(COMM-SAL),ABS(-35) FROM EMP WHERE DEPTNO =30; SAL COMM COMM-SAL ABS(COMM-SAL) ABS(-35) ------------------------------------------------------1600.00 300.00 -1300 1300 35 1250.00 500.00 -750 750 35 1250.00 1400.00 150 150 35 2850.00 35 1500.00 00 -1500 1500 35 950.00 35 MOD(value1,value2) gaseste restul impartirii lui 'value1'la 'value2'. SELECT FROM WHERE ORDER BY SAL , COMM , MOD(SAL,COMM), MOD(100,40) EMP DEPTNO =30 COMM;

SAL COMM MOD(SAL,COMM) MOD(100,40) -------------------------------------------------------2,850.00 20 950.00 20 1,600.00 300.00 100 20 1,250.00 500.00 250 20 1,250.00 1,400.00 1250 20 1,500.00 00 1500 20

Alte citeva functii matematice: LOG(m,n) intoarce logaritmul cu baza m si argument n. SIN(n) intoarce sinusul lui n SINH(n) intoarce sinusul hiperbolic al lui n TAN(n)

intoarce tangenta lui n TANH(n) intoarce tangenta hiperbolica a lui n COS(n) cosinus de n COSH(n) cosinus hiperbolic al lui n

Exercitii-cap.4- Folosirea functiilor Aceste exercitii acopera folosirea functiilor nu doar in constructiile SELECT dar si in WHERE si ORDER BY. Daca sunt folosite aliasuri ale coloanelor in rezultat , folo- siti-le in constructia SELECT a propozitiei SQL. TEME 1. Listati pentru toti angajatii numele si salariul marit cu 15 % si exprimat ca un nr. de dolari. DEPTNO ENAME PCTSAL --------------------------20 SMITH 920 30 ALLEN 1840 30 WARD 1438 20 JONES 3421 30 MARTIN 1438 30 BLAKE 3278 10 CLARK 2818 20 SCOTT 3450 10 KING 5750 30 TURNER 1725 20 ADAMS 1265 30 JAMES 1093 20 FORD 3450 10 MILLER 1495 14 selectate. 2. Obtineti urmatoarea iesire: EMPLOYEE_AND_JOB ---------------SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK

FORD ANALYST MILLER CLERK 3. Obtineti urmatoarea iesire EMPLOYEE -------SMITH(Clerk) ALLEN(Salesman) WARD(Salesman) JONES(Manager) MARTIN(Salesman) BLAKE(Manager) CLARK(Manager) SCOTT(Analyst) KING(President) TURNER(SAlesman) ADAMS(Clerk) JAMES(Clerk) FORD(Analyst) MILLER(Clerk) 4. Faceti o cautare pentru a obtine o lista a angajatilor cu functia 'job' specificata de utilizator. Enter value for job :clerk EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----------------------------------------------------------7369 SMITH CLERK 7902 17-DEC-80 1000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 1092.5 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10 5. S- a descoperit ca nu toti angajatii din departamentul 30 sunt barbati .Obtineti urmatoarea iesire: ENAME DEPTNO JOB ---------------------------ALLEN 30 Salesperson WARD 30 Salesperson MARTIN 30 Salesperson BLAKE 30 Manager TURNER 30 Salesperson JAMES 30 Clerk

SOLUTII Cap.4

1. SELECT DEPTNO,ENAME, ROUND(SAL*1.15) PCTSAL FROM EMP; 2. SELECT RPAD(ENAME,10)||LPAD(JOB,10) EMPLOYEE_AND_JOB FROM EMP; 3. SELECT ENAME ||'('||initcap(job)||')' EMPLOYEE FROM EMP; 4. SELECT * FROM EMP WHERE UPPER(JOB)=UPPER('&JOB');

5. SELECT ENAME,DEPTNO,INITCAP (REPLACE(JOB,'SALESMAN','SALESPERSON')) JOB FROM EMP WHERE DEPTNO =30;

CAPITOLUL 5

Alte functii singulare aplicate liniilor unei baze de date

In acest capitol vom trata functiile (de tip) referitoare la date calendaristice 'DATE', functiile de conversie si functii care accepta orice tip de data de intrare. Functiile de tip data calendaristica

Aceste functii se aplica asupra datelor ORACLE. Toate functiile de tip data calendaristica intorc valoarea tipului DATE cu exceptia lui MONTHS_BETWEEN care intoarce o valoare numerica. Stocarea datelor calendaristice ORACLE

ORACLE stocheaza datele calendaristice intr-un format numeric intern reprezentind: * Secolul * Anul * Luna * Ziua * Ora * Minutele * Secundele

Formatul implicit de afisare sau intrare pentru o data calendaristica e DD-MON-YY.Datele calendaristice ORACLE pot varia intre 1 ian 4712 ien si 31 dec 4712 e.n Sysdate

Sysdate intoarce data curenta si timpul.Puteti folosi SYSDATE la fel ca folosirea oricarui nume de coloana. De exemplu , puteti afisa data curenta selectind SYSDATE dintr-o tabela. Este obisnuit sa se selecteze SYSDATE dintr-o tabela 'dummy' numita DUAL. Tabela DUAL apartine utilizatorului 'SYS' si poate fi accesata de toti utilizatorii.Contine o singura coloana ,DUMMY si o linie cu valoarea 'x'.

Tabela DUAL e folositoare cind doriti sa obtineti o singura valoare-de exemplu , valoarea unei constante , pseudo-coloane sau expresii care nu e derivata dintr-o tabela cu data 'user'. Pentru a afisa date curenta: SELECT SYSDATE FROM SYS.DUAL;

Puteti selecta usor SYSDATE din EMP, dar 14 linii cu aceeasi SYSDATE vor fi intoarse , una pentru fiecare linie din tabela EMP. DUAL e preferata pentru ca e suficienta o singura linie intoarsa. Folosirea operatorilor aritmetici

Pornind de la faptul ca data calendaristica e memorata ca un numar , e posibil sa se faca calcule cu date calendaristice, folosind operatori aritmetici ca + sau -.Puteti aduna sau scadea constante numerice ca si date calendaristice. Operatiile pe care le puteti face sunt: data + numar aduna un numar de zile la data, reintorcind o data calendaristica data - numar scade un numar de zile dintr-o data, producind o data calendaristica date - date scade o data dintr-o data , obtinind un nr. de zile. date + numar/24 aduna un nr. de ore pentru a obtine o data calen- daristica. SELECT HIREDATE,HIREDATE+7, HIREDATE-7,SYSDATE - HIREDATE FROM EMP WHERE HIREDATE LIKE '%JUN%'; HIREDATE HIREDATE+7 HIREDATE-7 SYSDATE-HIREDATE ------------------------------------------------------13-jun-83 20-jun-83 06-jun-83 1982.70628 11-jun-84 18-jun-84 04-jun-84 1618.70628 04-jun-84 11-jun-84 28-may-84 1625.70628 04-jun-84 11-jun-84 28-may-84 1625.70628

Scazind din SYSDATE ,HIREDATE din tabela EMP intoarce nr.de zile de la angajarea fiecarui om.

MONTHS_BETWEEN(data1,data2) gaseste nr. de luni intre data1 si data2. Rezultatul poate fi pozitiv sau negativ. Daca data1 e mai tarzie decat data2 , REZULTATUL E POZITIV, daca data2 este mai tirzie decit data1 , REZULTATUL E NEGATIV.

SELECT FROM WHERE

MONTHS_BETWEEN (SYSDATE,HIREDATE), MONTHS_BETWEEN('01-jan-84','05-nov-88') EMP MONTHS_BETWEEN(SYSDATE,HIREDATE)>59;

MONTHS_BETWEEN(SYSDATE,HIREDATE) MONTHS_BETWEEN('01-jan-84','05-nov-88') ----------------------------------------------------------------------65.0873622 -58.129032 63 -58.129032 60.5067171 -58.129032 59.3454267 -58.129032 59.3454267 -58.129032 59.8292977 -58.129032 6 records selected

Partea neintreaga a rezultatului reprezinta o portiune dintr-o luna. ADD_MONTHS(data,n) aduna n numar de luni calendaristice la 'data'. n trebuie sa fie intreg si poate fi si negativ. SELECT HIREDATE, ADD_MONTHS(HIREDATE,3),ADD_MONTHS(HIREDATE,-3) FROM EMP WHERE DEPTNO =20; HIREDATE ADD