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

Od: Fiona Brownová Fiona Brownová
Hours aktualizováno

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 vytvořit a uložit do databáze jako databázové objekty. Mohou být volány nebo odkazovány také v jiných blocích.

Kromě toho pokryjeme hlavní rozdíly mezi těmito dvěma podprogramy. Také budeme diskutovat o Oracle vestavěné funkce.

Terminologie v podprogramech PL/SQL

Než se seznámíme s podprogramy PL/SQL, probereme různé terminologie, které jsou součástí těchto podprogramů. Níže jsou uvedeny terminologie, o kterých budeme diskutovat.

Parametr

Parametr je proměnný nebo zástupný symbol jakéhokoli platného datový typ PL/SQL prostřednictvím kterého si podprogram PL/SQL vyměňuje hodnoty s hlavním kódem. Tento parametr umožňuje zadat vstup do podprogramů a extrahovat z těchto podprogramů.

  • Tyto parametry by měly být definovány spolu s podprogramy v době vytvoření.
  • Tyto parametry jsou obsaženy ve volacím příkazu těchto podprogramů pro interakci hodnot s podprogramy.
  • Datový typ parametru v podprogramu a volajícího příkazu by měly být stejné.
  • Velikost datového typu by neměla být uvedena v době deklarace parametru, protože velikost je pro tento typ dynamická.

Na základě jejich účelových parametrů jsou klasifikovány jako

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

Parametr IN

  • Tento parametr se používá pro zadávání vstupu do podprogramů.
  • Je to proměnná pouze pro čtení uvnitř podprogramů. Jejich hodnoty nelze v podprogramu měnit.
  • Ve volajícím příkazu mohou být tyto parametry proměnná nebo doslovná hodnota nebo výraz, například to může být aritmetický výraz jako „5*8“ nebo „a/b“, kde „a“ a „b“ jsou proměnné .
  • Ve výchozím nastavení jsou parametry typu IN.

Parametr OUT

  • Tento parametr se používá pro získání výstupu z podprogramů.
  • Je to proměnná pro čtení a zápis uvnitř podprogramů. Jejich hodnoty lze měnit uvnitř podprogramů.
  • Ve volajícím příkazu by tyto parametry měly být vždy proměnnou, která bude obsahovat hodnotu z aktuálních podprogramů.

Parametr IN OUT

  • Tento parametr se používá jak pro zadávání vstupu, tak pro získávání výstupu z podprogramů.
  • Je to proměnná pro čtení a zápis uvnitř podprogramů. Jejich hodnoty lze měnit uvnitř podprogramů.
  • Ve volajícím příkazu by tyto parametry měly být vždy proměnnou, která bude obsahovat hodnotu z podprogramů.

Tyto typy parametrů by měly být uvedeny při vytváření podprogramů.

VRÁTIT SE

RETURN je klíčové slovo, které dává kompilátoru pokyn k přepnutí řízení z podprogramu na volající příkaz. V podprogramu RETURN jednoduše znamená, že ovládací prvek potřebuje opustit podprogram. Jakmile kontrolér najde v podprogramu klíčové slovo RETURN, následující kód bude přeskočen.

Normálně nadřazený nebo hlavní blok zavolá podprogramy a poté se řízení přesune z těchto rodičovských bloků na volané podprogramy. RETURN v podprogramu vrátí ovládací prvek zpět do jejich rodičovského bloku. V případě funkcí příkaz RETURN také vrací hodnotu. Datový typ této hodnoty je vždy uveden v době deklarace funkce. Datový typ může být libovolného platného datového typu PL/SQL.

Co je procedura v PL/SQL?

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

Poznámka: Podprogram není nic jiného než procedura a musí být vytvořen ručně podle požadavku. Po vytvoření budou uloženy jako databázové objekty.

Níže jsou uvedeny charakteristiky jednotky podprogramu Procedure v PL/SQL:

  • Procedury jsou samostatné bloky programu, které lze uložit do databáze.
  • Volání těchto procedur PLSQL lze provést odkazem na jejich název a provést příkazy PL/SQL.
  • Používá se hlavně k provádění procesu v PL/SQL.
  • Může mít vnořené bloky nebo může být definován a vnořen do jiných bloků nebo balíčků.
  • Obsahuje část deklarace (nepovinná), prováděcí část, část zpracování výjimek (nepovinná).
  • Hodnoty lze předávat do Oracle procedura nebo načtená z procedury prostřednictvím parametrů.
  • Tyto parametry by měly být zahrnuty do příkazu volání.
  • Procedura v SQL může mít příkaz RETURN, který vrátí ovládací prvek do volajícího bloku, ale nemůže vrátit žádné hodnoty prostřednictvím příkazu RETURN.
  • Procedury nelze volat přímo z příkazů SELECT. Mohou být volány z jiného bloku nebo pomocí klíčového slova EXEC.

