Oracle PL/SQL lagret procedure og funktioner med eksempler

Af: Fiona Brown Fiona Brown
Hours Opdateret

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 at udtrække 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.

Hvad er 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 i PL/SQL har sit eget unikke navn, som den kan henvises til og kaldes med. Denne underprogramenhed i Oracle database gemmes som et databaseobjekt.

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’);

Kodeforklaring:

  • Kodelinje 1: Oprettelse af proceduren med navnet 'welcome_msg' og med en parameter 'p_name' af typen 'IN'.
  • Kodelinje 4: Udskrivning af velkomstbeskeden ved at sammenkæde inputnavnet.
  • Proceduren er kompileret med succes.
  • Kodelinje 7: Kalder proceduren ved hjælp af EXEC-kommandoen med parameteren 'Guru99'. Proceduren udføres, og beskeden udskrives som "Welcome Guru99".

RELATEREDE ARTIKLER

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.

Funktioner i PL/SQL

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.

Funktioner i PL/SQL

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;

Kodeforklaring:

  • Kodelinje 1: Oprettelse af Oracle funktion med navnet 'welcome_msg_func' og med én parameter 'p_name' af typen 'IN'.
  • Kodelinje 2: erklærer returtypen som VARCHAR2
  • Kodelinje 5: Returnerer den sammenkædede værdi 'Velkommen' og parameterværdien.
  • Kodelinje 8: Anonym blok for at kalde ovenstående funktion.
  • Kodelinje 9: Erklærer variablen med datatype samme som returneringsdatatypen for funktionen.
  • Kodelinje 11: Kalder funktionen og udfylder returværdien til variablen 'lv_msg'.
  • Kodelinje 12: Udskrivning af variabelværdien. Outputtet, du får her, er "Welcome Guru99"
  • Kodelinje 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 dual;

Produktion: Guru99
RTRIM ( tekst ) Trimmer det efterfølgende hvide mellemrum fra teksten Vælg RTRIM('Guru99 ') fra dual;

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

Du kan lide: