/* Sesión10 – Funciones y ProcedimientoEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:i201010865@cibertec.edu.pe*/

Objetivos de la sesión.

Crear e invocar funciones propias con PL/SQL. Crear e invocar procedimientos con PL/SQL.

1) Creación de funciones y procedimientos.2) Subprogramas locales.3) Programas almacenados y el diccionario de datos.

1) Creación de funciones y procedimientos.

a) Tipos de bloques

Existen dos tipos principales de bloques PL/SQL, anónimos y nominados.

Los bloques anónimos, que empiezan con DECLARE o BEGIN, se compilan cada vez que se ejecutan, no se almacenan en la base de datos y no pueden llamarse directamente desde otros bloques PL/SQL.

Los bloques nominados se pueden almacenar en la base de datos, pueden ser ejecutados cuando sea conveniente y ser invocados desde otros bloques PL/SQL. Ejemplo Funciones, procedimientos, paquetes y disparadores.

b) Procedimiento y funciones

Los procedimientos y funciones PL/SQL se comportan de forma muy parecida a los procedimientos y funciones de otros lenguajes de tercera generación, compartiendo muchas de sus propiedades.

Los procedimientos y funciones también se conocen con el nombre de subprogramas.

c) Procedimientos

En primer lugar creamos el procedimiento con la instrucción CREATE OR REPLACE PROCEDURE

En la llamada al procedimiento le pasamos un parámetro, en el caso del ejemplo es un mensaje.

Un subprograma es un bloque PL/SQL con una sección declarativa, una sección ejecutable y una sección de tratamiento de errores, donde únicamente la sección ejecutable es obligatoria.

Sintaxis Explicación

CREATE [OR REPLACE] PROCEDURE nombre_procedimiento : Es el nombre del procedimiento que se

nombre_procedimiento [(argumento [IN|OUT |INOUT ] tipo....[(argumento [IN|OUT |INOUT ] tipo) ] {IS |AS} Cuerpo_procedimiento;

quiere crear. argumento : Es el nombre de un

parámetro del procedimiento. tipo : Es el tipo del parámetro. cuerpo_procedimiento : Es un bloque

PL/SQL que constituye el código del procedimiento.

Ejemplo1:

SQL> -- CREACIÓN CREATE OR REPLACE PROCEDURE SP_IMPRIME (MENSAJE VARCHAR2) AS BEGIN DBMS_OUTPUT.PUT_LINE(MENSAJE); END; /

Procedimiento creado.Procedimiento PL/SQL terminado correctamente.

SQL> -- LLAMADA AL SUB-PROGRAMA BEGIN SP_IMPRIME ('Este es mi primer procedimiento'); END; /Este es mi primer procedimientoProcedimiento PL/SQL terminado correctamente.

Ejemplo2:

SQL> -- CREAMOS UNA SECUENCIA CREATE SEQUENCE SEQ_DEPT START WITH 50 INCREMENT BY 10;

Secuencia creada.

SQL> -- PROCEDIMIENTO QUE INSERTA UN NUEVO DEPARTAMENTO CREATE OR REPLACE PROCEDURE SP_INSERTA_DEPT (V_DNAME DEPT.DNAME%TYPE,V_LOC DEPT.LOC%TYPE) AS -- Sección declarativa V_DEPTNO DEPT.DEPTNO%TYPE; -- Sección Ejecutable BEGIN SELECT SEQ_DEPT.NEXTVAL INTO V_DEPTNO FROM DUAL; INSERT INTO DEPT(DEPTNO,DNAME,LOC)

VALUES(V_DEPTNO,V_DNAME,V_LOC); COMMIT; -- Sección de excepciones EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Ya existe un departamento con el código generado'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error desconocido'); END; /

Procedimiento creado.

SQL> -- LLAMAMOS AL SUBPROGRAMA BEGIN SP_INSERTA_DEPT('José','Lima'); END; /

Procedimiento PL/SQL terminado correctamente.

SQL> -- COMPROBAMOS SELECT * FROM DEPT;

DEPTNO DNAME LOC---------- -------------- ------------- 50 José Lima 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

d) Funciones

Sintaxis Explicación

CREATE [OR REPLACE] FUNCTION nombre_función

[(argumento [IN|OUT |INOUT ] tipo....[(argumento [IN|OUT |INOUT ] tipo) ] RETURN tipo_retorno {IS |AS} Cuerpo_función;

nombre_función : Es el nombre de la función que se quiere crear.

argumento : Es el nombre de un parámetro de la función.

tipo : Es el tipo del parámetro. tipo_retorno : Es el tipo del valor que

devuelve la función. cuerpo_función : Es un bloque

PL/SQL que constituye el código de la función.

Dentro del cuerpo de una función, la instrucción RETURN, se utiliza para devolver el control, junto con un valor, al entorno que realizó la llamada.

Sintaxis:RETURN expresión;

Ejemplo:

SQL> -- FUNCIÓN QUE DEVUELVA EL NÚMERO DE EMPLEADOS POR DEPARTAMENTO CREATE OR REPLACE FUNCTION SF_NUMERO_EMP (V_DEPTNO DEPT.DEPTNO%TYPE) RETURN NUMBER AS V_NUMERO NUMBER(6); BEGIN SELECT COUNT(1) INTO V_NUMERO FROM EMP WHERE DEPTNO = V_DEPTNO; RETURN V_NUMERO; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR INESPERADO. ' || SQLERRM); RETURN -1; END; /

Funci¾n creada.

SQL> -- LLAMAMOS A LA FUNCIÓN A TRAVÉS DE UNA CONSULTA SELECT DEPTNO,DNAME, SF_NUMERO_EMP(DEPTNO) FROM DEPT;

DEPTNO DNAME SF_NUMERO_EMP(DEPTNO) --------- -------------- --------------------- 50 José 0 10 ACCOUNTING 4 20 RESEARCH 0 30 SALES 10 40 OPERATIONS 0

d) Eliminación de sub-programas

Eliminación de procedimientos:DROP PROCEDURE nombre_procedimiento;

Eliminación de funciones:DROP FUNCTION nombre_función;

e) Métodos de los parámetros

IN OUT IN OUTPasa un valor al subprograma Retorna una valor a quien

invoca el subprogramaPasa un valor al subprograma y retorna el valor actualizado

Puede ser una constante o variable

Debe ser una variable Debe ser una variable

Nota: Los procedimientos se utilizan para procesar mientras que las funciones para consultar y devolver un valor.

2) Subprogramas locales.

Los subprogramas locales no crean un objeto en la base de datos, solo tienen vigencia durante la ejecución del programa o subprograma que los crea e invoca.

Los subprogramas locales deben ser declarados al final de la sección declarativa.

Ejemplo:

DECLARECURSOR C_EMPS IS SELECT ENAME, JOB FROM EMP;

V_SALIDA_FORMATEADA VARCHAR2(50); -- Función que devolverá el nombre y job concatenados y separados por espacio FUNCTION FORMATEADOR (P_ENAME IN VARCHAR2, P_JOB IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN P_ENAME ||’ ‘|| P_JOB; END FORMATEADOR;BEGIN FOR V_EMP_RECORD IN C_EMPS LOOP V_SALIDA_FORMATEADA := FORMATEADOR(V_EMP_RECORD.ENAME, V_EMP_RECORD.JOB); DBMS_OUTPUT.PUT_LINE(V_SALIDA_FORMATEADA); END LOOP;END;/

3) Programas almacenados y el diccionario de datos.

La vista del diccionario de datos que contiene el código de un procedimiento o de una función es la vista ALL_SOURCE.

Problema1: Cree un procedimiento que inserta un Nuevo departamento y que devuelva el código del departamento ingresado y el resultado.

-- Procedimiento que inserta un nuevo departamento.CREATE OR REPLACE PROCEDURE SP_INSERTA_DEPT(V_DNAME DEPT.DNAME%TYPE, V_LOC DEPT.LOC%TYPE, V_RESULTADO OUT VARCHAR2)AS

V_DEPTNO DEPT.DEPTNO%TYPE;BEGIN

SELECT SEQ_DEPT.NEXTVAL INTO V_DEPTNO FROM DUAL;INSERT INTO DEPTVALUES(V_DEPTNO, V_DNAME, V_LOC);V_RESULTADO:='Departamento ' || V_DEPTNO || ' se grabó correctamente.';COMMIT;

EXCEPTIONWHEN DUP_VAL_ON_INDEX THEN

V_RESULTADO:='Ya existe un departamento con el código generado.';v_DEPTNO:=-1;

WHEN OTHERS THENV_RESULTADO:='Error desconocido ' || SQLERRM;v_DEPTNO:=-2;

END;/

-- Llamar al subprogramaDECLARE

V_RESULTADO VARCHAR(100);BEGIN

DBMS_OUTPUT.PUT_LINE(V_RESULTADO);SP_INSERTA_DEPT('FINANZAS','SAN LUIS',V_RESULTADO);

END;/

Problema2: Cree una función que retorne el total de ventas por empleado en un determinado rango de fechas y ordenado, luego llame a la función en una consulta.

-- Función que retorna total de ventas por empleadosCREATE OR REPLACE FUNCTION SF_VENTAS_EMP (V_EMPNO EMP.EMPNO%TYPE, V_FECHA1 DATE, V_FECHA2 DATE)RETURN NUMBERIS

V_TOT NUMBER(8,2);BEGIN

SELECT SUM(TOTAL) INTO V_TOTFROM ORD WHERE EMPNO=V_EMPNO AND ORDERDATE BETWEEN V_FECHA1 AND V_FECHA2ORDER BY 3 DESC;RETURN V_TOT;

EXCEPTIONWHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR INESPERADO.' || SQLERRM);RETURN –1;

END;/-- Llamar al subprogramaSELECT EMPNO,ENAME,SF_VENTAS_EMP(EMPNO) FROM EMP;