Oracle PL/SQL lagret procedure og funktioner med eksempler

I denne vejledning vil du se den detaljerede beskrivelse af, hvordan du opretter og udfører de navngivne blokke (procedurer og funktioner).

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

Bortset fra dette vil vi dække de store forskelle mellem disse to underprogrammer. Vi skal også diskutere Oracle indbyggede funktioner.

Terminologier i PL/SQL underprogrammer

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

Parameter

Parameteren er variabel eller pladsholder for enhver gyldig PL/SQL datatype hvorigennem PL/SQL-underprogrammet udveksler værdierne med hovedkoden. Denne parameter giver mulighed for at give input til underprogrammerne og til f.eks.tract fra disse underprogrammer.

• Disse parametre bør defineres sammen med underprogrammerne på oprettelsestidspunktet.
• Disse parametre er inkluderet i den kaldende sætning af disse underprogrammer for at interagere værdierne med underprogrammerne.
• Datatypen for parameteren i underprogrammet og den kaldende sætning skal være den samme.
• Størrelsen af ​​datatypen bør ikke nævnes på tidspunktet for parametererklæringen, da størrelsen er dynamisk for denne type.

Baseret på deres formål er parametre klassificeret som

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

IN parameter

• Denne parameter bruges til at give input til underprogrammerne.
• Det er en skrivebeskyttet variabel inde i underprogrammerne. Deres værdier kan ikke ændres inde i underprogrammet.
• I den kaldende sætning kan disse parametre være en variabel eller en bogstavelig værdi eller et udtryk, for eksempel kan det være det aritmetiske udtryk som '5*8' eller 'a/b', hvor 'a' og 'b' er variable .
• Som standard er parametrene af IN-typen.

OUT parameter

• Denne parameter bruges til at få output fra underprogrammerne.
• Det er en læse-skrive-variabel inde i underprogrammerne. Deres værdier kan ændres inde i underprogrammerne.
• I den kaldende sætning skal disse parametre altid være en variabel for at holde værdien fra de aktuelle underprogrammer.

IN OUT parameter

• Denne parameter bruges både til at give input og til at få output fra underprogrammerne.
• Det er en læse-skrive-variabel inde i underprogrammerne. Deres værdier kan ændres inde i underprogrammerne.
• I den kaldende sætning skal disse parametre altid være en variabel for at holde værdien fra underprogrammerne.

Disse parametertyper bør nævnes på tidspunktet for oprettelse af underprogrammerne.

RETURN

RETURN er nøgleordet, der instruerer compileren til at skifte styringen fra underprogrammet til den kaldende sætning. I underprogram RETURN betyder blot, at styringen skal forlade underprogrammet. Når controlleren finder RETURN nøgleordet i underprogrammet, vil koden efter dette blive sprunget over.

Normalt vil forældre- eller hovedblok kalde underprogrammerne, og derefter vil styringen skifte fra disse overordnede blok til de kaldede underprogrammer. RETURN i underprogrammet vil returnere styringen til deres overordnede blok. I tilfælde af funktioner returnerer RETURN-sætningen også værdien. Datatypen for denne værdi er altid nævnt på tidspunktet for funktionsdeklarationen. Datatypen kan være af enhver gyldig PL/SQL-datatype.

Bemærk: Underprogram er intet andet end en procedure, og det skal oprettes manuelt i henhold til kravet. Når de er oprettet, vil de blive gemt som databaseobjekter.

Nedenfor er karakteristikaene for Procedure underprogramenhed i PL/SQL:

• Procedurer er selvstændige blokke af et program, der kan gemmes i database.
• Kald til disse PLSQL-procedurer kan foretages ved at henvise til deres navn for at udføre PL/SQL-sætningerne.
• Det bruges hovedsageligt til at udføre en proces i PL/SQL.
• Det kan have indlejrede blokke, eller det kan defineres og indlejres inde i de andre blokke eller pakker.
• Den indeholder erklæringsdel (valgfrit), udførelsesdel, undtagelseshåndteringsdel (valgfri).
• Værdierne kan overføres til Oracle procedure eller hentes fra proceduren gennem parametre.
• Disse parametre bør indgå i den kaldende erklæring.
• En procedure i SQL kan have en RETURN-sætning til at returnere kontrolelementet til den kaldende blok, men den kan ikke returnere nogen værdier gennem RETURN-sætningen.
• Procedurer kan ikke kaldes direkte fra SELECT-sætninger. De kan kaldes fra en anden blok eller gennem EXEC nøgleord.

Syntaks

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

• CREATE PROCEDURE instruerer compileren til at oprette en ny procedure i Oracle. Nøgleord 'ELLER ERSTAT' instruerer kompileringen til at erstatte den eksisterende procedure (hvis nogen) med den nuværende.
• Procedurenavnet skal være unikt.
• Nøgleord 'IS' vil blive brugt, når den lagrede procedure i Oracle er indlejret i nogle andre blokke. Hvis proceduren er selvstændig, vil 'AS' blive brugt. Bortset fra denne kodningsstandard har begge samme betydning.

Eksempel 1: Oprettelse af procedure og kald ved hjælp af EXEC

I dette eksempel skal vi lave en Oracle procedure, der tager navnet som input og udskriver velkomstbeskeden som output. Vi vil bruge EXEC-kommandoen til at kalde procedure.

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 line 1: Oprettelse af proceduren med navnet 'welcome_msg' og med en parameter 'p_name' af typen 'IN'.
• Code line 4: Udskrivning af velkomstbeskeden ved at sammenkæde inputnavnet.
• Proceduren er kompileret med succes.
• Code line 7Kald af proceduren ved hjælp af EXEC-kommandoen med parameteren 'Guru99'. Proceduren udføres, og beskeden udskrives som "Velkommen Guru99. "

Hvad er funktion?

Funktioner er et selvstændigt PL/SQL-underprogram. Ligesom PL/SQL-proceduren har funktioner et unikt navn, som det kan henvises til. Disse gemmes som PL/SQL-databaseobjekter. Nedenfor er nogle af funktionernes karakteristika.

• Funktioner er en selvstændig blok, der hovedsageligt bruges til beregningsformål.
• Funktion brug RETURN nøgleord til at returnere værdien, og datatypen for dette er defineret på tidspunktet for oprettelsen.
• En funktion skal enten returnere en værdi eller hæve undtagelsen, dvs. returnering er obligatorisk i funktioner.
• Funktion uden DML-sætninger kan kaldes direkte i SELECT-forespørgsel, mens funktionen med DML-operation kun kan kaldes fra andre PL/SQL-blokke.
• Det kan have indlejrede blokke, eller det kan defineres og indlejres inde i de andre blokke eller pakker.
• Den indeholder erklæringsdel (valgfrit), udførelsesdel, undtagelseshåndteringsdel (valgfri).
• Værdierne kan overføres til funktionen eller hentes fra proceduren gennem parametrene.
• Disse parametre bør indgå i den kaldende erklæring.
• En PLSQL-funktion kan også returnere værdien gennem OUT-parametre ud over at bruge RETURN.
• Da det altid vil returnere værdien, ledsager det i kaldende sætning altid med tildelingsoperator for at udfylde variablerne.

Syntaks

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 instruerer compileren om at oprette en ny funktion. Nøgleord 'OR REPLACE' instruerer compileren til at erstatte den eksisterende funktion (hvis nogen) med den nuværende.
• Funktionsnavnet skal være unikt.
• RETURN datatype skal nævnes.
• Nøgleordet 'IS' vil blive brugt, når proceduren er indlejret i nogle andre blokke. Hvis proceduren er selvstændig, vil 'AS' blive brugt. Bortset fra denne kodningsstandard har begge samme betydning.

Eksempel 1: Oprettelse af funktion og kald den ved hjælp af anonym blok

I dette program skal vi oprette en funktion, der tager navnet som input og returnerer velkomstbeskeden som output. Vi kommer til at bruge anonym blok og vælg erklæring til at kalde funktionen.

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;

Code Forklaring:

• Code line 1: Oprettelse af Oracle funktion med navnet 'welcome_msg_func' og med én parameter 'p_name' af typen 'IN'.
• Code line 2: erklærer returtypen som VARCHAR2
• Code line 5: Returnerer den sammenkædede værdi 'Velkommen' og parameterværdien.
• Code line 8: Anonym blok for at kalde ovenstående funktion.
• Code line 9: Erklærer variablen med datatype samme som returneringsdatatypen for funktionen.
• Code line 11: Kalder funktionen og udfylder returværdien til variablen 'lv_msg'.
• Code line 12Udskriver variabelværdien. Outputtet du får her er "Velkommen Guru99 "
• Code line 14: Kalder den samme funktion gennem SELECT-sætning. Returværdien dirigeres direkte til standardudgangen.

Ligheder mellem procedure og funktion

• Begge kan kaldes fra andre PL/SQL-blokke.
• Hvis den i underprogrammet rejste undtagelse ikke håndteres i underprogrammet undtagelse håndtering sektionen, så forplanter den sig til den kaldende blok.
• Begge kan have så mange parametre som nødvendigt.
• Begge behandles som databaseobjekter i PL/SQL.

Procedure vs. Funktion: Nøgleforskelle

Procedure	Funktion
Bruges hovedsageligt til at udføre en bestemt proces	Bruges hovedsageligt til at udføre nogle beregninger
Kan ikke indkalde SELECT-sætning	En funktion, der ikke indeholder DML-sætninger, kan kaldes i SELECT-sætningen
Brug parameteren OUT for at returnere værdien	Brug RETURN for at returnere værdien
Det er ikke obligatorisk at returnere værdien	Det er obligatorisk at returnere værdien
RETURN forlader blot styringen fra underprogrammet.	RETURN forlader styringen fra underprogrammet og returnerer også værdien
Returdatatype vil ikke blive angivet på oprettelsestidspunktet	Returdatatype er obligatorisk på oprettelsestidspunktet

Indbyggede funktioner i PL/SQL

PL / SQL indeholder forskellige indbyggede funktioner til at arbejde med strenge og datodatatype. Her skal vi se de almindeligt anvendte funktioner og deres brug.

Konverteringsfunktioner

Disse indbyggede funktioner bruges til at konvertere en datatype til en anden datatype.

Funktionsnavn	Brug	Eksempel
TO_CHAR	Konverterer den anden datatype til karakterdatatype	TO_CHAR(123);
TO_DATE (streng, format)	Konverterer den givne streng til dato. Strengen skal matche formatet.	TO_DATE('2015-JAN-15', 'ÅÅÅÅ-MAN-DD'); Produktion: 1 / 15 / 2015
TO_NUMBER (tekst, format)	Konverterer teksten til taltype i det givne format. Informat '9' angiver antallet af cifre	Vælg TO_NUMBER('1234′,'9999') fra dobbelt; Produktion: 1234 Vælg TO_NUMBER('1,234.45′,'9,999.99') fra dobbelt; Produktion: 1234

Strengfunktioner

Det er de funktioner, der bruges på karakterdatatypen.

Funktionsnavn	Brug	Eksempel
INSTR(tekst; streng; start; forekomst)	Giver positionen af ​​bestemt tekst i den givne streng. tekst – Hovedstreng streng – tekst, der skal søges i start – startposition for søgningen (valgfrit) overensstemmelse – forekomst af den søgte streng (valgfrit)	Vælg INSTR('AEROPLANE','E',2,1) fra dual Produktion: 2 Vælg INSTR('AEROPLANE','E',2,2) fra dual Produktion: 9 (2nd forekomst af E)
SUBSTR (tekst, start, længde)	Giver understrengværdien for hovedstrengen. tekst – hovedstreng start – startposition længde – længde, der skal understrenges	vælg substr('flyvemaskine',1,7) fra dual Produktion: aeropla
ØVRE (tekst)	Returnerer det store bogstav i den angivne tekst	Vælg upper('guru99') fra dual; Produktion: GURU99
NEDRE ( tekst )	Returnerer små bogstaver i den angivne tekst	Vælg lavere ('AerOpLane') fra dobbelt; Produktion: flyvemaskine
INITCAP (tekst)	Returnerer den givne tekst med startbogstavet med stort bogstav.	Vælg ('guru99') fra dual Produktion: Guru99 Vælg ('min historie') fra dobbelt Produktion: Min historie
LÆNGDE (tekst)	Returnerer længden af ​​den givne streng	Vælg LENGTH ('guru99') fra dual; Produktion: 6
LPAD (tekst, længde, pad_char)	Padder strengen i venstre side for den givne længde (samlet streng) med det givne tegn	Vælg LPAD('guru99', 10, '$') fra dual; Produktion: $$$$guru99
RPAD (tekst, længde, pad_char)	Polster strengen i højre side for den givne længde (total streng) med det givne tegn	Vælg RPAD('guru99′,10,'-') fra dual Produktion: guru99—-
LTRIM ( tekst )	Trimmer det indledende hvide mellemrum fra teksten	Vælg LTRIM(' Guru99') fra dobbelt; Produktion: Guru99
RTRIM ( tekst )	Trimmer det efterfølgende hvide mellemrum fra teksten	Vælg RTRIM('Guru99') fra dobbelt; Produktion; Guru99

Dato funktioner

Dette er funktioner, der bruges til at manipulere med datoer.

Funktionsnavn	Brug	Eksempel
ADD_MONTHS (dato, antal måneder)	Tilføjer de givne måneder til datoen	ADD_MONTH('2015-01-01',5); Produktion: 05 / 01 / 2015
SYSDATE	Returnerer den aktuelle dato og klokkeslæt for serveren	Vælg SYSDATE fra dual; Produktion: 10/4/2015 2:11:43
BAGAGERUM	Afrund datovariablen til den lavere mulige værdi	vælg sysdate, TRUNC(sysdate) fra dual; Produktion: 10/4/2015 2:12:39 10/4/2015
ROUND	Afrunder datoen til nærmeste grænse, enten højere eller lavere	Vælg sysdate, ROUND(sysdate) fra dobbelt Produktion: 10/4/2015 2:14:34 10/5/2015
MONTHS_BETWEEN	Returnerer antallet af måneder mellem to datoer	Vælg MONTHS_BETWEEN (sysdate+60, sysdate) fra dobbelt Produktion: 2

Resumé

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

• Sådan opretter du Procedure og forskellige måder at kalde det på
• Sådan opretter du Funktion og forskellige måder at kalde det på
• Ligheder og forskelle mellem procedure og funktion
• Parametre og RETURN almindelige terminologier i PL/SQL underprogrammer
• Fælles indbyggede funktioner i Oracle PL / SQL