Lucrarea 4.
Comenzi SQL in Oracle 8i, 9i, 10g
Structura SGBD Oracle
Baza de date
ORACLE
DELPHI
VdBASE
Visual C++
C++Builder
Java Builder
Java
O
D
B
C
Oracle SQL
SERVER
- Web DB
- Format/Report/Graf
- Power Object
- Oracle Expres
- Enterprise Manager
- Oracle Navigator
PL/SQL
SQL * PLUS
User1
User2
Structura SGBD Oracle
Baza de date Oracle este o colec ie de informa ii organizate sub forma de tabele pentru
care se asigur stocarea, actualizarea i reg sirea informa iilor prin limbajul SQL. BD con ine:
Tabele sistem de control (Dic ionar)
Tabele utilizator care con in ntr-o forma normal toate informa iile
Vederi ale bazei de date, care sunt machete pentru vizualizarea tabelelor
Proceduri, func ii i package-uri pentru prelucrarea informa iilor
Obiecte definite de utilizator cu propriet ile i metodele lor.
Limbajul SQL (Structured Query Language) este limbajul de fundamental pentru gestiunea
BD i este standardizat prin mai multe norme ISO (International Stantard Organization) acceptat
total sau par ial de toate Sistemele de Gestiune a Bazelor de Date (SGBD). SQL este un limbaj linie
care are un num r redus de comenzi i un num r mare de clauze n Oracle i DB2/IBM .
1
Oracle SQL Server asigura accesul la baza de date pentru toate celelalte procese client:
Realizeaz controlul accesului la informa iile din BD cu drepturi diferen iate pentru
utilizatori.
Asigur< execu=ia pentru toate comenzile SQL care provin de la utilizatori prin SQL*Plus
sau de la alte programe Oracle sau non Oracle.
Asigur integritatea informa iilor din BD prin controlul tranzac iilor i protec ia mpotriva
distrugerilor accidentale prin opera ii de rollback i arhivare.
P streaz toate informa iile de structur a obiectelor BD i asigur organizarea datelor i
controlul condi iilor impuse de corectitudine (constrngeri)
Asigura mecanisme de reg sirea rapid a informa iilor pe baza con inutului lor folosind
tehnici de indexare i randomizare i opera ii de combinare a datelor din mai multe tabele.
Asigura accesul partajat in condi ii de securitate a mai multor utilizatori la BD
SQL * Plus este un mediu de acces la serviciile SQL Server pentru utilizatorii conecta i la BD.
Asigur editarea comenzilor i programelor SQL i PL/SQL, ce se execut pe Serverul SQL
Lanseaz n execu ie comenzile SQL i PL/SQL, care se execut pe serverul SQL i preia
rezultatul, care este o tabel asigurnd interfa a ntre serverul SQL i utilizatori
Asigur setarea condi iilor de lucru pentru fiecare sesiune de lucru
PL/SQL este un server care asigur executarea programelor PL/SQL care con in comenzi
procedurale i comenzi SQL, ntr-un limbaj care extinde posibilit ile limbajului SQL.
Se pot definii variabile i structuri de tip tablouri indexate i record n memorie.
Permite definirea elementelor de tip Cursor pentru prelucrarea individual i secven ial a
nregistr rilor rezultate din interog rile SQL
Asigur definirea unor programe, proceduri >i func=ii stocate n BD
Permite controlul informa iilor i accesului la nivel superior prin proceduri de tip TRIGGER
asociate comenzilor de modificare.
Permite definirea unor clase de obiecte utilizator, care permit implementarea conceptului de
baze de date rela=ional obiectuale.
Accesul la PL/SQL se face de c tre utilizatori prin SQL*Plus i nu direct.
In PL/SQL se execut numai comenzile proprii, iar comenzile SQL care acceseaz< baza de date se
execut< pe Serverul SQL. PL/SQL are cteva sute de func ii i proceduri proprii.
Intefa=a cu utilizatorii
Toate comenzile SQL se execut< pe Serverul SQL, care execut toate controalele de securitate i
asigur integritatea datelor. Comenzle SQL pot proveni de la utilizatori conecta i la:
SQL*Plus care pot transmite comenzi direct sau din programe PL/SQL
Programe aplicative Oracle (WebDB, Oracle developer server, Oracle Expres server,..)
La module ODBC (Object DataBase Conectivity) din programe scrise n alte Limbaje de
programare, care au facilit i de conectare i interogare a bayelor de date (Delphi, Borland
C++ Buider, Visual dBase, Java, Visual C++, Java Buider,..)
WebDB este un pachet de programe Oracle pentru dezvoltare un programe de lucru cu BD
Oracle folosind o interfa grafic interactiv .
Oracle Developer (Format Reports)con ine un set de programe pentru dezvoltare de aplica ii
interactive de la un terminal local al re elei sau prin Internet. Se asigur :
Definirea unor interfe e cu utilizatorul n Format grafic Windows pentru actualizarea
tabelelor i interogarea BD;
Definirea de rapoarte pentru interog ri complexe n aplica ii
Executarea unor grafice bi sau tridimensionale folosind informa iile din tabelele BD
Oracle Expres Server este destinat studiilor statistice pe BD multidimensionale
(DataWorhouse).
Oracle Enterprise Manager este destinat administr rii BD folosint o interfa grafic
interactiv i poate fi utilizat numai cu drepturi de administrator (DBA).
Programele scrise n limbajele de programare clasice au acces la BD Oracle prin module
ODBC speciale fiec rui limbaj, dac au drepturi de acces la un user. Limbajul asigur mediul de
dialog interactiv, care permite introducerea datelor ce vor servi la construc ia unei interog ri.
Interogarea se transmite spre Serverul SQL al BD ca text, se execut de serverul SQL, care
returneaz un rezultat sub form de tabel spre programul client. Programul client preia tabela i
afi eaz datele n forma dorit folosind toate posibilit ile de dialog ale limbajului.
Biblioteca de programe OCI (Oracle Call Interface) asigur cea mai mare vitez de prelucrare
i procedurile pot fi apelate din limbaje ca C++ i PHP. Aceste proceduri apeleaz direct Serverul
SQL evitnd modulele ODBC care ncetinesc prelucrarea de cteva ori. Dezavantajul utiliz rii OCI
const n sc derea portabilit ii programelor.
Trebuie remarcat faptul c< numai Oracle SQL Server are acces la baza de date.
Lansare SQL*Plus
Componentele de baza Oracle sunt:
Oracle SQL Server - executa toate comenzile SQL
SQL*Plus
- mediu de dezvoltare,interfata cu utilizatorii
PL/SQL
- Limbaj procedural pentru dezvoltare aplicatii
Oracle SQL Server este singurul component Oracle care are acces la informatiile din Baza de Date,
asigurand integritatea si securitatea informatiilor memorate. Serverul executa toate comenzile SQL
pentru toti utilizatorii conectati la statii. Pentru fiecare utilizator conectat se deschide o sesiune
separata. Fiecare utilizator va avea parola lui, dar mai multe sesiuni se pot lansa pe acelasi user de la
statii diferite.
Toata interfata cu utilizatorii este asigurata de catre SQL*Plus, care are unele comenzi de editare,
permite comunicarea cu Qracle SQL Server si lanseaza programe PL/SQL.
Lansarea SQL*Plus se face:
START | Programs | Oracle OraHome81 | Aplication Developement | SQL*Plus
In fereastra deschisa pentru conectare se da:
User Name: SCOTT
tab
- user existent cu drepturi limitate
Password:
TIGER
tab
- parola utilizatorului
Host string: B623server enter - Numele Bazei de Date existente pe server
Se va lansa SQL*Plus si Oracle8i SQL Server si va apare prompter-ul de SQL.
Se pot introduce comenzi SQL terminate cu ;. Limbajul nu este case sensitive.
O comanda se poate scrie pe mai multe linii, se termina cu; si se lanseaza cu enter.
SQL>Select * From EMP; -- afiseaza inregistrarile din tabela EMP
EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM
-------- ---------- --------- --------- --------- --------- --------- --------7369 SMITH CLERK
7902
17-DEC-80
800
7499 ALLEN SALESMAN 7698
20-FEB-81
1600
300
7521 WARD SALESMAN 7698
22-FEB-81
1250
500
7566 JONES
MANAGER 7839
02-APR-81
2975
7654 MARTIN SALESMAN 7698
28-SEP-81
1250
1400
7698 BLAKE MANAGER
7839
01-MAY-81 2850
7782 CLARK MANAGER
7839
09-JUN-81
2450
7788 SCOTT ANALYST
7566 19-APR-87
3000
7839 KING
PRESIDENT
17-NOV-81
5000
7844 TURNER SALESMAN 7698
08-SEP-81
1500
0
7876 ADAMS CLERK
7788
23-MAY-87
1100
7900 JAMES CLERK
7698
03-DEC-81
950
7902 FORD
ANALYST
7566
03-DEC-81
3000
7934 MILLER CLERK
7782 23-JAN-82
1300
DESCRIBE EMP
DEPTNO
20
30
30
20
30
30
10
20
10
30
20
30
20
10
- comanda SQL*Plus ce afiseaza structura tabelei EMP de salariati
Name
Null?
Type
------------------------------- -------- ---EMPNO
NOT NULL NUMBER(4)
- tip numeric
ENAME
VARCHAR2(10)
- tip character de lungime variabila
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
- tip dta calendaristica
SAL
NUMBER(7,2) - tip numar cu 10 cifre din care 2 zecimale
COMM
NUMBER(10,2)
DEPTNO
NUMBER(2)
Afisarea informatiilor se face conform setarilor SQL+ realizate cu comanda SET:
SET LineSize 120
SET PageSize 60
SET ColSep
|
SET Long
200
SET AutoCommit ON
SHOW ALL
EDIT
- specifica numarul de caractere pe rand
- specifica numarul de randuri pe pagina pentru care se pune cap de tabel
- specifica separatorul folosit intre coloanele tabelei afisate
- numarul de caractere afisate pentru campurile de tip LONG
- face commit dupa fiecare comanda executata
- afisarea setarilor existente in SQL+
RUN
- comanda SQL*Plus care deschide o fereastra de editare a ultimei comenzi SQL
- in fereastra de editare nu se pune ; si se termina cu / pe ultimul rand
- comanda SQL*Plus pentru lansare ultima comanda SQL editata in buffer
EDIT prog
START prog
- permite editarea unui script SQL sau program PL/SQL
- lanseaza in executie un program PL/SQL sau un script SQL
Comenzi SQL
Se poate folosi alias pentru numele de campuri si tabele.
SELECT Ename NUME_PRENUME, Job Functia, Sal Salar, Deptno Nr_Depart
FROM Emp WHERE sal>=1300;
NUME_PRENU FUNCTIA
SALAR NR_DEPART
---------- --------- --------- --------JONES
MANAGER
2975
20
BLAKE
MANAGER
2850
30
CLARK
MANAGER
2450
10
SCOTT
ANALYST
3000
20
KING
PRESIDENT
5000
10
FORD
ANALYST
3000
20
4
EDIT - va permite editarea ultimei comenzi modificand-o in JOIN intre tabela EMP si DEPT cu afisare Nume
departament.
SQL> RUN
1 select Ename NUME_PRENUME, Job Functia, [Link] Salar, [Link] Nume_Depart
2* FROM Emp E,Dept D WHERE Sal>=2000 AND [Link]=[Link];
NUME_PRENU FUNCTIA
SALAR NUME_DEPART
---------- --------- --------- -------------CLARK
MANAGER
2450
ACCOUNTING
KING
PRESIDENT
5000
ACCOUNTING
JONES
MANAGER
2975
RESEARCH
FORD
ANALYST
3000
RESEARCH
SCOTT
ANALYST
3000
RESEARCH
BLAKE
MANAGER
2850
SALES
SELECT * FROM Emp ,dept;
-- este un produs cartezian admis de Oracle (fara conditie)
In lista de campuri afisate se accepta si expresii, care pot contine functii.
Functia NVL (Null Value) precizeaza valoarea implicita pentru cazul in care campul este NULL.
Toate campurilor la care nu s-au precizat valorile se completeaza cu valoarea NULL care este
nedeterminata si nu se admite in calcul
Like descrie o masca de cautare pentru camp ( jobul incepe cu A)
SELECT Ename NUME, Job Functia,
Sal+NVL(Comm,0)
- comisionul NULL inlocuit cu 0
Venit, sal*0.16 impozit
FROM emp WHERE job='MANAGER' OR Job Like 'A%';
NUME
FUNCTIA
VENIT IMPOZIT
---------- --------- --------- --------JONES
MANAGER
2975
595
BLAKE
MANAGER
2850
570
CLARK
MANAGER
2450
490
SCOTT
ANALYST
3000
600
FORD
ANALYST
3000
600
O tabela poate fi creata prin copierea alteia.
CREATE TABLE Pers AS SELECT * FROM Emp; - creaza tabela Pers copiind tabela Emp
SELECT * FROM Pers;
-- afiseaza continutul noii tabele
DESCR Pers
-- afiseaza structura tabelei pers
Crearea unei tabele se face precizand intr-o lista numele si tipul campurilor.
CREATE TABLE Stud (Nume Char(20), Adresa Varchar2(15),Bursa Number(7),Datan Date);
Table created.
Tipul Varchar2(20) este sir de caractere de lungime variabila si maxim 20.
Stergerea unei tabele.
SQL> DROP TABLE stud1;
Table dropped.
-- nu salveaza inregistrarile in Rollback
Adaugarea de inregistrari intr-o tabela:
INSERT INTO stud1 (nume, adresa,bursa,datan) - lista de campuri
VALUES ('Radu', 'Lugoj', 1200,'15-jan-77');
- lista de valori
1 row created.
Stergere inregistrari:
DELETE FROM stud WHERE Adresa Like Lugoj%;
% indica un numar de caractere oarecare
_ indica un caracter care poate avea orice valoare
Atentie!! DELETE FROM stud; -- sterge toate inregistrarile salvandu-le in Rollback
ROLLBACK;
COMMIT;
-- reface toate inregistrarile sterse sau modificate
-- sterge fisierul Rollback si modificarile sunt definitive
TRUNCATE TABLE Stud; sterge numai inregistrarile si tabela ramane goala
UPDATE stud set bursa=1200 WHERE Nume= Radu;
UPDATE stud set bursa=bursa*1.2;
-- mareste toate bursele cu 20%
Afisarea tuturor tabelelor din spatiul utilizator:
SELECT * FROM TAB;
- afisare numele tabelelor utilizatorului curent
SELECT * FROM CAT;
- afisare catalog utilizator
SELECT Owner,table_name from All_tables;
- tabela toate datele despre tabele
Descr All_tables
-- descrie structura tabelei All_tables
Se admite Select in Select. Afisare salariatii care au aceeeasi functia ca si SCOTT.
SELECT * FROM Emp WHERE job= ANY(SELECT job FROM Emp where Ename='SCOTT');
Daca SELECT returneaza mai mult de o inregistrare se foloseste ANY sau ALL
ANY conditie indeplinita daca cel putin o valoare selectata indeplineste conditia.
ALL - conditie indeplinita daca toate valorile selectate indeplinesc conditia.
SELF JOIN se admite deschizand de 2 ori aceeasi tabela cu alias-uri diferite.
Afisare salariati numele si salariul sefului.
SELECT [Link], [Link] Salar, [Link] Nume_sef, [Link] Salar_sef
FROM EMP E, EMP S WHERE [Link]=[Link];
Operatii pe grupe de inregistrari folosind GROUP BY si functii agregat SUM, COUNT, AVG,
MIN, MAX. Conditia de grup se verifica cu HAVING.
Afisare pentru fiecare functie suma si media salariilor, salarul maxim, minim si numarul salariatilor.
SELECT Job,avg(sal) Salar_mediu,Sum(sal) Suma_sal, Max(Sal) Sal_max,
Min(sal) Min_sal Count(*) FROM EMP Group By job HAVING Avg(sal)>1000;
6