Oracle PL/SQL opgeslagen procedure en functies met voorbeelden

Door: Fiona Bruin Fiona Bruin
Hours Bijgewerkt

In deze zelfstudie krijgt u een gedetailleerde beschrijving te zien van hoe u de genoemde blokken (procedures en functies) kunt maken en uitvoeren.

Procedures en functies zijn de subprogramma's die als databaseobjecten in de database kunnen worden gemaakt en opgeslagen. Ze kunnen ook binnen de andere blokken worden gebeld of doorverwezen.

Daarnaast bespreken we de belangrijkste verschillen tussen deze twee subprogramma's. Daarnaast gaan we het hebben over de Oracle ingebouwde functies.

Terminologieën in PL/SQL-subprogramma's

Voordat we meer leren over PL/SQL-subprogramma's, zullen we de verschillende terminologieën bespreken die deel uitmaken van deze subprogramma's. Hieronder staan ​​de terminologieën die we gaan bespreken.

Parameter

De parameter is een variabele of een tijdelijke aanduiding voor een geldige parameter PL/SQL-gegevenstype waarmee het PL/SQL-subprogramma de waarden uitwisselt met de hoofdcode. Met deze parameter kunt u invoer geven aan de subprogramma's en daaruit extraheren.

  • Deze parameters moeten samen met de subprogramma's worden gedefinieerd op het moment dat ze worden gemaakt.
  • Deze parameters zijn opgenomen in de aanroepinstructie van deze subprogramma's om de waarden met de subprogramma's te laten communiceren.
  • Het datatype van de parameter in het subprogramma en de aanroepende instructie moeten hetzelfde zijn.
  • De grootte van het datatype mag niet worden vermeld op het moment dat de parameter wordt gedeclareerd, omdat de grootte voor dit type dynamisch is.

Op basis van hun doel worden parameters geclassificeerd als

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

IN-parameter

  • Deze parameter wordt gebruikt voor het invoeren van de subprogramma's.
  • Het is een alleen-lezen variabele binnen de subprogramma's. Hun waarden kunnen binnen het subprogramma niet worden gewijzigd.
  • In de aanroepende instructie kunnen deze parameters een variabele of een letterlijke waarde of een uitdrukking zijn. Het kan bijvoorbeeld een rekenkundige uitdrukking zijn zoals '5*8' of 'a/b' waarbij 'a' en 'b' variabelen zijn. .
  • Standaard zijn de parameters van het IN-type.

UIT-parameter

  • Deze parameter wordt gebruikt voor het verkrijgen van uitvoer van de subprogramma's.
  • Het is een lees-schrijfvariabele binnen de subprogramma's. Hun waarden kunnen binnen de subprogramma's worden gewijzigd.
  • In de aanroepende instructie moeten deze parameters altijd een variabele zijn om de waarde van de huidige subprogramma's vast te houden.

IN UIT-parameter

  • Deze parameter wordt gebruikt voor zowel het geven van invoer als het verkrijgen van uitvoer van de subprogramma's.
  • Het is een lees-schrijfvariabele binnen de subprogramma's. Hun waarden kunnen binnen de subprogramma's worden gewijzigd.
  • In de aanroepende instructie moeten deze parameters altijd een variabele zijn om de waarde uit de subprogramma's vast te houden.

Dit parametertype moet worden vermeld bij het maken van de subprogramma's.

RETURN

RETURN is het sleutelwoord dat de compiler instrueert om de besturing van het subprogramma naar de aanroepende instructie over te schakelen. In een subprogramma betekent RETURN eenvoudigweg dat de besturing het subprogramma moet verlaten. Zodra de controller het trefwoord RETURN in het subprogramma vindt, wordt de code daarna overgeslagen.

Normaal gesproken roept het bovenliggende of hoofdblok de subprogramma's aan, en dan verschuift de besturing van die bovenliggende blokken naar de aangeroepen subprogramma's. RETURN in het subprogramma brengt de besturing terug naar hun bovenliggende blok. In het geval van functies retourneert de RETURN-instructie ook de waarde. Het datatype van deze waarde wordt altijd genoemd op het moment van de functiedeclaratie. Het datatype kan van elk geldig PL/SQL-datatype zijn.

Wat is procedure in PL/SQL?

A Procedure in PL/SQL is een subprogramma-eenheid die bestaat uit een groep PL/SQL-instructies die op naam kunnen worden aangeroepen. Elke procedure in PL/SQL heeft zijn eigen unieke naam waarmee ernaar kan worden verwezen en deze kan worden aangeroepen. Deze subprogramma-eenheid in de Oracle database wordt opgeslagen als een databaseobject.

