Oracle PL/SQL lagrad procedur och funktioner med exempel

Vad är PL/SQL-underprogram?

I den här handledningen ser du en detaljerad beskrivning av hur du skapar och kör namngivna block, procedurer och funktioner.

Procedurer och funktioner är delprogram som kan skapas och sparas i databasen som databasobjekt. De kan anropas eller refereras till även inuti andra block.

Vi tar också upp de viktigaste skillnaderna mellan dessa två delprogram och diskuterar Oracle inbyggda funktioner.

Terminologier i PL/SQL-underprogram

Innan vi lär oss om PL/SQL-underprogram diskuterar vi de olika terminologierna som ingår i dessa underprogram.

Parameter

En parameter är en variabel eller platshållare för vilken giltig variabel som helst PL/SQL-datatyp genom vilket PL/SQL-underprogrammet utbyter värden med huvudkoden. Denna parameter tillåter inmatning till underprogrammen och t.ex.tracion av värden från dem.

• Dessa parametrar bör definieras tillsammans med underprogrammen vid tidpunkten för skapandet.
• De ingår i anropssatsen för att interagera med delprogrammen.
• Datatypen för parametern i delprogrammet och den anropande satsen ska vara densamma.
• Storleken på datatypen bör inte nämnas vid parameterdeklarationen, eftersom storleken är dynamisk.

Baserat på deras syfte klassificeras parametrar som:

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

IN-parameter

• Används för att ge inmatning till underprogrammen.
• Det är en skrivskyddad variabel i underprogrammen; dess värde kan inte ändras i underprogrammet.
• I den anropande satsen kan det vara en variabel, ett literalvärde eller ett uttryck, till exempel '5*8' eller 'a/b'.
• Som standard är parametrar av typen IN.

OUT-parameter

• Används för att hämta utdata från underprogrammen.
• Det är en läs-skriv-variabel inuti underprogrammen; dess värde kan ändras inuti dem.
• I den anropande satsen ska det alltid vara en variabel som håller värdet från delprogrammet.

IN OUT-parameter

• Används både för att ge indata och hämta utdata från underprogrammen.
• Det är en läs-skriv-variabel inuti underprogrammen; dess värde kan ändras inuti dem.
• I den anropande satsen ska det alltid vara en variabel som håller värdet från delprogrammet.

Parametertypen bör anges när underprogrammen skapas.

ÅNGERRÄTT & RETURER

RETURN är nyckelordet som instruerar kompilatorn att växla kontroll från delprogrammet till den anropande satsen. I ett delprogram betyder RETURN helt enkelt att kontrollen måste avsluta delprogrammet; när kontrollenheten hittar RETURN hoppas koden efter det över.

Normalt anropar förälder- eller huvudblocket delprogrammen, och kontrollen flyttas från förälderblocket till det anropade delprogrammet. RETURN i delprogrammet returnerar kontrollen tillbaka till förälderblocket. När det gäller funktioner returnerar RETURN-satsen också ett värde vars datatyp nämns vid funktionsdeklarationen.

Vad är en procedur i PL/SQL?

A Tillvägagångssätt I PL/SQL är en delprogramenhet som består av en grupp PL/SQL-satser som kan anropas med namn. Varje procedur har sitt eget unika namn och lagras i Oracle databas som ett databasobjekt.

Obs: Ett delprogram är inget annat än en procedur, och det måste skapas manuellt enligt kraven. När det väl har skapats lagras det som ett databasobjekt.

Egenskaperna för en procedurunderprogramenhet i PL/SQL är:

• Procedurer är fristående block som kan lagras i databas.
• De kan anropas vid sitt namn för att exekvera PL/SQL-satser.
• De används huvudsakligen för att genomföra en process.
• De kan ha kapslade block, eller vara kapslade inuti andra block eller paket.
• De innehåller en deklarationsdel (valfritt), en exekveringsdel och en undantagshanteringsdel (valfritt).
• Värden kan skickas till eller hämtas från en procedur via parametrar.
• Dessa parametrar bör inkluderas i det anropande uttalandet.
• En procedur kan ha en RETURN-sats för att återföra kontrollen till det anropande blocket, men den kan inte returnera något värde via RETURN.
• Procedurer kan inte anropas direkt från SELECT-satser; de kan anropas från ett annat block eller via nyckelordet EXEC.

syntax

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

• CREATE PROCEDURE instruerar kompilatorn att skapa en ny procedur. Nyckelordet 'OR REPLACE' instruerar den att ersätta den befintliga proceduren (om någon) med den nuvarande.
• Procedurnamnet ska vara unikt.
• Nyckelordet 'IS' används när den lagrade proceduren är kapslad i ett annat block. Om proceduren är fristående används 'AS'. Bortsett från denna kodningsstandard har båda samma betydelse.

Exempel 1: Skapa en procedur och anropa den med EXEC. I det här exemplet skapar vi en Oracle en procedur som tar ett namn som indata och skriver ut ett välkomstmeddelande som utdata, med hjälp av EXEC-kommandot för att anropa 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 Förklaring:

• Code rad 1: Skapar proceduren med namnet 'welcome_msg' och parametern 'p_name' av typen 'IN'.
• Code rad 4: Skriver ut välkomstmeddelandet genom att sammanfoga inmatningsnamnet.
• Proceduren har kompilerats.
• Code rad 7: Anropa proceduren med EXEC med parametern 'Guru99'. Proceduren körs och skriver ut "Välkommen Guru99. "

Vad är en funktion?

En funktion är ett fristående PL/SQL-underprogram. Precis som en procedur har en funktion ett unikt namn och lagras som ett PL/SQL-databasobjekt. Dess egenskaper är:

• Funktioner är fristående block som huvudsakligen används för beräkningar.
• En funktion använder RETURN-nyckelordet för att returnera ett värde vars datatyp definierades vid skapandet.
• En funktion ska antingen returnera ett värde eller generera ett undantag; retur är obligatorisk i funktioner.
• En funktion utan DML-satser kan anropas direkt i en SELECT-fråga, medan en funktion med DML bara kan anropas från andra PL/SQL-block.
• Den kan ha kapslade block, eller vara kapslad inuti andra block eller paket.
• Den innehåller en deklarationsdel (valfritt), en exekveringsdel och en undantagshanteringsdel (valfritt).
• Värden kan skickas till eller hämtas från funktionen via parametrar.
• Dessa parametrar bör inkluderas i det anropande uttalandet.
• En funktion kan också returnera ett värde via OUT-parametrar utöver att använda RETURN.
• Eftersom den alltid returnerar ett värde använder den anropande kommandot alltid en tilldelningsoperator för att fylla i en variabel.

syntax

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 instruerar kompilatorn att skapa en ny funktion. 'OR REPLACE' instruerar den att ersätta den befintliga funktionen (om någon finns) med den aktuella.
• Funktionsnamnet ska vara unikt.
• Datatypen RETURN bör nämnas.
• Nyckelordet 'IS' används när funktionen är kapslad i ett annat block. Om funktionen är fristående används 'AS'.

Exempel 1: Skapa en funktion och anropa den med ett anonymt block. I det här programmet skapar vi en funktion som tar ett namn som indata och returnerar ett välkomstmeddelande, med hjälp av ett anonymt block och en SELECT-sats för att anropa 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 Förklaring:

• Code rad 1: Skapar funktionen med namnet 'welcome_msg_func' och parametern 'p_name' av typen 'IN'.
• Code rad 2: Deklarerar returtypen som VARCHAR2.
• Code rad 5: Returnerar det sammanfogade värdet 'Välkommen' och parametervärdet.
• Code rad 8: Anonymt block för att anropa ovanstående funktion.
• Code rad 9: Deklarera variabeln med samma datatyp som funktionens returtyp.
• Code rad 11: Anropa funktionen och fylla i returvärdet i variabeln 'lv_msg'.
• Code rad 12: Skriver ut variabelvärdet. Utdata är "Välkommen Guru99. "
• Code rad 14: Anropar samma funktion via en SELECT-sats. Returvärdet dirigeras till standardutdata.

Likheter mellan en procedur och en funktion

• Båda kan anropas från andra PL/SQL-block.
• Om ett undantag som uppstår i delprogrammet inte hanteras i dess undantagshantering sektionen, propagerar den till det anropande blocket.
• Båda kan ha så många parametrar som krävs.
• Båda behandlas som databasobjekt i PL/SQL.

Procedur kontra funktion: Viktiga skillnader

Inbyggda funktioner i PL/SQL

PL / SQL innehåller olika inbyggda funktioner för att arbeta med sträng- och datumdatatyper. Här ser vi de vanligaste funktionerna och deras användning.

Konverteringsfunktioner

Dessa inbyggda funktioner konverterar en datatyp till en annan.

Strängfunktioner

Dessa funktioner används på teckendatatypen.

Datumfunktioner

Dessa funktioner används för att manipulera datum.

Vanliga frågor