Oracle PL/SQL-i salvestatud protseduur ja funktsioonid koos näidetega

Autor: Fiona Brown Fiona Brown
Hours Ajakohastatud

Selles õpetuses näete üksikasjalikku kirjeldust selle kohta, kuidas nimega plokke luua ja käivitada (protseduurid ja funktsioonid).

Protseduurid ja funktsioonid on alamprogrammid, mida saab luua ja andmebaasi andmebaasiobjektidena salvestada. Neid saab kutsuda või viidata ka teistes plokkides.

Peale selle käsitleme nende kahe alamprogrammi peamisi erinevusi. Samuti kavatseme arutada Oracle sisseehitatud funktsioonid.

Terminoloogiad PL/SQL-i alamprogrammides

Enne PL/SQL-i alamprogrammide tundmaõppimist käsitleme erinevaid terminoloogiaid, mis nende alamprogrammide osaks on. Allpool on terminoloogiad, mida me arutame.

Parameeter

Parameeter on mis tahes kehtiva muutuja või kohahoidja PL/SQL andmetüüp mille kaudu PL/SQL alamprogramm vahetab väärtusi põhikoodiga. See parameeter võimaldab anda sisendit alamprogrammidele ja nendest alamprogrammidest väljavõtteid teha.

  • Need parameetrid tuleks määratleda koos alamprogrammidega loomise ajal.
  • Need parameetrid sisalduvad nende alamprogrammide kutsumislauses, et väärtusi alamprogrammidega suhelda.
  • Alamprogrammi parameetri andmetüüp ja kutsuv lause peaksid olema samad.
  • Andmetüübi suurust ei tohiks parameetri deklareerimise ajal mainida, kuna selle tüübi suurus on dünaamiline.

Sõltuvalt nende eesmärgist liigitatakse parameetrid järgmiselt

  1. IN Parameeter
  2. OUT parameeter
  3. IN OUT Parameeter

IN Parameeter

  • Seda parameetrit kasutatakse alamprogrammidele sisendi andmiseks.
  • See on alamprogrammide sees kirjutuskaitstud muutuja. Nende väärtusi ei saa alamprogrammi sees muuta.
  • Kutsuvas lauses võivad need parameetrid olla muutujad või literaalväärtused või avaldised, näiteks võib see olla aritmeetiline avaldis nagu '5*8' või 'a/b', kus 'a' ja 'b' on muutujad .
  • Vaikimisi on parameetrid IN tüüpi.

OUT parameeter

  • Seda parameetrit kasutatakse alamprogrammide väljundi saamiseks.
  • See on alamprogrammide sees olev lugemis-kirjutamise muutuja. Nende väärtusi saab muuta alamprogrammide sees.
  • Kutsuvas lauses peaksid need parameetrid alati olema muutujad, mis hoiavad praeguste alamprogrammide väärtust.

IN OUT Parameeter

  • Seda parameetrit kasutatakse nii sisendi andmiseks kui ka väljundi saamiseks alamprogrammidest.
  • See on alamprogrammide sees olev lugemis-kirjutamise muutuja. Nende väärtusi saab muuta alamprogrammide sees.
  • Kutsuvas lauses peaksid need parameetrid alati olema muutujad, mis hoiavad alamprogrammide väärtust.

Need parameetritüübid tuleks mainida alamprogrammide loomise ajal.

RETURN

RETURN on märksõna, mis annab kompilaatorile korralduse lülitada juhtimine alamprogrammilt väljakutsuvale lausele. Alamprogrammis RETURN tähendab lihtsalt seda, et juhtelement peab alamprogrammist väljuma. Kui kontroller leiab alamprogrammist märksõna RETURN, jäetakse sellele järgnev kood vahele.

Tavaliselt kutsub ülem- või põhiplokk alamprogramme ja seejärel nihkub juhtelement neilt emaplokkidelt kutsutud alamprogrammidele. RETURN alamprogrammis tagastab juhtelemendid tagasi nende emaplokki. Funktsioonide puhul tagastab lause ka väärtuse. Selle väärtuse andmetüüpi mainitakse alati funktsiooni deklareerimise ajal. Andmetüüp võib olla mis tahes kehtiv PL/SQL andmetüüp.

Mis on protseduur PL/SQL-is?

A Menetlus PL/SQL-is on alamprogrammiüksus, mis koosneb PL/SQL-lausete rühmast, mida saab nimetada. Igal PL/SQL-i protseduuril on oma kordumatu nimi, millega saab sellele viidata ja kutsuda. See alamprogrammi üksus Oracle andmebaas salvestatakse andmebaasiobjektina.

Märge: Alamprogramm pole midagi muud kui protseduur ja see tuleb vastavalt vajadusele käsitsi luua. Pärast loomist salvestatakse need andmebaasiobjektidena.

Allpool on toodud protseduuride alamprogrammi üksuse omadused PL/SQL-is:

  • Protseduurid on programmi eraldiseisvad plokid, mida saab salvestada andmebaas.
  • Neid PLSQL-protseduure saab kutsuda nende nimele viidates PL/SQL-lausete täitmiseks.
  • Seda kasutatakse peamiselt protsessi käivitamiseks PL/SQL-is.
  • Sellel võivad olla pesastatud plokid või see võib olla määratletud ja pesastatud teiste plokkide või pakettide sisse.
  • See sisaldab deklaratsiooniosa (valikuline), täitmise osa, erandi käsitlemise osa (valikuline).
  • Väärtused saab üle kanda Oracle protseduuri või tuuakse protseduurist parameetrite kaudu.
  • Need parameetrid tuleks lisada kutsuvasse lausesse.
  • SQL-i protseduuril võib olla RETURN-lause, mis tagastab juhtelemendi kutsuvasse plokki, kuid see ei saa tagastada ühtegi väärtust lause RETURN kaudu.
  • Protseduure ei saa otse SELECT-lausetest välja kutsuda. Neid saab helistada teisest plokist või EXEC-i märksõna kaudu.

Süntaks

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE annab kompilaatorile käsu luua uus protseduur Oracle. Märksõna 'OR REPLACE' käsib kompilaatoril asendada olemasolev protseduur (kui see on olemas) praegusega.
  • Protseduuri nimi peaks olema kordumatu.
  • Märksõna „IS” kasutatakse siis, kui protseduur on salvestatud Oracle on pesastatud mõnesse muusse plokki. Kui protseduur on eraldiseisev, kasutatakse AS-i. Peale selle kodeerimisstandardi on mõlemal sama tähendus.

Näide1: Protseduuri loomine ja selle kutsumine EXEC-i abil

Selles näites loome ühe Oracle protseduur, mis võtab sisendiks nime ja prindib väljundina tervitussõnumi. Protseduuri kutsumiseks kasutame käsku 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’);

Koodi selgitus:

  • Koodirida 1: Protseduuri loomine nimega 'welcome_msg' ja ühe parameetriga 'p_name' 'IN' tüüpi.
  • Koodirida 4: tervitussõnumi printimine sisendi nime ühendamise teel.
  • Protseduur on edukalt koostatud.
  • Koodirida 7: Protseduuri kutsumine EXEC-käsuga parameetriga 'Guru99'. Protseduur viiakse läbi ja teade prinditakse välja kui "Tere tulemast Guru99".

SEOTUD ARTIKLID

Mis on funktsioon?

Functions on iseseisev PL/SQL-i alamprogramm. Sarnaselt PL/SQL-protseduurile on funktsioonidel ainulaadne nimi, millega saab sellele viidata. Need salvestatakse PL/SQL andmebaasiobjektidena. Allpool on toodud mõned funktsioonide omadused.

  • Funktsioonid on eraldiseisev plokk, mida kasutatakse peamiselt arvutamise eesmärgil.
  • Funktsioon kasutab väärtuse tagastamiseks märksõna RETURN ja selle andmetüüp määratakse loomise ajal.
  • Funktsioon peaks kas tagastama väärtuse või tõstma erandit, st tagastamine on funktsioonides kohustuslik.
  • DML-lauseteta funktsiooni saab otse välja kutsuda SELECT-päringus, samas kui DML-toiminguga funktsiooni saab kutsuda ainult teistest PL/SQL-plokkidest.
  • Sellel võivad olla pesastatud plokid või see võib olla määratletud ja pesastatud teiste plokkide või pakettide sisse.
  • See sisaldab deklaratsiooniosa (valikuline), täitmise osa, erandi käsitlemise osa (valikuline).
  • Väärtused saab edastada funktsiooni või tuua protseduurist parameetrite kaudu.
  • Need parameetrid tuleks lisada kutsuvasse lausesse.
  • PLSQL-funktsioon võib väärtuse tagastada ka muude parameetrite OUT kaudu, välja arvatud RETURN.
  • Kuna see tagastab alati väärtuse, on kutsuvas lauses muutujate sisestamiseks alati kaasas määramisoperaator.

Funktsioonid PL/SQL-is

Süntaks

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 juhendab kompilaatorit looma uue funktsiooni. Märksõna 'OR REPLACE' annab kompilaatorile käsu asendada olemasolev funktsioon (kui see on olemas) praegusega.
  • Funktsiooni nimi peaks olema kordumatu.
  • RETURN andmetüüp tuleks mainida.
  • Märksõna 'IS' kasutatakse siis, kui protseduur on pesastatud mõnesse muusse plokki. Kui protseduur on eraldiseisev, kasutatakse AS-i. Peale selle kodeerimisstandardi on mõlemal sama tähendus.

Näide1: Funktsiooni loomine ja sellele helistamine anonüümse ploki abil

Selles programmis loome funktsiooni, mis võtab sisendiks nime ja tagastab väljundina tervitussõnumi. Funktsiooni kutsumiseks kasutame anonüümset plokki ja valime avalduse.

Funktsioonid PL/SQL-is

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;

Koodi selgitus:

  • Koodirida 1: loomine Oracle funktsioon nimega "welcome_msg_func" ja ühe parameetriga "p_name" tüüpi "IN".
  • Koodirida 2: deklareerib tagastustüübiks VARCHAR2
  • Koodirida 5: tagastab ühendatud väärtuse 'Tere tulemast' ja parameetri väärtuse.
  • Koodirida 8: Anonüümne plokk ülaltoodud funktsiooni kutsumiseks.
  • Koodirida 9: muutuja deklareerimine, mille andmetüüp on sama, mis funktsiooni tagastatava andmetüübiga.
  • Koodirida 11: funktsiooni kutsumine ja tagastatava väärtuse sisestamine muutujale 'lv_msg'.
  • Koodirida 12: muutuja väärtuse printimine. Väljund, mille siit saate, on "Tere tulemast Guru99"
  • Koodirida 14: sama funktsiooni kutsumine SELECT-lause kaudu. Tagastusväärtus suunatakse otse standardväljundisse.

Sarnasused protseduuri ja funktsiooni vahel

  • Mõlemat saab kutsuda teistest PL/SQL-i plokkidest.
  • Kui alamprogrammis tõstatatud erandit alamprogrammis ei käsitleta erandite käsitlemine sektsiooni, siis levib see kutsuvasse plokki.
  • Mõlemal võib olla nii palju parameetreid kui vaja.
  • Mõlemat käsitletakse PL/SQL-is andmebaasiobjektidena.

Protseduur vs. Funktsioon: peamised erinevused

Menetlus funktsioon
Kasutatakse peamiselt teatud protsesside läbiviimiseks Kasutatakse peamiselt teatud arvutuste tegemiseks
SELECT-lauses ei saa helistada Funktsiooni, mis ei sisalda DML-lauseid, saab kutsuda SELECT-lauses
Kasutage väärtuse tagastamiseks parameetrit OUT Kasutage väärtuse tagastamiseks nuppu RETURN
Väärtuse tagastamine ei ole kohustuslik Väärtuse tagastamine on kohustuslik
RETURN väljub lihtsalt alamprogrammist. RETURN väljub alamprogrammi juhtelemendist ja tagastab ka väärtuse
Tagastamise andmetüüpi loomise ajal ei täpsustata Tagastamise andmetüüp on loomise ajal kohustuslik

Sisseehitatud funktsioonid PL/SQL-is

PL / SQL sisaldab erinevaid sisseehitatud funktsioone stringide ja kuupäeva andmetüübiga töötamiseks. Siin näeme sagedamini kasutatavaid funktsioone ja nende kasutamist.

Teisendusfunktsioonid

Neid sisseehitatud funktsioone kasutatakse ühe andmetüübi teisendamiseks teiseks andmetüübiks.

Funktsiooni nimi Kasutus Näide
TO_CHAR Teisendab teise andmetüübi märgi andmetüübiks TO_CHAR(123);
TO_DATE ( string, vorming ) Teisendab antud stringi kuupäevaks. String peab vastama vormingule.

TO_DATE('2015-JAN-15', 'AAAA-ES-PP');

Väljund: 1 / 15 / 2015
TO_NUMBER (tekst, vorming)

Teisendab teksti antud vormingu numbritüübiks.

Informat '9' tähistab numbrite arvu

 Valige TO_NUMBER('1234','9999') kahest;

Väljund: 1234

Valige TO_NUMBER('1,234.45','9,999.99') kahest;

Väljund: 1234

Stringi funktsioonid

Need on funktsioonid, mida kasutatakse märgi andmetüübi puhul.

Funktsiooni nimi Kasutus Näide
INSTR(tekst, string, algus, esinemine) Annab konkreetse teksti asukoha antud stringis.

  • tekst – põhistring
  • string – tekst, mida tuleb otsida
  • algus – otsingu alguspositsioon (valikuline)
  • vastavus – otsitava stringi esinemine (valikuline)
 Valige INSTR('LENNUK','E',2,1) dual

Väljund: 2

Valige INSTR('LENNUK','E',2,2) dual

Väljund: 9 (2nd E esinemine)
SUBSTR (tekst, algus, pikkus) Annab põhistringi alamstringi väärtuse.

  • tekst – põhistring
  • algus – lähtepositsioon
  • pikkus – pikkus alamstringina
 vali substr('lennuk',1,7) dual'ist

Väljund: aeropla
ÜLEMINE ( ​​tekst ) Tagastab esitatud teksti suurtähe Vali top('guru99') dual;

Väljund: GURU99
ALUMINE ( ​​tekst ) Tagastab esitatud teksti väiketähe Valige dual (AerOpLane) madalam;

Väljund: lennuk
INITCAP (tekst) Tagastab etteantud teksti algustähega suurtähtedega. Valige ('guru99') dual

Väljund: Guru99

Valige ("minu lugu") kahest

Väljund: Minu lugu
PIKKUS ( tekst ) Tagastab antud stringi pikkuse Vali PIKKUS ('guru99') kahest;

Väljund: 6
LPAD (tekst, pikkus, pad_char) Täidab vasakpoolses servas stringi etteantud pikkuses (string kokku) antud märgiga Valige LPAD('guru99', 10, '$') dual;

Väljund: $$$$guru99
RPAD (tekst, pikkus, pad_char) Täidab stringi paremal küljel etteantud pikkuses (string kokku) antud märgiga Valige duali hulgast RPAD('guru99',10,'-').

Väljund: guru99--
LTRIM (tekst) Kärbib teksti algusest tühja tühiku Vali LTRIM(' Guru99') dual;

Väljund: Guru99
RTRIM (tekst) Kärbib teksti lõpus oleva tühiku Vali RTRIM('Guru99') dual;

Väljund; Guru99

Kuupäeva funktsioonid

Need on funktsioonid, mida kasutatakse kuupäevadega manipuleerimiseks.

Funktsiooni nimi Kasutus Näide
ADD_MONTHS (kuupäev, kuude arv) Lisab kuupäevale antud kuud ADD_MONTH('2015-01-01',5);

Väljund: 05 / 01 / 2015
SYSDATE Tagastab serveri praeguse kuupäeva ja kellaaja Valige SYSDATE kahest;

Väljund: 10 4:2015:2
TRUNK Kuupäevamuutuja ümardamine madalamale võimalikule väärtusele vali sysdate, TRUNC(sysdate) duaalist;

Väljund: 10 4:2015:2 12
ROUND Ümardab kuupäeva lähima piirini, mis on suurem või väiksem Valige dual-st sysdate, ROUND(sysdate).

Väljund: 10 4:2015:2 14
MONTHS_BETWEEN Tagastab kuude arvu kahe kuupäeva vahel Valige MONTHS_BETWEEN (sysdate+60, sysdate) kahest

Väljund: 2

kokkuvõte

Selles peatükis oleme õppinud järgmist.

  • Protseduuri loomine ja selle kutsumise erinevad viisid
  • Funktsiooni loomine ja selle kutsumise erinevad viisid
  • Sarnasused ja erinevused protseduuri ja funktsiooni vahel
  • Parameetrid ja RETURN levinud terminoloogiad PL/SQL alamprogrammides
  • Levinud sisseehitatud funktsioonid Oracle PL / SQL