Taller PL/SQL: Bloques Anónimos, Procedimientos Almacenados, Funciones y Triggers sobre una base de datos Oracle 19C - $> SoloCodigoWeb Saltar al contenido

Taller PL/SQL: Bloques Anónimos, Procedimientos Almacenados, Funciones y Triggers sobre una base de datos Oracle 19C

Publicado por Gonzalo Chacaltana el 20/01/2020

Esta publicación es la continuación de «Taller SQL: Consultas SQL en Oracle Database 19C usando Oracle Live SQL». El objetivo de esta publicación es abordar los conceptos y sintaxis de PL/SQL.

Como se explica en la publicación anterior, no es necesario que instales el motor de base de datos Oracle 19C en tu computador, ya que vamos a utilizar la plataforma de entrenamiento de Oracle Live SQL, para trabajar los ejercicios propuestos.

¿Qué es PL/SQL?

A diferencia de SQL (Structure Query Language), PL/SQL es un lenguaje de programación de tipo procedimental. Las siglas PL y SQL de su nombre significan: «Procedural Language / Structure Query Language«.

Debes tener en cuenta que, PL/SQL fue desarrollado por la empresa Oracle Inc para dotar de mayores características y herramientas al SQL convencional, convirtiéndolo en un potente lenguaje de consulta y manipulación de base de datos Oracle.

Podemos decir que, el lenguaje PL/SQL viene incorporado en el servidor de base de datos Oracle y forma parte de la suite de herramientas que ofrece Oracle a sus millones de clientes.

Es precisamente con el lenguaje PL/SQL que podemos crear estructuras para consultar y manipular base de datos Oracle, como por ejemplo:

  • Bloques anónimos
  • Procedimientos Almacenados
  • Funciones
  • Triggers

Antes de continuar, sugerimos tener en cuenta lo siguiente:

  1. Tener una cuenta de usuario en Oracle Live.
  2. Tener conocimientos básicos sobre diseño de base de datos y sentencias SQL. Para este punto y el primero, puedes revisar esta publicación «Taller SQL: Consultas SQL en Oracle Database 19C usando Oracle Live SQL»
  3. Y como última sugerencia, tener un conocimiento básico sobre programación.

¿Qué es un procedimiento almacenado?

Un procedimiento almacenado, es un bloque de código declarativo de SQL o PL/SQL que se almacena en el catálogo de la base de datos y puede ser invocado posteriormente por un programa, un trigger o incluso otro procedimiento almacenado.

La ventaja de trabajar con procedimientos almacenados es que la definición y sintaxis del SQL o PL/SQL ya se encuentran interpretados por la instancia de una base de datos, mejorando los tiempos de respuesta al consumidor (aplicación o agente que solicita información)

¿Qué es un trigger?

Un trigger o disparador (en español) son bloques de código que se ejecutan de manera automática cuando ocurre un evento en la base de datos o en algún objeto de la base de datos. Por ejemplo, un trigger puede ejecutarse antes o después que ocurra un evento en la base de datos (inserción, actualización o eliminación de registros de una tabla).

Los ejercicios abordarán los siguientes temas:

  • Declaración de variables
  • Estructuras de control (IF, LOOP, WHILE, FOR)
  • Manejo de cursores
  • Manipulación de Excepciones
  • Creación de Bloques Anónimos
  • Creación de Procedimientos Almacenados
  • Creación de Funciones
  • Creación de Triggers
  • Código PL/SQL

A continuación, se detalla el enunciado para cada ejercicio y el script de la solución. Iniciaremos con ejercicios básicos (que no necesitan una base de datos), hasta ejercicios que devuelvan informes y/o se ejecuten cuando ocurra un evento en la base de datos.

Ejercicio 01 : Funciones Matemáticas

Crear un programa que calcule la distancia de dos puntos de un plano cartesiano (x,y). Dado los valores, x = (3, 2); y = (5, 8). Mostrar en pantalla la distancia de los puntos x, y.

DECLARE
    v_point_x1 NUMBER := 3;
    v_point_x2 NUMBER := 2;
    v_point_y1 NUMBER := 5;
    v_point_y2 NUMBER := 8;
    v_distance DECIMAL(6,2);
BEGIN
    v_distance := SQRT(POWER((v_point_x1-v_point_x2),2)+POWER((v_point_y1-v_point_y2),2));
    DBMS_OUTPUT.PUT_LINE('CALCULAR LA DISTANCIA DE DOS PUNTOS');
    DBMS_OUTPUT.PUT_LINE('************************************');
    DBMS_OUTPUT.PUT_LINE('Punto X : (' || v_point_x1||','||v_point_x2||')');
    DBMS_OUTPUT.PUT_LINE('Punto Y : (' || v_point_y1||','||v_point_y2||')');
    DBMS_OUTPUT.PUT_LINE('Distancia XY : ' || v_distance);
END;

Ejercicio 02 : Sentencia Loop While

Desarrollar un programa que sume «n» veces un número aleatorio.
Imprimir en pantalla los números aleatorios generados y la suma obtenida.
La cantidad de iteraciones deberá generarse de manera aleatoria, considerando los valores del 1 al 10. Los números aleatorios generados en cada iteración deberán ser del 10 al 100.

DECLARE
    v_sum NUMBER := 0;
    v_x NUMBER :=0;
    v_num NUMBER;
	v_limit NUMBER;
BEGIN
	v_limit := FLOOR(DBMS_RANDOM.value(1,10));
    LOOP
        v_x := v_x + 1;
        v_num := FLOOR(DBMS_RANDOM.value(10,100));
        DBMS_OUTPUT.PUT_LINE('Número aleatorio '||v_x||' -> '|| v_num);
        v_sum := v_sum + v_num;
    EXIT WHEN v_x = v_limit;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Suma Total = '||v_sum);
END;

Ejercicio 03 : Sentencia FOR

Desarrollar un programa que devuelva la cantidad de números múltiplos de 3 que existen del 1 al 100.

DECLARE
    v_multiple_3 NUMBER := 0;
BEGIN
    FOR v_num IN 1..100 LOOP
        IF (MOD(v_num,3)=0) THEN
            v_multiple_3 := v_multiple_3 + 1;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Números multiplos de 3 del 1 al 100: '||v_multiple_3);
END;
-- Output:
-- Números multiplos de 3 del 1 al 100: 3

Ejercicio 04: Cursores

Elabore un bloque anónimo PL/SQL que almacene en un objeto cursor la lista de empleados (código, nombres, apellidos y salario) que tiene un sueldo menor e igual $ 2,800. Mostrar el resultado por cada registro. Utilizar el esquema de base de datos HR (Human Resources) que viene por defecto en Live SQL.

DECLARE
    CURSOR employees_cursor IS
        SELECT employee_id,first_name,last_name,salary FROM hr.employees WHERE salary<=2800;
    v_id hr.employees.employee_id%TYPE;
    v_name hr.employees.first_name%TYPE;
    v_surname hr.employees.last_name%TYPE;
    v_salary hr.employees.salary%TYPE;
    v_num NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE(LPAD('-',50,'-'));
    DBMS_OUTPUT.PUT_LINE('INFORME DE EMPLEADOS');
    DBMS_OUTPUT.PUT_LINE(LPAD('-',50,'-'));
    OPEN employees_cursor;
    LOOP
        FETCH employees_cursor INTO v_id,v_name,v_surname,v_salary;
        EXIT WHEN employees_cursor%NOTFOUND;
        v_num := v_num+1;
        DBMS_OUTPUT.PUT_LINE('[' || v_num || '] Empleado N° '|| v_id || ' - ' || v_name || ' ' || v_surname || ' tiene un sueldo de' ||TO_CHAR(v_salary,'$99,999.00'));
    END LOOP;
    CLOSE employees_cursor;
END;

Ejercicio 05: Sentencia IF

Elaborar un bloque anónimo PL/SQL que devuelva un reporte de los empleados que tienen un salario menor e igual a $ 2,800.00. El reporte debe mostrarse agrupado por departamentos. Los valores de la columna deben ser por ancho fijo.

DECLARE
    CURSOR dpto_employees_cursor IS
        SELECT e.department_id,d.department_name,e.employee_id,e.first_name,e.last_name,e.salary 
        FROM hr.employees e
        INNER JOIN hr.departments d ON e.department_id=d.department_id
        WHERE salary<2800
        ORDER BY d.department_name DESC, salary DESC;
    v_last_department_id hr.departments.department_id%TYPE:=0;
    v_num NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE(LPAD('-',70,'-'));
    DBMS_OUTPUT.PUT_LINE('INFORME DE EMPLEADOS POR DEPARTAMENTOS');
    DBMS_OUTPUT.PUT_LINE(LPAD('-',70,'-'));
    FOR emp IN dpto_employees_cursor
    LOOP
        IF v_last_department_id<>emp.department_id THEN
            DBMS_OUTPUT.PUT_LINE(chr(13)||'DEPARTAMENTO: ' || UPPER(emp.department_name));
            DBMS_OUTPUT.PUT_LINE(LPAD('-',70,'-'));
        ELSE
            DBMS_OUTPUT.PUT_LINE(rpad(emp.employee_id, 10) || rpad(emp.first_name|| ' ' || emp.last_name, 20) ||TO_CHAR(emp.salary,'$999,999.00'));
        END IF;
        v_last_department_id:=emp.department_id;
    END LOOP;
END;

Base de datos de ventas

Para los siguientes ejercicios utilizaremos una propia base de datos de ventas, debido a que no contamos con los permisos de alterar la información de los esquemas de base de datos que nos proporciona Oracle Live SQL.

Copia las siguientes sentencias SQL y pégalo en la ventana de trabajo de la herramienta para finalmente ejecutar las sentencias.

-- Ventas Database SQL DUMP
-- version 1.1.2
-- source: www.solocodigoweb.com
-- author: Gonzalo Chacaltana Buleje
-- Servidor: Oracle Database 19c Enterprise Edition Release
-- Versión del servidor: 19.4.0.0.0

-- CREANDO TABLA CLIENTES

CREATE TABLE clientes (
  cod_cliente CHAR(5) NOT NULL PRIMARY KEY,
  nombres VARCHAR2(30) NOT NULL,
  distrito VARCHAR2(30) NULL,
  telefono VARCHAR2(10) NULL
);

-- CREANDO TABLA PRODUCTOS
CREATE TABLE productos (
  cod_producto CHAR(5) NOT NULL PRIMARY KEY,
  descripcion VARCHAR2(30) NULL,
  precio_unitario NUMBER(9,2) NULL
);

-- CREANDO TABLA FACTURAS
CREATE TABLE facturas (
  cod_factura CHAR(5) NOT NULL PRIMARY KEY,
  cod_cliente CHAR(5) NOT NULL,
  fecha_emision DATE NULL,
  importe_total DECIMAL(9,2) NULL
);

-- CREANDO TABLA DETALLE_FACTURAS
CREATE TABLE detalle_facturas (
  cod_factura CHAR(5) NOT NULL,
  cod_producto CHAR(5) NOT NULL,
  cantidad SMALLINT NOT NULL,
  subtotal DECIMAL(9,2) NOT NULL
);

-- AGREGANDO RELACIONES Y CLAVES PRIMARIAS

ALTER TABLE facturas
ADD FOREIGN KEY (cod_cliente)
REFERENCES clientes(cod_cliente);

ALTER TABLE detalle_facturas
ADD PRIMARY KEY (cod_factura,cod_producto);

ALTER TABLE detalle_facturas
ADD FOREIGN KEY (cod_factura)
REFERENCES facturas(cod_factura);

