Export oraz import danych z baz Oracle

Projekt: utworzenie bazy danych, oraz przygotowanie zestawu komend eksportujących oraz importujących dane z tabel i do tabel.

1. Wyświetla wszystkich pracowników pracujących w poszczególnych miastach w poszczególnych kinach.

Export danych z tabeli:

SELECT XMLELEMENT("MIASTO", 
  xmlattributes(miasto as "nazwa"),
  XMLAGG(xmlforest(imie,nazwisko,email,numer_telefonu,wynagrodzenie))) dane_xml
from pracownicy p 
join kina k 
on (p.id_kino=k.id_kino)
group by miasto;

Plik XML:

<pracownicy>
<MIASTO nazwa="Bydgoszcz">
 <IMIE>David</IMIE><NAZWISKO>Austin</NAZWISKO><EMAIL>DAUSTIN@wp.pl</EMAIL>
 <NUMER_TELEFONU>590-423-4569</NUMER_TELEFONU><WYNAGRODZENIE>4800.5</WYNAGRODZENIE>
 <IMIE>Steven</IMIE><NAZWISKO>King</NAZWISKO><EMAIL>SKING@wp.pl</EMAIL>
 <NUMER_TELEFONU>515-123-4567</NUMER_TELEFONU><WYNAGRODZENIE>24001.5</WYNAGRODZENIE>
 <IMIE>Neena</IMIE><NAZWISKO>Kochhar</NAZWISKO><EMAIL>NKOCHHAR@wp.pl</EMAIL>
 <NUMER_TELEFONU>515-123-4568</NUMER_TELEFONU><WYNAGRODZENIE>17000.5</WYNAGRODZENIE>
</MIASTO>
<MIASTO nazwa="Gdansk">
 <IMIE>Bruce</IMIE><NAZWISKO>Ernst</NAZWISKO><EMAIL>BERNST@wp.pl</EMAIL>
 <NUMER_TELEFONU>590-423-4568</NUMER_TELEFONU><WYNAGRODZENIE>6000.5</WYNAGRODZENIE>
</MIASTO>
<MIASTO nazwa="Katowice">
 <IMIE>Alexander</IMIE><NAZWISKO>Hunold</NAZWISKO><EMAIL>AHUNOLD@wp.pl</EMAIL>
 <NUMER_TELEFONU>590-423-4567</NUMER_TELEFONU><WYNAGRODZENIE>9000.5</WYNAGRODZENIE>
</MIASTO>
<MIASTO nazwa="Szczecin">
 <IMIE>Valli</IMIE><NAZWISKO>Pataballa</NAZWISKO><EMAIL>VPATABAL@wp.pl</EMAIL>
 <NUMER_TELEFONU>590-423-4560</NUMER_TELEFONU><WYNAGRODZENIE>4800.5</WYNAGRODZENIE>
</MIASTO>
<MIASTO nazwa="Warszawa">
 <IMIE>Lex</IMIE><NAZWISKO>De Haan</NAZWISKO><EMAIL>LDEHAAN@wp.pl</EMAIL>
 <NUMER_TELEFONU>515-123-4569</NUMER_TELEFONU><WYNAGRODZENIE>17001.5</WYNAGRODZENIE>
</MIASTO>
</pracownicy>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."PRACOWNICY_W_MIASTACH" 
( "MIASTO" VARCHAR2(20 BYTE), 
"IMIE" VARCHAR2(20 BYTE), 
"NAZWISKO" VARCHAR2(20 BYTE), 
"EMAIL" VARCHAR2(30 BYTE), 
"NUMER_TELEFONU" VARCHAR2(20 BYTE), 
"WYNAGRODZENIE" NUMBER(6,0)
) SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

Import danych:

DECLARE
 pliczek xmltype := xmltype( bfilename('PLIKI_XML','1.xml'), nls_charset_id('AL32UTF8') );
