Pohranjeni postupak Oracle PL / SQL & Funkcije s primjerima

Sadržaj:

Anonim

U ovom vodiču vidjet ćete detaljan opis kako stvoriti i izvršiti imenovane blokove (postupke i funkcije).

Postupci i funkcije su potprogrami koji se mogu stvoriti i spremiti u bazu podataka kao objekti baze podataka. Mogu se pozvati ili uputiti i unutar ostalih blokova.

Osim toga, pokriti ćemo glavne razlike između ova dva potprograma. Također, razgovarat ćemo o ugrađenim funkcijama Oraclea.

U ovom vodiču za pohranjene postupke u Oracleu naučit ćete-

  • Terminologije u PL / SQL potprogramima
  • Što je postupak u PL / SQL-u?
  • Što je funkcija?
  • Sličnosti između postupka i funkcije
  • Postupak vs. Funkcija: Ključne razlike
  • Ugrađene funkcije u PL / SQL

Terminologije u PL / SQL potprogramima

Prije nego što naučimo o PL / SQL potprogramima, razgovarat ćemo o raznim terminologijama koje su dio tih potprograma. Ispod su terminologije o kojima ćemo razgovarati.

Parametar:

Parametar je varijabla ili rezervirano mjesto bilo kojeg valjanog PL / SQL tipa podataka putem kojeg PL / SQL potprogram razmjenjuje vrijednosti s glavnim kodom. Ovaj parametar omogućuje unos potprograma i izdvajanje iz tih podprograma.

  • Te parametre treba definirati zajedno s potprogramima u vrijeme izrade.
  • Ti su parametri uključeni u pozivni izraz ovih potprograma radi interakcije vrijednosti s potprogramima.
  • Tip podataka parametra u potprogramu i pozivna izjava trebali bi biti isti.
  • Veličina podatkovnog tipa ne bi se trebala spominjati u trenutku deklariranja parametra, jer je veličina dinamička za ovaj tip.

Na temelju njihove namjene parametri su klasificirani kao

  1. IN parametar
  2. OUT parametar
  3. IN OUT parametar

IN parametar:

  • Ovaj se parametar koristi za unos potprograma.
  • To je varijabla samo za čitanje unutar potprograma. Njihove se vrijednosti ne mogu mijenjati unutar potprograma.
  • U pozivajućoj izjavi ti parametri mogu biti varijabla ili doslovna vrijednost ili izraz, na primjer, to može biti aritmetički izraz poput '5 * 8' ili 'a / b', gdje su 'a' i 'b' varijable .
  • Prema zadanim postavkama parametri su IN tipa.

OUT parametar:

  • Ovaj se parametar koristi za dobivanje rezultata iz potprograma.
  • To je varijabla za čitanje i pisanje unutar potprograma. Njihove vrijednosti mogu se mijenjati unutar potprograma.
  • U naredbi za pozivanje ti bi parametri uvijek trebali biti varijabla koja sadrži vrijednost iz trenutnih potprograma.

IN OUT parametar:

  • Ovaj se parametar koristi i za davanje ulaznih podataka i za dobivanje rezultata iz potprograma.
  • To je varijabla za čitanje i pisanje unutar potprograma. Njihove vrijednosti mogu se mijenjati unutar potprograma.
  • U naredbi za pozivanje ovi parametri uvijek trebaju biti varijabla koja sadrži vrijednost iz potprograma.

Ti se parametri trebaju spomenuti u vrijeme izrade potprograma.

POVRATAK

RETURN je ključna riječ koja upućuje prevoditelju da prebaci kontrolu s potprograma na pozivni izraz. U potprogramu RETURN jednostavno znači da kontrola treba izaći iz potprograma. Jednom kada kontroler pronađe ključnu riječ RETURN u potprogramu, kod nakon toga preskočit će se.

Uobičajeno će nadređeni ili glavni blok pozvati potprograme, a zatim će se kontrola prebaciti s tih nadređenih blokova na pozvane potprograme. RETURN u potprogramu vratit će kontrolu natrag u njihov nadređeni blok. U slučaju funkcija naredba RETURN također vraća vrijednost. Tip podataka ove vrijednosti uvijek se spominje u vrijeme deklaracije funkcije. Tip podataka može biti bilo kojeg valjanog PL / SQL tipa podataka.

