Oracle PL/SQL lagret procedure og funktioner med eksempler

Hvad er PL/SQL-underprogrammer?

I denne vejledning ser du en detaljeret beskrivelse af, hvordan du opretter og udfører de navngivne blokke, procedurer og funktioner.

Procedurer og funktioner er underprogrammer, der kan oprettes og gemmes i databasen som databaseobjekter. De kan også kaldes eller refereres til i andre blokke.

Vi dækker også de væsentligste forskelle mellem disse to underprogrammer og diskuterer Oracle indbyggede funktioner.

Terminologier i PL/SQL underprogrammer

Før vi lærer om PL/SQL-underprogrammer, diskuterer vi de forskellige terminologier, der er en del af disse underprogrammer.

Parameter

En parameter er en variabel eller pladsholder for enhver gyldig PL/SQL-datatype hvorigennem PL/SQL-underprogrammet udveksler værdier med hovedkoden. Denne parameter tillader input til underprogrammerne og f.eks.tracion af værdier fra dem.

• Disse parametre bør defineres sammen med underprogrammerne på oprettelsestidspunktet.
• De er inkluderet i kaldende sætning for at interagere med underprogrammerne.
• Datatypen for parameteren i underprogrammet og den kaldende sætning skal være den samme.
• Størrelsen på datatypen bør ikke nævnes ved parameterdeklarationen, da størrelsen er dynamisk.

Baseret på deres formål er parametrene klassificeret som:

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

IN parameter

• Bruges til at give input til underprogrammerne.
• Det er en skrivebeskyttet variabel i underprogrammerne; dens værdi kan ikke ændres i underprogrammet.
• I den kaldende sætning kan det være en variabel, en literal værdi eller et udtryk, f.eks. '5*8' eller 'a/b'.
• Som standard er parametre af typen IN.

OUT parameter

• Bruges til at hente output fra underprogrammer.
• Det er en læse-skrive-variabel i underprogrammerne; dens værdi kan ændres i dem.
• I kaldende sætningen skal det altid være en variabel, der indeholder værdien fra underprogrammet.

IN OUT parameter

• Bruges både til at give input og hente output fra underprogrammerne.
• Det er en læse-skrive-variabel i underprogrammerne; dens værdi kan ændres i dem.
• I kaldende sætningen skal det altid være en variabel, der indeholder værdien fra underprogrammet.

Parametertypen bør nævnes ved oprettelsen af ​​underprogrammerne.

RETURN

RETURN er nøgleordet, der instruerer compileren til at skifte kontrol fra underprogrammet til den kaldende sætning. I et underprogram betyder RETURN blot, at kontrollen skal afslutte underprogrammet; når controlleren finder RETURN, springes koden efter den over.

Normalt kalder den overordnede eller hovedblokken underprogrammerne, og kontrollen skifter fra den overordnede blokken til det kaldte underprogram. RETURN i underprogrammet returnerer kontrollen tilbage til den overordnede blokken. I tilfælde af funktioner returnerer RETURN-sætningen også en værdi, hvis datatype er nævnt på tidspunktet for funktionsdeklarationen.

Hvad er en procedure i PL/SQL?

A Procedure I PL/SQL er en underprogramenhed, der består af en gruppe PL/SQL-sætninger, der kan kaldes ved navn. Hver procedure har sit eget unikke navn og er gemt i Oracle databasen som et databaseobjekt.

Bemærk: Et underprogram er intet andet end en procedure, og det skal oprettes manuelt i henhold til kravet. Når det er oprettet, gemmes det som et databaseobjekt.

Karakteristikaene for en procedureunderprogramenhed i PL/SQL er:

• Procedurer er separate blokke, der kan gemmes i database.
• De kan kaldes ved deres navn for at udføre PL/SQL-sætningerne.
• De bruges primært til at udføre en proces.
• De kan have indlejrede blokke eller være indlejret i andre blokke eller pakker.
• De indeholder en deklarationsdel (valgfri), en udførelsesdel og en undtagelseshåndteringsdel (valgfri).
• Værdier kan overføres til eller hentes fra en procedure via parametre.
• Disse parametre bør indgå i den kaldende erklæring.
• En procedure kan have en RETURN-sætning til at returnere kontrollen til den kaldende blok, men den kan ikke returnere nogen værdi via RETURN.
• Procedurer kan ikke kaldes direkte fra SELECT-sætninger; de kan kaldes fra en anden blok eller via nøgleordet EXEC.

Syntaks

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

• CREATE PROCEDURE instruerer compileren i at oprette en ny procedure. Nøgleordet 'OR REPLACE' instruerer den i at erstatte den eksisterende procedure (hvis nogen) med den nuværende.
• Procedurenavnet skal være unikt.
• Nøgleordet 'IS' bruges, når den lagrede procedure er indlejret i en anden blok. Hvis proceduren er selvstændig, bruges 'AS'. Bortset fra denne kodningsstandard har begge den samme betydning.

Eksempel 1: Oprettelse af en procedure og kald af den ved hjælp af EXEC. I dette eksempel opretter vi en Oracle en procedure, der tager et navn som input og udskriver en velkomstbesked som output ved hjælp af EXEC-kommandoen til at kalde den.

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

Code Forklaring:

• Code linje 1: Opretter proceduren med navnet 'welcome_msg' og én parameter 'p_name' af typen 'IN'.
• Code linje 4: Udskriver velkomstbeskeden ved at sammenkæde inputnavnet.
• Proceduren er kompileret korrekt.
• Code linje 7: Kald af proceduren ved hjælp af EXEC med parameteren 'Guru99'. Proceduren udføres og udskriver "Velkommen Guru99. "

Hvad er en funktion?

En funktion er et selvstændigt PL/SQL-underprogram. Ligesom en procedure har en funktion et unikt navn og gemmes som et PL/SQL-databaseobjekt. Dens egenskaber er:

• Funktioner er selvstændige blokke, der primært bruges til beregninger.
• En funktion bruger RETURN-nøgleordet til at returnere en værdi, hvis datatype er defineret på oprettelsestidspunktet.
• En funktion skal enten returnere en værdi eller generere en undtagelse; return er obligatorisk i funktioner.
• En funktion uden DML-sætninger kan kaldes direkte i en SELECT-forespørgsel, hvorimod en funktion med DML kun kan kaldes fra andre PL/SQL-blokke.
• Den kan have indlejrede blokke eller være indlejret i andre blokke eller pakker.
• Den indeholder en deklarationsdel (valgfri), en udførelsesdel og en undtagelseshåndteringsdel (valgfri).
• Værdier kan sendes til eller hentes fra funktionen via parametre.
• Disse parametre bør indgå i den kaldende erklæring.
• En funktion kan også returnere en værdi via OUT-parametre ud over at bruge RETURN.
• Da den altid returnerer en værdi, bruger den kaldende sætning altid en tildelingsoperator til at udfylde en variabel.

Syntaks

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION instruerer compileren i at oprette en ny funktion. 'OR REPLACE' instruerer den i at erstatte den eksisterende funktion (hvis nogen) med den nuværende.
• Funktionsnavnet skal være unikt.
• Datatypen RETURN bør nævnes.
• Nøgleordet 'IS' bruges, når funktionen er indlejret i en anden blok. Hvis funktionen er selvstændig, bruges 'AS'.

Eksempel 1: Oprettelse af en funktion og kald af den ved hjælp af en anonym blok. I dette program opretter vi en funktion, der tager et navn som input og returnerer en velkomstbesked ved hjælp af en anonym blok og en SELECT-sætning til at kalde den.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
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;

Code Forklaring:

• Code linje 1: Opretter funktionen med navnet 'welcome_msg_func' og én parameter 'p_name' af typen 'IN'.
• Code linje 2: Deklarerer returtypen som VARCHAR2.
• Code linje 5: Returnerer den sammenkædede værdi 'Velkommen' og parameterværdien.
• Code linje 8: Anonym blok til at kalde ovenstående funktion.
• Code linje 9: Deklarering af variablen med samme datatype som funktionens returtype.
• Code linje 11: Kald af funktionen og indsæt returværdien i variablen 'lv_msg'.
• Code linje 12: Udskriver variabelværdien. Outputtet er "Velkommen Guru99. "
• Code linje 14: Kald af den samme funktion via en SELECT-sætning. Returværdien dirigeres til standardoutputtet.

Ligheder mellem en procedure og en funktion

• Begge kan kaldes fra andre PL/SQL-blokke.
• Hvis en undtagelse, der opstår i underprogrammet, ikke håndteres i dets undtagelseshåndtering sektion, forplanter den sig til den kaldende blokken.
• Begge kan have så mange parametre som nødvendigt.
• Begge behandles som databaseobjekter i PL/SQL.

Procedure vs. funktion: Nøgleforskelle

Indbyggede funktioner i PL/SQL

PL / SQL indeholder forskellige indbyggede funktioner til at arbejde med streng- og datodatatyper. Her ser vi de almindeligt anvendte funktioner og deres anvendelse.

Konverteringsfunktioner

Disse indbyggede funktioner konverterer én datatype til en anden.

Strengfunktioner

Disse funktioner bruges på tegndatatypen.

Dato funktioner

Disse funktioner bruges til at manipulere datoer.

Ofte Stillede Spørgsmål