Syntax

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 kompilátor, aby vytvořil novou proceduru v Oracle. Klíčové slovo 'OR REPLACE' dává kompilaci pokyn k nahrazení existující procedury (pokud existuje) aktuální.
  • Název procedury by měl být jedinečný.
  • Klíčové slovo 'IS' bude použito, když je uložená procedura v Oracle je vnořena do některých dalších bloků. Pokud je postup samostatný, použije se „AS“. Kromě tohoto standardu kódování mají oba stejný význam.

Příklad1: Vytvoření procedury a její volání pomocí EXEC

V tomto příkladu vytvoříme Oracle procedura, která vezme jméno jako vstup a vytiskne uvítací zprávu jako výstup. K volání procedury použijeme 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’);

Vysvětlení kódu:

  • Řádek kódu 1: Vytvoření procedury s názvem 'welcome_msg' as jedním parametrem 'p_name' typu 'IN'.
  • Řádek kódu 4: Tisk uvítací zprávy zřetězením názvu vstupu.
  • Postup je úspěšně zkompilován.
  • Řádek kódu 7: Volání procedury pomocí příkazu EXEC s parametrem 'Guru99'. Procedura se provede a zpráva se vytiskne jako „Welcome Guru99“.

SOUVISEJÍCÍ ČLÁNKY

Co je Funkce?

Funkce je samostatný podprogram PL/SQL. Stejně jako procedura PL/SQL mají funkce jedinečný název, pod kterým se na ně lze odkazovat. Ty jsou uloženy jako databázové objekty PL/SQL. Níže jsou uvedeny některé charakteristiky funkcí.

  • Funkce jsou samostatný blok, který se používá hlavně pro účely výpočtu.
  • Funkce používá klíčové slovo RETURN k vrácení hodnoty a její datový typ je definován v době vytvoření.
  • Funkce by měla buď vrátit hodnotu, nebo vyvolat výjimku, tj. návrat je ve funkcích povinný.
  • Funkci bez příkazů DML lze volat přímo v dotazu SELECT, zatímco funkci s operací DML lze volat pouze z jiných bloků PL/SQL.
  • Může mít vnořené bloky nebo může být definován a vnořen do jiných bloků nebo balíčků.
  • Obsahuje část deklarace (nepovinná), prováděcí část, část zpracování výjimek (nepovinná).
  • Hodnoty mohou být předány do funkce nebo načteny z procedury prostřednictvím parametrů.
  • Tyto parametry by měly být zahrnuty do příkazu volání.
  • Funkce PLSQL může také vrátit hodnotu prostřednictvím parametrů OUT jinak než pomocí RETURN.
  • Protože vždy vrátí hodnotu, při volání příkazu vždy doprovází operátor přiřazení k naplnění proměnných.

Funkce v PL/SQL

Syntax

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 dává kompilátoru pokyn k vytvoření nové funkce. Klíčové slovo 'OR REPLACE' dává kompilátoru pokyn, aby nahradil existující funkci (pokud existuje) aktuální.
  • Název funkce by měl být jedinečný.
  • Je třeba uvést datový typ RETURN.
  • Klíčové slovo 'IS' bude použito, když je procedura vnořena do jiných bloků. Pokud je postup samostatný, použije se „AS“. Kromě tohoto standardu kódování mají oba stejný význam.

Příklad1: Vytvoření funkce a její volání pomocí anonymního bloku

V tomto programu vytvoříme funkci, která vezme jméno jako vstup a vrátí uvítací zprávu jako výstup. K volání funkce použijeme příkaz anonymního bloku a výběru.

Funkce v 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;

Vysvětlení kódu:

  • Řádek kódu 1: Vytvoření Oracle funkce s názvem 'welcome_msg_func' as jedním parametrem 'p_name' typu 'IN'.
  • Řádek kódu 2: deklaruje návratový typ jako VARCHAR2
  • Řádek kódu 5: Vrací zřetězenou hodnotu 'Vítejte' a hodnotu parametru.
  • Řádek kódu 8: Anonymní blok pro volání výše uvedené funkce.
  • Řádek kódu 9: Deklarování proměnné s datovým typem stejným jako návratový datový typ funkce.
  • Řádek kódu 11: Volání funkce a vyplnění návratové hodnoty do proměnné 'lv_msg'.
  • Řádek kódu 12: Tisk hodnoty proměnné. Výstup, který zde získáte, je „Welcome Guru99“
  • Řádek kódu 14: Volání stejné funkce prostřednictvím příkazu SELECT. Návratová hodnota je směrována přímo 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í zpracována v podprogramu zpracování výjimek sekce, pak se rozšíří 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.

Postup vs. Funkce: Klíčové rozdíly

Postup funkce
Používá se hlavně k provedení určitého procesu Používá se hlavně k provedení některých výpočtů
Nelze volat v příkazu SELECT Funkci, která neobsahuje žádné příkazy DML, lze volat v příkazu SELECT
Pomocí parametru OUT vrátíte hodnotu Pomocí RETURN vrátíte hodnotu
Vrácení hodnoty není povinné Je povinné vrátit hodnotu
RETURN jednoduše ukončí ovládání z podprogramu. RETURN ukončí řízení z podprogramu a také vrátí hodnotu
Návratový datový typ nebude v době vytvoření zadán Návratový datový typ je povinný v době vytvoření

