Oracle PL / SQL: n tallentama menettely & Toiminnot esimerkkien kanssa

Sisällysluettelo:

Anonim

Tässä opetusohjelmassa näet yksityiskohtaisen kuvauksen nimettyjen lohkojen (menettelyt ja toiminnot) luomisesta ja suorittamisesta.

Menettelytavat ja toiminnot ovat aliohjelmia, jotka voidaan luoda ja tallentaa tietokantaan tietokantaobjekteina. Niitä voidaan kutsua tai viitata myös muihin lohkoihin.

Tämän lisäksi tarkastelemme näiden kahden alaohjelman suurimpia eroja. Aiomme myös keskustella Oraclen sisäänrakennetuista toiminnoista.

Tässä Oracle Stored Procedure -oppaassa opit

  • Termit PL / SQL-alaohjelmissa
  • Mikä on menettely PL / SQL: ssä?
  • Mikä on toiminto?
  • Menettelyn ja toiminnon yhtäläisyydet
  • Menettely Vs. Tehtävä: Keskeiset erot
  • Sisäänrakennetut toiminnot PL / SQL: ssä

Termit PL / SQL-alaohjelmissa

Ennen kuin opimme PL / SQL-alaohjelmista, keskustelemme eri terminologioista, jotka ovat osa näitä aliohjelmia. Alla on terminologia, josta aiomme keskustella.

Parametri:

Parametri on muuttuja tai minkä tahansa kelvollisen PL / SQL-tietotyypin paikkamerkki, jonka kautta PL / SQL-aliohjelma vaihtaa arvot pääkoodin kanssa. Tämän parametrin avulla voidaan syöttää aliohjelmia ja purkaa niistä.

  • Nämä parametrit tulisi määritellä yhdessä aliohjelmien kanssa luontihetkellä.
  • Nämä parametrit sisältyvät näiden aliohjelmien kutsulausekkeeseen vuorovaikutuksessa arvojen kanssa aliohjelmien kanssa.
  • Aliohjelman parametrin tietotyypin ja kutsulausekkeen tulisi olla samat.
  • Tietotyypin kokoa ei tule mainita parametrin ilmoittamisen yhteydessä, koska koko on dynaaminen tälle tyypille.

Parametrit luokitellaan niiden tarkoituksen perusteella

  1. IN-parametri
  2. OUT-parametri
  3. IN OUT -parametri

IN-parametri:

  • Tätä parametria käytetään syöttämään alaohjelmia.
  • Se on vain luku -muuttuja aliohjelmien sisällä. Niiden arvoja ei voida muuttaa alaohjelman sisällä.
  • Kutsuvassa lausekkeessa nämä parametrit voivat olla muuttuja tai litraaliarvo tai lauseke, esimerkiksi se voi olla aritmeettinen lauseke kuten '5 * 8' tai 'a / b' missä 'a' ja 'b' ovat muuttujia .
  • Oletusarvoisesti parametrit ovat IN-tyyppisiä.

OUT-parametri:

  • Tätä parametria käytetään aliohjelmien ulostulon hakemiseen.
  • Se on lukuohjelmien muuttuja. Niiden arvoja voidaan muuttaa alaohjelmien sisällä.
  • Kutsuvassa lausekkeessa näiden parametrien tulisi aina olla muuttuja nykyisten alaohjelmien arvon pitämiseksi.

IN OUT -parametri:

  • Tätä parametria käytetään sekä syötteen antamiseen että lähtöohjelman tuottamiseen.
  • Se on lukuohjelmien muuttuja. Niiden arvoja voidaan muuttaa alaohjelmien sisällä.
  • Kutsuvassa lausekkeessa näiden parametrien tulisi aina olla muuttujat, jotka pitävät arvon aliohjelmista.

Nämä parametrityypit tulee mainita aliohjelmien luomisen yhteydessä.

PALATA

RETURN on avainsana, joka kehottaa kääntäjää vaihtamaan ohjauksen alaohjelmasta kutsulausekkeeseen. Alaohjelmassa RETURN tarkoittaa yksinkertaisesti sitä, että ohjauksen on poistuttava alaohjelmasta. Kun ohjain löytää RETURN-avainsanan alaohjelmasta, tämän jälkeinen koodi ohitetaan.

Normaalisti vanhempi tai päälohko kutsuu aliohjelmat, ja sitten ohjaus siirtyy kyseisistä päälohkoista kutsuttuihin alaohjelmiin. RETURN aliohjelmassa palauttaa ohjauksen takaisin päälohkoonsa. Funktioiden tapauksessa RETURN-käsky palauttaa myös arvon. Tämän arvon tietotyyppi mainitaan aina funktion ilmoittamisen yhteydessä. Tietotyyppi voi olla mitä tahansa kelvollista PL / SQL-tietotyyppiä.