Opmerking: Subprogramma is niets anders dan een procedure en moet volgens de vereisten handmatig worden gemaakt. Eenmaal gemaakt, worden ze opgeslagen als databaseobjecten.

Hieronder staan ​​de kenmerken van de Procedure-subprogramma-eenheid in PL/SQL:

  • Procedures zijn op zichzelf staande blokken van een programma die kunnen worden opgeslagen in het databank.
  • U kunt deze PLSQL-procedures aanroepen door naar hun naam te verwijzen, om de PL/SQL-instructies uit te voeren.
  • Het wordt voornamelijk gebruikt om een ​​proces in PL/SQL uit te voeren.
  • Het kan geneste blokken bevatten, maar het kan ook gedefinieerd en genest zijn binnen de andere blokken of pakketten.
  • Het bevat een aangiftegedeelte (optioneel), een uitvoeringsgedeelte en een gedeelte voor het afhandelen van uitzonderingen (optioneel).
  • De waarden kunnen worden doorgegeven Oracle procedure of opgehaald uit de procedure via parameters.
  • Deze parameters moeten worden opgenomen in de aanroepende instructie.
  • Een procedure in SQL kan een RETURN-instructie hebben om de besturing terug te sturen naar het aanroepende blok, maar kan geen waarden retourneren via de RETURN-instructie.
  • Procedures kunnen niet rechtstreeks vanuit SELECT-instructies worden aangeroepen. Ze kunnen worden aangeroepen vanuit een ander blok of via het EXEC-sleutelwoord.

Syntaxis

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE instrueert de compiler om een ​​nieuwe procedure aan te maken Oracle. Het trefwoord 'OR REPLACE' instrueert de compiler om de bestaande procedure (indien aanwezig) te vervangen door de huidige.
  • De procedurenaam moet uniek zijn.
  • Het trefwoord 'IS' zal worden gebruikt wanneer de opgeslagen procedure wordt ingevoerd Oracle is genest in een aantal andere blokken. Als de procedure op zichzelf staat, wordt 'AS' gebruikt. Afgezien van deze coderingsstandaard hebben beide dezelfde betekenis.

Voorbeeld 1: Procedure maken en deze aanroepen met EXEC

In dit voorbeeld gaan we een Oracle procedure die de naam als invoer neemt en het welkomstbericht als uitvoer afdrukt. We gaan de EXEC-opdracht gebruiken om de procedure 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 Verklaring:

  • Coderegel 1: De procedure aanmaken met de naam 'welcome_msg' en met één parameter 'p_name' van het type 'IN'.
  • Coderegel 4: het welkomstbericht afdrukken door de ingevoerde naam samen te voegen.
  • De procedure is succesvol gecompileerd.
  • Coderegel 7: De procedure aanroepen met behulp van het EXEC-commando met de parameter 'Guru99'. De procedure wordt uitgevoerd en het bericht wordt afgedrukt als “Welcome Guru99”.

GERELATEERDE ARTIKELEN

Wat is functie?

Functions is een zelfstandig PL/SQL-subprogramma. Net als de PL/SQL-procedure hebben functies een unieke naam waarmee ernaar kan worden verwezen. Deze worden opgeslagen als PL/SQL-databaseobjecten. Hieronder staan ​​enkele kenmerken van functies.

  • Functies zijn een op zichzelf staand blok dat voornamelijk wordt gebruikt voor berekeningsdoeleinden.
  • Functie gebruikt het trefwoord RETURN om de waarde te retourneren, en het gegevenstype hiervan wordt gedefinieerd op het moment van creatie.
  • Een functie moet een waarde retourneren of de uitzondering verhogen, dat wil zeggen dat return verplicht is in functies.
  • Functies zonder DML-instructies kunnen rechtstreeks worden aangeroepen in een SELECT-query, terwijl functies met een DML-bewerking alleen kunnen worden aangeroepen vanuit andere PL/SQL-blokken.
  • Het kan geneste blokken bevatten, maar het kan ook gedefinieerd en genest zijn binnen de andere blokken of pakketten.
  • Het bevat een aangiftegedeelte (optioneel), een uitvoeringsgedeelte en een gedeelte voor het afhandelen van uitzonderingen (optioneel).
  • De waarden kunnen worden doorgegeven aan de functie of via de parameters uit de procedure worden opgehaald.
  • Deze parameters moeten worden opgenomen in de aanroepende instructie.
  • Een PLSQL-functie kan de waarde ook retourneren via OUT-parameters, behalve via RETURN.
  • Omdat de waarde altijd wordt geretourneerd, wordt bij het aanroepen van een statement altijd een toewijzingsoperator gebruikt om de variabelen in te vullen.

