Uložená procedura Oracle PL / SQL & Funkce s příklady

Obsah:

Anonim

V tomto tutoriálu uvidíte podrobný popis vytváření a provádění pojmenovaných bloků (procedur a funkcí).

Procedury a funkce jsou podprogramy, které lze vytvořit a uložit do databáze jako databázové objekty. Mohou být také voláni nebo odkazováni uvnitř ostatních bloků.

Kromě toho probereme hlavní rozdíly mezi těmito dvěma podprogramy. Budeme také diskutovat o vestavěných funkcích Oracle.

V tomto výukovém programu Oracle Stored Procedure se naučíte-

  • Terminologie v podprogramech PL / SQL
  • Co je procedura v PL / SQL?
  • Co je funkce?
  • Podobnosti mezi postupem a funkcí
  • Postup vs. Funkce: Klíčové rozdíly
  • Integrované funkce v PL / SQL

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 uvádíme terminologie, o kterých budeme diskutovat.

Parametr:

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

  • Tyto parametry by měly být definovány společně s podprogramy v době vytváření.
  • Tyto parametry jsou zahrnuty do volajícího příkazu těchto podprogramů pro interakci hodnot 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 zmínit v době deklarace parametru, protože velikost je pro tento typ dynamická.

Na základě jejich účelu jsou parametry klasifikovány jako

  1. V parametru
  2. Parametr OUT
  3. Parametr IN OUT

V parametru:

  • Tento parametr se používá pro zadávání vstupů do podprogramů.
  • Jedná se o proměnnou jen 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ávání výstupu z podprogramů.
  • Jedná se o proměnnou 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á drží hodnotu z aktuálních podprogramů.

IN OUT Parametr:

  • Tento parametr se používá jak pro zadávání vstupů, tak pro získávání výstupů z podprogramů.
  • Jedná se o proměnnou 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á drží hodnotu z podprogramů.

Tyto typy parametrů by měly být zmíněny v době vytváření podprogramů.

VRÁTIT SE

RETURN je klíčové slovo, které dává kompilátoru pokyn k přepnutí ovládacího prvku z podprogramu na volající příkaz. V podprogramu RETURN jednoduše znamená, že řízení musí opustit podprogram. Jakmile řídicí jednotka v podprogramu najde klíčové slovo RETURN, bude kód přeskočen.

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

Co je procedura v PL / SQL?

Postup v PL / SQL je podprogram jednotka, která se skládá ze skupiny příkazů PL / SQL, které lze nazvat podle názvu. Každá procedura v PL / SQL má svůj vlastní jedinečný název, pod kterým jej lze odkazovat a volat. Tato podprogramová jednotka v databázi Oracle je uložena jako databázový objekt.

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

Níže jsou vlastnosti jednotky podprogramu Procedura v PL / SQL:

  • Postupy jsou samostatné bloky programu, které lze uložit do databáze.
  • Volání na tyto procedury PLSQL lze provést odkazem na jejich jméno, aby bylo možné provést příkazy PL / SQL.
  • Používá se hlavně k provedení procesu v PL / SQL.
  • Může mít vnořené bloky nebo může být definováno a vnořeno do ostatních bloků nebo balíčků.
  • Obsahuje deklarační část (volitelná), prováděcí část, část pro zpracování výjimek (volitelná).
  • Hodnoty lze předat do procedury Oracle nebo načíst z procedury prostřednictvím parametrů.
  • Tyto parametry by měly být zahrnuty do volajícího příkazu.
  • Procedura v SQL může mít příkaz RETURN pro vrácení ovládacího prvku 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. Lze je volat z jiného bloku nebo pomocí klíčového slova EXEC.

Syntax:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE dává kompilátoru pokyn, aby v Oracle vytvořil nový postup. Klíčové slovo „NEBO VYMĚNIT“ dává kompilátoru pokyn k nahrazení stávající procedury (pokud existuje) současnou.
  • Název postupu by měl být jedinečný.
  • Klíčové slovo „IS“ bude použito, když je uložená procedura v Oracle 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říklad 1: Vytvoření procedury a její volání pomocí EXEC

V tomto příkladu vytvoříme proceduru Oracle, která vezme název jako vstup a vytiskne uvítací zprávu jako výstup. K vyvolání procedury použijeme příkaz EXEC.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_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' a s 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ě sestaven.
  • Řádek kódu 7 : Volání procedury pomocí příkazu EXEC s parametrem 'Guru99'. Procedura je provedena a zpráva je vytištěna jako „Welcome Guru99“.

Co je funkce?

Funkce je samostatný podprogram PL / SQL. Stejně jako procedura PL / SQL mají i funkce jedinečný název, na který je možné je odkazovat. Ty jsou uloženy jako objekty databáze PL / SQL. Níže uvádíme některé z charakteristik 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 jeho datový typ je definován v době vytvoření.
  • Funkce by měla buď vrátit hodnotu, nebo zvýšit výjimku, tj. Návrat je ve funkcích povinný.
  • Funkci bez příkazů DML lze přímo volat 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áno a vnořeno do ostatních bloků nebo balíčků.
  • Obsahuje deklarační část (volitelná), prováděcí část, část pro zpracování výjimek (volitelná).
  • Hodnoty lze předat do funkce nebo načíst z procedury prostřednictvím parametrů.
  • Tyto parametry by měly být zahrnuty do volajícího příkazu.
  • Funkce PLSQL může také vrátit hodnotu prostřednictvím parametrů OUT, než pomocí RETURN.
  • Protože vždy vrátí hodnotu, ve volajícím příkazu vždy doprovází operátor přiřazení k naplnění proměnných.

Syntax

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION dává kompilátoru pokyn, aby vytvořil novou funkci. Klíčové slovo „NEBO VYMĚNIT“ dává kompilátoru pokyn, aby nahradil stávající funkci (pokud existuje) aktuální.
  • Název funkce by měl být jedinečný.
  • Měl by být uveden datový typ RETURN.
  • Klíčové slovo „IS“ bude použito, pokud je procedura 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říklad 1: 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 vyvolání funkce použijeme anonymní příkaz block a select.

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;

Vysvětlení kódu:

  • Řádek kódu 1 : Vytvoření funkce Oracle s názvem „welcome_msg_func“ as jedním parametrem „p_name“ typu „IN“.
  • Řádek kódu 2 : deklarování návratového typu jako VARCHAR2
  • Řádek kódu 5 : Vrácení zřetězené hodnoty „Vítejte“ a hodnoty 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é jako návratový datový typ funkce.
  • Řádek kódu 11 : Volání funkce a naplně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 postupem a funkcí

  • Oba lze volat z jiných bloků PL / SQL.
  • Pokud výjimka vyvolaná v podprogramu není zpracována v sekci zpracování výjimek podprogramu, bude se šířit do volajícího bloku.
  • Oba mohou mít tolik parametrů, kolik je požadováno.
  • Oba jsou považovány za databázové objekty v PL / SQL.

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ějakého výpočtu
  • Nelze volat v příkazu SELECT
  • Funkci, která neobsahuje žádné příkazy DML, lze volat v příkazu SELECT
  • K vrácení hodnoty použijte parametr OUT
  • K vrácení hodnoty použijte RETURN
  • Vrácení hodnoty není povinné
  • Vrátit hodnotu je povinné
  • RETURN jednoduše opustí ovládání z podprogramu.
  • RETURN ukončí ovládání z podprogramu a také vrátí hodnotu
  • V době vytvoření nebude vrácen datový typ
  • V době vytvoření je povinný datový typ vrácení

Integrované funkce v PL / SQL

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

Konverzní funkce

Tyto integrované 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 datový typ znaků 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', 'YYYY-MON-DD'); Výstup: 15. 1. 2015
TO_NUMBER (text, formát) Převede text na typ čísla daného formátu. Informat '9' označuje počet číslic Vyberte TO_NUMBER ('1234', '9999') z dual; Výstup: 1234 Vyberte TO_NUMBER ('1 234,45', '9 999,99') z duálního; Výstup: 1234

Řetězcové funkce

Jedná se o funkce, které se používají v datovém typu znaku.

Název funkce Používání Příklad
INSTR (text, řetězec, začátek, výskyt) Dává pozici konkrétního textu v daném řetězci.
  • text - hlavní řetězec
  • řetězec - text, který je třeba prohledat
  • start - počáteční pozice hledání (volitelně)
  • souhlas - výskyt hledaného řetězce (volitelně)
 Vyberte INSTR ('AIRPLANE', 'E', 2,1) z duálního výstupu : 2 Vyberte INSTR ('AIRPLANE', 'E', 2,2) z duálního výstupu: 9 ( 2. výskyt E)
SUBSTR (text, začátek, délka) Dává hodnotu podřetězce hlavního řetězce.
  • text - hlavní řetězec
  • start - startovní pozice
  • délka - délka, která má být podřetězena
 vyberte substr ('airplane', 1,7) z duálního výstupu : 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žší („letadlo“) z duálního; Výstup : letadlo
INITCAP (text) Vrátí zadaný text s počátečním písmenem velkými písmeny. Vyberte ('guru99') z duálního výstupu : Guru99 Vyberte ('můj příběh') z duálního výstupu : Můj příběh
DÉLKA (text) Vrátí délku daného řetězce Vyberte LENGTH ('guru99') z dual; Výstup : 6
LPAD (text, délka, pad_char) Vloží řetězec na levé straně pro danou délku (celkový řetězec) s daným znakem Vyberte LPAD ('guru99', 10, '$') z dual; Výstup : $$$$ guru99
RPAD (text, délka, pad_char) Vloží řetězec na pravou stranu pro danou délku (celkový řetězec) s daným znakem Vyberte RPAD ('guru99', 10, '-') z duálního výstupu : guru99 ----
LTRIM (text) Ořízne úvodní prázdné místo z textu Vyberte LTRIM ('Guru99') z dual; Výstup : Guru99
RTRIM (text) Ořízne koncové prázdné místo z 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 duálního; Výstup : 4. 10. 2015 14:11:43
TRUNC Zaokrouhlení proměnné data na nižší možnou hodnotu vyberte sysdate, TRUNC (sysdate) z dual; Výstup : 4. 10. 2015 14:12:39 4. 4. 2015
KOLO Zaokrouhlí datum na nejbližší limit, buď vyšší, nebo nižší Vyberte sysdate, ROUND (sysdate) z duálního výstupu : 4/10/2015 14:14:34 PM 10/10/2015
MONTHS_BETWEEN Vrátí počet měsíců mezi dvěma daty Vyberte MONTHS_BETWEEN (sysdate + 60, sysdate) z duálního výstupu : 2

souhrn

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

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