„Oracle PL / SQL“ saugoma procedūra & Funkcijos su pavyzdžiais

Turinys:

Anonim

Šioje pamokoje pamatysite išsamų aprašymą, kaip sukurti ir vykdyti įvardytus blokus (procedūras ir funkcijas).

Procedūros ir funkcijos yra paprogramės, kurias galima sukurti ir išsaugoti duomenų bazėje kaip duomenų bazės objektus. Jie taip pat gali būti iškviesti arba nurodyti kituose blokuose.

Be to, aptarsime pagrindinius šių dviejų paprogramių skirtumus. Taip pat aptarsime „Oracle“ integruotas funkcijas.

Šioje „Oracle“ saugomų procedūrų pamokoje sužinosite

  • Terminai PL / SQL paprogramėse
  • Kas yra PL / SQL procedūra?
  • Kas yra funkcija?
  • Procedūros ir funkcijos panašumai
  • Procedūra vs. Funkcija: Pagrindiniai skirtumai
  • Integruotos PL / SQL funkcijos

Terminai PL / SQL paprogramėse

Prieš sužinodami apie PL / SQL paprogrames, aptarsime įvairias terminologijas, kurios yra šių paprogramių dalis. Toliau pateikiamos terminologijos, kurias ketiname aptarti.

Parametras:

Parametras yra kintamasis arba bet kurio galiojančio PL / SQL duomenų tipo, per kurį PL / SQL paprogramė keičiasi reikšmėmis su pagrindiniu kodu, kintamoji vieta. Šis parametras leidžia įvestis į subprogramas ir jas išgauti.

  • Šie parametrai turėtų būti apibrėžti kartu su paprogramėmis jų kūrimo metu.
  • Šie parametrai yra įtraukti į šių paprogramių iškviečiamąjį sakinį, kad sąveikautų reikšmės su paprogramėmis.
  • Parametrų duomenų tipas paprogramėje ir iškvietimo sakinys turėtų būti vienodi.
  • Duomenų tipo dydis neturėtų būti paminėtas deklaruojant parametrą, nes šio tipo dydis yra dinamiškas.

Pagal jų paskirtį parametrai klasifikuojami kaip

  1. IN parametras
  2. OUT parametras
  3. IN OUT parametras

IN parametras:

  • Šis parametras naudojamas įvedant paprogrames.
  • Tai yra tik skaitomas kintamasis paprogramių viduje. Jų reikšmės negalima pakeisti paprogramės viduje.
  • Skambinančiame sakinyje šie parametrai gali būti kintamasis, pažodinė reikšmė arba išraiška, pavyzdžiui, tai gali būti aritmetinė išraiška, pvz., „5 * 8“ arba „a / b“, kur „a“ ir „b“ yra kintamieji .
  • Pagal numatytuosius nustatymus parametrai yra IN tipo.

OUT parametras:

  • Šis parametras naudojamas gaunant iš subprogramų išvestį.
  • Tai yra skaitymo ir rašymo kintamasis paprogramių viduje. Jų reikšmes galima pakeisti paprogramių viduje.
  • Skambinančiame sakinyje šie parametrai visada turėtų būti kintamasis, kad būtų išlaikyta dabartinių paprogramių vertė.

IN OUT parametras:

  • Šis parametras naudojamas tiek įvedant, tiek gaunant išvesties iš paprogramių.
  • Tai yra skaitymo ir rašymo kintamasis paprogramių viduje. Jų reikšmes galima pakeisti paprogramių viduje.
  • Skambinančiame sakinyje šie parametrai visada turėtų būti kintamasis, kad būtų galima laikyti vertę iš paprogramių.

Šie parametrų tipai turėtų būti paminėti kuriant paprogrames.

GRĮŽTI

RETURN yra raktinis žodis, nurodantis kompiliatoriui perjungti valdymą iš paprogramės į iškvietimo sakinį. Pogrupyje RETURN reiškia, kad valdymas turi išeiti iš paprogramės. Valdikliui radus RETURN raktinį žodį, programa po to bus praleista.

Paprastai pagrindinis arba pagrindinis blokas iškvies pogrupius, o tada valdiklis pereis iš tų pirminių blokų į iškviestas pogrupius. RETURN pagal paprogramę grąžins valdiklį atgal į tėvų bloką. Funkcijų atveju RETURN sakinys taip pat grąžina vertę. Šios reikšmės duomenų tipas visada nurodomas deklaruojant funkciją. Duomenų tipas gali būti bet kokio galiojančio PL / SQL duomenų tipo.

Kas yra PL / SQL procedūra?

Tvarka PL / SQL yra paprogramė vienetas, kuris susideda iš PL / SQL grupėje, kuri gali būti vadinamas vardu. Kiekviena PL / SQL procedūra turi savo unikalų pavadinimą, kuriuo ją galima nurodyti ir iškviesti. Šis „Oracle“ duomenų bazės paprogramės vienetas saugomas kaip duomenų bazės objektas.

Pastaba: paprogramė yra ne kas kita, kaip procedūra, ir ją reikia sukurti rankiniu būdu, kaip reikalaujama. Sukūrę jie bus saugomi kaip duomenų bazės objektai.

Toliau pateikiamos „PL“ / „SQL“ programos paprogramės vieneto charakteristikos:

  • Procedūros yra atskiri programos blokai, kuriuos galima saugoti duomenų bazėje.
  • Šias PLSQL procedūras galite iškviesti nurodydami jų vardus, kad įvykdytumėte PL / SQL sakinius.
  • Jis daugiausia naudojamas procesui vykdyti PL / SQL.
  • Jame gali būti įdėti blokai arba jis gali būti apibrėžtas ir įdėtas į kitus blokus ar paketus.
  • Jame yra deklaracijos dalis (neprivaloma), vykdymo dalis, išimčių tvarkymo dalis (neprivaloma).
  • Reikšmės gali būti perkeliamos į „Oracle“ procedūrą arba gaunamos iš procedūros per parametrus.
  • Šie parametrai turėtų būti įtraukti į skambinimo pareiškimą.
  • Procedūra SQL gali turėti RETURN sakinį, kad grąžintų valdiklį į iškvietimo bloką, tačiau ji negali grąžinti jokių reikšmių per RETURN sakinį.
  • Procedūrų negalima iškviesti tiesiogiai iš SELECT sakinių. Juos galima iškviesti iš kito bloko arba naudojant EXEC raktinį žodį.

Sintaksė:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE nurodo kompiliatoriui sukurti naują „Oracle“ procedūrą. Raktinis žodis „ARBA PAKEISTI“ nurodo kompilei pakeisti esamą procedūrą (jei tokia yra) dabartine.
  • Procedūros pavadinimas turėtų būti unikalus.
  • Raktinis žodis „IS“ bus naudojamas, kai „Oracle“ saugoma procedūra bus įdėta į kitus blokus. Jei procedūra yra atskira, bus naudojama „AS“. Išskyrus šį kodavimo standartą, abu turi tą pačią reikšmę.

1 pavyzdys: Procedūros sukūrimas ir iškvietimas naudojant EXEC

Šiame pavyzdyje mes sukursime „Oracle“ procedūrą, kuri pavadinimą pavadins įvestimi ir sveikinimo pranešimą išspausdins kaip išvestį. Norėdami iškviesti procedūrą, naudosime komandą EXEC.

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

Kodo paaiškinimas:

  • 1 kodo eilutė : Procedūros sukūrimas pavadinimu „welcome_msg“ ir vieno parametro „p_name“ tipo „IN“.
  • 4 kodo eilutė : sveikinimo pranešimo spausdinimas susiejant įvesties pavadinimą.
  • Procedūra sudaroma sėkmingai.
  • 7 kodo eilutė : procedūros iškvietimas naudojant komandą EXEC su parametru „Guru99“. Procedūra vykdoma ir pranešimas išspausdinamas kaip „Welcome Guru99“.

Kas yra funkcija?

Funkcijos yra atskira PL / SQL paprogramė. Kaip ir PL / SQL procedūra, funkcijos turi unikalų pavadinimą, kuriuo ją galima nurodyti. Jie saugomi kaip PL / SQL duomenų bazės objektai. Žemiau yra keletas funkcijų charakteristikų.

  • Funkcijos yra atskiras blokas, kuris daugiausia naudojamas skaičiavimams atlikti.
  • Funkcija naudokite RETURN raktinį žodį, kad grąžintumėte vertę, o jo duomenų tipas yra apibrėžtas kuriant.
  • Funkcija turėtų arba grąžinti vertę, arba padidinti išimtį, ty grąža yra privaloma funkcijose.
  • Funkciją be DML sakinių galima tiesiogiai iškviesti naudojant SELECT užklausą, o funkciją su DML operacija galima iškviesti tik iš kitų PL / SQL blokų.
  • Jame gali būti įdėti blokai arba jis gali būti apibrėžtas ir įdėtas į kitus blokus ar paketus.
  • Jame yra deklaracijos dalis (neprivaloma), vykdymo dalis, išimčių tvarkymo dalis (neprivaloma).
  • Vertes galima perkelti į funkciją arba gauti iš procedūros per parametrus.
  • Šie parametrai turėtų būti įtraukti į skambinimo pareiškimą.
  • PLSQL funkcija taip pat gali grąžinti vertę naudodama OUT parametrus, išskyrus RETURN naudojimą.
  • Kadangi jis visada grąžins vertę, iškvietimo sakinyje jis visada pridedamas prie priskyrimo operatoriaus, kad užpildytų kintamuosius.

Sintaksė

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION nurodo kompiliatoriui sukurti naują funkciją. Raktinis žodis „ARBA PAKEISTI“ nurodo kompiliatoriui pakeisti esamą funkciją (jei yra) dabartine.
  • Funkcijos pavadinimas turėtų būti unikalus.
  • Reikėtų paminėti RETURN duomenų tipą.
  • Raktinis žodis „IS“ bus naudojamas, kai procedūra bus įdėta į kitus blokus. Jei procedūra yra atskira, bus naudojama „AS“. Išskyrus šį kodavimo standartą, abu turi tą pačią reikšmę.

1 pavyzdys: Funkcijos sukūrimas ir iškvietimas naudojant „Anonymous Block“

Šioje programoje mes sukursime funkciją, kuri priims pavadinimą kaip įvestį ir grąžins pasveikinimo pranešimą kaip išvestį. Norėdami iškviesti funkciją, naudosime anoniminį bloką ir pasirinksime sakinį.

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;

Kodo paaiškinimas:

  • 1 kodo eilutė : „Oracle“ funkcijos sukūrimas pavadinimu „welcome_msg_func“ ir vienu parametru „p_name“, kurio tipas yra „IN“.
  • 2 kodo eilutė : deklaruoja grąžinimo tipą kaip VARCHAR2
  • 5 kodo eilutė : grąžinama sujungta reikšmė „Sveiki“ ir parametro vertė.
  • 8 kodo eilutė : anoniminis blokavimas norint iškviesti aukščiau nurodytą funkciją.
  • 9 kodo eilutė : kintamojo deklaravimas duomenų tipu toks pat, kaip ir grąžinimo funkcijos duomenų tipas.
  • 11 kodo eilutė : iškviečiama funkcija ir užpildoma kintamojo „lv_msg“ grąžinimo vertė.
  • 12 kodo eilutė : kintamosios vertės spausdinimas. Čia gausite „Welcome Guru99“ išvestį
  • 14 kodo eilutė : Tos pačios funkcijos iškvietimas per SELECT sakinį. Grąžinimo vertė nukreipiama tiesiai į standartinę išvestį.

Procedūros ir funkcijos panašumai

  • Abu juos galima iškviesti iš kitų PL / SQL blokų.
  • Jei subprogramoje iškelta išimtis nėra tvarkoma skyriuje „Programos išimčių tvarkymas“, ji bus išplitusi į iškvietimo bloką.
  • Abu jie gali turėti tiek parametrų, kiek reikia.
  • Abu jie PL / SQL traktuojami kaip duomenų bazės objektai.

Procedūra vs. Funkcija: Pagrindiniai skirtumai

Procedūra Funkcija
  • Naudojamas daugiausia tam tikram procesui vykdyti
  • Naudojamas daugiausia tam tikriems skaičiavimams atlikti
  • Negalima skambinti SELECT sakinyje
  • Funkciją, kurioje nėra DML sakinių, galima iškviesti SELECT sakinyje
  • Norėdami grąžinti vertę, naudokite parametrą OUT
  • Norėdami grąžinti vertę, naudokite RETURN
  • Vertę grąžinti nėra privaloma
  • Privaloma grąžinti vertę
  • RETURN tiesiog pašalins valdymą iš paprogramės.
  • RETURN pašalins valdymą iš paprogramės ir grąžins vertę
  • Grąžinimo duomenų tipas kūrimo metu nebus nurodytas
  • Grąžinimo duomenų tipas yra privalomas kuriant

Integruotos PL / SQL funkcijos

PL / SQL yra įvairių integruotų funkcijų, skirtų dirbti su eilutėmis ir datos duomenų tipu. Čia apžvelgsime dažniausiai naudojamas funkcijas ir jų naudojimą.

Konversijos funkcijos

Šios integruotos funkcijos yra naudojamos konvertuoti vieną duomenų tipą į kitą duomenų tipą.

Funkcijos pavadinimas Naudojimas Pavyzdys
TO_CHAR Konvertuoja kitą duomenų tipą į simbolių duomenų tipą TO_CHAR (123);
TO_DATE (eilutė, formatas) Konvertuoja pateiktą eilutę į datą. Eilutė turi atitikti formatą. TO_DATE ('2015-JAN-15', 'YYYY-MON-DD'); Rezultatas: 2015-01-15
TO_NUMBER (tekstas, formatas) Konvertuoja tekstą į nurodyto formato numerio tipą. Informatas „9“ žymi skaitmenų skaičių Pasirinkite TO_NUMBER ('1234', '9999') iš dvigubo; Rezultatas: 1234 Pasirinkite TO_NUMBER ('1 234,45', '9 999,99') iš dvigubo; Išvestis: 1234

