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

Przez: Fiona Brown Fiona Brown
Hours Zaktualizowano

W tym samouczku zobaczysz szczegółowy opis tworzenia i wykonywania nazwanych bloków (procedury i funkcje).

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

Oprócz tego omówimy główne różnice między tymi dwoma podprogramami. Będziemy także omawiać Oracle wbudowane funkcje.

Terminologie w podprogramach PL/SQL

Zanim poznamy podprogramy PL/SQL, omówimy różne terminologie występujące w tych podprogramach. Poniżej znajdują się terminologie, które będziemy omawiać.

Parametr

Parametr jest zmienną lub symbolem zastępczym dowolnego prawidłowego Typ danych PL/SQL za pomocą którego podprogram PL/SQL wymienia wartości z kodem głównym. Ten parametr umożliwia wprowadzanie danych do podprogramów i wyciąganie z nich danych.

  • Parametry te należy zdefiniować wraz z podprogramami w momencie ich tworzenia.
  • Parametry te są zawarte w instrukcji wywołania tych podprogramów w celu interakcji wartości z podprogramami.
  • Typ danych parametru w podprogramie i instrukcja wywołująca powinny być takie same.
  • Rozmiar typu danych nie powinien być wymieniany w momencie deklaracji parametrów, ponieważ rozmiar jest dynamiczny dla tego typu.

W zależności od przeznaczenia parametry są klasyfikowane jako

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

W parametrze

  • Ten parametr służy do wprowadzania danych wejściowych do podprogramów.
  • Jest to zmienna przeznaczona tylko do odczytu wewnątrz podprogramów. Ich wartości nie można zmieniać wewnątrz podprogramu.
  • W instrukcji wywołującej parametry te mogą być zmienną, wartością dosłowną lub wyrażeniem, na przykład może to być wyrażenie arytmetyczne, takie jak „5*8” lub „a/b”, gdzie „a” i „b” są zmiennymi .
  • Domyślnie parametry są typu IN.

Parametr WYJŚCIOWY

  • Ten parametr służy do uzyskiwania danych wyjściowych z podprogramów.
  • Jest to zmienna do odczytu i zapisu wewnątrz podprogramów. Ich wartości można zmieniać wewnątrz podprogramów.
  • W instrukcji wywołującej parametry te powinny zawsze być zmienną przechowującą wartość z bieżącego podprogramu.

IN OUT Parametr

  • Ten parametr służy zarówno do podawania danych wejściowych, jak i do uzyskiwania danych wyjściowych z podprogramów.
  • Jest to zmienna do odczytu i zapisu wewnątrz podprogramów. Ich wartości można zmieniać wewnątrz podprogramów.
  • W instrukcji wywołującej parametry te powinny zawsze być zmienną przechowującą wartość z podprogramów.

O tym typie parametrów należy wspomnieć podczas tworzenia podprogramów.

POWRÓT

RETURN jest słowem kluczowym, które instruuje kompilator, aby przełączył kontrolę z podprogramu na instrukcję wywołującą. W podprogramie RETURN oznacza po prostu, że sterowanie musi wyjść z podprogramu. Gdy sterownik znajdzie w podprogramie słowo kluczowe RETURN, następujący po nim kod zostanie pominięty.

Zwykle blok nadrzędny lub główny wywołuje podprogramy, a następnie sterowanie przechodzi z tych bloków nadrzędnych do wywoływanych podprogramów. RETURN w podprogramie zwróci sterowanie z powrotem do ich bloku nadrzędnego. W przypadku funkcji polecenie RETURN również zwraca wartość. Typ danych tej wartości jest zawsze wymieniony w momencie deklaracji funkcji. Typ danych może być dowolnym prawidłowym typem danych PL/SQL.

Co to jest procedura w PL/SQL?

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

Uwaga: Podprogram to nic innego jak procedura i należy go utworzyć ręcznie zgodnie z wymaganiami. Po utworzeniu będą przechowywane jako obiekty bazy danych.

