TWORZENIE PROCEDUR SKŁADOWANYCH I FUNKCJI BAZODANOWYCH

Z WYKORZYSTANIEM NARZĘDZIA

MS SQL SERVER

Warszawa 2007

Spis treści

1. Wprowadzenie

1. Wprowadzenie

Procedury składowane są obiektami zapewniającymi realizację ciągu zdefiniowanych w procedurze operacji w ramach jednego prostego wywołania. Pozwalają na budowanie złożonych (często zagnieżdżonych struktur programistycznych), a potem wywoływanie ich w dowolnych miejscach programu. Umożliwia to tworzenie również takich struktur, które wykonują kilka różnych operacji w ramach uruchomienia jednej procedury.

Funkcje bazodanowe, podobnie jak procedury składowane, są obiektami umieszczonymi na serwerze bazy danych. Posiadają jednak parę dodatkowych cech, którymi odróżniają się od procedur, a znacznie ułatwiają przeprowadzanie całego szeregu tworzących je operacji.

Procedury składowane tworzymy podobnie jak skrypty sql, ale działają one znacznie szybciej niż taki sam skrypt. Dzieje się tak, ponieważ SQL Server przy każdym uruchamianiu skryptu musi wykonać:

• sprawdzenie poprawności wpisanego skryptu,

• sprawdzenie statystyk,

• wykonanie planu zapytania.

Za każdym razem, gdy wykonywany jest skrypt SQL, wszystkie powyższe operacje są realizowane podczas jego uruchomienia. Procedura składowana jest z definicji sprawdzana przez kompilator podczas jej tworzenia, a plan zapytania jest tworzony automatycznie przy jej pierwszym uruchomieniu. Tak więc dodatkowym atutem procedur składowanych jest szybkość ich działania w stosunku do skryptów.

Poniższe ćwiczenia umożliwią zapoznanie się ze składnią procedur i funkcji składowanych w MS SQL Server.

2. Procedury składowane

1. Wprowadzenie

Procedury składowane są zbiorami instrukcji języka SQL zapisanymi pod wspólną nazwą i wywoływanymi jak pojedyncza instrukcja. Procedury składowane umożliwiają:

• przekazywanie parametrów wywołania,

• wykonywanie instrukcji języka SQL - w tym wywoływania innych procedur składowanych,

• zwracanie dowolnej liczby wyników do programu, który wywołał tę procedurę,

• zwracanie informacji o udanej lub niewykonanej procedurze.

Procedury składowane są podstawą przy tworzeniu warstwy bazodanowej systemów informatycznych. Są powszechnie używane w celu:

• implementacji reguł logiki biznesowej,

• zabezpieczenia obiektów bazy danych przed bezpośrednim dostępem użytkowników,

• ochrony bazy danych przed atakami polegającymi na iniekcji kodu SQL,

• poprawy wydajności często wykonywanych instrukcji,

• zminimalizowania obciążenia sieci - zamiast wysyłać całe instrukcje języka SQL, użytkownik wywołuje jedynie procedurę, wysyłając jej nazwę i przekazując parametry jej wywołania.

Wewnątrz procedury możemy umieszczać dowolne instrukcje SQL. Dzięki temu można stworzyć standardową procedurę, która zapewni realizację określonych operacji, np. nada uprawnienia określonemu użytkownikowi albo usunie użytkownika i zapisy z nim związane z bazy danych. Takie grupowanie operacji usprawni obsługę bazy danych i stworzy standardowe podprogramy, które będą możliwe do wielokrotnego wykorzystania w różnych typach zastosowań. Wywołanie procedury z wnętrza programu spowoduje automatyczne pobranie przez nią danych wejściowych i wykonanie wszystkich operacji, które zostały w niej zapisane.

Każda procedura składa się z nazwy oraz parametrów, dzięki którym komunikuje się z programem. Aby utworzyć procedurę składowaną wystarczy wstawić blok instrukcji w szablon tworzący procedurę składowaną: CREATE PROCEDURE.

1. Zadanie 1

Przygotować procedurę składowaną, której zadaniem jest wprowadzenie tabel: Jezyki oraz PracJezyki do bazy danych Kadry oraz wygenerowanie relacji pomiędzy tabelami Pracownicy -> PracJezyki i Jezyki -> PracJezyki.

Tabela Jezyki składa się z jednego pola, które jest jednocześnie kluczem podstawowym, o nazwie Jezyk. Pole to będzie przechowywało nazwy wszystkich możliwych języków, jakimi posługują się pracownicy firmy.

Tabela PracJezyki składa się z trzech pól: idPracJezyki (pole - klucz podstawowy tabeli), idPrac (numer pracownika), Jezyk (nazwa jezyka) oraz Poziom (poziom umiejętności w posługiwaniu się językiem).

2. Wykonanie zadania 1

• Należy odnaleźć w folderze Databases - Kadry - Programmability folder przechowujący procedury składowane - Stored Procedures i z menu podręcznego wybrać opcję New Stored Procedure (pp. Rys. 1)

Rys. 1 Tworzenie nowej procedury składowanej

• W oknie roboczym po prawej stronie pojawi się szablon procedury (pp. Rys. 2).

Rys. 2 Szablon procedury składowanej

• W szablon należy wprowadzić procedurę, która utworzy dwie tabele oraz relacje pomiędzy nimi - zgodnie z wytycznymi podanymi w zadaniu 1 (pp. Rys. 3).

Rys. 3 Treść procedury składowanej Tworzenie_Tabel

• Po wpisaniu treści procedury wystarczy ją uruchomić (przycisk Execute), a następnie odświeżyć folder Stored Procedures i sprawdzić czy nowa procedura składowana została utworzona (pp. Rys. 4)

Rys. 4 Utworzona procedura składowana w folderze Stored Procedures

• Utworzona procedura może być w każdym momencie uruchomiona z menu podręcznego na nazwie procedury - po wybraniu opcji Execute Stored Procedure (pp. Rys. 5).

Rys. 5 Uruchamianie procedury składowanej

UWAGA! Przy ponownym uruchomieniem procedury trzeba z folderu Tables usunąć najpierw tabelę PracJezyki, a następnie tabelę Jezyki.

• Tabele zostały utworzone - co można sprawdzić na liście tabel bazy danych (pp. Rys. 6)

Rys. 6 Nowe tabele na liście bazy danych Kadry

• Należy też uaktualnić strukturę relacji bazy danych (pp. Rys. 7)

Rys. 7 Uaktualniona struktura bazy danych Kadry

1. Zadanie 2

Przygotować procedurę składowaną, której zadaniem jest wprowadzenie do tabel Jezyki i PracJezyki nowych rekordów.

2. Wykonanie zadania 2

• Treść procedury, którą należy utworzyć prezentuje Rys. 8.

Rys. 8 Treść procedury Wprowadzanie_danych

• Procedura powinna spowodować wprowadzenie do tabeli Jezyki 6 nowych rekordów I do tabeli PracJezyki - 40 nowych rekordów (dane oczywiście mogą się różnić w stosunku do zamieszczonego przykładu). Procedura powinna być dostępna na liście procedur składowanych (pp. Rys. 9).

Rys. 9 Uruchamianie procedury Wprowadzanie_danych

1. Zadanie 3

Przygotować procedurę składowaną, której zadaniem jest wyświetlenie informacji o pracownikach i ich umiejętności posługiwania się językami posortowanych według nazw jednostek organizacyjnych (miejsca zatrudnienia pracowników) i według nazwisk pracowników.

2. Wykonanie zadania 3

• Treść procedury składowanej prezentuje Rys. 10.

Rys. 10 Treść procedury Pracownicy_Jezyki

• Po uruchomieniu procedury składowanej (Execute Stored Procedure) można obejrzeć jej wyniki (pp. Rys. 11).

Rys. 11 Wynik działania procedury Pracownicy_Jezyki

1. Zadanie 4

Przygotować procedurę składowaną, której zadaniem jest wyszukanie informacji o pracownikach, którzy posługują się konkretnym językiem (język to parametr procedury) na określonym poziomie (poziom to parametr procedury).

2. Wykonanie zadania 4

• Kolejna procedura składowana to procedura z parametrem. Wynik jej działania będzie uzależniony od tego, jakie wartości parametrów wyspecyfikuje użytkownik w momencie uruchamiania procedury. Treść procedury składowanej prezentuje Rys. 12.

Rys. 12 Treść procedury z parametrem Jezyk_Poziom

• Po wywołaniu procedury (Execute Stored Procedure) pojawi się okno, do którego należy wprowadzić wartości parametrów: nazwę języka i nazwę poziomu (pp. Rys. 13)

Rys. 13 Wprowadzanie wartości parametrów procedury

• Po wprowadzeniu poprawnych wartości parametrów w oknie wyników pojawi się wynik działania procedury składowanej z parametrem (pp. Rys. 14).

Rys. 14 Okno wyników procedury Jezyki_Poziom

1. Zadanie 5

Przygotować procedurę składowaną, której zadaniem jest udzielenie podwyżki (wysokość podwyżki to parametr) wszystkim pracownikom posługującym się w stopniu dobrym lub bardzo dobrym wskazanym językiem (nazwa języka to parametr).

2. Wykonanie zadania 5

• Treść procedury składowanej prezentuje Rys. 15. Instrukcja UPDATE dokona podwyższenia płacy pracowników o wskazaną przez użytkownika wartość podwyżki. Instrukcja SELECT wyświetli dane wszystkich pracowników posługujących się wskazanym przez użytkownika językiem.

Rys. 15 Treść procedury z parametrem Podwyzka

• Instrukcja SELECT została tak napisana, że wyświetli wszystkich pracowników, którzy posługują się danym językiem na wszystkich poziomach (bardzo dobrym, dobrym i niskim). Natomiast podwyżkę otrzymali tylko ci, którzy językiem posługują się na poziomie dobrym lub bardzo dobrym. Dlatego powinna być widoczna różnica w płacy tych pracowników, którzy otrzymali i nie otrzymali podwyżki (pp. Rys. 16 i Rys. 17).

Rys. 16 Parametry procedury Podwyzka

Rys. 17 Wyniki działania procedury Podwyzka

3. Funkcje bazodanowe

1. Wprowadzenie

Funkcje bazodanowe, które użytkownik sam może zdefiniować, a następnie wywoływać je w obrębie różnych skryptów i podprogramów można podzielić na trzy typy:

• inline table-valued,

• multi-statement table-valued,

• scalar-valued.

Funkcje tabelaryczne rozwijane (ang. inline table-valued) zwracają dane w postaci tabeli. Można ich używać zamiast widoków (ang. views). Mają tę przewagę nad widokami, że umożliwiają przekazanie parametru do funkcji.

Funkcje tabelaryczne wielowierszowe (ang. multi-statement table-valued) to najbardziej zaawansowany rodzaj funkcji użytkownika. Podobnie jak funkcje tabelaryczne rozwijane zwracają dane w postaci tabeli, jednak pozwalają na bardziej skomplikowany sposób tworzenia zwracanego zestawu danych. W funkcji tego typu najpierw deklaruje się strukturę zwracanej tabeli. Następnie, aby zbudować zwracaną tabelę, można użyć dowolnych instrukcji Transact SQL, oczywiście z wyjątkiem funkcji wbudowanych - niedostępnych dla funkcji użytkownika. Zadeklarowaną strukturę tabeli traktuje się tak, jak każdą inną tabelę bazy danych i dodaje się do niej kolejne rekordy za pomocą instrukcji INSERT.

Funkcje skalarne (ang. Scalar) zwracają pojedynczą wartość, która może być dowolnego typu obsługiwanego przez MS SQL Server. Funkcje te znajdują zastosowanie np. w ograniczeniach sprawdzających (CHECK CONSTRAINT).

1. Zadanie 1

Przygotować funkcję, która zmieni układ daty ze standardowego formatu: rrrr-mm-dd hh:mm:ss na format niestandardowy: dd-mm-rrrr. Parametrem funkcji powinien być separator daty (dopuszczalny dowolny znak, np. `-`, `:', `/') oraz sama data, która podlega formatowaniu. Zastosować tę funkcję do przeformatowania pól DataUrP i DataZatP w tabeli Pracownicy.

2. Wykonanie zadania 1

• Utworzyć nową funkcję tabelaryczną rozwijaną (pp. Rys. 18)

Rys. 18 Tworzenie funkcji tabelarycznej rozwijanej

• Treść funkcji prezentuje Rys. 19. Funkcja pobiera, jako parametry, datę do formatowania (@Data) i separator tej daty (@Separator), a następnie dołącza do tabeli nowa kolumnę o nazwie DataForm, która zawiera tę samą datę, ale już sformatowaną.

Rys. 19 Treść funkcji Format_Daty

• Po uruchomieniu funkcja zostanie włączona do grupy funkcji Table-valued Functions (pp. Rys. 20)

Rys. 20 Folder funkcji bazodanowych

• Żeby zobaczyć wynik działania funkcji, należy jej treść rozszerzyć o wywołanie zawierające wartości parametrów funkcji: data do formatowania to pole DataUrP, separator daty to znak `/' (pp. Rys. 21).

Rys. 21 Formatowanie pola DataUrP w tabeli Pracownicy

• Funkcję można też wywoływać z innymi wartościami parametrów, np. DataUrP, `:'. Można też wywołanie odnieść do innej tabeli - Dzieci lub Projekty. Na Rys. 22 przedstawiono przykład formatowania daty rozpoczęcia projektu - pole DataRoz w tabeli Projekty. Separatorem daty jest znak dwukropka (:). Przy kolejnym uruchomieniu funkcji w nagłówku kolumny należy zmienić słowo kluczowe CREATE na ALTER (pp. Rys. 22)

Rys. 22 Formatowanie pola DataRoz w tabeli Projekty

1. Zadanie 2

Przygotować funkcję bazodanową, która wyznaczy liczbę pracowników posługujących się na poszczególnych poziomach umiejętności poszczególnymi językami.

2. Wykonanie zadania 2

• Tym razem skorzystamy z funkcji typu tabelaryczna wielowierszowa (pp. Rys. 23), która utworzy nową tabelę (która może być zachowana w charakterze widoku).

Rys. 23 Tworzenie funkcji tabelarycznej wielowierszowej

• Treść funkcji prezentuje Rys. 24. Nowo tworzona tabela @LiczbaPrac składa się z trzech kolumn: Jezyk, Poziom i LiczbaPracow (liczba pracowników, którzy posługują się danym językiem na określonym poziomie). Funkcja Liczba_PracJezyki nie posiada żadnego parametru.

Rys. 24 Treść funkcji Liczba_PracJezyk

• Po dodaniu wywołania funkcji można obejrzeć wyniki jej działania (pp. Rys. 25).

Rys. 25 Wywołanie funkcji Liczba_PracJezyki

1. Zadanie 3

Przygotować funkcję bazodanową, która policzy ile czasu upłynęło pomiędzy wskazanymi datami. Daty są parametrami funkcji. Jeżeli pomiędzy datami upłynęło ponad 365 dni to wynik podawany jest w latach, a w przeciwnym wypadku wynik jest wyznaczany w dniach.

2. Wykonanie zadania 3

• Należy wybrać funkcję skalarną (pp. Rys. 26).

Rys. 26 Tworzenie funkcji skalarnej

• Treść funkcji prezentuje Rys. 27.

Rys. 27 Treść funkcji Jak_Dlugo

• Funkcji można użyć do obliczenia czasu trwania od rozpoczęcia do zakończenia realizacji poszczególnych projektów (pp. Rys. 28)

Rys. 28 Zastosowanie funkcji w tabeli Projekty

• Funkcji można także użyć do obliczenia stażu pracy pracowników (pp. Rys. 29). Przy kolejnym uruchomieniu funkcji należy w nagłówku procedury słowo kluczowe CREATE zastąpić słowem kluczowym ALTER (pp. Rys. 29).

Rys. 29 Zastosowanie funkcji w tabeli Pracownicy

4. Zadanie do wykonania

Przygotować procedury składowane i funkcje bazodanowe dla swojego projektu. Zadanie stanowi element oceniany.

dr inż. T. Ostrowska, dr inż. K. Rostek Zakład Systemów Informatycznych

IOSP PW Bazy Danych - laboratorium

cw 13-14 Procedury składowane i funkcje bazodanowe, 2007-09-25

---