Oracle Uložená procedura a funkce PL/SQL s příklady

Co jsou to podprogramy PL/SQL?

V tomto tutoriálu uvidíte podrobný popis, jak vytvořit a spustit pojmenované bloky, procedury a funkce.

Procedury a funkce jsou podprogramy, které lze vytvářet a ukládat do databáze jako databázové objekty. Lze je volat nebo na ně odkazovat i uvnitř jiných bloků.

Také se zabýváme hlavními rozdíly mezi těmito dvěma podprogramy a diskutujeme o... Oracle vestavěné funkce.

Terminologie v podprogramech PL/SQL

Než se budeme věnovat podprogramům v PL/SQL, probereme si různé terminologie, které jsou součástí těchto podprogramů.

Parametr

Parametr je proměnná nebo zástupný symbol pro jakýkoli platný Datový typ PL/SQL pomocí kterého si podprogram PL/SQL vyměňuje hodnoty s hlavním kódem. Tento parametr umožňuje vstup do podprogramů a extracodvozování hodnot z nich.

• Tyto parametry by měly být definovány spolu s podprogramy v době vytvoření.
• Jsou zahrnuty ve volajícím příkazu pro interakci s podprogramy.
• Datový typ parametru v podprogramu a volajícím příkazu by měl být stejný.
• Velikost datového typu by se neměla uvádět při deklaraci parametru, protože je dynamická.

Podle jejich účelu se parametry dělí na:

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

Parametr IN

• Používá se pro zadávání vstupů do podprogramů.
• Je to proměnná pouze pro čtení uvnitř podprogramů; její hodnotu nelze uvnitř podprogramu změnit.
• Ve volajícím příkazu to může být proměnná, literální hodnota nebo výraz, například '5*8' nebo 'a/b'.
• Ve výchozím nastavení jsou parametry typu IN.

Parametr OUT

• Používá se pro získání výstupu z podprogramů.
• Je to proměnná pro čtení i zápis uvnitř podprogramů; její hodnotu lze v nich měnit.
• Ve volajícím příkazu by měla být vždy proměnná, která bude obsahovat hodnotu z podprogramu.

Parametr IN OUT

• Používá se jak pro zadávání vstupů, tak pro získávání výstupů z podprogramů.
• Je to proměnná pro čtení i zápis uvnitř podprogramů; její hodnotu lze v nich měnit.
• Ve volajícím příkazu by měla být vždy proměnná, která bude obsahovat hodnotu z podprogramu.

Typ parametru by měl být uveden při vytváření podprogramů.

VRÁTIT SE

RETURN je klíčové slovo, které instruuje kompilátor k přepnutí řízení z podprogramu na volající příkaz. V podprogramu RETURN jednoduše znamená, že řízení musí podprogram ukončit; jakmile kontrolér najde RETURN, kód za ním se přeskočí.

Normálně nadřazený nebo hlavní blok volá podprogramy a řízení se přesune z nadřazeného bloku na volaný podprogram. Příkaz RETURN v podprogramu vrací řízení zpět do nadřazeného bloku. V případě funkcí příkaz RETURN také vrací hodnotu, jejíž datový typ je uveden v okamžiku deklarace funkce.

Co je to procedura v PL/SQL?

A Postup V PL/SQL je podprogramová jednotka, která se skládá ze skupiny příkazů PL/SQL, které lze volat jménem. Každá procedura má svůj vlastní jedinečný název a je uložena v Oracle databázi jako databázový objekt.

Poznámka: Podprogram není nic jiného než procedura a je třeba jej vytvořit ručně podle požadavku. Po vytvoření se uloží jako databázový objekt.

Charakteristiky jednotky podprogramu procedury v PL/SQL jsou:

• Procedury jsou samostatné bloky, které lze uložit do databáze.
• Mohou být volány svým jménem pro spuštění příkazů PL/SQL.
• Používají se hlavně k provedení procesu.
• Mohou mít vnořené bloky nebo být vnořeny do jiných bloků či balíčků.
• Obsahují deklarační část (volitelné), prováděcí část a část pro ošetření výjimek (volitelné).
• Hodnoty lze do procedury předávat nebo z ní načítat pomocí parametrů.
• Tyto parametry by měly být zahrnuty do příkazu volání.
• Procedura může mít příkaz RETURN pro vrácení řízení volajícímu bloku, ale nemůže vrátit žádnou hodnotu prostřednictvím příkazu RETURN.
• Procedury nelze volat přímo z příkazů SELECT; lze je volat z jiného bloku nebo pomocí klíčového slova EXEC.

Syntax

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

• Příkaz CREATE PROCEDURE instruuje kompilátor k vytvoření nové procedury. Klíčové slovo 'OR REPLACE' instruuje kompilátor k nahrazení existující procedury (pokud existuje) aktuální procedurou.
• Název procedury by měl být jedinečný.
• Klíčové slovo „IS“ se používá, když je uložená procedura vnořená uvnitř jiného bloku. Pokud je procedura samostatná, používá se „AS“. Kromě tohoto kódovacího standardu mají oba výrazy stejný význam.

Příklad 1: Vytvoření procedury a její volání pomocí EXEC. V tomto příkladu vytvoříme Oracle procedura, která přijímá jméno jako vstup a tiskne uvítací zprávu jako výstup, přičemž k jejímu volání se používá příkaz 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 Vysvětlení:

• Code řádek 1: Vytvoření procedury s názvem 'welcome_msg' a jedním parametrem 'p_name' typu 'IN'.
• Code řádek 4: Výpis uvítací zprávy zřetězením vstupního názvu.
• Procedura byla úspěšně zkompilována.
• Code řádek 7: Volání procedury pomocí EXEC s parametrem 'Guru99'. Procedura se provede a vypíše „Vítejte Guru99. "

Co je funkce?

Funkce je samostatný podprogram v PL/SQL. Stejně jako procedura má funkce jedinečný název a je uložena jako databázový objekt PL/SQL. Její charakteristiky jsou:

• Funkce jsou samostatné bloky používané hlavně pro výpočty.
• Funkce používá klíčové slovo RETURN k vrácení hodnoty, jejíž datový typ je definován v době vytvoření.
• Funkce by měla buď vrátit hodnotu, nebo vyvolat výjimku; návrat je ve funkcích povinný.
• Funkci bez příkazů DML lze volat přímo v dotazu SELECT, zatímco funkci s DML lze volat pouze z jiných bloků PL/SQL.
• Může mít vnořené bloky nebo být vnořen do jiných bloků či balíčků.
• Obsahuje deklarační část (volitelné), prováděcí část a část pro ošetření výjimek (volitelné).
• Hodnoty lze do funkce předávat nebo z ní načítat pomocí parametrů.
• Tyto parametry by měly být zahrnuty do příkazu volání.
• Funkce může kromě použití funkce RETURN vracet hodnotu také prostřednictvím parametrů OUT.
• Protože vždy vrací hodnotu, volající příkaz vždy používá operátor přiřazení k naplnění proměnné.

Syntax

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

• Příkaz CREATE FUNCTION instruuje kompilátor k vytvoření nové funkce. Příkaz 'OR REPLACE' instruuje kompilátor k nahrazení existující funkce (pokud existuje) aktuální funkcí.
• Název funkce by měl být jedinečný.
• Je třeba zmínit datový typ RETURN.
• Klíčové slovo 'IS' se používá, když je funkce vnořená uvnitř jiného bloku. Pokud je funkce samostatná, používá se 'AS'.

Příklad 1: Vytvoření funkce a její volání pomocí anonymního bloku. V tomto programu vytvoříme funkci, která přijímá jméno jako vstup a vrací uvítací zprávu, přičemž k jejímu volání použijeme anonymní blok a příkaz SELECT.

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 Vysvětlení:

• Code řádek 1: Vytvoření funkce s názvem 'welcome_msg_func' a jedním parametrem 'p_name' typu 'IN'.
• Code řádek 2: Deklarace návratového typu jako VARCHAR2.
• Code řádek 5: Vrátí zřetězenou hodnotu 'Vítejte' a hodnotu parametru.
• Code řádek 8: Anonymní blok pro volání výše uvedené funkce.
• Code řádek 9: Deklarace proměnné se stejným datovým typem, jako je návratový typ funkce.
• Code řádek 11: Volání funkce a naplnění návratové hodnoty do proměnné 'lv_msg'.
• Code řádek 12: Výpis hodnoty proměnné. Výstup je „Vítejte“. Guru99. "
• Code řádek 14: Volání stejné funkce pomocí příkazu SELECT. Návratová hodnota je směrována na standardní výstup.

Podobnosti mezi procedurou a funkcí

• Oba lze volat z jiných bloků PL/SQL.
• Pokud výjimka vyvolaná v podprogramu není ošetřena v jeho zpracování výjimek sekce, šíří se do volajícího bloku.
• Oba mohou mít libovolný počet parametrů.
• Oba jsou v PL/SQL považovány za databázové objekty.

Procedura vs. funkce: Klíčové rozdíly

Vestavěné funkce v PL/SQL

PL / SQL obsahuje různé vestavěné funkce pro práci s datovými typy řetězců a data. Zde uvidíme běžně používané funkce a jejich použití.

Konverzní funkce

Tyto vestavěné funkce převádějí jeden datový typ na jiný.

Řetězcové funkce

Tyto funkce se používají s datovým typem znak.

Funkce data

Tyto funkce se používají k manipulaci s daty.

Nejčastější dotazy