I denne vejledning vil du se den detaljerede beskrivelse af, hvordan du opretter og udfører de navngivne blokke (procedurer og funktioner).
Procedurer og funktioner er de underprogrammer, der kan oprettes og gemmes i databasen som databaseobjekter. De kan også kaldes eller henvises inde i de andre blokke.
Bortset fra dette vil vi dække de store forskelle mellem disse to underprogrammer. Vi vil også diskutere de indbyggede Oracle-funktioner.
I denne Oracle Stored Procedure-tutorial lærer du-
- Terminologier i PL / SQL-underprogrammer
- Hvad er procedure i PL / SQL?
- Hvad er funktion?
- Ligheder mellem procedure og funktion
- Procedure Vs. Funktion: Nøgleforskelle
- Indbyggede funktioner i PL / SQL
Terminologier i PL / SQL-underprogrammer
Før vi lærer om PL / SQL-underprogrammer, vil vi diskutere de forskellige terminologier, der er en del af disse underprogrammer. Nedenfor er de terminologier, som vi skal diskutere.
Parameter:
Parameteren er variabel eller pladsholder for enhver gyldig PL / SQL-datatype, gennem hvilken PL / SQL-underprogrammet udveksler værdierne med hovedkoden. Denne parameter gør det muligt at give input til underprogrammerne og udtrække fra disse underprogrammer.
- Disse parametre skal defineres sammen med underprogrammerne på tidspunktet for oprettelsen.
- Disse parametre er inkluderet i opkaldserklæringen for disse underprogrammer for at interagere værdierne med underprogrammerne.
- Datatypen for parameteren i underprogrammet og den kaldende erklæring skal være den samme.
- Størrelsen på datatypen bør ikke nævnes på tidspunktet for parameterdeklarationen, da størrelsen er dynamisk for denne type.
Baseret på deres formål klassificeres parametre som
- IN Parameter
- OUT Parameter
- IN OUT Parameter
IN Parameter:
- Denne parameter bruges til at give input til underprogrammerne.
- Det er en skrivebeskyttet variabel inde i underprogrammerne. Deres værdier kan ikke ændres i underprogrammet.
- I kaldesætningen kan disse parametre være en variabel eller en bogstavelig værdi eller et udtryk, for eksempel kan det være det aritmetiske udtryk som '5 * 8' eller 'a / b' hvor 'a' og 'b' er variabler .
- Som standard er parametrene af IN-typen.
OUT Parameter:
- Denne parameter bruges til at hente output fra underprogrammerne.
- Det er en læse-skrive variabel inde i underprogrammerne. Deres værdier kan ændres inden i underprogrammerne.
- I kaldeanmeldelsen skal disse parametre altid være en variabel, der holder værdien fra de aktuelle underprogrammer.
IN OUT Parameter:
- Denne parameter bruges til både at give input og til at få output fra underprogrammerne.
- Det er en læse-skrive variabel inde i underprogrammerne. Deres værdier kan ændres inden i underprogrammerne.
- I kaldeanmeldelsen skal disse parametre altid være en variabel, der holder værdien fra underprogrammerne.
Disse parametertyper bør nævnes på tidspunktet for oprettelse af underprogrammerne.
VEND TILBAGE
RETURN er nøgleordet, der instruerer kompilatoren om at skifte styringen fra underprogrammet til den kaldende erklæring. I underprogram betyder RETURN simpelthen, at kontrollen skal forlades fra underprogrammet. Når controlleren finder RETURN-nøgleordet i underprogrammet, springes koden efter dette over.
Normalt kalder forælder- eller hovedblok underprogrammerne, og derefter skifter styringen fra disse overordnede blokke til de kaldte underprogrammer. RETURN i underprogrammet returnerer kontrolelementet til deres overordnede blok. I tilfælde af funktioner returnerer RETURN-sætningen også værdien. Datatypen for denne værdi nævnes altid på tidspunktet for funktionserklæringen. Datatypen kan være af en hvilken som helst gyldig PL / SQL-datatype.
Hvad er procedure i PL / SQL?
En procedure i PL / SQL er en underprogramenhed, der består af en gruppe PL / SQL-sætninger, der kan kaldes ved navn. Hver procedure i PL / SQL har sit eget unikke navn, som det kan henvises til og kaldes til. Denne underprogramenhed i Oracle-databasen er gemt som et databaseobjekt.
Bemærk: Underprogram er intet andet end en procedure, og det skal oprettes manuelt i henhold til kravet. Når de er oprettet, gemmes de som databaseobjekter.
Nedenfor er kendetegnene for Procedure-underprogramenheden i PL / SQL:
- Procedurer er enkeltstående blokke af et program, der kan gemmes i databasen.
- Opkald til disse PLSQL-procedurer kan foretages ved at henvise til deres navn for at udføre PL / SQL-udsagnene.
- Det bruges hovedsageligt til at udføre en proces i PL / SQL.
- Det kan have indlejrede blokke, eller det kan defineres og indlejres i de andre blokke eller pakker.
- Den indeholder erklæringsdel (valgfri), eksekveringsdel, undtagelseshåndteringsdel (valgfri).
- Værdierne kan overføres til Oracle-proceduren eller hentes fra proceduren gennem parametre.
- Disse parametre skal medtages i den kaldende erklæring.
- En procedure i SQL kan have en RETURN-sætning for at returnere kontrolelementet til opkaldsblokken, men den kan ikke returnere nogen værdier gennem RETURN-sætningen.
- Procedurer kan ikke kaldes direkte fra SELECT-sætninger. De kan kaldes fra en anden blok eller via EXEC-nøgleord.
Syntaks:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE instruerer compileren om at oprette en ny procedure i Oracle. Nøgleordet 'ELLER UDSKIFT' instruerer kompileringen om at erstatte den eksisterende procedure (hvis nogen) med den aktuelle.
- Procedurenavn skal være entydigt.
- Nøgleordet 'IS' vil blive brugt, når den lagrede procedure i Oracle er indlejret i nogle andre blokke. Hvis proceduren er uafhængig, vil 'AS' blive brugt. Bortset fra denne kodningsstandard har begge den samme betydning.
Eksempel 1: Oprette procedure og kalde den ved hjælp af EXEC
I dette eksempel skal vi oprette en Oracle-procedure, der tager navnet som input og udskriver velkomstmeddelelsen som output. Vi skal bruge EXEC-kommandoen til at ringe til proceduren.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Kode Forklaring:
- Kodelinje 1 : Oprettelse af proceduren med navnet 'welcome_msg' og med en parameter 'p_name' af typen 'IN'.
- Kodelinje 4 : Udskrivning af velkomstmeddelelsen ved sammenkædning af inputnavnet.
- Proceduren er kompileret med succes.
- Kodelinje 7 : Henter proceduren ved hjælp af EXEC-kommandoen med parameteren 'Guru99'. Proceduren udføres, og meddelelsen udskrives som "Welcome Guru99".
Hvad er funktion?
Funktioner er et enkeltstående PL / SQL-underprogram. Ligesom PL / SQL-proceduren har funktioner et unikt navn, som det kan henvises til. Disse gemmes som PL / SQL-databaseobjekter. Nedenfor er nogle af egenskaberne ved funktioner.
- Funktioner er en enkeltstående blok, der hovedsagelig bruges til beregningsformål.
- Funktion brug RETURN-nøgleordet til at returnere værdien, og datatypen for denne defineres på tidspunktet for oprettelsen.
- En funktion skal enten returnere en værdi eller hæve undtagelsen, dvs. returnering er obligatorisk i funktioner.
- Funktion uden DML-sætninger kan kaldes direkte i SELECT-forespørgsel, mens funktionen med DML-operation kun kan kaldes fra andre PL / SQL-blokke.
- Det kan have indlejrede blokke, eller det kan defineres og indlejres i de andre blokke eller pakker.
- Den indeholder erklæringsdel (valgfri), eksekveringsdel, undtagelseshåndteringsdel (valgfri).
- Værdierne kan overføres til funktionen eller hentes fra proceduren gennem parametrene.
- Disse parametre skal medtages i den kaldende erklæring.
- En PLSQL-funktion kan også returnere værdien gennem andre OUT-parametre end ved hjælp af RETURN.
- Da det altid vil returnere værdien, ledsages det altid i en kaldende erklæring med tildelingsoperatøren for at udfylde variablerne.
Syntaks
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION instruerer compileren om at oprette en ny funktion. Nøgleordet 'ELLER UDSKIFT' instruerer kompilatoren om at erstatte den eksisterende funktion (hvis nogen) med den aktuelle.
- Funktionsnavnet skal være entydigt.
- RETURN datatype skal nævnes.
- Nøgleordet 'IS' vil blive brugt, når proceduren er indlejret i nogle andre blokke. Hvis proceduren er uafhængig, vil 'AS' blive brugt. Bortset fra denne kodningsstandard har begge den samme betydning.
Eksempel 1: Oprettelse af funktion og kaldelse af den ved hjælp af anonym blokering
I dette program skal vi oprette en funktion, der tager navnet som input og returnerer velkomstmeddelelsen som output. Vi skal bruge anonym blokering og vælge erklæring for at ringe til 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;
Kode Forklaring:
- Kodelinje 1 : Oprettelse af Oracle-funktionen med navnet 'welcome_msg_func' og med en parameter 'p_name' af typen 'IN'.
- Kodelinje 2 : erklærer returtypen som VARCHAR2
- Kodelinje 5 : Returnering af den sammenkædede værdi 'Velkommen' og parameterværdien.
- Kodelinje 8 : Anonym blokering for at kalde ovenstående funktion.
- Kodelinje 9 : Erklæring af variablen med datatype, der er den samme som datatypens returdatatype.
- Kodelinje 11 : Kaldelse af funktionen og udfyldning af returværdien til variablen 'lv_msg'.
- Kodelinje 12 : Udskrivning af den variable værdi. Det output, du får her, er "Velkommen Guru99"
- Kodelinje 14 : Opkald til den samme funktion gennem SELECT-sætning. Returværdien dirigeres direkte til standardoutputtet.
Ligheder mellem procedure og funktion
- Begge kan kaldes fra andre PL / SQL-blokke.
- Hvis undtagelsen, der er rejst i underprogrammet, ikke håndteres i afsnittet om håndtering af undtagelsesprogrammer, spreder den sig til opkaldsblokken.
- Begge kan have så mange parametre som nødvendigt.
- Begge behandles som databaseobjekter i PL / SQL.
Procedure Vs. Funktion: Nøgleforskelle
| Procedure | Fungere |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Indbyggede funktioner i PL / SQL
PL / SQL indeholder forskellige indbyggede funktioner til at arbejde med strenge og datatype. Her skal vi se de almindeligt anvendte funktioner og deres anvendelse.
Konverteringsfunktioner
Disse indbyggede funktioner bruges til at konvertere en datatype til en anden datatype.
| Funktionsnavn | Anvendelse | Eksempel |
|---|---|---|
| TO_CHAR | Konverterer den anden datatype til karakterdatatypen | TO_CHAR (123); |
| TO_DATE (streng, format) | Konverterer den givne streng til dato. Strengen skal matche med formatet. | TO_DATE ('2015-JAN-15', 'ÅÅÅÅ-MÅN-DD'); Output: 15/1/2015 |
| TO_NUMBER (tekst, format) | Konverterer teksten til nummertypen i det givne format. Informat '9' angiver antallet af cifre | Vælg TO_NUMBER ('1234', '9999') fra dual; Output: 1234 Vælg TO_NUMBER ('1.234,45', '9.999,99') fra dobbelt; Output: 1234 |
Strengfunktioner
Dette er de funktioner, der bruges på karakterdatatypen.
| Funktionsnavn | Anvendelse | Eksempel |
|---|---|---|
| INSTR (tekst, streng, start, forekomst) | Giver placeringen af en bestemt tekst i den givne streng.
| Vælg INSTR ('AIRPLANE', 'E', 2,1) fra dobbelt output : 2 Vælg INSTR ('AIRPLANE', 'E', 2,2) fra dual output: 9 ( 2. forekomst af E) |
| SUBSTR (tekst, start, længde) | Giver substringsværdien af hovedstrengen.
| vælg substr ('fly', 1,7) fra dobbelt output : aeropla |
| ØVRE (tekst) | Returnerer store bogstaver for den angivne tekst | Vælg øvre ('guru99') fra dobbelt; Output : GURU99 |
| NEDRE (tekst) | Returnerer små bogstaver i den angivne tekst | Vælg lavere ('fly') fra dobbelt; Output : fly |
| INITCAP (tekst) | Returnerer den givne tekst med startbogstavet i store bogstaver. | Vælg ('guru99') fra dobbelt output : Guru99 Vælg ('min historie') fra dobbelt output : Min historie |
| LÆNGDE (tekst) | Returnerer længden af den givne streng | Vælg LÆNGDE ('guru99') fra dobbelt; Output : 6 |
| LPAD (tekst, længde, pad_char) | Pads strengen i venstre side for den givne længde (total streng) med det givne tegn | Vælg LPAD ('guru99', 10, '$') fra dual; Output : $$$$ guru99 |
| RPAD (tekst, længde, pad_char) | Pads strengen i højre side for den givne længde (total streng) med det givne tegn | Vælg RPAD ('guru99', 10, '-') fra dobbelt output : guru99 ---- |
| LTRIM (tekst) | Beskærer det førende hvide rum fra teksten | Vælg LTRIM ('Guru99') fra dobbelt; Output : Guru99 |
| RTRIM (tekst) | Trimmer det bageste hvide rum fra teksten | Vælg RTRIM ('Guru99') fra dual; Output ; Guru99 |
Datofunktioner
Dette er funktioner, der bruges til at manipulere med datoer.
| Funktionsnavn | Anvendelse | Eksempel |
|---|---|---|
| ADD_MONTHS (dato, antal måneder) | Tilføjer de givne måneder til datoen | ADD_MONTH ('01-01-2015', 5); Output : 05/01/2015 |
| SYSDATE | Returnerer serverens aktuelle dato og klokkeslæt | Vælg SYSDATE fra dobbelt; Udgang : 10/10/2015 14:11:43 PM |
| TRUNC | Runde af datovariablen til den lavest mulige værdi | vælg sysdate, TRUNC (sysdate) fra dual; Output : 10/10/2015 14:12:39 PM 04/10/2015 |
| RUND | Afrunder datoen til den nærmeste grænse, enten højere eller lavere | Vælg sysdate, RUND (sysdate) fra dobbelt Output : 10/10/2015 14:14:34 PM 10/5/2015 |
| MÅNEDER_BETWEEN | Returnerer antallet af måneder mellem to datoer | Vælg MONTHS_BETWEEN (sysdate + 60, sysdate) fra dobbelt output : 2 |
Resumé
I dette kapitel har vi lært følgende.
- Sådan oprettes en procedure og forskellige måder at kalde den på
- Sådan oprettes funktion og forskellige måder at kalde den på
- Ligheder og forskelle mellem procedure og funktion
- Parametre og RETURN almindelige terminologier i PL / SQL-underprogrammer
- Almindelige indbyggede funktioner i Oracle PL / SQL