CONSIDERATII DESPRE EFICIENTA SI PROIECTARE
Scopul acestui capitol:
Se vor trata temele:
- scrierea mai eficienta SQL in trigger-e.
- optiuni de prelucrare si rulare
- cursoare
- folosirea cursoarelor explicite
- sumar al erorilor si a comenzilor de exceptie
- folosirea semnului #
EFICIENT SI LIZIBIL
- Construiti-va SQL-ul lizibil
- Prezentati numele coloanelor
- Folositi alias-uri pentru tabele
- Cind folositi I N S E R T, listati coloanele
- Scrieti-va SQL-ul astfel incit sa folositi indecsi atunci cind alocati
- Minimizati-va SQL
EFICIENT si LIZIBIL
Construiti-va o baza de date lizibila
Exemplu:
SELECT e.empno
,e.ename
,d.dname
,s.grade
INTO :emp.empno
,:emp.ename
,:emp.scr_dname
,:emp.scr_grade
FROM emp e
,salgrade s
,dept d
WHERE :emp.empno = e.empno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.deptno = d.deptno;
PREZENTATI NUMELE COLOANELOR
Cind o tabela este specificata intr-o instructiune SQL, toate coloanele
de referinta ar trebui prezentate cu numele tabelei sau folosind aliasuri mai
scurte.In acest mod:
- se adauga claritate declaratiei
- imbunatateste performantele in timpul fazei de analiza sintactica
INSTRUCTIUNEA I N S E R T
Acolo unde este folosit INSERT, lista rubricilor(coloanelor) in care se
insereaza trebuie sa fie specificata.
Exemplu:
INSERT INTO ORDERS_HISTORY
(ORDERID,OLD_AMOUNT,CHANGEDATE)
VALUES........
Insert-urile nu vor fi modificate daca tabela este schimbata.
MINIMIZATI-VA SQL-ul
Declaratiile SQL necesita
- memorie(in general 4K)
- procesare Oracle
Evident, nu puteti neglija SQL FORMS, dar puteti minimiza SQL prin
folosirea de joins,proceduri form-level, triggers cu nume, PL/SQL.
I N D E X
- INDEX care grabeste interogarea
- folosit daca se cauta in mai putin de 10%-15% din rinduri
- alternativa este o tabela scan plina.
- INDEX SLOWS DOWN UPDATE
- Un INDEX este folosit daca:
- exista AND
- este referit intr-o clauza WHERE AND
- coloana indexata nu este modificata
- AND Oracle decide s-o foloseasca
- Principii generale
INDEXAREA
Atunci cind scrieti SQL, in SQL FORMS sau in oricare alta productie ORACLE,
intotdeauna va trebui sa luati in seama indexul. Intrebati-va :
- Vreau sa folosesc aici un index ?
- Daca da, mi-am scris SQL-ul astfel incit sa folosesc indecsi ?
Alternativa la folosirea unui index este o tabela scan. Deseori,(daca un
query va intoarce mai mult decit 10%-15% din rinduri) o tabela scan plina este
mai rapida decit un index. Cind scrieti SQL-ul, ar trebui sa stiti doua lucruri:
cum lucreaza indecsi si sa va cunoasteti datele.
Furnizarea indecsilor potriviti pentru declaratiile SQL si scrierea avanta-
joasa a SQL-ului va imbunatati performantele bazei de date. Indecsi sunt folo-
siti pentru accelerarea chestionarilor si asigurarea unicitatii valorilor din
coloane. Operatorii DML sunt mai lenti cind o tabela are indecsi.
Principii generale:
Indecsii trebuie sa fie stabiliti pe urmatoarele coloane:
INDEX UNIC----------------pe chei primare pentru a asigura unicitate
INDEX NON-UNIC------------pe toate coloanele join si coloane utilizate
pentru limitatea chestionarilor
Mai multe informatii despre indecsi puteti obtine
- citind Ghidul Administratorului Bazei de Date ORACLE( V.6.0.)
si Ghidul ORACLE RDBMS TUNING PERFORMANCE
- Cursul APPLICATION TUNING UNDER ORACLE V6 contine informatii
detaliate despre indecsi
SETARILE RUN AND DESIGN OPTIONS
Puteti sa setati optiunile pentru sesiunea curenta
Optiunile pe care le puteti alege din meniu sunt
_____________________________________________________________________________
| Action Form Block Field Trigger Procedure Image Help Option |
| ############################ Option Select ########################## |
| |
| _________________________________________________________________________ |
| | ---SQL*Forms Run Form Options--- | ---SQL*Forms Design Options--- | |
| |_____________________________________|___________________________________| |
| | [ ] Buffer Record in File | [ ] Disable Trigger Compilation | |
| | [ ] Debug Mode | [ ] Generate Before Executing Form| |
| | [ ] Disable Array Processing | [ ] Limit Undo Buffer | |
| | [ ] Display Block Menu | [ ] Show List of Values | |
| | [ ] Quiet Mode | [ ] Suppress Hints | |
| | [ ] Set Block Validation as Default | [ ] Use Forms as Default | |
| | [ ] Share Cursors for Implicit SQL | | |
| | [ ] Share Cursors for Trigger SQL | | |
| | [ ] Statistics | | |
| |_____________________________________|___________________________________| |
| |
| Buffer rows in a file ?################################################### |
| Frm:dept_emp Blk:dept2 Fld:DEPTNO Trg: Rep |
| |
|_____________________________________________________________________________|
Optiuni de rulare si proiectare
SQL FORMS va ofera optiuni care permit sa adoptati comportamentul formelor
cand le executati in SQL*Forms(Design). Exista,de asemenea,optiuni de proiectare
pentru modificarea comportamentului interfetei de proiectare.Nu trebuie sa se
utilizeze optiuni a caror setare sa afecteze definirea formei.
- Utilizati optiunea 'Option' din meniul principal a ajunge la forma
'Option Selection'.Form-ul 'Options Selection' permite setarea ambelor optiuni
SQL*Forms(Design) si SQL*Forms(Runform).
- Odata o optiune setata,ea ramane activa pana cand o resetati sau pana
cand se termina sesiunea curenta SQL*Forms(Design).
- In plus,puteti avea o configuratie utilizator care va permite sa
mentineti optiuni active pe parcursul sesiunii.
Pentru a seta o optiune:
- Utilizati [Next Field] pentru a va muta pe optiunea pe doriti sa o
setati.
- Apasati [Select] pentru a introduce valoarea in casuta de control.De
asemenea,tastarea oricarui caracter diferit de spatiu activeaza casuta ;
spatiu dezactiveaza casuta.
- Implicit toate optiunile sunt dezactivate.
- Cele mai multe optiuni au corespondent in corespondent in
linia de comanda.Vezi SQL*Forms Designer's Reference pentru
detalii.
OPTIUNI DE PROIECTARE
Actiunile multor optiuni de proiectare sunt descrise mai jos.Optiunile
de rulare vor fi mentionate in text.
Optiune de proiectare: Daca este setata:
---------------------- -----------------
Disable Trigger Compilation Trigger-ul nu se compileaza pana
cand nu se genereaza form-ul.
Generate Before Executing Contextul curent al form-ului se
Form genereaza automat cand optiunea
Execute este selectata.
Limit Undo Buffer Undo buffer contine doar cea mai
recenta actiune efectuata.
Show List of Values Afisarea automata a listei de valori
in intrarea unui camp care are lista
de valori.
Suppress Hints Optiunile de proiectare nu sunt afisate
in linia de mesaje.
Use Forms as Default Definirea form-ului este afisata
implicit in locul tabelei desfasurate.
INVOCAREA AUTOMATA A OPTIUNILOR SQL FORMS
- Fisierul utilizatorului
- se numeste SQLFORMS.CFG
- este in mod automat invocat odata cu comenzile SQLFORMS
- Cuvintele cheie corespunzatoare fiecarei optiuni
- Sintaxa pentru presetarea maparii
Invocarea automata a optiunilor SQLFORMS
Fisierul utilizatorului
Puteti sa va creati un fisier care deschide automat optiunile SQL Forms atunci
cind va veti loga la SQL Forms(Design). Se creaza un fisier SQLFORMS.CFG in di-
rectorul curent care este activ cind accesati SQL Forms. Fisierul contine una
sau mai multe intrari de formatul urmator:
keyword = ON | OFF
Fiecare cuvint cheie, listat mai jos, corespunde unei optiuni din fereastra
OPTIONS din SQL Forms(Design);
Tipul optiunii Cuvint cheie
__________________ __________________________
RUNFORM BUFFER_RECORD
DEBUG_MODE
DISABLE_ARRAY_PROCESSING
DISPLAY_BLOCK_MENU
QUIET_MODE
SET_BLOCK_VALIDATION_AS_DEFAULT
SHARE_CURSORS_FOR_IMPLICIT_SQL
SHARE_CURSORS_FOR_TRIGGER_SQL
STATITICS
SQLFORMS DISABLE_TRIGGER_COMPILATION
GENERATE_BEFORE_EXECUTING_FORM
LIMIT_UNDO_BUFFER_TO_1
SHOW_LIST_OF_VALUES
SUPPRESS_HINTS
USE_FORMS_AS_DEFAULT
Sintaxa pentru presetarea maparii
Pentru a preseta maparea SQL FORMS terminal si tastatura folositi urmatoarea
sintaxa pentru a indica un fisier mapat la ORACLE terminal
MAPPING = mapping_name
OPTIUNI IN LINIA DE COMANDA
Aceste optiuni se aplica unei sesiuni SQL*Forms din linia de comanda.Multe
dintre aceste optiuni corespund setarilor din ecranul 'Options'.Vezi SQL*Forms
Designer's Reference pentru lista completa.
SQL*Forms(Design)
Switch Description
------ -----------
-c Specifica o mapare a terminalului alta decat cea
implicita pentru device-ul curent.
-e Creeaza un 'key script file'
-l Limiteaza 'undo buffer' la o operatie.
-n Face contarea la SQL*Forms(Design) fara legarea la o
baza de date.Permite facilitati limitate pentru crearea
si modificarea form-ului.
-r Citeste un 'key script file'
SQL*Forms(Runform)
Switch Description
------ -----------
-a Opreste prelucrarea tabelei
-b Seteaza numarul de inregistrari incarcate in memorie
-c Specifica maparea neimplicita a terminalului
-d Modul debug
-e Creeaza un 'key script file'
-m Afiseaza blocul de meniu din form-ul anterior
-o Utilizeaza cursoare pentru trigger SQL din V2
-q Modul quiet
-r Citeste un 'key script file'
-s Afiseaza numarul de cursoare utilizate si setate
-t Utiliseaza cursoare pentru SQL implicit
CE PUTETI FACE PENTRU A UTILIZA MAI PUTINA MEMORIE
- Minimizati SQL
- Scurtati instructiunile SQL
- utilizati nume scurte si tabela de alias-uri
- Evitati repetarile SQL
- Utilizati optiunile SQL*Forms:
- Share Cursors for Implicit SQL
- Share Cursors for Trigger SQL
( doar pentru trigger-e din V2 )
- Eliberati memoria cand apelati o forma
CE PUTETI FACE PENTRU A UTILIZA MAI PUTINA MEMORIE
Cand scrieti SQL-ul:
- Minimizati instructiunile SQL.
- nu utilizati niciodata doua SELECT-uri - utilizati join.Nu
uitati de join-urile din afara.
- utilizati PL/SQL in loc de SQL de cate ori puteti
- Scurtati instructiunile SQL
- utilizati tabela de alias-uri
- scurtati numele obiectelor SQL*Forms
- Evitati repetarile SQL; scrieti in locul lor, proceduri
Optiuni de selectare SQL*Forms:
- Alegeti SHARE CURSORS FOR IMPLICIT SQL drept instructiune de rulare
( RUNFORM -T )
Aceasta optiune indica formei SQL sa distribuie cursoare pentru toti
DML impliciti ,generati de forma .Timpul de procesare creste cind
optiunea este selectata. Declaratiilor SELECT generate in interogari
li se vor asigna cursoare separate.
- Daca aveti V2 style trigerrs in forma, exista o a doua memorie
disponibila pentru salvarea optiunii
SHARE CURSORS FOR TRIGGER SQL (RUNFORM -R ) indica formei sa aloce
un cursor pentru toate declaratiile SQL in trigger-ele V2.
Acelasi efect obtineti daca folositi caracteristicile pasului trigger
New Cursor Area . Aceasta optiune nu are efect pentru style trigger-e
in V3.
Eliberarea memoriei cand apelati o forma
- Cand apelati o forma ,utilizati mai bine NEW_FORM decat CALL.
CUM SINT FOLOSITE CURSOARELE DE SQL FORMS ?
- Ce este un cursor ?
- Ce se intimpla intr-un cursor ?
- Cum folosesc cursoare SQL FORMS ?
- Cite cursoare foloseste o forma ?
Cum folosesc cursoare SQL FORMS ?
Ce este un cursor ?
Un cursor este o arie de memorie folosita de ORACLE pentru a verifica
si executa declaratii SQL. Marimea cursorului este determinata la instalarea
ORACLE in sistem. De regula este de 4K. ORACLE ii va acorda mai multa memorie
decit necesar pentru a mari complexitatea declaratiilor SQL.
Ce se intimpla intr-un cursor ?
O declaratie SQL trebuie sa fie verificata inainte de a fi executata. Verifica-
rea include controlul sintaxei, a informatiilor din tabela si din coloane,
rezolvarea referintelor catre variabile si determinarea cailor de acces optim
la date.
Cum folosesc formele cursoare ?
SQL FORMS deschid un cursor imediat ce o declaratie SQL trece in Oracle.
In timpul sesiunii Runform, cursorul ramine deschis si este actualizat
de fiecare data cind declaratia SQL este executata. Asta inseamna ca fiecare
declaratie din forma trebuie analizata o singura data. Executiile ulterioare
a aceleiasi declaratii duce la overhead.
La terminarea sesiunii Runform, toate cursoarele sunt inchise si
memoria eliberata.
Cite cursoare foloseste o forma ?
La initializare o forma primeste 4 cursoare. Forma este verificata si ramine
rezidenta. In timpul executiei, o forma simpla poate folosi 20-30 de cursoare,
iar una complexa, sute.
Sunt mai multe moduri de a reduce numarul de cursoare ale aplicatiei.
Determinati numarul de cursoare folosite de forma dv. !
Ca sa aflati cite cursore folositi in timpul unei sesiuni Runform, selectati
optiunea STATITICS in SQL_Forms. Numarul cursoarelor folosite va apare pe ecran
dupa ce iesiti din sesiunea Runform.
PL/SQL si cursoare
- Cursorul
- arie de lucru in memorie
- este necesar pentru fiecare declaratie SQL
- 2 tipuri de baza
- Cursoarele implicite contra celor explicite
- implicite
- usor de codificat
- cazuri de exceptie
- explicite
- greu de codat
- control marit
- numai pentru SELECTS
Folosirea cursoarelor explicite
La prelucrarea declaratiilor SQL, PL/SQL deschid arii de lucru care sunt
folosite pentru a executa declaratiile si pentru a stora informatia. Aceasta
informatie va putea fi accesata folosind un 'cursor'. PL/SQL foloseste doua
tipuri de cursoare: implicite si explicite.
Cursorele implicite sunt obtinute de PL/SQL pentru toate SQL netratate de
catre cursoarele explicite. Exemplele ce vor urma folosesc cursoare implicite.
Cursoarele explicite sunt declarate si tratate de codul PL/SQL.
Cursoarele explicite contra celor implicite
Avantajele folosirii cursoarelor implicite sunt:
- usurinta in codificare
- erorile pot fi tratate folosind criterii speciale
Totodata, al 2-lea fetch este intotdeauna realizat pentru a asigura faptul
ca un singur rind va fi returnat.
In exemplul urmator,PL/SQL realizeaza un al 2-lea fetch pentru a verifica
ca este un singur rind valid
BEGIN
SELECT DESCRIP
INTO :ITEM.SCR_DESCRIP
FROM PRODUCT
WHERE :ITEM.PRODID = PRODID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MESSAGE('invalid product number entered ');
RAISE FORM_TRIGGER_FAILURE;
END;
Al 2-lea fetch este important in cazul triggerelor post-change si post-query
pe un bloc cu multe inregistrari.
Folosirea cursoarelor explicite inlatura acest neajuns. Cursoarele explicite
sunt mai dificile din punct de vedere al codificarii si sunt folosite doar
pentru declaratii SELECT.
Declarati-va cursoare proprii
- DECLARE
- asociati un SELECT cu un nume de cursor
- OPEN
- executati o declaratie SELECT
- FETCH
- CLOSE
- TRATARE ERORI
- controlul nu este transferat sectiunii de exceptie
Declarati-va cursoare proprii
Un cursor primeste un nume si este asociat SELECT-ului, definit in sectiunea
de declaratii a blocului PL/SQL.
DECLARE
CURSOR prod_descrip IS
SELECT descrip
FROM product
WHERE :item.prodid = prodid;
BEGIN
.
Folosirea cursoarelor explicite
La executarea declaratiei SELECT, cursorul trebuie sa fie deschis in sectiunea
executabila. Pentru accesul la date, folositi declaratia FETCH. Fiecare FETCH
aduce un rind.
DECLARE
CURSOR prod_descript IS
SELECT descript
FROM product
WHERE :item.prodid =prodid;
BEGIN
OPEN prod_descript;
FETCH prod_descript
INTO :item.scr_descrip;
CLOSE prod_descipt;
END;
Declaratia CLOSE inchide cursorul.
Exemplul de mai sus va arata descrierea potrivita pentru identificatorul intro-
dus de utilizator. Daca utilizatorul introduce un identificator invalid atunci
nici unul dintre rinduri nu va suporta fetch.
Rezultatul ultimului fetch poate fi testat folosind atributele cursorului.Tes-
tul trebuie sa se faca inainte sa se produca inchiderea cursorului.
ATRIBUTELE CURSORULUI
Atributele cursorului
Sint 3 atribute care pot fi folosite pentru a returna informatia despre execu-
tia declaratiei SELECT
Attributes Values if no rows Values if>=1
fetched rows fetched
________________________ _______________________ ______________
%NOTFOUND TRUE FALSE
%FOUND FALSE TRUE
%ROWCOUNT 0 number of rows
fetched so far
Al 4-lea atribut, %ISOPEN, va intoarce TRUE, daca cursorul era deschis,si
FALSE in caz contrar.
Daca numele cursorului era C1, atunci atributele vor avea prefixul C1.
Exemplu 1:
IF C1 %NOTFOUND THEN ......
Exemplul 2:
DECLARE
CURSOR prod_descript IS
SELECT descript
FROM product
WHERE :item.prodid = prodid;
BEGIN
OPEN prod_descript;
FETCH prod_descript
INTO :item.scr_descript;
IF prod_descript %notfound THEN
MESSAGE ('Invalid product number entered ');
CLOSE prod_descript;
RAISE FORM_TRIGGER_FAILURE;
ENDIF;
CLOSE prod_descript;
END;
Observati ca declaratia CLOSE apare in IF si dupa IF. Se garanteaza ca, curso-
rul va fi inchis, chiar daca trigger-ul "cade", sau nu.
Folosirea cursoarelor explicite
Exemplul KEY-DELREC
Formele SQL, versiunea 3, folosesc cursoare explicite in cod generat automat
cind se specifica o relatie master/detail.
Urmatorul trigger este un exemplu al trigger-ului KEY-DELREC in blocul DEPT:
declare
cursor detail_cur is select 'x' from emp
where DEPTNO=:dept.DEPTNO;
detail_dummy char(1);
begin
open detail_cur;
fetch detail_cur into detail_dummy;
if ( detail_cur %found ) then
message
('Cannot delete master record when matching detail records
exist ');
close detail_cur;
raise form_trigger_failure;
end if;
close detail_cur;
end;
begin
delete_record;
clear_dept_details(FALSE,NO_COMMIT);
query_dept_details;
exception when form_trigger_failure then null;
end;
FOLOSIREA SEMNULUI #
# permite sa cititi un string direct din interogarea SQL Forms. Acest
mod adauga flexibilitate si il puteti utiliza in unele situatii.
MESAJE
Intelegerea cazurilor de eroare pentru fiecare tip de comanda
Cautati sa intelegeti erorile pentru fiecare tip de comanda
- Definiti situatii de eroare pentru:
- selectarea niciunui rind/ mai multe rinduri
- erori Oracle
- Folositi sectiuni executabile pentru
- testarea succesului/esecului procedurilor si actiunilor
formelorSQL
- verificarea rezultatului operatorilor DML
Intelegerea cazurilor de eroare pentru fiecare tip de comanda
In SQL*Forms Versiunea 3 designer-ul isi poate exercita controlul
peste cazurile de eroare si conditiilor speciale.Problemele constau in a stii
cum si unde sa testezi situatii de eroare sau conditii de esec.
ERORI SI SITUATII DE EXCEPTIE
---------------------------------------------------------------------------
Command Error/Exception Automatic Transfer to How/where to
Condition Exception Handler? query what
happened
----------------------------------------------------------------------------
Any SQL Oracle error yes WHEN OTHERS
or PL/SQL and use
SQLERRM &
SQLCODE
functions or
when predefined
exception
SELECT no record found yes when NO_DATA_FOUND
(Implicit > 1 record found yes when TOO_MANY_ROWS
cursor)
SELECT no record found no if< cursorname>%NOTFOUND
(Explicit then...
cursor) > 0 record found no if< cursorname>%FOUND
then...
RAISE whenever executed yes when< exception_name>
< exception_name> or
when OTHERS
DML no record acted upon no ifSQL%NOTFOUNDthen...
records acted upon no ifSQL%FOUNDthen...
Any any failure no use packaged functions
Packaged (FORM_FATAL,FORM_FAILURE
Procedure ERROR_CODE,ERRORTEXT)
STABILIREA STANDARDELOR
- Standardele sint:
- Practice, nu teoretice
- Realiste
- Usor de inteles
- E bine sa aderi la ele
- Standardele asigura:
- eficienta
- consistenta
- sustinere
- dezvoltare
STANDARDELE V3
LISTA 1
- Numele obiectelor->cit mai scurte
- Comentarii
- Format de mesaj
- facilitati de help
- facilitati de mesaje
- Screen layout
- utilizare pop-up
- utilizare meniu
LISTA 2
- Consideratii pop-up
- forma pop-up / blocul pop-up
- folosirea borderului/title-ului
- exit in cazul pop-up-urilor incomplete
- folositi PL/SQL
- folositi cursoare
- coduri generate de forme
- stocarea formelor
- INP pentru forme
- baza de date pentru biblioteci
LISTA 3
- form-level procedures
- library form
- contine
- proceduri generice
- antete de pagina
- triggere standard
- acces privilegiat
- mentine si schimba controlul
EXERCITII
EXERCITIUL A: CURSOARE EXPLICITE
- Rescrieti trigger-ul PRE-INSERT pe blocul CUS folosind cursoare explicite
- Modificati cimpul ON-VALIDATE al triggerului pe CUS.REPID pentru a folosi
cursoare explicite. Continutul cursorului poate fi storat intr-o variabila
locala declarata.
Daca aveti timp:
EXERCITIUL B: CREAREA UNUI FISIER
- Iesiti in sistemul de operare
- Creati un fisier numit SQLFORMS.CFG
- Scrieti urmatoarele in fisier
GENERATE_BEFORE_EXECUTING_FORM=ON
De cite ori "invocati" SQL*Forms din directorul curent, acest fisier
va fi citit de SQL*Forms.
CAPITOLUL 21 - S O L U T I I
EXERCITIUL A: CURSOARE EXPLICITE
- PRE-INSERT in blocul CUS
....................
declare
cursor cus_no is
select maxseqno+1, rowid
from seqnos
where table_name = 'CUS'
for update of maxseqno;
begin
open cus_no;
fetch cus_no
into :cus.custid,:cus.scr_rowid;
if cus_no %notfound then
message ('SYSTEM ERROR -can't get key value from SEQNOS');
close cus_no;
fail;
end if;
close cus_no;
end;
- ON-VALIDATE-FIELD on CUS.REPID
..................
declare
rep_flag char;
cursor sales_rep is
select 'X'
from emp
where :cus,repid=empno
and job = 'SALESMAN';
begin
open sales_rep;
fetch sales_rep into rep_flag;
if sales_rep %notfound then
message ('No salesman had this ID-please re-enter');
close cus_no;
fail;
end if;
close sales_rep;
end;
EXERCITIUL B: USER PREFERENCE FILE
NO FORMAL SOLUTION