Uložená procedúra Oracle PL / SQL & Funkcie s príkladmi

Obsah:

Anonim

V tomto tutoriále sa dozviete podrobný popis spôsobu vytvárania a vykonávania pomenovaných blokov (procedúr a funkcií).

Procedúry a funkcie sú podprogramy, ktoré je možné vytvárať a ukladať do databázy ako databázové objekty. Dajú sa volať alebo odkazovať aj do ostatných blokov.

Okrem toho sa budeme venovať hlavným rozdielom medzi týmito dvoma podprogramami. Ďalej sa budeme zaoberať zabudovanými funkciami systému Oracle.

V tomto výučbe Oracle Stored Procedure sa naučíte -

  • Terminológie v podprogramoch PL / SQL
  • Čo je postup v PL / SQL?
  • Čo je funkcia?
  • Podobnosti medzi postupom a funkciou
  • Postup vs. Funkcia: Kľúčové rozdiely
  • Zabudované funkcie v PL / SQL

Terminológie v podprogramoch PL / SQL

Predtým, ako sa dozvieme o podprogramoch PL / SQL, prediskutujeme rôzne terminológie, ktoré sú súčasťou týchto podprogramov. Ďalej uvádzame terminológie, o ktorých sa chystáme diskutovať.

Parameter:

Parameter je premenná alebo zástupný symbol ľubovoľného platného dátového typu PL / SQL, prostredníctvom ktorého si podprogram PL / SQL vymieňa hodnoty s hlavným kódom. Tento parameter umožňuje zadávať podprogramy a extrahovať ich z týchto podprogramov.

  • Tieto parametre by sa mali definovať spolu s podprogrammi v čase ich vytvárania.
  • Tieto parametre sú zahrnuté vo volacom vyhlásení týchto podprogramov na interakciu s hodnotami s podprogrammi.
  • Dátový typ parametra v podprograme a volajúcom príkaze by mal byť rovnaký.
  • V čase vyhlásenia parametra by sa nemala uvádzať veľkosť údajového typu, pretože pre tento typ je veľkosť dynamická.

Podľa ich účelu sú parametre klasifikované ako

  1. Parameter IN
  2. Parameter OUT
  3. Parameter IN OUT

IN parameter:

  • Tento parameter sa používa na zadávanie vstupov do podprogramov.
  • Je to premenná iba na čítanie vo vnútri podprogramov. Ich hodnoty nie je možné meniť vo vnútri podprogramu.
  • Vo volajúcom príkaze môžu byť týmito parametrami premenná alebo literálna hodnota alebo výraz, napríklad to môže byť aritmetický výraz ako „5 * 8“ alebo „a / b“, kde „a“ a „b“ sú premenné. .
  • Štandardne sú parametre typu IN.

Parameter OUT:

  • Tento parameter sa používa na získanie výstupu z podprogramov.
  • Je to premenná na čítanie a zápis vo vnútri podprogramov. Ich hodnoty je možné meniť vo vnútri podprogramov.
  • Vo volajúcom príkaze by tieto parametre mali byť vždy premennou, ktorá drží hodnotu z aktuálnych podprogramov.

IN OUT Parameter:

  • Tento parameter sa používa ako na zadávanie vstupov, tak na získanie výstupov z podprogramov.
  • Je to premenná na čítanie a zápis vo vnútri podprogramov. Ich hodnoty je možné meniť vo vnútri podprogramov.
  • Vo volajúcom príkaze by tieto parametre mali byť vždy premennou, ktorá drží hodnotu z podprogramov.

Tieto typy parametrov by sa mali uviesť pri vytváraní podprogramov.

NÁVRAT

RETURN je kľúčové slovo, ktoré dáva kompilátoru pokyn na prepnutie ovládacieho prvku z podprogramu na volací príkaz. V podprograme RETURN jednoducho znamená, že riadenie musí opustiť podprogram. Akonáhle kontrolér nájde v podprograme kľúčové slovo RETURN, bude nasledujúci kód preskočený.

Za normálnych okolností nadradený alebo hlavný blok zavolá podprogramy a potom sa kontrola presunie z nadradeného bloku do volaných podprogramov. RETURN v podprograme vráti kontrolu späť do jej nadradeného bloku. V prípade funkcií príkaz RETURN vráti aj hodnotu. Datový typ tejto hodnoty je vždy uvedený v čase deklarácie funkcie. Dátový typ môže byť ľubovoľného platného dátového typu PL / SQL.