Vestavěné funkce v PL/SQL

PL / SQL obsahuje různé vestavěné funkce pro práci s řetězci a datovým typem data. Zde se podíváme na běžně používané funkce a jejich použití.

Konverzní funkce

Tyto vestavěné funkce se používají k převodu jednoho datového typu na jiný datový typ.

Název funkce Používání Příklad
TO_CHAR Převede druhý datový typ na znakový datový typ TO_CHAR(123);
TO_DATE ( řetězec, formát ) Převede daný řetězec na datum. Řetězec by měl odpovídat formátu.

TO_DATE('2015-JAN-15', 'RRRR-MON-DD');

Výstup: 1 / 15 / 2015
TO_NUMBER (text, formát)

Převede text na číselný typ daného formátu.

Informat '9' označuje počet číslic

 Vyberte TO_NUMBER('1234′,'9999') z duálního;

Výstup: 1234

Vyberte TO_NUMBER('1,234.45′,'9,999.99') z duálního;

Výstup: 1234

Řetězcové funkce

Toto jsou funkce, které se používají na datovém typu znaku.

Název funkce Používání Příklad
INSTR(text, řetězec, začátek, výskyt) Udává pozici konkrétního textu v daném řetězci.

  • text – Hlavní řetězec
  • řetězec – text, který je třeba vyhledat
  • start – výchozí pozice vyhledávání (volitelné)
  • souhlas – výskyt hledaného řetězce (nepovinné)
 Vyberte INSTR('AEROPLANE','E',2,1) z duálního

Výstup: 2

Vyberte INSTR('AEROPLANE','E',2,2) z duálního

Výstup: 9 (2nd výskyt E)
SUBSTR ( text, začátek, délka) Udává hodnotu podřetězce hlavního řetězce.

  • text – hlavní řetězec
  • start – výchozí pozice
  • délka – délka, která má být podřetězcem
 select substr('aeroplane',1,7) from dual

Výstup: aeropla
HORNÍ ( text ) Vrátí velká písmena zadaného textu Vyberte horní('guru99') z dual;

Výstup: GURU99
DOLNÍ ( text ) Vrátí malá písmena zadaného textu Vyberte nižší ('AerOpLane') z duálního;

Výstup: letadlo
INITCAP ( text) Vrátí zadaný text s počátečním písmenem velkým. Vyberte ('guru99') z dual

Výstup: Guru99

Vyberte ('můj příběh') z dual

Výstup: Můj příběh
DÉLKA ( text ) Vrátí délku zadaného řetězce Vyberte LENGTH ('guru99') z dual;

Výstup: 6
LPAD ( text, délka, pad_char) Doplní řetězec na levé straně pro danou délku (celkový řetězec) daným znakem Vyberte LPAD('guru99', 10, '$') z dual;

Výstup: $$$$ guru99
RPAD (text, délka, pad_char) Doplní řetězec na pravou stranu pro danou délku (celkový řetězec) daným znakem Vyberte RPAD('guru99′,10,'-') z dual

Výstup: guru99—-
LTRIM ( text ) Ořízne úvodní bílé místo z textu Vyberte LTRIM(' Guru99') z dual;

Výstup: Guru99
RTRIM ( text ) Ořízne bílé místo na konci textu Vyberte RTRIM('Guru99') z dual;

Výstup; Guru99

Funkce data

Jedná se o funkce, které se používají pro manipulaci s daty.

Název funkce Používání Příklad
ADD_MONTHS (datum, počet měsíců) Přidá dané měsíce k datu ADD_MONTH('2015-01-01',5);

Výstup: 05 / 01 / 2015
SYSDATE Vrátí aktuální datum a čas serveru Vyberte SYSDATE z dual;

Výstup: 10. 4. 2015 2:11:43
KMEN Zaokrouhlení proměnné data na nižší možnou hodnotu vyberte sysdate, TRUNC(sysdate) z dual;

Výstup: 10. 4. 2015 2:12:39 10. 4. 2015
KOLO Zaokrouhlí datum na nejbližší limit buď vyšší nebo nižší Vyberte sysdate, ROUND(sysdate) z dual

Výstup: 10. 4. 2015 2:14:34 10. 5. 2015
MONTHS_BETWEEN Vrátí počet měsíců mezi dvěma daty Vyberte MONTHS_BETWEEN (sysdate+60, sysdate) z dual

Výstup: 2

Shrnutí

V této kapitole jsme se naučili následující.

  • Jak vytvořit proceduru a různé způsoby jejího volání
  • Jak vytvořit funkci a různé způsoby jejího volání
  • Podobnosti a rozdíly mezi procedurou a funkcí
  • Parametry a RETURN běžné terminologie v podprogramech PL/SQL
  • Běžné vestavěné funkce v Oracle PL / SQL

Máš rád: