Procedimientos almacenados en PL/SQL Jonathan Medina Gómez Facultad de Ingeniería Universidad de Antioquia

Ventajas... ● Los bloques se pueden almacenar en forma permanente mediante subprogramas (funciones y procedimientos) para usarlos repetidamente. ● Los subprogramas pueden llevar argumentos (parámetros).

Sea la tabla: CREATE TABLE registro( id_usuario VARCHAR2(10), fecha DATE, estacion VARCHAR2(15) ); CREATE OR REPLACE PROCEDURE registrarse IS BEGIN INSERT INTO registro VALUES (USER, SYSDATE, USERENV('TERMINAL')); END; /

Para ejecutarlo en SQL*Plus: EXECUTE registrarse;

Procedimientos Sintaxis: CREATE [OR REPLACE] PROCEDURE nombre_procedimiento [( arg1 [modo] tipo [, arg2 [modo] tipo...])] IS | AS [] BEGIN [EXCEPTION] END;

A tener en cuenta... ● Se debe especificar la opción REPLACE cuando ya exista el procedimiento y se desee reemplazar. ● Se puede usar AS o IS (son equivalentes). ● El bloque PL/SQL empieza, ya sea con la palabra BEGIN, o con la declaración de las variables locales (sin usar la palabra DECLARE). ● Para ver los errores de compilación se puede usar el comando SHOW ERRORS en SQL*Plus. ● No se puede especificar tamaño para los parámetros en lo que respecta al tipo de datos.

A tener en cuenta... ● El "modo" especifica el tipo de argumento, el cual puede ser : ○ IN (el predeterminado): Parámetro de entrada al subprograma. ○ OUT: Parámetro de salida. El subprograma devuelve un valor en el parámetro. ○ IN OUT: Parámetro de entrada y salida. El subprograma devolverá un valor posiblemente diferente al enviado originalmente.

Ejemplo CREATE OR REPLACE PROCEDURE consulta_emp (v_nro IN emp.cod%TYPE) IS v_nom emp.nom%TYPE; BEGIN SELECT nom INTO v_nom FROM emp WHERE cod = v_nro; DBMS_OUTPUT.PUT_LINE(v_nom); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Empleado no existe'); END; /

Parámetros de salida Si el procedimiento retorna un parámetro de salida se debe declarar una variable en SQL*Plus para verlo, así: ● VAR nom_var TIPO; ● Invocar el subprograma con los parámetros correspondientes (las vbles de SQL*PLUS se preceden con ‘:’ ). ● Imprimir: PRINT nom_var; (no se requiere ‘:’ para imprimir). ● Los parámetros de salida usualmente son recibidos por otros subprogramas que los invocan.

Ejemplo CREATE OR REPLACE PROCEDURE consulta_emp (v_nro IN emp.cod%TYPE, v_nom OUT emp.nom%TYPE) IS BEGIN SELECT nom INTO v_nom -- Se llena el parámetro de salida FROM emp WHERE cod = v_nro; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Empleado no existe'); END; /

Invocación desde SQL*Plus: SQL> VAR a VARCHAR2(10); SQL> EXECUTE consulta_emp(15, :a); SQL> PRINT a;

Invocación desde un subprograma CREATE OR REPLACE PROCEDURE invoca_consulta(v_nro IN emp.cod%TYPE) IS nombre emp.nom%TYPE; Retornará la variable nombre BEGIN consulta_emp(v_nro, nombre); DBMS_OUTPUT.PUT_LINE('El nombre del empleado es: '|| nombre); END; / Para ejecutar: EXECUTE invoca_consulta(15);

Ejemplo ● Sea el modelo:

Instrucciones (DDL) Sean las tablas: CREATE TABLE cliente( ced NUMBER(8) PRIMARY KEY, nom VARCHAR2(10) NOT NULL); CREATE TABLE empleo( ced NUMBER(8) REFERENCES cliente, nit_empresa INTEGER, valor_mensual NUMBER(6) NOT NULL, PRIMARY KEY (ced, nit_empresa)); CREATE TABLE gasto( cod_gasto NUMBER(8) PRIMARY KEY, ced NUMBER(8) REFERENCES cliente, valor_mensual NUMBER(6), desc_gasto VARCHAR2(10));

Realizar... Los subprogramas necesarios para imprimir la cédula de cada cliente y la diferencia entre todo lo que devenga y todo lo que se gasta.

Soluciones Versión 1: Total gastos CREATE OR REPLACE PROCEDURE totalg( codigo cliente.ced%TYPE, total OUT NUMBER) IS BEGIN SELECT NVL(SUM(valor_mensual),0) INTO total FROM gasto WHERE ced = codigo; END; /

Total ingresos CREATE OR REPLACE PROCEDURE totali( codigo cliente.ced%TYPE, total OUT NUMBER) IS BEGIN SELECT NVL(SUM(valor_mensual),0) INTO total FROM empleo WHERE ced = codigo; END; /

Soluciones Versión 2: CREATE OR REPLACE PROCEDURE total( codigo cliente.ced%TYPE, tabla VARCHAR, total OUT NUMBER) IS BEGIN EXECUTE IMMEDIATE 'SELECT NVL(SUM(valor_mensual),0) FROM ' || tabla || ' WHERE ced = ' || codigo INTO total; END; / * Compacto pero puede ser peligroso en rendimiento...

Solución completa Por lo tanto la solución completa usando el procedimiento total anterior es: CREATE OR REPLACE PROCEDURE neto IS totalg NUMBER(8); totali NUMBER(8); BEGIN FOR mis_emp IN (SELECT * FROM cliente) LOOP total(mis_emp.ced,'empleo',totali); total(mis_emp.ced,'gasto',totalg); DBMS_OUTPUT.PUT_LINE(mis_emp.ced ||' Total neto: '|| (totali - totalg)); END LOOP; END; /

¿Cómo hubiera sido la respuesta del problema usando sólo SQL puro? ●

Mediante subconsultas correlacionadas:

SELECT ced,((SELECT NVL(SUM(valor_mensual),0) FROM empleo WHERE ced = c.ced) ( SELECT NVL(SUM(valor_mensual),0) FROM gasto WHERE ced = c.ced)) AS total FROM cliente c; Pero las subconsultas correlacionadas son en general costosas...

¿Cómo hubiera sido la respuesta del problema usando sólo SQL puro? Mediante OUTER JOINS: SELECT ced, (NVL(sumae,0) - NVL(sumag,0)) AS total FROM ( cliente NATURAL LEFT OUTER JOIN (SELECT ced, SUM(valor_mensual) AS sumae FROM empleo GROUP BY ced)) NATURAL LEFT OUTER JOIN (SELECT ced, SUM(valor_mensual) AS sumag FROM gasto GROUP BY ced );

Ejercicio en clase ● Realice un procedimiento almacenado que reciba como parámetros el nombre y la cédula de un cliente, el nit de la empresa en la cual trabaja y el salario que gana, tenga en cuenta lo siguiente: ○ Si el registro (cliente, empleo) no existe se debe crear. ○ Si el registro ya existe se debe modificar. ○ Se debe mostrar un mensaje al usuario para que sepa cuál de las operaciones fue la que se realizó. ○ Tenga en cuenta las posibles excepciones!!!