Što je postupak u PL / SQL-u?

Postupak u PL / SQL je potprogram jedinica koja se sastoji od skupine PL / SQL izraze koji se može nazvati po imenu. Svaka procedura u PL / SQL-u ima svoje jedinstveno ime pod kojim se može pozivati ​​i nazivati. Ova podprogramska jedinica u Oracle bazi podataka pohranjena je kao objekt baze podataka.

Napomena: Podprogram nije ništa drugo nego postupak i treba ga stvoriti ručno prema zahtjevu. Jednom stvorene pohranit će se kao objekti baze podataka.

Ispod su karakteristike jedinice potprograma Procedure u PL / SQL:

  • Postupci su samostalni blokovi programa koji se mogu pohraniti u bazu podataka.
  • Poziv na ove PLSQL procedure može se izvršiti pozivanjem na njihovo ime radi izvršavanja PL / SQL naredbi.
  • Uglavnom se koristi za izvršavanje procesa u PL / SQL-u.
  • Može imati ugniježđene blokove ili se može definirati i ugniježditi unutar ostalih blokova ili paketa.
  • Sadrži dio deklaracije (neobavezno), dio izvršenja, dio obrade iznimke (neobavezno).
  • Vrijednosti se mogu proslijediti u Oracle proceduru ili dohvatiti iz procedure kroz parametre.
  • Ti bi parametri trebali biti uključeni u izjavu o pozivu.
  • Postupak u SQL-u može imati izraz RETURN za vraćanje kontrole u pozivni blok, ali ne može vratiti nikakve vrijednosti kroz izraz RETURN.
  • Postupci se ne mogu pozivati ​​izravno iz SELECT izraza. Mogu se pozvati iz drugog bloka ili putem EXEC ključne riječi.

Sintaksa:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE nalaže prevoditelju da kreira novi postupak u Oracleu. Ključna riječ 'ILI ZAMJENA' nalaže kompajleru da zamijeni postojeći postupak (ako postoji) trenutnim.
  • Naziv postupka trebao bi biti jedinstven.
  • Ključna riječ 'IS' koristit će se kada se pohranjena procedura u Oracleu ugnijezdi u neke druge blokove. Ako je postupak samostalni, tada će se koristiti 'AS'. Osim ovog standarda kodiranja, oba imaju isto značenje.

Primjer1: Izrada postupka i njegovo pozivanje pomoću EXEC-a

U ovom primjeru stvorit ćemo Oracle proceduru koja uzima ime kao ulaz i ispisuje poruku dobrodošlice kao izlaz. Koristit ćemo naredbu EXEC za pozivanje postupka.

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

Objašnjenje koda:

  • Redak koda 1 : Stvaranje procedure s imenom 'welcome_msg' i s jednim parametrom 'p_name' tipa 'IN'.
  • Redak koda 4 : Ispis poruke dobrodošlice spajanjem imena unosa.
  • Postupak je uspješno sastavljen.
  • Redak koda 7 : Pozivanje postupka pomoću naredbe EXEC s parametrom 'Guru99'. Postupak se izvršava, a poruka se ispisuje kao "Dobrodošli Guru99".

Što je funkcija?

Functions je samostalni PL / SQL potprogram. Kao i PL / SQL procedura, funkcije imaju jedinstveni naziv pod kojim se mogu pozivati. Oni su pohranjeni kao PL / SQL objekti baze podataka. Ispod su neke od karakteristika funkcija.

  • Funkcije su samostalni blok koji se uglavnom koristi u svrhu izračuna.
  • Funkcija koristi RETURN ključnu riječ da bi vratila vrijednost, a vrsta podataka je definirana u trenutku izrade.
  • Funkcija treba ili vratiti vrijednost ili podići iznimku, tj. Povratak je obavezan u funkcijama.
  • Funkcija bez DML izraza može se izravno pozvati u SELECT upitu, dok se funkcija s DML operacijom može pozvati samo iz drugih PL / SQL blokova.
  • Može imati ugniježđene blokove ili se može definirati i ugniježditi unutar ostalih blokova ili paketa.
  • Sadrži dio deklaracije (neobavezno), dio izvršenja, dio obrade iznimke (neobavezno).
  • Vrijednosti se mogu proslijediti u funkciju ili dohvatiti iz procedure kroz parametre.
  • Ti bi parametri trebali biti uključeni u izjavu o pozivu.
  • PLSQL funkcija također može vratiti vrijednost kroz OUT parametre, osim korištenjem RETURN.
  • Budući da će uvijek vratiti vrijednost, u naredbi pozivanja uvijek se pridružuje operatoru dodjele za popunjavanje varijabli.

Sintaksa

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION nalaže prevoditelju da stvori novu funkciju. Ključna riječ 'ILI ZAMJENA' nalaže prevoditelju da postojeću funkciju (ako postoji) zamijeni trenutnom.
  • Naziv funkcije trebao bi biti jedinstven.
  • Treba spomenuti tip podataka POVRAT.
  • Ključna riječ 'IS' koristit će se kada je postupak ugniježđen u neke druge blokove. Ako je postupak samostalni, tada će se koristiti 'AS'. Osim ovog standarda kodiranja, oba imaju isto značenje.

Primjer1: Stvaranje funkcije i pozivanje pomoću Anonimnog bloka

U ovom ćemo programu stvoriti funkciju koja uzima ime kao ulaz i vraća poruku dobrodošlice kao izlaz. Upotrijebit ćemo anonimni blok i odabrati naredbu za pozivanje funkcije.

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;

Objašnjenje koda:

  • Linija 1 : Stvaranje Oracle funkcije s imenom 'welcome_msg_func' i s jednim parametrom 'p_name' tipa 'IN'.
  • Redak koda 2 : deklariranje tipa povrata kao VARCHAR2
  • Redak koda 5 : Vraćanje spojene vrijednosti 'Dobrodošli' i vrijednosti parametra.
  • Redak koda 8 : Anonimni blok za pozivanje gornje funkcije.
  • Redak koda 9 : Deklariranje varijable s tipom podataka istim kao i povratni tip podataka funkcije.
  • Redak koda 11 : Pozivanje funkcije i popunjavanje povratne vrijednosti u varijablu 'lv_msg'.
  • Redak koda 12 : Ispis vrijednosti varijable. Rezultat koji ćete ovdje dobiti je "Dobrodošli Guru99"
  • Redak koda 14 : Pozivanje iste funkcije putem naredbe SELECT. Povratna vrijednost usmjerena je izravno na standardni izlaz.

Sličnosti između postupka i funkcije

  • Oba se mogu pozvati iz drugih PL / SQL blokova.
  • Ako se izuzetak pokrenut u potprogramu ne obrađuje u odjeljku rukovanja izuzecima potprograma, tada će se proširiti na pozivni blok.
  • Obje mogu imati onoliko parametara koliko je potrebno.
  • Oboje se u PL / SQL tretiraju kao objekti baze podataka.

Postupak vs. Funkcija: Ključne razlike

Postupak Funkcija
  • Koristi se uglavnom za izvršavanje određenog postupka
  • Koristi se uglavnom za izvršavanje nekih izračuna
  • Ne može se pozvati u naredbi SELECT
  • Funkcija koja ne sadrži DML izraze može se pozvati u naredbi SELECT
  • Upotrijebite OUT parametar za vraćanje vrijednosti
  • Upotrijebite RETURN da biste vratili vrijednost
  • Vraćanje vrijednosti nije obvezno
  • Obvezna je vrijednost
  • RETURN će jednostavno izaći iz kontrole iz potprograma.
  • RETURN će izaći iz kontrole iz potprograma i također vraća vrijednost
  • Vrsta podataka za povratak neće biti navedena u trenutku izrade
  • Vrsta podataka za povratak obavezna je u trenutku stvaranja

Ugrađene funkcije u PL / SQL

PL / SQL sadrži razne ugrađene funkcije za rad sa nizovima i tipom podataka datuma. Ovdje ćemo vidjeti najčešće korištene funkcije i njihovu upotrebu.

Funkcije pretvorbe

Te se ugrađene funkcije koriste za pretvaranje jednog tipa podataka u drugi tip podataka.

Naziv funkcije Upotreba Primjer
TO_CHAR Pretvara drugi tip podataka u tip podataka znaka TO_CHAR (123);
TO_DATE (string, format) Pretvara zadani niz u datum. Niz mora odgovarati formatu. DO_DATUMA ('2015-JAN-15', 'GGGG-MON-DD'); Izlaz: 15.1.2015
TO_NUMBER (tekst, format) Pretvara tekst u brojčanu vrstu datog formata. Podatak '9' označava broj znamenki Odaberite TO_NUMBER ('1234', '9999') iz dual; Izlaz: 1234 Odaberite TO_NUMBER ('1.234,45', '9,999.99') iz dual; Izlaz: 1234

String funkcije

To su funkcije koje se koriste na tipu podataka znaka.

Naziv funkcije Upotreba Primjer
INSTR (tekst, niz, početak, pojavljivanje) Daje položaj određenog teksta u danom nizu.
  • tekst - Glavni niz
  • string - tekst koji treba pretražiti
  • start - početni položaj pretraživanja (nije obavezno)
  • suglasnost - pojava pretraživanog niza (nije obavezno)
 Odaberite INSTR ('ZRAKOPLOV', 'E', 2,1) iz dvostrukog izlaza : 2 Odaberite INSTR ('ZRAKOPLOV', 'E', 2,2) iz dvostrukog izlaza: 9 ( drugo pojavljivanje E)
SUBSTR (tekst, početak, duljina) Daje vrijednost podniza glavnog niza.
  • tekst - glavni niz
  • start - početni položaj
  • length - duljina koja se podniže
 odaberite substr ('avion', 1,7) iz dvostrukog izlaza : aeropla
GORNJI (tekst) Vraća veliko slovo navedenog teksta Odaberite gornji ('guru99') iz dual; Izlaz : GURU99
DONJE (tekst) Vraća mala slova navedenog teksta Odaberite donji ('avion') iz dvostrukog; Izlaz : avion
INITCAP (tekst) Vraća dani tekst s početnim slovom velikim slovom. Select ('guru99') from dual Output : Guru99 Select ('guru99') from dual Output : My Story
DUŽINA (tekst) Vraća duljinu zadanog niza Odaberite LENGTH ('guru99') iz dual; Izlaz : 6
LPAD (tekst, dužina, pad_char) Postavlja niz na lijevoj strani za zadanu duljinu (ukupni niz) s datim znakom Odaberite LPAD ('guru99', 10, '$') iz dual; Izlaz : $$$$ guru99
RPAD (tekst, dužina, pad_char) Postavlja niz s desne strane za zadanu duljinu (ukupni niz) s datim znakom Odaberite RPAD ('guru99', 10, '-') iz dvostrukog izlaza : guru99 ----
LTRIM (tekst) Izrezuje vodeći razmak iz teksta Odaberite LTRIM ('Guru99') iz dual; Izlaz : Guru99
RTRIM (tekst) Izrezuje zaostali razmak iz teksta Odaberite RTRIM ('Guru99') iz dual; Izlaz ; Guru99

Datumske funkcije

To su funkcije koje se koriste za manipulaciju datumima.

Naziv funkcije Upotreba Primjer
DODAJ_MJESECI (datum, broj mjeseci) Zadane mjesece dodaje datumu DODAJ_MJESEC ('01.01.2015', 5); Izlaz : 01.05.2015
SYSDATE Vraća trenutni datum i vrijeme poslužitelja Odaberite SYSDATE iz dual; Izlaz : 4.10.2015. 14:11:43
TRUNC Zaokružite datumsku varijablu na nižu moguću vrijednost odaberite sysdate, TRUNC (sysdate) iz dual; Izlaz : 4.10.2015. 14:12:39 04.10.2015
KRUG Zaokružuje datum na najbližu granicu, bilo veću ili manju Odaberite sysdate, ROUND (sysdate) iz dualnog izlaza : 4.10.2015. 14:14:34 05.10.2015.
MJESECI_MEĐU Vraća broj mjeseci između dva datuma Odaberite MONTHS_BETWEEN (sysdate + 60, sysdate) iz dvostrukog izlaza : 2

Sažetak

U ovom smo poglavlju naučili sljedeće.

  • Kako stvoriti proceduru i različite načine pozivanja
  • Kako stvoriti funkciju i različite načine pozivanja
  • Sličnosti i razlike između postupka i funkcije
  • Parametri i RETURN uobičajene terminologije u PL / SQL potprogramima
  • Uobičajene ugrađene funkcije u Oracle PL / SQL