piątek, 27 grudnia 2013

Klauzula RETURNING w PL/SQL

W PL/SQL istnieje opcjonalna klauzula RETURNING INTO pozwalająca na zapisanie do zmiennej wartości pochodzących z rekordu wstawianego przez polecenie INSERT lub modyfikowanego przez UPDATE. Taka możliwość staje się bardzo użyteczna, gdy zechcemy uzyskać ID pochodzącego z sekwencji właśnie wstawionego rekordu, lub generowanej dynamicznie innej wartości (np. jeśli wstawiamy sysdate).


W powyższym przykładzie wstawiłem nowy wiersz do tabeli jobs, a przy pomocy klauzuli RETURNING INTO uzyskałem ID wstawionego wiersza. Wartość ID została przypisana do zmiennej ID której typ został zdeklarowany na podstawie typu kolumny job_id z tabeli jobs. Taka możliwość nabiera ogromnego znaczenia, jeśli wartość wstawiana do kolumny klucza głównego pochodzi z sekwencji, a mamy zamiar operować na właśnie wstawionych danych. Oczywiście w takim przypadku można by również zastosować odwołanie do currval wykorzystywanej sekwencji, jednak nie mamy żadnej gwarancji, że w międzyczasie ktoś inny nie skorzystał z tej samej sekwencji i nie zmienił jej wartości.

sobota, 21 grudnia 2013

Klauzula FOR UPDATE

Przy użyciu klauzuli FOR UPDATE możemy zablokować wiersze do edycji przez inne sesje. Działa to na zasadzie transakcyjnej blokady zasobów. Jeśli my wykonamy jakiś UPDATE lub DELETE, wiersze których te polecenia zostaną zablokowane do czasu zatwierdzenia lub wycofania transakcji. W tym czasie inne sesje usiłujące dokonać jakiejkolwiek zmiany będą musiały oczekiwać na zwolnienie zasobów przez nas. Najniższy poziom blokady to wiersz, tak więc nawet jeśli zmienimy zawartość jednej kolumny, nikt nie będzie mógł zmienić również pozostałych kolumn w tych wierszach.
Klauzulę tę możemy wykorzystywać zarówno w SQL, jak i w kursorach w PL/SQL. Przykład użycia w SQL:






Wyświetlam 3 osoby z departamentu nr 90 , jednocześnie blokując te wiersze do edycji przez inne sesje. Teraz z innej sesji usiłuję te wiersze zmodyfikować:



Zauważ że modyfikuję inną kolumnę, niż te które wyświetlałem z pierwszej sesji. Sesja czeka na zwolnienie zasobów. Możemy teraz swobodnie dokonać zmian, bez obawy że ktoś inny w międzyczasie dokona jakichś zmian na „naszych” wierszach.


Dopiero po wydaniu polecenia „COMMIT”, wiersze zostają odblokowane i sesja która oczekiwała na odblokowanie zasobów może dokonać zmian:





Klauzulę FOR UPDATE możemy wykorzystywać również w PL/SQL w kursorach. Samo zadeklarowanie kursora nie spowoduje jednak blokady wierszy, jak się za chwilę przekonamy.
Uruchomiłem blok anonimowy z samą deklaracją kursora:


Aktualizacja z innej sesji przebiegła bez żadnych problemów:





Aby wiersze zostały zablokowane , kursor trzeba przynajmniej otworzyć:



Nie koniecznie musi to być otwarcie jawne, może być to również automatyczne otwarcie kursora które
następuje w pętli kursorowej, tak jak to widać poniżej:




Z wykorzystaniem klauzuli for update wiąże się również klauzula „WHERE CURRENT OF” która pozwala aktualizować lub kasować wiersze zablokowane przez kursor.



Istotna uwaga: klauzula WHERE CURRENT OF odnosi się do wiersza który właśnie został zfetchowany z kursora.

Niezależnie od ilości wierszy w kursorze, klauzula WHERE CURRENT OF odnosi się do ostatnio pobranego z kursora wiersza. Poniżej zastosowałem pętle kursorową, i jak widzimy zawsze ilość zaktualizowanych wierszy wynosi 1.






W przypadku próby wykorzystania klauzuli WHERE CURRENT OF bez uprzedniego fetcha, dostajemy błąd :





sobota, 14 grudnia 2013

Funkcje deterministyczne

Najpierw wyjaśnijmy, co to znaczy że funkcja jest lub nie jest deterministyczna. Funkcja jest deterministyczna wtedy, kiedy dla takich samych parametrów zwróci zawsze ten sam wynik. To oznacza, że taka funkcja musi działać zawsze w ten sam sposób, a na wynik nie powinny wpływać żadne czynniki zewnętrzne tj. funkcja nie powinna korzystać z żadnych zmiennych pakietowych ani innych źródeł zewnętrznych. Taka funkcja nie może też zmieniać żadnych danych w bazie (w tabelach ani pakietach). W niektórych sytuacjach wymagane jest by funkcja była deterministyczna. Przykładowo jeśli zechcemy użyć własnej funkcji w indeksie funkcyjnym, to funkcja ta musi być deterministyczna. Nie tylko spełniać warunek jako taki, ale też musi to być jasno określone w treści funkcji.
Poniżej przykład. Tworzę zwykłą funkcję, której już konstrukcja jasno wskazuje że funkcja jest deterministyczna (wartość parametru zawsze zostanie podzielona przez 12 i zaokrąglona do 2 miejsca po przecinku). Nie jest to jednak określone specjalną klauzulą DETERMINISTIC.
Przy próbie wykorzystania takiej funkcji w indeksie funkcyjnym dostajemy błąd
„ ORA-30553 The function is not deterministic”






Teraz dodaję klauzulę DETERMINISTIC (ponadto nic się nie zmienia), i przebudowuję funkcję, a następnie ponownie próbuję stworzyć indeks funkcyjny w oparciu o tę funkcję:



Tym razem obyło się bez problemów.

sobota, 7 grudnia 2013

Pakiet DBMS_SQL

Pakiet DBMS_SQL możemy wykorzystywać alternatywnie do klauzuli EXECUTE IMMEDIATE. Każdą z czynności : otwarcie kursora, parsowanie zapytania, wykonanie zapytania i zamknięcie kursora wykonujemy tutaj ręcznie i osobno. To ma swoje zady i walety. Z jednej strony jest więcej pisania i ilość kodu gwałtownie wzrasta. Z drugiej strony mamy większą kontrolę nad wszystkim co się dzieje, kod jest nieco bardziej przejrzysty. Przeanalizujmy teraz porównianie obu tych technik.
Użycie EXECUTE IMMEDIATE :




To samo z użyciem pakietu DBMS_SQL:





W obu przypadkach musiałem konkatenować zapytanie, z tym że w pierwszym wykonanie całości sprowadza się do polecenia EXECUTE IMMEDIATE. W drugim mamy kilka innych poleceń, wywołań procedur i funkcji z pakietu DBMS_SQL. Przeanalizujmy je po kolei.



dbms_sql.open_cursor
To funkcja alokująca przestrzeń na przetwarzanie kursora. Zwraca numeryczą referencję do kursora (taki identyfikator, dzięki któremu wiadomo o który kursor chodzi).



Dbms_sql.parse
Procedura ta sprawdza poprawność semantyczną zapytania (sprawdza czy wpisałeś zapytanie czy np. „Suchą szosą szedł sobie Sasza” ). W przypadku operacji DDL procedura ta wykonuje polecenie! W takim przypadku dzieje się to już tutaj i polecenie execute nie jest obowiązkowe.
Pierwszy parametr to identyfikator kursora (uzyskany przy wywołaniu funkcji open_cursor), drugi to zapytanie które będzie wykonywane, trzeci to wersja SQL. Można w trzecim parametrze ustawić zgodność SQL z wersją np. 6.












dbms_sql.execute
Funkcja wykonuje zapytanie. Jest zbędna w przypadku operacji DDL. W przypadku operacji DML zwraca ilość wierszy które uległy zmianie. W przypadku klauzuli EXECUTE IMMEDIATE również moglibyśmy się dowiedzieć ilu wierszy dotyczyła zmiana, ale musielibysmy dodatkowo użyć klauzuli SQL%ROWCOUNT.



dbms_sql.close
Dealokuje przestrzeń w pamięci operacyjnej wykorzystywaną na potrzeby przetwarzania kursora.



