Oracle PL / SQL lagret prosedyre & Funksjoner med eksempler

Innholdsfortegnelse:

Anonim

I denne opplæringen skal du se den detaljerte beskrivelsen av hvordan du oppretter og utfører de nevnte blokkene (prosedyrer og funksjoner).

Prosedyrer og funksjoner er underprogrammene som kan opprettes og lagres i databasen som databaseobjekter. De kan også kalles eller henvises i de andre blokkene.

Bortsett fra dette, vil vi dekke de store forskjellene mellom disse to delprogrammene. Vi skal også diskutere de innebygde Oracle-funksjonene.

I denne Oracle Stored Procedure-opplæringen vil du lære-

  • Terminologier i PL / SQL-underprogrammer
  • Hva er prosedyre i PL / SQL?
  • Hva er funksjon?
  • Likheter mellom prosedyre og funksjon
  • Prosedyre vs. Funksjon: Viktige forskjeller
  • Innebygde funksjoner i PL / SQL

Terminologier i PL / SQL-underprogrammer

Før vi lærer om PL / SQL-underprogrammer, vil vi diskutere de forskjellige terminologiene som er del av disse underprogrammene. Nedenfor er terminologiene vi skal diskutere.

Parameter:

Parameteren er variabel eller plassholder for en hvilken som helst gyldig PL / SQL-datatype som PL / SQL-underprogrammet utveksler verdiene med hovedkoden gjennom. Denne parameteren gjør det mulig å gi input til underprogrammene og trekke ut fra disse underprogrammene.

  • Disse parametrene bør defineres sammen med underprogrammene på tidspunktet for opprettelsen.
  • Disse parametrene er inkludert i anropsuttalelsen til disse underprogrammene for å samhandle verdiene med underprogrammene.
  • Datatypen til parameteren i underprogrammet og anropsuttalelsen skal være den samme.
  • Størrelsen på datatypen skal ikke nevnes på tidspunktet for parameterdeklarasjonen, siden størrelsen er dynamisk for denne typen.

Basert på deres formål er parametere klassifisert som

  1. IN Parameter
  2. OUT Parameter
  3. IN UT Parameter

IN Parameter:

  • Denne parameteren brukes til å gi input til underprogrammene.
  • Det er en skrivebeskyttet variabel i underprogrammene. Verdiene deres kan ikke endres i underprogrammet.
  • I kallesetningen kan disse parametrene være en variabel eller en bokstavelig verdi eller et uttrykk, for eksempel kan det være det aritmetiske uttrykket som '5 * 8' eller 'a / b' der 'a' og 'b' er variabler .
  • Som standard er parameterne av IN-type.

OUT Parameter:

  • Denne parameteren brukes til å få utgang fra underprogrammene.
  • Det er en lese-skrive-variabel inne i underprogrammene. Verdiene deres kan endres i underprogrammene.
  • I anropsuttalelsen bør disse parametrene alltid være en variabel for å holde verdien fra gjeldende underprogrammer.

IN OUT Parameter:

  • Denne parameteren brukes både for å gi input og for å få output fra underprogrammene.
  • Det er en lese-skrive-variabel inne i underprogrammene. Verdiene deres kan endres i underprogrammene.
  • I anropsuttalelsen bør disse parametrene alltid være en variabel for å holde verdien fra underprogrammene.

Disse parametertypene bør nevnes når du oppretter delprogrammene.

KOMME TILBAKE

RETURN er nøkkelordet som instruerer kompilatoren om å bytte kontrollen fra underprogrammet til den kallende uttalelsen. I underprogram betyr RETURN ganske enkelt at kontrollen må gå ut av underprogrammet. Når kontrolleren finner RETURN-nøkkelordet i underprogrammet, blir koden etter dette hoppet over.

Normalt vil foreldre- eller hovedblokk kalle underprogrammene, og deretter vil kontrollen skifte fra de overordnede blokkeringen til de kallte underprogrammene. RETURN i underprogrammet vil returnere kontrollen til foreldreblokken. I tilfelle funksjoner returnerer RETURN-setningen også verdien. Datatypen for denne verdien nevnes alltid på tidspunktet for funksjonserklæringen. Datatypen kan være av en hvilken som helst gyldig PL / SQL-datatype.

