Oracle PL / SQLi salvestatud protseduur & Funktsioonid näidetega

Lang L: none (table-of-contents):

Anonim

Selles õpetuses näete üksikasjalikku kirjeldust nimega plokkide (protseduuride ja funktsioonide) loomise ja käivitamise kohta.

Protseduurid ja funktsioonid on alamprogrammid, mida saab andmebaasi luua ja andmebaasi objektidena salvestada. Neid saab helistada või viidata ka teiste plokkide sisse.

Peale selle käsitleme nende kahe alamprogrammi peamisi erinevusi. Arutame ka Oracle'i sisseehitatud funktsioonide üle.

Selles Oracle'i salvestatud protseduuri õpetuses saate

  • Terminid PL / SQL alamprogrammides
  • Mis on protseduur PL / SQL-is?
  • Mis on funktsioon?
  • Menetluse ja funktsiooni sarnasused
  • Protseduur vs. Funktsioon: peamised erinevused
  • Sisseehitatud funktsioonid PL / SQL-is

Terminid PL / SQL alamprogrammides

Enne PL / SQL alamprogrammide tundmaõppimist arutame erinevaid terminoloogiaid, mis on nende alamprogrammide osa. Allpool on toodud terminoloogiad, mida arutame.

Parameeter:

Parameeter on muutuja või kohatäide mis tahes kehtivast PL / SQL-tüüpi andmetüübist, mille kaudu PL / SQL-alamprogramm vahetab väärtusi põhikoodiga. See parameeter võimaldab sisendit alamprogrammidele ja nendest alamprogrammidest väljavõtmist.

  • Need parameetrid tuleks loomise ajal määratleda koos alamprogrammidega.
  • Need parameetrid on lisatud nende alamprogrammide kutsuvale avaldusele, et väärtused interakteeruksid alamprogrammidega.
  • 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.

Parameetrid klassifitseeritakse vastavalt nende otstarbele

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

IN parameeter:

  • Seda parameetrit kasutatakse alamprogrammidele sisendi andmiseks.
  • See on kirjutuskaitstud muutuja alamprogrammides. Nende väärtusi ei saa alamprogrammis muuta.
  • Helistamislauses võivad need parameetrid olla muutuja või sõnasõnaline väärtus või avaldis, 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 kirjutus-kirjutusmuutuja. Nende väärtusi saab alamprogrammides muuta.
  • Helistamislauses peaksid need parameetrid olema alati muutuja, mis hoiab praeguste alamprogrammide väärtust.

Parameeter IN OUT

  • Seda parameetrit kasutatakse nii sisendi andmiseks kui ka alamprogrammide väljundi saamiseks.
  • See on alamprogrammide sees olev kirjutus-kirjutusmuutuja. Nende väärtusi saab alamprogrammides muuta.
  • Helistamislauses peaksid need parameetrid olema alati muutuja, mis hoiab alamprogrammide väärtust.

Need parameetrite tüübid tuleks mainida alamprogrammide loomise ajal.

TAGASI

RETURN on märksõna, mis käsib kompilaatoril lülitada juhtimine alamprogrammist väljakutsele. Alamprogrammis tähendab RETURN lihtsalt seda, et juhtimine peab alamprogrammist väljuma. Kui kontroller leiab alamprogrammist märksõna RETURN, jäetakse selle järel kood vahele.

Tavaliselt kutsub vanem- või põhiplokk alamprogramme ja seejärel nihkub juhtnupp nendelt vanemplokkidelt kutsutud alamprogrammidele. Alamprogrammis RETURN tagastatakse juhtimine tagasi oma vanemplokki. Funktsioonide korral tagastab lause RETURN ka väärtuse. Selle väärtuse andmetüüp mainitakse alati funktsiooni deklareerimise ajal. Andmetüüp võib olla mis tahes kehtiv PL / SQL andmetüüp.

Mis on protseduur PL / SQL-is?

Protseduur on PL / SQL on alamprogramm üksus, mis koosneb rühm PL / SQL avaldused, mida võib nimetada nimepidi. Igal PL / SQL-i protseduuril on oma ainulaadne nimi, mille abil sellele saab viidata ja sellele helistada. See Oracle'i andmebaasi alamprogrammi üksus on salvestatud andmebaasi objektina.

Märkus: Alamprogramm pole muud kui protseduur ja see tuleb vastavalt nõudele käsitsi luua. Kui need on loodud, salvestatakse need andmebaasi objektidena.

Allpool on esitatud protseduuri alamprogrammiüksuse omadused PL / SQL-is:

  • Protseduurid on eraldiseisvad programmi plokid, mida saab andmebaasi salvestada.
  • Nendele PLSQL-i protseduuridele saab helistada, viidates nende nimele, et täita PL / SQL-käske.
  • 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 deklaratsiooni osa (valikuline), täitmisosa, erandite käsitlemise osa (valikuline).
  • Väärtusi saab Oracle'i protseduuri edastada või protseduurist parameetrite kaudu tuua.
  • Need parameetrid tuleks lisada väljakutsesse.
  • SQL-i protseduuril võib olla käsk RETURN juhtimise tagastamiseks kutseplokile, kuid see ei saa RETURN-lause kaudu ühtegi väärtust tagastada.
  • Protseduure ei saa otse SELECT-lausetest kutsuda. Neile saab helistada mõnest teisest plokist või EXECi märksõna kaudu.

Süntaks:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE käsib kompilaatoril luua Oracle'is uus protseduur. Märksõna „VÕI ASENDA” käsib kompileerimisel asendada olemasolev protseduur (kui see on olemas) praegusega.
  • Protseduuri nimi peaks olema kordumatu.
  • Märksõna „IS” kasutatakse siis, kui Oracle'i salvestatud protseduur on pesastatud teistesse plokkidesse. Kui protseduur on eraldiseisev, siis 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 Oracle'i protseduuri, mis võtab nime sisendiks ja prindib tervitussõnumi väljundiks. Menetluse kutsumiseks kasutame käsku EXEC.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_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' tüüpi 'IN'.
  • Koodirida 4 : tervitussõnumi printimine sisendnime liitmise teel.
  • Protseduur on koostatud edukalt.
  • Koodirida 7 : protseduuri kutsumine EXEC-käsu abil parameetriga 'Guru99'. Protseduur viiakse läbi ja sõnum prinditakse kujul "Welcome Guru99".

Mis on funktsioon?

Funktsioonid on eraldiseisev PL / SQL alamprogramm. Nagu PL / SQL protseduur, on ka funktsioonidel ainulaadne nimi, mille abil neile saab viidata. Need salvestatakse PL / SQL andmebaasi objektidena. Allpool on toodud mõned funktsioonide omadused.

  • Funktsioonid on eraldiseisev plokk, mida kasutatakse peamiselt arvutuseks.
  • Funktsioon kasutab väärtuse tagastamiseks märksõna RETURN ja selle andmetüüp on määratletud loomise ajal.
  • Funktsioon peaks kas tagastama väärtuse või tõstma erandi, st tagastamine on funktsioonides kohustuslik.
  • Funktsiooni ilma DML-lauseteta saab SELECT-päringus otse kutsuda, samas kui DML-operatsiooniga 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 deklaratsiooni osa (valikuline), täitmisosa, erandite käsitlemise osa (valikuline).
  • Väärtusi saab parameetrite kaudu funktsiooni edastada või protseduurist hankida.
  • Need parameetrid tuleks lisada väljakutsesse.
  • PLSQL-funktsioon võib väärtuse tagastada ka muude OUT-parameetrite kaudu kui RETURN-i kasutamine.
  • Kuna see tagastab alati väärtuse, kaasneb kutsungil alati muutujate täitmiseks määranguoperaator.

Süntaks

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION käsib kompilaatoril luua uus funktsioon. Märksõna "VÕI ASENDA" käsib kompilaatoril asendada olemasolev funktsioon (kui see on olemas) praegusega.
  • Funktsiooni nimi peaks olema kordumatu.
  • Tuleks mainida RETURNi andmetüüpi.
  • Märksõna „IS” kasutatakse siis, kui protseduur on sisestatud teistesse plokkidesse. Kui protseduur on eraldiseisev, siis kasutatakse AS-i. Peale selle kodeerimisstandardi on mõlemal sama tähendus.

Näide1: Funktsiooni loomine ja selle kutsumine anonüümse ploki abil

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

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;

Koodi selgitus:

  • Koodirida 1 : Oracle'i funktsiooni loomine nimega 'welcome_msg_func' ja ühe parameetriga 'p_name' tüüpi 'IN'.
  • Koodirida 2 : deklareeritakse tagastustüübiks VARCHAR2
  • Koodirida 5 : tagastatakse liidetud väärtus „Welcome“ ja parameetri väärtus.
  • Koodirida 8 : anonüümne plokk ülaltoodud funktsiooni kutsumiseks.
  • Koodirida 9 : Muutuja deklareerimine andmetüübiga sama, mis funktsiooni tagastatava andmetüüp.
  • Koodirida 11 : funktsiooni kutsumine ja muutuja 'lv_msg' tagasiväärtuse täitmine.
  • Koodirida 12 : muutuja väärtuse printimine. Siit saate väljundi "Welcome Guru99"
  • Koodirida 14 : Sama funktsiooni kutsumine SELECT-lause kaudu. Tagasiväärtus suunatakse otse standardväljundisse.

Menetluse ja funktsiooni sarnasused

  • Mõlemat saab kutsuda teistest PL / SQL-plokkidest.
  • Kui alamprogrammis tõstatatud erandit alamprogrammi erandite käitlemise osas ei käsitleta, levib see kutseplokki.
  • Mõlemal võib olla nii palju parameetreid kui vaja.
  • Mõlemat käsitletakse PL / SQL-is andmebaasi objektidena.

Protseduur vs. Funktsioon: peamised erinevused

Menetlus Funktsioon
  • Kasutatakse peamiselt teatud protsessi käivitamiseks
  • Kasutatakse peamiselt mõningate arvutuste tegemiseks
  • SELECT-lauses ei saa helistada
  • Funktsiooni, mis ei sisalda DML-lauseid, saab kutsuda SELECT-lauses
  • Väärtuse tagastamiseks kasutage parameetrit OUT
  • Väärtuse tagastamiseks kasutage nuppu RETURN
  • Väärtuse tagastamine pole kohustuslik
  • Väärtus on kohustuslik tagastada
  • RETURN väljub juhtimisest lihtsalt alamprogrammist.
  • RETURN väljub juhtimisest alamprogrammist 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 mitmesuguseid sisseehitatud funktsioone stringide ja kuupäeva andmetüübiga töötamiseks. Siin näeme tavaliselt kasutatavaid funktsioone ja nende kasutamist.

Teisendamisfunktsioonid

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

Funktsiooni nimi Kasutamine Näide
TO_CHAR Teisendab teise andmetüübi tähemärgi andmetüübiks TO_CHAR (123);
TO_DATE (string, formaat) Teisendab antud stringi kuupäevaks. String peaks sobima vorminguga. TO_DATE ('2015-JAAN-15', 'AAAA-ESP-PP'); Väljund: 15.01.2015
TO_NUMBER (tekst, formaat) Teisendab teksti antud vormingu numbritüübiks. Informaat '9' tähistab numbrite arvu Valige topelt TO_NUMBER ('1234', '9999'); Väljund: 1234 Valige kahekordsest TO_NUMBER ('1 234,45', '9 999,99'); Väljund: 1234

Stringi funktsioonid

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

Funktsiooni nimi Kasutamine Näide
INSTR (tekst, string, algus, esinemissagedus) Annab konkreetse teksti positsiooni antud stringis.
  • tekst - põhistring
  • string - tekst, mida tuleb otsida
  • algus - otsingu algusasend (valikuline)
  • vastavus - otsitud stringi esinemine (valikuline)
 Select INSTR ( 'lennuki, "E", 2,1) kahekordne Output : 2 Valige INSTR (' lennuki, "E", 2,2) kahekordne Output: 9 (2 nd esinemise E)
SUBSTR (tekst, algus, pikkus) Annab põhistringi alamstringi väärtuse.
  • tekst - põhistring
  • algus - lähtepositsioon
  • pikkus - alamstringitav pikkus
 valige alam ('lennuk', 1,7) kahest väljundist : aeropla
Ülemine (tekst) Tagastab esitatud teksti suurtähe Valige topelt ('guru99'); Väljund : GURU99
LOWER (tekst) Tagastab esitatud teksti väiketähe Valige madalam ('lennuk') kahest; Väljund : lennuk
INITCAP (tekst) Tagastab antud teksti suure algustähega. Valige ( "guru99") kahekordne väljund : Guru99 Valige ( "minu lugu") kahekordne väljund : Minu lugu
LENGTH (tekst) Tagastab antud stringi pikkuse Valige kahesugusest LENGTH ('guru99'); Väljund : 6
LPAD (tekst, pikkus, pad_char) Padjad vasakul küljel antud pikkusega (kogu string) stringi antud tähemärgiga Valige kahekordsest LPAD ('guru99', 10, '$'); Väljund : $$$$ guru99
RPAD (tekst, pikkus, pad_char) Pehmendab stringi antud tähemärgiga antud pikkuse (kogu stringi) paremal küljel Valige RPAD ( "guru99", 10 "-") kahekordne väljund : guru99 ----
LTRIM (tekst) Kärbib tekstist juhtiva tühiku Valige kahesugusest LTRIM ('Guru99'); Väljund : Guru99
RTRIM (tekst) Kärbib tekstist tühja tühiku Valige RTRIM ('Guru99') kahest; Väljund ; Guru99

Kuupäeva funktsioonid

Need on funktsioonid, mida kasutatakse kuupäevadega manipuleerimiseks.

Funktsiooni nimi Kasutamine 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 topelt SYSDATE; Väljund : 4.10.2015 14:11:43
TRUNC Kuupäevamuutuja ümardamine madalaima võimaliku väärtuseni vali sysdate, TRUNC (sysdate) kahest; Väljund : 4.10.2015 14:12:39 4.10.2015
ÜMBER Ümardab kuupäeva lähima piirini kas suuremaks või madalamaks Valige SYSDATE ROUND (SYSDATE) kahekordne väljund : 2015/10/04 02:14:34 2015/10/05
MONTHS_BEWEEN Tagastab kuude arvu kahe kuupäeva vahel Valige kahest väljundist MONTHS_BETWEEN (sysdate + 60, sysdate) : 2

Kokkuvõte

Selles peatükis oleme õppinud järgmist.

  • Kuidas luua protseduur ja erinevad viisid selle kutsumiseks
  • Kuidas luua funktsioon ja erinevad viisid selle kutsumiseks
  • Protseduuri ja funktsiooni sarnasused ja erinevused
  • Parameetrid ja RETURN levinud terminoloogiad PL / SQL alamprogrammides
  • Levinumad sisseehitatud funktsioonid Oracle PL / SQL-is