Čo je postup v PL / SQL?

Postup v PL / SQL je podprogram jednotka, ktorá sa skladá zo skupiny príkazov PL / SQL, ktoré možno nazvať podľa názvu. Každý postup v PL / SQL má svoj vlastný jedinečný názov, pod ktorým ho možno označiť a zavolať. Táto podprogramová jednotka v databáze Oracle je uložená ako databázový objekt.

Poznámka: Podprogram nie je nič iné ako postup a je potrebné ho vytvoriť manuálne podľa požiadavky. Po vytvorení sa uložia ako databázové objekty.

Ďalej sú uvedené vlastnosti jednotky podprogramu Procedúra v PL / SQL:

  • Procedúry sú samostatné bloky programu, ktoré je možné uložiť do databázy.
  • Tieto procedúry PLSQL je možné volať odkazom na ich meno, aby sa mohli vykonať príkazy PL / SQL.
  • Používa sa hlavne na vykonanie procesu v PL / SQL.
  • Môže mať vnorené bloky alebo môže byť definované a vnorené do ostatných blokov alebo balíkov.
  • Obsahuje časť vyhlásenia (voliteľné), časť vykonania, časť zaoberajúcu sa výnimkami (voliteľné).
  • Hodnoty môžu byť odovzdané do procedúry Oracle alebo načítané z procedúry cez parametre.
  • Tieto parametre by mali byť obsiahnuté vo volajúcom príkaze.
  • Procedúra v SQL môže mať príkaz RETURN na vrátenie ovládacieho prvku do volajúceho bloku, ale nemôže vrátiť žiadne hodnoty prostredníctvom príkazu RETURN.
  • Procedúry nemožno volať priamo z príkazov SELECT. Dajú sa zavolať z iného bloku alebo pomocou kľúčového slova EXEC.

Syntax:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE dáva pokyn kompilátoru, aby vytvoril nový postup v systéme Oracle. Kľúčové slovo „ALEBO VYMENIŤ“ dá kompilácii pokyn, aby nahradila existujúci postup (ak existuje) súčasným.
  • Názov postupu by mal byť jedinečný.
  • Kľúčové slovo „IS“ sa použije, keď je uložená procedúra v systéme Oracle vnorená do niektorých ďalších blokov. Ak je postup samostatný, použije sa „AS“. Okrem tohto štandardu kódovania majú oba rovnaký význam.

Príklad1: Vytvorenie procedúry a jej vyvolanie pomocou EXEC

V tomto príklade vytvoríme procedúru Oracle, ktorá vezme meno ako vstup a vytlačí uvítaciu správu ako výstup. Na vyvolanie procedúry použijeme príkaz EXEC.

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

Vysvetlenie kódu:

  • Riadok kódu 1 : Vytvorenie procedúry s názvom „welcome_msg“ a s jedným parametrom „p_name“ typu „IN“.
  • Riadok kódu 4 : Tlač uvítacej správy zreťazením názvu vstupu.
  • Postup je úspešne zostavený.
  • Riadok kódu 7 : Zavolanie procedúry pomocou príkazu EXEC s parametrom „Guru99“. Procedúra sa vykoná a správa sa vytlačí ako „Vitajte Guru99“.

Čo je funkcia?

Functions je samostatný podprogram PL / SQL. Rovnako ako procedúra PL / SQL, aj funkcie majú jedinečný názov, pod ktorým ich možno označiť. Ukladajú sa ako databázové objekty PL / SQL. Ďalej uvádzame niektoré z charakteristík funkcií.

  • Funkcie sú samostatný blok, ktorý sa používa hlavne na účely výpočtu.
  • Funkcia používa kľúčové slovo RETURN na vrátenie hodnoty a jej dátový typ je definovaný v čase vytvorenia.
  • Funkcia by mala buď vrátiť hodnotu, alebo zvýšiť výnimku, tj návrat je vo funkciách povinný.
  • Funkciu bez príkazov DML je možné priamo volať v dotaze SELECT, zatiaľ čo funkciu s operáciou DML možno volať iba z iných blokov PL / SQL.
  • Môže mať vnorené bloky alebo môže byť definované a vnorené do ostatných blokov alebo balíkov.
  • Obsahuje časť vyhlásenia (voliteľné), časť vykonania, časť zaoberajúcu sa výnimkami (voliteľné).
  • Hodnoty je možné prenášať do funkcie alebo načítať z procedúry prostredníctvom parametrov.
  • Tieto parametre by mali byť obsiahnuté vo volajúcom príkaze.
  • Funkcia PLSQL môže tiež vrátiť hodnotu cez parametre OUT inak ako pomocou RETURN.
  • Pretože vždy vráti hodnotu, vo volajúcom príkaze vždy sprevádza operátor priradenia na vyplnenie premenných.