Hva er prosedyre i PL / SQL?

En prosedyre i PL / SQL er en underprogramenhet som består av en gruppe PL / SQL-setninger som kan kalles med navn. Hver prosedyre i PL / SQL har sitt eget unike navn som det kan henvises til og kalles til. Denne underprogramenheten i Oracle-databasen er lagret som et databaseobjekt.

Merk: Underprogram er ikke annet enn en prosedyre, og det må opprettes manuelt i henhold til kravet. Når de er opprettet, lagres de som databaseobjekter.

Nedenfor er kjennetegnene til Procedure-underprogramenheten i PL / SQL:

  • Prosedyrer er frittstående blokker for et program som kan lagres i databasen.
  • Anrop til disse PLSQL-prosedyrene kan gjøres ved å referere til navnet deres for å utføre PL / SQL-setningene.
  • Den brukes hovedsakelig til å utføre en prosess i PL / SQL.
  • Den kan ha nestede blokker, eller den kan defineres og nestes i de andre blokkene eller pakkene.
  • Den inneholder erklæringsdel (valgfri), utførelsesdel, unntakshåndteringsdel (valgfri).
  • Verdiene kan overføres til Oracle-prosedyren eller hentes fra prosedyren gjennom parametere.
  • Disse parametrene bør inkluderes i kallet.
  • En prosedyre i SQL kan ha en RETURN-setning for å returnere kontrollen til anropsblokken, men den kan ikke returnere noen verdier gjennom RETURN-setningen.
  • Prosedyrer kan ikke kalles direkte fra SELECT-setninger. De kan ringes fra en annen blokk eller gjennom EXEC-nøkkelord.

Syntaks:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE instruerer kompilatoren om å opprette en ny prosedyre i Oracle. Søkeord 'ELLER ERSTATT' instruerer kompileringen om å erstatte den eksisterende prosedyren (hvis noen) med den nåværende.
  • Prosedyrens navn skal være unikt.
  • Nøkkelordet 'IS' vil bli brukt når den lagrede prosedyren i Oracle er nestet i noen andre blokker. Hvis prosedyren er frittstående, vil 'AS' brukes. Bortsett fra denne kodingsstandarden, har begge samme betydning.

Eksempel 1: Opprette prosedyre og ringe den ved hjelp av EXEC

I dette eksemplet skal vi lage en Oracle-prosedyre som tar navnet som input og skriver velkomstmeldingen ut. Vi skal bruke EXEC-kommandoen for å ringe prosedyren.

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

Kode Forklaring:

  • Kodelinje 1 : Opprette prosedyren med navnet 'welcome_msg' og med en parameter 'p_name' av 'IN' -typen.
  • Kodelinje 4 : Skrive ut velkomstmeldingen ved å sammenkoble inngangsnavnet.
  • Prosedyren er kompilert.
  • Kodelinje 7 : Anrope prosedyren ved hjelp av EXEC-kommandoen med parameteren 'Guru99'. Prosedyren utføres, og meldingen skrives ut som "Welcome Guru99".

Hva er funksjon?

Funksjoner er et frittstående PL / SQL-underprogram. I likhet med PL / SQL-prosedyren har funksjoner et unikt navn som det kan henvises til. Disse lagres som PL / SQL-databaseobjekter. Nedenfor er noen av funksjonene til funksjonene.

  • Funksjoner er en frittstående blokk som hovedsakelig brukes til beregningsformål.
  • Funksjon bruk RETURN-nøkkelordet for å returnere verdien, og datatypen for dette defineres på tidspunktet for opprettelsen.
  • En funksjon skal enten returnere en verdi eller heve unntaket, dvs. retur er obligatorisk i funksjoner.
  • Funksjon uten DML-setninger kan kalles direkte i SELECT-spørring, mens funksjonen med DML-operasjon bare kan kalles fra andre PL / SQL-blokker.
  • Den kan ha nestede blokker, eller den kan defineres og nestes i de andre blokkene eller pakkene.
  • Den inneholder erklæringsdel (valgfri), utførelsesdel, unntakshåndteringsdel (valgfri).
  • Verdiene kan overføres til funksjonen eller hentes fra prosedyren gjennom parametrene.
  • Disse parametrene bør inkluderes i kallet.
  • En PLSQL-funksjon kan også returnere verdien gjennom andre OUT-parametere enn å bruke RETURN.
  • Siden den alltid vil returnere verdien, følger den alltid med oppdragsoperatøren for å fylle ut variablene i anropsuttalelse.

