Oracle PL/SQL-tallennettu prosessi ja funktiot esimerkein

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

Proseduurit ja funktiot ovat aliohjelmia, jotka voidaan luoda ja tallentaa tietokantaan tietokantaobjekteina. Niitä voidaan kutsua tai viitata myös muiden lohkojen sisällä.

Tämän lisäksi käsittelemme tärkeimmät erot näiden kahden aliohjelman välillä. Lisäksi aiomme keskustella Oracle sisäänrakennetut toiminnot.

Terminologiat PL/SQL-aliohjelmissa

Ennen kuin opimme PL/SQL-aliohjelmista, keskustelemme erilaisista terminologioista, jotka ovat osa näitä aliohjelmia. Alla on terminologiat, joista aiomme keskustella.

Parametri

Parametri on minkä tahansa kelvollisen muuttuja tai paikkamerkki PL/SQL-tietotyyppi jonka kautta PL/SQL-aliohjelma vaihtaa arvot pääkoodin kanssa. Tämä parametri mahdollistaa syötteen syöttämisen aliohjelmille ja poiminnan näistä aliohjelmista.

• Nämä parametrit tulee määrittää aliohjelmien kanssa luonnin yhteydessä.
• Nämä parametrit sisältyvät näiden aliohjelmien kutsulauseeseen arvojen vuorovaikuttamiseksi aliohjelmien kanssa.
• Aliohjelman parametrin ja kutsuvan käskyn tietotyypin tulee olla sama.
• Tietotyypin kokoa ei tulisi mainita parametrien määrittelyhetkellä, koska koko on tälle tyypille dynaaminen.

Käyttötarkoituksensa perusteella parametrit luokitellaan

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

IN-parametri

• Tätä parametria käytetään syöttämään aliohjelmia.
• Se on vain luku -muotoinen muuttuja aliohjelmien sisällä. Niiden arvoja ei voi muuttaa aliohjelman sisällä.
• Kutsuvassa käskyssä nämä parametrit voivat olla muuttuja tai kirjaimellinen arvo tai lauseke, esimerkiksi se voi olla aritmeettinen lauseke, kuten '5*8' tai 'a/b', missä 'a' ja 'b' ovat muuttujia. .
• Oletusarvoisesti parametrit ovat IN-tyyppiä.

OUT-parametri

• Tätä parametria käytetään tulosteiden saamiseen aliohjelmista.
• Se on luku-kirjoitettava muuttuja aliohjelmien sisällä. Niiden arvoja voidaan muuttaa aliohjelmien sisällä.
• Kutsuvassa käskyssä näiden parametrien tulee aina olla muuttujia, jotka sisältävät nykyisten aliohjelmien arvon.

IN OUT -parametri

• Tätä parametria käytetään sekä syötteen antamiseen että tulosteiden saamiseen aliohjelmista.
• Se on luku-kirjoitettava muuttuja aliohjelmien sisällä. Niiden arvoja voidaan muuttaa aliohjelmien sisällä.
• Kutsuvassa käskyssä näiden parametrien tulee aina olla muuttujia, jotka sisältävät aliohjelmien arvon.

Nämä parametrityypit tulee mainita aliohjelmia luotaessa.

PALATA

RETURN on avainsana, joka käskee kääntäjää vaihtamaan ohjauksen aliohjelmasta kutsuvaan lauseeseen. Aliohjelmassa RETURN tarkoittaa yksinkertaisesti sitä, että ohjauksen on poistuttava aliohjelmasta. Kun ohjain löytää aliohjelmasta RETURN-avainsanan, sen jälkeinen koodi ohitetaan.

Normaalisti ylä- tai päälohko kutsuu aliohjelmia, ja sitten ohjaus siirtyy näistä päälauseista kutsuttuihin aliohjelmiin. RETURN aliohjelmassa palauttaa ohjauksen takaisin päälauseeseensa. Funktioiden tapauksessa RETURN-lause palauttaa myös arvon. Tämän arvon tietotyyppi mainitaan aina funktion määrittelyn yhteydessä. Tietotyyppi voi olla mikä tahansa kelvollinen PL/SQL-tietotyyppi.

Huomautus: Aliohjelma ei ole muuta kuin menettely, ja se on luotava manuaalisesti vaatimusten mukaisesti. Kun ne on luotu, ne tallennetaan tietokantaobjekteina.

Alla on Procedure-aliohjelmayksikön ominaisuudet PL/SQL:ssä:

• Proseduurit ovat erillisiä ohjelman lohkoja, jotka voidaan tallentaa järjestelmään tietokanta.
• Näitä PLSQL-proseduureja voidaan kutsua viittaamalla niiden nimeen 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 voi olla määritetty ja sisäkkäinen muiden lohkojen tai pakettien sisällä.
• Se sisältää ilmoitusosan (valinnainen), suoritusosan, poikkeusten käsittelyosan (valinnainen).
• Arvot voidaan siirtää Oracle proseduurista tai haetaan prosessista parametrien kautta.
• Nämä parametrit tulee sisällyttää kutsuvaan lauseeseen.
• SQL:n toimintosarjassa voi olla RETURN-käsky, joka palauttaa ohjauksen kutsuvaan lohkoon, mutta se ei voi palauttaa arvoja RETURN-käskyn kautta.
• Proseduureja ei voi kutsua suoraan SELECT-käskyistä. Niitä voidaan kutsua toisesta lohkosta tai EXEC-avainsanan kautta.

Syntaksi

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;

• CREATE PROCEDURE käskee kääntäjää luomaan uuden proseduurin Oracle. Avainsana 'OR REPLACE' ohjeistaa kääntäjää korvaamaan olemassa olevan menettelyn (jos sellainen on) nykyisellä.
• Menettelyn nimen tulee olla yksilöllinen.
• Avainsanaa "IS" käytetään, kun tallennettu toimintosarja sisään Oracle on sisäkkäinen joihinkin muihin lohkoihin. Jos menettely on erillinen, käytetään "AS". Tämän koodausstandardin lisäksi molemmilla on sama merkitys.

Esimerkki 1: Proseduurin luominen ja kutsuminen EXEC:llä

Tässä esimerkissä aiomme luoda an Oracle menettely, joka ottaa nimen syötteeksi ja tulostaa tervetuloviestin tulosteena. Aiomme käyttää EXEC-komentoa menettelyn kutsumiseen.

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

Koodin selitys:

• Koodirivi 1: Proseduurin luominen nimellä 'welcome_msg' ja yhdellä parametrilla 'p_name', jonka tyyppi on 'IN'.
• Koodirivi 4: Tervetuloviestin tulostaminen ketjuttamalla syötteen nimi.
• Toimenpide on koottu onnistuneesti.
• Koodirivi 7: Proseduurin kutsuminen EXEC-komennolla parametrilla 'Guru99'. Toimenpide suoritetaan ja viesti tulostetaan nimellä "Tervetuloa Guru99".

Mikä on Function?

Functions on erillinen PL/SQL-aliohjelma. Kuten PL/SQL-proseduurilla, 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 palauttamaan arvon, ja tämän tietotyyppi määritellään luomishetkellä.
• Funktion tulee joko palauttaa arvo tai korottaa poikkeusta, eli palautus on funktioissa pakollinen.
• Funktiota, jossa ei ole DML-käskyjä, voidaan kutsua suoraan SELECT-kyselyssä, kun taas funktiota, jossa on DML-toiminto, voidaan kutsua vain muista PL/SQL-lohkoista.
• Siinä voi olla sisäkkäisiä lohkoja tai se voi olla määritetty ja sisäkkäinen muiden lohkojen tai pakettien sisällä.
• Se sisältää ilmoitusosan (valinnainen), suoritusosan, poikkeusten käsittelyosan (valinnainen).
• Arvot voidaan siirtää funktioon tai hakea prosessista parametrien kautta.
• Nämä parametrit tulee sisällyttää kutsuvaan lauseeseen.
• PLSQL-funktio voi palauttaa arvon myös muilla OUT-parametreilla kuin RETURN-toiminnolla.
• Koska se palauttaa aina arvon, kutsuvassa käskyssä se liitetään aina määritysoperaattorin kanssa muuttujien täyttämiseksi.

