SQL

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak Wprowadzenie do języka PL/SQL Język PL/SQL jest złożonym językiem prog...
Author: Artur Nowacki
5 downloads 4 Views 271KB Size
Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

Wprowadzenie do języka PL/SQL Język PL/SQL jest złożonym językiem programowania firmy Oracle, pozwalającym na uzyskiwanie dostępu do baz danych Oracle z różnych środowisk. Poniżej zostaną omówione przyczyny, które spowodowały konieczność opracowania języka PL/SQL. W końcowej części rozważań zostały dołączone tabele przykładowej bazy danych „Sprzedaż towarów”, która posłuży jako źródło przykładów ilustrujących podstawowe własności i możliwości wykorzystania proceduralnego języka programowania PL/SQL.

Język PL/SQL – informacje wstępne Spośród istniejących na światowym rynku systemów zarządzania relacyjnymi bazami danych jednym z najpopularniejszych (również w Polsce) i najbardziej zaawansowanych technologicznie jest system Oracle firmy Oracle Corporation. Jest aktualnie jednym z częściej wykorzystywanych systemów zarządzania relacyjną bazą danych, przeznaczonym do opracowywania i uruchamiania różnego rodzaju aplikacji – od prostych baz danych w środowiskach klient-serwer, aż choćby po prowadzenie e-handlu w sieci Web. Natomiast PL/SQL jest proceduralnym językiem programowania firmy Oracle,a, a skrót PL/SQL oznacza Procedural Language Structured Query Language. Kod języka PL/SQL działa po stronie serwera, co daje bezpośrednią interakcję z bazą danych Oracle i silnikiem SQL, co jest niewątpliwie jedną z jego głównych zalet. Firma Oracle po raz pierwszy wprowadziła język PL/SQL w roku 1991 w wersji 6.0 swojego systemu zarządzania relacyjną bazą danych (ang. Relational Database Management System – RDBMS). Wraz z rozwojem Oracle’a język PL/SQL był stopniowo udoskonalany poprzez wprowadzanie nowych funkcji oraz poprawianie już istniejących. W Oracle 9i obowiązywała wersja 9.2 lub 9.0 języka PL/SQL, zależnie od tego, czy jest to Oracle 9i Relase 2 (9.2.x) czy Oracle 9i Relase 1 (9.0.x). W wersji Oracle 10g mamy kolejną wersję PL/SQL-a z numerem 10, zaś w wersji Oracle 11g wersję tego języka Relase 1 (11.1). Jak należy rozumieć, iż PL/SQL jest proceduralnym językiem programowania. Otóż komputerowy język programowania jest pewnym sposobem wydawania instrukcji komputerowi. Języki komputerowe charakteryzuje stosunkowo niewielki zasób słownictwa w porównaniu z normalnym językiem, używanym przez człowieka. Ograniczony jest również sposób wykorzystania tego słownictwa – czyli gramatyka języka. Różnice te wynikają z faktu, że komputer interpretuje wszystko dosłownie i nie ma tu miejsca na pewne niedomówienia czy prowadzenie czegoś w rodzaju gier słownych. Mianem proceduralny określa się sposób wydawania komputerowi nie jednej, lecz całej serii uporządkowanych instrukcji, które mają doprowadzić do otrzymania pożądanego wyniku. Języki proceduralne cechuje także posiadanie struktur danych, które służą do przechowywania informacji w celu ich wielokrotnego wykorzystania. Podczas tworzenia programów w takim języku wykorzystywane jest jego sekwencyjne, warunkowe oraz iteracyjne konstrukcje w celu zapisania algorytmu. Ograniczenia języka SQL. Strukturalny język zapytań SQL (Structured Qurey Language) jest językiem, którego podstawą jest teoria zbiorów, a zatem jego zadaniem jest manipulowanie zbiorami danych. SQL składa się ze stosunkowo niewielkiej liczby podstawowych poleceń, takich jak SELECT, INSERT, CREATE czy GRANT. Jest elastycznym i wydajnym językiem, który jest przeznaczony do manipulowania i przetwarzania danych w 1

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

relacyjnych bazach danych. Tym niemniej w praktyce każde z powyżej przytoczonych poleceń wykonuje zadanie, którego zapis w języku strukturalnym mógłby zająć setki linii kodu. Jest to jeden z głównych powodów, dla którego działające na jego podstawie aktualnie dominują na rynku oprogramowania bazodanowego i są aktualnie tak powszechnie wykorzystywane. Mimo wszystko, nazwa „SQL” jest myląca, gdyż w istocie nie jest to język strukturalny, a zdaniem niektórych specjalistów w ogóle nie jest to język programowania. Faktem jest, iż wysokopoziomowe operacje, które oferuje SQL, są bardzo wygodne do wykorzystania przez programistów relacyjnych baz danych, lecz często niestety trzeba wykonywać także inne zadania, niż bezpośrednia manipulacja danymi w bazie danych. SQL nie jest językiem ogólnego przeznaczenia i nie umożliwia tworzenia różnego rodzaju algorytmów. „Program” utworzony w języku SQL, składa się z oczywiście serii jego instrukcji, to jednak nie może zawierać w sobie żadnych instrukcji warunkowych. Ponadto język SQL okazuje się także niewystarczający, np. w sytuacji, gdy zaistniej potrzeba ochrony i szczególnego zabezpieczenia danych. W przypadku, gdy podstawą stosowanej metody zabezpieczania danych jest język SQL, należy uwzględnić fakt, iż administrator bazy danych ma wprawdzie pewną kontrolę nad tym, kto może manipulować danymi, ale nie ma kontroli nad formą tych modyfikacji. Mimo tych wad i rozbieżności jest to jednak aktualnie najbardziej uniwersalny i wykorzystywany język w relacyjnych systemach baz danych takich jak np. serwer Oracle, DB2 firmy IBM, Access czy SQL Server firmy Microsoft. Jednak niewątpliwe wady języka SQL spowodowały konieczność rozszerzenia możliwości programistycznych w bazie danych firmy Oracle, choćby ze względu na możliwość tworzenia konstrukcji proceduralnych niedostępnych w językach czwartej generacji. PL/SQL – jako programowe rozszerzenie języka SQL Rozszerzenie języka jest zbiorem właściwości, które w pewien sposób zwiększają jego możliwości. Tego typu stwierdzenie mogłoby sugerować, iż PL/SQL jest jedynie wersją języka SQL, w pewien celowy sposób rozszerzającą jego możliwości programistyczne. Nie jest to prawdziwym stwierdzeniem, gdyż PL/SQL jest zupełnie odrębnym językiem programowania z własną składnią, własnymi zasadami i własnym kompilatorem. Możliwe jest pisanie w języku PL/SQL, zarówno programów wykorzystujących, jak i nie korzystających z poleceń SQL. Panuje przekonanie, iż PL/SQL stanowi nadbudowę języka SQL, lecz nie jest stwierdzenie do końca prawdziwe, gdyż z poziomu PL/SQL można w prosty sposób wykonywać tylko najpowszechniej stosowane polecenia języka SQL. Język PL/SQL rozszerza możliwości języka SQL dzięki wprowadzeniu konstrukcji występujących w innych językach proceduralnych, np.: zmienne i typy danych są zarówno predefiniowane, jak i definiowane przez samego użytkownika, istnieją struktury sterowania programu, np. instrukcja IF-THEN-ELSE oraz pętle, oczywiście mamy możliwość tworzenia konstrukcji proceduralnych, jakimi są procedury i funkcje mamy możliwość tworzenia typów obiektowych i metod (dostępne od wersji Oracle8). 2

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

Można zatem wysunąć stwierdzenie, iż PL/SQL jest blisko związany z językiem SQL, ale jednocześnie umożliwia tworzenie programów w formie uporządkowanej serii poleceń. Ponieważ konstrukcje proceduralne są w pełni zintegrowane z Oracle SQL, to takie powiązanie tworzy język strukturalny o dużych możliwościach. Głównymi cechami charakterystycznymi języka programowania PL/SQL są: złożony język programowania, rezyduje bezpośrednio na danych, zintegrowany z serwerem bazy danych Oracle, dostępny w niektórych programach narzędziowych Oracle działających po stronie klienta, pozwala na uzyskiwanie dostępu do baz danych Oracle z różnych środowisk, baza danych Oracle pełni funkcję mechanizmu dla języka PL/SQL, mechanizm języka PL/SQL jest połączony z bazą danych.

PL/SQL – powiązanie cech języka trzeciej i czwartej generacji Języki trzeciej generacji (3GL), takie jak np. C lub COBOL, są językami proceduralnymi. Oznacza to, iż program utworzony w języku opisuje algorytm umożliwiający rozwiązanie określonego zagadnienia krok po kroku. Języki obiektowe, takie jak C++ czy Java, również są językami trzeciej generacji. Chociaż implementują obiekty, to jednak w dalszym ciągu to programy w nich zaimplementowane określają algorytmy wykonywane krok po kroku. Języki czwartej generacji (4GL), takie jak np. SQL, są na ogół proste (w porównaniu z językami 3GL) i udostępniają mniejsza liczbę poleceń. Fakt, iż język SQL jest językiem czwartej generacji implikuje, iż opisuje on tylko żądanie zadania, a nie sposób jego wykonania. Ponadto języki 4GL izolują również użytkownika od wewnętrznych struktur danych i algorytmów. Poniższy schemat prezentuje porównanie możliwości języka SQL jako języka 4GL z możliwościami charakterystycznymi dla języków 3GL. wydajność prostota język SQL

