În acest tutorial, veți vedea descrierea detaliată despre cum să creați și să executați blocurile numite (proceduri și funcții).
Procedurile și funcțiile sunt subprogramele care pot fi create și salvate în baza de date ca obiecte ale bazei de date. Ele pot fi apelate sau menționate și în celelalte blocuri.
În afară de aceasta, vom acoperi diferențele majore dintre aceste două subprograme. De asemenea, vom discuta despre funcțiile încorporate Oracle.
În acest tutorial Oracle Stored Procedure, veți învăța-
- Terminologii în subprograme PL / SQL
- Ce este Procedura în PL / SQL?
- Ce este funcția?
- Asemănări între procedură și funcție
- Procedura vs. Funcție: Diferențe cheie
- Funcții încorporate în PL / SQL
Terminologii în subprograme PL / SQL
Înainte de a afla despre subprogramele PL / SQL, vom discuta diferitele terminologii care fac parte din aceste subprograme. Mai jos sunt terminologiile pe care le vom discuta.
Parametru:
Parametrul este variabil sau substituent al oricărui tip de date valid PL / SQL prin care subprogramul PL / SQL schimbă valorile cu codul principal. Acest parametru permite introducerea subprogramelor și extragerea din aceste subprograme.
- Acești parametri trebuie definiți împreună cu subprogramele în momentul creării.
- Acești parametri sunt incluși în declarația de apelare a acestor subprograme pentru a interacționa valorile cu subprogramele.
- Tipul de date al parametrului din subprogram și instrucțiunea de apelare trebuie să fie aceleași.
- Dimensiunea tipului de date nu trebuie menționată în momentul declarării parametrilor, deoarece dimensiunea este dinamică pentru acest tip.
Pe baza scopului lor, parametrii sunt clasificați ca
- Parametru IN
- Parametru OUT
- Parametru IN OUT
Parametru IN:
- Acest parametru este utilizat pentru a da intrare subprogramelor.
- Este o variabilă numai în citire din subprograme. Valorile lor nu pot fi modificate în interiorul subprogramului.
- În declarația de apelare, acești parametri pot fi o variabilă sau o valoare literală sau o expresie, de exemplu, ar putea fi expresia aritmetică precum „5 * 8” sau „a / b” unde „a” și „b” sunt variabile .
- În mod implicit, parametrii sunt de tip IN.
Parametru OUT:
- Acest parametru este utilizat pentru obținerea de ieșiri din subprograme.
- Este o variabilă de citire-scriere din interiorul subprogramelor. Valorile lor pot fi modificate în cadrul subprogramelor.
- În instrucțiunea de apelare, acești parametri ar trebui să fie întotdeauna o variabilă pentru a reține valoarea din subprogramele curente.
Parametru IN OUT:
- Acest parametru este utilizat atât pentru a da intrări, cât și pentru a obține ieșiri din subprograme.
- Este o variabilă de citire-scriere din interiorul subprogramelor. Valorile lor pot fi modificate în cadrul subprogramelor.
- În instrucțiunea de apelare, acești parametri ar trebui să fie întotdeauna o variabilă pentru a reține valoarea din subprograme.
Acest tip de parametru trebuie menționat în momentul creării subprogramelor.
ÎNTOARCERE
RETURN este cuvântul cheie care instruiește compilatorul să comute controlul de la subprogram la instrucțiunea de apelare. În subprogram RETURN înseamnă pur și simplu că controlul trebuie să iasă din subprogram. Odată ce controlerul găsește cuvântul cheie RETURN în subprogram, codul după acesta va fi omis.
În mod normal, blocul părinte sau principal va apela subprogramele, iar apoi controlul va trece de la acel bloc părinte la subprogramele numite. RETURN în subprogram va returna controlul înapoi la blocul părinte. În cazul funcțiilor, instrucțiunea RETURN returnează și valoarea. Tipul de date al acestei valori este întotdeauna menționat în momentul declarării funcției. Tipul de date poate fi de orice tip de date valid PL / SQL.
Ce este Procedura în PL / SQL?
O procedură în PL / SQL este o unitate de subprogram care constă dintr-un grup de instrucțiuni PL / SQL care pot fi apelate pe nume. Fiecare procedură din PL / SQL are propriul său nume unic prin care poate fi menționat și apelat. Această unitate de subprogram din baza de date Oracle este stocată ca obiect de bază de date.
Notă: Subprogramul nu este altceva decât o procedură și trebuie creat manual, conform cerințelor. Odată create, acestea vor fi stocate ca obiecte de bază de date.
Mai jos sunt caracteristicile unității de subprogram Procedură din PL / SQL:
- Procedurile sunt blocuri independente ale unui program care pot fi stocate în baza de date.
- Apelarea la aceste proceduri PLSQL poate fi făcută referindu-se la numele lor, pentru a executa instrucțiunile PL / SQL.
- Este utilizat în principal pentru a executa un proces în PL / SQL.
- Poate avea blocuri imbricate sau poate fi definit și imbricat în interiorul celorlalte blocuri sau pachete.
- Conține partea de declarație (opțional), partea de execuție, partea de tratare a excepțiilor (opțional).
- Valorile pot fi trecute în procedura Oracle sau preluate din procedură prin intermediul parametrilor.
- Acești parametri trebuie incluși în declarația de apelare.
- O procedură în SQL poate avea o instrucțiune RETURN pentru a returna controlul către blocul apelant, dar nu poate returna nicio valoare prin instrucțiunea RETURN.
- Procedurile nu pot fi apelate direct din instrucțiunile SELECT. Pot fi apelate dintr-un alt bloc sau prin cuvântul cheie EXEC.
Sintaxă:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE instruiește compilatorul să creeze o nouă procedură în Oracle. Cuvântul cheie „SAU ÎNLOCUIE” instruiește compilarea să înlocuiască procedura existentă (dacă există) cu cea curentă.
- Numele procedurii trebuie să fie unic.
- Cuvântul cheie „IS” va fi utilizat, atunci când procedura stocată în Oracle este cuibărită în alte blocuri. Dacă procedura este independentă, atunci va fi utilizat „AS”. În afară de acest standard de codificare, ambele au aceeași semnificație.
Exemplul 1: Procedura de creare și apelarea acesteia folosind EXEC
În acest exemplu, vom crea o procedură Oracle care ia numele ca intrare și tipărește mesajul de întâmpinare ca ieșire. Vom folosi comanda EXEC pentru a apela procedura.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Explicatie cod:
- Linia de cod 1 : Crearea procedurii cu numele „welcome_msg” și cu un parametru „p_name” de tip „IN”.
- Linia de cod 4 : Tipărirea mesajului de întâmpinare prin concatenarea numelui de intrare.
- Procedura este compilată cu succes.
- Linia de cod 7 : Apelarea procedurii utilizând comanda EXEC cu parametrul „Guru99”. Procedura este executată, iar mesajul este tipărit ca „Bun venit Guru99”.
Ce este funcția?
Functions este un subprogram PL / SQL independent. La fel ca procedura PL / SQL, funcțiile au un nume unic prin care pot fi referite. Acestea sunt stocate ca obiecte de bază de date PL / SQL. Mai jos sunt câteva dintre caracteristicile funcțiilor.
- Funcțiile sunt un bloc independent care este utilizat în principal în scop de calcul.
- Funcția utilizează cuvântul cheie RETURN pentru a returna valoarea, iar tipul de date al acestuia este definit în momentul creării.
- O funcție ar trebui fie să returneze o valoare, fie să ridice excepția, adică returnarea este obligatorie în funcții.
- Funcția fără instrucțiuni DML poate fi apelată direct în interogarea SELECT, în timp ce funcția cu operație DML poate fi apelată numai din alte blocuri PL / SQL.
- Poate avea blocuri imbricate sau poate fi definit și imbricat în interiorul celorlalte blocuri sau pachete.
- Conține partea de declarație (opțional), partea de execuție, partea de tratare a excepțiilor (opțional).
- Valorile pot fi transmise în funcție sau preluate din procedură prin intermediul parametrilor.
- Acești parametri trebuie incluși în declarația de apelare.
- O funcție PLSQL poate, de asemenea, să returneze valoarea prin intermediul parametrilor OUT, în afară de utilizarea RETURN.
- Deoarece va returna întotdeauna valoarea, în instrucțiunea de apelare se însoțește întotdeauna cu operatorul de atribuire pentru a popula variabilele.
Sintaxă
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION instruiește compilatorul să creeze o nouă funcție. Cuvântul cheie „SAU ÎNLOCUIE” instruiește compilatorul să înlocuiască funcția existentă (dacă există) cu cea curentă.
- Numele funcției ar trebui să fie unic.
- Trebuie menționat tipul de date RETURN.
- Cuvântul cheie „ESTE” va fi utilizat, atunci când procedura este imbricată în alte blocuri. Dacă procedura este independentă, atunci va fi utilizat „AS”. În afară de acest standard de codificare, ambele au aceeași semnificație.
Exemplul 1: crearea funcției și apelarea acesteia folosind bloc anonim
În acest program, vom crea o funcție care ia numele ca intrare și returnează mesajul de întâmpinare ca ieșire. Vom folosi un bloc anonim și vom selecta declarația pentru a apela funcția.
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;
Explicatie cod:
- Linia de cod 1 : Crearea funcției Oracle cu numele „welcome_msg_func” și cu un parametru „p_name” de tip „IN”.
- Linia de cod 2 : declararea tipului de returnare ca VARCHAR2
- Linia de cod 5 : Returnarea valorii concatenate „Bun venit” și a valorii parametrului.
- Linia de cod 8 : bloc anonim pentru a apela funcția de mai sus.
- Linia de cod 9 : Declararea variabilei cu tip de date identic cu tipul de date returnat al funcției.
- Linia de cod 11 : Apelarea funcției și completarea valorii returnate la variabila „lv_msg”.
- Linia de cod 12 : Tipărirea valorii variabilei. Rezultatul pe care îl veți obține aici este „Welcome Guru99”
- Linia de cod 14 : Apelarea aceleiași funcții prin instrucțiunea SELECT. Valoarea returnată este direcționată direct la ieșirea standard.
Asemănări între procedură și funcție
- Ambele pot fi apelate din alte blocuri PL / SQL.
- Dacă excepția ridicată în subprogram nu este tratată în secțiunea de gestionare a excepțiilor subprogramului, atunci se va propaga către blocul de apelare.
- Ambele pot avea cât mai mulți parametri necesari.
- Ambele sunt tratate ca obiecte de bază de date în PL / SQL.
Procedura vs. Funcție: Diferențe cheie
| Procedură | Funcţie |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Funcții încorporate în PL / SQL
PL / SQL conține diverse funcții încorporate pentru a lucra cu șiruri și tip de date. Aici vom vedea funcțiile utilizate în mod obișnuit și utilizarea acestora.
Funcții de conversie
Aceste funcții încorporate sunt utilizate pentru a converti un tip de date în alt tip de date.
| Numele funcției | Utilizare | Exemplu |
|---|---|---|
| TO_CHAR | Convertește celălalt tip de date în tip de date caracter | TO_CHAR (123); |
| TO_DATE (șir, format) | Convertește șirul dat în dată. Șirul ar trebui să se potrivească cu formatul. | TO_DATE ('2015-JAN-15', 'AAAA-LUN-ZZ'); Ieșire: 15.01.2015 |
| TO_NUMBER (text, format) | Convertește textul în tipul de număr al formatului dat. Informatul „9” indică numărul de cifre | Selectați TO_NUMBER ('1234', '9999') din dual; Ieșire: 1234 Selectați TO_NUMBER („1.234.45”, „9.999.99”) din dual; Ieșire: 1234 |
Funcții de șir
Acestea sunt funcțiile care sunt utilizate pe tipul de date al caracterului.
| Numele funcției | Utilizare | Exemplu |
|---|---|---|
| INSTR (text, șir, start, ocurență) | Oferă poziția unui anumit text în șirul dat.
| Selectați INSTR ('AIRPLANE', 'E', 2,1) din ieșire duală : 2 Selectați INSTR ('AIRPLANE', 'E', 2,2) din ieșire duală: 9 (a 2 -a apariție a E) |
| SUBSTR (text, început, lungime) | Oferă valoarea sub șirului șirului principal.
| selectați substr („avion”, 1,7) din ieșire dublă : aeropla |
| SUPERIOR (text) | Returnează majusculele textului furnizat | Selectați upper („guru99”) din dual; Ieșire : GURU99 |
| LOWER (text) | Returnează minuscula textului furnizat | Selectați mai jos („avion”) din dual; Ieșire : avion |
| INITCAP (text) | Returnează textul dat cu litera de început cu majuscule. | Selectați („guru99”) din ieșire dublă : Guru99 Select („povestea mea”) din ieșire duală : Povestea mea |
| LUNGIME (text) | Returnează lungimea șirului dat | Selectați LUNGIME („guru99”) din dual; Ieșire : 6 |
| LPAD (text, lungime, pad_char) | Completează șirul din partea stângă pentru lungimea dată (șirul total) cu caracterul dat | Selectați LPAD („guru99”, 10, „$”) din dual; Ieșire : $$$$ guru99 |
| RPAD (text, lungime, pad_char) | Completează șirul din partea dreaptă pentru lungimea dată (șirul total) cu caracterul dat | Selectați RPAD ('guru99', 10, '-') din ieșire duală : guru99 ---- |
| LTRIM (text) | Îndepărtează spațiul alb din text | Selectați LTRIM („Guru99”) din dual; Ieșire : Guru99 |
| RTRIM (text) | Îndepărtează spațiul alb din text | Selectați RTRIM („Guru99”) din dual; Ieșire ; Guru99 |
Funcții de dată
Acestea sunt funcții care sunt utilizate pentru manipularea cu date.
| Numele funcției | Utilizare | Exemplu |
|---|---|---|
| ADD_MONTHS (data, nr. De luni) | Adăugă lunile date la dată | ADD_MONTH („01.01.2015”, 5); Ieșire : 05/01/2015 |
| SYSDATE | Returnează data și ora curente ale serverului | Selectați SYSDATE din dual; Ieșire : 04/10/2015 14:11:43 |
| TRUNC | Runda variabilei de dată la valoarea posibilă mai mică | selectați sysdate, TRUNC (sysdate) din dual; Ieșire : 04/10/2015 14:12:39 PM 04/10/2015 |
| RUNDĂ | Rotunjește data la cea mai apropiată limită, fie mai mare, fie mai mică | Selectați sysdate, ROUND (sysdate) din ieșire duală : 04.04.2015 14:14:34 PM 10/5/2015 |
| MONTHS_BETWEEN | Returnează numărul de luni între două date | Selectați MONTHS_BETWEEN (sysdate + 60, sysdate) din ieșire duală : 2 |
rezumat
În acest capitol, am învățat următoarele.
- Cum să creați procedura și diferite moduri de a o apela
- Cum să creați funcția și diferite moduri de a o apela
- Asemănări și diferențe între procedură și funcție
- Parametri și RETURN terminologii comune în subprograme PL / SQL
- Funcții încorporate obișnuite în Oracle PL / SQL