Możemy też wykorzystać zmienne bindowane w obu przypadkach. Wersja z EXECUTE IMMEDIATE:







Wersja z DBMS_SQL:



declare
dep integer:=90;
mana integer:=100;
podwyzka number:=400;
cu_id integer;
ile integer;
begin
cu_id:=dbms_sql.open_cursor;
dbms_sql.parse(cu_id, 'update employees set salary=salary+:p where department_id=:d and manager_id=:m',dbms_sql.native);
dbms_sql.bind_variable(cu_id,':p',podwyzka);
dbms_sql.bind_variable(cu_id,':d',dep);
dbms_sql.bind_variable(cu_id,':m',mana);
ile:=dbms_sql.execute(cu_id);
dbms_sql.close_cursor(cu_id);

end;






To co się tutaj pojawiło nowego, to wywołanie procedury bind_variable. Służy ona do podawania wartości jakie mają trafić do zmiennych bindowanych. Pierwszy parametr to identyfikator kursora, drugi zmienna bindowana użyta w zapytaniu, trzeci wartość do wstawienia do tej zmiennej. W zasadzie w obu przypadkach zamiast zmiennych dep,mana i podwyżka mógłbym użyć równie dobrze wartości bezpośrednio.

niedziela, 1 grudnia 2013

Wyrażenia regularne w Oracle (REGEXP)

Wyrażenia regularne

Funkcje

Wyrażenia regularne pozwalają nam wynajdywać w tekście fragmenty wg wzorców. Związane z regexp jest 5 funkcji:


REGEXP_COUNT

funkcja dostępna od wersji 11g. Zwraca ilość wystąpień elementów pasujących do wzorca. Poniższy przykład zwraca ilość liczb występujących w tekście:



select regexp_count('fff4ff563fffff','[[:digit:]]')
from dual;

















REGEXP_REPLACE




Funkcja zamienia elementy pasujące do wzorca na podany zamiennik. Na poniższym przykładzie widzimy zamianę wszystkich cyfr na ciąg XXX.



select regexp_replace('fff4ff563fffff','[[:digit:]]','XXX')

from dual;
















REGEXP_SUBSTR

Funkcja zwraca z podanego tekstu pierwszy element pasujący do wzorca. Poniżej widzimy wycięcie pierwszego przynajmniej 3 elementowego zbioru cyfr z tekstu:


select regexp_substr('eeeeee23ee44445eee444445','[[:digit:]]{3,}')

from dual;

















REGEXP_INSTR

Funkcja zwraca pozycję ciągu tekstowego pasującego do wzorca. Poniżej określenie pozycji elementu składającego się z przynajmniej 4 cyfr:



select regexp_instr('eeeeee23ee44445eee444445','[[:digit:]]{4,}')

from dual;


















REGEXP_LIKE

Służy sprawdzaniu czy podany tekst pasuje do wzorca:



select * from employees where
regexp_like(phone_number,'[[:digit:]]{3}\.[[:digit:]]{2}\.[[:digit:]]{4}\.[[:digit:]]{6}');









Wzorce w wyrażeniach regularnych




.
Dowolny znak
+
Jeden lub więcej wystąpień
?
Zero lub jedno wystąpienie
*
Dowolna ilość wystąpień (w tym zero)
{3}
Dokładnie trzy wystąpienia
{3,}
Przynajmniej trzy wystąpienia
{3,6}
Od trzech do sześciu wystąpień
|
Lub
[[:digit:]]
Cyfra
[[:alpha:]]
Litera
[[:alnum:]]
Znak alfanumeryczny
\
Wyłączenie znaku jako specjalnego. Np. jeśli zechcemy użyć kropki jako w kontekście znaku tekstowego, a nie znaku specjalnego oznaczającego dowolny znak






To oczywiście nie wszystkie możliwe wzorce, jest ich znacznie więcej. Wzorce dotyczące ilości wystąpień dotyczą zawsze poprzedzającego elementu. Kilka przykładów:



'[[:digit:]]{4,}'
Przynajmniej 4 cyfry
([[:alnum:]]| |\.|-){8,14}
Ciąg składający się z od 8 do 14 elementów wśród których mogą wystąpić dowolne litery i cyfry, spacje, kropki lub myślniki
[[:alnum:]]{1,}(\.[[:alnum:]]{1,})?@[[:alnum:]]{1,}\.[[:alpha:]]{2,4}
Przynajmniej jednoelementowy wyraz składający się z liter lub cyfr, po którym może nastąpić ciąg rozpoczynający się od kropki i składający się z przynajmniej jednej litery lub cyfry, po których nastąpi znak @, po których nastąpi przynajmniej jednoelementowy ciąg alfanumeryczny, po którym nastąpi kropka i ciąg składający się z samych tylko liter o całkowitej długości od 2 do 4 znaków. Czyli znajdziemy element wyglądający jak adres email :)






czwartek, 21 listopada 2013

Tabele zewnętrzne (External Table) typu DATA_PUMP

Tabele zewnętrzne typu Data_Pump


Od wersji 10g istnieje możliwość szybkiego utworzenia pliku na dysku na podstawie danych z zapytania. Taki plik jest w formacie Oracle Data Pump i może być przez to narzędzie odczytywany. Plik taki możemy też przenieść do innego systemu i podpiąć go jako tabelę zewnętrzną.
Nie możemy wyrzucić danych do dowolnego katalogu na dysku, a jedynie do tych które zostały zamapowane i mamy uprawnienia do zapisu w nich. W pierwszej kolejności mapujemy więc istniejący katalog i nadajemy użytkownikowi stosowne uprawnienie (jako administrator):

create directory temp as 'c:\temp';
grant read, write on directory temp to hr;


Następnie przystępujemy do eksportu:


create table lista_plac
organization external
( type oracle_datapump
default directory temp
location ('lista_plac.dmp')
) as select last_name,first_name,salary from employees;



















Parametr default directory określa alias katalogu w którym ma się znaleźć nasz plik eksportu. Location służy do podania nazwy pliku do jakiego dane mają zostać wyeksportowane. Dane jakie mają zostać wyeksportowane są określane przez zapytanie na końcu instrukcji.
Po takiej tabeli możemy wywoływać select'y jak po każdej innej:





















nie możemy jednak niestety aktualizować danych w takiej tabeli. Pozostanie ona tylko do odczytu.
Plik taki możemy za to przenieść do innego systemu i podpiąć go pod inną bazę.
Po stronie drugiej bazy musimy zamapować katalog w którym plik z danymi się znajdzie, oraz nadać do niego odpowiednie uprawnienia:


create directory dane as 'c:\temp\imporciki';
grant read,write on directory dane to hr;

Następnie tworzymy tabelę:


create table zarobki
(
first_name varchar2(50),
last_name varchar2(50),
salary number
)
organization external
( type oracle_datapump
default directory dane
location ('lista_plac.dmp')
) ;



















Nazwy kolumn w takiej tabeli nie mogą być przypadkowe, muszą odpowiadać nazwom pod jakimi zostały wyeksportowane. Definiujemy katalog w którym znajduje się taki plik, oraz jego nazwę. Z takiej tabeli korzystamy tak jak i wcześniej:





czwartek, 14 listopada 2013

Operatory Exists i Not Exists

Operatory Exists i Not Exists



Operator Exists sprawdza czy kolejne elementy z jednego zbioru znajdują swój odpowiednik w drugim zbiorze. Istotny jest sam fakt zaistnienia odpowiednika po drugiej stronie, a nie ilość wystąpień.



select * from departments d where exists(
select * from employees e where e.department_id=d.department_id

);

























Powyższe zapytanie zwraca nam departamenty z tabeli departments w których pracuje jakikolwiek pracownik. W podzapytaniu występuje warunek where e.department_id=d.department_id , określający zasadę według której system poszukuje odpowiedników w podzbiorze. Brany jest każdy kolejny wiersz z tabeli departments , a następnie przeszukiwana tabela employees w poszukiwaniu wiersza który w polu department_id miałby taką samą wartość. Wystarczy znalezienie jednego odpowiednika i system przestaje skanować dane. Z tego wynika wydajnościowa przewaga operatora EXISTS nad operatorem IN , oraz NOT EXISTS nad operatorem NOT IN.