liczba number:=0;
BEGIN
        FOR E IN (SELECT
        EXTRACTVALUE(VALUE(X), '/IMIE/text()') AS aa
        FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//pracownicy/MIASTO/IMIE'))) X)
        loop
        liczba:=liczba+1;
                 FOR R IN (SELECT 
                 EXTRACTVALUE(VALUE(Z), '/MIASTO/@nazwa') AS miasto,
				 EXTRACTVALUE(VALUE(Z), '/MIASTO/IMIE[position()='||liczba||']') AS imie,
         EXTRACTVALUE(VALUE(Z), '/MIASTO/NAZWISKO[position()='||liczba||']') AS nazwisko,
         EXTRACTVALUE(VALUE(Z), '/MIASTO/EMAIL[position()='||liczba||']') AS email,
         EXTRACTVALUE(VALUE(Z), '/MIASTO/NUMER_TELEFONU[position()='||liczba||']') AS telefon,
         EXTRACTVALUE(VALUE(Z), '/MIASTO/WYNAGRODZENIE[position()='||liczba||']') AS wynagrodzenie
        FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//pracownicy/MIASTO'))) Z)
        LOOP
          if R.imie is not null then
          insert into pracownicy_w_miastach (miasto,imie, nazwisko,email,numer_telefonu,wynagrodzenie) values (R.miasto,R.imie, R.nazwisko,R.email,R.telefon,R.wynagrodzenie);
          end if;
          END LOOP;
END LOOP;
END;

2. Wyświetla sprzedane bilety (na 1000 dni przed wykonaniem exportu) w danym mieście, pokazuje zysk jak i datę zakupu.

Zmienne:
– możliwe ustawienie ilość dni wstecz do wykonania raportu.

Export danych z tabeli:

SELECT XMLELEMENT("MIASTO",xmlattributes(miasto as "nazwa"),
 xmlagg(xmlconcat(xmlelement("TYTUL", f.tytul),
 xmlelement("DATA_SPRZEDAZY", e.data_sprzedazy),
 xmlelement("ZYSK", e.zysk)
 ))) dane_xml
 from ewidencja e
 join kina k on (e.id_kino=k.id_kino)
 join filmy f on (e.film_id=f.film_id)
 where ((sysdate - data_sprzedazy)<1000)
 group by k.miasto;

Plik XML:

<bilety>
<MIASTO nazwa="Bydgoszcz">
	<TYTUL>Matrix</TYTUL><DATA_SPRZEDAZY>2016-06-17</DATA_SPRZEDAZY><ZYSK>12</ZYSK>
	<TYTUL>Avatar</TYTUL><DATA_SPRZEDAZY>2016-09-21</DATA_SPRZEDAZY><ZYSK>25</ZYSK>
	<TYTUL>Avatar</TYTUL><DATA_SPRZEDAZY>2015-06-25</DATA_SPRZEDAZY><ZYSK>10</ZYSK>
	<TYTUL>Straszny Film</TYTUL><DATA_SPRZEDAZY>2016-12-05</DATA_SPRZEDAZY><ZYSK>10</ZYSK>
</MIASTO>
<MIASTO nazwa="Gdansk">
	<TYTUL>Assasins Creed</TYTUL><DATA_SPRZEDAZY>2014-05-21</DATA_SPRZEDAZY><ZYSK>12</ZYSK>
</MIASTO>
<MIASTO nazwa="Lodz">
	<TYTUL>Szklana Pulapka</TYTUL><DATA_SPRZEDAZY>2016-01-13</DATA_SPRZEDAZY><ZYSK>25</ZYSK>
</MIASTO>
<MIASTO nazwa="Warszawa">
	<TYTUL>Assasins Creed</TYTUL><DATA_SPRZEDAZY>2016-01-03</DATA_SPRZEDAZY><ZYSK>12</ZYSK>
</MIASTO>
</bilety>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."SPRZEDANE_BILETY"
 ( "MIASTO" VARCHAR2(20 BYTE),
 "TYTUL" VARCHAR2(20 BYTE),
 "DATA_SPRZEDAZY" VARCHAR2(20 BYTE),
 "ZYSK" NUMBER
 ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ;

Import danych:

DECLARE
pliczek xmltype := xmltype( bfilename('PLIKI_XML','2.xml'), nls_charset_id('AL32UTF8') );
liczba number:=0;
BEGIN
FOR E IN (SELECT
EXTRACTVALUE(VALUE(X), '/MIASTO/@nazwa') AS miastok
FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//bilety/MIASTO/TYTUL'))) X)
loop
 liczba:=liczba+1;
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/MIASTO/@nazwa') AS miasto,
 EXTRACTVALUE(VALUE(Z), '/MIASTO/TYTUL[position()='||liczba||']') AS tytul,
 EXTRACTVALUE(VALUE(Z), '/MIASTO/DATA_SPRZEDAZY[position()='||liczba||']') AS datas,
 EXTRACTVALUE(VALUE(Z), '/MIASTO/ZYSK[position()='||liczba||']') AS zysk
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//bilety/MIASTO'))) Z)
 LOOP
if R.tytul is not null then
 insert into sprzedane_bilety (miasto,tytul,data_sprzedazy,zysk) values (R.miasto,R.tytul,R.datas,R.zysk);
 end if;
 END LOOP;
 END LOOP;
 END;

3. Wyświetla pracowników ze stażem większym niż "liczba" oraz wynagrodzeniem większym niż "wynagrodzenie".

Zmienne:
– liczba dni określająca ilość dni zatrudnienia,
– wynagrodzenie określające wysokość wynagrodzenia pracowników.

Export danych z tabeli:

SELECT XMLELEMENT("Pracownik",
 xmlattributes(pracownik_id as "ID"),
 xmlelement("Imie",imie ),
 xmlelement("nazwisko",nazwisko ),
 xmlelement("zatrudniony",data_zatrudnienia )
 ) dane_xml
 from pracownicy p where ((sysdate - data_zatrudnienia)<10000) and wynagrodzenie > 10000;

Plik XML:

<pracownicy>
<Pracownik ID="2">
 <Imie>Neena</Imie><nazwisko>Kochhar</nazwisko><zatrudniony>1989-09-21</zatrudniony>
 </Pracownik>
 <Pracownik ID="3">
 <Imie>Lex</Imie><nazwisko>De Haan</nazwisko><zatrudniony>1993-01-13</zatrudniony>
 </Pracownik>
 </pracownicy>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."PRACOWNICY_ZE_STAZEM"
 ( "ID" NUMBER,
 "IMIE" VARCHAR2(20 BYTE),
 "NAZWISKO" VARCHAR2(20 BYTE),
 "ZATRUDNIONY" VARCHAR2(20 BYTE)
 ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ;

Import danych:

DECLARE
 pliczek xmltype := xmltype( bfilename('PLIKI_XML','3.xml'), nls_charset_id('AL32UTF8') );
 BEGIN
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Pracownik/@ID') AS ids,
 EXTRACTVALUE(VALUE(Z), '/Pracownik/Imie/text()') AS imie,
 EXTRACTVALUE(VALUE(Z), '/Pracownik/zatrudniony/text()') AS zatrudniony,
 EXTRACTVALUE(VALUE(Z), '/Pracownik/nazwisko/text()') AS nazwisko
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//pracownicy/Pracownik'))) Z)
 LOOP
 insert into pracownicy_ze_stazem (ID,imie, nazwisko,zatrudniony) values (R.ids,R.imie, R.nazwisko,R.zatrudniony);
 END LOOP;
 END;

4. Wypisuje listę faktur, grupując osobami na które zostały wystawione, podaje także datę wystawienia oraz kwotę.

Export danych z tabeli:

SELECT XMLELEMENT("Faktura",xmlattributes(k.nazwisko as "nazwisko", k.karta_id as "id"),
 XMLAGG( xmlconcat(XMLELEMENT("kwota", kwota),
 XMLELEMENT("data_wystawienia", data_wystawienia)
 ))) dane_xml
 from faktury f
 join karty_stalego_klienta k on (f.karta_id=k.karta_id)
 group by k.nazwisko, k.karta_id;

Plik XML:

<Ewidencje>
 <Faktura nazwisko="King" id="1">
 <kwota>300</kwota><data_wystawienia>2016-02-05</data_wystawienia>
 <kwota>3030</kwota><data_wystawienia>2016-06-17</data_wystawienia>
 <kwota>3010</kwota><data_wystawienia>2016-09-21</data_wystawienia>
 </Faktura>
 <Faktura nazwisko="De Haan" id="3">
 <kwota>300</kwota><data_wystawienia>2014-05-21</data_wystawienia>
 </Faktura>
 <Faktura nazwisko="Kochhar" id="2">
 <kwota>100</kwota><data_wystawienia>2015-06-25</data_wystawienia>
</Faktura>
 </Ewidencje>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."LISTA_FAKTUR"
( "NAZWISKO" VARCHAR2(20 BYTE),
 "ID" VARCHAR2(20 BYTE),
 "KWOTA" VARCHAR2(20 BYTE),
 "DATA_WYSTAWIENIA" VARCHAR2(20 BYTE)
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "USERS" ;

Import danych:

DECLARE
pliczek xmltype := xmltype( bfilename('PLIKI_XML','4.xml'), nls_charset_id('AL32UTF8') );
 liczba number:=0;
 BEGIN
 FOR E IN (SELECT
 EXTRACTVALUE(VALUE(X), '/Faktura/@nazwisko') AS miastok
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Ewidencje/Faktura/kwota'))) X)
loop
 liczba:=liczba+1;
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Faktura/@nazwisko') AS nazwisko,
 EXTRACTVALUE(VALUE(Z), '/Faktura/@id') AS ids,
 EXTRACTVALUE(VALUE(Z), '/Faktura/data_wystawienia[position()='||liczba||']') AS data_wystawienia,
 EXTRACTVALUE(VALUE(Z), '/Faktura/kwota[position()='||liczba||']') AS kwota
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Ewidencje/Faktura'))) Z)
 LOOP
if R.kwota is not null then
 insert into lista_faktur (nazwisko,id,kwota,data_wystawienia) values (R.nazwisko,R.ids,R.kwota,R.data_wystawienia);
 end if;
 END LOOP;
 END LOOP;
 END;

5. Select wyświetlający wszystkie transakcje zawarte w danym kinie, zarówno te z kartą stałego klienta jak i bez niej przez wyznaczonego pracownika.

Zmienne
– numer pracownika.

Export danych z tabeli:

SELECT XMLELEMENT("Ewidencja",
XMLATTRIBUTES(e.ewidencja_id, e.typ_biletu),
XMLELEMENT("Paragon", XMLATTRIBUTES(e.pracownik_id,(SELECT d.nazwisko FROM pracownicy d WHERE d.pracownik_id = e.pracownik_id) as "Nazwisko"),
XMLELEMENT("Miasto",(SELECT d.miasto FROM kina d WHERE d.id_kino = e.id_kino)) as "Kino"),
XMLELEMENT("Zysk", e.zysk),
XMLELEMENT("Data", e.data_sprzedazy)) AS "Element"
 FROM ewidencja e
 WHERE pracownik_id = 1;

Plik XML:

<Ewidencje>
<Ewidencja EWIDENCJA_ID="5" TYP_BILETU="Ulgowy">
 <Paragon PRACOWNIK_ID="1" Nazwisko="King">
 <Miasto>Gdansk</Miasto>
 </Paragon>
 <Zysk>12</Zysk><Data>2014-05-21</Data>
 </Ewidencja>
 <Ewidencja EWIDENCJA_ID="7" TYP_BILETU="Inny"><Paragon PRACOWNIK_ID="1" Nazwisko="King"><Miasto>Bydgoszcz</Miasto></Paragon><Zysk>10</Zysk><Data>2016-12-05</Data></Ewidencja>
 </Ewidencje>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."EWIDENCJA_PRAC_KINO"
( "EWIDENCJA_ID" NUMBER,
"TYP_BILETU" VARCHAR2(20 BYTE),
 "PRACOWNIK_ID" NUMBER,
 "NAZWISKO" VARCHAR2(20 BYTE),
 "MIASTO" VARCHAR2(20 BYTE),
 "ZYSK" NUMBER,
 "DATA_WYD" VARCHAR2(20 BYTE)
 ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ;

Import danych:

DECLARE
 pliczek xmltype := xmltype( bfilename('PLIKI_XML','5.xml'), nls_charset_id('AL32UTF8') );
 BEGIN
FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/@EWIDENCJA_ID') AS ewi_id,
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/@TYP_BILETU') AS typ,
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/Data/text()') AS data_wyd,
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/Zysk/text()') AS zysk,
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/Paragon/Miasto/text()') AS miasto,
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/Data/text()') AS nazwisko,
 EXTRACTVALUE(VALUE(Z), '/Ewidencja/Paragon/@PRACOWNIK_ID') AS pra_id
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Ewidencje/Ewidencja'))) Z)
 LOOP
 insert into ewidencja_prac_kino (EWIDENCJA_ID,TYP_BILETU, PRACOWNIK_ID,NAZWISKO,MIASTO,ZYSK,DATA_WYD) values (R.ewi_id,R.typ, R.pra_id,R.nazwisko,R.miasto,R.zysk,R.data_wyd);
 END LOOP;
 END;

6. Select wyświetlający osoby posiadające kartę stałego klienta oraz zarejestrowaną firmę na nią. (oraz są aktywne)

Export danych z tabeli:

SELECT XMLELEMENT("Firmowe",
 XMLATTRIBUTES(k.karta_id as "ID"),
 XMLFOREST(k.imie,k.nazwisko),
 XMLELEMENT("Data",k.data_utworzenia),
 XMLELEMENT("NIP", k.nip)) AS "Stali klienci"
 FROM karty_stalego_klienta k
 WHERE k.nip is not null and k.konto_zaparkowane = 0 order by k.nazwisko;

Plik XML:

<Karty>
 <Firmowe ID="3"><IMIE>Adam</IMIE><NAZWISKO>De Haan</NAZWISKO><Data>2016-01-03</Data><NIP>54326</NIP></Firmowe>
 <Firmowe ID="1"><IMIE>Jan</IMIE><NAZWISKO>King</NAZWISKO><Data>2016-06-17</Data><NIP>3123</NIP></Firmowe>
 <Firmowe ID="2"><IMIE>Jan</IMIE><NAZWISKO>Kochhar</NAZWISKO><Data>2016-09-21</Data><NIP>45323</NIP></Firmowe>
 </Karty>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."STALI_KLIENCI"
 ( "ID" NUMBER,
 "IMIE" VARCHAR2(20 BYTE),
 "NAZWISKO" VARCHAR2(20 BYTE),
 "DATA_UTWORZENIA" VARCHAR2(20 BYTE),
 "NIP" VARCHAR2(20 BYTE)
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "USERS" ;

Import danych:

DECLARE
 pliczek xmltype := xmltype( bfilename('PLIKI_XML','6.xml'), nls_charset_id('AL32UTF8') );
BEGIN
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Firmowe/@ID') AS ids,
 EXTRACTVALUE(VALUE(Z), '/Firmowe/IMIE/text()') AS imie,
 EXTRACTVALUE(VALUE(Z), '/Firmowe/NAZWISKO/text()') AS nazwisko,
 EXTRACTVALUE(VALUE(Z), '/Firmowe/NIP/text()') AS nip,
 EXTRACTVALUE(VALUE(Z), '/Firmowe/Data/text()') AS datas
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Karty/Firmowe'))) Z)
 LOOP
 insert into stali_klienci (ID,IMIE, NAZWISKO,NIP,DATA_UTWORZENIA) values (R.ids,R.imie, R.nazwisko,R.nip,R.datas);
 END LOOP;
 END;

7. Select wyświetlający zyski z jednego miesiąca w każdym z zarejestrowanych lat.

Zmienna:
– numer miesiąca.

Export danych z tabeli:

SELECT XMLELEMENT("Archiwum",
XMLATTRIBUTES(k.rok as "Rok"),
xmlelement("Miesiac",k.miesiac),
xmlelement("Normalne",k.normalne),
xmlelement("Ulgowe",k.ulgowe),
xmlelement("Pozostale",k.pozostale),
xmlelement("Niepodliczone",k.pozostale),
XMLELEMENT("Bilans",k.bilans)) AS "Podliczenie"
 FROM archiwum_ewidencji k
 where k.miesiac = 1
 order by k.rok;

Plik XML:

<Archiwizacja>
 <Archiwum Rok="2012"><Miesiac>1</Miesiac><Normalne>0</Normalne><Ulgowe>0</Ulgowe><Pozostale>0</Pozostale><Niepodliczone>0</Niepodliczone><Bilans>0</Bilans></Archiwum>
 <Archiwum Rok="2013"><Miesiac>1</Miesiac><Normalne>0</Normalne><Ulgowe>0</Ulgowe><Pozostale>0</Pozostale><Niepodliczone>0</Niepodliczone><Bilans>0</Bilans></Archiwum>
 <Archiwum Rok="2014"><Miesiac>1</Miesiac><Normalne>0</Normalne><Ulgowe>0</Ulgowe><Pozostale>0</Pozostale><Niepodliczone>0</Niepodliczone><Bilans>0</Bilans></Archiwum>
 <Archiwum Rok="2015"><Miesiac>1</Miesiac><Normalne>0</Normalne><Ulgowe>0</Ulgowe><Pozostale>0</Pozostale><Niepodliczone>0</Niepodliczone><Bilans>0</Bilans></Archiwum>
 <Archiwum Rok="2016"><Miesiac>1</Miesiac><Normalne>1</Normalne><Ulgowe>1</Ulgowe><Pozostale>0</Pozostale><Niepodliczone>0</Niepodliczone><Bilans>37</Bilans></Archiwum>
 <Archiwum Rok="2017"><Miesiac>1</Miesiac><Normalne>0</Normalne><Ulgowe>0</Ulgowe><Pozostale>0</Pozostale><Niepodliczone>0</Niepodliczone><Bilans>0</Bilans></Archiwum>
 </Archiwizacja>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."PODLICZENIE"
( "ROK" NUMBER,
"MIESIAC" NUMBER,
"BILANS" NUMBER,
"ULGOWE" NUMBER,
 "NORMALNE" NUMBER,
 "POZOSTALE" NUMBER
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "USERS" ;

Import danych:

DECLARE
pliczek xmltype := xmltype( bfilename('PLIKI_XML','7.xml'), nls_charset_id('AL32UTF8') );
 BEGIN
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Archiwum/@Rok') AS rok,
 EXTRACTVALUE(VALUE(Z), '/Archiwum/Ulgowe/text()') AS ulgowe,
 EXTRACTVALUE(VALUE(Z), '/Archiwum/Normalne/text()') AS normalne,
 EXTRACTVALUE(VALUE(Z), '/Archiwum/Pozostale/text()') AS pozostale,
EXTRACTVALUE(VALUE(Z), '/Archiwum/Miesiac/text()') AS miesiac,
 EXTRACTVALUE(VALUE(Z), '/Archiwum/Bilans/text()') AS bilans
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Archiwizacja/Archiwum'))) Z)
 LOOP
 insert into podliczenie (rok,miesiac, bilans,ulgowe,normalne,pozostale) values (R.rok,R.miesiac, R.bilans,R.ulgowe,R.normalne, R.pozostale);
 END LOOP;
 END;

8. Select wyświetlający oglądalność filmu. (popyt na dany film).

Export danych z tabeli:

SELECT XMLELEMENT("Ogladalnosc",
XMLATTRIBUTES(k.film_id as "ID"),
 xmlelement("Tytul",k.tytul),
XMLELEMENT("Status",k.status),
 xmlelement("Sprzedanych_biletow",(SELECT count(*) from ewidencja e where k.film_id = e.film_id))) AS "Ogladalnosc"
 FROM filmy k
 order by k.tytul;

Plik XML:

<Popyt>
<Ogladalnosc ID="1"><Tytul>Assasins Creed</Tytul><Status>Premiera</Status><Sprzedanych_biletow>2</Sprzedanych_biletow></Ogladalnosc>
<Ogladalnosc ID="4"><Tytul>Avatar</Tytul><Status>Normalny</Status><Sprzedanych_biletow>2</Sprzedanych_biletow></Ogladalnosc>
<Ogladalnosc ID="3"><Tytul>Matrix</Tytul><Status>Premiera</Status><Sprzedanych_biletow>1</Sprzedanych_biletow></Ogladalnosc>
 <Ogladalnosc ID="7"><Tytul>Resident Evil</Tytul><Status>Premiera</Status><Sprzedanych_biletow>0</Sprzedanych_biletow></Ogladalnosc>
 <Ogladalnosc ID="6"><Tytul>Ring</Tytul><Status>Premiera</Status><Sprzedanych_biletow>0</Sprzedanych_biletow></Ogladalnosc>
 <Ogladalnosc ID="5"><Tytul>Straszny Film</Tytul><Status>Premiera</Status><Sprzedanych_biletow>1</Sprzedanych_biletow></Ogladalnosc>
 <Ogladalnosc ID="2"><Tytul>Szklana Pulapka</Tytul><Status>Premiera</Status><Sprzedanych_biletow>1</Sprzedanych_biletow></Ogladalnosc>
 </Popyt>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."POPYT"
 ( "ID" NUMBER,
"TYTUL" VARCHAR2(20 BYTE),
 "STATUS" VARCHAR2(20 BYTE),
 "SPRZEDAZ" NUMBER
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "USERS" ;

Import danych:

DECLARE
pliczek xmltype := xmltype( bfilename('PLIKI_XML','8.xml'), nls_charset_id('AL32UTF8') );
BEGIN
FOR R IN (SELECT
EXTRACTVALUE(VALUE(Z), '/Ogladalnosc/@ID') AS ids,
EXTRACTVALUE(VALUE(Z), '/Ogladalnosc/Status/text()') AS status,
EXTRACTVALUE(VALUE(Z), '/Ogladalnosc/Tytul/text()') AS tytul,
EXTRACTVALUE(VALUE(Z), '/Ogladalnosc/Sprzedanych_biletow/text()') AS sprzedaz
FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Popyt/Ogladalnosc'))) Z)
LOOP
insert into popyt (id,status, tytul,sprzedaz) values (R.ids,R.status, R.tytul,R.sprzedaz);
END LOOP;
 END;

9. Select wyświetlający ilość błędnych transakcji w bazie, pogrupowane po pracowniku który daną transakcję wprowadził do bazy.

Export danych z tabeli:

SELECT XMLELEMENT("Korekty",
 xmlagg(xmlelement("Check",XMLATTRIBUTES(k.pracownik_id as "ID"),
 xmlelement("Bledy",(SELECT count(*) from ewidencja e where ((e.czy_karta = 0 and e.id_karta is not null) or (e.czy_karta = 1 and e.id_karta is null)) and e.pracownik_id = k.pracownik_id))))) AS "Ilosc bledow"
 FROM ewidencja k
 group by k.pracownik_id
 order by k.pracownik_id;

Plik XML:

<Sprawdzenie>
 <Korekty><Check ID="1"><Bledy>2</Bledy></Check><Check ID="1"><Bledy>2</Bledy></Check></Korekty>
 <Korekty><Check ID="2"><Bledy>1</Bledy></Check></Korekty>
 <Korekty><Check ID="3"><Bledy>0</Bledy></Check></Korekty>
 </Sprawdzenie>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."BLEDY"
( "PRACOWNIK_ID" NUMBER,
 "ILOSC" NUMBER
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "USERS" ;

Import danych:

DECLARE
 pliczek xmltype := xmltype( bfilename('PLIKI_XML','9.xml'), nls_charset_id('AL32UTF8') );
 prac number :=0;
 BEGIN
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Check/@ID') AS pracownik_id,
 EXTRACTVALUE(VALUE(Z), '/Check/Bledy/text()') AS ilosc
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Sprawdzenie/Korekty/Check'))) Z)
 LOOP
if prac <> R.pracownik_id then
 insert into bledy (pracownik_id,ilosc) values (R.pracownik_id,R.ilosc);
 end if;
 prac:=R.pracownik_id;
 END LOOP;
 END;

10. Select wyświetlający przewidywany zysk z dostępnych produktów.

Export danych z tabeli:

SELECT XMLELEMENT("Produkt", xmlattributes(produkt_id as "ID"),
 xmlelement("Nazwa",nazwa),
 xmlelement("Koszt",koszt),
 xmlelement("Zysk",(SELECT koszt*ilosc_sztuk from produkty_marketingowe where produkt_id = p.produkt_id)))
 AS "Przewidywania"
 FROM produkty_marketingowe p
 where czy_dostepny = 1 and ilosc_sprzedanych <> 0
 order by p.nazwa;

Plik XML:

<Produkty>
 <Produkt ID="2"><Nazwa>Mikser</Nazwa><Koszt>11</Koszt><Zysk>110</Zysk></Produkt>
 </Produkty>

Tabela przygotowana do importu danych:

CREATE TABLE "HR"."MOZLIWY_ZYSK"
 ( "PRODUKT_ID" NUMBER,
 "NAZWA" VARCHAR2(20 BYTE),
 "KOSZT" NUMBER,
 "PRZEWIDYWANY_ZYSK" NUMBER
 ) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 TABLESPACE "USERS" ;

Import danych:

DECLARE
 pliczek xmltype := xmltype( bfilename('PLIKI_XML','10.xml'), nls_charset_id('AL32UTF8') );
 prac number :=0;
 BEGIN
 FOR R IN (SELECT
 EXTRACTVALUE(VALUE(Z), '/Produkt/@ID') AS produkt_id,
 EXTRACTVALUE(VALUE(Z), '/Produkt/Nazwa/text()') AS nazwa,
 EXTRACTVALUE(VALUE(Z), '/Produkt/Koszt/text()') AS koszt,
 EXTRACTVALUE(VALUE(Z), '/Produkt/Zysk/text()') AS przewidywany_zysk
 FROM TABLE(XMLSEQUENCE(EXTRACT(pliczek, '//Produkty/Produkt'))) Z)
 LOOP
 insert into mozliwy_zysk (produkt_id,nazwa,koszt, przewidywany_zysk) values (R.produkt_id,R.nazwa,R.koszt,R.przewidywany_zysk);
 END LOOP;
 END;

Bazy danych

Poprzedni Następny