I denna handledning kommer du att se den detaljerade beskrivningen om hur du skapar och kö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 inuti de andra blocken.
Bortsett från detta kommer vi att täcka de stora skillnaderna mellan dessa två delprogram. Vi ska också diskutera de inbyggda Oracle-funktionerna.
I denna Oracle Stored Procedure-handledning lär du dig-
- Terminologier i PL / SQL-underprogram
- Vad är procedur i PL / SQL?
- Vad är funktion?
- Likheter mellan procedur och funktion
- Procedur Vs. Funktion: Viktiga skillnader
- Inbyggda funktioner i PL / SQL
Terminologier i PL / SQL-underprogram
Innan vi lär oss om PL / SQL-delprogram diskuterar vi de olika terminologierna som ingår i dessa delprogram. Nedan följer de terminologier som vi ska diskutera.
Parameter:
Parametern är variabel eller platshållare för valfri PL / SQL-datatyp genom vilken PL / SQL-underprogrammet utbyter värdena med huvudkoden. Denna parameter gör det möjligt att ge inmatning till underprogrammen och extrahera från dessa underprogram.
- Dessa parametrar bör definieras tillsammans med underprogrammen vid tidpunkten för skapandet.
- Dessa parametrar ingår i anropsuttalandet för dessa underprogram för att interagera värdena med underprogrammen.
- Datatypen för parametern i underprogrammet och anropssatsen ska vara densamma.
- Storleken på datatypen bör inte nämnas vid tidpunkten för parameterdeklarationen, eftersom storleken är dynamisk för den här typen.
Baserat på deras syfte klassificeras parametrar som
- IN Parameter
- OUT Parameter
- IN OUT Parameter
IN Parameter:
- Denna parameter används för att ge inmatning till underprogrammen.
- Det är en skrivskyddad variabel i 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-skriv-variabel i underprogrammen. Deras värden kan ändras i delprogrammen.
- I samtalsmeddelandet bör dessa parametrar alltid vara en variabel för att hålla värdet från de aktuella delprogrammen.
IN OUT Parameter:
- Denna parameter används både för att ge inmatning och för att få utmatning från underprogrammen.
- Det är en läs-skriv-variabel i underprogrammen. Deras värden kan ändras i delprogrammen.
- I samtalsmeddelandet bör dessa parametrar alltid vara en variabel för att hålla värdet från underprogrammen.
Dessa parametertyper bör nämnas när subprogrammen skapas.
LÄMNA TILLBAKA
RETURN är nyckelordet som instruerar kompilatorn att växla kontrollen från underprogrammet till samtalsuttrycket. I underprogram betyder RETURN helt enkelt att kontrollen måste avslutas från underprogrammet. När styrenheten har hittat RETURN-nyckelordet i underprogrammet hoppas koden efter detta.
Normalt kommer föräldrar eller huvudblock att anropa underprogrammen, och sedan kommer kontrollen att flyttas från det överordnade blocket till de kallade underprogrammen. RETURN i underprogrammet återställer kontrollen till sitt överordnade block. I fallet med funktioner returnerar RETURN-uttalandet också värdet. Datatypen för detta värde nämns alltid vid tidpunkten för funktionsdeklarationen. Datatypen kan ha valfri PL / SQL-datatyp.
Vad är procedur i PL / SQL?
En procedur i PL / SQL är en underprogramenhet som består av en grupp PL / SQL-satser som kan kallas med namn. Varje procedur i PL / SQL har sitt eget unika namn genom vilket det kan hänvisas till och anropas. Denna underprogramenhet i Oracle-databasen lagras som ett databasobjekt.
Obs: Underprogram är inget annat än ett förfarande, och det måste skapas manuellt enligt kravet. När de väl har skapats lagras de som databasobjekt.
Nedan följer egenskaperna för Procedure-underprogramenheten i PL / SQL:
- Procedurer är fristående block för ett program som kan lagras i databasen.
- Uppmaning till dessa PLSQL-procedurer kan göras genom att hänvisa till deras namn för att utföra PL / SQL-uttalanden.
- Den används främst för att utföra en process i PL / SQL.
- Det kan ha kapslade block, eller det kan definieras och kapslas inuti de andra blocken eller paketen.
- Den innehåller deklarationsdel (valfri), exekveringsdel, undantagshanteringsdel (valfri).
- Värdena kan skickas till Oracle-proceduren eller hämtas från proceduren genom parametrar.
- Dessa parametrar bör ingå i samtalsmeddelandet.
- 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 via RETURN-satsen.
- Procedurer kan inte anropas direkt från SELECT-uttalanden. De kan anropas från ett annat block eller via EXEC-nyckelord.
Syntax:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE instruerar kompilatorn att skapa en ny procedur i Oracle. Nyckelordet "ELLER BYT ut" instruerar kompileringen att ersätta den befintliga proceduren (om någon) med den aktuella.
- Procedurens namn 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. Bortsett från 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 inmatning och skriver ut välkomstmeddelandet som utdata. Vi kommer att använda EXEC-kommandot för att ringa proceduren.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Kodförklaring:
- Kodrad 1 : Skapa proceduren med namn 'welcome_msg' och med en parameter 'p_name' av 'IN' -typ.
- Kodrad 4 : Skriv ut välkomstmeddelandet genom att sammanfoga inmatningsnamnet.
- Proceduren sammanställs framgångsrikt.
- Kodrad 7 : Anropa proceduren med EXEC-kommandot med parametern 'Guru99'. Proceduren utförs och meddelandet skrivs ut som "Welcome Guru99".
Vad är funktion?
Funktioner är ett fristående PL / SQL-underprogram. Precis som PL / SQL-proceduren har funktioner ett unikt namn som det kan hänvisas till. Dessa lagras som PL / SQL-databasobjekt. Nedan följer några av funktionernas egenskaper.
- Funktioner är ett fristående block som huvudsakligen används för beräkningsändamål.
- Funktion använder RETURN-nyckelordet 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, det vill säga 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 det kan definieras och kapslas inuti de andra blocken eller paketen.
- Den innehåller deklarationsdel (valfri), exekveringsdel, undantagshanteringsdel (valfri).
- Värdena kan skickas till funktionen eller hämtas från proceduren genom parametrarna.
- Dessa parametrar bör ingå i samtalsmeddelandet.
- 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 följer det alltid med uppdragsoperatören att anropa variablerna i anropsuttalande.
Syntax
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION instruerar kompilatorn att skapa en ny funktion. Nyckelordet "ELLER BYT ut" instruerar kompilatorn att ersätta den befintliga funktionen (om någon) med den aktuella.
- 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. Bortsett från denna kodningsstandard har båda samma betydelse.
Exempel 1: Skapa funktion och anropa den med Anonymt block
I det här programmet ska vi skapa en funktion som tar namnet som inmatning och returnerar välkomstmeddelandet som utdata. Vi ska använda anonymt block och välj uttalande för att ringa funktionen.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_msg := welcome_msg_func (‘Guru99’);dbms_output.put_line(lv_msg);END;SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
Kodförklaring:
- Kodrad 1 : Skapa Oracle-funktionen med namnet 'welcome_msg_func' och med en parameter 'p_name' av 'IN' -typ.
- Kodrad 2 : deklarerar returtypen som VARCHAR2
- Kodrad 5 : Returnerar det sammanhängande värdet 'Välkommen' och parametervärdet.
- Kodrad 8 : Anonymt block för att anropa ovanstående funktion.
- Kodrad 9 : Förklarar variabeln med datatyp samma som returdatatypen för funktionen.
- Kodrad 11 : Anropa funktionen och fylla i returvärdet till variabeln 'lv_msg'.
- Kodrad 12 : Skriva ut variabelvärdet. Produktionen du får här är "Welcome Guru99"
- Kodrad 14 : Ringer samma funktion via SELECT-satsen. Returvärdet riktas direkt till standardutmatningen.
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 avsnittet för hantering av undantag för underprogram, sprids det till anropsblocket.
- Båda kan ha så många parametrar som krävs.
- Båda behandlas som databasobjekt i PL / SQL.
Procedur Vs. Funktion: Viktiga skillnader
| Procedur | Fungera |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Inbyggda funktioner i PL / SQL
PL / SQL innehåller olika inbyggda funktioner för att arbeta med strängar och datumdatatyp. Här kommer vi att se de vanliga funktionerna och deras användning.
Omvandlingsfunktioner
Dessa inbyggda funktioner används för att konvertera en datatyp till en annan datatyp.
| Funktionsnamn | Användande | Exempel |
|---|---|---|
| TO_CHAR | Konverterar den andra datatypen till teckendatatypen | TO_CHAR (123); |
| TO_DATE (sträng, format) | Konverterar den angivna strängen hittills. Strängen ska matcha formatet. | TO_DATE ('2015-JAN-15', 'YYYY-MON-DD'); Utgång: 2015-01-15 |
| TO_NUMBER (text, format) | Konverterar texten till nummertyp för det angivna formatet. Informat '9' anger antalet siffror | Välj TO_NUMBER ('1234', '9999') från dubbel; Utgång: 1234 Välj TO_NUMBER ('1,234,45', '9,999,99') från dubbel; Utgång: 1234 |
Strängfunktioner
Dessa är de funktioner som används på teckendatatypen.
| Funktionsnamn | Användande | Exempel |
|---|---|---|
| INSTR (text, sträng, start, förekomst) | Ger positionen för viss text i den angivna strängen.
| Välj INSTR ('AIRPLANE', 'E', 2,1) från dubbel utgång : 2 Välj INSTR ('AIRPLANE', 'E', 2,2) från dual output: 9 (2: a förekomst av E) |
| SUBSTR (text, start, längd) | Ger huvudsträngens substringvärde.
| välj substr ('flygplan', 1,7) från dubbel utgång : aeropla |
| ÖVRE (text) | Returnerar versalerna i den angivna texten | Välj övre ('guru99') från dubbel; Utgång : GURU99 |
| NEDRE (text) | Returnerar gemener i den angivna texten | Välj lägre ('flygplan') från dubbelt; Utgång : flygplan |
| INITCAP (text) | Returnerar den angivna texten med startbokstaven i versaler. | Välj ('guru99') från dubbel utgång : Guru99 Välj ('min historia') från dubbel utgång : min berättelse |
| LÄNGD (text) | Returnerar längden på den angivna strängen | Välj LÄNGD ('guru99') från dubbel; Utgång : 6 |
| LPAD (text, längd, pad_char) | Pads strängen i vänster sida för den angivna längden (total sträng) med den angivna karaktären | Välj LPAD ('guru99', 10, '$') från dual; Output : $$$$ guru99 |
| RPAD (text, längd, pad_char) | Pads strängen på höger sida för den angivna längden (total sträng) med den angivna karaktären | Välj RPAD ('guru99', 10, '-') från dubbel utgång : guru99 ---- |
| LTRIM (text) | Beskär det ledande vita utrymmet från texten | Välj LTRIM ('Guru99') från dubbel; Utgång : Guru99 |
| RTRIM (text) | Beskär det efterföljande vita utrymmet från texten | Välj RTRIM ('Guru99') från dubbel; Utgång ; Guru99 |
Datumfunktioner
Dessa är funktioner som används för att manipulera med datum.
| Funktionsnamn | Användande | Exempel |
|---|---|---|
| ADD_MONTHS (datum, antal månader) | Lägger till de angivna månaderna till datumet | ADD_MONTH ('2015-01-01', 5); Produktion : 2015-05-01 |
| SYSDATE | Returnerar aktuellt datum och tid för servern | Välj SYSDATE från dubbel; Utgång : 2015-10-04 14:11:43 |
| TRUNC | Omgång av datumvariabeln till lägre möjliga värde | välj sysdate, TRUNC (sysdate) från dual; Utgång : 2015-10-04 14:12:39 2015-10-04 |
| RUNDA | Avrundar datumet till närmaste gräns antingen högre eller lägre | Välj SYSDATE, ROUND (SYSDATE) från dubbla Output : 2015/10/04 02:14:34 2015/10/05 |
| MÅNADER_BETWEEN | Returnerar antalet månader mellan två datum | Välj MONTHS_BETWEEN (sysdate + 60, sysdate) från dubbel utgång : 2 |
Sammanfattning
I detta kapitel har vi lärt oss följande.
- Hur man skapar procedur och olika sätt att ringa det
- Hur man skapar funktion och olika sätt att ringa den
- Likheter och skillnader mellan procedur och funktion
- Parametrar och RETURN vanliga terminologier i PL / SQL-underprogram
- Vanliga inbyggda funktioner i Oracle PL / SQL