Oracle PL/SQL lagrad procedur och funktioner med exempel

By: Fiona Brown Fiona Brown
Hours Uppdaterad

I den här handledningen kommer du att se den detaljerade beskrivningen av hur du skapar och utför de namngivna blocken (procedurer och funktioner).

Procedurer och funktioner är de underprogram som kan skapas och sparas i databasen som databasobjekt. De kan också kallas eller hänvisas till de andra blocken.

Bortsett från detta kommer vi att täcka de stora skillnaderna mellan dessa två underprogram. Vi kommer också att diskutera Oracle inbyggda funktioner.

Terminologier i PL/SQL-underprogram

Innan vi lär oss om PL/SQL-underprogram kommer vi att diskutera de olika terminologierna som är en del av dessa underprogram. Nedan är de terminologier som vi kommer att diskutera.

Parameter

Parametern är variabel eller platshållare för någon giltig PL/SQL-datatyp genom vilket PL/SQL-underprogrammet utbyter värdena med huvudkoden. Denna parameter tillåter att ge input till underprogrammen och att extrahera från dessa underprogram.

  • Dessa parametrar bör definieras tillsammans med underprogrammen vid tidpunkten för skapandet.
  • Dessa parametrar ingår i anropssatsen för dessa underprogram för att interagera värdena med underprogrammen.
  • Datatypen för parametern i underprogrammet och den anropande satsen bör vara samma.
  • Storleken på datatypen bör inte nämnas vid tidpunkten för parameterdeklaration, eftersom storleken är dynamisk för denna typ.

Baserat på deras syfte klassificeras parametrar som

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

IN-parameter

  • Denna parameter används för att ge input till underprogrammen.
  • Det är en skrivskyddad variabel inuti underprogrammen. Deras värden kan inte ändras i underprogrammet.
  • I anropssatsen kan dessa parametrar vara en variabel eller ett bokstavligt värde eller ett uttryck, till exempel kan det vara det aritmetiska uttrycket som '5*8' eller 'a/b' där 'a' och 'b' är variabler .
  • Som standard är parametrarna av IN-typ.

OUT-parameter

  • Denna parameter används för att få utdata från underprogrammen.
  • Det är en läs-skrivvariabel inuti underprogrammen. Deras värden kan ändras i underprogrammen.
  • I anropssatsen ska dessa parametrar alltid vara en variabel för att hålla värdet från de aktuella underprogrammen.

IN OUT-parameter

  • Denna parameter används både för att ge input och för att få utdata från underprogrammen.
  • Det är en läs-skrivvariabel inuti underprogrammen. Deras värden kan ändras i underprogrammen.
  • I anropssatsen ska dessa parametrar alltid vara en variabel för att hålla värdet från underprogrammen.

Dessa parametertyper bör nämnas när underprogrammen skapas.

ÅNGERRÄTT & RETURER

RETURN är nyckelordet som instruerar kompilatorn att byta kontrollen från underprogrammet till den anropande satsen. I underprogrammet RETURN betyder helt enkelt att styrningen måste lämna underprogrammet. När styrenheten hittar nyckelordet RETURN i underprogrammet kommer koden efter detta att hoppas över.

Normalt kommer föräldra- eller huvudblocket att anropa underprogrammen, och sedan kommer kontrollen att flyttas från dessa föräldrablock till de anropade underprogrammen. RETURN i underprogrammet kommer att återföra kontrollen till sitt moderblock. I fallet med funktioner returnerar RETURN-satsen också värdet. Datatypen för detta värde nämns alltid vid funktionsdeklarationen. Datatypen kan vara av vilken giltig PL/SQL-datatyp som helst.

Vad är procedur i PL/SQL?

A Tillvägagångssätt i PL/SQL är en underprogramenhet som består av en grupp PL/SQL-satser som kan anropas med namn. Varje procedur i PL/SQL har sitt eget unika namn som det kan refereras till och anropas med. Denna underprogramenhet i Oracle databasen lagras som ett databasobjekt.

Obs: Underprogram är inget annat än en procedur, och det måste skapas manuellt enligt kravet. När de väl har skapats kommer de att lagras som databasobjekt.

Nedan är egenskaperna för Procedure-underprogramenheten i PL/SQL:

  • Procedurer är fristående block av ett program som kan lagras i databas.
  • Anrop till dessa PLSQL-procedurer kan göras genom att hänvisa till deras namn, för att exekvera PL/SQL-satserna.
  • Det används främst för att köra en process i PL/SQL.
  • Det kan ha kapslade block, eller så kan det definieras och kapslas inuti de andra blocken eller paketen.
  • Den innehåller deklarationsdel (valfritt), exekveringsdel, undantagshanteringsdel (valfritt).
  • Värdena kan överföras till Oracle procedur eller hämtas från proceduren genom parametrar.
  • Dessa parametrar bör inkluderas i det anropande uttalandet.
  • En procedur i SQL kan ha en RETURN-sats för att returnera kontrollen till det anropande blocket, men den kan inte returnera några värden genom RETURN-satsen.
  • 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>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE instruerar kompilatorn att skapa en ny procedur i Oracle. Nyckelordet 'ELLER ERSÄTT' instruerar kompileringen att ersätta den befintliga proceduren (om någon) med den nuvarande.
  • Procedurnamnet ska vara unikt.
  • Nyckelordet 'IS' kommer att användas när den lagrade proceduren i Oracle är kapslad i några andra block. Om proceduren är fristående kommer 'AS' att användas. Förutom denna kodningsstandard har båda samma betydelse.

Exempel 1: Skapa procedur och anropa den med EXEC

I det här exemplet ska vi skapa en Oracle procedur som tar namnet som indata och skriver ut välkomstmeddelandet som utdata. Vi kommer att använda EXEC-kommandot för att anropa proceduren.

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

Kodförklaring:

  • Kodrad 1: Skapar proceduren med namnet 'welcome_msg' och med en parameter 'p_name' av typen 'IN'.
  • Kodrad 4: Skriver ut välkomstmeddelandet genom att sammanfoga ingångsnamnet.
  • Proceduren har kompilerats framgångsrikt.
  • Kodrad 7: Anropar proceduren med EXEC-kommandot med parametern 'Guru99'. Proceduren utförs och meddelandet skrivs ut som "Welcome Guru99".

RELATERADE ARTIKLAR

Vad är funktion?

Functions är ett fristående PL/SQL-underprogram. Liksom PL/SQL-proceduren har funktioner ett unikt namn som det kan hänvisas till. Dessa lagras som PL/SQL-databasobjekt. Nedan är några av funktionernas egenskaper.

  • Funktioner är ett fristående block som huvudsakligen används för beräkningsändamål.
  • Funktion använd RETURN nyckelord för att returnera värdet, och datatypen för detta definieras vid tidpunkten för skapandet.
  • En funktion ska antingen returnera ett värde eller höja undantaget, dvs retur är obligatoriskt i funktioner.
  • Funktion utan DML-satser kan anropas direkt i SELECT-frågan medan funktionen med DML-operation endast kan anropas från andra PL/SQL-block.
  • Det kan ha kapslade block, eller så kan det definieras och kapslas inuti de andra blocken eller paketen.
  • Den innehåller deklarationsdel (valfritt), exekveringsdel, undantagshanteringsdel (valfritt).
  • Värdena kan skickas in i funktionen eller hämtas från proceduren genom parametrarna.
  • Dessa parametrar bör inkluderas i det anropande uttalandet.
  • En PLSQL-funktion kan också returnera värdet genom andra OUT-parametrar än att använda RETURN.
  • Eftersom det alltid kommer att returnera värdet, i calling-satsen följer det alltid med tilldelningsoperatören för att fylla i variablerna.

Funktioner i PL/SQL

syntax

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 instruerar kompilatorn att skapa en ny funktion. Nyckelordet 'ELLER ERSÄTT' instruerar kompilatorn att ersätta den befintliga funktionen (om någon) med den nuvarande.
  • Funktionsnamnet ska vara unikt.
  • RETURN datatyp bör nämnas.
  • Nyckelordet 'IS' kommer att användas när proceduren är kapslad i några andra block. Om proceduren är fristående kommer 'AS' att användas. Förutom denna kodningsstandard har båda samma betydelse.

Exempel 1: Skapa funktion och anropa den med hjälp av anonymt block

I det här programmet kommer vi att skapa en funktion som tar namnet som indata och returnerar välkomstmeddelandet som utdata. Vi kommer att använda anonymt block och välj uttalande för att anropa 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;

Kodförklaring:

  • Kodrad 1: Skapar Oracle funktion med namnet 'welcome_msg_func' och med en parameter 'p_name' av typen 'IN'.
  • Kodrad 2: deklarerar returtypen som VARCHAR2
  • Kodrad 5: Returnerar det sammanlänkade värdet 'Välkommen' och parametervärdet.
  • Kodrad 8: Anonymt block för att anropa ovanstående funktion.
  • Kodrad 9: Deklarerar variabeln med datatyp samma som returdatatypen för funktionen.
  • Kodrad 11: Anropar funktionen och fyller i returvärdet till variabeln 'lv_msg'.
  • Kodrad 12: Skriver ut variabelvärdet. Resultatet du får här är "Welcome Guru99"
  • Kodrad 14: Anropar samma funktion genom SELECT-satsen. Returvärdet riktas direkt till standardutgången.

Likheter mellan procedur och funktion

  • Båda kan anropas från andra PL/SQL-block.
  • Om undantaget som tas upp i underprogrammet inte hanteras i underprogrammet undantagshantering sektionen, kommer den att spridas till anropsblocket.
  • Båda kan ha så många parametrar som krävs.
  • Båda behandlas som databasobjekt i PL/SQL.

Procedur vs. Funktion: Nyckelskillnader

Tillvägagångssätt Funktion
Används främst för att utföra en viss process Används främst för att utföra vissa beräkningar
Kan inte anropa SELECT-satsen En funktion som inte innehåller några DML-satser kan anropas i SELECT-satsen
Använd parametern OUT för att returnera värdet Använd RETURN för att returnera värdet
Det är inte obligatoriskt att returnera värdet Det är obligatoriskt att returnera värdet
RETURN kommer helt enkelt att lämna kontrollen från underprogrammet. RETURN lämnar kontrollen från underprogrammet och returnerar även värdet
Returdatatyp kommer inte att anges vid skapandet Returdatatyp är obligatorisk vid skapandet

Inbyggda funktioner i PL/SQL

PL / SQL innehåller olika inbyggda funktioner för att arbeta med strängar och datumdatatyp. Här ska vi se de vanligaste funktionerna och deras användning.

Konverteringsfunktioner

Dessa inbyggda funktioner används för att konvertera en datatyp till en annan datatyp.

Funktionsnamn Användning Exempelvis
TO_CHAR Konverterar den andra datatypen till teckendatatyp TO_CHAR(123);
TO_DATE ( sträng, format ) Konverterar den givna strängen till datum. Strängen ska matcha formatet.

TO_DATE('2015-JAN-15', 'ÅÅÅÅ-MÅN-DD');

Produktion: 1 / 15 / 2015
TO_NUMBER (text, format)

Konverterar texten till nummertyp för det givna formatet.

Informat '9' anger antalet siffror

 Välj TO_NUMBER('1234′,'9999') från dubbel;

Produktion: 1234

Välj TO_NUMBER('1,234.45′,'9,999.99') från dubbel;

Produktion: 1234

Strängfunktioner

Det här är funktionerna som används på teckendatatypen.

Funktionsnamn Användning Exempelvis
INSTR(text; sträng; start; förekomst) Ger positionen för en viss text i den givna strängen.

  • text – Huvudsträng
  • sträng – text som behöver sökas
  • start – startposition för sökningen (valfritt)
  • överensstämmelse – förekomsten av den sökta strängen (valfritt)
 Välj INSTR('AEROPLANE','E',2,1) från dubbel

Produktion: 2

Välj INSTR('AEROPLANE','E',2,2) från dubbel

Produktion: 9 (2nd förekomst av E)
SUBSTR (text, start, längd) Ger delsträngens värde för huvudsträngen.

  • text – huvudsträng
  • start – startposition
  • längd – längd som ska understrängas
 välj substr('flygplan',1,7) från dubbel

Produktion: aeropla
ÖVRE ( text ) Returnerar versaler i den angivna texten Välj upper('guru99') från dual;

Produktion: GURU99
LÄGRE ( text ) Returnerar gemener i den angivna texten Välj lägre ('AerOpLane') från dubbel;

Produktion: flygplan
INITCAP ( text) Returnerar den givna texten med startbokstaven i versaler. Välj ('guru99') från dubbel

Produktion: Guru99

Välj ('min berättelse') från dubbel

Produktion: Min berättelse
LENGTH ( text ) Returnerar längden på den givna strängen Välj LENGTH ('guru99') från dubbel;

Produktion: 6
LPAD (text, längd, pad_char) Vadderar strängen på vänster sida för den givna längden (totalsträngen) med det givna tecknet Välj LPAD('guru99', 10, '$') från dual;

Produktion: $$$$guru99
RPAD (text, längd, pad_char) Vadderar strängen på höger sida för den givna längden (totalsträngen) med det givna tecknet Välj RPAD('guru99′,10,'-') från dubbel

Produktion: guru99—-
LTRIM ( text ) Beskär det inledande vita utrymmet från texten Välj LTRIM(' Guru99') från dubbel;

Produktion: Guru99
RTRIM ( text ) Beskär det efterföljande vita utrymmet från texten Välj RTRIM('Guru99 ') från dubbel;

Produktion; Guru99

Datumfunktioner

Det här är funktioner som används för att manipulera med datum.

Funktionsnamn Användning Exempelvis
ADD_MONTHS (datum, antal månader) Lägger till de givna månaderna till datumet ADD_MONTH('2015-01-01',5);

Produktion: 05 / 01 / 2015
SYSDATE Returnerar aktuellt datum och tid för servern Välj SYSDATE från dubbel;

Produktion: 10/4/2015 2:11:43
TRUNK Avrunda datumvariabeln till det lägre möjliga värdet välj sysdate, TRUNC(sysdate) från dubbel;

Produktion: 10/4/2015 2:12:39 10/4/2015
RUNT Avrundar datumet till närmaste gräns, antingen högre eller lägre Välj sysdate, ROUND(sysdate) från dubbel

Produktion: 10/4/2015 2:14:34 10/5/2015
MONTHS_BETWEEN Returnerar antalet månader mellan två datum Välj MONTHS_BETWEEN (sysdate+60, sysdate) från dubbel

Produktion: 2

Sammanfattning

I det här kapitlet har vi lärt oss följande.

  • Hur man skapar Procedur och olika sätt att kalla det
  • Hur man skapar Funktion och olika sätt att kalla det
  • Likheter och skillnader mellan procedur och funktion
  • Parametrar och RETURN vanliga terminologier i PL/SQL-underprogram
  • Vanliga inbyggda funktioner i Oracle PL / SQL

Du kanske gillar: