Oracle PL/SQL tárolt eljárások és függvények példákkal

By: Fiona Brown Fiona Brown
Hours korszerűsített

Ebben az oktatóanyagban részletes leírást fog látni a megnevezett blokkok létrehozásáról és végrehajtásáról (eljárások és funkciók).

Az eljárások és függvények azok az alprogramok, amelyek adatbázis-objektumként létrehozhatók és elmenthetők az adatbázisban. A többi blokkon belül is hívhatók vagy hivatkozhatók.

Ezen kívül kitérünk a két alprogram közötti főbb különbségekre. Emellett megbeszéljük a Oracle beépített funkciók.

Terminológiák a PL/SQL alprogramokban

Mielőtt megismerkednénk a PL/SQL alprogramokkal, megvitatjuk az alprogramok részét képező különféle terminológiákat. Az alábbiakban felsoroljuk azokat a terminológiákat, amelyekről beszélni fogunk.

Vizsgált paraméter

A paraméter bármely érvényes változó vagy helyőrzője PL/SQL adattípus amelyen keresztül a PL/SQL alprogram kicseréli az értékeket a fő kóddal. Ez a paraméter lehetővé teszi, hogy bemenetet adjon az alprogramokhoz, és kinyerjen ezekből az alprogramokból.

  • Ezeket a paramétereket az alprogramokkal együtt kell meghatározni a létrehozáskor.
  • Ezek a paraméterek szerepelnek ezen alprogramok hívó utasításában, hogy kölcsönhatásba léphessenek az értékek és az alprogramok között.
  • A paraméter adattípusának az alprogramban és a hívó utasításban meg kell egyeznie.
  • Az adattípus méretét nem szabad feltüntetni a paraméter deklarálásakor, mivel a méret ennél a típusnál dinamikus.

Céljuk alapján a paraméterek besorolása a

  1. IN paraméter
  2. OUT paraméter
  3. IN OUT Paraméter

IN paraméter

  • Ez a paraméter az alprogramok bevitelére szolgál.
  • Ez egy csak olvasható változó az alprogramokon belül. Értékük az alprogramon belül nem módosítható.
  • A hívó utasításban ezek a paraméterek lehetnek változók vagy literális értékek vagy kifejezések, például lehetnek számtani kifejezések, például '5*8' vagy 'a/b', ahol az 'a' és 'b' változók. .
  • Alapértelmezés szerint a paraméterek IN típusúak.

OUT paraméter

  • Ez a paraméter az alprogramok kimenetének lekérésére szolgál.
  • Ez egy olvasható-írható változó az alprogramokon belül. Értékük az alprogramokon belül módosítható.
  • A hívó utasításban ezeknek a paramétereknek mindig olyan változóknak kell lenniük, amelyek az aktuális alprogramokból származó értékeket tárolják.

IN OUT Paraméter

  • Ez a paraméter mind bemenet adására, mind az alprogramok kimenetének lekérésére szolgál.
  • Ez egy olvasható-írható változó az alprogramokon belül. Értékük az alprogramokon belül módosítható.
  • A hívó utasításban ezeknek a paramétereknek mindig olyan változóknak kell lenniük, amelyek az alprogramokból származó értéket tárolják.

Ezeket a paramétertípusokat meg kell említeni az alprogramok létrehozásakor.

VISSZATÉRÉS

A RETURN az a kulcsszó, amely arra utasítja a fordítót, hogy a vezérlést az alprogramról a hívó utasításra váltsa. A RETURN alprogramban egyszerűen azt jelenti, hogy a vezérlőnek ki kell lépnie az alprogramból. Ha a vezérlő megtalálta a RETURN kulcsszót az alprogramban, az ezt követő kód kimarad.

Normális esetben a szülő- vagy főblokk hívja meg az alprogramokat, majd a vezérlés átvált ezekről a szülőblokkokról a meghívott alprogramokra. A RETURN az alprogramban visszaállítja a vezérlést a szülőblokkjába. Függvények esetén a RETURN utasítás is visszaadja az értéket. Ennek az értéknek az adattípusa mindig szerepel a függvény deklarációjakor. Az adattípus bármilyen érvényes PL/SQL adattípus lehet.

Mi az az eljárás a PL/SQL-ben?

A Eljárás A PL/SQL-ben egy alprogram-egység, amely név szerint hívható PL/SQL utasítások csoportjából áll. A PL/SQL-ben minden eljárásnak megvan a maga egyedi neve, amellyel hivatkozni lehet rá és meghívható. Ez az alprogram egység a Oracle adatbázis adatbázis-objektumként van tárolva.