Możemy też wykonać operację odwrotną, tj. wyświetlić te departamenty, w których nikt nie pracuje tj. które nie znajdują swojego odpowiednika w drugim zbiorze:



select * from departments d where not exists(
select * from employees e where e.department_id=d.department_id

);


czwartek, 7 listopada 2013

Klauzula grouping w SQL

 Grouping



Przy używaniu klauzul Rollup czy Cube powstaje jeden problem. Jak odróżnić które Nulle w wierszach wynikają z tego że dany wiersz to podsumowanie, a które nulle to brak danych? Objawia się ten problem zwłaszcza jeśli wynik posortujemy w taki sposób że podsumowania nie są na dole.
Zobrazujmy to przykładem:



select manager_Id,department_id,count(*), round(avg(salary))
from employees

group by rollup(department_id,manager_id);






















Podświetliłem agregację dla wartości 90 w department_id. Który z dwóch wierszy w których występuje null w kolumnie manager_id to ten z podsumowaniem?

Ten problem rozwiązuje nam zastosowanie klauzuli GROUPING :



select grouping(department_id),grouping(manager_id) ,
manager_Id,department_id,count(*), round(avg(salary)) from employees
group by rollup(department_id,manager_id);



Klauzulę GROUPING stosujemy wobec kolumn wg których grupujemy. Grouping zwróci nam wartość 0 albo 1 w zależności od tego czy dany wiersz zawiera podsumowanie czy nie.

 
Na powyższym przykładzie w najniższym z trzech zaznaczonych wierszy w kolumnie powstałej dzięki „grouping(manager_id)” widzimy wartość 1. To oznacza że null w kolumnie „manager_id” wynika z podsumowania, a nie z braku danych.


piątek, 1 listopada 2013

Klauzula WITH w SQL

Klauzula WITH




Klauzula WITH pozwala między inymi na nieco bardziej przejrzyste budowanie kodu. Pozwala podzapytaniu nadać nazwę i dzięki temu możemy później odwoływać się do niego poprzez tą nazwę, bez potrzeby wielokrotnego wstawiania tego samego kawałka kodu. Przykładowo taki kod:



select last_name,department_name from employees
join (
select * from departments join locations
using(location_id) where city='Seattle'
)

using(department_id);



























Można zamienić na znacznie bardziej przejrzystą postać:




with departamenty_seattle as (
select * from departments join locations
using(location_id) where city='Seattle'
)
select last_name,department_name from employees

join departamenty_seattle using(department_id);




środa, 23 października 2013

Oznaczanie kolumn jako UNUSED

Oznaczanie kolumn jako UNUSED


Usuwanie kolumn w bardzo dużych tabelach, z reguły trwa dość długo i generuje pewne obciążenie dla bazy danych. Jeśli zależy Ci na pozbyciu się kolumny bez takich negatywnych perturbacji i zależy Ci na czasie, możesz taką niepożądaną kolumnę oznaczyć jako UNUSED. Takie oznaczenie kolumny nie sprawi że zostanie ona usunięta, ani też nie uwolni zajmowanego przez nią miejsca. Nie będzie jednak widoczna w wynikach zapytania na tej tabeli, ewentualne referencje zostaną usunięte, a będzie można stworzyć kolejną kolumnę o takiej samej nazwie. Oznaczanie kolumn jako UNUSED wykonujemy taką komendą:

ALTER TABLE HR.EMPLOYEES SET UNUSED(SALARY,FIRST_NAME);


Sprawdzić czy mamy jakieś kolumny oznaczone jako unused możemy w słowniku DBA_UNUSED_COLS_TABS:

SELECT * FROM DBA_UNUSED_COLS_TABS WHERE TABLE_NAME='EMPLOYEES';



W przypadku zwykłego użytkownika mamy do dyspozycji słownik USER_UNUSED_COL_TABS:


SELECT * FROM USER_UNUSED_COL_TABS WHERE TABLE_NAME='EMPLOYEES';


W późniejszym czasie możemy fizycznie usunąć kolumny oznaczone jako UNUSED przy użyciu komendy:


ALTER TABLE EMPLOYEES DROP UNUSED COLUMNS;

poniedziałek, 21 października 2013

Synonimy w Oracle


Synonimy




Synominy są alternatywnymi nazwami dla tabel, widoków, sekwencji, procedur, funkcji, pakietów, widoków zmaterializowanych, klas javowych, typów obiektowych zdefiniowanych przez użytkownika, lub innych synonimów. Synonimy są użyteczne gdy np. zechcemy mieć krótszą nazwę dla jakiegoś obiektu, lub nazwę bardziej zrozumiałą dla człowieka. Np. tabela nazywa się „HUR_KLI_SZK_ORA” i nadajemy jej synomin „klienci_szkolen_oracle”. Nadanie użytkownikowi uprawnień do synonimu nie powoduje nadania uprawnień do obiektu dla którego synonim został stworzony. Uprawnienia do takiego obiektu muszą zostać nadane oddzielnie.
Synonimy możemy wykonywać w operacjach : SELECT, INSERT, DELETE, UPDATE, FLASHBACK, EXPLAIN PLAN, LOCK TABLE, AUDIT, NOAUDIT, GRANT, REVOKE, COMMENT



Uprawnienia do tworzenia synonimów




Aby tworzyć prywatne synonimy, wymagane jest uprawnienie CREATE SYNONYM
Aby tworzyć publiczne synonimy, wymagane jest uprawnienie CREATE PUBLIC SYNONYM
Aby tworzyć prywatne synonimy w schemacie innego użytkownika, wymagane jest uprawnienie CREATE ANY SYNONYM



Synonimy prywatne i publiczne




Synonimy publiczne, to synonimy widoczne dla wszystkich użytkowników. Aby korzystać z synonimów, użytkownicy muszą mieć dostęp do obiektów do których synonim się odnosi. Synonimy prywatne są widoczne tylko dla użytkownika w którego schemacie synonim się znajduje.




Tworzenie synominów




Składnia tworzenia publicznych synonimów jest następująca:



CREATE PUBLIC SYNONYM EMPS FOR EMPLOYEES;



Składnia tworzenia prywatnych synonimów jest następująca:



CREATE SYNONYM EMPS FOR EMPLOYEES;



Składnia tworzenia synonimu w schemacie innego użytkownika:



CREATE SYNONYM HR.EMPS FOR HR.EMPLOYEES;



Jeśli nazwiemy synonim prywatny i publiczny tak samo, system domyślnie będzie odwoływał się do synonimu prywatnego.



Tworząc synonim możemy również zastosować klauzulę OR REPLACE:



CREATE OR REPLACE SYNONYM HR.EMPS FOR HR.EMPLOYEES;



Sprawi ona, że jeżeli synonim o takiej nazwie już istnieje, zostanie podmieniony, a jeśli nie istnieje, zostanie utworzony.

Kasowanie synonimów




Aby skasować synonim prywatny :



DROP SYNONYM EMPS;



Aby skasować synonim publiczny:



DROP PUBLIC SYNONYM EMPS;



Aby skasować synonim ze schematu innego użytkownika:



DROP SYNONYM HR.EMPS;

Synonimy dla procedur, funkcji i pakietów




Tworzę w schemacie użytkownika SYS procedurę WITACZ:



CREATE PROCEDURE WITACZ(IMIE VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('WITAJ '||IMIE);
END;



Następnie tworzę publiczny synonim o nazwie WITACZ dla procedury WITACZ. Zauważ że podając nazwę procedury, nie podaję jej parametrów:



CREATE PUBLIC SYNONYM WITACZ FOR WITACZ;



Ponieważ sama możliwość sięgnięcia do synonimu publicznego przez użytkownika HR nie sprawia że może on wywołać procedurę której dotyczny synonim, nadaję osobne uprawnienia do tej procedury:



GRANT EXECUTE ON WITACZ TO HR;



Z poziomu użytkownika HR wywołuję procedurę korzystając z synonimu:



EXECUTE WITACZ('ANDRZEJ');



W sposób analogiczny odbywa się to dla funkcji.



Nie możemy nadawać synonimów dla procedur i funkcji znajdujących się w pakietach. Możemy za to nadać synonim pakietowi:



CREATE PUBLIC SYNONYM PISANIE FOR DBMS_OUTPUT;



Następnie korzystając z synonimu dla pakietu, odwoływać się do elementów tego pakietu:



EXECUTE PISANIE.PUT_LINE('HELLO!');

poniedziałek, 14 października 2013

Wielotabelowy INSERT (Multitable Insert)

Wielotabelowy insert (Multitable insert)

Przypuśćmy że wynik poniższego zapytania:



select department_name, count(*), max(salary), min(salary),round(avg(salary))

from employees join departments using(department_id) group by department_name;



Chciałbym załadować do kilku tabel, rozbijając wynik na wiele elementów. Przykładowo do tabeli „liczba_pracowników” trafiłaby nazwa departamentu i liczba pracowników, do tabeli „srednie_zarobki” trafiłaby nazwa tabeli i średnie zarobki etc.
W pierwszej kolejności tworzę tabele do których następnie załaduję dane:

create table liczba_pracownikow(
nazwa_departamentu varchar2(50),
liczba integer
);

create table najwyzsze_zarobki(
nazwa_departamentu varchar2(50),
kwota number
);

create table najnizsze_zarobki(
nazwa_departamentu varchar2(50),
kwota number
);

create table srednie_zarobki(
nazwa_departamentu varchar2(50),
kwota number
);


Mógłbym teraz zastosować kilka osobnych insertów w taki sposób:

insert into liczba_pracownikow select department_name,count(*)
from employees join departments using(department_id) group by department_name;
insert into najwyzsze_zarobki select department_name,max(salary)
from employees join departments using(department_id) group by department_name;
insert into najnizsze_zarobki select department_name,min(salary)
from employees join departments using(department_id) group by department_name;
insert into srednie_zarobki select department_name,round(avg(salary))
from employees join departments using(department_id) group by department_name;

Jednak ma to zasadnicze wady:

  • Robiąc kilka insertów na podstawie zapytań, doprowadzam do wielokrotnych skanów tabeli źródłowej. To problematyczne i nieefektywne przy dużych tabelach.
  • Pomiędzy poszczególnymi insertami dane w tabeli źródłowej mogą ulec zmianie, więc efekty mogą być niespójne

    Zamiast wielu insertów, możemy zrobić jeden insert wielotabelowy:

    insert all
    into liczba_pracownikow(nazwa_departamentu,liczba) values(department_name,liczba)
    into najwyzsze_zarobki(nazwa_departamentu,kwota) values(department_name,maksi)
    into najnizsze_zarobki(nazwa_departamentu,kwota) values(department_name, mini)
    into srednie_zarobki(nazwa_departamentu,kwota) values(department_name,srednia)
    select department_name,
    count(*) liczba, max(salary) maksi, min(salary) mini,round(avg(salary)) srednia
    from employees join departments using(department_id) group by department_name;


    W tym przypadku, skan po tabeli źródłowej wykonywany jest raz, a odczytane dane są rozbijane po czterech tabelach na raz. Ponadto dane pozostają spójne. Przyjrzyjmy się planowi wykonania zapytania:





poniedziałek, 7 października 2013

Pivot (Tabele przestawne) w Oracle

Pivot (tabele przestawne)


Pivot (tabele przestawne) jest dostępny od wersji 11g Oracle. Umożliwia nam przestawianie kolumn w miejsce wierszy i odwrotnie. Jest to wspaniałe narzędzie analityczne, pozwala wygodnie przeglądać np. tendencje. Pivot jest operacją agregującą. Przykład działania pivota:


Przed:



Po:



Prawda że czytelniej?
Przejdziemy teraz całą ścieżkę krok po kroku. W pierwszej kolejności tworzymy tabelę:

create table wyniki_sprzedazy(
okres varchar2(50),
produkt varchar2(50),
suma_przychodu number
);



Następnie ładujemy do niej dane:


insert into wyniki_sprzedazy values('06-2013','Pralki',65040);
insert into wyniki_sprzedazy values('07-2013','Pralki',31650);
insert into wyniki_sprzedazy values('08-2013','Pralki',24678);
insert into wyniki_sprzedazy values('06-2013','Telewizory',40600);
insert into wyniki_sprzedazy values('07-2013','Telewizory',12300);
insert into wyniki_sprzedazy values('08-2013','Telewizory',9004);
insert into wyniki_sprzedazy values('06-2013','Lodówki',45080);
insert into wyniki_sprzedazy values('07-2013','Lodówki',21600);
insert into wyniki_sprzedazy values('08-2013','Lodówki',4670);
commit;


Zajrzyjmy teraz do tabeli:

select * from wyniki_sprzedazy;



Dane przedstawione są w tej chwili w sposób płaski i mało czytelny. Zawartość tabeli reprezentuje sprzedaż trzech produktów na przestrzeni trzech miesięcy.
Przestawimy teraz troszeczkę te dane. Chcemy wyświetlić jak zmieniała się sprzedaż produktów na przestrzeni czasu. Robimy pewnego rodzaju szachownicę gdzie jeden bok jest wyznaczany przez produkty, a drugi przez okresy. Na przecięciu kolumn i wierszy ma znaleźć się suma sprzedaży danego produktu w danym okresie.


select * from wyniki_sprzedazy pivot
(
sum(suma_przychodu) for okres in ('06-2013','07-2013','08-2013')
)
;



sum(suma_przychodu) to element agregacji, musi się tutaj pojawić.
For okres in wyznacza kolumny jakie mają powstać na podstawie danych z kolumny okres.


Pełna składnia polecenia PIVOT:


SELECT …..
FROM …..
PIVOT
(
PIVOT_CLAUSE
PIVOT_FOR_CLAUSE
PIVOT_IN_CLAUSE
) WHERE......

PIVOT_CLAUSE to funkcja wyliczeniowa (agregująca) u nas jest to sum(suma_przychodu).
PIVOT_FOR_CLAUSE określa dane na podstawie których mają powstać kolumny. U nas jest to klauzula „for okres ….” która definiuje na podstawie jakich danych powstaną kolumny.
PIVOT_IN_CLAUSE to lista wartości z danych określonych w PIVOT_FOR_CLAUSE na podstawie których powstają kolumny. Tutaj „in('06-2013','07-2013','08-2013')”. Krótko mówiąc PIVOT_FOR_CLAUSE określa z danych jakiej kolumny w źródle mają powstać kolumny w pivocie, a PIVOT_IN_CLAUSE określa zakres tychże danych -tj. ile kolumn ma być.

Powstającym kolumnom podczas pivotowania możemy nadać aliasy:


select * from wyniki_sprzedazy pivot
(
sum(suma_przychodu) for okres in ('06-2013' czerwiec,'07-2013' lipiec,'08-2013' sierpien)
)
;





Robimy to wewnątrz PIVOT_IN_CLAUSE.


Dane wynikowe możemy też sortować:


select * from wyniki_sprzedazy pivot
(
sum(suma_przychodu)
for okres
in ('06-2013' czerwiec,'07-2013' lipiec,'08-2013' sierpien)
) ORDER BY 1; 




Klauzulę dotyczącą sortowania dajemy na samym końcu całego zapytania.
Inny przykład – tym razem z użyciem schematu HR. Mamy w tabelce employees kolumny JOB_ID DEPARTMENT_ID. Job_id to indentyfikator zawodu wykonywanego przez pracownika, department_id to identyfikator departamentu w którym dany pracownik pracuje. Chcielibyśmy teraz zrobić jakieś zestawienie obrazujące ilość pracowników na różnych stanowiskach w różnych departamentach. Zaczynamy więc od wykonania grupowania:


select job_id,department_id,count(*) from employees
group by job_id,department_id;



Musieliśmy ograniczyć całą zawartość tabeli do tych dwóch kolumn (job_id i department_id) oraz jednej kolumny z agregacją (count(*)). Pozostałe kolumny nie są nam potrzebne, mając więcej niż 3 wartość ( X , Y i wartość na przecięciu X – Y) wynik byłby całkowicie niezrozumiały. Dane jako takie już mamy przygotowane, są jednak przedstawione w formie płaskiej. Teraz chcielibyśmy te dane troszeczkę przesunąć i stworzyć z nich „szachownicę” czyli tabelę przestawną.

select * from (
select job_id,department_id,count(*) liczba from employees
group by job_id,department_id
) pivot (
sum(liczba) for department_id in (10,20,30,40,50,60,70,80,90,100,110)
);

Zauważ że po FROM może znaleźć się nie tylko nazwa tabeli, ale również całe zapytanie: