CVIČENIE 11. Reporty

Téma cvičenia.

Tvorba zostáv a vytváranie dávok

SQL*Plus pre tvorbu zostáv

V SQL*Plus sú k dispozícii príkazy pre nasledujúce činnosti:

· Úprava, uloženie, prečítanie a spustenie súborov s príkazmi jazyka SQL

· Prevádzanie činností, ktoré súvisia s vytváraním zostáv, ako sú:

o Sumarizácie

o Výpočty

o Vytváranie záhlaví, zápätí a titulov

o Formátovanie záhlaví stĺpcov

o Prerušenie stránok

o Tlač zostáv, alebo uloženie výsledkov do súboru

· V SQL*Plus je možné pracovať priamo s procedurálnym programovacím jazykom PL/SQL bez nutnosti kompilovať programy

· Práca s premennými

· Zobrazenie definície stĺpcov v tabuľkách, pohľadoch a synonymách v databáze

· Kopírovanie dát medzi databázami

Základné príkazy pre vytváranie zostáv

Ak chceme vytvoriť zostavu v SQL*Plus, začneme tým, že napíšeme dotaz. Keď je vytvorený, môžete výstup začať formátovať pomocou príkazov SQL*Plus a vytvoriť nadpis, záhlavia stĺpcov atď.

PAGESIZE a LINESIZE

SET PAGESIZE n

SET LINESIZE n

Nastavenie počtu riadkov na stranu a počet znakov na riadok.

Príklad:

… štandard A4 na výšku

SQL>SET PAGESIZE 60

SQL>SET LINESIZE 123

Príklad:

… štandard A4 na šírku

SQL>SET PAGESIZE 25

SQL>SET LINESIZE 80

COLUMN

Pomocou príkazu COLUMN je možné zmeniť záhlavie stĺpca a niektoré vlastnosti formátovania.

COLUMN nazov_stlpca HEADING text_zahlavia FORMAT format WORD_WRAPPED

Pokiaľ text záhlavia obsahuje medzery je nutné ho uzavrieť do dvojitých úvodzoviek (""). Ak má byť zobrazené na dvoch riadkoch, použite ako oddeľovač zvislú čiaru (|).

FORMAT - NUMBER

Dátový typ	Formát	Výsledok
Number	999,999.00	4,550.00
Number	000,000.00	004,550.00
Number	$9,000.00	$4,550.00
Character	A10	Abcdefghij
Character	A20	Abcdefghijklmnoprstu

Pri znakových stĺpcoch sa formát zadáva v tvare A, kde n je počet znakov. Pokiaľ nechcete dáta v stĺpci orezať na určenú dĺžku, ale zalamovať pridajte k príkazu COLUMN parameter WORD_WRAP.

Príklad:

SQL> COLUMN meno FORMAT A5 WORD_WRAP

SQL> SELECT ou_meno meno FROM os_udaje;

MENO

-----

Karol

ina

Marek

Tomas

Jana

MENO

-----

Emil

Zuzan

Stani

slav

MENO

-----

Celin

Príklad:

SQL> COLUMN meno HEADING "Meno študenta" FORMAT A20

SQL> SELECT ou_meno meno FROM os_udaje;

Meno študenta

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

Karolina

Marek

Tomas

Jana

Emil

Zuzana

Stanislav

Celine

Príklad:

SQL> COLUMN meno HEADING "Meno|študenta" FORMAT A10

SQL> SELECT ou_meno meno FROM os_udaje;

MENO

STUDENTA

----------

Karolina

Marek

Tomas

Jana

Emil

Zuzana

Stanislav

Celine

Pre formáty čísel použite vzor z číslic 0 a 9. Pri formáte 990.00 napríklad bude u všetkých čísel vytlačená číslica pre jednotky.

Príklad:

SQL> column priemer format 90.00

SQL> select zp_st_os_cislo, avg(zp_vysledok ) priemer from zap_predmety

2* group by zp_st_os_cislo;

ZP_ST_OS_CISLO PRIEMER

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

1001 2.00

1002 1.00

1003 2.00

1004 2.00

1005 1.00

1006 2.00

1007 2.50

FORMAT - DATE

Pri stĺpcoch typu DATE nie je možné formát definovať príkazom COLUMN. Môžete však týmto príkazom definovať šírku stĺpca na zostave, aby zodpovedala formátu použitému pre zobrazenie stĺpca.

Formát stĺpca typu DATE je treba určiť pomocou funkcie TO_CHAR v príkaze SELECT. Často používané formátovacie znaky pre dátumy sú v nasledujúcej tabuľke. Formáty RR a RRRR sú v Oracle8 nové. Pracujú podobne ako formáty YY a YYYY, ale inak doplňujú storočie pre neúplne zadaný rok.

Skratka	Význam
DD	Deň v mesiaci (01 až 31)
Day	Deň v týždni, prvé písmeno je veľké (napr. Saturday)
MM	Mesiac (01 až 12)
Month	Názov mesiaca, prvé písmeno je veľké
MON	Prvé tri písmena z názvu mesiaca veľkými písmenami
YY	Rok (00 až 99)
YYYY	Rok (vrátane storočia, napr. 1999, alebo 1901)
RR	Rok (00 až 99)
RRRR	Rok (vrátane storočí, napr. 1999, alebo 2001)
MI	Minúta (00 až 60)
HH	Hodina (01 až 12)
HH24	Hodina (00 až 23)
SS	Sekunda (00 až 59)

Všimnite si malého, ale zásadného rozdielu medzi MM - mesiac a MI - minúta.

TO_CHAR (d [, 'x','nls_parm'])

Použité premenné

d: premenná typu DATE

x: reťazec udávajúci formát dátumu (pozri predošlú tabuľku). Nemusí byť uvedený. nls_parm: udáva jazyk, v ktorom sa majú vrátiť názvy mesiacov, dní a rokov. Parameter nemusí byť uvedený. Ako implicitná hodnota sa použije implicitný jazyk pre dáta. Parameter sa udáva v tvare 'NLS_DATE_LANGUAGE = language'

Pozri SQL-DML

Príklad:

1* SELECT TO_CHAR(SYSDATE,'DD.MM.RRRR') FROM DUAL

SQL> /

TO_CHAR(SY

----------

03.02.2003

Formát stanovený príkazom COLUMN má význam po celú dobu, čo ste v nástroji SQL* Plus.

Nadpisy - TTITLE a BTITLE

TITLE

S hlavičkou vyzerá výstup dotazu ako skutočná zostava. Existujú dve verzie príkazu TTITLE. Pomocou prvej z nich je možné na začiatok zostavy vypísať riadok textu:

TTITLE zarovnanie "Nadpis"

TTITLE [printspec [text|variable] ...] |[OFF|ON]

Použité premenné

printspec: klauzula udávajúca, kam sa má umiestniť a ako má SQL*Plus formátovať hlavičku.

Môže to byť:

· COL n

· SKIP [n]

· TAB n

· LEFT

· CENTER

· RIGHT

· BOLD

· FORMAT text

text: text záhlavia variable: jedna z nasledovných premenných SQL*Plus:

· SQL.LNO - číslo riadku

· SQL.PNO - číslo stránky

· SQL.RELASE - verzia jadra Oracle

· SQL.SQLCODE - číslo poslednej chyby

· SQL.USER - prihlásený užívateľ

OFF|ON - vypnutie a znovu zapnutie vypisovania nadpisu

Druhá možnosť tohto príkazu umožňuje zložitejšie formátovanie. Môžete formátovať časť záhlavia vľavo, vpravo a uprostred a môžete do záhlavia umiestniť a stĺpce použité v dotaze. Táto varianta sa často používa v zostavách, ktoré sú utriedené a agregované podľa jednotlivých kategórií.

Príklad:

SQL> TTITLE CENTER 'Zoznam studentov' RIGHT SQL.PNO

SQL> SELECT ou_meno meno, ou_priezvisko priezvisko FROM os_udaje;

Zoznam studentov 1

MENO

STUDENTA PRIEZVISKO

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

Karolina Velka

Marek Sartoris

Tomas Chrenka

Jana Ulicna

Emil Bobok

Zuzana Bebjakova

Zoznam studentov 2

MENO PRIEZVISKO

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

Stanislav Vnuk

Celine Dion

SQL> TTITLE OFF

SQL> /

MENO PRIEZVISKO

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

Karolina Velka

Marek Sartoris

Tomas Chrenka

Jana Ulicna

Emil Bobok

Zuzana Bebjakova

Stanislav Vnuk

MENO PRIEZVISKO

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

Celine Dion

SQL> TTITLE ON

SQL> /

... opätovné zapnutie nadpisu, výsledok je rovnaký ako prvý prípad

Dátum v nadpise

Ak chcete do záhlavia zostavy vložiť aktuálny dátum, musíte previesť nasledujúce kroky:

· Pridajte do dotazu pseudo-stĺpec SYSDATE a priraďte mu alias.

· Definujte pre tento alias príkaz COLUMN

· Definujte príkaz TTITLE, ktorý bude obsahovať alias.

Príklad:

SQL>TTITLE LEFT datum CENTER 'Zoznam studentov'

SQL>COLUMN datum NEW_VALUE datum NOPRINT

SQL>SELECT TO_CHAR(SYSDATE,'DD.MM.RRRR') datum, st_os_cislo Cislo, ou_meno, ou_priezvisko

2 FROM student, os_udaje

3 WHERE st_ou_rod_cislo = ou_rod_cislo;

03.02.2003 Zoznam studentov

CISLO OU_MENO OU_PRIEZVISKO

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

1001 Karolina Velka

1002 Marek Sartoris

1003 Tomas Chrenka

1004 Jana Ulicna

1005 Emil Bobok

1006 Zuzana Bebjakova

1007 Stanislav Vnuk

1008 Celine Dion

BTITLE

Pomocou príkazu BTITLE je možné definovať pätu stránky zostavy. Má rovnakú syntax ako príkaz TTITLE

Príklad:

SQL>TTITLE CENTER 'Zoznam studentov'

SQL>btitle center SQL.PNO

SQL>SELECT os_cislo Cislo, meno, ou_priezvisko

2 FROM student, os_udaje

3 WHERE st_ou_rod_cislo = ou_rod_cislo;

Zoznam studentov

CISLO OU_MENO OU_PRIEZVISKO

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

1001 Karolina Velka

1002 Marek Sartoris

1003 Tomas Chrenka

1004 Jana Ulicna

1005 Emil Bobok

1006 Zuzana Bebjakova

1007 Stanislav Vnuk

1008 Celine Dion

Zobrazenie nastavenia

SHOW {TTITLE|BTITLE} Nezabudnite na možnosť odložiť si výstupnú zostavu do súboru pomocou príkazu SPOOL. SPOOL {súbor[.prípona]|OFF} Ak neuvediete príponu, pridá ju SQL*Plus automaticky (zvyčajne .lis, alebo .lst). Po vykonaní príkazu SPOOL začne SQL*Plus posielať všetky výstupy aj do súboru, až po vykonanie príkazu SPOOL OFF, alebo po ukončenie SQL*Plus.

Agregačné funkcie a výstup

BREAK a COMPUTE

SQL*Plus ponúka možnosť sumarizácie skupín záznamov v zostave. Pre vytvorenie zostavy, ktorá obsahuje detaily, prerušenia a sumarizácie, je možné využiť príkazy BREAK a COMPUTE.

BREAK [ON element_zostavy [akcia]]

Príkazom určíte body prerušenia v zostave. Pokiaľ bude stĺpec uvedený v príkaze BREAK obsahovať inú hodnotu ako v predchádzajúcom zázname, prevedie SQL*Plus vami požadovanú akciu (napr. prechod na novú stranu). Vytlačí taktiež sumarizačné informácie o tejto skupine. Tieto informácie sa definujú pomocou príkazu COMPUTE. Jeho syntax je nasledovná:

funkcia: jedna z nasledujúcich funkcií: AVG, COU[NT], MAX[IMUM], MIN[IMUM], NUM[BER], STD, SUM, VAR[IANCE] text: text pre vypočítanú hodnotu výraz: funkcia, ktorá sa má pre výpočet použiť stĺpec: stĺpce, ktoré sa používajú pri výpočte alias: aliasy pre stĺpce, ktoré sa používajú pri výpočte

Príklad:

SQL> break on report

SQL> compute avg label 'Celkovy priemer ' of zp_vysledok on report

SQL>Select zp_st_os_cislo , zp_vysledok from zap_predmety

2* order by 1;

ZP_ST_OS_CISLO ZP_VYSLEDOK

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

1001 3

1001 1

1002 1

1003 2

1004 3

1004 2

1004 1

1005

1005 1

1006 2

ZP_ST_OS_CISLO ZP_VYSLEDOK

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

1007 3

1007 2

-----------

Celkovy prieme 1.83333333

Príklad:

SQL>BREAK ON zp_st_os_cislo SKIP 2

SQL>COMPUTE avg LABEL 'Priemer ' OF zp_vysledok ON zp_st_os_cislo

SQL>Select zp_st_os_cislo , zp_vysledok from zap_predmety

2* order by 1;

ZP_ST_OS_CISLO ZP_VYSLEDOK

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

1001 3

************** -----------

Priemer 2

1002 1

************** -----------

Priemer 1

ZP_ST_OS_CISLO ZP_VYSLEDOK

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

1003 2

************** -----------

Priemer 2

1004 3

************** -----------

Priemer 2

ZP_ST_OS_CISLO ZP_VYSLEDOK

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

1005

************** -----------

Priemer 1

1006 2

************** -----------

Priemer 2

ZP_ST_OS_CISLO ZP_VYSLEDOK

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

1007 3

************** -----------

Priemer 2.5

Užitočné príkazy SQL*Plus

SPOOL

Nezabudnite na možnosť odložiť si výstupnú zostavu do súboru pomocou príkazu SPOOL.

SPOOL {súbor[.prípona]|OFF}

Ak neuvediete príponu, pridá ju SQL*Plus automaticky (zvyčajne .lis, alebo .lst). Po vykonaní príkazu SPOOL začne SQL*Plus posielať všetky výstupy aj do súboru, až po vykonanie príkazu SPOOL OFF, alebo po ukončenie SQL*Plus.

SET

Keď vytvárame zostavu, obvykle nechcete, aby obsahovala dodatočné informácie. Tieto informácie môžete v SQL*Plus potlačiť pomocou nasledujúcich príkazov:

· SET FEEDBACK OFF.

Potlačí zobrazovanie informácií o počte riadkov na konci dotazu

· SET VERIFY OFF.

Potlačí akýkoľvek výstup na terminál. Príkaz je užitočný, keď pomocou príkazu SPOOL vytvárate zostavu do súboru. Výstup bude potlačené len pri prevádzaní príkazov uložených v súbore - výstup interektívne zadávaných príkazov toto nastavenie neovplyvní.

· SET ECHO OFF.

Potlačí zobrazenie príkazu SQL pred jeho prevedením. Pokiaľ chcete príkaz pred prevedením zobraziť, zadajte SET ECHO ON (opäť platí len pre príkazy uložené v súbore).

RECSEP, RECSEPCHAR, colsep a UNDERLINE

RECSEP je skratka pre "record separator" - oddeľovač záznamov. Nastavenie tohto prametra hovorí SQL*Plus, čo má robiť medzi dvoma záznamami v zostave. Parameter RECSEPCHAR udáva znak, ktorý SQL*Plus opakovane tlačí na riadok medzi dva záznamy v zostave.

Počiatočné nastavenie pre RECSEP je WRAP a prázdny znak pre RECSEPCHAR. Pri tomto nastavení sa medzi záznamy tlačí prázdny riadok, ale len vtedy, keď aspoň v jednom stĺpci prvého z nich došlo k zalomeniu textu do ďalšieho riadku.

COLSEP je skratka pre "column separator" - oddeľovač stĺpcov. Nastavenie tohto prametra hovorí SQL*Plus, aký znak má byť použitý ako oddeľovač stĺpcov. Implicitne je to prázdny znak.

UNDERLINE slúži na podčiarknutie hlavičky. Implicitne je tento parameter nastavený na ‘-‘. Je možné ho nastaviť na zapnuté, alebo vypnuté.

SET RECSEP {WR[APPED]|EA[ACH]|OFF}

SET RECSEPCHAR {''|znak}

SET COLSEP {''|znak}

SET und[erline] {''|znak| ON | OFF}

Príklad:

SQL> set linesize 31

SQL> set recsep each

SQL> set colsep |

SQL> set underline *

SQL> select ou_meno Meno, ou_priezvisko Priezvisko from os_udaje;

MENO |PRIEZVISKO

***************|***************

Karolina |Velka

_______________________________

Marek |Sartoris

_______________________________

Tomas |Chrenka

_______________________________

Jana |Ulicna

_______________________________

Emil |Bobok

_______________________________

Zuzana |Bebjakova

_______________________________

Stanislav |Vnuk

_______________________________

Celine |Dion

_______________________________

8 rows selected.

Premenné a parametre

DEFINE

Vďaka premenným môžete vytvárať rýchlo sa prispôsobiteľné dotazy. Ukážeme si ako definovať premenné a ako do SQL*Plus predávať parametre.

Premenné sa používajú, aby ste mohli pomocou jedného dotazu získať rôzne výstupy. Premennú môžete v dotaze umiestniť všade, kde môžete napísať stĺpec, alebo výraz. Ak potrebujete z dotazu získať záznamy pre rôzne zadania, umiestnite premennú do klauzule WHERE.

Premenné sa definujú príkazom DEFINE:

DEFINE [premenná]|[premenná = text]

Ďalej je možné premenné definovať aj jednoduchým odkazom na ňu (pred menom premennej sa uvedie znak '&' v dotaze)

Ak SQL*Plus narazí na premennú, ktorej dosiaľ nebola priradená hodnota, vyzve vás, aby ste ju zadali.

Počas vykonávania dotazu vyzve Oracle8 užívateľa, aby definoval premennú ..... V našom príklade užívateľ zadal ...., a potom stlačil Enter. Oracle8 zobrazí informáciu o tom, ako premennú použil a vypíše výsledok dotazu.

Ak sa v dotaze potrebujete na rovnakú premennú odkázať viac ako raz, napíšte namiesto jedného znaku '&' dva (&&). Tým poviete SQL*Plus, aby vás vyzval k zadaniu hodnoty premennej len raz, a potom už používal vami zadané hodnoty. Pri vykonaní nasledujúceho dotazu budete napríklad k zadaniu hodnoty premennej .... vyzvaný len raz:

Príklad:

SQL> select ou_meno, ou_priezvisko

2 from os_udaje

3 where ou_meno LIKE '&meno';

Enter value for meno: K%

old 3: where ou_meno LIKE '&meno'

new 3: where ou_meno LIKE 'K%'

OU_MENO OU_PRIEZVISKO

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

Karolina Velka

Príklad:

SQL> define priezvisko = 'B%'

SQL> select ou_meno, ou_priezvisko

2 from os_udaje

3 where ou_priezvisko LIKE '&priezvisko';

old 3: where ou_priezvisko LIKE '&priezvisko'

new 3: where ou_priezvisko LIKE 'B%'

OU_MENO OU_PRIEZVISKO

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

Emil Bobok

Zuzana Bebjakova

SQL> define priezvisko = 'V%'

SQL> /

old 3: where ou_priezvisko LIKE '&priezvisko'

new 3: where ou_priezvisko LIKE 'V%'

OU_MENO OU_PRIEZVISKO

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

Karolina Velka

Stanislav Vnuk

Príklad:

SQL> select ou_meno, ou_priezvisko

2 from os_udaje

3 where ou_meno LIKE '&prem'

4 OR ou_priezvisko LIKE '&prem';

Enter value for prem: S%

old 3: where ou_meno LIKE '&prem'

new 3: where ou_meno LIKE 'S%'

Enter value for prem: S%

old 4: OR ou_priezvisko LIKE '&prem'

new 4: OR ou_priezvisko LIKE 'S%'

OU_MENO OU_PRIEZVISKO

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

Marek Sartoris

Stanislav Vnuk

Príklad:

SQL> select ou_meno, ou_priezvisko

2 from os_udaje

3 where ou_meno LIKE '&&prem'

4 OR ou_priezvisko like '&&prem';

Enter value for prem: S%

old 3: where ou_meno LIKE '&&prem'

new 3: where ou_meno LIKE 'S%'

old 5: like '&&prem'

new 5: like 'S%'

OU_MENO OU_PRIEZVISKO

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

Marek Sartoris

Stanislav Vnuk

Spúšťanie dávok

Dotaz uložený v súbore môžeme spustiť z príkazového riadku pomocou parametra @ meno_suboru alebo pomocou príkazu SQL*Plus START meno_suboru. Z operačného systému je možné spustiť script nasledovne:

sqlplus user/password @meno_suboru

Príklad:

Majme v súbore najdi.sql uložený SQL príkaz z predchádzajúceho príkadu' a za dotaz bol napísaný príkaz pre jeho spustenie - znak ';', alebo '/'. Teda jeho obsah je:

select ou_meno, ou_priezvisko

from os_udaje

where ou_meno LIKE '&&prem'

OR ou_priezvisko like '&&prem';

Potom príkaz na jeho spustenie z operačného systému je nasledujúci:

[vajsova@frdb2 vajsova]$ sqlplus vajsova @najdi

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Feb 7 14:07:58 2003

Enter password:

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production

With the Partitioning option

JServer Release 8.1.7.0.1 - Production

Input truncated to 31 characters

Enter value for prem: S%

old 3: where ou_meno LIKE '&&prem'

new 3: where ou_meno LIKE 'S%'

old 4: OR ou_priezvisko LIKE '&&prem'

new 4: OR ou_priezvisko LIKE 'S%'

OU_MENO OU_PRIEZVISKO

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

Marek Sartoris

Stanislav Vnuk

Operačný systém teda zavedie SQL*Plus, ktorý spustí zadaný skript. SQL*Plus a vyzve vás k zadaniu hodnoty pre premennú prem.

Z príkazového riadku operačného systému môžete zavolať SQL*Plus a spustiť dotaz (alebo iný príkaz SQL) a môžete mu taktiež pomocou parametrov predať hodnoty. Hodnoty zadané na príkazovom riadku sú v SQL*Plus dostupné pomocou preddefinovanej sady parametrov označených číslicami, ktoré zodpovedajú poradiu parametrov na príkazovom riadku.

Máme rovnaký dotaz ako v predchádzajúcom príklade, ale meno premennej zmeníme na &1:

Príklad:

Majme ten istý SQL príkaz v súbore najdi.sql, len namiesto pomenovania premennej, pomenujeme ju číslom 1. Teda obsah tohto súboru je:

select ou_meno, ou_priezvisko

from os_udaje

where ou_meno LIKE '&&1'

OR ou_priezvisko like '&&1';

Potom príkaz na jeho spustenie z operačného systému so zadaním hodnoty parametra je nasledujúci:

[vajsova@frdb2 vajsova]$ sqlplus vajsova @najdi S%

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Feb 7 14:20:17 2003

Enter password:

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production

With the Partitioning option

JServer Release 8.1.7.0.1 - Production

Input truncated to 28 characters

old 3: where ou_meno LIKE '&&1'

new 3: where ou_meno LIKE 'S%'

old 4: OR ou_priezvisko LIKE '&&1'

new 4: OR ou_priezvisko LIKE 'S%'

OU_MENO OU_PRIEZVISKO

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

Marek Sartoris

Stanislav Vnuk

Operačný systém zavolá SQL*Plus spustí skript, vezme reťazec S% a nahradí ním v dotaze premennú &&1.