manipulowanie danymi przetwarzanie danych opisuje żądane zadanie, a nie sposób ich wykonania izolacja od wewnętrznych struktur danych i algorytmów

język czwartej generacji (4GL) 3

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

języki proceduralne (np. C, COBOL) Języki trzeciej generacji (3GL) języki obiektowe (np. C++, Java) program jest wynikiem algorytmu umożliwiającego rozwiązanie określonego zagadnienia krok po kroku. Rysunek 1.1. Schemat porównawczy możliwości SQL jako języka 4GL z możliwościami języków 3GL Cechy i możliwości PL/SQL – jako języka (3GL) i języka (4GL).  konstrukcje proceduralne są w pełni zintegrowane z Oracle SQL,  język strukturalny o dużych możliwościach, w którym jednocześnie możemy wykorzystywać zarówno elementy programistyczne charakterystyczne dla języków czwartej generacji i elementy programistyczne charakterystyczne dla języków trzeciej generacji  łączy on w sobie elastyczność i prostotę języka SQL z wydajnością i możliwościami konfigurowania charakterystycznymi dla języka trzeciej generacji (cecha główna), zintegrowane są w nim niezbędne konstrukcje proceduralne, możliwość uzyskania dostępu do bazy danych. Wyjątkowość języka PL/SQL polega na tym, iż łączy on w sobie elastyczność języka SQL jako języka czwartej generacji, z wydajnością i możliwościami konfigurowania języka trzeciej generacji. W języku PL/SQL zintegrowane są zarówno niezbędne konstrukcje proceduralne, jak i możliwości uzyskiwania dostępu do bazy danych Oracle. W efekcie powstał wydajny język programowania o ogromnym potencjale, umożliwiający tworzenie złożonych aplikacji. Język PL/SQL wykorzystuje wiele koncepcji programowanie strukturalne, statyczne typy danych, zarządzanie wyjątkami, przetwarzanie równoległe, znanych z języka Ada, który powstał na potrzeby departamentu obrony USA i był projektowany dla systemów wbudowanych czasu rzeczywistego o wysokiej niezawodności (np. tych stosowanych w samolotach i rakietach). Z kolei w języku Ada zapożyczono wiele elementów z języka Pascal, np. operatory przypisania, operatory przypisania, apostrofy d określania granic ciągów znaków. Zastosowanie języka Ada jako podstawy miało sens z wielu powodów. Jednym z najważniejszych był fakt, iż także język SQL wykorzystuje apostrofy ograniczające ciągi znaków, operatory i deklaratywne typy skalarne 4

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

