Shranjeni postopek Oracle PL / SQL & Funkcije s primeri

Kazalo:

Anonim

V tej vadnici boste videli podroben opis, kako ustvariti in izvajati imenovane bloke (postopke in funkcije).

Postopki in funkcije so podprogrami, ki jih je mogoče ustvariti in shraniti v bazo podatkov kot predmete baze podatkov. Lahko jih pokličete ali napotite tudi znotraj drugih blokov.

Poleg tega bomo zajeli glavne razlike med tema dvema podprogramoma. Razpravljali bomo tudi o vgrajenih funkcijah Oracle.

V tej vadnici Oracle Stored Procedure boste izvedeli -

  • Terminologije v podprogramih PL / SQL
  • Kaj je postopek v PL / SQL?
  • Kaj je funkcija?
  • Podobnosti med postopkom in funkcijo
  • Postopek Vs. Funkcija: Ključne razlike
  • Vgrajene funkcije v PL / SQL

Terminologije v podprogramih PL / SQL

Preden se naučimo o podprogramih PL / SQL, bomo obravnavali različne terminologije, ki so del teh podprogramov. Spodaj so terminologije, o katerih bomo razpravljali.

Parameter:

Parameter je spremenljivka ali nadomestno mesto katerega koli veljavnega podatkovnega tipa PL / SQL, prek katerega podprogram PL / SQL izmenjuje vrednosti z glavno kodo. Ta parameter omogoča vnos podprogramov in pridobivanje iz teh podprogramov.

  • Te parametre je treba določiti skupaj s podprogrami v času ustvarjanja.
  • Ti parametri so vključeni v klicni stavek teh podprogramov za interakcijo med vrednostmi in podprogrami.
  • Podatkovni tip parametra v podprogramu in klicni stavek morata biti enaka.
  • Velikost podatkovnega tipa ne sme biti omenjena v času deklaracije parametra, saj je velikost za to vrsto dinamična.

Glede na njihov namen so parametri razvrščeni kot

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

IN parameter:

  • Ta parameter se uporablja za vnos podprogramov.
  • To je spremenljivka samo za branje znotraj podprogramov. Njihovih vrednosti ni mogoče spreminjati znotraj podprograma.
  • V klicnem stavku so lahko ti parametri spremenljivka ali dobesedna vrednost ali izraz, na primer lahko je aritmetični izraz, kot je '5 * 8' ali 'a / b', kjer sta 'a' in 'b' spremenljivki .
  • Privzeto so parametri tipa IN.

Parameter OUT:

  • Ta parameter se uporablja za pridobivanje izhodnih podatkov iz podprogramov.
  • Je spremenljivka za branje in pisanje znotraj podprogramov. Njihove vrednosti je mogoče spreminjati znotraj podprogramov.
  • V stavku klicanja morajo biti ti parametri vedno spremenljivka, ki vsebuje vrednost iz trenutnih podprogramov.

IN OUT Parameter:

  • Ta parameter se uporablja tako za podajanje vhodnih podatkov kot za pridobivanje izhodnih podatkov iz podprogramov.
  • Je spremenljivka za branje in pisanje znotraj podprogramov. Njihove vrednosti je mogoče spreminjati znotraj podprogramov.
  • V stavku klicanja bi morali biti ti parametri vedno spremenljivka, ki vsebuje vrednost iz podprogramov.

Ti parametri morajo biti omenjeni med izdelavo podprogramov.

VRNITEV

RETURN je ključna beseda, ki prevajalniku naroči, naj preklopi krmilnik iz podprograma v klicni stavek. V podprogramu RETURN preprosto pomeni, da mora krmilnik izstopiti iz podprograma. Ko krmilnik v podprogramu najde ključno besedo RETURN, bo koda za tem preskočena.

Običajno nadrejeni ali glavni blok pokliče podprograme, nato pa se nadzor preklopi iz teh nadrejenih blokov v klicane podprograme. RETURN v podprogramu vrne nadzor nazaj v nadrejeni blok. V primeru funkcij stavek RETURN vrne tudi vrednost. Tip podatkov te vrednosti je vedno omenjen v času izjave funkcije. Podatkovni tip je lahko katerega koli veljavnega podatkovnega tipa PL / SQL.

Kaj je postopek v PL / SQL?