Functies in PL/SQL

Syntaxis

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 instrueert de compiler om een ​​nieuwe functie te maken. Het trefwoord 'OR REPLACE' instrueert de compiler om de bestaande functie (indien aanwezig) te vervangen door de huidige.
  • De functienaam moet uniek zijn.
  • RETURN-gegevenstype moet worden vermeld.
  • Het trefwoord 'IS' zal worden gebruikt wanneer de procedure in een aantal andere blokken is genest. Als de procedure op zichzelf staat, wordt 'AS' gebruikt. Afgezien van deze coderingsstandaard hebben beide dezelfde betekenis.

Voorbeeld 1: Functie maken en deze aanroepen met Anonymous Block

In dit programma gaan we een functie maken die de naam als invoer gebruikt en het welkomstbericht als uitvoer retourneert. We gaan een anonieme blok- en selectie-instructie gebruiken om de functie aan te roepen.

Functies in 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;

Code Verklaring:

  • Coderegel 1: Het creëren van de Oracle functie met naam 'welcome_msg_func' en met één parameter 'p_name' van het type 'IN'.
  • Coderegel 2: declareert het retourtype als VARCHAR2
  • Coderegel 5: Retourneert de samengevoegde waarde 'Welkom' en de parameterwaarde.
  • Coderegel 8: Anoniem blok om de bovenstaande functie aan te roepen.
  • Coderegel 9: De variabele declareren met hetzelfde datatype als het retourdatatype van de functie.
  • Coderegel 11: Roep de functie aan en vul de retourwaarde in voor de variabele 'lv_msg'.
  • Coderegel 12: Afdrukken van de variabelewaarde. De uitvoer die u hier krijgt is "Welcome Guru99"
  • Coderegel 14: Dezelfde functie aanroepen via de SELECT-instructie. De retourwaarde wordt rechtstreeks naar de standaarduitvoer gestuurd.

Overeenkomsten tussen procedure en functie

  • Beide kunnen vanuit andere PL/SQL-blokken worden aangeroepen.
  • Als de in het subprogramma gegenereerde uitzondering niet in het subprogramma wordt afgehandeld afhandeling van uitzonderingen sectie, dan zal het zich voortplanten naar het aanroepende blok.
  • Beide kunnen zoveel parameters hebben als nodig is.
  • Beide worden behandeld als databaseobjecten in PL/SQL.

Procedure versus. Functie: Belangrijkste verschillen

Procedure Functie
Wordt voornamelijk gebruikt om een ​​bepaald proces uit te voeren Wordt voornamelijk gebruikt om berekeningen uit te voeren
Kan SELECT-instructie niet oproepen Een functie die geen DML-instructies bevat, kan worden aangeroepen in de SELECT-instructie
Gebruik de OUT-parameter om de waarde te retourneren Gebruik RETURN om de waarde terug te geven
Het is niet verplicht om de waarde terug te geven Het is verplicht om de waarde terug te geven
RETURN verlaat eenvoudigweg de besturing van het subprogramma. RETURN verlaat de besturing van het subprogramma en retourneert ook de waarde
Het retourgegevenstype wordt niet gespecificeerd op het moment van creatie Het retourgegevenstype is verplicht op het moment van creatie

Ingebouwde functies in PL/SQL

PL / SQL bevat verschillende ingebouwde functies om met tekenreeksen en datumgegevenstype te werken. Hier gaan we de veelgebruikte functies en hun gebruik bekijken.

Conversiefuncties

Deze ingebouwde functies worden gebruikt om het ene datatype naar een ander datatype te converteren.

Functie Naam Gebruik Voorbeeld
TO_CHAR Converteert het andere gegevenstype naar tekengegevenstype TO_CHAR(123);
TO_DATE (tekenreeks, formaat) Converteert de gegeven string naar datum. De tekenreeks moet overeenkomen met het formaat.

TO_DATE('2015-JAN-15', 'JJJJ-MON-DD');

uitgang: 1 / 15 / 2015
TO_NUMBER (tekst, indeling)

Converteert de tekst naar een getaltype met het opgegeven formaat.