ALTER TABLE detalle_facturas
ADD FOREIGN KEY (cod_producto)
REFERENCES productos(cod_producto);

-- INSERTANDO REGISTROS A LA TABLA CLIENTES

INSERT INTO clientes
  VALUES('C0001','Julián Pérez','Lince','3214568');
INSERT INTO clientes
  VALUES('C0002','Maria Chavez','Jesus Maria','4215678');
INSERT INTO clientes
  VALUES('C0003','Juan Chacaltana','Comas','5416321');
INSERT INTO clientes
  VALUES('C0004','Guadalupe Fernández','Cercado','5216364');
INSERT INTO clientes
  VALUES('C0005','José Gómez','Lince','3458974');
INSERT INTO clientes
  VALUES('C0006','Carlos Segura','Rimac','4562312');
INSERT INTO clientes
  VALUES('C0007','Javier Buleje','Lince','3218974');
INSERT INTO clientes
  VALUES('C0008','Mary Huaman','Cercado','5631278');
INSERT INTO clientes
  VALUES('C0009','Cristian Sánchez','Cercado','5410591');
INSERT INTO clientes
  VALUES('C0010','Flor Paz','Lince','3564165');

-- INSERTANDO REGISTROS A LA TABLA PRODUCTOS

INSERT INTO productos
  VALUES('P0001','Memoria DDR 256 Mb',120.00);
INSERT INTO productos
  VALUES('P0002','Memoria DDR 500 Mb',190.00);
INSERT INTO productos
  VALUES('P0003','Mainboard PcChip v25',280.00);
INSERT INTO productos
  VALUES('P0004','Mainboard Intel V101',320.00);
INSERT INTO productos
  VALUES('P0005','Procesador Intel 3.6',390.00);
INSERT INTO productos
  VALUES('P0006','Procesador Intel 2.6',340.00);
INSERT INTO productos
  VALUES('P0007','Procesador Celeron 3.2',320.00);
INSERT INTO productos
  VALUES('P0008','Disco Duro SATA 80 GB',180.00);
INSERT INTO productos
  VALUES('P0009','Disco Duro SATA 120 GB',220.00);
INSERT INTO productos
  VALUES('P0010','Case Prezcom 500 Wats',120.00);
  
-- INSERTANDO REGISTROS A TABLA FACTURAS
INSERT INTO facturas
  VALUES('F0001','C0004',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0002','C0006',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0003','C0007',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0004','C0004',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0005','C0008',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0006','C0002',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0007','C0006',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0008','C0009',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0009','C0010',SYSDATE,0);
INSERT INTO facturas
  VALUES('F0010','C0004',SYSDATE,0);

-- INSERTANDO REGISTROS A LA TABLA DETALLE FACTURAS
INSERT INTO detalle_facturas
  VALUES('F0001','P0003',3,840.00);
INSERT INTO detalle_facturas
  VALUES('F0001','P0005',5,1950.00);
INSERT INTO detalle_facturas
  VALUES('F0001','P0006',2,680.00);
INSERT INTO detalle_facturas
  VALUES('F0001','P0009',1,220.00);
INSERT INTO detalle_facturas
  VALUES('F0002','P0009',3,660.00);
INSERT INTO detalle_facturas
  VALUES('F0002','P0005',8,3120.00);
INSERT INTO detalle_facturas
  VALUES('F0002','P0002',2,380.00);
INSERT INTO detalle_facturas
  VALUES('F0002','P0007',3,960.00);
INSERT INTO detalle_facturas
  VALUES('F0002','P0010',1,120.00);
INSERT INTO detalle_facturas
  VALUES('F0003','P0010',4,480.00);
INSERT INTO detalle_facturas
  VALUES('F0003','P0003',7,1960.00);
INSERT INTO detalle_facturas
  VALUES('F0003','P0006',2,680.00);
INSERT INTO detalle_facturas
  VALUES('F0003','P0009',1,220.00);
