Oracle Procedură stocată PL/SQL și funcții cu exemple

Î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 de bază de date. Ele pot fi apelate sau trimise și în interiorul celorlalte blocuri.

În afară de aceasta, vom acoperi diferențele majore dintre aceste două subprograme. De asemenea, vom discuta despre Oracle funcții încorporate.

Terminologii în subprogramele PL/SQL

Înainte de a afla despre subprogramele PL/SQL, vom discuta despre diferitele terminologii care fac parte din aceste subprograme. Mai jos sunt terminologiile pe care le vom discuta.

Parametru

Parametrul este variabil sau substituent pentru orice valid Tip de date 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 ar trebui definiți împreună cu subprogramele în momentul creării.
• Acești parametri sunt incluși în instrucțiunea de apelare a acestor subprograme pentru a interacționa valorile cu subprogramele.
• Tipul de date al parametrului din subprogram și instrucțiunea de apelare ar trebui să fie aceleași.
• Mărimea tipului de date nu trebuie menționată în momentul declarării parametrului, deoarece dimensiunea este dinamică pentru acest tip.

Pe baza scopului lor, parametrii sunt clasificați ca

1. Parametrul IN
2. Parametrul OUT
3. Parametru IN OUT

Parametrul IN

• Acest parametru este utilizat pentru a da intrare subprogramelor.
• Este o variabilă numai în citire în cadrul subprogramelor. Valorile acestora nu pot fi modificate în cadrul subprogramului.
• În instrucțiunea 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.

Parametrul OUT

• Acest parametru este utilizat pentru obținerea rezultatelor din subprograme.
• Este o variabilă de citire-scriere în interiorul subprogramelor. Valorile acestora pot fi modificate în cadrul subprogramelor.
• În instrucțiunea de apelare, acești parametri ar trebui să fie întotdeauna o variabilă care să rețină valoarea din subprogramele curente.

Parametru IN OUT

• Acest parametru este folosit atât pentru a da intrări, cât și pentru a obține ieșiri din subprograme.
• Este o variabilă de citire-scriere în interiorul subprogramelor. Valorile acestora pot fi modificate în cadrul subprogramelor.
• În instrucțiunea de apelare, acești parametri ar trebui să fie întotdeauna o variabilă care să rețină valoarea din subprograme.

Acest tip de parametri trebuie menționat în momentul creării subprogramelor.

A REVENI

RETURN este cuvântul cheie care indică compilatorului 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 controlorul găsește cuvântul cheie RETURN în subprogram, codul de după acesta va fi omis.

În mod normal, blocul părinte sau principal va apela subprogramele, iar apoi controlul se va muta de la blocul părinte la subprogramele apelate. RETURN în subprogram va returna controlul înapoi în 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 PL/SQL valid.

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 Procedure în PL/SQL:

• Procedurile sunt blocuri independente ale unui program care pot fi stocate în Baza de date.
• Apelarea la aceste proceduri PLSQL se poate face referindu-se la numele lor, pentru a executa instrucțiunile PL/SQL.
• Este folosit î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 gestionare a excepțiilor (opțional).
• Valorile pot fi transmise în Oracle procedura sau preluate din procedură prin parametri.
• Acești parametri ar trebui să fie incluși în instrucțiunea de apelare.
• O procedură în SQL poate avea o instrucțiune RETURN pentru a returna controlul blocului apelant, dar nu poate returna nicio valoare prin instrucțiunea RETURN.
• Procedurile nu pot fi apelate direct din instrucțiunile SELECT. Ele pot fi apelate dintr-un alt bloc sau prin cuvântul cheie EXEC.

Sintaxă

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

• CREATE PROCEDURE instruiește compilatorul să creeze o nouă procedură în Oracle. Cuvântul cheie „SAU ÎNLOCUIT” indică compilației să înlocuiască procedura existentă (dacă există) cu cea actuală.
• Numele procedurii trebuie să fie unic.
• Cuvântul cheie „IS” va fi folosit atunci când procedura stocată în Oracle este imbricat în alte blocuri. Dacă procedura este independentă, atunci va fi folosit „AS”. În afară de acest standard de codare, ambele au același înțeles.

