Zaglądamy w przeszłość :) Czyli odzyskiwanie utraconych danych dla NIE-adminów

Pewnie drogi czytelniku zdarzyło Ci się już nie raz pomylić się przy jakimś update i chciałbyś cofnąć zmiany. Jeśli nie masz włączonego autocommita to żaden problem zrobić ROLLBACK. Co jednak jeśli zacommitowałeś sam albo miałeś włączonego autocommita i dopiero po kilku innych zmianach ogarnąłeś się że coś jest nie tak?
Telefon do admina i przywracanie z backupu? Nie zawsze jest to konieczne. Jak to mówi Czesio z "Włatcóf muh" - teraz będzie sztuczka magiczka :).

Robimy! Zrób sobie kopię tabeli employees albo jakiejś innej. Sprawdzam i widzę że są jakieś dane.

 

Spójrz na zegarek. U mnie jest 12:38.  To jest czas kiedy z tabelą jest jeszcze wszystko w porządku. Tzn - jeszcze przed moimi niecnymi działaniami. Poczekaj parę chwil - tak ze dwie minuty, żeby nam się czas chociaż o minutę zmienił.

To teraz robimy problemy. Kasujemy wszystko z tabeli i commitujemy. 

Idź po kawę. Symulujemy poranne zamulenie, czyli udajemy że cośtam robisz i dopiero za moment się połapiesz że zwaliłeś akcję. Jak chcesz to możesz jeszcze sobie coś pododawać do tabeli, pozmieniać i pokasować dodane dane. Tak dla mocniejszego efektu dzieła zniszczenia.

Minęło już trochę?

To właśnie się połapaliśmy że nabroiliśmy. Wiemy że tabelka zawierała jeszcze poprawne dane jakieś 10 minut temu. "Chciałbym się cofnąć w czasie o te 10 minut".... No to się cofnij! :

Kodzik:

SELECT * FROM WYPADEK
as of timestamp (  to_timestamp('12:38:00 2010-11-28','hh24:mi:ss yyyy-mm-dd')   );

Tylko oczywiście zmień sobie datę i czas na swoje :) Dodanie klauzuli as of timestamp spowodowało wyświetlenie całej zawartości tabeli WYPADEK takiej jaka była we wskazanym czasie. Funkcja to_timestamp zwraca nam dane typu timestamp z tekstu. Drugi parametr to wzorzec. Przyjmuje takie same znaczniki jak funkcja to_date. Przyjmuje datę w postaci tekstu, zamienia wg. wzorca na format daty.

Możesz teraz przenieść sobie te dane do jakiejś innej tabeli:

CREATE TABLE WYPADEK2 AS SELECT * FROM WYPADEK WHERE 1=2;
INSERT INTO WYPADEK2 SELECT * FROM WYPADEK
as of timestamp (  to_timestamp('12:38:00 2010-11-28','hh24:mi:ss yyyy-mm-dd')  );

Tadam!

Select po pliku tekstowym

Dobry trick przy maści wszelkiej integracji systemów. Przypuśćmy że jeden system ma gdzieś tam na dysku pliki CSV, aktualizuje je regularnie. Może być też tak że taki plik jest często eksportowany z innego miejsca, a my chcemy być z danymi zawartymi w tym pliku na bieżąco. Regularne importy nawet przy użyciu CRONa są nieco workaroundem bo przecież dane mogą się zmieniać co parę sekund, a poza tym import to spore obciążenie dla systemu.
W Oracle jest opcja stworzenia specjalnej tabeli będącej niejako nakładką na plik tekstowy. Sprawia ona że możemy korzystać z danych w pliku tekstowym prawie w taki sam sposób jakby znajdowały się one bezpośrednio w tabeli. Pisząc prawie mam na myśli to, że możemy je czytać ale nie możemy do nich pisać.

Aby móc korzystać ten sposób z pliku tekstowego, Oracle musi mieć zmapowany katalog w którym owy plik się znajduje. Zaloguj się jako SYS i wykonaj poniższe polecenia:

create directory loader as 'c:\andrzej\loader'
grant read, write on directory loader to hr

Oczywiście wpisz adres katalogu na swoim dysku. Katalog musi fizycznie istnieć na dysku, a użytkownik systemowy Oracle (z którego baza korzysta w systemie) musi mieć do niego dostęp. Jeśli pracujesz w Linuksie, nadaj odpowiedni chmod. Druga linia powyższego kodu nadaje uprawnienia do zapisu i odczytu ale użytkownikowi bazodanowemu a nie systemowemu.

Zaloguj się jako user któremu nadałeś uprawnienia do tego katalogu (tutaj HR) , a następnie uruchom poniższy kod.


CREATE TABLE regiony_plik (
region_id number,
region_name varchar2(100)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY loader
ACCESS PARAMETERS(
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(region_id, region_name)
)
LOCATION ('regiony.csv')
)
REJECT LIMIT UNLIMITED;

Zauważ że jest to zwyczajna definicja tabeli z pewnymi dodatkami.
Type oracle_loader oznacza że jest to tabela z której będziemy korzystać w sposób opisany na początku tego posta.
Default directory loader - tutaj podajemy nazwę katalogu pod jaką funkcjonuje on w Oracle - nie podajemy tutaj żadnej ścieżki. Po to tworzyliśmy wcześniej mapowanie katalogu.
Fields terminated by ',' określa znacznik jaki rozdziela poszczególne kolumny w pliku csv.
Missing field values are null mówi oraclowi że puste wartości pomiędzy znakami rozdzielającymi to nie błąd tylko po prostu pusta wartość w kolumnie.
(region_id, region_name) to nazwy kolumn do jakich mają w podanej kolejności być ładowane dane.
Location('regiony.csv') określa nazwę pliku w zmapowanym katalogu.
Zrób sobie jakiegoś selecta na stworzonej tabeli by upewnić się że wszystko gra. Niektóre błędy mogą pojawić się dopiero teraz.

I voila!

SQL Loader - czyli wciągamy do Oracle dane z plików CSV

Jeśli przenosimy dane pomiędzy dwoma rodzajami baz (z czego docelowa to Oracle :) ), albo ewentualnie chcemy wczytać coś z Excela najlepiej wyeksportować ze źródła dane do postaci pliku CSV, a następnie wciągać SQL Loaderem.

Na potrzeby tej lekcji wyeksportuj zawartość tabeli regions z schematu hr do pliku CSV. Możesz to zrobić klikając w SQL Developerze prawym przyciskiem myszy na nazwę tabeli a następnie klikając export data--> csv.

Stwórz sobie teraz tabelkę do której będziemy wciągać dane. Moglibyśmy wciągnąć dane do istniejącej już tabeli, ale sql loader nadpisze wiersze jeśli trafi na identyczne. Na potrzeby ćwiczeń stwórz tabelkę która będzie kopią (samej struktury) tabeli regions:

Kod:

create table regions2 as select * from regions where 1=2

Ten warunek WHERE 1=2 jest po to by przy kopiowaniu tabeli nie kopiował jej zawartości.

Jak już będziesz miał tabelę to stwórz sobie plik kontrolny który będzie sterował poczynaniami sqlloadera, z analogiczną zawartością:

load data
infile 'c:\andrzej\ld\regions.csv'
append into table regions2
fields terminated by ','
(region_id,region_name)

load data zostawiasz w spokoju.
infile określa plik csv z którego wciągane będą dane.
Append into table regions2 to znaczy że dane będą wciągane do tabeli regions2
fields terminated by ',' oznacza znak który rozdziela kolumny - oczywiście może być co inny znak np. bardzo często jest to średnik ; .
Ostatnia linijka oznacza nazwy kolumn do których w wybranej tabeli mają zostać wciągane dane we wskazanej kolejności.

Zapisz plik i w konsoli wpisz coś takiego:
sqlldr hr/hr control=c:\andrzej\ld\ustawienia.txt

hr/hr to nazwa użyszkodnika z poziomu którego robimy wciąganie danych (musi mieć do danej tabeli uprawnienia oczywiście) . Control wskazuje na położenie pliku kontrolnego który sobie przed momentem stworzyłeś.

Zajrzyj teraz do tabeli regions2. Masz tam dane z pliku csv :)

Export i import danych dla NIE-adminów

Dzisiaj przedstawiam proste techniki importu i eksportu danych z bazy Oracle dla normalnych developerów Oracle (tj. nie geeków siedzących 25 godzin na  dobę przed komputerem znających każde najdrobniejsze pojęcie związane z Oracle, wszystkie możliwe struktury i dostępne parametry dla wszystkich programów, tylko dla takich  co pracują jak biali ludzie, mają trochę życia, może nawet psa..).
W Oracle mamy dwa fajne programy do tego celu. 
Generalnie przy użyciu programów exp i imp możemy eksportować i importować :
-całą bazę
-wybrany schemat
-wybrane tabele

Eksport danych

Najprostszy jest eksport całej bazy, swoją drogą może się przydać gdybyśmy coś napsuli :P

Kod:

exp userid=system/assembler1 file='c:\andrzej\ei\calosc.dat' log='c:\andrzej\ei\.calosc.log' FULL=Y

To co następuje po userid to nazwa użytkownika łamana przez hasło. W tym wypadku  muszę to zrobić jako system, ponieważ jako zwykły użytkownik nie mam dostępu do całej bazy (a całą chcę eksportować).
Po file podaję nazwę pliku do którego zostanie wrzucona zawartość bazy. Plik stworzy się sam, ale struktura katalogów już musi być. Po log podaję plik do którego mają być zrzucane logi. Podawanie tego nie jest obowiązkowe. Opcja Full=y nakazuje eksport całej bazy. Jeśli jej nie podasz, musisz wyeksportować schemat, albo tabelę, ale i tak musisz określić które.

Eksport całego schematu:

Kod:

exp userid=hr/hr file='c:\andrzej\ei\schemat.dat' log='c:\andrzej\ei\schemat.log' owner=hr


Parametry userid, file, log znaczą to samo co poprzednio :). Eksportuję jako user hr swój własny schemat. Oczywiście mogę eksportować również schematy innych userów, ale muszę do tego być zalogowany jako user system (albo user który jest ownerem schematu).
Poza tym zamiast "full=y" pojawiło się "owner=hr", co oznacza że wyeksportowany ma zostać cały schemat usera hr.

Na wypadek gdyby nie chciało Ci się wstać z fotela i zrobić eksport na kompie stojącym 2 metry dalej (albo serwerze pilnie strzeżonego przez 1435454 drzwi, strażników i bramek) to możesz zrobić to zdalnie, dodając w userid "@adres_ip" :

Eksport wybranych tabel

Możesz chcieć też zrobić sobie kopię zapasową tylko kilku tabel. Do tego celu w miejsce wcześniejszego full=y czy owner=hr wpisujesz np. tables=employees, departments :

 

Import danych

Jak już wyrzuciliśmy dane z bazy, to może zechcemy sobie je zaimportować z powrotem albo wrzucić na  innych serwerze. Do takich zabaw używamy programu imp.

Import całej bazy

Kod:

imp userid=system/assember1 file='c:\andrzej\ei\calosc.dat' log='c:\andrzej\ei\iportall.log' full=y destroy=y

Parametr userid - wiadomo to samo co przy exp, wiadomo że jak całość wciągamy to logujemy się jako user system. File to plik z wcześniejszego eksportu danych, log tak jak wcześniej, full=y bo wciągamy całość, destroy=y oznacza, że jeśli istnieje już obiekt w bazie o takiej nazwie to zostanie skasowany i nadpisany. Jeśli tego nie dodamy, to w takiej sytuacji zacznie krzyczeć (chyba że dodamy ignore=y, wtedy olewa problemy).

Chciałbym już pisać dalej i robić screeny ale jeszcze mi się importuje....


Import schematu

Importować możemy zawartość schematu nie tylko z powrotem do schematu usera z którego zostały dane wyciągnięte, ale też dane możemy wstawić innemu userowi. Dlatego przy imporcie nie ma już 'owner=hr', ale za to jest 'touser=hr' określający do schematu jakiego usera mają dane zostać wrzucone, oraz 'fromuser=hr'
czyli schemat jakiego usera może być wciągnięty ( bo jeżeli importujemy jakiś schemat z eksportu całej bazy, to przecież mamy tam schematów od pytki i trzeba któryś wybrać ).

 

Kod:

imp userid=hr/hr file='c:\andrzej\ei\schemat.dat' fromuser=hr touser=hr destroy=y

Tutaj też mógłbym dać np. log='costamcostam' jak przy eksporcie, ale mi się nie chciało pisać (czytaj: da się).


Import poszczególnych tabel

Podobnie jak przy imporcie schematu robię fromuser i touser - w tym względzie działa to tak samo. Wybieram sobie też tabele które chcę zaimportować.

Kod:

imp userid=hr/hr file='c:\andrzej\tabele.dat' fromuser=hr touser=hr tables=employees

Uważaj tylko z tym importem, bo Oracle nadpisuje tabele importowane bez żadnej gadki.

W nagrodę że chciało Ci się tyle czytać masz zagadkę: Co to jest : "nie świeci i nie mieści się w dupie?"
..
...
....
..
..

"radziecka maszyna do świecenia w dupie".

Programy exp i imp mają jeszcze kilka mniej lub bardziej przydatnych parametrów. Wszystkie znajdziesz tutaj :)

Partycjonowanie tabel

Dobre wieści:

Jeśli mamy duuuuuże tabele (tzn. zawierające dużo danych) to ich przetwarzanie możemy sobie usprawnić dzieląc je na takie wirtualne części nazywane partycjami. Dzięki temu:

1. Można zadać zapytanie tylko do jednej partycji w tabeli i dzięki temu nie ma potrzeby przetwarzania całej tabeli (co jak się nie trudno domyślić jest szybsze)
2. Joiny śmigają szybciej na takiej jednej partycji niż na całej tabeli, to samo grupowania, sortowanie i cała reszta możliwych sposobów na męczenie bazy.
3. Partycje możemy sobie stworzyć na podstawie zawartości kolumny w tabeli, dzięki czemu możemy np. mieć szybszy dostęp do faktur przykładowo z tego roku.
4. W ogóle idą za tym wszystkie korzyści związane z mniejszą ilością danych do przetworzenia.

Partycje możemy generalnie tworzyć na podstawie zawartości lub podzielić na sekcje równej wielkości. Dzielone na podstawie zawartości mogą być różnych rozmiarów - możemy mieć przykładowo milion faktur z tego roku, ale 5 milionów z zeszłego.

Partycjonowanie względem zawartości:

Powyżej podzieliłem tablicę na partycję względem zawartości kolumny salary na sekcje.

Partycjonowanie hashowe (takie na równej wielkości kawałki):
 

Złe wieści:

1. Taka opcja dostępna jest tylko w wersji Enterprise (która kosztuje tyle co dom Twój i wszystkich Twoich sąsiadów z całej wioski :P )
2. Sama tylko opcja kosztuje tyle co całkiem niezgorszy samochód 

Nie, nie robię sobie jaj. Sam sprawdź: Cennik :D

Tabele tymczasowe

W Oracle możemy stworzyć tabelę tymczasową która będzie przechowywać dane  tylko w czasie bieżącego połączenia.
Tabela tymczasowa dostępna jest z wielu sesji.
Zawartość takiej tabeli widoczna jest tylko dla aktualnej sesji.
Wykorzystujemy taki byt do chwilowego przechowywania danych.
Możemy stworzyć taką tabelę w sposób zbliżony do tworzenia zwykłej tabeli (różnica w dodaniu GLOBAL TEMPORARY):

Podając kolumny:

Na podstawie zapytania :

Warunek "where 1!=1" jest po to, by skopiować tylko strukturę tabeli bez danych. Posiadając taką tabelę, mamy możliwość zrobienia sobie czegoś w rodzaju backupu. Czyli wrzucamy sobie przykładowo zawartość tabeli employees do tabeli tymczasowej, robimy jakieś fiku miku na tabeli employees a w razie jakbyśmy coś spraprali mamy poprzedni stan danych łatwo dostępny w tabeli tymczasowej bez potrzeby robienia normalnych backupów.

