d6x6xp
In acest capitol vom prezenta facilitatile pentru SQL*Plus.In particular, capitolul prezinta modul in care comenzile SQL*Plus pot fi folosite pentru a formata rezultatele cererilor in rapoarte simple.

Comenzi SQL si SQL*Plus
SQL*Plus este un mediu in care pot apare doua tipuri de comenzi :

Comenzi SQL (cum ar fi SELECT).
Comenzi SQL*Plus (cum ar fi SAVE).
Comenzile SQL*Plus difera de comenzile SQL in urmatoarele moduri :

Ele nu sint conectate cu nici o declaratie particulara SELECT.
Ele se introduc pe o singura linie.Oricum, un caracter de continuare, li- niuta de unire, poate fi folosit daca comanda este prea lunga pentru a o introduce pe o singura linie.
Comenzile SQL*Plus pot fi folosite pentru a influenta prezentarea datelor furnizate de declaratiile SELECT, si din acest motiv sint folositoare pentru producerea rapoartelor, la fel de bine ca si pentru contolul mediului si pentru identificatorul de fisier.

Comenzile SQL*Plus sint introduse la promter-ul SQL pe o singura linie, dar ele nu deschid un buffer.Urmatoarele sint citeva comenzi suplimentare.

RUNFORM nume_fisier ruleaza o aplicatie a Oracle Forms din interiorul sesiunii tale SQL*Plus.
Spool nume_fisier scrie toate comenzile ulterioare si/sau le scrie in fisierul cu numele nume_fisier. Fisierului de spool i se asociaza extensia .LIS (pe unele sisteme extensia .LST).
SPOaOLi OFF | OUT
OFF inchide fisierul de spool si OUT opreste procesul de spool-ing si trimite fisierul de spool la imprimanta.
DESCaRIBEi nume_de_tabela afiseaza structura tabelei din baza de date.
HELP invoca help-ul intern din Oracle.
$0 | S comanda
HOST comanda invoca o comanda sistem din interiorul SQL*Plus ($ este specific pentru VAX).
CONNaECTi identificator_utilizator parola produce logarea la alt utilizator Oracle din interiorul login-ului curent.
PROMPT text afiseaza textul cind se ruleaza un fisier de comenzi.
Comenzi SET
Comenzile SET controleaza 'mediul' in care ruleaza in mod curent SQL*Plus. In general, comenzile asociaza o valoare unei variabile de sistem sau o trece pe ON sau OFF.Aceste comenzi pot fi folosite in mod implicit pentru fiecare sesiune prin includerea lor intr-un fisier numit LOGIN.SQL, care este citit de fiecare data cind se ruleaza SQL*Plus.Daca o comanda SET aparuta in timpul se- siunii modifica valoarea implicita, aceasta va fi valabila doar pentru acea sesiune.Iesirea (EXIT) din SQL*Plus va reseta variabilele de sistem la valo- rile lor implicite.

Comenzile SQL*Plus pot fi prescurtate.

Cuvintele subliniate reprezinta starile implicite pentru comenzile de mai jos.

ECHO AOFF ONS
ON face ca SQL*Plus sa afiseze comenzile asa cum sint ele executate dintr-un fisier de comenzi.OFF suprima acest lucru.
FEEDaBACKi A6n OFF ONS n face ca SQL*Plus sa afiseze numarul inre- gistrarilor selectate intr-o cerere cind sint selectate cel putin n inregistrari sau mai multe.ON sau OFF face ca aceasta sa fie sau nu afisata. Trecerea feedback-ului pe ON face ca n sa fie setat la 1.Setarea feed- back-ului la 0 este echivalent cu trecerea sa pe OFF.
HEAaDINGi AOFF ONS
ON face ca SQL*Plus sa afiseze in rapoarte capetele de coloane.OFF suprima capetele de coloane.
LINaESIZEi A80nS seteaza numarul de caractere pe care SQL*Plus le va afisa pe o linie si de asemenea con- troleaza centrarea si aliniamentul din dreapta a textului.Valoarea maxima pentru n este 500.
NEWPaAGEi A1nS seteaza numarul liniilor goale ce vor fi tiparite intre titlul de jos de la fiecare pagina si titlul de sus al paginii urmatoa- re.Valoarea 0 face ca SQL*Plus sa stearga ecranul pe majoritatea terminalelor.
NUMaFORMATi text seteaza formatul implicit pentru afisarea numarului articolelor de date.Text trebuie sa fie un format de numar.Vedeti optiunea COLUMN, clauza FORMAT pentru formatul nu- merelor descris sub titlul 'COLUMN - Optiuni', mai tirziu in acest subcapitol.
NUMaWIDTHi A10nS seteaza latimea implicita pentru afisarea valorilor numerice.
PAGESaIZEi A24nS seteaza numarul liniilor pe o pagina.Pentru rapoarte tiparite pe hirtie de lungime de 11 inch, valoarea 54 (plus valoarea NEWPAGE 6) lasa margini de 1 inch deasupra si dede- subtul paginii.
VERaIFYi AOFF / ON textS
ON face ca SQL*Plus sa afiseze textul unei comenzi inainte si dupa substituirea va- riabilei cu valoarea sa.OFF suprima acest lucru.
TIMaINGi AOFF / ONS
ON face ca SQL*Plus sa dea statistici de timp la fiecare declaratie SQL ce este rulata.OFF suprima acest lucru pentru fiecare comanda SQL.
SPAaCEi A1nS seteaza numarul de spatii dintre coloane pentru o afisare de tabela.Valoarea maxima pentru n este 10.
TERMaOUTi AOFF ONS
OFF suprima afisarea iesirii generate de comenzi executate dintr-un fisier, asa ca iesirea poate fi prelucrata fara sa apara pe ecran.ON permite ca iesirea sa fie afisata.
SQLCASE AMIXED / LOWER / UPPERS
LOWER / UPPER convertesc comenzile SQL chiar inainte de executie.Tot textul din interi- orul comenzii este convertit.MIXED lasa tex- tul nemodificat.
Pentru a gasi valoarea pentru o variabila SET folosim comanda SHOW. De exemplu, pentru a gasi dimensiunea paginii (pagesize), introducem :

SHOW PAGESaIZEi

Pentru a vedea valorile pentru toate variabilele SET, introducem :

SHOW ALL

|---------------------------------------------------------------------|

| |

| RAPOARTE SQL*PLUS |

| |

| Titlu |

| | |

| | |

| |------------------------------|------------------------------| |

| | | | |

| | Thu April 10 | Page 1 | |

| | | | |

| | | | |

| | COMPANY REPORT | |

| | | |

| | | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | ===== | |

| | | |

| | | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | ===== | |

| | | |

| | | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | ----- -------- ------ ----- --- ------- | |

| | | ===== | |

| | | ===== | |

| | | | | |

| | | Confidential | | |

| | | | | |

| | | | | |

| |------------------|---------------------------------|--------| |

| | | |

| | | |

| Formate de coloana Calcule |

| |

| |

|---------------------------------------------------------------------|

COLUMN - Optiuni
Comanda COLUMN stabileste optiunile de afisare pentru o coloana.Formatul este :

COLaUMNi nume_coloana / alias lista_optiuni

Observatii asupra optiunilor pentru COLUMN :

nume_coloana trebuie sa se refere la un alias de coloana, daca un alias de coloana este folosit in clauza SELECT.
Optiunile pot apare in orice ordine.
O data ce apare o declaratie de coloana, ea ramine activa tot timpul cit sesiunea SQL*Plus ramine deschisa.Ea continua sa fie setata in timp ce sint afisate alte tabele (fara aceasta coloana).
Optiunile pentru COLUMN pot fi sterse in timpul unei sesiuni.
Pentru a gasi setarea optiunilor curente pentru o coloana, introducem :

COL nume_coloana / alias

Alte citeva optiuni pentru COLUMN sint :

WRAP permite specificarea catre SQL*Plus ca ori de cite ori
TRUNC continutul actual al unei coloane depaseste latimea specifi- cata, informatia aditionala este impachetata (WRAP) prin rotunjire in linia urmatoare sau trunchiata (TRUNC).WRAP este valoarea implicita.
WORD_WRAPPED muta un cuvint de intrare pe linia urmatoare, in loc sa-l desparta pe doua linii.Un cuvint nu va fi despartit pe doua linii daca nu este mai mare ca latimea coloanei.
HEADING specifica un cap pentru o coloana.
NULL sir seteaza NULL-uri in coloana la sirul specificat.
PRINT
NOPRINT coloana va fi afisata / nu va fi afisata pe rapotrul de iesire.
NEW_VALUE var
Valoarea coloanei este inscrisa in variabila specificata in momentul in care coloana este selectata (SELECT).

Optiuni de format in comanda COLUMN
Formatul de afisare a coloanelor

An alfanumeric, de lungime n

9 pozitie numerica 999999 1234

0 forteaza zerouri principale 099999 001234

$ floating dollar $999999 $1234

. punct zecimal 999999.99 1234.00

, virgula 999,999 1,234

MI semnul minus la dreapta 999999MI 1234-

PR numere negative intre paranteze 999999PR <1234>

EEEE notatie stiintifica 99.999EEEE 1.234E+03

V multiplicare cu 10 la n 9999V99 123400

(n = numar de 9 dupa V)

B valorile zero devin blank-uri B9999.99

NOTA:
Formatul 999999 - afiseaza blank-uri pentru zerouri!
Formatul B9999.99 - daca punctul zecimal e inclus in formatul de afi- sare, formatul B este cerut pentru a face valori- le nule blank-uri.
Formatul B999999 - Formatul B nu are efect pentru ca 999999 fara 'B' descrie zerourile ca blank-uri.
MESAJE DE EROARE

### ...valoare prea mare pentru format

% ...tip gresit al valorii pentru format

Exemple de format al coloanelor

COLUMN DEPTNO FORMAT 099 HEADING 'Dept.'

COLUMN JOB FORMAT A9 HEADING 'Job' JUSTIFY RIGHT

COLUMN EMPNO FORMAT 9999 HEADING 'Empl|No'

COLUMN SAL FORMAT 99,999.99 HEADING 'Monthly|Salary'

COLUMN COMM FORMAT 99,990.99 HEADING 'Y-T-D|Commission' - NULL 'No Comm'

COLUMN REM FORMAT 999,999.99 HEADING 'Total Rem.'

SELECT DEPTNO,

JOB,

EMPNO,

SAL,

COMM,

SAL*12+NVL(COMM,0) REM

FROM EMP;

Rezultatul cererii afiseaza caracteristici ale optiunilor de formatare a coloanelor. Fiecare coloana are un titlu diferit. Justificarea titlului a fost schimbata din cea implicita, si in unele cazuri separata peste un numar de linii, folosind o bara verticala (|), Optiunea NULL a fost folo- sita sa forteze un sir de caractere sa fie afisat cand un null ar trebui in mod normal sa apara.

Clauza SELECT poate contine alias-uri de coloane, coloane cu un prefix de tabela, expresii si siruri literale care determina numele coloanei speci- ficat in comanda COLUMN a SQL*PLUS.

Daca aceasta este lista Folositi acest nume de coloana

de articole a SELECT-ului in comanda COLUMN

------------------------- ------------------------------

sal sal

emp.sal sal

e.sal sal

sal*12+nvl(comm,0) sal*12+nvl(comm,0)

sal*12 annsal annsal

sysdate sysdate

empno||'-'||ename empno||'-'||ename

To_char(Hiredate,'ddth To_char(Hiredate,'ddth MONYYYY')

MONYYYY')

To_char(sysdate,'Day Today

Mon YY')Today

Comenzile TTITLE si BTITLE
Comenzile TTITLE si BTITLE sunt folosite pentru a produce titluri pe o pagina.

Comanda Descriere

------- ---------

TTITLE 'sir de caractere' tipareste data curenta in coltul din

stanga sus al fiecarei pagini, numarul

paginii in coltul din dreapta sus si cen-

treaza titlul pe linia de dedesupt.

BTITLE 'sir de caractere' tipareste textul centrat in partea de jos

a fiecarei pagini. In ambele cazuri un ca-

racter '|' va face ca urmatorul text sa

fie centrat pe linia urmatoare.

TTITLE afiseaza TTITLE-ul sau BTITLE-ul curent

BTITLE

TTITLE OFF anuleaza afisarea unui titlu definit ante-

BTITLE OFF rior sau a unei note de subsol.

COLUMN DEPTNO FORMAT 099 HEADING 'Dept.'

COLUMN JOB FORMAT A9 HEADING 'Job' JUSTIFY RIGHT

COLUMN EMPNO FORMAT 9999 HEADING 'Empl|No'

COLUMN SAL FORMAT 99,999.99 HEADING 'Monthly|Salary'

COLUMN COMM FORMAT 99,990.99 HEADING 'Y-T-D|Commission' - NULL 'No Comm'

COLUMN REM FORMAT 999,999.99 HEADING 'Total Rem.'

TTILTE 'COMPANY REPORT|Produced by Personnel Dept'

BTITLE 'Company Confidential'

SELECT DEPTNO, JOB, EMPNO, SAL, COMM, SAL*12+NVL(COMM,0) REM

FROM EMP;

Comenzile TTITLE si BTITLE pot include un numar de clauze, facand posibil ca aparitia titlului sa fie specificata mai detaliat.

TTIaTLEi aprintspec OFF ONi printspec defineste titlul si poate contine

mai multe clauze.

BTIaTLEi aprintspec OFF ONi printspec defineste not de subsol si poate

contine mai multe clauze.

Clauze in Printspec

COL n sare tiparirea pozitiei n a liniei curente

(inapoi daca coloana a fost trecuta).

SKIP n sare de la startul unei noi linii de n ori.

Daca n este omis, sare o linie; daca n este

0, inapoi la inceputul liniei curente.

TAB n sare inainte n pozitii de tiparit (inapoi

daca n este negativ).

LEFT,CENTER and aliniere stanga, centru sau dreapta pe linia

RIGHT curenta. Articolele de date ce urmeaza aces-

tei clauze sunt aliniate ca un grup, pana la

sfarsitul comenzii TTILTE sau pana la urmato-

rul LEFT, CENTER, RIGHT sau COLUMN.(CENTER si

RIGHT folosesc valoarea lui SET LINESIZE

pentru a calcula pozitiile articolelor de da-

te).

FORMAT defineste formatul articolelor de date care o

urmeaza, pana la urmatoarea clauza FORMAT sau

pana la sfarsitul comenzii.Specificarea for-

matului este la fel cu cea a clauzei FORMAT

a comenzii COLUMN. O singura specificare de

format poate fi efectiva la un moment dat.

Daca este al unui tip de date gresit pentru

un articol particular, nu are efect asupra

acelui articol particular.

Daca nu este efectiva nici o clauza FORMAT

potrivita, valorile numerice sunt tiparite in

acord cu formatul SET NUMFORMAT, sau daca

SET NUMFORMAT nu a fost folosit, in acord cu

formatul implicit. Valorile datei sunt tipa-

rite in formatul implicit.

SQL.PNO Variabila sistem pentru numarul paginii cu-

rente a raportului. Va puteti referi la va-

loarea unei variabile sistem dandu-i prefixul

SQL. De exemplu, pentru a folosi variabila

PNO scrieti:

TTITLE RIGHT 'page' SQL.PNO

Aceasta va produce 'page 1' la marginea din

dreapta a liniei daca 1 este pagina curenta.

Eliminarea valorilor duplicate si spargerea unui raport in sectiuni
Liniile unui raport pot fi sparte in sectiuni, folosind comanda BREAK. Prin spargerea unei coloane, afisarea valorilor duplicate este eliminata. Puteti deasemenea sa lasati linii goale sau sa incepeti o noua pagina intre sectiuni. Pana cand va aparea o 'spartura' , valorile coloanelor se schimba de fiecare data, trebuie sa va reamintiti sa 'ORDER BY' coloana declaratia SELECT sau raportul dumneavoastra va fi rupt in sectiuni de neinteles.

Poate fi o singura comanda BREAK activa la un moment dat; prin urmare, daca cereti break-uri multiple ele trebuie sa fie specificate ca o singura comanda BREAK. Trebuie sa listati coloanele sparte in ordinea importantei, 'sparturile' majore mai intii.

Optiuni BREAK
Break-urile pot fi active la :

Column
Row
Page
Report

BREAK ON acolumn/alias ROWiaSKIP n DUP PAGEi ON...aON REPORTi

Un BREAK ON REPORT va permite calcule finale sumare. La orice "break", ur- matoarele optiuni pot fi specificate:

Optiune Descriere

------- ---------

PAGE renunta la o pagina cand o valoare in coloana

se schimba.

SKIP n sare n linii cand valoarea se schimba.

DUPaLICATEi duplica valorile. Implicit este:

NODUPaLICATEi.

Exemple:

BREAK ON REPORT ON DEPTNO PAGE ON JOB SKIP 2

BREAK ON REPORT ON DEPTNO PAGE ON JOB DUP

Pentru a curata break-urile emiteti comanda:

CLEAR BREAKS

Pentru a tipari break-urile curente emiteti comanda:

BREAK

Urmatorul exemplu ilustreaza folosirea BREAK-urilor:

COLUMN DEPTNO FORMAT 099 HEADING 'Dept.'

COLUMN JOB FORMAT A9 HEADING 'Job' JUSTIFY RIGHT

COLUMN EMPNO FORMAT 9999 HEADING 'Empl|No'

COLUMN SAL FORMAT 99,999.99 HEADING 'Monthly|Salary'

COLUMN COMM FORMAT 99,990.99 HEADING 'Y-T-D|Commission' - NULL 'No Comm'

COLUMN REM FORMAT 999,999.99 HEADING 'Total Rem.'

TTILTE 'COMPANY REPORT|Produced by Personnel Dept'

BTITLE 'Company Confidential'

BREAK ON DEPTNO SKIP 1 ON JOB ON REPORT

SELECT DEPTNO, JOB, EMPNO, SAL, COMM, SAL*12+NVL(COMM,0) REM

FROM EMP

ORDER BY DEPTNO,JOB;

Calcule Sumare
Comanda COMPUTE executa calcule pe break-uri stabilite de comanda BREAK.

COMPUTE clauza(e) OF coloana(e) ON break(uri)

Comanda Descriere

------- ---------

OF specifica coloana sau expresia a carei valoare va

fi calculata.

ON specifica articolul de date sau elementul de tabe-

la care sa fie folosit ca break.

Clauza Calcule Aplicate pe tipul de coloana

------ ------- ----------------------------

AVG valoare medie numar

COUaNTi contorizeaza valori toate tipurile

nenule

MAXaIMUMi valoare maxima numar, caracter

MINaIMUMi valoare minima numar, caracter

NUMaBERi contorizare a liniilor toate tipurile

STD deviatia standard numar

SUM suma valorilor nenule numar

VARaIANCEi variatie numar

Pot fi multe comenzi COMPUTE, cu toate ca adesea este mai usor sa specificati toate calculele cerute intr-o singura comanda.

De exemplu:

COMPUTE SUM AVG OF SAL COMM ON DEPTNO REPORT

va calcula salariile medii si totale si comisionul figurate in sectiunea DEPTNO si la sfarsitul REPORT-ului.
Fisierul de comenzi SQL*PLUS compunere SELECT declaratie
SAVE nume fisier
ED nume fisier

SET

COL

SET UP

TTITLE SQL*PLUS

ENVIRONMENT

BTITLE

BREAK

SELECT ...

SQL

/

TTITLE OFF

COL DEPTNO CLEAR RESET

SQL*Plus

COL EMPNO CLEAR ENVIRONMENT

Etc.

CTRL Z VAX SPECIFIC
EXaITi or QUIT
START nume fisier
CAPITOLUL 10 Exercitiu - Raport sub forma de tabel
Produce un raport care arata asemanator cu urmatorul. Asigurati-va la formatul coloanelor de date, la capul de tabel, si la specificatia ti- tlului paginii si notei de subsol.
Adaugati o comanda BREAK la script-ul SQL astfel ca raportul dumneavoas- tra sa sara o linie la sfarsitul detaliilor fiecarui departament, si e- limina duplicatele numelor de slujbe.
Capitolul 10 Solutie

SET ECHO OFF

SET PAGESIZE 24

SET FEEDBACK OFF

SET LINESIZE 78

COL A FORMAT A10 HEADING 'Department'

COL B FORMAT A9 HEADING 'Job'

COL C FORMAT 9999 HEADING 'Emp.|No.'

COL D FORMAT A8 HEADING 'Name'

COL E FORMAT A5 HEADING 'Hire|Date'

COL F FORMAT B99,999.99 HEADING 'Monthly|Salary'

COL G FORMAT 9,990.99 HEADING 'Annual|Comm'

COL H FORMAT 999,999.99 HEADING 'Total'

BREAK ON A SKIP 1 ON B

TTITLE 'EMPLOYEE REPORT'

BTITLE 'CONFIDENTIAL'

SELECT DNAME A,

JOB B,

EMPNO C,

ENAME D,

TO_CHAR(HIREDATE,'MM/YY') E,

SAL F,

COMM G,

SAL*12+NVL(COMM,0) H

FROM EMP E,DEPT D

WHERE E.DEPTNO = D.DEPTNO

ORDER BY DNAME,JOB

/

CLEAR COLUMNS

TTITLE OFF

BTITLE OFF

SET FEDBACK ON

SET PAGES 24

CLEAR BREAKS

,/pre>