Syntaks

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION instruerer kompilatoren om å opprette en ny funksjon. Nøkkelordet 'ELLER ERSTATT' instruerer kompilatoren om å erstatte den eksisterende funksjonen (hvis noen) med den nåværende.
  • Funksjonsnavnet skal være unikt.
  • RETURN datatype bør nevnes.
  • Nøkkelordet 'IS' vil bli brukt når prosedyren er nestet i noen andre blokker. Hvis prosedyren er frittstående, vil 'AS' brukes. Bortsett fra denne kodingsstandarden, har begge samme betydning.

Eksempel1: Opprette funksjon og ringe den ved hjelp av anonym blokk

I dette programmet skal vi lage en funksjon som tar navnet som input og returnerer velkomstmeldingen som output. Vi skal bruke anonym blokk og velg uttalelse for å ringe funksjonen.

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;

Kode Forklaring:

  • Kodelinje 1 : Opprette Oracle-funksjonen med navnet 'welcome_msg_func' og med en parameter 'p_name' av 'IN' -typen.
  • Kodelinje 2 : erklærer returtypen som VARCHAR2
  • Kodelinje 5 : Returnerer den sammenkoblede verdien 'Velkommen' og parameterverdien.
  • Kodelinje 8 : Anonym blokk for å ringe til funksjonen ovenfor.
  • Kodelinje 9 : Erklærer variabelen med datatype samme som returdatatypen til funksjonen.
  • Kodelinje 11 : Kaller funksjonen og fyller tilbake verdien til variabelen 'lv_msg'.
  • Kodelinje 12 : Skrive ut variabelverdien. Resultatet du får her er "Welcome Guru99"
  • Kodelinje 14 : Ringer den samme funksjonen gjennom SELECT-setningen. Returverdien rettes direkte til standardutgangen.

Likheter mellom prosedyre og funksjon

  • Begge kan kalles fra andre PL / SQL-blokker.
  • Hvis unntaket som er reist i underprogrammet ikke håndteres i delen for unntakshåndtering av underprogrammet, vil det spre seg til anropsblokken.
  • Begge kan ha så mange parametere som nødvendig.
  • Begge behandles som databaseobjekter i PL / SQL.

Prosedyre vs. Funksjon: Viktige forskjeller

Fremgangsmåte Funksjon
  • Brukes hovedsakelig til å utføre en viss prosess
  • Brukes hovedsakelig for å utføre noe beregning
  • Kan ikke ringe inn SELECT-setningen
  • En funksjon som ikke inneholder DML-setninger, kan kalles i SELECT-setningen
  • Bruk OUT-parameteren for å returnere verdien
  • Bruk RETURN for å returnere verdien
  • Det er ikke obligatorisk å returnere verdien
  • Det er obligatorisk å returnere verdien
  • RETURN vil ganske enkelt avslutte kontrollen fra underprogrammet.
  • RETURN vil avslutte kontrollen fra underprogrammet og returnerer også verdien
  • Returdatatype vil ikke bli spesifisert på tidspunktet for opprettelsen
  • Returdatatype er obligatorisk på tidspunktet for opprettelsen

Innebygde funksjoner i PL / SQL

PL / SQL inneholder forskjellige innebygde funksjoner for å jobbe med strenger og datatype. Her skal vi se de vanlige funksjonene og bruken av dem.

Konverteringsfunksjoner

Disse innebygde funksjonene brukes til å konvertere en datatype til en annen datatype.

Funksjonsnavn Bruk Eksempel
TO_CHAR Konverterer den andre datatypen til karakterdatatypen TO_CHAR (123);
TO_DATE (streng, format) Konverterer gitt streng til dags dato. Strengen skal samsvare med formatet. TO_DATE ('2015-JAN-15', 'ÅÅÅÅ-MÅN-DD'); Utgang: 15/1/2015
TO_NUMBER (tekst, format) Konverterer teksten til nummertype for det gitte formatet. Informat '9' angir antall sifre Velg TO_NUMBER ('1234', '9999') fra dual; Utgang: 1234 Velg TO_NUMBER ('1,234,45', '9,999,99') fra dual; Utgang: 1234

Strengfunksjoner

Dette er funksjonene som brukes på karakterdatatypen.

Funksjonsnavn Bruk Eksempel
INSTR (tekst, streng, start, forekomst) Gir posisjonen til bestemt tekst i den gitte strengen.
  • tekst - Hovedstreng
  • streng - tekst som må søkes
  • start - startposisjon for søket (valgfritt)
  • samsvar - forekomst av den søkte strengen (valgfritt)
 Velg INSTR ( 'AIRPLANE', 'E', 2,1) fra dual Utgang : 2 Velg INSTR ( 'AIRPLANE', 'E', 2,2) fra to utganger: 9 (2 nd forekomst av E)
SUBSTR (tekst, start, lengde) Gir understrengingsverdien til hovedstrengen.
  • tekst - hovedstreng
  • start - startposisjon
  • lengde - lengde som skal understrenges
 velg substr ('fly', 1,7) fra dual Output : aeropla
ØVRE (tekst) Returnerer store bokstaver for den oppgitte teksten Velg øvre ('guru99') fra dual; Utgang : GURU99
NEDRE (tekst) Returnerer små bokstaver i den angitte teksten Velg lavere ('fly') fra dobbelt; Utgang : fly
INITCAP (tekst) Returnerer gitt tekst med startbokstaven i store bokstaver. Velg ('guru99') fra dual Output : Guru99 Select ('my story') fra dual Output : My Story
LENGDE (tekst) Returnerer lengden på den gitte strengen Velg LENGDE ('guru99') fra dual; Utgang : 6
LPAD (tekst, lengde, pad_char) Pads strengen på venstre side for den angitte lengden (total streng) med det gitte tegnet Velg LPAD ('guru99', 10, '$') fra dual; Produksjon : $$$$ guru99
RPAD (tekst, lengde, pad_char) Pads strengen på høyre side for den angitte lengden (total streng) med det gitte tegnet Velg RPAD ('guru99', 10, '-') fra dual Output : guru99 ----
LTRIM (tekst) Beskjærer det ledende hvite rommet fra teksten Velg LTRIM ('Guru99') fra dual; Utgang : Guru99
RTRIM (tekst) Beskjærer det etterfølgende hvite rommet fra teksten Velg RTRIM ('Guru99') fra dual; Utgang ; Guru99

Datofunksjoner

Dette er funksjoner som brukes til å manipulere med datoer.

Funksjonsnavn Bruk Eksempel
ADD_MONTHS (dato, antall måneder) Legger til de gitte månedene til datoen ADD_MONTH ('2015-01-01', 5); Utgang : 05/01/2015
SYSDATE Returnerer gjeldende dato og klokkeslett for serveren Velg SYSDATE fra dual; Utgang : 10.10.2015 14:11:43
TRUNC Runde av datovariabelen til den laveste mulige verdien velg sysdate, TRUNC (sysdate) fra dual; Utgang : 04.10.2015 14:12:39 PM 04.10.2015
RUND Avrunder datoen til nærmeste grense, enten høyere eller lavere Velg sysdate, RUND (sysdate) fra dobbel Utgang : 10.10.2015 14:14:34 PM 10/5/2015
MÅNEDER_BETWEEN Returnerer antall måneder mellom to datoer Velg MONTHS_BETWEEN (sysdate + 60, sysdate) fra dobbel utgang : 2

Sammendrag

I dette kapittelet har vi lært følgende.

  • Hvordan lage prosedyre og forskjellige måter å kalle det på
  • Hvordan lage funksjon og forskjellige måter å ringe den på
  • Likheter og forskjeller mellom prosedyre og funksjon
  • Parametere og RETURN vanlige terminologier i PL / SQL-underprogrammer
  • Vanlige innebygde funksjoner i Oracle PL / SQL