INSERT INTO detalle_facturas
  VALUES('F0004','P0005',3,1170.00);
INSERT INTO detalle_facturas
  VALUES('F0004','P0006',6,2040.00);
INSERT INTO detalle_facturas
  VALUES('F0004','P0007',8,2560.00);
INSERT INTO detalle_facturas
  VALUES('F0005','P0003',3,840.00);
INSERT INTO detalle_facturas
  VALUES('F0005','P0005',5,1950.00);
INSERT INTO detalle_facturas
  VALUES('F0005','P0002',1,190.00);
INSERT INTO detalle_facturas
  VALUES('F0006','P0007',3,960.00);
INSERT INTO detalle_facturas
  VALUES('F0006','P0002',2,380.00);
INSERT INTO detalle_facturas
  VALUES('F0007','P0003',3,840.00);
INSERT INTO detalle_facturas
  VALUES('F0007','P0008',4,720.00);
INSERT INTO detalle_facturas
  VALUES('F0008','P0003',3,840.00);
INSERT INTO detalle_facturas
  VALUES('F0008','P0006',1,340.00);
INSERT INTO detalle_facturas
  VALUES('F0009','P0003',3,840.00);
INSERT INTO detalle_facturas
  VALUES('F0010','P0009',5,1100.00);

Ejercicio 06: Procedimiento Almacenado

Crear un procedimiento almacenado para actualizar el precio de un producto que se envíe como parámetro de entrada.

CREATE OR REPLACE PROCEDURE actualizar_precio_producto
(v_cod_producto IN CHAR, v_nuevo_precio IN NUMBER)
IS
BEGIN
    UPDATE productos SET precio_unitario = v_nuevo_precio
    WHERE cod_producto = v_cod_producto;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Precio actualizado con éxito');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END actualizar_precio_producto;

-- EXECUTION
CALL actualizar_precio_producto('P0001',130);

Ejercicio 07: Iterar un cursor

Crear un procedimiento almacenado que devuelva la relación de clientes que no tienen una factura emitida a su nombre.

CREATE OR REPLACE PROCEDURE clientes_sin_facturas
IS
CURSOR report_cursor IS
    SELECT cod_cliente,nombres FROM clientes WHERE cod_cliente NOT IN (SELECT distinct(cod_cliente) FROM facturas);
BEGIN
    DBMS_OUTPUT.PUT_LINE('Clientes sin factura');
    FOR rep IN report_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(rep.cod_cliente,8)||rep.nombres);
    END LOOP;
END clientes_sin_facturas;

-- EXECUTION
CALL clientes_sin_facturas()

Ejercicio 08: Funciones

Crear una función que devuelva el nombre del producto más vendido.

CREATE OR REPLACE FUNCTION producto_mas_vendido
RETURN VARCHAR2
IS
    product_name VARCHAR2(30);
    CURSOR report IS
    SELECT p.descripcion "producto"
    FROM detalle_facturas fd
    INNER JOIN productos p ON fd.cod_producto=p.cod_producto
    GROUP BY p.descripcion
    ORDER BY SUM(fd.cantidad) DESC
    FETCH NEXT 1 ROWS ONLY;
BEGIN
    OPEN report;
    FETCH report INTO product_name;
    RETURN product_name;
END producto_mas_vendido;

-- PROBANDO
SELECT producto_mas_vendido FROM dual;

Ejercicio 09: Función de agrupación SUM

Escribir un procedimiento almacenado que permita actualizar el importe total de una factura, según el detalle que tenga asignado. El código de factura debe enviarse como parámetro de entrada.

CREATE OR REPLACE PROCEDURE actualiza_total_factura
(v_cod_factura IN CHAR)
IS
BEGIN
    UPDATE facturas SET importe_total=(
    SELECT SUM(subtotal) 
    FROM detalle_facturas WHERE cod_factura=v_cod_factura)
    WHERE cod_factura=v_cod_factura;
    DBMS_OUTPUT.PUT_LINE('Operación realizada con éxito');
END actualiza_total_factura;

-- EXECUTE
-- SELECT importe_total FROM facturas WHERE cod_factura='F0002'
CALL actualiza_total_factura('F0002');

Ejercicio 10: Subconsultas

Crear un procedimiento que permita insertar información al detalle de una factura.

CREATE OR REPLACE PROCEDURE insertar_detalle_factura(
v_cod_factura IN CHAR,
v_cod_producto IN CHAR,
v_cantidad IN NUMBER
)
IS
    v_precio_producto NUMBER(9,2);
    v_subtotal NUMBER(9,2);
BEGIN
    SELECT precio_unitario INTO v_precio_producto FROM productos WHERE cod_producto=v_cod_producto;
    v_subtotal:=v_precio_producto*v_cantidad;
    INSERT INTO detalle_facturas (cod_factura,cod_producto,cantidad,subtotal) VALUES (v_cod_factura,v_cod_producto,v_cantidad,v_subtotal);
    DBMS_OUTPUT.PUT_LINE('Detalle de factura registrado con éxito.');
END insertar_detalle_factura;

-- Probando
CALL insertar_detalle_factura('F0010','P0001',12);

-- Validando resultado
SELECT * FROM detalle_facturas where cod_factura='F0010';

Ejercicio 11: Reporte de ventas

Desarrollar un procedimiento almacenado que devuelva un reporte con la lista y sumatoria total de todas facturas agrupadas por cliente.

CREATE OR REPLACE PROCEDURE reporte_cliente_ventas
IS
    v_total NUMBER(9,2):=0;
    CURSOR clientes_cursor IS
    SELECT cod_cliente,nombres FROM clientes WHERE cod_cliente IN (SELECT DISTINCT(cod_cliente) FROM facturas);
    CURSOR facturas_cursor IS
    SELECT cod_factura,importe_total,cod_cliente FROM facturas;
BEGIN
    DBMS_OUTPUT.PUT_LINE(LPAD('-',70,'-'));
    DBMS_OUTPUT.PUT_LINE('INFORME DE VENTAS');
    DBMS_OUTPUT.PUT_LINE(LPAD('-',70,'-'));
    FOR cli IN clientes_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE(chr(13)||RPAD(UPPER(cli.cod_cliente),6)||UPPER(cli.nombres));
        DBMS_OUTPUT.PUT_LINE(LPAD('-',70,'-'));
        v_total:=0;
        FOR fa IN facturas_cursor
        LOOP
            IF fa.cod_cliente=cli.cod_cliente THEN
                v_total:=v_total+fa.importe_total;
                DBMS_OUTPUT.PUT_LINE(RPAD(fa.cod_factura, 6) || TO_CHAR(ROUND(fa.importe_total,2),'$999,999.00'));
            END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(RPAD('Total:',6) || TO_CHAR(ROUND(v_total,2),'$999,999.00'));
    END LOOP;
END reporte_cliente_ventas;

-- Probando
CALL reporte_cliente_ventas;

Ejercicio 12: Triggers

Crear un trigger que actualice el importe total de una factura cuando se inserte o se elimine un registro de la tabla detalle facturas.

CREATE OR REPLACE TRIGGER actualiza_factura
BEFORE INSERT OR DELETE ON detalle_facturas
FOR EACH ROW
BEGIN
  IF INSERTING THEN 
    UPDATE facturas SET importe_total = importe_total + :new.subtotal WHERE cod_factura = :new.cod_factura;
  END IF;
  
  IF DELETING THEN 
    UPDATE facturas SET importe_total = importe_total - :old.subtotal WHERE cod_factura = :old.cod_factura;
  END IF;

END actualiza_factura;

Publicaciones que pueden interesarte:

Recibe por correo electrónico noticias y publicaciones de este blog.

Publicado enBase de DatosHerramientas
Secured By miniOrange