Wyższość tego rozwiązania nad tworzeniem zwykłej tabeli do tego samego celu, polega na tym że po zakończeniu sesji dane z tabeli tymczasowej znikają. Dzięki temu nie zostają nam śmieci zajmujące miejsce, a zupełnie nie potrzebne nam później.

Kurs Oracle PL/SQL. Wyzwalacze typu INSTEAD-OF

Na niektórych widokach nie można wykonywać operacji DML (kasowania, zmiany, dodawania wierszy) . Przykładem może być widok oparty o połączone tabele:

Ponieważ w tym widoku znajdują się dane pochodzące z dwóch tabel, nie mogę na nim wykonać operacji delete i muszę zastosować wyzwalacz typu instead-of.

Dane pochodzące z widoku:

 

Widzimy że w jednej lokalizacji może znajdować się więcej niż jeden departament. Teraz chciałbym, aby przy usuwaniu danego departamentu :

• jeśli wybrany departament jest jedynym w lokalizacji, automatycznie powinna zostać skasowana również lokalizacja
  
• jeśli w lokalizacji w której znajduje się wybrany departament znajdują się inne departamenty, to lokalizacja powinna zostać nienaruszona, natomiast powinien zostać usunięty departament.
  
  
  

Poniższy kod najpierw sprawdza ile jest departamentów w danej lokalizacji, a następnie w zależności od tego ile znajduje się departamentów w tej lokalizacji usuwa tylko departament, lub departament i lokalizację. Uwaga: ponieważ tabela locations powiązana jest kluczem obcym z tabelą countries, przed wykonaniem poniższego wyzwalacza należałoby poszerzyć kod o sprawdzanie zależności w tabeli countries. Poniższy przykład ma charakter pokazowy, dodawanie dodatkowego kodu sprawiłoby że przykład przestałby być czytelny.

Ćwiczenia

1.Stwórz tabelę logs zawierającą pola info typu varchar2(100), time typu date. Stwórz wyzwalacz który przy każdej operacji zmieniającej w jakikolwiek sposób dane w tabeli jobs, do tabeli logs została wprowadzona informacja o typie zmiany oraz czasie kiedy ta zmiana została wykonana.



2. Zablokuj przy pomocy wyzwalacza możliwość zmiany istniejących danych w tabeli employees w taki sposób by zmniejszona została wartość w kolumnie salary dla któregokolwiek wiersza.



3.Do tabeli employees dodaj kolumnę typu liczbowego o nazwie month_salary. Spraw by przy każdym insercie do tej tabeli uwzględniającym informacje o wypłacie (kolumna "salary"), do nowo stworzonej kolumny była wprowadzana 1/12 wartości salary. Jeśli żadne dane nie zostaną wprowadzone do kolumny salary, przechwyć obsługę błędu i wyświetl na konsoli informacje o brakujących danych.



 




Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Predykaty w wyzwalaczach

Jeden wyzwalacz może reagować na więcej niż jedno zdarzenie na obiekcie. Wystarczy dodać kolejne warunki w definicji wyzwalacza. W poniższym przykładzie widoczny jest wyzwalacz który zareaguje na insert, update oraz delete.
Wygodnie jest mieć jeden wyzwalacz przypisany do tabeli, jednak muszę w takim wypadku mieć możliwość rozróżnienia jakie polecenie zostało wykonane na tabeli. Rozróżniam je dzięki predykatom INSERTING,DELETING,UPDATING. Predykaty mogę stosować w warunkach takich jak IF, a dzięki temu wykonywać różne operacje w zależności od tego jaka instrukcja została wykonana.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Wyzwalacze wierszowe

Istnieje możliwość modyfikowania danych przez wyzwalacz, zanim te zostaną wstawione do tabeli, lub zanim wiersz w tabeli zostanie zmieniony tymi danymi.
Możemy wykonać takie działania, ponieważ istnieją dwie zmienne :new oraz :old zawierające cały modyfikowany, dodawany lub usuwany wiersz. Korzystając z tych zmiennych możemy przed wstawieniem danych do tabeli pobrać je, zmodyfikować, a następnie wstawić już zmienione dane.
Aby móc korzystać ze zmiennych :new i :old muszę dodać dodatkową klauzulę „FOR EACH ROW” do definicji wyzwalacza.
Na poniższym przykładzie prezentuję przykład modyfikacji danych wstawianych do tabeli. Musimy pamiętać, by takie operacje jak modyfikacje wstawianych danych wykonywać w wyzwalaczu uruchamianym PRZED zdarzeniem, a nie po.

Nakładając wyzwalacz na UPDATE tabeli mam również dostęp do zmiennej :old. Zawiera ona zmieniany wiersz w postaci w jakiej występował przed modyfikacją. Mogę więc określić jak zmieniana nazwa stanowiska brzmiała przed, a jak po zmianie wiersza:
 

Mam też możliwość zdefiniowania wyzwalacza który będzie się wykonywał tylko jeśli spełniony zostanie warunek dotyczący wprowadzanych danych.


Uwaga: W ciele wyzwalaczy nie może być instrukcji które modyfikują strukturę lub zawartość tabeli na którą nałożony jest wyzwalacz. Nie mogą też zawierać instrukcji wpływających na transakcję ani odwoływać się do podprogramów zawierających takie instrukcje.


W wyzwalaczu CUSTOM3 wyświetlona zostanie informacja tylko jeśli wartość MAX_SALARY wstawianego wiersza będzie wyższa niż 10000. Zwróć uwagę, że wykonywane są również inne wyzwalacze które wcześniej nałożyłem na działania na tej tabeli.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Wyzwalacze obiektowe