Syntaksi

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 ohjeistaa kääntäjää luomaan uuden funktion. Avainsana 'OR REPLACE' ohjeistaa kääntäjää korvaamaan olemassa olevan funktion (jos sellainen on) nykyisellä.
• Toiminnon nimen tulee olla yksilöllinen.
• RETURN-tietotyyppi on mainittava.
• Avainsanaa 'IS' käytetään, kun proseduuri on upotettu joihinkin muihin lohkoihin. Jos menettely on erillinen, käytetään "AS". Tämän koodausstandardin lisäksi molemmilla on sama merkitys.

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

Tässä ohjelmassa aiomme luoda funktion, joka ottaa nimen syötteenä ja palauttaa tervetuloviestin lähtönä. Aiomme käyttää anonyymiä lohkoa ja valita lauseketta kutsuaksesi funktiota.

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;

Koodin selitys:

• Koodirivi 1: Luodaan Oracle funktio, jonka nimi on 'welcome_msg_func' ja yksi parametri 'p_name', jonka tyyppi on IN.
• Koodirivi 2: ilmoittaa palautustyypiksi VARCHAR2
• Koodirivi 5: Palauttaa ketjutetun arvon 'Tervetuloa' ja parametrin arvon.
• Koodirivi 8: Nimetön esto yllä olevan toiminnon kutsumiseksi.
• Koodirivi 9: Ilmoitetaan muuttuja, jonka tietotyyppi on sama kuin funktion palautustietotyyppi.
• Koodirivi 11: funktion kutsuminen ja palautusarvon täyttäminen muuttujaan 'lv_msg'.
• Koodirivi 12: Tulostetaan muuttujan arvo. Täältä saatava tulos on "Tervetuloa Guru99"
• Koodirivi 14: Saman funktion kutsuminen SELECT-käskyn kautta. Palautusarvo ohjataan suoraan vakiolähtöön.

Menettelyn ja funktion yhtäläisyydet

• Molempia voidaan kutsua muista PL/SQL-lohkoista.
• Jos aliohjelmassa esitettyä poikkeusta ei käsitellä aliohjelmassa poikkeusten käsittely -osiossa, se etenee kutsuvaan lohkoon.
• Molemmilla voi olla niin monta parametria kuin tarvitaan.
• 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 laskelmien suorittamiseen
Ei voi kutsua SELECT-käskyä	Funktiota, joka ei sisällä DML-käskyjä, voidaan kutsua SELECT-käskyssä
Käytä OUT-parametria palauttaaksesi arvon	Käytä RETURN palauttaaksesi arvon
Arvon palauttaminen ei ole pakollista	Arvon palauttaminen on pakollista
RETURN yksinkertaisesti poistuu ohjauksesta aliohjelmasta.	RETURN sulkee ohjauksen aliohjelmasta ja palauttaa myös arvon
Palautustietotyyppiä ei määritetä luonnin yhteydessä	Palautustietotyyppi on pakollinen luomishetkellä

Sisäänrakennetut toiminnot PL/SQL:ssä

PL / SQL sisältää erilaisia ​​sisäänrakennettuja toimintoja merkkijonojen ja päivämäärätietotyyppien kanssa työskentelemiseen. Täällä aiomme nähdä yleisesti käytetyt toiminnot ja niiden käytön.

Muunnosfunktiot

Näitä sisäänrakennettuja toimintoja käytetään muuntamaan 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 tulee vastata muotoa.	TO_DATE('2015-JAN-15', 'VVVV-MA-PP'); ulostulo: 1 / 15 / 2015
TO_NUMBER (teksti, muoto)	Muuntaa tekstin tietyn muodon numerotyypiksi. Informat '9' tarkoittaa numeroiden määrää	Valitse TO_NUMBER('1234','9999') dualista; ulostulo: 1234 Valitse TO_NUMBER('1,234.45','9,999.99') dualista; ulostulo: 1234

Merkkijonofunktiot

Nämä ovat toimintoja, 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, joka on etsittävä aloitus – haun aloituspaikka (valinnainen) yhdenmukaisuus – haetun merkkijonon esiintyminen (valinnainen)	Valitse INSTR('LENTOkone','E',2,1) dualista ulostulo: 2 Valitse INSTR('LENTOkone','E',2,2) dualista ulostulo: 9 (2nd E) esiintyminen
SUBSTR (teksti, alku, pituus)	Antaa päämerkkijonon alimerkkijonon arvon. teksti – päämerkkijono aloitusasento pituus – pituus alimerkkijonoon	valitse substr('lentokone',1,7) dualista ulostulo: aeropla
UPPER ( teksti )	Palauttaa annetun tekstin isot kirjaimet	Valitse ylempi('guru99') dualista; ulostulo: GURU99
ALEMPI ( teksti )	Palauttaa annetun tekstin pienet kirjaimet	Valitse alempi ('AerOpLane') dualista; ulostulo: lentokone
INITCAP (teksti)	Palauttaa annetun tekstin aloituskirjaimella isolla kirjaimella.	Valitse ('guru99') dualista ulostulo: Guru99 Valitse ('minun tarinani') dualista ulostulo: Minun tarinani
PITUUS ( teksti )	Palauttaa annetun merkkijonon pituuden	Valitse PITUUS ('guru99') dualista; ulostulo: 6
LPAD (teksti, pituus, pad_char)	Täyttää vasemmalla puolella olevan merkkijonon annetulla pituudella (koko merkkijono) annetulla merkillä	Valitse LPAD('guru99', 10, '$') dualista; ulostulo: $$$$guru99
RPAD (teksti, pituus, pad_char)	Täyttää oikealla puolella olevan merkkijonon annetulla pituudella (koko merkkijono) annetulla merkillä	Valitse RPAD('guru99',10,'-') dualista ulostulo: guru99--
LTRIM ( teksti )	Leikkaa tekstin alussa olevan valkoisen tilan	Valitse LTRIM(' Guru99') dualista; ulostulo: Guru99
RTRIM ( teksti )	Leikkaa tekstin lopussa olevan valkoisen tilan	Valitse RTRIM('Guru99') dualista; ulostulo; Guru99

Päivämäärätoiminnot

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

Toiminnon nimi	Käyttö	esimerkki
ADD_MONTHS (päivämäärä, kuukausien lukumäärä)	Lisää päivämäärään annetut kuukaudet	ADD_MONTH('2015-01-01',5); ulostulo: 05 / 01 / 2015
SYSDATE	Palauttaa palvelimen nykyisen päivämäärän ja kellonajan	Valitse SYSDATE dualista; ulostulo: 10 4:2015:2
TRUNK	Päivämäärämuuttujan pyöristys pienempään mahdolliseen arvoon	valitse sysdate, TRUNC(sysdate) dualista; ulostulo: 10 4:2015:2 12
ROUND	Pyöristää päivämäärän lähimpään ylä- tai alarajaan	Valitse sysdate, ROUND(sysdate) dualista ulostulo: 10 4:2015:2 14
MONTHS_BETWEEN	Palauttaa kahden päivämäärän välisten kuukausien määrän	Valitse MONTHS_BETWEEN (sysdate+60, sysdate) dualista ulostulo: 2

Yhteenveto

Tässä luvussa olemme oppineet seuraavaa.

• Proseduurin luominen ja eri tavat kutsua sitä
• Funktioiden luominen ja eri tapoja kutsua sitä
• Menettelyn ja funktion yhtäläisyydet ja erot
• Parametrit ja RETURN yleiset terminologiat PL/SQL-aliohjelmissa
• Yleisiä sisäänrakennettuja toimintoja Oracle PL / SQL