Poniżej przedstawiono charakterystykę jednostki podprogramu Procedura w języku PL/SQL:

  • Procedury są samodzielnymi blokami programu, które można przechowywać w pliku baza danych.
  • Aby wykonać instrukcje PL/SQL, można wywołać te procedury PLSQL, odwołując się do ich nazwy.
  • Służy głównie do wykonywania procesów w języku PL/SQL.
  • Może mieć zagnieżdżone bloki lub może być zdefiniowany i zagnieżdżony w innych blokach lub pakietach.
  • Zawiera część deklaracyjną (opcjonalnie), część wykonawczą, część dotyczącą obsługi wyjątków (opcjonalnie).
  • Wartości można przekazywać do Oracle procedury lub pobierane z procedury poprzez parametry.
  • Parametry te należy uwzględnić w instrukcji wywołującej.
  • Procedura w języku SQL może zawierać instrukcję RETURN, która zwraca kontrolę do bloku wywołującego, ale nie może zwracać żadnych wartości poprzez instrukcję RETURN.
  • Procedur nie można wywoływać bezpośrednio z instrukcji SELECT. Można je wywołać z innego bloku lub poprzez słowo kluczowe EXEC.

Składnia

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE instruuje kompilator, aby utworzył nową procedurę w Oracle. Słowo kluczowe „OR REPLACE” instruuje kompilację, aby zastąpiła istniejącą procedurę (jeśli istnieje) bieżącą.
  • Nazwa procedury powinna być unikalna.
  • Słowo kluczowe „IS” zostanie użyte, gdy procedura składowana w Oracle jest zagnieżdżony w innych blokach. Jeśli procedura jest samodzielna, zostanie użyte słowo „AS”. Poza tym standardem kodowania oba mają to samo znaczenie.

Przykład 1: Tworzenie procedury i wywoływanie jej przy użyciu EXEC

W tym przykładzie utworzymy plik Oracle procedura, która przyjmuje nazwę jako dane wejściowe i wypisuje wiadomość powitalną jako wynik. Będziemy używać polecenia EXEC do wywołania procedury.

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

Wyjaśnienie kodu:

  • Linia kodu 1: Utworzenie procedury o nazwie „welcome_msg” i jednym parametrze „p_name” typu „IN”.
  • Linia kodu 4: Drukowanie wiadomości powitalnej poprzez połączenie nazwy wejściowej.
  • Procedura została pomyślnie skompilowana.
  • Linia kodu 7: Wywołanie procedury za pomocą polecenia EXEC z parametrem „Guru99”. Procedura zostanie wykonana, a wiadomość zostanie wydrukowana jako „Witamy Guru99”.

POWIĄZANE ARTYKUŁY

Co to jest funkcja?

Functions jest samodzielnym podprogramem PL/SQL. Podobnie jak procedura PL/SQL, funkcje mają unikalną nazwę, pod którą można się do nich odnosić. Są one przechowywane jako obiekty bazy danych PL/SQL. Poniżej znajdują się niektóre charakterystyki funkcji.

  • Funkcje są samodzielnym blokiem używanym głównie do celów obliczeniowych.
  • Funkcja używa słowa kluczowego RETURN do zwrócenia wartości, a jej typ danych jest zdefiniowany w momencie tworzenia.
  • Funkcja powinna albo zwrócić wartość, albo zgłosić wyjątek, tj. powrót jest obowiązkowy w funkcjach.
  • Funkcja bez instrukcji DML może być bezpośrednio wywołana w zapytaniu SELECT, natomiast funkcja z operacją DML może być wywołana wyłącznie z innych bloków PL/SQL.
  • Może mieć zagnieżdżone bloki lub może być zdefiniowany i zagnieżdżony w innych blokach lub pakietach.
  • Zawiera część deklaracyjną (opcjonalnie), część wykonawczą, część dotyczącą obsługi wyjątków (opcjonalnie).
  • Wartości można przekazać do funkcji lub pobrać z procedury poprzez parametry.
  • Parametry te należy uwzględnić w instrukcji wywołującej.
  • Funkcja PLSQL może również zwracać wartość poprzez parametry OUT w inny sposób niż przy użyciu RETURN.
  • Ponieważ zawsze zwróci wartość, podczas wywołania instrukcji zawsze stosuje się operator przypisania, aby wypełnić zmienne.

Funkcje w PL/SQL

Składnia

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • CREATE FUNCTION instruuje kompilator, aby utworzył nową funkcję. Słowo kluczowe „OR REPLACE” instruuje kompilator, aby zastąpił istniejącą funkcję (jeśli istnieje) bieżącą.
  • Nazwa funkcji powinna być unikalna.
  • Należy wspomnieć o typie danych RETURN.
  • Słowo kluczowe „IS” zostanie użyte, gdy procedura będzie zagnieżdżona w innych blokach. Jeśli procedura jest samodzielna, zostanie użyte słowo „AS”. Poza tym standardem kodowania oba mają to samo znaczenie.

Przykład 1: Tworzenie funkcji i wywoływanie jej za pomocą bloku anonimowego

W tym programie utworzymy funkcję, która przyjmie nazwę jako dane wejściowe i zwróci wiadomość powitalną jako wynik. Do wywołania funkcji użyjemy instrukcji bloku anonimowego i instrukcji Select.

Funkcje w PL/SQL

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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;

Wyjaśnienie kodu:

  • Linia kodu 1: Tworzenie Oracle funkcja o nazwie 'welcome_msg_func' i jednym parametrze 'p_name' typu 'IN'.
  • Linia kodu 2: deklarowanie typu zwracanego jako VARCHAR2
  • Linia kodu 5: Zwracanie połączonej wartości „Witamy” i wartości parametru.
  • Linia kodu 8: Anonimowy blok do wywołania powyższej funkcji.
  • Linia kodu 9: Deklarowanie zmiennej o typie danych takim samym jak typ danych zwracanych przez funkcję.
  • Linia kodu 11: Wywołanie funkcji i zapisanie wartości zwracanej do zmiennej „lv_msg”.
  • Linia kodu 12: Drukowanie wartości zmiennej. Wynik, który tutaj otrzymasz, to „Witamy Guru99”
  • Linia kodu 14: Wywołanie tej samej funkcji poprzez instrukcję SELECT. Wartość zwracana jest kierowana bezpośrednio na standardowe wyjście.

Podobieństwa między procedurą a funkcją

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

Procedura vs. Funkcja: Kluczowe różnice

Procedura Funkcjonować
Używane głównie do wykonania określonego procesu Używany głównie do wykonywania niektórych obliczeń
Nie można wywołać instrukcji SELECT W instrukcji SELECT można wywołać funkcję, która nie zawiera żadnych instrukcji DML
Użyj parametru OUT, aby zwrócić wartość Użyj RETURN, aby zwrócić wartość
Zwrócenie wartości nie jest obowiązkowe Zwrócenie wartości jest obowiązkowe
RETURN po prostu zakończy sterowanie z podprogramu. RETURN spowoduje wyjście ze sterowania z podprogramu i zwrócenie wartości
Typ danych zwrotnych nie zostanie określony w momencie tworzenia Typ danych zwrotnych jest obowiązkowy w momencie tworzenia

Wbudowane funkcje w PL/SQL

PL / SQL zawiera różne wbudowane funkcje do pracy z ciągami znaków i typem danych daty. Tutaj zobaczymy często używane funkcje i ich zastosowanie.

Funkcje konwersji

Te wbudowane funkcje służą do konwersji jednego typu danych na inny typ danych.

Nazwa funkcji Stosowanie Przykład
TO_CHAR Konwertuje inny typ danych na znakowy typ danych TO_CHAR(123);
TO_DATE (ciąg, format) Konwertuje podany ciąg znaków na bieżąco. Ciąg powinien pasować do formatu.

TO_DATE('2015 stycznia 15 r., 'RRRR-MON-DD');

Wydajność: 1 / 15 / 2015
TO_NUMBER (tekst, format)

Konwertuje tekst na typ liczbowy o podanym formacie.

Informat '9' oznacza liczbę cyfr

 Wybierz TO_NUMBER('1234′,'9999') z podwójnego;

Wydajność: 1234

Wybierz TO_NUMBER('1,234.45′,'9,999.99') z podwójnego;

Wydajność: 1234

Funkcje łańcuchowe

Są to funkcje używane w typie danych znakowych.

Nazwa funkcji Stosowanie Przykład
INSTR(tekst, ciąg znaków, początek, wystąpienie) Podaje pozycję określonego tekstu w podanym ciągu.

  • tekst – ciąg główny
  • string – tekst, który należy przeszukać
  • start – pozycja początkowa wyszukiwania (opcjonalnie)
  • zgodność – wystąpienie szukanego ciągu (opcjonalnie)
 Wybierz INSTR('AEROPLANE','E',2,1) z dual

Wydajność: 2

Wybierz INSTR('AEROPLANE','E',2,2) z dual

Wydajność: 9 (2nd wystąpienie E)
SUBSTR (tekst, początek, długość) Podaje wartość podciągu głównego ciągu.

  • tekst – ciąg główny
  • start – pozycja wyjściowa
  • długość – długość podciągu
 wybierz substr('samolot',1,7) z dual

Wydajność: aeropla
GÓRNY ( tekst ) Zwraca wielkie litery podanego tekstu Wybierz górną („guru99”) z podwójnej;

Wydajność:GURU99
DOLNY ( tekst ) Zwraca małe litery podanego tekstu Wybierz niższą („AerOpLane”) z podwójnej;

Wydajność: samolot
INITCAP (tekst) Zwraca podany tekst zaczynający się od dużej litery. Wybierz („guru99”) z trybu podwójnego

Wydajność: Guru99

Wybierz („moja historia”) z opcji podwójnej

Wydajność: Moja historia
DŁUGOŚĆ ( tekst ) Zwraca długość podanego ciągu Wybierz DŁUGOŚĆ („guru99”) z podwójnego;

Wydajność: 6
LPAD (tekst, długość, pad_char) Dopełnia ciąg po lewej stronie o zadaną długość (całkowity ciąg) podanym znakiem Wybierz LPAD('guru99', 10, '$') z dual;

Wydajność: $$$$guru99
RPAD (tekst, długość, pad_char) Dopełnia ciąg po prawej stronie o podaną długość (całkowity ciąg) podanym znakiem Wybierz RPAD('guru99′,10,'-') z dual

Wydajność: guru99—-
LTRIM ( tekst ) Przycina wiodący biały znak z tekstu Wybierz LTRIM('Guru99') z dual;

Wydajność: Guru99
RTRIM ( tekst ) Przycina końcowy biały znak z tekstu Wybierz RTRIM('Guru99') z dual;

Wydajność; Guru99

Funkcje daty

Są to funkcje służące do manipulowania datami.

Nazwa funkcji Stosowanie Przykład
ADD_MONTHS (data, liczba miesięcy) Dodaje do daty podane miesiące ADD_MIESIĄC('2015-01-01',5);

Wydajność: 05 / 01 / 2015
SYSDATA Zwraca bieżącą datę i godzinę serwera Wybierz SYSDATE z dual;

Wydajność: 10, 4:2015:2
PIEŃ Zaokrąglenie zmiennej daty do najniższej możliwej wartości wybierz sysdate, TRUNC(sysdate) z dual;

Wydajność: 10 4:2015:2 12
ROUND Zaokrągla datę do najbliższej granicy wyższej lub niższej Wybierz sysdate, ROUND(sysdate) z dual

Wydajność: 10 4:2015:2 14
MIESIĄCE_BETWEEN Zwraca liczbę miesięcy pomiędzy dwiema datami Wybierz MONTHS_BETWEEN (sysdate+60, sysdate) z dual

Wydajność: 2

Podsumowanie

W tym rozdziale dowiedzieliśmy się następujących rzeczy.

  • Jak utworzyć procedurę i różne sposoby jej wywoływania
  • Jak utworzyć funkcję i różne sposoby jej wywoływania
  • Podobieństwa i różnice pomiędzy procedurą a funkcją
  • Parametry i popularne terminologie RETURN w podprogramach PL/SQL
  • Typowe wbudowane funkcje w Oracle PL / SQL