Funciones y Procedimientos en PL/SQL: Ejemplos Prácticos con Oracle

Funciones y procedimientos en PL/SQL

A continuación se presentan ejemplos prácticos de funciones y procedimientos almacenados en PL/SQL sobre una tabla LICEO.

1. Función de suma

Crear una función que devuelva la suma de dos números:

CREATE OR REPLACE FUNCTION FN_SUMA(P_NUM1 NUMBER, P_NUM2 NUMBER) RETURN NUMBER
IS
  V_SUMA NUMBER := 0;
BEGIN
  V_SUMA := P_NUM1 + P_NUM2;
  RETURN V_SUMA;
END;
/

SELECT FN_SUMA(7,8) FROM DUAL;

DECLARE
  V_RESULTADO NUMBER := 0;
BEGIN
  V_RESULTADO := FN_SUMA(7,8);
  DBMS_OUTPUT.PUT_LINE('LA SUMA ES: ' || V_RESULTADO);
END;

2. Función que determina si un empleado está jubilado

Crear una función que permita determinar si un empleado está jubilado según su edad:

CREATE OR REPLACE FUNCTION FN_JUBILA(P_EDAD NUMBER) RETURN VARCHAR2
IS
  V_RESULTADO VARCHAR2(30);
BEGIN
  IF P_EDAD > 65 THEN
    V_RESULTADO := 'JUBILÓ';
  ELSE
    V_RESULTADO := 'NO JUBILÓ';
  END IF;
  RETURN V_RESULTADO;
END;
/

SELECT FN_JUBILA(33) FROM DUAL;

3. Función que suma las horas adjudicadas de un profesor

Crear una función que sume las horas adjudicadas de un profesor:

SELECT NOMBRE, HRS_ADJUDICADAS FROM LICEO WHERE NOMBRE = 'Abreu Adriana';
-- ORDER BY NOMBRE;
-- SELECT SUM(HRS_ADJUDICADAS) FROM LICEO WHERE NOMBRE = 'Abreu Adriana';

CREATE OR REPLACE FUNCTION FN_TOTAL_HRS(P_NOMBRE VARCHAR2) RETURN NUMBER
IS
  V_TOTAL NUMBER := 0;
BEGIN
  -- SELECT SUM(HRS_ADJUDICADAS) INTO V_TOTAL FROM LICEO WHERE NOMBRE = P_NOMBRE;
  SELECT SUM(HRS_ADJUDICADAS) INTO V_TOTAL FROM LICEO WHERE UPPER(NOMBRE) = UPPER(P_NOMBRE);
  RETURN V_TOTAL;
END;
/

SELECT FN_TOTAL_HRS('Abreu Adriana') FROM DUAL;

4. Función que presenta el total de profesores de un liceo

Crear una función que devuelva el número de profesores de un liceo en particular:

-- SELECT LICEO, NOMBRE FROM LICEO WHERE LICEO = 'FDS';
SELECT COUNT(NOMBRE) FROM LICEO WHERE LICEO = 'FDS';

CREATE OR REPLACE FUNCTION FN_NUM_PROFE(P_LICEO VARCHAR2) RETURN NUMBER
IS
  V_TOTAL NUMBER := 0;
BEGIN
  SELECT COUNT(NOMBRE) INTO V_TOTAL FROM LICEO WHERE UPPER(LICEO) = UPPER(P_LICEO);
  RETURN V_TOTAL;
END;
/

SELECT FN_NUM_PROFE('PIRIÁPOLIS') FROM DUAL;

5. Función que cuenta profesores por rango de fechas de ingreso

Crear una función que presente la cantidad de profesores que ingresaron entre dos fechas:

SELECT COUNT(NOMBRE) FROM LICEO WHERE FECHA_INGRESO BETWEEN '01-01-2005' AND '31-12-2005';

CREATE OR REPLACE FUNCTION FN_NUM_FECHA(P_FI DATE, P_FT DATE) RETURN NUMBER
IS
  V_TOTAL NUMBER := 0;
BEGIN
  SELECT COUNT(NOMBRE) INTO V_TOTAL FROM LICEO WHERE FECHA_INGRESO BETWEEN P_FI AND P_FT;
  RETURN V_TOTAL;
END;
/

SELECT FN_NUM_FECHA('01-01-2000','31-12-2005') FROM DUAL;

6. Procedimiento que lista profesores por asignatura

Crear un procedimiento que muestre en columnas el nombre y liceo de los profesores de una asignatura en especial:

CREATE OR REPLACE PROCEDURE PR_LISTA_ASIG(P_ASIGNATURA VARCHAR2)
IS
  CURSOR C_PROFE IS
    SELECT NOMBRE, LICEO FROM LICEO WHERE UPPER(ASIGNATURA) = UPPER(P_ASIGNATURA);
  REG_PROFE C_PROFE%ROWTYPE;
BEGIN
  OPEN C_PROFE;
  LOOP
    FETCH C_PROFE INTO REG_PROFE;  -- FETCH funciona como un FOREACH
    EXIT WHEN C_PROFE%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(RPAD(REG_PROFE.NOMBRE, 30) || RPAD(REG_PROFE.LICEO, 30));
  END LOOP;
  CLOSE C_PROFE;
END;
/

EXECUTE PR_LISTA_ASIG('LITERATURA');

7. Procedimiento para modificar las horas adjudicadas de un profesor

Crear un procedimiento que permita modificar las horas adjudicadas de un profesor en un liceo:

SELECT NOMBRE, LICEO, HRS_ADJUDICADAS FROM LICEO
WHERE NOMBRE = 'Diaz Walter' AND LICEO = 'Barros Blancos';

UPDATE LICEO SET HRS_ADJUDICADAS = 40
WHERE NOMBRE = 'Diaz Walter' AND LICEO = 'Barros Blancos';

CREATE OR REPLACE PROCEDURE PR_CAMBIO_HRS(P_PROFESOR VARCHAR2, P_LICEO VARCHAR2, P_HRS NUMBER)
IS
BEGIN
  UPDATE LICEO SET HRS_ADJUDICADAS = P_HRS
  WHERE UPPER(NOMBRE) = UPPER(P_PROFESOR) AND UPPER(LICEO) = UPPER(P_LICEO);
  DBMS_OUTPUT.PUT_LINE('ACTUALIZACIÓN REALIZADA');
END;
/

EXECUTE PR_CAMBIO_HRS('DIAZ WALTER', 'BARROS BLANCOS', 30);

8. Procedimiento que organiza los profesores por liceo

Crear un procedimiento que organice en grupos a los profesores por liceo, mostrando nombre, asignatura y carácter (efectivo o suplente):

SELECT NOMBRE, LICEO, CARACTER, ASIGNATURA FROM LICEO WHERE LICEO = 'FDS';
SELECT LICEO FROM LICEO GROUP BY LICEO;

CREATE OR REPLACE PROCEDURE PR_PROF_LICEO
IS
  CURSOR C_LICEO IS SELECT LICEO FROM LICEO GROUP BY LICEO;
  CURSOR C_PROFE(P_LICEO VARCHAR2) IS
    SELECT NOMBRE, LICEO, CARACTER, ASIGNATURA FROM LICEO WHERE LICEO = P_LICEO;
  REG_LICEO C_LICEO%ROWTYPE;
  REG_PROFE C_PROFE%ROWTYPE;
BEGIN
  OPEN C_LICEO;
  LOOP
    FETCH C_LICEO INTO REG_LICEO;
    EXIT WHEN C_LICEO%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('LICEO: ' || REG_LICEO.LICEO);
    DBMS_OUTPUT.PUT_LINE('-------------------------');
    OPEN C_PROFE(REG_LICEO.LICEO);
    LOOP
      FETCH C_PROFE INTO REG_PROFE;
      EXIT WHEN C_PROFE%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(RPAD(REG_PROFE.NOMBRE, 30) || RPAD(REG_PROFE.ASIGNATURA, 30) || RPAD(REG_PROFE.CARACTER, 20));
    END LOOP;
    CLOSE C_PROFE;
  END LOOP;
  CLOSE C_LICEO;
END;
/

EXECUTE PR_PROF_LICEO;