Procedury składowane pomagają w optymalizacji bazy danych i zwiększeniu możliwości ponownego użycia. Zamiast wykonywać to samo zapytanie, możesz utworzyć zadanie jako procedurę składowaną, którą będziesz wywoływać w razie potrzeby. Wszystkiego o procedurach składowanych dowiesz się na końcu tego wpisu.
Praca z procedurami składowanymi w PostgreSQL
Jako użytkownik PostgreSQL mogłeś zauważyć, że funkcje PostgreSQL nie wykonują transakcji. Chociaż możliwe jest utworzenie transakcji, jej zatwierdzenie lub przywrócenie do poprzedniego stanu nie jest możliwe. Jednak te ograniczenia są omijane przy użyciu procedur składowanych.
Oto podstawowa składnia tworzenia procedury składowanej w PostgreSQL:
UTWÓRZ LUB ZAMIEŃ PROCEDUrę nazwa_procedury(
parametr[y] typ_danych
)
JĘZYK plpsql;
JAKO $$
OGŁOSIĆ
zmienne_jeśli_dowolny typ_danych
ZACZYNAĆ
logika
KONIEC;
$$
Najważniejsze rzeczy, na które należy zwrócić uwagę w podanej składni, to „nazwa_procedury”, która jest nazwą, której będziesz używać w procedurze składowanej, parametry, które chcesz uwzględnić i ich typy danych, a także logika, którą są głównie instrukcje SQL.
Podajmy trzy przykłady, które pomogą Ci zrozumieć, jak tworzyć procedury składowane w PostgreSQL.
Przykład 1: Procedura składowana obliczająca kwadrat liczby
W naszym pierwszym przykładzie tworzymy procedurę składowaną, która używa instrukcji „RAISE NOTICE” w celu wydrukowania wyniku na terminalu. Procedura składowana przyjmuje wartość całkowitą podaną podczas jej wywoływania i oblicza jej kwadrat.
Oto jak tworzymy procedurę składowaną:
Nazywamy nasz parametr „num1” i jest to liczba całkowita. W części logicznej definiujemy, w jaki sposób pobiera kwadrat „num1” i zapisuje go jako zmienną kwadratową. Kiedy wykonamy polecenie, otrzymamy wynik „CREATE PROCEDURE”, który potwierdza, że udało nam się pomyślnie utworzyć procedurę składowaną.
Kolejnym zadaniem jest wywołanie procedury i podanie jej oczekiwanego argumentu.
CALL nazwa_procedury(argumenty);Otrzymasz wynik CALL pokazujący, że procedura składowana została wykonana, a my otrzymamy oczekiwany wynik, który w tym przypadku jest kwadratem dodanego argumentu.
Przykład 2: Procedura składowana służąca do wstawiania wartości do wpisu tabeli
Poniższe dwa przykłady pokazują, jak utworzyć procedurę składowaną współpracującą z tabelą bazy danych. Stwórzmy szybko tabelę „uczniów”, z którą będziemy pracować.
Na potrzeby tego przykładu tworzymy procedurę składowaną, która umożliwia użytkownikowi wstawienie wartości do nowo utworzonej tabeli. Zwróć uwagę, jak określamy parametry, które mają zostać dodane jako argumenty podczas wywoływania procedury składowanej. Ponadto definiujemy logikę, która pobiera dodane argumenty i wykonuje instrukcję INSERT SQL do tabeli „studentów”.
Możemy sprawdzić dostępne procedury składowane, uruchamiając następującą komendę:
\dfPierwszą procedurą składowaną, którą możemy zobaczyć w poniższych wynikach, jest „add_student”, którą wcześniej utworzyliśmy.
Teraz wywołajmy procedurę składowaną, aby ją wykonać. Poniższy obraz pokazuje, że mamy pustą tabelę, ale wywołaliśmy procedurę składowaną, aby dodać pierwszego ucznia:
Jeśli wymienimy wartości w naszej tabeli, zwróć uwagę, że argumenty dodane za pomocą polecenia wywołania procedury są wartościami dla naszego pierwszego ucznia w naszej tabeli. W ten sposób tworzysz procedurę składowaną, która wstawia wartości do tabeli.
Należy pamiętać, że podczas tworzenia procedury składowanej określone parametry muszą odpowiadać oczekiwaniom w tabeli, aby uniknąć błędów. Ponadto typ danych musi się zgadzać.
Przykład 3: Procedura składowana aktualizująca wpis w tabeli
Idąc dalej, utwórzmy kolejną procedurę składowaną, która aktualizuje wpis w tabeli. Jeśli chcesz szybko zaktualizować wartości w naszej tabeli, możesz utworzyć procedurę składowaną aktualizacji w następujący sposób:
Określ, którą kolumnę chcesz zaktualizować, używając słowa kluczowego WHERE i nową wartość, używając słowa kluczowego SET. Następnie należy dodać słowo kluczowe COMMIT, aby utrwalić zmiany.
Wywołajmy procedurę składowaną update i dodajmy oczekiwane argumenty: „student_id” oraz nowy kurs.
Jeśli wymienimy wpisy w naszej tabeli, możemy sprawdzić, czy mamy zaktualizowany kurs dla konkretnego ucznia, do którego się zwróciliśmy. Tak działa procedura składowana aktualizacji.
Wniosek
Możesz utworzyć dowolną procedurę składowaną w PostgreSQL. Wystarczy zrozumieć składnię, której należy przestrzegać, a następnie zdefiniować logikę procedury składowanej. Stamtąd wywołaj procedurę składowaną i sprawdź, czy została wykonana zgodnie z oczekiwaniami. W tym poście wyjaśniono procedury składowane w PostgreSQL i podano przykłady ich tworzenia.