Jegyzet: Az alprogram nem más, mint egy eljárás, amelyet a követelményeknek megfelelően manuálisan kell létrehozni. Létrehozásuk után adatbázis-objektumként kerülnek tárolásra.

Az alábbiakban a Procedure alprogram egység jellemzői PL/SQL-ben:

  • Az eljárások egy program önálló blokkjai, amelyek a programban tárolhatók adatbázis.
  • Ezek a PLSQL eljárások a nevükre való hivatkozással hívhatók meg a PL/SQL utasítások végrehajtásához.
  • Főleg folyamatok végrehajtására szolgál PL/SQL-ben.
  • Lehetnek benne beágyazott blokkok, vagy definiálható és beágyazható a többi blokkba vagy csomagba.
  • Tartalmaz deklarációs részt (opcionális), végrehajtási részt, kivételkezelési részt (nem kötelező).
  • Az értékeket át lehet adni Oracle eljárást, vagy lekérjük az eljárásból a paramétereken keresztül.
  • Ezeket a paramétereket szerepeltetni kell a hívó utasításban.
  • Az SQL-ben lévő eljárás rendelkezhet RETURN utasítással, amely visszaadja a vezérlőt a hívó blokkhoz, de nem ad vissza semmilyen értéket a RETURN utasításon keresztül.
  • Az eljárások nem hívhatók meg közvetlenül a SELECT utasításokból. Meghívhatók egy másik blokkból vagy EXEC kulcsszón keresztül.

Szintaxis

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • A CREATE PROCEDURE utasítja a fordítót, hogy hozzon létre új eljárást Oracle. Az 'OR REPLACE' kulcsszó arra utasítja a fordítást, hogy a meglévő eljárást (ha van) cserélje le a jelenlegire.
  • Az eljárás nevének egyedinek kell lennie.
  • Az „IS” kulcsszó kerül felhasználásra, amikor a tárolt eljárás bekerül Oracle be van ágyazva néhány más blokkba. Ha az eljárás önálló, akkor a rendszer az „AS”-t használja. A kódolási szabványon kívül mindkettőnek ugyanaz a jelentése.

1. példa: Eljárás létrehozása és meghívása EXEC segítségével

Ebben a példában létrehozunk egy Oracle eljárás, amely bemenetként veszi a nevet, kimenetként pedig az üdvözlő üzenetet nyomtatja ki. Az EXEC parancsot fogjuk használni az eljárás meghívásához.

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

Kód magyarázata:

  • 1. kódsor: Az eljárás létrehozása 'welcome_msg' névvel és egy 'IN' típusú 'p_name' paraméterrel.
  • 4. kódsor: Az üdvözlő üzenet nyomtatása a bemeneti név összefűzésével.
  • Az eljárás összeállítása sikeresen megtörtént.
  • 7. kódsor: Az eljárás meghívása EXEC paranccsal a 'Guru99' paraméterrel. Az eljárás végrehajtásra kerül, és az üzenetet „Üdvözöljük Guru99”-ként nyomtatják ki.

KAPCSOLÓDÓ CIKKEK

Mi az a funkció?

A Functions egy önálló PL/SQL alprogram. A PL/SQL eljáráshoz hasonlóan a függvényeknek is egyedi neve van, amellyel hivatkozni lehet rájuk. Ezek PL/SQL adatbázis-objektumként vannak tárolva. Az alábbiakban bemutatjuk a funkciók néhány jellemzőjét.

  • A függvények egy önálló blokk, amelyet elsősorban számítási célokra használnak.
  • A függvény a RETURN kulcsszót használja az érték visszaadásához, és ennek adattípusa a létrehozáskor van meghatározva.
  • A függvénynek vagy értéket kell visszaadnia, vagy meg kell emelnie a kivételt, azaz a return kötelező a függvényekben.
  • A DML utasításokat nem tartalmazó függvény közvetlenül hívható a SELECT lekérdezésben, míg a DML művelettel rendelkező függvény csak más PL/SQL blokkokból hívható meg.
  • Lehetnek benne beágyazott blokkok, vagy definiálható és beágyazható a többi blokkba vagy csomagba.
  • Tartalmaz deklarációs részt (opcionális), végrehajtási részt, kivételkezelési részt (nem kötelező).
  • Az értékek átadhatók a függvénynek, vagy lekérhetők az eljárásból a paramétereken keresztül.
  • Ezeket a paramétereket szerepeltetni kell a hívó utasításban.
  • A PLSQL függvény az OUT paramétereken keresztül is visszaadhatja az értéket a RETURN használatával.
  • Mivel mindig az értéket adja vissza, a hívó utasításban mindig kíséri a hozzárendelési operátort a változók feltöltéséhez.

Funkciók PL/SQL-ben

Szintaxis

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • A CREATE FUNCTION egy új függvény létrehozására utasítja a fordítót. Az 'OR REPLACE' kulcsszó arra utasítja a fordítót, hogy a meglévő függvényt (ha van) cserélje le az aktuálisra.
  • A függvény nevének egyedinek kell lennie.
  • A RETURN adattípust meg kell említeni.
  • Az 'IS' kulcsszó kerül felhasználásra, ha az eljárás más blokkba van beágyazva. Ha az eljárás önálló, akkor a rendszer az „AS”-t használja. A kódolási szabványon kívül mindkettőnek ugyanaz a jelentése.

1. példa: Függvény létrehozása és meghívása Anonymous Block segítségével

Ebben a programban egy olyan függvényt fogunk létrehozni, amely bemenetként veszi a nevet, és kimenetként az üdvözlő üzenetet adja vissza. A függvény meghívásához anonim blokkot és kiválasztási utasítást fogunk használni.

Funkciók PL/SQL-ben

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;

Kód magyarázata:

  • 1. kódsor: Létrehozása a Oracle függvény 'welcome_msg_func' névvel és egy 'IN' típusú 'p_name' paraméterrel.
  • 2. kódsor: a visszatérési típus VARCHAR2 deklarálása
  • 5. kódsor: Az "Üdvözöljük" összefűzött érték és a paraméterérték visszaadása.
  • 8. kódsor: Névtelen blokk a fenti függvény meghívásához.
  • 9. kódsor: A változó deklarálása a függvény visszatérési adattípusával megegyező adattípussal.
  • 11. kódsor: A függvény meghívása és a visszatérési érték feltöltése az 'lv_msg' változóba.
  • 12. kódsor: A változó értékének kinyomtatása. A kimenet, amit itt kapsz, a „Welcome Guru99”
  • 14. kódsor: Ugyanannak a függvénynek a meghívása a SELECT utasítással. A visszatérési érték közvetlenül a szabványos kimenetre kerül.

Hasonlóságok az eljárás és a funkció között

  • Mindkettő meghívható más PL/SQL blokkokból.
  • Ha az alprogramban felvetett kivételt az alprogram nem kezeli kivétel kezelése szakaszban, akkor a hívó blokkba fog terjedni.
  • Mindkettőnek tetszőleges számú paramétere lehet.
  • Mindkettőt adatbázis-objektumként kezeli a PL/SQL.

Eljárás Vs. Funkció: Főbb különbségek

Eljárás Funkció
Főleg bizonyos folyamatok végrehajtására szolgál Főleg számítások elvégzésére szolgál
A SELECT utasítás nem hívható meg Az olyan függvény, amely nem tartalmaz DML utasításokat, meghívható a SELECT utasításban
Használja az OUT paramétert az érték visszaadásához A RETURN gombbal adja vissza az értéket
Az érték visszaadása nem kötelező Az érték visszaadása kötelező
A RETURN egyszerűen kilép a vezérlésből az alprogramból. A RETURN kilép az alprogramból, és visszaadja az értéket
A visszatérési adattípus a létrehozáskor nem lesz megadva A visszaküldési adattípus kötelező a létrehozáskor

Beépített funkciók PL/SQL-ben

PL / SQL különböző beépített függvényeket tartalmaz a karakterláncokkal és a dátum adattípusokkal való munkához. Itt megtekintjük a gyakran használt funkciókat és azok használatát.

Konverziós funkciók

Ezeket a beépített függvényeket egy adattípus másik adattípussá alakítására használják.

Funkció neve Használat Példa
TO_CHAR A másik adattípust karakteres adattípussá alakítja TO_CHAR(123);
TO_DATE ( karakterlánc, formátum ) A megadott karakterláncot dátummá alakítja. A karakterláncnak meg kell egyeznie a formátummal.

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

teljesítmény: 1 / 15 / 2015
TO_NUMBER (szöveg, formátum)

A szöveget az adott formátumú számtípussá alakítja.

Informat '9' a számjegyek számát jelöli

 Válassza ki a TO_NUMBER('1234','9999') számot a dual közül;

teljesítmény: 1234

Válassza ki a TO_NUMBER('1,234.45','9,999.99') számot a dual közül;

teljesítmény: 1234

Húrfüggvények

Ezek azok a függvények, amelyeket a karakter adattípusnál használunk.

Funkció neve Használat Példa
INSTR(szöveg, karakterlánc, kezdet, előfordulás) Megadja az adott szöveg pozícióját az adott karakterláncban.

  • szöveg – Fő karakterlánc
  • karakterlánc – keresendő szöveg
  • start – a keresés kezdőpontja (opcionális)
  • összhangban – a keresett karakterlánc előfordulása (nem kötelező)
 Válassza ki az INSTR('REPÜLŐ','E',2,1) elemet a dual közül

teljesítmény: 2

Válassza ki az INSTR('REPÜLŐ','E',2,2) elemet a dual közül

teljesítmény: 9 (2nd E) előfordulása
SUBSTR (szöveg, kezdet, hossz) Megadja a fő karakterlánc részkarakterlánc értékét.

  • szöveg – fő karakterlánc
  • start – kiinduló helyzet
  • hossz – a karakterlánc alatti hossz
 válassza ki a substr('aeroplane',1,7)-t a dual közül

teljesítmény: aeropla
FELSŐ ( szöveg ) A megadott szöveg nagybetűjét adja vissza Válassza a felső('guru99')-t a dual közül;

teljesítmény: GURU99
LOWER ( szöveg ) A megadott szöveg kisbetűjét adja vissza Válasszon alacsonyabbat ('AerOpLane') a kettős közül;

teljesítmény: repülőgép
INITCAP (szöveg) A megadott szöveget a kezdőbetűvel nagybetűvel adja vissza. Válassz ('guru99') a dual közül

teljesítmény: Guru99

Válassza ki a ('my story') a kettős közül

teljesítmény: A történetem
LENGTH ( szöveg ) A megadott karakterlánc hosszát adja vissza Válassza a LENGTH ('guru99') lehetőséget a dual közül;

teljesítmény: 6
LPAD (szöveg, hossz, pad_karakter) A bal oldalon lévő karakterláncot a megadott hosszúságra (teljes karakterláncra) tölti be az adott karakterrel Válassza ki az LPAD('guru99', 10, '$') értéket a dual közül;

teljesítmény: $$$$guru99
RPAD (szöveg, hossz, pad_char) A jobb oldalon lévő karakterláncot a megadott hosszúságra (teljes karakterláncra) kiegészíti az adott karakterrel Válassza ki az RPAD('guru99',10,'-') lehetőséget a dual közül

teljesítmény: guru99--
LTRIM (szöveg) Levágja a bevezető fehér szóközt a szövegből Válassza ki az LTRIM(' Guru99')-t a dualból;

teljesítmény: Guru99
RTRIM (szöveg) Levágja a szöveg végén lévő fehér szóközt Válassza ki az RTRIM('Guru99') elemet a dual közül;

teljesítmény; Guru99

Dátum funkciók

Ezek olyan függvények, amelyeket a dátumokkal való manipulációra használnak.

Funkció neve Használat Példa
ADD_MONTHS (dátum, hónapok száma) Hozzáadja a dátumhoz a megadott hónapokat ADD_MONTH('2015-01-01',5);

teljesítmény: 05 / 01 / 2015
SYSDATE A szerver aktuális dátumát és idejét adja vissza Válassza a SYSDATE lehetőséget a kettős közül;

teljesítmény: 10/4/2015 2:11:43
TÖRZS A dátumváltozó kerekítése a lehetséges alacsonyabb értékre válasszuk a sysdate, TRUNC(sysdate) dual közül;

teljesítmény: 10. 4:2015:2 12.
FORDULÓ Felkerekíti a dátumot a legközelebbi határértékre, akár magasabbra, akár alacsonyabbra Válassza ki a rendszerdátumot, a ROUND(sysdate)-t a dualból

teljesítmény: 10. 4:2015:2 14.
MONTHS_BETWEEN Két dátum közötti hónapok számát adja vissza Válassza ki a MONTHS_BETWEEN (sysdate+60, sysdate) a dual közül

teljesítmény: 2

Összegzésként

Ebben a fejezetben a következőket tanultuk meg.

  • Az eljárás létrehozása és a hívás különböző módjai
  • A függvény létrehozása és a meghívásának különböző módjai
  • Hasonlóságok és különbségek az eljárás és a funkció között
  • Paraméterek és RETURN általános terminológiák a PL/SQL alprogramokban
  • Gyakori beépített funkciók Oracle PL / SQL

Talán tetszene: