TRANSKRYPCJA

Witam serdecznie, nazywam się Paweł Niemyt i jestem trenerem Altkom Akademii. Na co dzień zajmuję się szkoleniami z SQL-a, z VBA, z aplikacji MS Project, czyli ogólnie zajęciami wspierającymi nas w zarządzaniu projektami i dzisiaj chciałbym powiedzieć parę słów na temat procedur składowanych.

Co to są procedury składowane? SQL Server daje nam możliwość zapisywania kodu T-SQL-a po stronie serwera w postaci procedur składowanych, zwanych stored procedures. Są one specjalnie zaprojektowane do zapytań z grupy DQL i DML. Co to znaczy DQL i DML? DQL to jest Data Query Language, czyli select tak na dobrą sprawę, natomiast DML to jest Data Manipulation Language, czyli zapytania typu insert.

A więc jeżeli chodzi o procedury składowane, możemy zapisywać procedurami składowanymi selecty, update’y, inserty, delete’y, w odróżnieniu od widoku, gdzie też zapisujemy coś po stronie serwera, ale to są tylko jakby definicje, to są selecty, czyli tam nie ma żadnych update’ów.

Natomiast my jeżeli chodzi o procedury składowane możemy po stronie serwera zapisać wszystkie jakby te zapytania funkcyjne, tak; czyli za pomocą tego kodu T-SQL-owego, czyli Transacta, Transact-SQL, możemy zapisać selecty, inserty, delete’y, update’y, tak jak wcześniej powiedziałem. Nazywa się je również procedurami typu crud, czyli c, r, u, d, czyli create, read, update and delete, tak, czyli crud. Bez zbędnych wstępów chciałbym pokazać po prostu jak to wygląda i zaczniemy od prostych procedur, które pokażę na kilku przykładach.

Przeniesiemy się teraz, sezam, i pokażemy kilka prostych procedur. Pierwszą prostą procedurą, której użyjemy to będzie procedura składowana, która będzie zwracać nam selecta, z wynikami, z listą towarów, w odpowiednim przedziale kwotowym. Zacznijmy od zapisania selecta, który nam to pokaże, from tabela towary, nazwa towaru, cena katalogowa, where cena katalogowa=5 and cena katalogowa i = 10. Jeszcze zrobimy jakieś order_by i mamy listę towaru.

Jak widzimy tutaj, w nazwie towaru mamy spację z przodu; jeżeli ktoś chciałby się spacji pozbyć, możemy jeszcze użyć funkcji T-SQL-owej LTRIM, która nam usunie spację z lewej strony. I mamy LTRIM od left, jest jeszcze RTRIM, czyli right, usuwa nam spacje od lewej i prawej strony, od wersji SQL-a 2017 mamy również funkcję TRIM, która usuwa spacje i z przodu i z tyłu. Natomiast jeżeli ktoś ma starszą wersję SQL Servera, musi używać funkcji LTRIM i RTRIM jedno w drugim żeby uzyskać efekt usunięcia spacji z przodu i z tyłu.

Przeróbmy to teraz na procedurę składowania. Zacznijmy od create; zanim utworzę jakikolwiek obiekt, który będzie przywoływany na serwerze to używam create procedure, nazwa procedury. Ja procedury składowane poprzedzam zawsze „p”, od procedur; można sp, można ps, jak kto woli, jakiś prefiks powinien być żeby było wiadomo, że to jest procedura składowana. Nazwiemy to towaryCenaOdDo, wtedy będzie jasne o co tu chodzi.

No i teraz tak – ta nasza procedura będzie posiadała dwa parametry. Pierwszy parametr to będzie to, czyli cena katalogowa od, a drugi parametr to będzie cena katalogowa. Teraz tak, jeżeli chodzi o te dwa parametry to musimy je podać od razu za nazwą lub po nazwie, to nie ma dużego znaczenia bo jeżeli chodzi o linijki to podział na linijki nie jest kluczowy.

Więc teraz tak, nazwę sobie pierwszy parametr „cena od” jakiego typu, no najlepiej money jeżeli mają być to kwoty, @cena do, też do typu money. Ważne jest to, i tu warto zawsze do tego dość dużą uwagę przywiązać, aby parametry, które będą przekazywane do zapytań, obojętnie jakich, czy do insertów, czy do selectów, czy delete’ów były zgodne z typami, które mamy w SQL-u, w tabelach naszych. Bo jeżeli tak nie będzie no to niestety może być problem ponieważ może najzwyczajniej w świecie ten wynik nie wyjść.

Zobaczmy, że tam podświetla nam się, że kolumna, cena katalogowa, to jest typ money więc tu też taki zastosowałem aby było to wszystko zgodne. No i teraz tak, wpisujemy S i to jest nasze jakby ciało procedury, tutaj wewnątrz będziemy już mieć to co chcemy pod tą procedurą przechować. Ja sobie skopiuję to zapytanie, C tylko zmienię, zrobię sobie coś takiego, że zamiast tego to tu będzie @cena_od, @cena_do.

I to tak naprawdę wszystko. Zaznaczam to wszystko, create, widać, że się wykonało, co nie oznacza wcale, że jest poprawnie, to znaczy, że nie mamy tu żadnego błędu składniowego, natomiast może się okazać, że w ogóle nie ma sensu ta procedura i wyniki będą bez sensu albo w ogóle ich nie będzie. Natomiast to oznacza, że nie popełniliśmy żadnego błędu składniowego, związanego z tworzeniem procedury składowanej, nie ma tu żadnego takiego błędu.

Tu nam podkreśla create bo brakuje jeszcze tego separator bash; jeżeli tworzymy właśnie jakieś obiekty, trzeba go powrzucać aby oddzielać jedną część od drugiej. Ja to jakoś dodałem, to widzimy, przestało nam się to podkreślać, natomiast to nie miało wpływu na to, czy to się wykonało, czy nie. No i teraz tak, jakby jakieś procesy działały, no to mogłoby to wtedy to zakłócić.

Natomiast teraz tak; jeżeli chodzi o to gdzie ona się utworzyła, ponieważ pracujemy na bazie sezam, no to znajdziemy ją w bazie sezam, a otworzymy sobie tutaj, mamy taki folder, tu mamy storage procedure, tu są akurat systemowe, trzeba to wszystko odświeżyć no i teraz to będzie wszystko ładnie wyglądało. Sezam, storage procedure, no i tu nasza procedura składowana jest. No i ona jest zapisana już na serwerze.

Jak się odwołuje procedury składowane? Polecenie, wpisujemy nazwę procedury, tu mamy kilka już procedur jakichś tam zdefiniowanych, natomiast ja sobie wybieram tę naszą i teraz ona wymaga podania tych parametrów. Oznacza to, że jakby ktoś chciał odpalić tę procedurę bez podania tych parametrów, to ona nie zadziała. Więc muszę podać pierwszy parametr, na przykład 5,10. I teraz jak zaznaczę to, to się wykona.

No i w ten oto sposób mamy stworzoną prostą procedurę składowaną, która wyświetla mi towary do pewnego przedziału, mogę oczywiście przedziały sobie zmieniać dowolnie, wpisywać sobie różne inne wartości i będę miał wyniki.  Czyli tu sobie zapisałem zwykłego selecta, który jest teraz przechowywany na serwerze. Zrobimy sobie jeszcze jedną procedurę składowaną; teraz procedura składowana będzie nam zmieniać, czyli będzie nam modyfikować cenę towaru, całej kategorii, czyli będzie nam na przykład podnosić cenę produktów z jakiejś tam danej kategorii.

Warto sobie na początku zawsze sprawdzić co posiadamy w tej tabeli; to będzie tabela znowu towary i mamy taką listę towarów, od razu sobie dorzucę też kategorię ID, na przykład zrobimy sobie kategorię czwartą, zobaczymy co mamy w tej tabeli czwartej, kategorię czwartą, czyli mamy ciastka i czekoladę i mamy nagle jakąś literówkę, wciskamy to też się zdarza niestety w bazach danych, takie sytuacje, i chcielibyśmy na przykład podnieść lub obniżyć ceny katalogowe tych produktów, w danej kategorii czwartej, czyli tam słodyczy, tak.

No i teraz tak – żeby to zrobić to musielibyśmy zrobić to za pomocą polecenia update no i update jest też  niebezpieczny bo jeżeli można podać update’a bez where’a, czyli jeżeli na przykład ktoś zapisze update w ten sposób to zrobi update, tabela towary, set, cena katalogowa = cena katalogowa i będzie to razem 1,1 czyli tam podniesiemy, załóżmy, o 10% mniej więcej, no to co, to się podniosą wszystkie bo w update nie jest konieczne używanie where.

Natomiast jak zrobimy jeszcze where, to możemy zrobić kategorię ID no i tu wpisujemy na przykład tę czwórkę; dzięki temu podniesiemy o 10% tylko i wyłącznie towary z kategorii czwartej. Ale ryzyko jest takie, że ktoś może zapomnieć tej kategorii i nam wszystko na przykład zmieni; zamiast czterech rekordów nagle przed naszymi oczami pojawi się, że się zaktualizowało 1000 rekordów albo 20 tysięcy no i się trochę spocimy, tak.

Więc można zrobić coś takiego, ze jeżeli ktoś ma aktualizować rekordy, możemy to zapisać w postaci procedury składowanej, która będzie mieć obowiązkowe dwa parametry; tę wartość o którą podwyższamy i numer kategorii. Dzięki temu, i ta osoba będzie update’ować tylko za pomocą tej procedury, nie ma możliwości, że ona na przykład nie poda wartości kategorii. Można to potem też oczywiście zaszyć w jakimś formularzu i dzięki temu mamy to jakoś zabezpieczone.

No dobra, ja tu sobie zapisałem z czego to się będzie składać, natomiast teraz stworzymy sobie tę procedurę, czyli znowu create procedure, nazwiemy to zmiana ceny katalogowej po kategorii, a, żebyśmy wiedzieli o co po prostu tu chodzi. No i teraz tak, jakie parametry będziemy potrzebować? Będę potrzebował, nazwę to zmiana, zmiana jakiego typu damy, jakiegoś jaki Wam odpowiada, na przykład możemy dać float, może być oczywiście money też, mogłoby być też inaczej, jak wolimy i będzie jeszcze co, kategoria, czyli ja to nazwę trochę inaczej, numer kategorii i to będzie int, as i zaczyna się nasza procedura.

Tu oczywiście wstawimy go żeby nam tu nie podkreślał, jeżeli nam podkreśla to jest ryzyko, że może nam coś nie podpowiadać. I teraz tak – zaznaczamy cały update, wrzucamy tu poniżej, to oczywiście zastąpimy parametrami i tak samo to i dodatkowo zrobimy sobie coś takiego, że wrzucimy tego naszego selecta. Ale to za chwilę, pokażę jak będziemy musieli to zupdate’ować, natomiast teraz tak to sobie zrobimy, odpalamy, wykonało się no i teraz wywołamy.

Exec p, to się nazywa zmiana ceny, podaję najpierw cenę, zmiana, czyli niech będzie 1,1, mamy w końcu 4, zaznaczamy, execute, po czterech rekordach się wykonało, zobaczmy,o,cena się podniosła, mogę jeszcze raz to odpalić,  tu mamy 11w pierwszym przypadku, tu już jest 12, czyli cena rośnie. Oczywiście moglibyśmy ją sobie teraz obniżać więc mamy to w ten sposób.

Natomiast jeżeli na przykład ktoś zapomniałby tego, no to mamy error, nie, procedura składowana posiada dwa parametry i nie możemy żadnego z nich pominąć. Możemy oczywiście zrobić coś takiego jak parametr domyślny; akurat tu, w tym przypadku, powiem szczerze, byłoby to ryzykowne, natomiast można to oczywiście zrobić, jest to dość proste, ale to bym pokazał na kolejnym przykładzie.

I teraz tak – jak się update’uje procedury. Jeżeli procedurę chcemy zupdate’ować to tutaj zamiast create dajemy alter i tak jak już wspomniałem, możemy wrzucić sobie też select. Czyli sobie wrzucamy selecta, tu pod spodem żeby to było razem i tu też dorzucę @, numer kategorii i teraz jak to wszystko sobie zupdate’ujemy to teraz jak wykonam tę procedurę, zobaczmy,że nie dość, że wykona mi pewne rzeczy, to jeszcze od razu się wyświetli, jeszcze raz odpalę, no i tutaj mamy, jeszcze raz odpalę, będą kwoty już.

Czyli w ten sposób zupdate’owałem; jeżeli procedurę chcemy wykasować no to robimy drop procedure, zmiana ceny od do, zaznaczamy, widzimy, wykonało się i teraz jakbym chciał to wywołać to już nie działa, nie ma czegoś takiego. No to teraz na nowo to bym musiał zrobić create, execute, wykonało się. W ten oto sposób możemy zrobić tę procedurę, która modyfikuje tak naprawdę nam dwie rzeczy.

I teraz wracając do parametru domyślnego, jeżeli byłaby taka potrzeba. Tak jak wspomniałem, czyli już przy tej modyfikacji, alter, no to teraz tak – załóżmy, że możemy zrobić na przykład domyślną wartość kategorii, to tutaj na przykład mogę wpisać równa się i dać na przykład, że to=1 albo na przykład 2. Zaznaczam to, że jeżeli ktoś nie poda tej kategorii, to domyślnie zwiększy nam produkt z kategorii drugiej do pierwszej, czy jaką sobie wybierzemy, nie. I może zróbmy tą 4, tak jak była wcześniej; wykonam teraz tę modyfikację i teraz zobaczmy.

Jeżeli ja na przykład wstawię tu kategorię pierwszą to mi pokaże co w tym momencie, jakieś tam napoje. Natomiast jak nie podam to zmienią się na słodycze. I teraz tak; to jest tak zwany parametr domyślny. Co jeżeli obydwa parametry byłyby domyślne, na przykład tu by było domyślnie, że na przykład zwiększamy o, no nie jest w ogóle zwiększany, czyli jest na tym samym poziomie, zobaczmy, jak nic nie podamy to w tym momencie jest taka sama, jeszcze raz ta sama, nic się nie zmienia.

No i chcielibyśmy na przykład teraz, to oczywiście nie ma sensu, układać tylko numer kategorii, to możemy, tylko ponieważ ten numer kategorii jest drugim parametrem, to trzeba podać jego nazwę. Czyli tak, numer kategorii = to będzie 1, i teraz mogę w ogóle w odwrotnej kolejności też podać, równa się, obniżymy wartość i zobaczmy, mamy teraz napoje, które będą tanieć.

Podsumowując – procedury składowane możemy sobie zapisywać na serwerze, możemy zapisywać fragmenty kodu T-SQL-owego, który może dokonywać zmian w tabelach, może modyfikować nam backordy, może nam usuwać zawartość tych backordów, może wyświetlać nam te rezultaty, czyli te wszystkie z grupy DML no i możemy je przechowywać na serwerze; tu jeszcze tylko odświeżę wszystko, to niestety trzeba odświeżyć żeby pokazać gdzie one tu się zapisały no i to są właśnie fragmenty kodu, które możemy sobie tutaj podejrzeć jak one wyglądają, jak z tego możemy skorzystać.

Oczywiście mogą być dużo bardziej skomplikowane, możemy używać, te ilości kodu mogą być dużo większe, możemy tworzyć też, tak jak wspomniałem, inserty, możemy tworzyć jakieś zabezpieczenia, żeby jak ktoś tam coś będzie chciał wpisać to nie będzie mógł, czyli daje to nam bardzo dużo zastosowań i to do tego służy. Niestety czy stety, trzeba mieć do tego uprawnienia, ale jak mamy to możemy takie rzeczy robić. Ja dziękuję za uwagę i do zobaczenia na szkoleniach.