Styginių funkcijos

Tai funkcijos, naudojamos simbolių duomenų tipui.

Funkcijos pavadinimas Naudojimas Pavyzdys
INSTR (tekstas, eilutė, pradžia, įvykis) Pateikia konkretaus teksto poziciją pateiktoje eilutėje.
  • tekstas - pagrindinė eilutė
  • eilutė - tekstas, kurio reikia ieškoti
  • pradžia - pradinė paieškos vieta (nebūtina)
  • Atitikimas - ieškomos eilutės atsiradimas (neprivaloma)
 Pasirinkite INSTR ( "LĖKTUVŲ", "E", 2,1) iš dvigubos išvesties : 2 pasirinkite INSTR ( "LĖKTUVŲ", "E", 2,2) iš dvigubos galingumas: 9 (2 -oji paplitimas gamtoje E)
SUBSTR (tekstas, pradžia, ilgis) Pateikia pagrindinės eilutės substring vertę.
  • tekstas - pagrindinė eilutė
  • startas - pradinė padėtis
  • length - ilgis, kurį reikia subtieduoti
 pasirinkite pagrindą („lėktuvas“, 1,7) iš dvigubos išvesties : aeropla
AUKŠTAS (tekstas) Grąžina pateikto teksto didžiąsias raides Pasirinkite viršutinį ('guru99') iš dvigubo; Išvestis : GURU99
LOWER (tekstas) Grąžina pateikto teksto mažąsias raides Pasirinkite žemesnį („lėktuvas“) iš dvigubo; Rezultatas : lėktuvas
INITCAP (tekstas) Pateikia pateiktą tekstą su didžiosiomis raidėmis. Pasirinkite ('guru99') iš dvigubos išvesties : Guru99 Pasirinkite ('mano istorija') iš dvigubos išvesties : mano istorija
LENGTH (tekstas) Grąžina pateiktos eilutės ilgį Pasirinkite LENGTH ('guru99') iš dvigubo; Išvestis : 6
LPAD (tekstas, ilgis, pad_char) Pamuša eilutę kairėje pusėje nurodytam ilgiui (bendra eilutė) su nurodytu simboliu Pasirinkite LPAD ('guru99', 10, '$') iš dvigubo; Rezultatas : $$$$ guru99
RPAD (tekstas, ilgis, pad_char) Dešinėje pusėje užfiksuoja eilutę nurodytu ilgiu (bendra eilutė) su nurodytu simboliu Pasirinkite RPAD ('guru99', 10, '-') iš dvigubos išvesties : guru99 ----
LTRIM (tekstas) Apkirpkite iš teksto pagrindinę baltąją erdvę Pasirinkite dvigubą LTRIM ('Guru99'); Rezultatas : Guru99
RTRIM (tekstas) Apkirpkite teksto gale esančią baltą vietą Pasirinkite dvigubą RTRIM ('Guru99'); Išvestis ; Guru99

Datos funkcijos

Tai yra funkcijos, kurios naudojamos manipuliuojant datomis.

Funkcijos pavadinimas Naudojimas Pavyzdys
ADD_MONTHS (data, mėnesių skaičius) Prideda nurodytus mėnesius prie datos ADD_MONTH ('2015-01-01', 5); Rezultatas : 2015-01-05
SYSDATE Grąžina dabartinę serverio datą ir laiką Pasirinkite SYSDATE iš dual; Rezultatas : 2015-10-04 14:11:43
TRUNC Datos kintamojo apvalinimas iki mažiausios galimos vertės pasirinkite sysdate, TRUNC (sysdate) iš dual; Rezultatas : 2015-10-04 14:12:39 PM 2015-04-04
TURAS Datą suapvalina iki artimiausios ribos - didesnę arba mažesnę Pasirinkite sysdate, ROUND (sysdate) iš dvigubo išvesties : 2015-10-04 14:14:34 2015-10-05
MONTHS_BETWEEN Grąžina mėnesių tarp dviejų datų skaičių Pasirinkite MONTHS_BETWEEN (sysdate + 60, sysdate) iš dvigubo išvesties : 2

Santrauka

Šiame skyriuje sužinojome taip.

  • Kaip sukurti Procedūrą ir įvairius jos iškvietimo būdus
  • Kaip sukurti funkciją ir skirtingus būdus jai paskambinti
  • Procedūros ir funkcijos panašumai ir skirtumai
  • Parametrai ir RETURN bendros terminijos PL / SQL paprogramėse
  • Dažniausiai įmontuotos „Oracle PL / SQL“ funkcijos