Oracle Procedury i funkcje składowane PL/SQL z przykładami

Czym są podprogramy PL/SQL?

W tym samouczku znajdziesz szczegółowy opis tworzenia i wykonywania nazwanych bloków, procedur i funkcji.

Procedury i funkcje to podprogramy, które można tworzyć i zapisywać w bazie danych jako obiekty. Można je wywoływać lub odwoływać się do nich również z poziomu innych bloków.

Omówimy również główne różnice między tymi dwoma podprogramami i omówimy Oracle wbudowane funkcje.

Terminologie w podprogramach PL/SQL

Zanim przyjrzymy się podprogramom PL/SQL, omówimy terminologię stanowiącą część tych podprogramów.

Parametr

Parametr to zmienna lub symbol zastępczy dowolnego prawidłowego parametru Typ danych PL/SQL poprzez który podprogram PL/SQL wymienia wartości z kodem głównym. Ten parametr umożliwia wprowadzanie danych do podprogramów i np.tracwyciąganie z nich wartości.

• Parametry te należy zdefiniować wraz z podprogramami w momencie ich tworzenia.
• Są one uwzględniane w instrukcji wywołania w celu interakcji z podprogramami.
• Typ danych parametru w podprogramie i instrukcji wywołującej powinien być taki sam.
• Rozmiar typu danych nie powinien być podawany w momencie deklaracji parametru, ponieważ jest dynamiczny.

Ze względu na cel parametry klasyfikuje się jako:

1. W parametrze
2. Parametr WYJŚCIOWY
3. IN OUT Parametr

W parametrze

• Służy do wprowadzania danych do podprogramów.
• Jest to zmienna tylko do odczytu wewnątrz podprogramów; jej wartości nie można zmienić wewnątrz podprogramu.
• W instrukcji wywołującej może to być zmienna, wartość literalna lub wyrażenie, takie jak „5*8” lub „a/b”.
• Domyślnie parametry są typu IN.

Parametr WYJŚCIOWY

• Służy do pobierania danych wyjściowych z podprogramów.
• Jest to zmienna do odczytu i zapisu wewnątrz podprogramów; jej wartość można w nich zmieniać.
• W wywołaniu zawsze powinna to być zmienna przechowująca wartość z podprogramu.

IN OUT Parametr

• Używany do wprowadzania danych wejściowych i uzyskiwania danych wyjściowych z podprogramów.
• Jest to zmienna do odczytu i zapisu wewnątrz podprogramów; jej wartość można w nich zmieniać.
• W wywołaniu zawsze powinna to być zmienna przechowująca wartość z podprogramu.

Typ parametru należy podać w momencie tworzenia podprogramów.

POWRÓT

RETURN to słowo kluczowe, które instruuje kompilator, aby przełączył sterowanie z podprogramu na instrukcję wywołującą. W podprogramie RETURN oznacza po prostu, że sterowanie musi wyjść z podprogramu; po znalezieniu RETURN przez kontroler, kod po nim jest pomijany.

Zwykle blok nadrzędny lub główny wywołuje podprogramy, a sterowanie jest przekazywane z bloku nadrzędnego do wywoływanego podprogramu. Instrukcja RETURN w podprogramie zwraca sterowanie z powrotem do bloku nadrzędnego. W przypadku funkcji instrukcja RETURN zwraca również wartość, której typ danych jest określony w momencie deklaracji funkcji.

Czym jest procedura w PL/SQL?

A Procedura W PL/SQL jest to jednostka podprogramu składająca się z grupy instrukcji PL/SQL, które można wywołać po nazwie. Każda procedura ma swoją unikalną nazwę i jest przechowywana w Oracle baza danych jako obiekt bazy danych.

Uwaga: Podprogram to nic innego jak procedura, którą należy utworzyć ręcznie, zgodnie z wymaganiami. Po utworzeniu jest on przechowywany jako obiekt bazy danych.

Charakterystyka jednostki podprogramu procedury w PL/SQL jest następująca:

• Procedury to samodzielne bloki, które można przechowywać w baza danych.
• Można je wywołać po nazwie w celu wykonania instrukcji PL/SQL.
• Służą głównie do realizacji procesu.
• Mogą zawierać zagnieżdżone bloki lub być zagnieżdżone wewnątrz innych bloków lub pakietów.
• Zawierają część deklaracyjną (opcjonalną), część wykonawczą i część obsługi wyjątków (opcjonalną).
• Wartości mogą być przekazywane do procedury lub pobierane z procedury za pomocą parametrów.
• Parametry te należy uwzględnić w instrukcji wywołującej.
• Procedura może zawierać instrukcję RETURN służącą do zwrócenia sterowania do bloku wywołującego, ale nie może ona zwracać żadnej wartości za pomocą instrukcji RETURN.
• Procedur nie można wywołać bezpośrednio z poleceń SELECT; można je wywołać z innego bloku lub za pomocą słowa kluczowego EXEC.

Składnia

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE PROCEDURE instruuje kompilator, aby utworzył nową procedurę. Słowo kluczowe „OR REPLACE” instruuje go, aby zastąpił istniejącą procedurę (jeśli istnieje) bieżącą.
• Nazwa procedury powinna być unikatowa.
• Słowo kluczowe „IS” jest używane, gdy procedura składowana jest zagnieżdżona w innym bloku. Jeśli procedura jest samodzielna, używane jest słowo „AS”. Poza tym standardem kodowania, oba mają to samo znaczenie.

Przykład 1: Tworzenie procedury i wywoływanie jej za pomocą EXEC. W tym przykładzie tworzymy Oracle procedura przyjmująca nazwę jako dane wejściowe i drukująca komunikat powitalny jako dane wyjściowe. Do jej wywołania służy polecenie EXEC.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Wyjaśnienie:

• Code linia 1: Utworzenie procedury o nazwie „welcome_msg” i jednym parametrze „p_name” typu „IN”.
• Code linia 4: Wydrukuj komunikat powitalny poprzez połączenie wprowadzonej nazwy.
• Procedura została pomyślnie skompilowana.
• Code linia 7: Wywołanie procedury za pomocą EXEC z parametrem 'Guru99. Procedura jest wykonywana i wyświetla komunikat „Witamy” Guru99 ”.

Co to jest funkcja?

Funkcja to samodzielny podprogram PL/SQL. Podobnie jak procedura, funkcja ma unikalną nazwę i jest przechowywana jako obiekt bazy danych PL/SQL. Jej cechy to:

• Funkcje to samodzielne bloki służące głównie do obliczeń.
• Funkcja używa słowa kluczowego RETURN w celu zwrócenia wartości, której typ danych jest zdefiniowany w momencie tworzenia.
• Funkcja powinna albo zwracać wartość, albo zgłaszać wyjątek; return jest w funkcjach obowiązkowy.
• Funkcja bez instrukcji DML może być wywołana bezpośrednio w zapytaniu SELECT, natomiast funkcja z instrukcjami DML może być wywołana wyłącznie z innych bloków PL/SQL.
• Może zawierać zagnieżdżone bloki lub być zagnieżdżony wewnątrz innych bloków lub pakietów.
• Zawiera część deklaracyjną (opcjonalną), część wykonawczą i część obsługi wyjątków (opcjonalną).
• Wartości mogą być przekazywane do funkcji lub pobierane z niej za pomocą parametrów.
• Parametry te należy uwzględnić w instrukcji wywołującej.
• Funkcja może również zwracać wartość za pomocą parametrów OUT oprócz użycia RETURN.
• Ponieważ zawsze zwraca wartość, polecenie wywołujące zawsze używa operatora przypisania do wypełnienia zmiennej.

Składnia

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION nakazuje kompilatorowi utworzenie nowej funkcji. 'OR REPLACE' nakazuje zastąpienie istniejącej funkcji (jeśli istnieje) funkcją bieżącą.
• Nazwa funkcji powinna być unikatowa.
• Należy podać typ danych RETURN.
• Słowo kluczowe „IS” jest używane, gdy funkcja jest zagnieżdżona w innym bloku. Jeśli funkcja jest samodzielna, używane jest słowo kluczowe „AS”.

Przykład 1: Tworzenie funkcji i wywoływanie jej za pomocą bloku anonimowego. W tym programie tworzymy funkcję, która przyjmuje nazwę jako dane wejściowe i zwraca komunikat powitalny, używając anonimowego bloku i instrukcji SELECT do wywołania jej.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Wyjaśnienie:

• Code linia 1: Tworzenie funkcji o nazwie „welcome_msg_func” i jednym parametrze „p_name” typu „IN”.
• Code linia 2: Deklaracja typu zwracanego jako VARCHAR2.
• Code linia 5: Zwracanie połączonej wartości „Witamy” i wartości parametru.
• Code linia 8: Anonimowy blok do wywołania powyższej funkcji.
• Code linia 9: Deklarowanie zmiennej z tym samym typem danych, co typ zwracany przez funkcję.
• Code linia 11: Wywołanie funkcji i wpisanie wartości zwracanej do zmiennej „lv_msg”.
• Code linia 12: Wydruk wartości zmiennej. Na wyjściu pojawia się komunikat „Witamy” Guru99 ”.
• Code linia 14: Wywołanie tej samej funkcji za pomocą instrukcji SELECT. Wartość zwracana jest kierowana na standardowe wyjście.

Podobieństwa między procedurą a funkcją

• Obydwa można wywołać z innych bloków PL/SQL.
• Jeżeli wyjątek zgłoszony w podprogramie nie zostanie obsłużony w jego obsługa wyjątków sekcji, propaguje ona do bloku wywołującego.
• Obydwa mogą mieć dowolną liczbę parametrów.
• Obydwa są traktowane jako obiekty bazy danych w PL/SQL.

Procedura kontra funkcja: kluczowe różnice

Wbudowane funkcje w PL/SQL

PL / SQL Zawiera różne wbudowane funkcje do pracy z ciągami znaków i datami. Tutaj przedstawiamy najczęściej używane funkcje i ich zastosowanie.

Funkcje konwersji

Te wbudowane funkcje konwertują jeden typ danych na inny.

Funkcje łańcuchowe

Funkcje te są używane w przypadku typu danych znakowych.

Funkcje daty

Funkcje te służą do manipulowania datami.

FAQ