Exemplul 1: Crearea procedurii și apelarea acesteia folosind EXEC

În acest exemplu, vom crea un Oracle procedură care ia numele ca intrare și tipărește mesajul de bun venit ca rezultat. Vom folosi comanda EXEC pentru a apela procedura.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) 
IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXEC welcome_msg (‘Guru99’);

Explicația codului:

• Linia de cod 1: Crearea procedurii cu numele 'welcome_msg' și cu un parametru 'p_name' de tip 'IN'.
• Linia de cod 4: Imprimarea mesajului de bun venit prin concatenarea numelui introdus.
• Procedura este compilată cu succes.
• Linia de cod 7: Apelarea procedurii folosind comanda EXEC cu parametrul „Guru99”. Procedura este executată, iar mesajul este tipărit ca „Welcome 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 de sine stătător care este utilizat în principal în scopuri de calcul.
• Funcția folosește cuvântul cheie RETURN pentru a returna valoarea, iar tipul de date al acesteia 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 operare 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 gestionare a excepțiilor (opțional).
• Valorile pot fi trecute în funcție sau preluate din procedură prin intermediul parametrilor.
• Acești parametri ar trebui să fie incluși în instrucțiunea de apelare.
• O funcție PLSQL poate returna, de asemenea, valoarea prin parametrii OUT, alții decât folosirea RETURN.
• Deoarece va returna întotdeauna valoarea, în instrucțiunea de apelare însoțește întotdeauna un operator de atribuire pentru a popula variabilele.

Sintaxă

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 indică compilatorului să creeze o nouă funcție. Cuvântul cheie „SAU ÎNLOCUIȚI” indică compilatorului să înlocuiască funcția existentă (dacă există) cu cea actuală.
• Numele funcției ar trebui să fie unic.
• Trebuie menționat tipul de date RETURN.
• Cuvântul cheie „IS” va fi folosit atunci când procedura este imbricată în alte blocuri. Dacă procedura este independentă, atunci va fi folosit „AS”. În afară de acest standard de codare, ambele au același înțeles.

Exemplul 1: Crearea funcției și apelarea acesteia folosind Blocul anonim

În acest program, vom crea o funcție care ia numele ca intrare și returnează mesajul de bun venit ca rezultat. Vom folosi blocul anonim și instrucțiunea select pentru a apela funcția.

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;

Explicația codului:

• Linia de cod 1: Crearea Oracle funcție 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: returnează valoarea concatenata „Bun venit” și valoarea parametrului.
• Linia de cod 8: Bloc anonim pentru a apela funcția de mai sus.
• Linia de cod 9: Declararea variabilei cu tipul de date identic cu tipul de date returnat al funcției.
• Linia de cod 11: Apelarea funcției și popularea valorii returnate la variabila 'lv_msg'.
• Linia de cod 12: Imprimarea 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 către 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 gestionată în subprogram manipularea excepțiilor secțiunea, apoi se va propaga la blocul apelant.
• Ambele pot avea oricât de mulți parametri sunt necesari.
• Ambele sunt tratate ca obiecte de bază de date în PL/SQL.

Procedura vs. Funcție: Diferențele cheie

Procedură	Funcţie
Folosit în principal pentru a executa un anumit proces	Folosit în principal pentru a efectua unele calcule
Nu se poate apela în instrucțiunea SELECT	O funcție care nu conține instrucțiuni DML poate fi apelată în instrucțiunea SELECT
Utilizați parametrul OUT pentru a returna valoarea	Utilizați RETURN pentru a returna valoarea
Nu este obligatoriu să returnați valoarea	Este obligatoriu să returnați valoarea
RETURN va părăsi pur și simplu controlul din subprogram.	RETURN va ieși din control din subprogram și va returna, de asemenea, valoarea
Tipul de date returnate nu va fi specificat în momentul creării	Tipul de date returnat este obligatoriu la momentul creării

Funcții încorporate în PL/SQL

PL / SQL conține diverse funcții încorporate pentru a lucra cu șiruri de caractere și tip de date de date. Aici vom vedea funcțiile utilizate în mod obișnuit și utilizarea lor.

Funcții de conversie

Aceste funcții încorporate sunt utilizate pentru a converti un tip de date într-un alt tip de date.

Numele funcției	Folosire	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 data. Șirul trebuie să se potrivească cu formatul.	TO_DATE('2015-JAN-15', 'AAAA-LU-ZZ'); producție: 1 / 15 / 2015
TO_NUMBER (text, format)	Convertește textul în tipul de număr al formatului dat. Informația „9” denotă numărul de cifre	Selectați TO_NUMBER('1234′,'9999') din dual; producție: 1234 Selectați TO_NUMBER('1,234.45′,'9,999.99') din dual; producție: 1234

Funcții șiruri

Acestea sunt funcțiile care sunt utilizate pe tipul de date caracter.

Numele funcției	Folosire	Exemplu
INSTR(text, șir, început, apariție)	Oferă poziția unui anumit text în șirul dat. text – șirul principal șir – text care trebuie căutat start – poziția de pornire a căutării (opțional) conformitate – apariția șirului căutat (opțional)	Selectați INSTR('AEROPLAN','E',2,1) din dual producție: 2 Selectați INSTR('AEROPLAN','E',2,2) din dual producție: 9 (2nd apariția lui E)
SUBSTR ( text, început, lungime)	Oferă valoarea subșirului șirului principal. text – șir principal start – poziție de pornire lungime – lungime pentru a fi sub șiruri	selectați substr('avion',1,7) din dual producție: aeropla
SUS (text)	Returnează majusculele textului furnizat	Selectați upper('guru99') din dual; producție: GURU99
LOWER (text)	Returnează literele mici ale textului furnizat	Selectați mai jos („AerOpLane”) din dual; producție: avion
INITCAP (text)	Returnează textul dat cu litera de început în majuscule.	Selectați („guru99”) din dual producție: Guru99 Selectați („povestea mea”) din dual producție: Povestea mea
LUNGIME (text)	Returnează lungimea șirului dat	Selectați LENGTH („guru99”) din dual; producție: 6
LPAD (text, lungime, pad_char)	Adăugați șirul din partea stângă pentru lungimea dată (șirul total) cu caracterul dat	Selectați LPAD('guru99', 10, '$') din dual; producție: $$$$guru99
RPAD (text, lungime, pad_char)	Adăugați șirul din partea dreaptă pentru lungimea dată (șirul total) cu caracterul dat	Selectați RPAD('guru99′,10,'-') din dual producție: guru99—-
LTRIM (text)	Decupează spațiul alb principal din text	Selectați LTRIM('Guru99') din dual; producție: Guru99
RTRIM (text)	Decupează spațiul alb final din text	Selectați RTRIM('Guru99 ') din dual; producție; Guru99

Funcții de dată

Acestea sunt funcții care sunt folosite pentru manipularea cu date.

Numele funcției	Folosire	Exemplu
ADD_MONTHS (data, nr. de luni)	Adaugă lunile date la dată	ADD_MONTH('2015-01-01',5); producție: 05 / 01 / 2015
SYSDATE	Returnează data și ora curente ale serverului	Selectați SYSDATE din dual; producție: 10 4:2015:2
TRUNC	Rotunjiți variabilei date la valoarea mai mică posibilă	selectați sysdate, TRUNC(sysdate) din dual; producție: 10 4:2015:2 12
RUNDA	Rotunjește data la cea mai apropiată limită, fie mai mare, fie mai mică	Selectați sysdate, ROUND(sysdate) din dual producție: 10 4:2015:2 14
MONTHS_BETWEEN	Returnează numărul de luni dintre două date	Selectați MONTHS_BETWEEN (sysdate+60, sysdate) din dual producție: 2

Rezumat

În acest capitol, am învățat următoarele.

• Cum să creați Procedure și diferite moduri de a o apela
• Cum se creează funcția și diferite moduri de a o apela
• Asemănări și diferențe între procedură și funcție
• Parametri și terminologii comune RETURN în subprogramele PL/SQL
• Funcții comune încorporate în Oracle PL / SQL