PL/SQL Functions Collection and Development Reference

PL/SQL Functions Repository

This document contains a collection of essential PL/SQL functions for Oracle databases, including arithmetic operations, string manipulation, and logical conditions.

1. Sum Function

CREATE OR REPLACE FUNCTION suma (NUM1 NUMBER, NUM2 NUMBER) RETURN NUMBER IS CUENTA NUMBER := 0; BEGIN CUENTA := NUM1 + NUM2; RETURN CUENTA; END;

2. Average Function

CREATE OR REPLACE FUNCTION media_dos (NUM1 NUMBER, NUM2 NUMBER) RETURN NUMBER IS CUENTA NUMBER := 0; BEGIN CUENTA := (NUM1 + NUM2) / 2; RETURN CUENTA; END;

3. Age Category Function

CREATE OR REPLACE FUNCTION categ_edat (edat NUMBER) RETURN VARCHAR2 IS categoria VARCHAR2(20); BEGIN IF edat < 18 THEN categoria := 'menor'; ELSIF edat BETWEEN 18 AND 65 THEN categoria := 'adult'; ELSE categoria := 'jubilat'; END IF; RETURN categoria; END categ_edat;

4. Maximum Number Function

CREATE OR REPLACE FUNCTION mayor (NUM1 NUMBER, NUM2 NUMBER) RETURN NUMBER IS BEGIN IF (NUM1 > NUM2) THEN RETURN NUM1; ELSE RETURN NUM2; END IF; END MAYOR;

5. Even Position Character Modification

CREATE OR REPLACE FUNCTION par_pornum (TEXTO VARCHAR2) RETURN VARCHAR2 IS CONCATENAR VARCHAR2(100) := ''; BEGIN FOR I IN 1..LENGTH(TEXTO) LOOP IF MOD(I,2) = 0 THEN CONCATENAR := CONCATENAR || '5'; ELSE CONCATENAR := CONCATENAR || SUBSTR(TEXTO, I, 1); END IF; END LOOP; RETURN CONCATENAR; END;

6. String Reversal Function

CREATE OR REPLACE FUNCTION alreves (TEXTO VARCHAR2) RETURN VARCHAR2 IS CONCATENAR VARCHAR2(100) := ''; BEGIN FOR I IN REVERSE 1..LENGTH(TEXTO) LOOP CONCATENAR := CONCATENAR || SUBSTR(TEXTO, I, 1); END LOOP; RETURN CONCATENAR; END;

7. Factorial Calculation

CREATE OR REPLACE FUNCTION factorial (n NUMBER) RETURN NUMBER IS resultat NUMBER := 1; BEGIN IF n = 0 THEN RETURN 1; END IF; FOR i IN 1..n LOOP resultat := resultat * i; END LOOP; RETURN resultat; END factorial;

8. Count Even Numbers in Range

CREATE OR REPLACE FUNCTION PARES (n1 NUMBER, n2 NUMBER) RETURN NUMBER IS CUANTOS NUMBER := 0; BEGIN FOR I IN N1..N2 LOOP IF MOD(I, 2) = 0 THEN CUANTOS := CUANTOS + 1; END IF; END LOOP; RETURN CUANTOS; END;

9. Character Padding Function

CREATE OR REPLACE FUNCTION caracter (NUM1 NUMBER, TEXTO VARCHAR2) RETURN VARCHAR2 IS LONGITUD NUMBER := LENGTH(TEXTO); LETRA VARCHAR2(100) := ''; BEGIN IF NUM1 = 1 THEN LETRA := '*'; ELSIF NUM1 = 2 THEN LETRA := '+'; ELSIF NUM1 = 3 THEN LETRA := '.'; ELSE LETRA := '-'; END IF; RETURN RPAD(LETRA, LONGITUD, LETRA); END;

10. Prime Number Checker

CREATE OR REPLACE FUNCTION primo (NUM NUMBER) RETURN VARCHAR2 IS BEGIN IF NUM = 1 THEN RETURN 'SI'; END IF; FOR i IN 2..NUM-1 LOOP IF (MOD(NUM, i) = 0) THEN RETURN 'NO'; END IF; END LOOP; RETURN 'SI'; END;

11. Text Transformation Function

CREATE OR REPLACE FUNCTION pol1 (TEXTO VARCHAR2) RETURN VARCHAR2 IS resultado VARCHAR2(100) := ''; BEGIN FOR i IN REVERSE 1..LENGTH(TEXTO) LOOP IF (SUBSTR(TEXTO, i, 1) <> ' ') THEN resultado := resultado || SUBSTR(TEXTO, i, 1); END IF; END LOOP; RETURN TRANSLATE(resultado, 'aeiou', '66666'); END;

12. Compare String Lengths

CREATE OR REPLACE FUNCTION MASLARGA (PAL1 VARCHAR2, PAL2 VARCHAR2) RETURN NUMBER IS BEGIN IF (LENGTH(PAL1) >= LENGTH(PAL2)) THEN RETURN LENGTH(PAL1); ELSE RETURN LENGTH(PAL2); END IF; END;