Wyzwalacze są podprogramami wywoływanymi jako reakcja na pewne zdarzenie na obiekcie bazy danych. Przykładowo tworząc wyzwalacz możemy określić jakie działanie ma podjąć w przypadku gdyby użytkownik wprowadził dane do wybranej tabeli.
Triggery są znakomitym narzędziem do logowania zdarzeń w systemie. Możemy stworzyć własną tabelę z logami, następnie wyzwalacze które będą do tej tabeli wstawiać kolejne wiersze w zależności od działań na obiektach bazy danych.

Tworzenie wyzwalaczy

Ogólna konstrukcja tworzenia wyzwalaczy wygląda w taki sposób:

CREATE OR REPLACE TRIGGER nazwa_triggera

BEFORE/AFTER

DELETE/UPDATE/INSERT/INSERT OR UPDATE OR DELETE

ON nazwa_tabeli

instrukcje które mają zostać wykonane.

W poniższym przykładzie utworzyłem wyzwalacz który przed wprowadzeniem wiersza do tabeli JOBS ma wyświetlić na konsoli wiadomość „ktoś wstawił wiersz do tabeli jobs!!”

Od tego momentu, za każdym razem gdy będzie wstawiany wiersz do tabeli jobs zostanie wywołany wyzwalacz CUSTOM. Działania w nim opisane będą wykonywane PRZED wprowadzeniem danych do tej tabeli, ponieważ użyłem klauzuli BEFORE.

Poniższa ilustracja prezentuje efekt działania wyzwalacza. Wprowadziłem wiersz do tabeli jobs, a Oracle wywołał mój wcześniej stworzony wyzwalacz który wypisał na konsoli wiadomość.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle SQL. Pakiety - sekcja inicjalizacyjna pakietu

W niektórych sytuacjach zmienne w pakiecie muszą zostać zainicjalizowane dynamicznie np. wartością pochodzącą z zapytania.
Do naszego ćwiczebnego pakietu dodaję zmienną dostępną dla wszystkich podprogramów w tym pakiecie:

Chciałbym teraz aby do nowej zmiennej podczas inicjalizacji pakietu (czyli wtedy gdy po raz pierwszy w danej sesji odwołuję się do jakiegoś składnika pakietu) została przypisana dynamiczna wartość – w tym wypadku pochodząca z jednej z funkcji tegoż pakietu.

W tym celu muszę uzupełnić sekcję inicjującą pakietu. Wykonuję to poprzez dodanie poleceń które mają zostać wykonane przed końcem treści pakietu, poprzedzając je słowem „BEGIN”.:

Do zmiennej „zmienna” zostanie przypisana wartość będąca wynikiem działania funkcji „srednia_zarobkow_dzialu”. Zwróć uwagę że jeśli zmieni się wartość jaka wynikałaby z tej funkcji w trakcie korzystania z pakietu, ale już po inicjalizacji – zawartość zmiennej zainicjalizowanej w ten sposób nie zmieni się. 

Ćwiczenia

1. Stwórz pakiet który będzie "sercem" prostego programu kadrowo - płacowego. Pakiet nazwij hrmanager.

2. Do pakietu hrmanager dodaj funkcję która zwróci największe zarobki w całej firmie.

3. Do pakietu hrmanager dodaj procedurę o nazwie "srednia" która pobiera jako parametr id działu. Procedura powinna wyświetlić na konsoli średnią zarobków w dziale o id takim jak przyjęty parametr.

4. Do pakietu hrmanager dodaj procedurę o nazwie "srednia" która pobiera jako parametry id działu oraz id managera. Procedura powinna wyświetlić na konsoli średnią zarobków w dziale o id takim jak przyjęty pierwszy parametr, ale tylko tych osób które są podwładnymi managera o numerze podanym jako parametr drugi.

5. Do pakietu hrmanager dodaj procedurę o nazwie "srednia" która pobiera jako parametry nazwę działu oraz nazwisko managera. Procedura powinna wyświetlić na konsoli średnią zarobków w dziale o nazwie takiej jak przyjęty pierwszy parametr, ale tylko tych osób które są podwładnymi managera o nazwisku podanym jako parametr drugi. Procedura powinna wyświetlać na konsoli informację, jeśli w wyniku zapytania zostanie zwrócone 0 wierszy.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL.Pakiety - przeciążanie procedur i funkcji

W jednym pakiecie możemy zdefiniować kilka procedur lub funkcji o identycznych nazwach, jednak muszą one różnić się ilością lub typem parametrów przyjmowanych. Nie można zdefiniować identycznych podprogramów jeśli różnią się jedynie zwracanym typem lub typami parametrów IN, IN OUT i OUT.

W poniższym przykładzie dodałem drugą funkcję „srednia_zarobkow_dzialu” różniącą się tylko jednym parametrem. Dodałem parametr id_managera aby wyświetlać średnie zarobków w danym dziale, obliczane na podstawie zarobków pracowników podległych konkretnemu managerowi w tym dziale.

Następnym krokiem było rozbudowanie części implementacji pakietu o ciało nowej funkcji:

 

Mogę teraz wykorzystać obie wersje funkcji, a to która z nich zostanie użyta zależeć będzie od ilości podanych parametrów:

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Pakiety - podstawy

Pakiety są logicznym zbiorem obiektów takich jak podprogramy, typy, wyjątki, kursory. Składają się z części specyfikacji oraz implementacji. W części specyfikacji deklarujemy jedynie co ma zawierać pakiet, a w części implementacji „ciała” deklarowanych obiektów. Jeśli więc w części specyfikacji deklarujemy że pakiet zawierać ma procedurę „procedura1” z dwoma parametrami typu number, to w części implementacji musimy określić dokładnie jak ta procedura działa, a więc całość jej kodu.

Może istnieć deklaracja obiektu np. procedury w części specyfikacji bez rozwinięcia w części implementacji, ale nie może być odwrotnie. Obiekty w pakiecie mogą zasadniczo występować w dowolnej kolejności, poza sytuacją gdy procedura A odnosi się do procedury B – w takim wypadku procedura A musi być zadeklarowana wcześniej.

Przystępując do tworzenia pakietu musimy najpierw zdeklarować elementy z których pakiet ma się składać. Robimy to w części specyfikacji:

Na razie definiujemy jedynie jakie elementy mają się znaleźć w pakiecie, oraz jakie parametry mają przyjmować i zwracać procedury i funkcje.

Przechodzimy teraz do opisania sposobu działania funkcji i procedur tj. części „Body” pakietu.

„Zapowiedź” procedur i funkcji która pojawiła się w części specyfikacji pakietu, jest teraz rozwijana o implementację. Każda procedura i funkcja która pojawiła się w poprzednim przykładzie jest teraz napisana w całości. Wszystkie nazwy i typy parametrów są identyczne.

Mając już działający skompilowany pakiet mogę się odwołać do funkcji i procedur w nim zawartych:

Jeśli odwołuję się do funkcji/procedury zawartej w pakiecie z konsoli, podprogramu nie ujętego w pakiecie lub zawierającego się w innym pakiecie niż podprogram do którego się odwołuje, muszę podać nazwę pakietu przed nazwą wywoływanej funkcji. Jeśli odnoszę się do tego podprogramu z innego podprogramu zawartego w tym samym pakiecie, nazwy wywoływanej funkcji/procedury nie muszę poprzedzać nazwą pakietu.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Kursory

Kursory są encją w której przechowywane są wiersze zwrócone przez zapytanie z bazy danych.
Przetwarzając kursory wykonujemy cztery operacje:

• zadeklarować kursor
  
• otworzyć go
  
• pobrać wiersze
  
• zamknąć kursor
  

Deklaracja kursora

 Kursor deklarujemy w sekcji DECLARE bloku PL/SQL

 

Składa się on z podania nazwy kursora oraz zapytania na podstawie którego mają zostać do kursora „wciągnięte” dane.

Otwarcie kursora

 

 
Kursor otwieramy w sekcji wykonawczej bloku PL/SQL. W tym momencie do kursora pobierane są dane. W jednym bloku kursor możemy otworzyć tylko raz.

Pobranie wierszy z kursora

Mając dane załadowane do kursora (kursor otwarty), możemy pobrać je do zmiennych. Na poniższej ilustracji widać, że zdeklarowałem zmienne do których następnie pobieram dane z kursora. Istotne jest by pamiętać że dane zostaną załadowane do zmiennych w takiej kolejności w jakiej wypadają kolumny kursora. W tym wypadku zapytanie na podstawie którego stworzony został kursor zwraca dokładnie jeden wiersz. Gdybym z tego zapytania usunął warunek „where job_id==160” w kursorze znalazłoby się tyle wierszy ile znajduje się w tabeli jobs. W takiej sytuacji zastosowanie klauzuli FETCH zwróciłoby mi pierwszy wiersz ze zbioru danych w kursorze. Gdybym zechciał przetworzyć więcej niż jeden wiersz w wielowierszowym kursorze, musiałbym zastosować pętlę kursorową o której będzie mowa w następnych rozdziałach.

Zamknięcie kursora

Kursor zamykamy przy pomocy poniższej konstrukcji. Zamkniętego kursora nie możemy już przetwarzać.
 

Atrybuty kursora

Służą do określania własności kursora.


ISOPEN – Zwraca „true” jeśli kursor jest otwarty i „false” jeśli kursor jest zamknięty.
ROWCOUNT – Zwraca ilość wierszy które już zostały ściągnięte z kursora
NOTFOUND – Służy najczęściej do sprawdzania czy w kursorze są jeszcze jakieś dane. Zwraca „true” jeśli komenda FETCH nie może pobrać kolejnego wiersza. Jeśli FETCH może pobierać dane dalej, zwraca „false”
FOUND - Służy najczęściej do sprawdzania czy w kursorze są jeszcze jakieś dane. W przeciwieństwie do NOTFOUND zwraca „false” jeśli komenda FETCH nie może pobrać kolejnego wiersza. Jeśli FETCH może pobierać dane dalej, zwraca „true”

Poniżej prezentacja zastosowania atrybutów kursora. Kursor został zadeklarowany i otwarty, a następnie przetwarzany jest w pętli do momentu kiedy atrybut NOTFOUND zwróci wartość true.

Odbywa się to dzięki klauzuli EXIT WHEN. Zwróć uwagę że nie zastosowałem tutaj żadnego warunku typu „notfound=true”. Jeśli nie zdeklaruję takiego porównania, domyślnie dostawiane jest „=true” (my tego oczywiście nie widzimy – dzieje się to wewnątrzsystemowo). Na koniec korzystam z atrybutu ROWCOUNT aby określić ile ostatecznie wierszy zostało pobrane z kursora.

Sprawdzanie ilości wierszy wykonuję jeszcze na otwartym kursorze, przed jego zamknięciem, ponieważ w innym wypadku otrzymałbym komunikat o błędzie „invalid cursor” i program zostałby przerwany.

 

Pętla kursorowa

Stosowanie konstrukcji ujętej w poprzednim rozdziale nie jest najwygodniejszym rozwiązaniem w praktyce. Zaprezentowałem je by zapoznać czytelnika z mechanizmami związanymi z kursorami i pomóc zrozumieć zasady ich działania. Pętla kursorowa jest konstrukcją znacznie prostszą, nie musimy sprawdzać czy jeszcze są jakieś dane do pobrania ani otwierać i zamykać kursora.

Poniższa pętla wykona się tyle razy ile będzie wierszy w kursorze. Poniższa pętla pobierze po kolei wszystkie wiersze z kursora i wrzuci je do zmiennej rekordowej stworzonej na podstawie konstrukcji tabeli „employees” z której dane pobieram do rekordu.

Kursory sparametryzowane

Na pierwszy rzut oka kursory wydają się być przyjemne i wygodne w użyciu. Jest jeden problem …

Przy wiedzy którą dotychczas posiadamy nie możemy zmieniać zapytania na podstawie którego tworzony jest kursor. A co jeśli zechcemy zmienić parametr w warunku WHERE zapytania?

Kursory parametryzować mogę na dwa sposoby. Poprzez zmienną lub poprzez parametr. Oba zaprezentowałem na poniższym przykładzie. Pierwszy z kursorów pobiera wartość do warunku where ze zmiennej której wartość ustalam przed otwarciem kursora. Drugi otrzymuje tą wartość jako parametr uruchomieniowy przy otwieraniu kursora. Dlaczego to istotne? Dzięki temu możemy napisać jeden generyczny program narzędziowy który można wykorzystywać wielokrotnie, zamiast dublować ten sam kod ze zmienionym jednym parametrem.

Ćwiczenia

1. Stwórz kursor który pobierze dane o pracownikach zarabiających więcej niż średnia zarobków w firmie, a następnie wyświetli na konsoli ich imiona, nazwiska, wypłatę oraz średnia zarobków w firmie.

2. Stwórz kursor sparametryzowany, który wyświetli na ekranie wszystkich pracowników managera o id podanym jako parametr kursora. Parametr do kursora ma zostać przekazany z parametru procedury.

3. Stwórz procedurę. Zadeklaruj tablicę obiektów o strukturze pól takiej jak rekord w tabeli departments. Stwórz kursor który pobierze wszystkie dane z tabeli departments, a następnie umieści je w tablicy.

 






Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Tablice

Tablice przechowują listę innych zmiennych tego samego typu. Tablice posiadają indeksy które numerują kolejne elementy i umożliwiają odwoływanie się do nich. Każdy z „wierszy” w tablicy zawiera więc dwa pola – pole indeksu, oraz pole wartości.
Aby zdefiniować tablicę piszemy w sekcji DECLARE :
TYPE NAZWA_TYPU IS TABLE OF WYBRANY_TYP_ZARTOŚCI
INDEX BY;


Następnie by móc z takiego typu tablicowego korzystać, muszę stworzyć zmienną nowo stworzonego typu – w tym przypadku zmienną „tab”. Aby odwołać się do poszczególnej wartości w tablicy podajemy nazwę tablicy i numer pozycji w okrągłych nawiasach.

 

Atrybuty tablic

Tablice posiadają atrybuty służące do operacji na zawartości tablic.


EXIST(x) – sprawdza czy na pozycji x znajduje się jakiś element. Zwraca wartość bool true lub false.
COUNT – zwraca ilość niepustych elementów w tablicy.
DELETE – usuwa całą zawartość tablicy
DELETE(x) – usuwa element z tablicy znajdujący się pod indeksem X.
DELETE(x,y) – usuwa z tablicy elementy począwszy od indeksu x do y.
LAST – zwraca indeks ostatniego niepustego elementu w tablicy
FIRST – zwraca indeks pierwszego niepustego elementu w tablicy
NEXT(x) – zwraca indeks następnego niepustego elementu tablicy następującego po indeksie X.
PRIOR(x) – zwraca indeks poprzedniego niepustego elementu tablicy znajdującego się przed indeksem x.


Przykłady wykorzystania atrybutów tablic :
 

Ćwiczenia

1. Stwórz blok anonimowy. Zdefiniuj w nim tablicę typu varchar2 o długości tysiąca pozycji. Zainicjuj wszystkie pozycje treścią "pusto".
2. Do bloku z ćwiczenia nr. 1 dodaj podsumowanie sprawdzające i wypisujące na ekranie ile pól w tablicy zostało wypełnionych.



Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Propagacja wyjątków

W przypadku bloków zagnieżdżonych, jeśli wystąpi błąd w najgłębiej osadzonym bloku, sprawdzana jest obsługa błędu w sekcji EXCEPTION danego bloku. Jeśli ten wyjątek będzie w nim obsługiwany, błąd nie zostanie przekazany do bloku wyższego poziomu i wyższe bloki będą zachowywać się tak, jakby ten wyjątek nie wystąpił. Jeśli jednak wyjątek nie będzie obsługiwany w bloku w którym wystąpił, zostanie przekazany blok wyżej celem sprawdzenia czy tam ten błąd jest obsługiwany. Będzie się tak działo do momentu kiedy obsługa danego błędu nie pojawi się w którymś z poziomów zagnieżdżenia.

Ćwiczenia

1. Stwórz procedurę wyświetlającą na konsoli wynik dzielenia 2 wartości podanych jako jej parametry. Obsłuż błędy które mogą się pojawić w trakcie działania programu. Do obsługi błędów stwórz oddzielną procedurę z parametrem.
2. Do procedury stworzonej w ćwiczeniu nr. 1 dodaj własny wyjątek informujący użytkownika o równości dzielnika i dzielnej. Dodaj obsługę takiego wystąpienia.





Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Definiowanie własnych wyjątków

Niekiedy zachodzi potrzeba obsłużenia sytuacji wyjątkowych nie ujętych w zbiorze wyjątków predefiniowanych. Przykładowo użytkownik wprowadza w formularzu wzrost nie mieszczący się w naturalnych granicach np. 4 metry. Możemy taką sytuację obsłużyć definiując własny wyjątek.
W sekcji DECLARE musimy zdefiniować wyjątek - w tym przypadku „moj_wyjatek”. W sekcji EXCEPTION zadeklarowałem działania które mają zostać wykonane w przypadku pojawienia się mojego wyjątku. Dodałem warunek, że jeśli podany wzrost wyniesie więcej niż 240cm ma zostać wywołany mój nowo stworzony wyjątek. Uzyskuję to poprzez zastosowanie klauzuli RAISE NAZWA_WYJATKU. W ten sposób mogę wywoływać również wyjątki predefiniowane.
Niekiedy w sytuacjach podbramkowych definiowanie własnych wyjątków jest stosowane w taki sposób jak instrukcja GOTO w starszych językach programowania – jak Pascal lub Basic. Nie warto jednak działać w ten sposób, ponieważ kod staje się w takim wypadku bardzo nieczytelny.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Obsługa wyjątków

Wyjątki obsługujemy w sekcji EXCEPTION która nie pojawiła się jeszcze we wcześniejszych przykładach. Sekcja EXCEPTION musi znajdować się pomiędzy BEGIN i END. Po klauzuli WHEN podajemy nazwę wyjątku a następnie THEN i instrukcje które mają zostać wykonane w przypadku wystąpienia wyjątku. Możemy również zdeklarować reakcję na wystąpienie pozostałych wyjątków których wcześniej nie uwzględniliśmy przy pomocy klauzuli OTHERS.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Wyjątki predefiniowane

Predefiniowane wyjątki

Lista najczęściej pojawiających się predefiniowanych wyjątków w PL/SQL


INVALID_NUMBER powstaje podczas próby wykorzystania wartości tekstowej jako wartości numerycznej w SQL. W PL/SQL zamiast niego wywoływany jest wyjątek VALUE_ERROR



TOO_MANY_ROWS pojawia się, gdy polecenie SELECT INTO wybrało więcej niż jeden rekord a jak już wiemy z poprzednich rozdziałów select w PL/SQL przypisujący wartości do zmiennej musi zawsze zwrócić jeden wiersz.



NO_DATA_FOUND powstaje, gdy polecenie SELECT INTO nie zwraca żadnego wiersza.



LOGIN_DENIED nieudana próba zalogowania do bazy Oracle przy użyciu nieprawidłowej nazwy użytkownika lub hasła, oraz w przypadkach gdy użytkownik nie posiada uprawnień do łączenia się z bazą danych.



DUP_VAL_ON_INDEX powstaje w czasie próby dodania wiersza do tabeli z naruszeniem klucza głównego lub indeksu UNIQUE kiedy podajemy wartość już istniejącą w kolumnie.



CURSOR_ALREADY_OPEN powstaje w czasie próby otwarcia kursora już otwartego.

INVALID CURSOR powstaje w czasie próby wykonania nielegalnej operacji na kursorze, na przykład, zamknięcia kursora nie otwartego.

NO_LOGGED_ON powstaje podczas próby wykonania działań na bazie danych bez uprzedniego zalogowania się.


STORAGE_ERROR powstaje, gdy zabraknie pamięci operacyjnej dla programu, lub gdy nastąpiła awaria pamięci.


VALUE_ERROR powstaje z powodu błędów arytmetycznych i konwercyjnych w sytuacjach, których nie obejmuje wyjątek INVALID_NUMBER.


ZERO_DIVIDE powstaje przy próbie dzielenia liczby przez zero.



Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

Kurs Oracle PL/SQL. Wyjątki - omówienie

Wyjątki występują kiedy użytkownik próbuje wykonać polecenie którego nie może zrealizować serwer. Każdy wyjątek ma swój numer i opis, jest ich kilka tysięcy. Przykłady kiedy może wystąpić wyjątek:

• użytkownik nie ma uprawnień do obiektu do którego się odwołuje
• pojawił się błąd w nazwie obiektu -tzw. literówka
• naruszenie więzów integralności

Instrukcja podczas której wystąpił wyjątek nie zostanie wykonana. Jeśli dodatkowo inne instrukcje objęte są tą samą transakcją, również zostaną wycofane.

Informacja o wystąpieniu wyjątku zwracana jest do sesji użytkownika i np. wyświetlana przez konsolę. Na poniższym przykładzie wystąpił wyjątek ORA-06502 spowodowany próbą przypisania wartości tekstowej do zmiennej liczbowej.

Jako programiści możemy obsługiwać wyjątki tj. definiować działania będące reakcją na określony wyjątek. Nie możemy tego zrobić w trakcie działania programu, musimy przewidzieć możliwość wystąpienia wyjątku i tak przygotować program by potrafił go obsłużyć.

Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.