Postopek v PL / SQL je podprogram enota, ki je sestavljena iz skupine izjav PL / SQL, ki jih lahko imenujemo po imenu. Vsak postopek v PL / SQL ima svoje edinstveno ime, s katerim se nanj lahko sklicuje in ga kliče. Ta podprogramska enota v zbirki podatkov Oracle je shranjena kot objekt baze podatkov.

Opomba: Podprogram ni nič drugega kot postopek in ga je treba v skladu z zahtevo ustvariti ročno. Ko bodo ustvarjeni, bodo shranjeni kot objekti baze podatkov.

Spodaj so značilnosti enote podprograma Postopek v PL / SQL:

  • Postopki so samostojni bloki programa, ki jih je mogoče shraniti v bazo podatkov.
  • Te postopke PLSQL lahko pokličete tako, da se za izvajanje stavkov PL / SQL sklicujete na njihovo ime.
  • Uporablja se predvsem za izvajanje procesa v PL / SQL.
  • Lahko ima ugnezdene bloke ali pa ga definira in ugnezdi znotraj drugih blokov ali paketov.
  • Vsebuje del deklaracije (neobvezno), izvedbeni del, del za obdelavo izjem (neobvezno).
  • Vrednosti je mogoče prenesti v postopek Oracle ali jih iz parametra pridobiti iz postopka.
  • Te parametre je treba vključiti v izpisek klica.
  • Postopek v SQL ima lahko stavek RETURN za vrnitev kontrolnika v klicni blok, ne more pa vrniti nobene vrednosti prek stavka RETURN.
  • Postopkov ni mogoče poklicati neposredno iz stavkov SELECT. Lahko jih pokličete iz drugega bloka ali prek ključne besede EXEC.

Sintaksa:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE naroči prevajalniku, da v Oracle ustvari nov postopek. Ključna beseda 'ALI ZAMENJAJ' naroči prevajalniku, naj obstoječi postopek (če obstaja) nadomesti s trenutnim.
  • Ime postopka mora biti enolično.
  • Ključna beseda 'IS' bo uporabljena, ko bo shranjena procedura v Oracle ugnezdena v druge bloke. Če je postopek samostojen, se uporabi 'AS'. Razen tega standarda kodiranja imata oba enak pomen.

Primer1: Ustvarjanje postopka in klic z uporabo EXEC

V tem primeru bomo ustvarili postopek Oracle, ki bo ime vzel za vhod in natisnil pozdravno sporočilo kot izhod. Za klic postopka bomo uporabili ukaz EXEC.

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

Razlaga kode:

  • Vrstica kode 1 : Ustvarjanje postopka z imenom 'welcome_msg' in z enim parametrom 'p_name' vrste 'IN'.
  • Vrstica kode 4 : Tiskanje pozdravnega sporočila s spajanjem imena vnosa.
  • Postopek je uspešno sestavljen.
  • 7. vrstica kode : klic postopka z ukazom EXEC s parametrom „Guru99“. Postopek se izvede in sporočilo se natisne kot "Dobrodošli Guru99".

Kaj je funkcija?

Functions je samostojni podprogram PL / SQL. Tako kot postopek PL / SQL imajo tudi funkcije edinstveno ime, na katerega se lahko sklicuje. Ti so shranjeni kot objekti baze podatkov PL / SQL. Spodaj je nekaj značilnosti funkcij.

  • Funkcije so samostojni blok, ki se v glavnem uporablja za izračun.
  • Funkcija za vrnitev vrednosti uporablja ključno besedo RETURN, tip podatkov pa je določen v času ustvarjanja.
  • Funkcija mora vrniti vrednost ali zvišati izjemo, tj. Vrnitev je v funkcijah obvezna.
  • Funkcije brez stavkov DML lahko v poizvedbi SELECT pokličete neposredno, funkcijo z delovanjem DML pa lahko pokličete samo iz drugih blokov PL / SQL.
  • Lahko ima ugnezdene bloke ali pa ga definira in ugnezdi znotraj drugih blokov ali paketov.
  • Vsebuje del deklaracije (neobvezno), izvedbeni del, del za obdelavo izjem (neobvezno).
  • Vrednosti je mogoče prenesti v funkcijo ali iz parametrov pridobiti iz postopka.
  • Te parametre je treba vključiti v izpisek klica.
  • Funkcija PLSQL lahko vrne vrednost tudi prek parametrov OUT, razen z uporabo RETURN.
  • Ker bo vedno vrnil vrednost, se v stavku za klic vedno priloži operaterju dodelitve za zapolnitev spremenljivk.

Sintaksa

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION naroči prevajalniku, da ustvari novo funkcijo. Ključna beseda 'ALI ZAMENJAJ' prevajalniku naroči, naj obstoječo funkcijo (če obstaja) nadomesti s trenutno.
  • Ime funkcije mora biti enolično.
  • Omeniti je treba tip podatkov RETURN.
  • Ključna beseda 'IS' bo uporabljena, ko bo postopek ugnezden v druge bloke. Če je postopek samostojen, se uporabi 'AS'. Razen tega standarda kodiranja imata oba enak pomen.

Primer1: Ustvarjanje funkcije in klicanje z uporabo anonimnega bloka

V tem programu bomo ustvarili funkcijo, ki bo ime vzela za vhod in vrnila pozdravno sporočilo kot izhod. Za klic funkcije bomo uporabili anonimni blok in izbrali stavek.

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;

Razlaga kode:

  • Vrstica kode 1 : Ustvarjanje funkcije Oracle z imenom 'welcome_msg_func' in z enim parametrom 'p_name' vrste 'IN'.
  • Vrstica kode 2 : razglasitev vrste vrnitve kot VARCHAR2
  • Vrstica kode 5 : Vrnitev združene vrednosti "Dobrodošli" in vrednosti parametra.
  • Vrstica kode 8 : Anonimni blok za klic zgornje funkcije.
  • Vrstica kode 9 : razglasitev spremenljivke s podatkovnim tipom, enakim vrnjenemu podatkovnemu tipu funkcije.
  • Vrstica kode 11 : klic funkcije in izpolnitev vrnjene vrednosti v spremenljivko 'lv_msg'.
  • Vrstica kode 12 : Tiskanje vrednosti spremenljivke. Rezultat, ki ga boste dobili tukaj, je "Dobrodošli Guru99"
  • Vrstica kode 14 : Klicanje iste funkcije prek stavka SELECT. Vrnjena vrednost je usmerjena neposredno na standardni izhod.

Podobnosti med postopkom in funkcijo

  • Oboje je mogoče poklicati iz drugih blokov PL / SQL.
  • Če izjema, ki je bila postavljena v podprogramu, ni obravnavana v razdelku za obravnavo izjem iz podprograma, se bo razširila na klicni blok.
  • Oba imata lahko toliko parametrov, kot je zahtevano.
  • Oba se v PL / SQL obravnavata kot predmeta zbirke podatkov.

Postopek Vs. Funkcija: Ključne razlike

Postopek Funkcija
  • Uporablja se predvsem za izvedbo določenega postopka
  • Uporablja se predvsem za izvajanje nekaterih izračunov
  • V stavku SELECT ni mogoče poklicati
  • V stavku SELECT je mogoče poklicati funkcijo, ki ne vsebuje stavkov DML
  • Za vrnitev vrednosti uporabite parameter OUT
  • Za vrnitev vrednosti uporabite RETURN
  • Vrnitev vrednosti ni obvezna
  • Vrniti vrednost je obvezno
  • RETURN bo preprosto zapustil nadzor iz podprograma.
  • RETURN zapre nadzor iz podprograma in vrne vrednost
  • Vrsta podatkov o vrnitvi ob izdelavi ne bo določena
  • Vrnitev podatkovnega tipa je obvezna v času ustvarjanja

Vgrajene funkcije v PL / SQL

PL / SQL vsebuje različne vgrajene funkcije za delo z nizi in podatkovnim tipom datuma. Tu bomo videli pogosto uporabljene funkcije in njihovo uporabo.

Pretvorbene funkcije

Te vgrajene funkcije se uporabljajo za pretvorbo enega podatkovnega tipa v drugega.