Mikä on menettely PL / SQL: ssä?

Menettely PL / SQL on aliohjelman yksikkö, joka koostuu ryhmästä PL / SQL, jota voidaan kutsua nimellä. Jokaisella PL / SQL-menetelmällä on oma yksilöllinen nimi, jolla siihen voidaan viitata ja kutsua. Tämä Oracle-tietokannan alaohjelmayksikkö on tallennettu tietokantaobjektiksi.

Huomaa: Aliohjelma ei ole muuta kuin menettely, ja se on luotava manuaalisesti vaatimuksen mukaisesti. Kun ne on luotu, ne tallennetaan tietokantaobjekteiksi.

Alla ovat PL / SQL: n Procedure-alaohjelmayksikön ominaisuudet:

  • Menettelytavat ovat erillisiä ohjelman lohkoja, jotka voidaan tallentaa tietokantaan.
  • Näihin PLSQL-menettelyihin voidaan soittaa viittaamalla niiden nimiin PL / SQL-käskyjen suorittamiseksi.
  • Sitä käytetään pääasiassa prosessin suorittamiseen PL / SQL: ssä.
  • Siinä voi olla sisäkkäisiä lohkoja, tai se voidaan määrittää ja sijoittaa muiden lohkojen tai pakettien sisään.
  • Se sisältää ilmoituksen osan (valinnainen), suoritusosan, poikkeuksen käsittelyosan (valinnainen).
  • Arvot voidaan siirtää Oracle-menettelyyn tai noutaa menettelystä parametrien kautta.
  • Nämä parametrit tulisi sisällyttää kutsulausekkeeseen.
  • SQL-menettelyllä voi olla RETURN-käsky palauttaa ohjausobjekti kutsulohkolle, mutta se ei voi palauttaa arvoja RETURN-käskyn kautta.
  • Menettelyjä ei voida kutsua suoraan SELECT-lauseista. Niitä voidaan kutsua toisesta lohkosta tai EXEC-avainsanan kautta.

Syntaksi:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE kehottaa kääntäjää luomaan uuden menettelyn Oraclessa. Avainsana TAI VAIHDA käskee kääntää korvaamaan nykyisen menettelyn (jos sellainen on) nykyisellä.
  • Menettelynimen tulee olla yksilöllinen.
  • Avainsanaa 'IS' käytetään, kun Oraclen tallennettu menettely on sisäkkäin joihinkin muihin lohkoihin. Jos menettely on itsenäinen, käytetään 'AS'. Molemmilla on sama merkitys kuin tällä koodausstandardilla.

Esimerkki 1: Menettelyn luominen ja kutsuminen EXEC: llä

Tässä esimerkissä aiomme luoda Oracle-menettelyn, joka ottaa nimen syötteeksi ja tulostaa tervetuloviestin lähtöön. Aiomme käyttää EXEC-komentoa kutsua menettelyyn.

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

Koodin selitys:

  • Koodirivi 1 : Menettelyn luominen nimellä 'welcome_msg' ja yhdellä 'IN' -tyyppisellä parametrilla 'p_name'.
  • Koodirivi 4 : Tervetuloviestin tulostaminen yhdistämällä syötteen nimi.
  • Menettely on koottu onnistuneesti.
  • Koodirivi 7 : Menettelyn kutsuminen EXEC-komennolla parametrilla 'Guru99'. Menettely suoritetaan ja viesti tulostetaan muodossa "Welcome Guru99".

Mikä on toiminto?

Funktiot on erillinen PL / SQL-alaohjelma. PL / SQL-menettelyn tavoin funktioilla on yksilöllinen nimi, jolla niihin voidaan viitata. Nämä tallennetaan PL / SQL-tietokantaobjekteina. Alla on joitain toimintojen ominaisuuksia.

  • Funktiot ovat erillinen lohko, jota käytetään pääasiassa laskentatarkoituksiin.
  • Funktio käyttää RETURN-avainsanaa arvon palauttamiseen, ja sen tietotyyppi määritetään luontihetkellä.
  • Funktion tulisi joko palauttaa arvo tai nostaa poikkeus, eli palautus on pakollinen funktioissa.
  • Funktio, jolla ei ole DML-käskyjä, voidaan kutsua suoraan SELECT-kyselyssä, kun taas toiminto, jolla on DML-toiminto, voidaan kutsua vain muista PL / SQL-lohkoista.
  • Siinä voi olla sisäkkäisiä lohkoja, tai se voidaan määrittää ja sijoittaa muiden lohkojen tai pakettien sisään.
  • Se sisältää ilmoituksen osan (valinnainen), suoritusosan, poikkeuksen käsittelyosan (valinnainen).
  • Arvot voidaan siirtää funktioon tai noutaa menettelystä parametrien kautta.
  • Nämä parametrit tulisi sisällyttää kutsulausekkeeseen.
  • PLSQL-toiminto voi myös palauttaa arvon OUT-parametrien kautta muulla kuin RETURN-toiminnolla.
  • Koska se palauttaa aina arvon, kutsulausekkeessa sen mukana on aina tehtäväoperaattori muuttujien täyttämiseksi.