znane z Pascala. Jeżyki stosujące typy deklaratywne często nazywa się językami z silną kontrolą typów. Tego typu silna kontrola okazuje się niezbędna do poprawnego powiązania typów SQL i PL/SQL. Zastosowanie podobnej składni (operatory, apostrofy) oznacza uproszczoną analizę kodu, ponieważ PL/SQL jest rozszerzeniem jęzka SQL, a zatem SQL jest jego podzbiorem. Zalety języka PL/SQL i korzyści płynące z jego wykorzystania to: Możliwości proceduralne – główną zaletą PL/SQL jest zdolność do definiowania i implementowania konstrukcji języka (3GL) za pomocą osadzonych instrukcji SQL. Istnieje możliwość zaimplementowania standardowych możliwości języka 3GL, takich jak np. logika sekwencyjna, warunkowa i iteracyjna, obsługa logicznych typów danych, możliwość przechowywania i wykorzystania tablic i podprogramów oraz elementów obiektowych. Ponieważ wszystkie te cechy można wykorzystywać integrując je odpowiednio z kodem SQL, w ten sposób PL/SQL zyskał na znaczeniu i efektywności wykorzystania. Możliwość definiowania konstrukcji 3GL jest niezbędna zwłaszcza wówczas, gdy używamy PL/SQL do pisania aplikacji, które mają uwzględnić skomplikowaną strukturę i niezbędną w danym zastosowaniu logikę. Należy również wspomnieć o fakcie, iż PL/SQL jest głównym proceduralnym językiem dla narzędzi Oracle po stronie klienta, takich jak Oracle Forms i Oracle Reports. Przenośność – programy napisane w PL/SQL są niezależne od sprzętu i systemu operacyjnego. Można je z powodzeniem przenosić i będą działały na każdej platformie programowej, gdzie zainstalowano serwer Oracle i zintegrowane środowisko programowania (ang. Integrated Development Enviroment – IDE). Z reguły wyróżniamy trzy rodzaje środowisk potrzebnych każdemu językowi programowania, co w przypadku PL/SQL ma także miejsce: 1. środowisko projektowania 2. środowisko wykonania 3. środowisko debuggowania Dwoma głównymi środowiskami programistycznymi przygotowanymi przez firmę Oracle, które są przeznaczone do projektowania i wykonywania programów PL/SQL, są SQL*Plus i Procedure Builder. Te środowiska IDE nie tylko umożliwiają projektowanie i wykonywanie programów PL/SQL, ale również pomagają w ich debuggowaniu, przy czym różnią się swoimi interfejsami. Pełna integracja z SQL – PL/SQL jest ściśle zintegrowany z językiem SQL. Dzięki temu programy napisane w języku PL/SQL mogą wykorzystywać prawie wszystkie elementy właściwe dla języka SQL. Poza tym, częścią Oracle jest zintegrowany parser SQL i PL/SQL. Oznacza to, iż ten sam parser jest używany dla kodu SQL i PL/SQL, a to z kolei znacznie poprawia efektywność pracy. Modularność – język PL/SQL pozwala pisać programy jako niezależne moduły, które potem można integrować. Cecha ta jest szeroko stosowana w przypadku implementowania i wykorzystywania takich elementów języka PL/SQL, jak procedury, funkcje, czy też pakiety. W języku PL/SQL zaimplementowano modułowość niemalże w sposób identyczny jak w języku Ada. 5

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

Lepsza wydajność –o lepszej wydajności PL/SQL możemy mówić w kontekście 3 poniższych czynników: Konwersja typów danych nie jest wymagana ani na wejściu, ani na wyjściu. Kiedy dane są wczytywane do zmiennych PL/SQL albo kiedy wprowadzamy je do bazy danych Oracle za pośrednictwem tych zmiennych, PL/SQL zajmuje się odpowiednio konwersją pomiędzy serwerem Oracle i PL/SQL, wykorzystując przy tym ten sam wewnętrzny format danych. Należy tu podkreślić tę niewątpliwą zaletę języka PL/SQL, gdyż w przypadku innych programów 3GL, napisanych w takich językach jak np. C++ lub Javie, aby korzystać z bazy danych Oracle, wymagana jest konwersja danych z formatu Oracle do formatu C++ lub Java i odwrotnie. W ogólnym przypadku aplikacje bazodanowe, które działają w środowisku klient-serwer, są instalowane albo w architekturze dwuwarstwowej (system RDBMS Oracle na serwerze, zaś aplikacja PL/SQL na kliencie), albo w architekturze trójwarstwowej (ang. three-tier, warstwa serwera aplikacji na serwerze aplikacji, zaś warstwa prezentacyjna na kliencie). PL/SQL pozwala na zgromadzenie zestawu instrukcji SQL (wspólnie z logiką aplikacji) w bloku PL/SQL, który następnie zostaje przesłany do serwera Oracle. Takie rozwiązanie podnosi wydajność, gdyż umożliwia zmniejszenie obciążenia sieci i prowadzi do szybszego działania aplikacji. Nawet jeśli programy klienta i serwera działają na tym samym komputerze, to zastosowanie omawianego rozwiązania powoduje wzrost wydajności przetwarzania. W takim przypadku, nie zmniejszenie obciążenia sieci, lecz możliwość łączenia poleceń języka SQL w pakiety daje w efekcie prostszy program, który powoduje mniejszą liczbę odwołań do bazy danych. Ponieważ język PL/SQL umożliwia łączenie poleceń SQL w pakiety, to takie rozwiązanie korzystnie wpływa również na wydajność w modelu trójwarstwowym. W tym przypadku klient (często działający w środowisku przeglądarki WWW) komunikuje się z serwerem aplikacji, który z kolei przesyła informacje do bazy danych. To właśnie w tej ostatniej komunikacji mają zastosowanie właściwości języka PL/SQL.

SQL

Serwer Bazy

Serwer Bazy

Danych Oracle

Danych Oracle

SQL

SQL SQL

... SQL ... SQL ...

Aplikacja klienta

