CAPITOLUL 10
SQL*PLUS SI REPORTING
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 :
1. Comenzi SQL (cum ar fi SELECT).
2. Comenzi SQL*Plus (cum ar fi SAVE).
Comenzile SQL*Plus difera de comenzile SQL in urmatoarele moduri :
1. Ele nu sint conectate cu nici o declaratie particulara SELECT.
2. 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).
SPO[OL] OFF | OUT
OFF inchide fisierul de spool si OUT opreste procesul de
spool-ing si trimite fisierul de spool la imprimanta.
DESC[RIBE] 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).
CONN[ECT] 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 {OFF ON}
ON face ca SQL*Plus sa afiseze comenzile asa cum sint ele
executate dintr-un fisier de comenzi.OFF suprima acest lucru.
FEED[BACK] {6n OFF ON}
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.
HEA[DING] {OFF ON}
ON face ca SQL*Plus sa afiseze in rapoarte capetele de
coloane.OFF suprima capetele de coloane.
LIN[ESIZE] {80n}
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.
NEWP[AGE] {1n}
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.
NUM[FORMAT] 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.
NUM[WIDTH] {10n}
seteaza latimea implicita pentru afisarea valorilor numerice.
PAGES[IZE] {24n}
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.
VER[IFY] {OFF / ON text}
ON face ca SQL*Plus sa afiseze textul unei comenzi inainte si
dupa substituirea va- riabilei cu valoarea sa.OFF suprima acest
lucru.
TIM[ING] {OFF / ON}
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.
SPA[CE] {1n}
seteaza numarul de spatii dintre coloane pentru o afisare de
tabela.Valoarea maxima pentru n este 10.
TERM[OUT] {OFF ON}
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 {MIXED / LOWER / UPPER}
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 PAGES[IZE]
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 :
COL[UMN] nume_coloana / alias lista_optiuni
Observatii asupra optiunilor pentru COLUMN :
1. nume_coloana trebuie sa se refere la un alias de coloana, daca un
alias de coloana este folosit in clauza SELECT.
2. Optiunile pot apare in orice ordine.
3. 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).
4. Optiunile pentru COLUMN pot fi sterse in timpul unei sesiuni.
5. 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
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:
1. Formatul 999999 - afiseaza blank-uri pentru zerouri!
2. 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.
3. 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.
TTI[TLE] [printspec OFF ON] printspec defineste titlul si poate contine
mai multe clauze.
BTI[TLE] [printspec OFF ON] 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 [column/alias ROW][SKIP n DUP PAGE] ON...[ON REPORT]
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.
DUP[LICATE] duplica valorile. Implicit este:
NODUP[LICATE].
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
COU[NT] contorizeaza valori toate tipurile
nenule
MAX[IMUM] valoare maxima numar, caracter
MIN[IMUM] valoare minima numar, caracter
NUM[BER] contorizare a liniilor toate tipurile
STD deviatia standard numar
SUM suma valorilor nenule numar
VAR[IANCE] 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
1. compunere SELECT declaratie
2. SAVE nume fisier
3. 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.
4. CTRL Z VAX SPECIFIC
5. EX[IT] or QUIT
6. START nume fisier
CAPITOLUL 10 EXERCITIU - RAPORT SUB FORMA DE TABEL
1. 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.
2. 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>
BogSoft 1999