Syntax

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION dáva pokyn kompilátoru, aby vytvoril novú funkciu. Kľúčové slovo „OR REPLACE“ dáva kompilátoru pokyn, aby nahradil existujúcu funkciu (ak existuje) súčasnou.
  • Názov funkcie by mal byť jedinečný.
  • Je potrebné spomenúť dátový typ RETURN.
  • Ak je postup vnorený do niektorých ďalších blokov, použije sa kľúčové slovo „IS“. Ak je postup samostatný, použije sa „AS“. Okrem tohto štandardu kódovania majú oba rovnaký význam.

Príklad1: Vytvorenie funkcie a jej volanie pomocou anonymného bloku

V tomto programe vytvoríme funkciu, ktorá vezme meno ako vstup a vráti uvítaciu správu ako výstup. Na vyvolanie funkcie použijeme anonymný prí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;

Vysvetlenie kódu:

  • Riadok kódu 1 : Vytvorenie funkcie Oracle s názvom „welcome_msg_func“ a s jedným parametrom „p_name“ typu „IN“.
  • Riadok kódu 2 : deklarovanie návratového typu ako VARCHAR2
  • Riadok kódu 5 : Vrátenie zreťazenej hodnoty „Vitajte“ a hodnoty parametra.
  • Riadok kódu 8 : Anonymný blok na volanie vyššie uvedenej funkcie.
  • Riadok kódu 9 : Deklarovanie premennej s dátovým typom rovnaké ako návratový dátový typ funkcie.
  • Riadok kódu 11 : Zavolanie funkcie a vyplnenie návratovej hodnoty do premennej „lv_msg“.
  • Riadok kódu 12 : Tlač hodnoty premennej. Výstup, ktorý tu získate, je „Vitajte Guru99“
  • Riadok kódu 14 : Volanie tej istej funkcie prostredníctvom príkazu SELECT. Návratová hodnota je smerovaná priamo na štandardný výstup.

Podobnosti medzi postupom a funkciou

  • Do oboch je možné volať z iných blokov PL / SQL.
  • Ak výnimka vyvolaná v podprograme nie je spracovaná v sekcii spracovania výnimiek podprogramu, potom sa rozšíri do volajúceho bloku.
  • Oba môžu mať toľko požadovaných parametrov.
  • Oba sú považované za databázové objekty v PL / SQL.

Postup vs. Funkcia: Kľúčové rozdiely

Postup Funkcia
  • Používa sa hlavne na vykonanie určitého procesu
  • Používa sa hlavne na vykonanie určitého výpočtu
  • Nie je možné volať príkaz SELECT
  • Funkciu, ktorá neobsahuje žiadne príkazy DML, je možné vyvolať v príkaze SELECT
  • Na vrátenie hodnoty použite parameter OUT
  • Na vrátenie hodnoty použite RETURN
  • Vrátenie hodnoty nie je povinné
  • Vrátenie hodnoty je povinné
  • RETURN jednoducho opustí riadenie z podprogramu.
  • RETURN ukončí kontrolu z podprogramu a vráti tiež hodnotu
  • Vrátený údajový typ nebude v čase vytvorenia zadaný
  • Vrátený údajový typ je povinný v čase vytvorenia

Zabudované funkcie v PL / SQL

PL / SQL obsahuje rôzne vstavané funkcie na prácu s reťazcami a dátovým typom dátumu. Tu uvidíme bežne používané funkcie a ich využitie.

Konverzné funkcie

Tieto vstavané funkcie sa používajú na prevod jedného údajového typu na druhý.

Názov funkcie Využitie Príklad
TO_CHAR Skonvertuje druhý údajový typ na znakový údajový typ TO_CHAR (123);
TO_DATE (reťazec, formát) Skonvertuje daný reťazec na dátum. Reťazec by sa mal zhodovať s formátom. TO_DATE ('2015-JAN-15', 'RRRR-PO-DD'); Výstup: 15. 1. 2015
TO_NUMBER (text, formát) Prevedie text na typ čísla daného formátu. Informat '9' označuje počet číslic Vyberte TO_NUMBER ('1234', '9999') z duálu; Výstup: 1234 Vyberte TO_NUMBER ('1 234,45', '9 999,99') z duálu; Výstup: 1234

Funkcie reťazca

Toto sú funkcie, ktoré sa používajú v dátovom type znaku.

Názov funkcie Využitie Príklad
INSTR (text, reťazec, začiatok, výskyt) Poskytuje pozíciu konkrétneho textu v danom reťazci.
  • text - hlavný reťazec
  • string - text, ktorý treba vyhľadať
  • štart - počiatočná pozícia vyhľadávania (voliteľné)
  • súlade - výskyt hľadaného reťazca (voliteľné)
 Vyberte INSTR ('AIRPLANE', 'E', 2,1) z duálneho výstupu : 2 Vyberte INSTR ('AIRPLANE', 'E', 2,2) z duálneho výstupu: 9 ( 2. výskyt E)
SUBSTR (text, začiatok, dĺžka) Poskytuje hodnotu podreťazca hlavného reťazca.
  • text - hlavný reťazec
  • štart - východisková pozícia
  • dĺžka - dĺžka, ktorá sa má pripútať
 vyberte substr ('airplane', 1,7) z duálneho Výstup : aeropla
HORNÁ (text) Vráti veľké písmená zadaného textu Vyberte zvrchu ('guru99') z duálu; Výstup : GURU99
LOWER (text) Vráti malé písmená zadaného textu Vyberte nižšie („lietadlo“) z duálneho; Výstup : lietadlo
INITCAP (text) Vráti zadaný text s veľkým začiatočným písmenom. Vyberte ('guru99') z duálneho výstupu : Guru99 Vyberte ('môj príbeh') z duálneho výstupu : Môj príbeh
LENGTH (text) Vráti dĺžku daného reťazca Vyberte LENGTH ('guru99') z duálu; Výstup : 6
LPAD (text, dĺžka, pad_char) Vložte reťazec na ľavej strane pre danú dĺžku (celkový reťazec) s daným znakom Vyberte LPAD ('guru99', 10, '$') z duálu; Výstup : $$$$ guru99
RPAD (text, dĺžka, pad_char) Vložte reťazec na pravej strane pre danú dĺžku (celkový reťazec) s daným znakom Vyberte RPAD ('guru99', 10, '-') z duálneho výstupu : guru99 ----
LTRIM (text) Orezanie úvodného bieleho miesta z textu Vyberte LTRIM ('Guru99') z duálu; Výstup : Guru99
RTRIM (text) Orezanie koncového bieleho miesta z textu Vyberte RTRIM ('Guru99') z duálu; Výstup ; Guru99

Funkcie dátumu

Toto sú funkcie, ktoré sa používajú na manipuláciu s dátumami.

Názov funkcie Využitie Príklad
ADD_MONTHS (dátum, počet mesiacov) K dátumu pripočíta dané mesiace ADD_MONTH ('2015-01-01', 5); Výstup : 01.01.2015
SYSDATE Vráti aktuálny dátum a čas servera Vyberte SYSDATE z duálu; Výstup : 4. 10. 2015 14:11:43
TRUNC Premenná zaokrúhlenia dátumu na nižšiu možnú hodnotu vyberte sysdate, TRUNC (sysdate) z dual; Výstup : 4. 10. 2015 14:12:39 4. 10. 2015
OKRÚHLY Zaokrúhli dátum na najbližší vyšší alebo nižší limit Vyberte sysdate, ROUND (sysdate) z duálneho výstupu : 4. 10. 2015 14:14:34 PM 10.10.2015
MONTHS_BETWEEN Vráti počet mesiacov medzi dvoma dátumami Vyberte MONTHS_BETWEEN (sysdate + 60, sysdate) z duálneho výstupu : 2

Zhrnutie

V tejto kapitole sme sa dozvedeli nasledujúce.

  • Ako vytvoriť postup a rôzne spôsoby jeho volania
  • Ako vytvoriť funkciu a rôzne spôsoby jej volania
  • Podobnosti a rozdiely medzi postupom a funkciou
  • Parametre a RETURN bežné terminológie v podprogramoch PL / SQL
  • Bežné vstavané funkcie v Oracle PL / SQL