PL/SQL, część 1

Bardziej zaawansowane przetwarzanie danych.

  • rozszerzenie SQL o elementy programowania proceduralnego i obiektowego (Procedural Language/Structured Query Language)
  • Nastawiony na przetwarzanie danych
  • Nieprzeznaczony do interakcji z użytkownikiem
  • Pozwala na definiowanie:
    • Anonimowych bloków programowych,
    • Struktur trwale zapisywanych w bazie danych, tzw. programów składowych
    • Można umieszczać polecenia DML
    • Nie można umieszczać instrukcji DDL i DCL
  • Indywidualne rozwiązanie firmy Oracle (nie jest elementem standardu). Inne SZBD mają swoje rozszerzenia, wszystkie one się mocno od siebie różnią.

Najważniejsze korzyści: większa łatwość wykonania niektórych zadań niż w SQL, zwiększenie wydajności (bloki PL/SQL), dostępność mechanizmów nieobecnych w SQL (zmienne, stałe, struktury sterujące, obsługa błędów), kod wykonywany po stronie serwera, możliwość wykorzystania predefiniowanych pakietów, pełna kompatybilność z SZBD Oracle

Bloki

Blok anonimowy

Podstawowa jednostka programowa: blok anonimowy. Nie posiada nazwy i jest wykonywany natychmiast po utworzeniu

Postać podstawowa

Postać pełna

Sekcja deklaracji, wykonywalna bloku, obsługi błędów.

Bloki zagnieżdżone

Można je dodać w sekcji wykonywania oraz w sekcji wyjątków bloku zewnętrznego.

Przykład

Etykiety bloków

Wykorzystane do zmiany widoczności zmiennych, skoków.

Wskaźnik do zmiennej (notacja kropkowa): block_label.variable_name.

Przykład

  • poprawia czytelność kodu,
  • pozwala na lepszą kontrolę nad wykonaniem kodu (etykieta bloku może być celem instrukcji EXIT i CONTINUE)
  • Umożliwia odwołanie się (..?)

Typy danych w PL/SQL

  • Proste
    • Liczbowe
    • Znakowe
    • Logiczny
    • Czasowy
  • Złożone
    • Rekord
    • Tablica
    • Obiekt
  • Wskaźnikowe
  • LOB (Large Object)

Zmienne

  • cel: krótkotrwałe przechowywanie danych wewnątrz bloku PL/SQL
  • Definiowana w sekcji DECLARE - wymaga podania nazwy zmiennej i określenia jej typu (polecana konwencja: v_)
  • Zmienne proste (np. liczb, ciągów znaków, dat i wartości logicznych)
  • Zmienne złożone (np. tablice, rekordy i obiekty)

Przykład

Inicjowanie zmiennej

  • nadanie początkowej wartości
  • niezainicjowana zmienna posiada wartość NULL
  • sposoby inicjowania:
    • przez przypisanie wartości - użycie operatora przypisania :=
    • przez określenie wartości domyślnej - słowo DEFAULT
    • dla zmiennej można wymusić obowiązkowość wartości (NOT NULL)

Przykład

Zmienna typu rekord

Grupa powiązanych danych składowanych w polach, z których każde ma własną nazwę i typ.

Przykład

Atrybuty %TYPE

Mechanizm ułatwiający deklarowanie zmiennej prostej na podstawie typu atrybutu relacji bazy danych lub typu innej zmiennej. Składnia: referencing_item referenced_item%TYPE.

Atrybut ten jest szczególnie przydatny przy deklarowaniu zmiennych do przechowywania wartości z bazy danych. Składnia: variable_name table_name.column_name%TYPE.

Element referencyjny dziedziczy następujące elementy z punktu odniesienia: typ i rozmiar danych oraz ograniczenia (chyba że odnośnik jest kolumną)
Przykład

Atrybut %ROWTYPE

Mechanizm ułatwiający deklarowanie zmiennych. Służy do deklarowania zmiennej rekordowej w oparciu o schemat relacji, kursora lub typ innej zmiennej rekordowej. Używany, gdy potrzeba zmiennej rekordowej, która ma przechować cały rekord ze wskazanej relacji.

Inne zastosowania:

  • tworzymy pojedynczą zmienną zamiast kilku(nastu, dziesięciu, set, …)
  • nie musimy znać nazwy każdej kolumny

Składnia: record_name.field_name.
Przykład

Przykład: deklarowanie rekordu, który reprezentuje wiersz ze złączenia i wybrany podzbiór kolumn

Stałe

Zmienne, których wartość nie zmienia się podczas wykonania procedury. Będą wywoływane tylko po prawej stronie operatorów. Próba zmiany wartości przerywa działanie procedury i zwraca komunikat o błędzie. Definiowana w DECLARE, polecana notacja c_.

Przykład

Musi zostać zainicjowana przy deklaracji:

Rodzaje struktur sterujących

Sekwencja

a->b->c->… -- ciąg poleceń wykonywanych w określonym porządku

Selekcja

Przykład

Iteracja

Przykłady

Pętle

Bezwarunkowa

z EXIT WHEN

pętla z EXIT

Iteracja - WHILE

Można przerwać poleceniem EXIT lub EXIT WHEN.

Iteracja - instrukcja FOR

Zmienne licznikowe nie muszą być deklarowane. [REVERSE] - określa porządek. Dodatkowo EXIT i EXIT WHEN przerywają pętlę.

Przykład

Instrukcja NULL

  • nie wykonuje żadnej akcji,
  • używana na etapie projektowania programu do testowania struktur sterujących,
  • unikanie wygenerowania błędu niepoprawnej składni IF … THEN

Przykład

Interakcja z użytkownikiem

  • przekazanie do programu informacji od użytkownika - zmienne podstawienia
  • rozpoczynają się od znaku &: v_zmienna := &zmienna_podstawienia;,
  • zmienne podstawienia są zamieniane na wartości przed wykonaniem procedury, a nie w jej trakcie. Nie można jej użyć do pytania użytkownika co do przebiegu programu (np. rozgałęziania).

Wypisanie informacji: DBMS_OUTPUT.PUT_LINE(ciag_tekstowy);.

DML w PL/SQL

Instrukcje modyfikujące obiekty w bloku PL/SQL są analogiczne jak w języku SQL, ale:

  • zapytanie musi zwrócić dokładnie jeden wiersz,
  • w zapytaniu dodatkowa klauzula INTO, a w niej:
    • lista zmiennych prostych; liczba zmiennych musi odpowiadać liczbie wyrażeń w klauzuli SELECT zapytania, typy muszą być zgodne; lub
    • zmienna rekordowa o strukturze zgodnej ze strukturą rekordu,
  • średnik umieszczamy na końcu całego polecenia.

Przykład

Opcjonalna klauzula RETURNING … INTO dla INSERT lub UPDATE pozwala na zapisanie we wskazanej zmiennej:

  • Wartości atrybutów rekordu wstawionego przez INSERT,
  • Wartości atrybutów rekordu zmodyfikowanego przez UPDATE,
  • Wartości atrybutów rekordu usuniętego przez DELETE,
  • Tylko jednowierszowe zlecenia.
  • Kursor

    Każde polecenie SQL w bloku PL/SQL w trakcie wykonania zostaje skojarzone z obszarem pamięci. Dostęp do tego obszaru pamięci - obszaru roboczego - jest możliwy za pomocą specjalnej struktury - kursora. Każdy kursor ma swoją własną nazwę.

    Rodzaje kursorów:

    • Jawne - deklarowane przez programistę,
    • Niejawne - tworzone automatycznie dla poleceń INSERT, UPDATE, DELETE i SELECT INTO.

    Kursor jawny

    Jest dedykowany zapytaniom, które mają zwrócić więcej niż jeden rekord. Jest jawnie deklarowany przez programistę.

    Sposób użycia:

    • Zadeklarowanie w sekcji DECLARE
    • Otwarcie - wykonanie zapytania związanego z kursorem, odczytane rekordy trafiają do pamięci
    • Pobieranie kolejnych rekordów
    • Zamknięcie - zwolnienie obszaru pamięci kursora

    Składnia:

    • Po CURSOR - nazwa kursora,
    • jeśli ma być sparametryzowany, listę parametrów kursora umieszczamy w nawiasach po nazwie kursora, jako typ podajemy nazwę kursora

    Dobra praktyka nazewnictwa: cur_.

    Przykład

    Próba odczytania zamkniętego kursora skutkuje błędem INVALID_CURSOR.

    Atrybuty kursora

    • %ISOPEN - TRUE, jeśli otwarty; FALSE wpp.
    • %FOUND - TRUE, jeśli ostatnie pobranie rekordu z kursora zakończyło się powodzeniem, FALSE wpp., NULL przed pierwszym pobraniem
    • %NOTFOUND - odwrotnie niż wyżej
    • %ROWCOUNT - liczba pobranych do tej pory rekordów

    Pętla FOR z kursorem

    Pętla wykona się tyle razy, ile rekordów odczyta kursor. Nie ma konieczności otwierania i zamykania kursora. Zmienna indeksująca pętli jest zmienną rekordową, nie należy jej deklarować. Odwołania do atrybutów relacji udostępnianych przez kursor - przy użyciu notacji kropkowej: kursor.nazwa_atrybutu.

    Przykład

    Pętla FOR z podzapytaniem

    Klauzula WHERE CURRENT OF

    Umożliwia modyfikację (poleceniem UPDATE) bądź usunięcie (poleceniem DELETE) bieżącego rekordu kursora. Kursor musi zostać zadeklarowany z klauzulą FOR UPDATE. Otwarcie kursora zakłada blokady na rekordach otrzymanych w zapytaniu.

    Kursor niejawny

    Tworzony automatycznie dla INSERT, UPDATE, DELETE, SELECT INTO. Nie wymaga deklarowania. Otwierany bezpośrednio przed wykonaniem polecenia, zamykany zaraz po wykonaniu polecenia. Nazwa kursora: SQL. Atrybutów %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN zwykle nie używamy.

    Obsługa błędów PL/SQL

    Źródłem błędów mogą być niepoprawne operacje, awarie SZBD, utrata połączenia z SZBD. Oracle ma zdefiniowany katalog błędów, w którym każdy błąd ma przypisany numer i odpowiedni komunikat. Wystąpienie błędu w trakcie działania procedury powoduje jej przerwanie i wyświetlenie na konsoli komunikatu o wystąpieniu błędu.

    Obsługa błędów sprawia, że w razie wystąpienia błędu program nie zostanie przerwany, ale będzie wykonywał jakąś alternatywę. Wyjątek to błąd lub ostrzeżenie, które zostało wygenerowane w trakcie działania programu.

    Struktura sekcji EXCEPTION

    Wywołanie sekcji EXCEPTION następuje, gdy pojawi się wyjątek. Sekcja obsługi wyjątków składa się z szeregu podsekcji WHEN … THEN (minimum jednej). Jedna podsekcja może być związana z kilkoma wyjątkami (WHEN wyj_1 OR wyj_2). Opcjonalna sekcja OTHERS

    Propagacja wyjątków

    Niech będzie blok zewnętrzny i wewnętrzny. Blok wewnętrzny może obsługiwać błędy powstałe w nim, jeśli nie obsłuży - obsługę przejmie blok zewnętrzny.

    Przykład

    Wyjątki użytkownika

    RAISE_APPLICATION_ERROR

    Przerywa działanie programu z wypisaniem na konsoli komunikatu o wypisaniu błędu.
    Użycie: RAISE_APPLICATION_ERROR(numer błędu, komunikat);.
    Zakres numerów błędów: od … do …

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

This site uses Akismet to reduce spam. Learn how your comment data is processed.