Informat '9' geeft het aantal cijfers aan

 Selecteer TO_NUMBER('1234′,'9999') uit dubbel;

uitgang: 1234

Selecteer TO_NUMBER('1,234.45′,'9,999.99') uit dubbel;

uitgang: 1234

String-functies

Dit zijn de functies die worden gebruikt op het karakterdatatype.

Functie Naam Gebruik Voorbeeld
INSTR(tekst; tekenreeks; begin; voorkomen) Geeft de positie van een bepaalde tekst in de gegeven string.

  • tekst – Hoofdreeks
  • string – tekst die moet worden doorzocht
  • start – startpositie van de zoekopdracht (optioneel)
  • overeenstemming – voorkomen van de gezochte string (optioneel)
 Selecteer INSTR('AEROPLANE','E',2,1) uit dubbel

uitgang: 2

Selecteer INSTR('AEROPLANE','E',2,2) uit dubbel

uitgang: 9 (2nd voorkomen van E)
SUBSTR (tekst, begin, lengte) Geeft de subtekenreekswaarde van de hoofdtekenreeks.

  • tekst – hoofdreeks
  • start – startpositie
  • lengte – lengte die moet worden gesubstringeerd
 selecteer substr('vliegtuig',1,7) uit dubbel

uitgang: vliegtuig
BOVEN ( tekst ) Retourneert de hoofdletter van de opgegeven tekst Selecteer upper('guru99') uit dual;

uitgang:GURU99
LAGER ( tekst ) Retourneert de kleine letter van de opgegeven tekst Selecteer lager ('AerOpLane') uit dubbel;

uitgang: vliegtuig
INITCAP (tekst) Geeft de opgegeven tekst terug met de beginletter in hoofdletters. Selecteer ('guru99') uit dual

uitgang: Goeroe99

Selecteer ('mijn verhaal') uit dubbel

uitgang: Mijn verhaal
LENGTE ( tekst ) Geeft de lengte van de gegeven string terug Selecteer LENGTH ('guru99') uit dual;

uitgang: 6
LPAD (tekst, lengte, pad_char) Vult de string aan de linkerkant voor de opgegeven lengte (totale string) in met het opgegeven teken Selecteer LPAD('guru99', 10, '$') uit dubbel;

uitgang: $$$$goeroe99
RPAD (tekst, lengte, pad_char) Vult de tekenreeks aan de rechterkant voor de opgegeven lengte (totale tekenreeks) in met het opgegeven teken Selecteer RPAD('guru99′,10,'-') uit dubbel

uitgang: goeroe99—-
LTRIM ( tekst ) Snijdt de leidende witruimte uit de tekst Selecteer LTRIM('Guru99') uit dubbel;

uitgang: Goeroe99
RTRIM ( tekst ) Snijdt de achterliggende witte ruimte uit de tekst Selecteer RTRIM('Guru99 ') uit dubbel;

uitgang; Goeroe99

Datum Functies

Dit zijn functies die worden gebruikt voor het manipuleren van datums.

Functie Naam Gebruik Voorbeeld
ADD_MONTHS (datum, aantal maanden) Voegt de opgegeven maanden toe aan de datum TOEVOEGEN_MAAND('2015-01-01',5);

uitgang: 05 / 01 / 2015
SYSDATUM Retourneert de huidige datum en tijd van de server Selecteer SYSDATE uit dubbel;

uitgang: 10-4-2015 2:11:43 uur
KOFFERBAK Rond de datumvariabele af naar de laagst mogelijke waarde selecteer sysdate, TRUNC(sysdate) uit dual;

uitgang: 10-4-2015 2:12:39 10-4-2015
ROUND Rondt de datum af op de dichtstbijzijnde limiet, hoger of lager Selecteer sysdate, ROUND(sysdate) uit dubbel

uitgang: 10-4-2015 2:14:34 10-5-2015
MAANDEN_BETWEEN Retourneert het aantal maanden tussen twee datums Selecteer MAANDEN_BETWEEN (sysdate+60, sysdate) uit dubbel

uitgang: 2

Samenvatting

In dit hoofdstuk hebben we het volgende geleerd.

  • Procedure maken en verschillende manieren om deze aan te roepen
  • Hoe u Functie kunt creëren en hoe u deze op verschillende manieren kunt aanroepen
  • Overeenkomsten en verschillen tussen procedure en functie
  • Parameters en RETURN algemene terminologieën in PL/SQL-subprogramma's
  • Algemene ingebouwde functies in Oracle PL / SQL

Je houdt misschien van: