Procedura składowana Oracle PL / SQL & Funkcje z przykładami

Spisie treści:

Anonim

W tym samouczku zobaczysz 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 bazy danych. Można je również wywoływać lub odwoływać w innych blokach.

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

W tym samouczku dotyczącym procedury składowanej Oracle nauczysz się:

  • Terminologie w podprogramach PL / SQL
  • Co to jest procedura w PL / SQL?
  • Co to jest funkcja?
  • Podobieństwa między procedurą a funkcją
  • Procedura vs. Funkcja: kluczowe różnice
  • Wbudowane funkcje w PL / SQL

Terminologie w podprogramach PL / SQL

Zanim poznamy podprogramy PL / SQL, omówimy różne terminologie, które są częścią tych podprogramów. Poniżej znajdują się terminologie, które będziemy omawiać.

Parametr:

Parametr jest zmienną lub symbolem zastępczym dowolnego poprawnego typu danych PL / SQL, za pośrednictwem którego podprogram PL / SQL wymienia wartości z głównym kodem. Ten parametr umożliwia wprowadzenie danych wejściowych do podprogramów i wyodrębnienie z tych podprogramów.

  • Parametry te należy zdefiniować wraz z podprogramami w momencie tworzenia.
  • Te parametry są zawarte w instrukcji wywołującej te podprogramy 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 parametru, ponieważ rozmiar jest dynamiczny dla tego typu.

Ze względu na ich przeznaczenie parametry są klasyfikowane jako

  1. Parametr IN
  2. Parametr OUT
  3. Parametr IN OUT

Parametr IN:

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

Parametr OUT:

  • Ten parametr służy do pobierania 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ą, która będzie przechowywać wartość z aktualnych podprogramów.

Parametr IN OUT:

  • Ten parametr jest używany zarówno do podawania danych wejściowych, jak i do pobierania 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ą, która będzie przechowywać wartość z podprogramów.

O tych typach parametrów należy wspomnieć przy tworzeniu podprogramów.

POWRÓT

RETURN jest słowem kluczowym, 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. Gdy sterownik znajdzie słowo kluczowe RETURN w podprogramie, następujący po nim kod zostanie pominięty.

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

Co to jest procedura w PL / SQL?

Procedura PL / SQL jest jednostką podprogram, który składa się z grupy instrukcji PL / SQL, które można nazwać po imieniu. Każda procedura w PL / SQL ma swoją własną unikalną nazwę, za pomocą której można się do niej odwoływać i wywoływać. Ta jednostka podprogramu w bazie danych Oracle jest przechowywana jako obiekt bazy danych.

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

Poniżej znajduje się charakterystyka jednostki podprogramu Procedura w PL / SQL:

  • Procedury to samodzielne bloki programu, które można przechowywać w bazie danych.
  • Wywołanie tych procedur PLSQL można wykonać odwołując się do ich nazwy, aby wykonać instrukcje PL / SQL.
  • Służy głównie do wykonywania procesów 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ęść deklaracji (opcjonalna), część wykonawczą, część obsługi wyjątków (opcjonalna).
  • Wartości można przekazać do procedury Oracle lub pobrać z procedury za pomocą parametrów.
  • Te parametry powinny być zawarte w instrukcji wywołującej.
  • Procedura w języku SQL może mieć instrukcję RETURN, która zwraca sterowanie do bloku wywołującego, ale nie może zwracać żadnych wartości za pośrednictwem instrukcji RETURN.
  • Procedur nie można wywoływać bezpośrednio z instrukcji SELECT. Można je wywołać z innego bloku lub za pomocą słowa kluczowego EXEC.

Składnia:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE instruuje kompilator, aby utworzył nową procedurę w Oracle. Słowo kluczowe „OR REPLACE” instruuje kompilację, aby zastąpić istniejącą procedurę (jeśli istnieje) obecną.
  • Nazwa procedury powinna być niepowtarzalna.
  • Słowo kluczowe „IS” będzie używane, gdy procedura składowana w Oracle jest zagnieżdżona w innych blokach. Jeśli procedura jest samodzielna, zostanie użyty „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 utworzymy procedurę Oracle, która przyjmuje nazwę jako dane wejściowe i drukuje wiadomość powitalną jako dane wyjściowe. Do wywołania procedury użyjemy komendy EXEC.

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

Objaśnienie kodu:

  • Linia kodu 1 : Tworzenie procedury o nazwie „welcome_msg” i jednym parametrze „p_name” typu „IN”.
  • Linia kodu 4 : Drukowanie wiadomości powitalnej poprzez konkatenację nazwy wejściowej.
  • Procedura została pomyślnie skompilowana.
  • Linia kodu 7 : Wywołanie procedury za pomocą polecenia EXEC z parametrem „Guru99”. Procedura jest wykonywana, a wiadomość jest drukowana jako „Welcome Guru99”.

Co to jest funkcja?

Funkcje to samodzielny podprogram PL / SQL. Podobnie jak procedura PL / SQL, funkcje mają unikalną nazwę, za pomocą której można się do nich odwoływać. Są one przechowywane jako obiekty bazy danych PL / SQL. Poniżej znajduje się kilka charakterystyk funkcji.

  • Funkcje są samodzielnymi blokami używanymi głównie do celów obliczeniowych.
  • Funkcja używa słowa kluczowego RETURN, aby zwrócić wartość, a typ danych jest zdefiniowany w momencie tworzenia.
  • Funkcja powinna albo zwrócić wartość, albo zgłosić wyjątek, tj. Zwrot jest obowiązkowy w funkcjach.
  • Funkcję bez instrukcji DML można wywołać bezpośrednio w zapytaniu SELECT, natomiast funkcję z operacją DML można wywołać tylko 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ęść deklaracji (opcjonalna), część wykonawczą, część obsługi wyjątków (opcjonalna).
  • Wartości można przekazać do funkcji lub pobrać z procedury za pomocą parametrów.
  • Te parametry powinny być zawarte w instrukcji wywołującej.
  • Funkcja PLSQL może również zwrócić wartość przez parametry OUT inne niż użycie RETURN.
  • Ponieważ zawsze zwraca wartość, w instrukcji wywołującej zawsze towarzyszy operator przypisania do wypełnienia zmiennych.

Składnia

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION instruuje kompilator, aby utworzył nową funkcję. Słowo kluczowe „OR REPLACE” instruuje kompilator, aby zamienić istniejącą funkcję (jeśli istnieje) na aktualną.
  • Nazwa funkcji powinna być unikalna.
  • Należy wspomnieć o typie danych RETURN.
  • Słowo kluczowe „IS” będzie używane, gdy procedura zostanie zagnieżdżona w innych blokach. Jeśli procedura jest samodzielna, zostanie użyty „AS”. Poza tym standardem kodowania oba mają to samo znaczenie.

Przykład 1: Tworzenie funkcji i wywoływanie jej przy użyciu bloku anonimowego

W tym programie utworzymy funkcję, która przyjmuje nazwę jako dane wejściowe i zwraca wiadomość powitalną jako dane wyjściowe. Zamierzamy użyć anonimowego bloku i instrukcji select, aby wywołać funkcję.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_msg := welcome_msg_func (‘Guru99’);dbms_output.put_line(lv_msg);END;SELECT welcome_msg_func(‘Guru99:) FROM DUAL;

Objaśnienie kodu:

  • Linia kodu 1 : Tworzenie funkcji Oracle o nazwie „welcome_msg_func” i jednym parametrze „p_name” typu „IN”.
  • Linia kodu 2 : zadeklarowanie zwracanego typu jako VARCHAR2
  • Linia kodu 5 : Zwracanie połączonej wartości „Witamy” i wartości parametru.
  • Linia kodu 8 : Anonimowy blok wywołujący powyższą funkcję.
  • Linia kodu 9 : Zadeklarowanie zmiennej o typie danych takim samym, jak typ danych zwrotnych funkcji.
  • Linia kodu 11 : Wywołanie funkcji i zapełnienie zwracanej wartości do zmiennej „lv_msg”.
  • Linia kodu 12 : Drukowanie wartości zmiennej. Wynik, który tu uzyskasz, to „Welcome Guru99”
  • Linia kodu 14 : Wywołanie tej samej funkcji za pomocą instrukcji SELECT. Wartość zwracana jest kierowana bezpośrednio na standardowe wyjście.

Podobieństwa między procedurą a funkcją

  • Oba można wywołać z innych bloków PL / SQL.
  • Jeśli wyjątek zgłoszony w podprogramie nie jest obsługiwany w sekcji obsługi wyjątków podprogramu, to zostanie on propagowany do bloku wywołującego.
  • Oba mogą mieć dowolną liczbę parametrów.
  • Oba są traktowane jako obiekty bazy danych w PL / SQL.

Procedura vs. Funkcja: kluczowe różnice

Procedura Funkcjonować
  • Używany głównie do wykonania określonego procesu
  • Używany głównie do wykonywania obliczeń
  • Nie można wywołać w instrukcji SELECT
  • Funkcję, która nie zawiera instrukcji DML, można wywołać w instrukcji SELECT
  • Użyj parametru OUT, aby zwrócić wartość
  • Użyj RETURN, aby zwrócić wartość
  • Zwracanie wartości nie jest obowiązkowe
  • Zwrócenie wartości jest obowiązkowe
  • RETURN po prostu opuści sterowanie z podprogramu.
  • RETURN opuści sterowanie z podprogramu i również zwróci wartość
  • Typ danych zwrotnych nie zostanie określony w momencie tworzenia
  • Typ danych zwrotu 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 najczęściej używane funkcje i ich zastosowanie.

Funkcje konwersji

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

Nazwa funkcji Stosowanie Przykład
TO_CHAR Konwertuje inny typ danych na typ znakowy TO_CHAR (123);
TO_DATE (ciąg, format) Konwertuje podany ciąg na datę. Ciąg powinien pasować do formatu. TO_DATE („2015-JAN-15”, „RRRR-MON-DD”); Wyjście: 15.01.2015
TO_NUMBER (tekst, format) Konwertuje tekst na typ liczbowy podanego formatu. Informat '9' oznacza liczbę cyfr Wybierz TO_NUMBER ('1234', '9999') z podwójnego; Wyjście: 1234 Wybierz TO_NUMBER ('1,234,45', '9,999,99') z podwójnego; Wyjście: 1234

Funkcje łańcuchowe

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

Nazwa funkcji Stosowanie Przykład
INSTR (tekst, ciąg, początek, wystąpienie) Podaje pozycję określonego tekstu w podanym ciągu.
  • tekst - główny ciąg
  • string - tekst, który ma zostać przeszukany
  • start - pozycja początkowa wyszukiwania (opcjonalnie)
  • zgodność - wystąpienie szukanego ciągu (opcjonalne)
 Wybór INSTR ( "pokładowego, 'E', 2,1) z podwójnym Wyjście 2 wybrać INSTR (" pokładowego, 'E', 2,2), z dwoma wyjściowego: 9 (2 nd occurance E)
SUBSTR (tekst, początek, długość) Podaje wartość podłańcucha głównego ciągu.
  • tekst - główny ciąg
  • start - pozycja startowa
  • length - długość, która ma zostać podłańcuchowa
 wybierz substr ('samolot', 1,7) z dual Output : aeropla
UPPER (tekst) Zwraca wielką literę z podanego tekstu Wybierz górny („guru99”) z podwójnego; Wyjście : GURU99
LOWER (tekst) Zwraca małe litery z podanego tekstu Wybierz niższy („samolot”) z podwójnego; Wyjście : samolot
INITCAP (tekst) Zwraca podany tekst z wielką literą początkową. Wybierz ('guru99') z podwójnego wyjścia : Guru99 Wybierz ('moja historia') z podwójnego Wyjście : Moja historia
LENGTH (tekst) Zwraca długość podanego ciągu Wybierz LENGTH ('guru99') z dual; Wyjście : 6
LPAD (tekst, długość, znak_pada) Uzupełnia ciąg po lewej stronie podaną długość (całkowitą) podanym znakiem Wybierz LPAD ('guru99', 10, '$') z dual; Wyjście : $$$$ guru99
RPAD (tekst, długość, znak_pada) Uzupełnia ciąg po prawej stronie dla podanej długości (całkowitej) podanym znakiem Wybierz RPAD ('guru99', 10, '-') z podwójnego wyjścia : guru99 ----
LTRIM (tekst) Przycina wiodące białe znaki z tekstu Wybierz LTRIM ('Guru99') z dual; Wyjście : Guru99
RTRIM (tekst) Przycina końcowe białe znaki z tekstu Wybierz RTRIM ('Guru99') z dual; Wyjście ; Guru99

Funkcje daty

Są to funkcje używane do manipulowania datami.

Nazwa funkcji Stosowanie Przykład
ADD_MONTHS (data, liczba miesięcy) Dodaje podane miesiące do daty ADD_MONTH („2015-01-01”, 5); Wyjście : 01.05.2015
SYSDATE Zwraca bieżącą datę i godzinę serwera Wybierz SYSDATE z dual; Wyjście : 04.10.2015 14:11:43
TRUNC Zaokrąglij zmienną date do najniższej możliwej wartości wybierz sysdate, TRUNC (sysdate) z dual; Wyjście : 04.10.2015 14:12:39 10.04.2015
OKRĄGŁY Zaokrągla datę do najbliższego limitu wyższego lub niższego Wybierz sysdate, ROUND (sysdate) z dual Output : 10/4/2015 14:14:34 PM 10/5/2015
MONTHS_BETWEEN Zwraca liczbę miesięcy między dwiema datami Wybierz MONTHS_BETWEEN (sysdate + 60, sysdate) z podwójnego wyjścia : 2

Podsumowanie

W tym rozdziale dowiedzieliśmy się, co następuje.

  • Jak stworzyć Procedurę i różne sposoby jej nazywania
  • Jak stworzyć funkcję i różne sposoby jej nazywania
  • Podobieństwa i różnice między procedurą a funkcją
  • Parametry i POWRÓT wspólne terminologie w podprogramach PL / SQL
  • Typowe funkcje wbudowane w Oracle PL / SQL