Ime funkcije Uporaba Primer
TO_CHAR Pretvori drugi podatkovni tip v podatkovni tip znakov TO_CHAR (123);
TO_DATE (niz, oblika) Pretvori dani niz v datum. Niz se mora ujemati z obliko. DO_DATUM ('2015-JAN-15', 'LLLL-PON-DD'); Izhod: 15.1.2015
TO_NUMBER (besedilo, oblika) Pretvori besedilo v vrsto števil v dani obliki. Podatek '9' označuje število števk Izberite TO_NUMBER ('1234', '9999') med dvojnimi; Izhod: 1234 Izberite TO_NUMBER ('1.234,45', '9,999.99') med dvojnimi; Izhod: 1234

Funkcije niza

To so funkcije, ki se uporabljajo za tip podatkov znakov.

Ime funkcije Uporaba Primer
INSTR (besedilo, niz, začetek, pojav) Podaja položaj določenega besedila v danem nizu.
  • besedilo - glavni niz
  • niz - besedilo, ki ga je treba iskati
  • start - začetni položaj iskanja (neobvezno)
  • skladnost - pojav iskanega niza (neobvezno)
 Iz dvojnega izhoda izberite INSTR ('LETALO', 'E', 2,1) : 2 Iz dvojnega izhoda izberite 9 INSTR ('LETALO', 'E', 2,2): 9 ( 2. pojav E)
SUBSTR (besedilo, začetek, dolžina) Daje vrednost podniza glavnega niza.
  • besedilo - glavni niz
  • start - začetni položaj
  • length - dolžina, ki jo je treba podnizati
 izberite dual substr ('letalo', 1,7) iz dvojnega izhoda : aeropla
ZGOR (besedilo) Vrne velike črke podanega besedila Izberite zgornji ('guru99') med dvojnimi; Izhod : GURU99
SPODNJA (besedilo) Vrne male črke podanega besedila Izberite nižje ('letalo') med dvojnimi; Izhod : letalo
INITCAP (besedilo) Vrne dano besedilo z začetnico v velikih črkah. Izberite ('guru99') iz dvojnega izhoda : Guru99 Select ('moja zgodba') iz dvojnega izhoda : Moja zgodba
LENGTH (besedilo) Vrne dolžino podanega niza Izberite LENGTH ('guru99') med dvojnimi; Izhod : 6
LPAD (besedilo, dolžina, pad_char) Niz na levi strani za določeno dolžino (skupni niz) doda z danim znakom Izberite LPAD ('guru99', 10, '$') med dvojnimi; Rezultat : $$$$ guru99
RPAD (besedilo, dolžina, pad_char) Niz na desni strani za določeno dolžino (skupni niz) položi z danim znakom Izberite RPAD ('guru99', 10, '-') iz dvojnega izhoda : guru99 ----
LTRIM (besedilo) Iz besedila je obrezan vodilni presledek Izberite LTRIM ('Guru99') med dvojnimi; Rezultat : Guru99
RTRIM (besedilo) Iz besedila obreže končni presledek Izberite RTRIM ('Guru99') med dvojnimi; Izhod ; Guru99

Datum funkcije

To so funkcije, ki se uporabljajo za manipulacijo z datumi.

Ime funkcije Uporaba Primer
ADD_MONTHS (datum, št. Mesecev) Dane mesece doda datumu ADD_MONTH ('01.01.2015', 5); Izhod : 01.05.2015
SYSDATE Vrne trenutni datum in uro strežnika Izberite SYSDATE med dvojnimi; Izhod : 4. 10. 2015 14:11:43
TRUNC Krog datumske spremenljivke na najnižjo možno vrednost izberite sysdate, TRUNC (sysdate) iz dual; Izhod : 4.10.2015 14:12:39 04.10.2015
OKROGLA Datum zaokroži na najbližjo mejo, višjo ali nižjo Izberite sysdate, ROUND (sysdate) iz dvojnega izhoda : 4.10.2015 14:14:34 05.10.2015
MONTHS_BETWEEN Vrne število mesecev med dvema datumoma Izberite MONTHS_BETWEEN (sysdate + 60, sysdate) iz dvojnega izhoda : 2

Povzetek

V tem poglavju smo izvedeli naslednje.

  • Kako ustvariti postopek in različne načine njegovega klica
  • Kako ustvariti funkcijo in različne načine njenega klicanja
  • Podobnosti in razlike med postopkom in funkcijo
  • Parametri in RETURN skupne terminologije v podprogramih PL / SQL
  • Pogoste vgrajene funkcije v Oracle PL / SQL