Syntaksi

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION kehottaa kääntäjää luomaan uuden toiminnon. Hakusana TAI VAIHDA kehottaa kääntäjää korvaamaan nykyisen toiminnon (jos sellainen on) nykyisellä.
  • Toiminnon nimen tulee olla yksilöllinen.
  • RETURN-tietotyyppi tulisi mainita.
  • Avainsanaa 'IS' käytetään, kun menettely on sisäkkäin joihinkin muihin lohkoihin. Jos menettely on itsenäinen, käytetään 'AS'. Molemmilla on sama merkitys kuin tällä koodausstandardilla.

Esimerkki 1: Funktion luominen ja kutsuminen nimettömällä estolla

Tässä ohjelmassa aiomme luoda toiminnon, joka ottaa nimen syötteeksi ja palauttaa tervetuloviestin lähtöön. Aiomme käyttää anonyymia estoa ja valitse lauseke kutsua funktio.

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;

Koodin selitys:

  • Koodirivi 1 : Oracle-toiminnon luominen nimellä 'welcome_msg_func' ja yhdellä parametrilla 'p_name', jonka tyyppi on 'IN'.
  • Koodirivi 2 : palautustyypin ilmoittaminen VARCHAR2: ksi
  • Koodirivi 5 : Palautetaan liitetty arvo 'Welcome' ja parametriarvo.
  • Koodirivi 8 : Anonyymi estää yllä olevan toiminnon kutsumisen.
  • Koodirivi 9 : Muuttujan ilmoittaminen datatyypillä sama kuin funktion palautustietotyyppi.
  • Koodirivi 11 : Funktion kutsuminen ja paluuarvon lisääminen muuttujaan 'lv_msg'.
  • Koodirivi 12 : Muuttujan arvon tulostaminen. Tuloksena saat "Welcome Guru99"
  • Koodirivi 14 : Saman toiminnon kutsuminen SELECT-käskyn kautta. Palautusarvo ohjataan suoraan vakiolähtöön.

Menettelyn ja toiminnon yhtäläisyydet

  • Molempia voidaan kutsua muista PL / SQL-lohkoista.
  • Jos alaohjelmassa nostettua poikkeusta ei käsitellä alaohjelman poikkeusten käsittelyosassa, se etenee kutsulohkoon.
  • Molemmilla voi olla niin monta parametria kuin vaaditaan.
  • Molempia käsitellään tietokantaobjekteina PL / SQL: ssä.

Menettely Vs. Tehtävä: Keskeiset erot

Menettely Toiminto
  • Käytetään pääasiassa tietyn prosessin suorittamiseen
  • Käytetään pääasiassa joidenkin laskelmien suorittamiseen
  • Ei voi soittaa SELECT-käskyssä
  • Funktio, joka ei sisällä DML-käskyjä, voidaan kutsua SELECT-käskyssä
  • Palauta arvo parametrilla OUT
  • Palauta arvo käyttämällä RETURN-painiketta
  • Arvon palauttaminen ei ole pakollista
  • Arvo on palautettava
  • RETURN yksinkertaisesti sulkee ohjauksen alaohjelmasta.
  • RETURN sulkee ohjausohjelman alaohjelmasta ja palauttaa myös arvon
  • Palautuksen tietotyyppiä ei määritetä luontihetkellä
  • Palautuksen tietotyyppi on pakollinen luontihetkellä

Sisäänrakennetut toiminnot PL / SQL: ssä

PL / SQL sisältää useita sisäänrakennettuja toimintoja, jotka toimivat merkkijonojen ja päivämäärätyyppien kanssa. Täällä näemme yleisesti käytetyt toiminnot ja niiden käytön.

Muunnosfunktiot

Näitä sisäänrakennettuja toimintoja käytetään muuntaa yksi tietotyyppi toiseksi tietotyypiksi.

Toiminnon nimi Käyttö Esimerkki
TO_CHAR Muuntaa toisen tietotyypin merkkitietotyypiksi TO_CHAR (123);
TO_DATE (merkkijono, muoto) Muuntaa annetun merkkijonon päivämääräksi. Merkkijonon on vastattava muotoa. TO_DATE ('2015-TAMMI-15', 'VVVV-ma-PP'); Tuotos: 15.1.2015
TO_NUMBER (teksti, muoto) Muuntaa tekstin annetun muodon numerotyypiksi. Informat '9' tarkoittaa numeroiden lukumäärää Valitse kaksoisviestistä TO_NUMBER ('1234', '9999'); Tulos: 1234 Valitse TO_NUMBER ('1 234,45', '9 999,99') kaksoisnapsautuksesta; Lähtö: 1234

Merkkijonotoiminnot

Nämä ovat funktioita, joita käytetään merkkitietotyypissä.

Toiminnon nimi Käyttö Esimerkki
INSTR (teksti, merkkijono, alku, esiintyminen) Antaa tietyn tekstin sijainnin annetussa merkkijonossa.
  • teksti - päämerkkijono
  • merkkijono - teksti, jota on etsittävä
  • aloitus - haun aloituspaikka (valinnainen)
  • sääntö - haetun merkkijonon esiintyminen (valinnainen)
 Valitse INSTR (AEROPLANE ', 'E', 2,1), mistä kahden Tuotos : 2 Valitse INSTR (AEROPLANE', 'E', 2,2), mistä kahden Lähtö: 9 (2 toinen esiintyminen E)
SUBSTR (teksti, alku, pituus) Antaa päämerkkijonon alimerkkiarvon.
  • teksti - päämerkkijono
  • lähtö - lähtöasento
  • pituus - pituus merkkijonoksi
 Valitse substr ( 'lentokone', 1,7) kaksoisroolien Lähtö : aeropla
YLÄ (teksti) Palauttaa toimitetun tekstin ison kirjaimen Valitse ylempi ('guru99') kaksoisnapsautuksesta; Tuotos : GURU99
LOWER (teksti) Palauttaa toimitetun tekstin pienet kirjaimet Valitse alempi ('lentokone') kaksoisnapsautuksesta; Tuotos : lentokone
INITCAP (teksti) Palauttaa annetun tekstin alkukirjaimella isoilla kirjaimilla. Valitse ( 'guru99) kaksoisroolien Lähtö : Guru99 Valitse (Oma tarina) kaksoisroolien Lähtö : My Story
PITUUS (teksti) Palauttaa annetun merkkijonon pituuden Valitse PITUUS ('guru99') kaksoisnapsautuksesta; Lähtö : 6
LPAD (teksti, pituus, pad_char) Pehmustaa merkkijonon vasemmalla puolella tietylle pituudelle (kokonaismerkkijono) annetulla merkillä Valitse kaksinkertainen LPAD ('guru99', 10, '$'); Tuotos : $$$$ guru99
RPAD (teksti, pituus, pad_char) Pehmustaa merkkijonon oikealla puolella tietylle pituudelle (kokonaismerkkijono) annetulla merkillä Valitse RPAD (guru99 ', 10, '-') kaksoisroolien Lähtö : guru99 ----
LTRIM (teksti) Leikkaa tekstin johtavan valkoisen tilan Valitse LTRIM ('Guru99') kaksoisnapsautuksesta; Tuotos : Guru99
RTRIM (teksti) Leikkaa tekstin jäljessä olevan valkoisen tilan Valitse RTRIM ('Guru99') kaksoisviestistä; Tuotos ; Guru99

Päivämäärätoiminnot

Nämä ovat toimintoja, joita käytetään manipuloimaan päivämäärien kanssa.

Toiminnon nimi Käyttö Esimerkki
ADD_MONTHS (päiväys, kuukausien määrä) Lisää annetut kuukaudet päivämäärään ADD_MONTH ('2015-01-01', 5); Tuotos : 05.01.2015
SYSDATE Palauttaa palvelimen nykyisen päivämäärän ja kellonajan Valitse SYSDATE dualista; Lähtö : 4.10.2015 14:11:43
TRUNC Kierrä päivämäärämuuttuja pienimpään mahdolliseen arvoon valitse sysdate, TRUNC (sysdate) kaksoisohjelmasta; Lähtö : 4.10.2015 14:12:39 4.10.2015
PYÖRISTÄÄ Pyöristää päivämäärän lähimpään rajaan joko suuremmaksi tai pienemmäksi Valitse SYSDATE, pyöreä (SYSDATE) kaksoisroolien Lähtö : 04.10.2015 14:14:34 05.10.2015
MONTHS_BETWEEN Palauttaa kahden päivämäärän välisen kuukauden lukumäärän Valitse MONTHS_BETWEEN (SYSDATE + 60, SYSDATE) alkaen kaksi Tuotos : 2

Yhteenveto

Tässä luvussa olemme oppineet seuraavat.

  • Kuinka luoda menettely ja eri tapoja kutsua sitä
  • Kuinka luoda toiminto ja erilaisia ​​tapoja kutsua sitä
  • Menettelyn ja toiminnon yhtäläisyydet ja erot
  • Parametrit ja RETURN-yhteiset terminologiat PL / SQL-aliohjelmissa
  • Yleiset sisäänrakennetut toiminnot Oracle PL / SQL: ssä