Aplikacja klienta Stosowanie SQL

Stosowanie PL/SQL

Rysunek 1.2. PL/SQL w środowisku oprogramowania klient-serwer 6

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

PL/SQL od Oracla 9i wprowadza pierwotną kompilację kodu PL/SQL, a następnie konwersję do języka C i przechowywanie w kodzie maszynowym. Dzięki temu wykonywanie programów odbywa się szybciej. W starszych wersjach baz Oracle (przed wersją Oracle9i) interpretowane wersje kodu PL/SQL, znane jako p-kod (ang. p-code), tylko w nieznacznym stopniu wpływały na szybsze wykonywanie kodu PL/SQL. Obiektowość – język PL/SQL ma wiele cech języków obiektowych, np. abstrakcyjność (możliwość definiowania abstrakcyjnych typów danych), dziedziczność, polimorfizm i dynamiczne szeregowanie metod, hermetyzacja, itd. Należy podkreślić, iż tak naprawdę mechanizmy programowania obiektowego na dobrą sprawę zostały wprowadzone do języka PL/SQL dopiero w wersji 9i., chociaż już w jego wcześniejszych wersjach mamy możliwość programowania, które możemy określić mianem pseudoobiektowego. Język programowania PL/SQL łączy w sobie duże możliwości i elastyczność języka czwartej generacji (4GL) SQL z konstrukcjami proceduralnymi języka trzeciej generacji (3GL). Jego składnia jest w dużej mierze oparta na języku Ada, zaś po opracowanej przez firmę Oracle wersji SQL dziedziczy przestrzeń typów danych oraz wiele wbudowanych funkcji. Jest proceduralnym językiem programowania, który obsługuje takie jednostki programowe jak choćby procedury, funkcje, pakiety oraz wyzwalacze. PL/SQL jest to zatem język programowania umożliwiający tworzenie złożonych aplikacji.

Ograniczenia PL/SQL Mimo że użyteczność języka PL/SQL nie podlega dyskusji, to jednak nie wszystko, co jest związane z przetwarzaniem danych i programowaniem operacji wykonywanych w aplikacjach bazodanowych daje się wykonać przy użyciu języka PL/SQL. Istnieją pewne ograniczenia w jego użyciu, jak również pewne elementy programowe, których nie można zaimplementować za pomocą PL/SQL. Niewielka liczba konstrukcji służących do interakcji z użytkownikiem. Mimo, iż w języku PL/SQL istnieje wiele konstrukcji służących do zarządzania danymi w bazie danych, to nie istnieje zbyt wiele struktur programowych wspomagających bezpośrednią interakcję z użytkownikiem. Wynika to z faktu, iż w samym języku PL/SQL nie ma instrukcji służących do połączenia z baz danych lub ustanowienia sesji. To zadanie wykonywane jest przez środowisko przesyłające polecenia PL/SQL. Co prawda, w przypadku programu utworzonego za pomocą języka PL/SQL istnieje możliwość pobierania informacji wyjściowej w postaci tekstowej, ale nie da się w łatwy sposób pobierać informacji od użytkownika. Fakt ten wymusza zazwyczaj na programiście napisanie tzw. fronton (ang. front-end), czyli użycie aplikacji innego języka programowania. Taki fronton przekazuje informacje wejściowe do PL/SQL. Jedną z możliwości wykorzystania takiego frontonu w aplikacji języka PL/SQL jest oparcie go na wykorzystaniu przeglądarki stron WWW. 7

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

Język wewnętrzny. Język PL/SQL został stworzony przez firmę Oracle i, co jest sprawą oczywistą, jest jej własnością. Z faktu tego wynika chyba największe ograniczenie związane z możliwością wykorzystania języka PL/SQL aplikacjach bazodanowych. Jest on niestety bezużyteczny w przypadku systemu bazodanowego należącego do innego producenta. Wynika to z faktu, iż aby skompilować i uruchomić program języka PL/SQL, należy przekazać go do mechanizmu PL/SQL, a ten oczywiście związany jest z serwerem baz danych firmy Oracle. Wprawdzie istnieje możliwość zintegrowania serwera baz danych Oracle z serwerami innych producentów, tym niemniej kod języka PL/SQL może być wykorzystywany wyłącznie na serwerze Oracle. Taka sytuacja jest niewygodna szczególnie dla niezależnych dostawców oprogramowania, którzy zazwyczaj preferują tworzenie takiego oprogramowania, które mogłoby współpracować z różnymi systemami obsługi baz danych. Stąd z kolei oczywistym staje się fakt, iż jest mało realnym fakt, iż największe firmy zdecydują się uznać język PL/SQL za standard tworzenia proceduralnych programów obsługi baz danych. Ograniczone możliwości programowania zorientowanego obiektowo. Aż do pojawienia się wersji 9i. języka PL/SQL nie posiadał on żadnych mechanizmów wspierających programowanie zorientowane obiektowo, chociaż wersja 8. oferowała już wsparcie abstrakcyjnych typów danych, zaś programowanie pseudo-obiektowe było już nawet możliwe w wersji 7. języka PL/SQL. Dopiero jednak wprowadzenie systemu Oracle9i umożliwiło udostępnienie większej liczby mechanizmów programowania obiektowego, takich jak np.: wielopoziomowe kolekcje danych (ang. multi-level collections), zwane też kolekcjami, dziedziczenie (ang. inheritance), polimorfizm w czasie wykonywania (ang. runtime polymorphism). Nadal jednak istnieją pewne ograniczenia możliwości programowania obiektowego, jak np. brak metod prywatnych (ang. private methods). Podsumowując omawiane ograniczenia języka PL/SQL można stwierdzić, iż jednak prawdopodobnie największy problem dotyczący jego użytkowania (w oparciu o tabele SQL-owe) związany jest z pobieraniem danych (z tych tabel). Wynika to z faktu, że SQL jest językiem zorientowanym na obsługę zbiorów (ang. setoriented), zaś języki proceduralne (a takim jest właśnie język PL/SQL) są zorientowane na obsługę rekordów (ang. record-orineted). Pobieranie danych z bazy danych jest więc zadaniem nieco problematycznym, ponieważ wewnętrzne struktury i operatory języków SQL i PL/SQL nie są do końca zgodne.

8

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

Przykładowa relacyjna baza danych „Sprzedaż towarów”. Przykłady wykorzystane w dalszej części rozważań dotyczących własności i możliwości wykorzystania języka PL/SQL opierają się na zestawie tabel bazy danych służących do obsługi sprzedaży towarów w hurtowni. Zestaw ten składa się pięciu tabel: pracownicy, towary, klienci, faktury, pozycje, które zawierają niezbędne dane umożliwiające wykorzystanie tego systemu bazodanowego w tym konkretnym przeznaczeniu, jakim jest obsługa pracy hurtowni towarów. Poniżej została przedstawiona struktura tych tabel oraz instrukcje SQL konieczne do ich utworzenia. Tabela pracownicy Tabela pracownicy zawiera informacje dotyczące pracowników zatrudnionych w hurtowni. CREATE TABLE pracownicy ( id

NUMBER(2) CONSTRAINT pracownicy_PK PRIMARY KEY,

nazwisko

VARCHAR2(15) NOT NULL,

imie

VARCHAR2(15) NOT NULL,

data_ur

DATE,

data_zatr

DATE,

plec

CHAR(1) NOT NULL CONSTRAINT prac_plec_CH CHECK(plec='K' OR plec='M'),

stanowisko

VARCHAR2(25),

szef_id

NUMBER(2) REFERENCES pracownicy

); INSERT INTO pracownicy VALUES(1,'borsuk','Jan',To_Date('21/10/1965','dd/mm/yyyy'),To_Date('11/06/1999','dd/mm/yyyy'),'M','Dyrekt or',1); INSERT INTO pracownicy VALUES(2,'kotecki','Adam',To_Date('21/11/1968','dd/mm/yyyy'),To_Date('01/10/1998','dd/mm/yyyy'),'M','Kie rownik d/s sprzedaży',1); INSERT INTO pracownicy VALUES(3,'olek','Tadeusz',To_Date('23/10/1975','dd/mm/yyyy'),To_Date('05/03/2001','dd/mm/yyyy'),'M','Kier ownik d/s marketingu',1); INSERT INTO pracownicy VALUES(4,'Krawczyk','Adam',To_Date('02/06/1975','dd/mm/yyyy'),To_Date('21/10/2001','dd/mm/yyyy'),'M',' Sprzedawca',2); INSERT INTO pracownicy 9

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

VALUES(5,'Lisiak','Anna',To_Date('05/12/1968','dd/mm/yyyy'),To_Date('01/01/1998','dd/mm/yyyy'),'K','Sprze dawca',2); INSERT INTO pracownicy VALUES(6,'Kowalska','Ewa',To_Date('11/05/1979','dd/mm/yyyy'),To_Date('05/09/2001','dd/mm/yyyy'),'K','Sp rzedawca',2); INSERT INTO pracownicy VALUES(7,'Nowaczyk','Maria',To_Date('14/08/1963','dd/mm/yyyy'),To_Date('01/01/1998','dd/mm/yyyy'),'K',' Główny Księgowy',1); INSERT INTO pracownicy VALUES(8,'Marczyk','Barbara',To_Date('18/03/1984','dd/mm/yyyy'),To_Date('01/03/2002','dd/mm/yyyy'),'K',' Sprzedawca',2); INSERT INTO pracownicy VALUES(9,'Kraska','Katarzyna',To_Date('07/06/1983','dd/mm/yyyy'),To_Date('15/09/2002','dd/mm/yyyy'),'K',' Sprzedawca',2); INSERT INTO pracownicy VALUES(10,'Michalak','Anna',To_Date('17/08/1970','dd/mm/yyyy'),To_Date('04/03/2001','dd/mm/yyyy'),'K',' Referent d/s księgowości',7); Tabela towary Tabela towary zawiera informacje odnośnie asortymentu towarów, które są oferowane do sprzedaży w hurtowni. CREATE TABLE towary ( id

NUMBER(2) CONSTRAINT towary_PK PRIMARY KEY,

nazwa

VARCHAR2(30) NOT NULL,

cena

NUMBER(7,2) NOT NULL,

stan

NUMBER(7,2) NOT NULL,

wycofany

CHAR(3) NOT NULL CONSTRAINT towar_wycofany_CH CHECK(wycofany='Tak' OR wycofany='Nie')

); INSERT INTO towary VALUES (1,'czekolada mleczna',2.10,1200,'Nie'); INSERT INTO towary VALUES (2,'masło',3.50,120,'Tak'); 10

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

INSERT INTO towary VALUES (3,'truskawki',4.20,1550,'Nie'); INSERT INTO towary VALUES (4,'piwo Pilsener',1.40,5500,'Nie'); INSERT INTO towary VALUES (5,'szprot w oleju',1.60,50,'Tak'); INSERT INTO towary VALUES (6,'czekolada deserowa',2.20,2600,'Nie'); INSERT INTO towary VALUES (7,'cukier',3.10,9250,'Nie'); INSERT INTO towary VALUES (8,'chleb',1.80,1030,'Nie'); INSERT INTO towary VALUES (9,'napój jabłkowy',2.00,1850,'Nie'); INSERT INTO towary VALUES (10,'śmietana',1.70,1060,'Tak'); Tabela klienci Tabela pracownicy zawiera informacje dotyczące klientów, którzy dokonują zakupów w hurtowni. CREATE TABLE klienci ( id

NUMBER(2) CONSTRAINT klienci_PK PRIMARY KEY,

nazwa

VARCHAR2(40) NOT NULL,

kraj

VARCHAR2(25)

); INSERT INTO klienci VALUES(1,'Agrotex','Polska'); INSERT INTO klienci VALUES(2,'Mar-Trans','Polska'); INSERT INTO klienci VALUES(3,'ABC','Polska'); INSERT INTO klienci VALUES(4,'France Restauration','Francja'); 11

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

INSERT INTO klienci VALUES(5,'Johns Markets','USA'); INSERT INTO klienci VALUES(6,'White Clover Markets','USA'); INSERT INTO klienci VALUES(7,'Alfreds Futterkiste','Niemcy'); INSERT INTO klienci VALUES(8,'Frankenversand','Niemcy'); INSERT INTO klienci VALUES(9,'Morgenstern Gesundkost','Niemcy'); INSERT INTO klienci VALUES(10,'Zajazd Wolski','Polska'); Tabela faktury Tabela faktury zawiera informacje dotyczące faktur, które dotyczą sprzedaży produktów w hurtowni. CREATE TABLE faktury ( nr

NUMBER(2) NOT NULL,

kl_id

NUMBER(2) NOT NULL,

prac_id

NUMBER(2) NOT NULL,

data_w

DATE,

fracht

NUMBER(7,2) DEFAULT 0 NOT NULL,

CONSTRAINT faktury_PK PRIMARY KEY(nr), CONSTRAINT klienci_FK FOREIGN KEY(kl_id) REFERENCES klienci(id), CONSTRAINT pracownicy_FK FOREIGN KEY(prac_id) REFERENCES pracownicy(id) ); INSERT INTO faktury VALUES(1,1,4,to_date('01/03/2005','dd/mm/yyyy'),20.50); INSERT INTO faktury VALUES(2,1,5,to_date('12/03/2005','dd/mm/yyyy'),11.00); INSERT INTO faktury VALUES(3,1,5,to_date('21/04/2005','dd/mm/yyyy'),0); INSERT INTO faktury VALUES(4,7,5,to_date('25/04/2005','dd/mm/yyyy'),0); 12

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

INSERT INTO faktury VALUES(5,2,6,to_date('04/03/2005','dd/mm/yyyy'),10.20); INSERT INTO faktury VALUES(6,2,4,to_date('10/04/2005','dd/mm/yyyy'),0); INSERT INTO faktury VALUES(7,3,8,to_date('01/03/2005','dd/mm/yyyy'),0); INSERT INTO faktury VALUES(8,4,9,to_date('01/03/2005','dd/mm/yyyy'),7.50); INSERT INTO faktury VALUES(9,5,4,to_date('05/05/2005','dd/mm/yyyy'),0); INSERT INTO faktury VALUES(10,5,4,to_date('09/05/2005','dd/mm/yyyy'),15.20); INSERT INTO faktury VALUES(11,6,8,to_date('19/03/2005','dd/mm/yyyy'),0); INSERT INTO faktury VALUES(12,8,5,to_date('17/03/2005','dd/mm/yyyy'),0); Tabela pozycje Tabela pozycje zawiera opis faktur, które dotyczą sprzedaży towarów w hurtowni. CREATE TABLE pozycje ( nr

NUMBER(2) NOT NULL,

towar_id NUMBER(2) NOT NULL, ilosc

NUMBER(7,2) NOT NULL,

cena_s

NUMBER(7,2) NOT NULL,

CONSTRAINT pozycje_PK PRIMARY KEY(nr, towar_id), CONSTRAINT towary_FK FOREIGN KEY(towar_id) REFERENCES towary(id), CONSTRAINT faktury_FK FOREIGN KEY(nr) REFERENCES faktury(nr) ); INSERT INTO pozycje VALUES(1,1,30,2.10); INSERT INTO pozycje VALUES(1,2,5,1.10); INSERT INTO pozycje 13

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

VALUES(1,3,10,3.50); INSERT INTO pozycje VALUES(2,4,300,1.25); INSERT INTO pozycje VALUES(2,7,55,3.10); INSERT INTO pozycje VALUES(2,10,12,1.70); INSERT INTO pozycje VALUES(3,4,80,1.40); INSERT INTO pozycje VALUES(3,3,10,4.00); INSERT INTO pozycje VALUES(4,8,70,1.80); INSERT INTO pozycje VALUES(4,9,60,2.00); INSERT INTO pozycje VALUES(4,10,8,1.70); INSERT INTO pozycje VALUES(5,4,300,1.20); INSERT INTO pozycje VALUES(6,6,25,2.20); INSERT INTO pozycje VALUES(6,1,20,2.10); INSERT INTO pozycje VALUES(6,3,12,4.20); INSERT INTO pozycje VALUES(7,4,70,1.40); INSERT INTO pozycje VALUES(7,9,60,2.00); INSERT INTO pozycje VALUES(8,2,5,1.20); INSERT INTO pozycje VALUES(8,7,100,3.10); INSERT INTO pozycje VALUES(8,10,12,1.70); 14

Wykład 1 – Wprowadzenie do języka PL/SQL – dr hab. prof. nadzw.Tadeusz Antczak

INSERT INTO pozycje VALUES(9,4,500,1.10); INSERT INTO pozycje VALUES(10,1,12,2.10); INSERT INTO pozycje VALUES(10,7,30,3.10); INSERT INTO pozycje VALUES(10,8,18,1.80); INSERT INTO pozycje VALUES(10,9,40,2.00); INSERT INTO pozycje VALUES(11,3,30,3.50); INSERT INTO pozycje VALUES(12,9,30,2.00); INSERT INTO pozycje VALUES(12,4,12,1.40); Tabela dziennik_bledow Tabela dziennik_bledow jest wykorzystywana do rejestrowania błędów systemu Oracle w przykładach języka PL/SQL zaprezentowanych poniżej. CREATE TABLE dziennik_bledow ( kod

NUMBER NOT NULL,

komunikat

VARCAHR2(200)

); Tabela tabela_tymcz Tabela tabela_tymcz jest stosowana do przechowywania tymczasowych danych w przykładach języka PL/SQL zaprezentowanych poniżej, które niekiedy nie mają żadnego związku z informacjami dotyczącymi rozważanej aplikacji bazodanowej „Sprzedaż towarów”, a służą analizy i prezentacji pewnych, konkretnych własności języka PL/SQL. CREATE TABLE tabela_tymcz ( kol_num

NUMBER,

kol_znak

VARCAHR2(2000)

);

15