In deze tutorial ga je de gedetailleerde beschrijving zien over het maken en uitvoeren van de benoemde blokken (procedures en functies).
Procedures en functies zijn de subprogramma's die kunnen worden gemaakt en opgeslagen in de database als databaseobjecten. Ze kunnen ook binnen de andere blokken worden gebeld of doorverwezen.
Daarnaast behandelen we de belangrijkste verschillen tussen deze twee subprogramma's. We gaan ook de ingebouwde Oracle-functies bespreken.
In deze Oracle Stored Procedure-zelfstudie leert u:
- Terminologieën in PL / SQL-subprogramma's
- Wat is procedure in PL / SQL?
- Wat is functie?
- Overeenkomsten tussen procedure en functie
- Procedure Vs. Functie: belangrijkste verschillen
- Ingebouwde functies in PL / SQL
Terminologieën in PL / SQL-subprogramma's
Voordat we 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 tijdelijke aanduiding van elk geldig PL / SQL-datatype waarmee het PL / SQL-subprogramma de waarden uitwisselt met de hoofdcode. Deze parameter maakt het mogelijk om input te geven aan de subprogramma's en om uit deze subprogramma's te extraheren.
- Deze parameters moeten samen met de subprogramma's worden gedefinieerd op het moment dat ze worden gemaakt.
- Deze parameters zijn opgenomen in de oproepende instructie 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 van parameterdeclaratie, aangezien de grootte dynamisch is voor dit type.
Op basis van hun doel worden parameters geclassificeerd als
- IN Parameter
- OUT-parameter
- IN UIT Parameter
IN parameter:
- Deze parameter wordt gebruikt om input te geven aan de subprogramma's.
- Het is een alleen-lezen variabele binnen de subprogramma's. Hun waarden kunnen niet binnen het subprogramma worden gewijzigd.
- In de aanroepende instructie kunnen deze parameters een variabele of een letterlijke waarde of een uitdrukking zijn, het kan bijvoorbeeld de rekenkundige uitdrukking zijn zoals '5 * 8' of 'a / b' waarbij 'a' en 'b' variabelen zijn .
- Standaard zijn de parameters van het IN-type.
OUT-parameter:
- Deze parameter wordt gebruikt om uitvoer van de subprogramma's op te halen.
- 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 om zowel input te geven als om output van de subprogramma's te krijgen.
- 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 subprogramma's vast te houden.
Dit parametertype moet worden vermeld bij het maken van de subprogramma's.
TERUGKEER
RETURN is het sleutelwoord dat de compiler instrueert om de besturing over te schakelen van het subprogramma naar de aanroepende instructie. In het subprogramma betekent RETURN gewoon dat de besturing het subprogramma moet verlaten. Zodra de controller het RETURN-sleutelwoord in het subprogramma vindt, wordt de code hierna overgeslagen.
Normaal gesproken roept het ouder- of hoofdblok de subprogramma's op, en dan verschuift de besturing van dat ouderblok naar de opgeroepen subprogramma's. RETURN in het subprogramma keert de besturing terug naar het bovenliggende blok. In het geval van functies retourneert de instructie RETURN ook de waarde. Het datatype van deze waarde wordt altijd vermeld op het moment van functieverklaring. Het datatype kan van elk geldig PL / SQL-gegevenstype zijn.
Wat is procedure in PL / SQL?
Een procedure in PL / SQL is een subprogramma-eenheid die bestaat uit een groep PL / SQL-instructies die bij naam kunnen worden aangeroepen. Elke procedure in PL / SQL heeft zijn eigen unieke naam waarmee ernaar kan worden verwezen en die kan worden aangeroepen. Deze subprogramma-eenheid in de Oracle-database wordt opgeslagen als een database-object.
Opmerking: het subprogramma is niets anders dan een procedure en moet volgens de vereisten handmatig worden gemaakt. Zodra ze zijn gemaakt, worden ze opgeslagen als databaseobjecten.
Hieronder staan de kenmerken van de procedure-subprogramma-eenheid in PL / SQL:
- Procedures zijn zelfstandige blokken van een programma die in de database kunnen worden opgeslagen.
- Een oproep naar deze PLSQL-procedures kan worden gedaan 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 hebben, of het kan worden gedefinieerd en genest in de andere blokken of pakketten.
- Het bevat declaratiegedeelte (optioneel), uitvoeringsgedeelte, uitzonderingsafhandelingsgedeelte (optioneel).
- De waarden kunnen worden doorgegeven aan de Oracle-procedure of via parameters uit de procedure worden opgehaald.
- Deze parameters moeten worden opgenomen in de aanroepende instructie.
- Een procedure in SQL kan een RETURN-instructie hebben om het besturingselement terug te sturen naar het aanroepende blok, maar kan geen waarden retourneren via de RETURN-instructie.
- Procedures kunnen niet rechtstreeks worden aangeroepen vanuit SELECT-instructies. Ze kunnen worden aangeroepen vanuit een ander blok of via het EXEC-trefwoord.
Syntaxis:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE instrueert de compiler om een nieuwe procedure in Oracle te maken. Trefwoord 'OR REPLACE' instrueert de compilatie om de bestaande procedure (indien aanwezig) te vervangen door de huidige.
- De procedurenaam moet uniek zijn.
- Het trefwoord 'IS' wordt gebruikt als de opgeslagen procedure in Oracle in andere blokken is genest. Als de procedure op zichzelf staat, wordt 'AS' gebruikt. Behalve deze coderingsstandaard hebben beide dezelfde betekenis.
Voorbeeld 1: Procedure maken en aanroepen met EXEC
In dit voorbeeld gaan we een Oracle-procedure maken die de naam als invoer aanneemt en het welkomstbericht als uitvoer afdrukt. We gaan het EXEC-commando gebruiken om de procedure aan te roepen.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Code Verklaring:
- Coderegel 1 : Creëren van de procedure met de naam 'welcome_msg' en met één parameter 'p_name' van het type 'IN'.
- Coderegel 4 : het welkomstbericht afdrukken door de invoernaam samen te voegen.
- De procedure is met succes samengesteld.
- Coderegel 7 : Aanroepen van de procedure met behulp van het EXEC-commando met de parameter 'Guru99'. De procedure wordt uitgevoerd en het bericht wordt afgedrukt als "Welcome Guru99".
Wat is functie?
Functies 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 rekendoeleinden.
- Functie gebruikt RETURN sleutelwoord om de waarde te retourneren, en het datatype hiervan wordt gedefinieerd op het moment van creatie.
- Een functie moet een waarde retourneren of de uitzondering verhogen, dwz dat de terugkeer verplicht is in functies.
- Functie zonder DML-instructies kan direct worden aangeroepen in SELECT-query, terwijl de functie met DML-bewerking alleen kan worden aangeroepen vanuit andere PL / SQL-blokken.
- Het kan geneste blokken hebben, of het kan worden gedefinieerd en genest in de andere blokken of pakketten.
- Het bevat declaratiegedeelte (optioneel), uitvoeringsgedeelte, uitzonderingsafhandelingsgedeelte (optioneel).
- De waarden kunnen worden doorgegeven aan de functie of worden opgehaald uit de procedure via de parameters.
- Deze parameters moeten worden opgenomen in de aanroepende instructie.
- Een PLSQL-functie kan de waarde ook retourneren via OUT-parameters anders dan met RETURN.
- Omdat het altijd de waarde retourneert, gaat het bij het aanroepen van de instructie altijd samen met de toewijzingsoperator om de variabelen in te vullen.
Syntaxis
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION instrueert de compiler om een nieuwe functie te maken. Trefwoord 'OR REPLACE' instrueert de compiler om de bestaande functie (indien aanwezig) te vervangen door de huidige.
- De functienaam moet uniek zijn.
- Het gegevenstype RETURN moet worden vermeld.
- Het trefwoord 'IS' wordt gebruikt als de procedure in andere blokken is genest. Als de procedure op zichzelf staat, wordt 'AS' gebruikt. Behalve deze coderingsstandaard hebben beide dezelfde betekenis.
Voorbeeld 1: Functie maken en aanroepen met anonieme blokkering
In dit programma gaan we een functie maken die de naam als invoer aanneemt en het welkomstbericht als uitvoer retourneert. We gaan een anonieme blokkering gebruiken en een instructie selecteren om de functie aan te roepen.
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;
Code Verklaring:
- Coderegel 1 : Creëren van de Oracle-functie met de 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 aaneengeschakelde waarde 'Welkom' en de parameterwaarde.
- Coderegel 8 : Anoniem blok om de bovenstaande functie op te roepen.
- Coderegel 9 : Declaratie van de variabele met datatype hetzelfde als het geretourneerde datatype van de functie.
- Coderegel 11 : Roep de functie op en vul de retourwaarde in voor de variabele 'lv_msg'.
- Coderegel 12 : de waarde van de variabele afdrukken. De output die je hier krijgt is "Welcome Guru99"
- Coderegel 14 : dezelfde functie aanroepen via SELECT-instructie. De geretourneerde waarde wordt rechtstreeks naar de standaarduitvoer gestuurd.
Overeenkomsten tussen procedure en functie
- Beide kunnen worden aangeroepen vanuit andere PL / SQL-blokken.
- Als de uitzondering die in het subprogramma is opgewekt, niet wordt afgehandeld in de sectie voor het afhandelen van uitzonderingen op het subprogramma, wordt deze doorgegeven aan het aanroepende blok.
- Beide kunnen zoveel parameters hebben als nodig is.
- Beide worden behandeld als databaseobjecten in PL / SQL.
Procedure Vs. Functie: belangrijkste verschillen
| Procedure | Functie |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Ingebouwde functies in PL / SQL
PL / SQL bevat verschillende ingebouwde functies om te werken met tekenreeksen en datumdatatype. Hier gaan we de veelgebruikte functies en hun gebruik bekijken.
Conversiefuncties
Deze ingebouwde functies worden gebruikt om het ene datatype naar het andere datatype te converteren.
| Functienaam | Gebruik | Voorbeeld |
|---|---|---|
| TO_CHAR | Converteert het andere datatype naar het karakterdatatype | TO_CHAR (123); |
| TO_DATE (tekenreeks, indeling) | Converteert de opgegeven tekenreeks tot nu toe. De string moet overeenkomen met het formaat. | TO_DATE ('2015-JAN-15', 'JJJJ-MA-DD'); Uitgang: 15/01/2015 |
| TO_NUMBER (tekst, indeling) | Converteert de tekst naar het nummertype van de opgegeven indeling. Informat '9' geeft het aantal cijfers aan | Selecteer TO_NUMBER ('1234', '9999') uit dubbel; Uitvoer: 1234 Selecteer TO_NUMBER ('1,234,45', '9,999,99') van dubbel; Uitgang: 1234 |
String-functies
Dit zijn de functies die worden gebruikt op het datatype van het teken.
| Functienaam | Gebruik | Voorbeeld |
|---|---|---|
| INSTR (tekst, tekenreeks, begin, voorkomen) | Geeft de positie van een bepaalde tekst in de opgegeven tekenreeks.
| Selecteer INSTR ('VLIEGTUIG', 'E', 2,1) uit dubbele uitvoer : 2 Selecteer INSTR ('VLIEGTUIG', 'E', 2,2) uit dubbele uitvoer: 9 ( tweede keer E) |
| SUBSTR (tekst, begin, lengte) | Geeft de subtekenreekswaarde van de hoofdreeks.
| selecteer substraat ('vliegtuig', 1,7) van dual Output : aeropla |
| UPPER (tekst) | Retourneert de hoofdletter van de opgegeven tekst | Selecteer upper ('guru99') uit dual; Uitgang : GURU99 |
| LAGER (tekst) | Retourneert de kleine letters van de opgegeven tekst | Selecteer lager ('vliegtuig') uit dubbel; Output : vliegtuig |
| INITCAP (tekst) | Retourneert de gegeven tekst met de beginletter in hoofdletters. | Selecteer ('guru99') uit dubbele uitvoer : Guru99 Selecteer ('mijn verhaal') uit dubbele uitvoer : mijn verhaal |
| LENGTH (tekst) | Retourneert de lengte van de opgegeven tekenreeks | Selecteer LENGTH ('guru99') uit dual; Uitgang : 6 |
| LPAD (tekst, lengte, pad_char) | Vult de tekenreeks aan de linkerkant voor de opgegeven lengte (totale tekenreeks) op met het opgegeven teken | Selecteer LPAD ('guru99', 10, '$') uit dual; Uitvoer : $$$$ guru99 |
| RPAD (tekst, lengte, pad_char) | Vult de tekenreeks aan de rechterkant voor de opgegeven lengte (totale tekenreeks) op met het opgegeven teken | Selecteer RPAD ('guru99', 10, '-') uit dubbele uitvoer : guru99 ---- |
| LTRIM (tekst) | Hiermee wordt de eerste witruimte van de tekst bijgesneden | Selecteer LTRIM ('Guru99') uit dual; Uitgang : Guru99 |
| RTRIM (tekst) | Hiermee wordt de achterliggende witruimte van de tekst bijgesneden | Selecteer RTRIM ('Guru99') van dual; Uitvoer ; Guru99 |
Datumfuncties
Dit zijn functies die worden gebruikt om met datums te manipuleren.
| Functienaam | Gebruik | Voorbeeld |
|---|---|---|
| ADD_MONTHS (datum, aantal maanden) | Telt de opgegeven maanden op bij de datum | ADD_MONTH ('2015-01-01', 5); Uitgang : 01/05/2015 |
| SYSDATE | Retourneert de huidige datum en tijd van de server | Selecteer SYSDATE van dual; Uitgang : 04-10-2015 14:11:43 uur |
| TRUNC | Rond de datumvariabele af naar de laagst mogelijke waarde | selecteer sysdate, TRUNC (sysdate) van dual; Uitgang : 04/10/2015 14:12:39 PM 04/10/2015 |
| RONDE | Rondt de datum af op de dichtstbijzijnde limiet, hoger of lager | Selecteer sysdate, ROUND (sysdate) uit dubbele uitvoer : 04/10/2015 14:14:34 PM 05/10/2015 |
| MONTHS_BETWEEN | Retourneert het aantal maanden tussen twee datums | Selecteer MONTHS_BETWEEN (sysdate + 60, sysdate) uit dubbele uitvoer : 2 |
Overzicht
In dit hoofdstuk hebben we het volgende geleerd.
- Hoe maak je een procedure en verschillende manieren om het te noemen
- Hoe u een functie kunt maken en verschillende manieren om het te noemen
- Overeenkomsten en verschillen tussen procedure en functie
- Parameters en algemene terminologieën RETURN in PL / SQL-subprogramma's
- Gemeenschappelijke ingebouwde functies in Oracle PL / SQL