Oracle PL/SQL opgeslagen procedure en functies met voorbeelden

Wat zijn PL/SQL-subprogramma's?

In deze handleiding vindt u een gedetailleerde beschrijving van hoe u benoemde blokken, procedures en functies kunt maken en uitvoeren.

Procedures en functies zijn subprogramma's die kunnen worden aangemaakt en opgeslagen in de database als databaseobjecten. Ze kunnen ook binnen andere blokken worden aangeroepen of waarnaar kan worden verwezen.

We behandelen ook de belangrijkste verschillen tussen deze twee subprogramma's en bespreken de Oracle ingebouwde functies.

Terminologieën in PL/SQL-subprogramma's

Voordat we meer leren over PL/SQL-subprogramma's, bespreken we eerst de verschillende terminologieën die bij deze subprogramma's horen.

Parameter

Een parameter is een variabele of plaatshouder van een geldige waarde. PL/SQL-gegevenstype waarmee het PL/SQL-subprogramma waarden uitwisselt met de hoofdcode. Deze parameter maakt invoer voor de subprogramma's mogelijk en zorgt voor uitvoer.trachet ontlenen van waarden aan hen.

• Deze parameters moeten samen met de subprogramma's worden gedefinieerd op het moment dat ze worden gemaakt.
• Ze worden in de aanroepinstructie opgenomen om met de subprogramma's te communiceren.
• Het gegevenstype van de parameter in het subprogramma en in de aanroepende instructie moet hetzelfde zijn.
• De grootte van het gegevenstype hoeft niet te worden vermeld bij de parameterdeclaratie, aangezien de grootte dynamisch is.

Op basis van hun doel worden parameters als volgt geclassificeerd:

1. IN-parameter
2. UIT-parameter
3. IN UIT-parameter

IN-parameter

• Wordt gebruikt om invoer te geven aan de subprogramma's.
• Het is een alleen-lezen variabele binnen de subprogramma's; de waarde ervan kan niet binnen het subprogramma worden gewijzigd.
• In de aanroepinstructie kan het een variabele, een letterlijke waarde of een expressie zijn, zoals '5*8' of 'a/b'.
• Standaard zijn de parameters van het type IN.

UIT-parameter

• Wordt gebruikt om uitvoer van de subprogramma's te verkrijgen.
• Het is een lees-schrijfvariabele binnen de subprogramma's; de waarde ervan kan daarin worden gewijzigd.
• In de aanroepinstructie moet het altijd een variabele zijn die de waarde van het subprogramma bevat.

IN UIT-parameter

• Wordt gebruikt voor zowel het geven van invoer als het ontvangen van uitvoer van de subprogramma's.
• Het is een lees-schrijfvariabele binnen de subprogramma's; de waarde ervan kan daarin worden gewijzigd.
• In de aanroepinstructie moet het altijd een variabele zijn die de waarde van het subprogramma bevat.

Het parametertype moet worden vermeld bij het aanmaken van de subprogramma's.

RETURN

RETURN is het sleutelwoord dat de compiler instrueert om de controle van het subprogramma over te dragen aan de aanroepende instructie. In een subprogramma betekent RETURN simpelweg dat de controle het subprogramma moet verlaten; zodra de controller RETURN tegenkomt, wordt de code die erop volgt overgeslagen.

Normaal gesproken roept het hoofdblok (of parent block) de subprogramma's aan, waarna de controle van het hoofdblok naar het aangeroepen subprogramma wordt overgedragen. De RETURN-instructie in het subprogramma geeft de controle terug aan het hoofdblok. In het geval van functies retourneert de RETURN-instructie ook een waarde, waarvan het gegevenstype is gespecificeerd bij de functiedeclaratie.

Wat is een procedure in PL/SQL?

A Procedure Een procedure in PL/SQL is een subprogramma-eenheid die bestaat uit een groep PL/SQL-instructies die op naam kunnen worden aangeroepen. Elke procedure heeft een eigen unieke naam en wordt opgeslagen in de code. Oracle database als databaseobject.

Let op: Een subprogramma is niets anders dan een procedure en moet handmatig worden aangemaakt, afhankelijk van de vereisten. Eenmaal aangemaakt, wordt het opgeslagen als een databaseobject.

De kenmerken van een procedure-subprogramma-eenheid in PL/SQL zijn:

• Procedures zijn op zichzelf staande blokken die kunnen worden opgeslagen in de databank.
• Ze kunnen bij naam worden aangeroepen om de PL/SQL-instructies uit te voeren.
• Ze worden voornamelijk gebruikt om een ​​proces uit te voeren.
• Ze kunnen geneste blokken bevatten, of zelf genest zijn binnen andere blokken of pakketten.
• Ze bevatten een declaratiegedeelte (optioneel), een uitvoeringsgedeelte en een gedeelte voor foutafhandeling (optioneel).
• Waarden kunnen via parameters aan een procedure worden doorgegeven of eruit worden opgehaald.
• Deze parameters moeten worden opgenomen in de aanroepende instructie.
• Een procedure kan een RETURN-instructie bevatten om de controle terug te geven aan het aanroepende blok, maar kan via RETURN geen waarde retourneren.
• Procedures kunnen niet rechtstreeks vanuit SELECT-instructies worden aangeroepen; ze kunnen worden aangeroepen vanuit een ander blok of via het trefwoord EXEC.

Syntaxis

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

• CREATE PROCEDURE instrueert de compiler om een ​​nieuwe procedure te creëren. Het trefwoord 'OR REPLACE' geeft de compiler de opdracht om de bestaande procedure (indien aanwezig) te vervangen door de huidige procedure.
• De procedurenaam moet uniek zijn.
• Het sleutelwoord 'IS' wordt gebruikt wanneer de opgeslagen procedure zich binnen een ander codeblok bevindt. Als de procedure op zichzelf staat, wordt 'AS' gebruikt. Afgezien van deze codestandaard hebben beide dezelfde betekenis.

Voorbeeld 1: Een procedure maken en deze aanroepen met EXEC. In dit voorbeeld maken we een Oracle Een procedure die een naam als invoer accepteert en een welkomstbericht als uitvoer afdrukt, waarbij de EXEC-opdracht wordt gebruikt om deze aan te roepen.

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 Uitleg:

• Code lijn 1: Het aanmaken van de procedure met de naam 'welcome_msg' en één parameter 'p_name' van het type 'IN'.
• Code lijn 4: Het welkomstbericht afdrukken door de ingevoerde naam eraan toe te voegen.
• De procedure is succesvol gecompileerd.
• Code lijn 7: De procedure aanroepen met EXEC met de parameter 'Guru99'. De procedure wordt uitgevoerd en print "Welkom". Guru99 ".

Wat is een functie?

Een functie is een zelfstandig PL/SQL-subprogramma. Net als een procedure heeft een functie een unieke naam en wordt deze opgeslagen als een PL/SQL-databaseobject. De kenmerken ervan zijn:

• Functies zijn op zichzelf staande blokken die voornamelijk voor berekeningen worden gebruikt.
• Een functie gebruikt het sleutelwoord RETURN om een ​​waarde terug te geven waarvan het gegevenstype is gedefinieerd op het moment van creatie.
• Een functie moet ofwel een waarde retourneren, ofwel een uitzondering genereren; het retourneren van een waarde is verplicht bij functies.
• Een functie zonder DML-instructies kan rechtstreeks in een SELECT-query worden aangeroepen, terwijl een functie met DML alleen vanuit andere PL/SQL-blokken kan worden aangeroepen.
• Het kan geneste blokken bevatten, of zelf genest zijn binnen andere blokken of pakketten.
• Het bevat een declaratiegedeelte (optioneel), een uitvoeringsgedeelte en een gedeelte voor foutafhandeling (optioneel).
• Waarden kunnen via parameters aan de functie worden doorgegeven of eruit worden opgehaald.
• Deze parameters moeten worden opgenomen in de aanroepende instructie.
• Een functie kan naast het gebruik van RETURN ook een waarde retourneren via OUT-parameters.
• Omdat de aanroepende instructie altijd een waarde retourneert, gebruikt deze altijd een toewijzingsoperator om een ​​variabele te vullen.

Syntaxis

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' instrueert de compiler om een ​​nieuwe functie te creëren. 'OR REPLACE' instrueert de compiler om de bestaande functie (indien aanwezig) te vervangen door de huidige functie.
• De functienaam moet uniek zijn.
• Het gegevenstype RETURN moet worden vermeld.
• Het sleutelwoord 'IS' wordt gebruikt wanneer de functie zich binnen een ander blok bevindt. Als de functie op zichzelf staat, wordt 'AS' gebruikt.

Voorbeeld 1: Een functie maken en deze aanroepen met behulp van een anoniem blok. In dit programma maken we een functie die een naam als invoer accepteert en een welkomstbericht retourneert. We gebruiken hiervoor een anoniem blok en een SELECT-instructie om de functie aan te roepen.

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 Uitleg:

• Code lijn 1: De functie met de naam 'welcome_msg_func' en één parameter 'p_name' van het type 'IN' aanmaken.
• Code lijn 2: Het retourtype wordt gedeclareerd als VARCHAR2.
• Code lijn 5: De gecombineerde waarde 'Welkom' en de parameterwaarde worden geretourneerd.
• Code lijn 8: Een anoniem blok om de bovenstaande functie aan te roepen.
• Code lijn 9: De variabele declareren met hetzelfde gegevenstype als het retourtype van de functie.
• Code lijn 11: De functie aanroepen en de retourwaarde in de variabele 'lv_msg' opslaan.
• Code lijn 12: De waarde van de variabele afdrukken. De uitvoer is "Welkom". Guru99 ".
• Code lijn 14: Dezelfde functie aanroepen via een SELECT-statement. De retourwaarde wordt naar de standaarduitvoer gestuurd.

Overeenkomsten tussen een procedure en een functie

• Beide kunnen vanuit andere PL/SQL-blokken worden aangeroepen.
• Als een uitzondering die in het subprogramma wordt gegenereerd niet wordt afgehandeld in het subprogramma zelf, dan wordt deze uitzondering afgehandeld. uitzonderingsafhandeling sectie, het wordt doorgegeven aan het aanroepende blok.
• Beide kunnen zoveel parameters hebben als nodig is.
• Beide worden behandeld als databaseobjecten in PL/SQL.

Procedure versus functie: belangrijke verschillen

Ingebouwde functies in PL/SQL

PL / SQL Bevat diverse ingebouwde functies voor het werken met tekenreeks- en datumgegevenstypen. Hieronder zien we de meest gebruikte functies en hun toepassing.

Conversiefuncties

Deze ingebouwde functies converteren het ene gegevenstype naar het andere.

String-functies

Deze functies worden gebruikt op het gegevenstype 'character'.

Datum Functies

Deze functies worden gebruikt voor het bewerken van datums.

Veelgestelde vragen