PLSQL

Documentación

[PL/SQL para desarrolladores Oracle España](https://www.oracle.com/es/database/technologies/appdev/plsql.html)
PLSQL para desarrolladores Oracle España-plsql.html
Diferencia entre SQL y PLSQL Cuándo utilizar cada uno-difference-between-sql-and-plsql

Qué es PL/SQL

PL/SQL es el lenguaje procedimental nativo de Oracle, diseñado para extender SQL con estructuras de programación para crear lógica compleja, procesos transaccionales y manipulación de datos eficiente dentro de la base de datos.

Características principales

Extensión de SQL: añade variables, bucles, condiciones y tipos de datos avanzados.
Bloques Anidados: estructura basada en DECLARE → BEGIN → EXCEPTION → END.
Alto rendimiento: ejecución cercana al motor, minimiza viajes entre aplicación y base.
Manejadores de excepciones: control robusto de errores.
Paquetes: encapsulación de lógica en módulos reutilizables.
Triggers: ejecución automática ligada a eventos de tablas o vistas.
Cursores: control explícito o implícito sobre conjuntos de resultados.

Arquitectura de un bloque PL/SQL

Ejemplo de estructura

DECLARE
	v_total NUMBER;
BEGIN
	SELECT SUM(salary) INTO v_total FROM employees;
	DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Tipos de programas PL/SQL

Procedures: lógica que ejecuta acciones.
Functions: devuelven un valor y pueden usarse desde SQL (con restricciones).
Packages: colecciones de funciones/procedimientos organizadas en especificación y cuerpo.
Triggers: lógica que responde a INSERT/UPDATE/DELETE o eventos del sistema.
Tipos definidos por usuario: records, colecciones, objetos.

Cursores

Los cursores permiten procesar filas una a una cuando la lógica lo requiere.

Cursor explícito

DECLARE
	CURSOR c_emp IS SELECT first_name, salary FROM employees;
	v_name employees.first_name%TYPE;
	v_salary employees.salary%TYPE;
BEGIN
	FOR rec IN c_emp LOOP
		DBMS_OUTPUT.PUT_LINE(rec.first_name || ' - ' || rec.salary);
	END LOOP;
END;
/

Manejo de excepciones

Ejemplo

BEGIN
	INSERT INTO employees(id, name) VALUES (1, 'John');
EXCEPTION
	WHEN DUP_VAL_ON_INDEX THEN
		DBMS_OUTPUT.PUT_LINE('ID duplicado.');
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('Error no esperado: ' || SQLERRM);
END;
/

Ventajas de PL/SQL

Encapsulación de lógica compleja.
Seguridad: control de acceso granular y privilegios mínimos.
Reutilización mediante paquetes.
Reducción del tráfico entre cliente y servidor.
Integración con SQL y tipos del motor de Oracle.

Cuándo usar SQL y cuándo PL/SQL

Usar SQL:
- Consultas simples.
- Agregaciones sin lógica adicional.
- Manipulación directa de datos sin procesos procedurales.
Usar PL/SQL:
- Lógica condicional compleja.
- Procesos que requieren control de flujo, bucles o cursores.
- Automatización mediante triggers.
- APIs internas dentro de la base.

Buenas prácticas

Usar %TYPE y %ROWTYPE para mantener coherencia con la estructura de la base.
Colocar EXCEPTION solo cuando sea necesario.
Documentar cada paquete y rutina.
Evitar cursores explícitos cuando un bucle implícito es suficiente.
Minimizar commits dentro del código para evitar inconsistencias.
Agrupar lógica relacionada en paquetes para claridad y mantenimiento.

Herramientas recomendadas

SQL Developer
Oracle APEX
PL/SQL Unit Testing (UTPLSQL)
Oracle LiveSQL (online)

PLSQL — Conceptos Avanzados y Temas Faltantes

Optimización y Rendimiento

Bulk Operations (FORALL / BULK COLLECT): permiten mejorar el rendimiento al procesar grandes volúmenes reduciendo context switches entre SQL y PL/SQL.
Parallel Execution: algunas operaciones PL/SQL pueden coordinar trabajo paralelo cuando se ejecutan vía SQL, especialmente en ETL.
Pipelined Table Functions: funciones que devuelven datos fila a fila sin esperar al set completo, ideales para integraciones y flujos de datos en streaming.
PL/SQL Native Compilation: compilar paquetes a código nativo para aumentar rendimiento en cálculos intensivos.

Bulk Collect

DECLARE
	TYPE t_names IS TABLE OF employees.first_name%TYPE;
	l_names t_names;
BEGIN
	SELECT first_name BULK COLLECT INTO l_names FROM employees;
	FOR i IN 1 .. l_names.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE(l_names(i));
	END LOOP;
END;
/

Diseño Profesional de Paquetes

Los paquetes pueden estructurarse como mini-módulos de software:

Especificación mínima: solo lo que debe ser público.
Cuerpo encapsulado: implementar lógica privada, sub-funciones, constantes internas.
Variables de estado: cuidado con estado persistente entre sesiones, usar solo cuando sea estrictamente necesario.
Instrumentación integrada: DBMS_APPLICATION_INFO para trazabilidad.

Plantilla recomendada

CREATE OR REPLACE PACKAGE customer_api IS
	PROCEDURE create_customer(p_name VARCHAR2, p_email VARCHAR2);
	FUNCTION get_customer(p_id NUMBER) RETURN customer_obj;
END customer_api;
/

CREATE OR REPLACE PACKAGE BODY customer_api IS
	PROCEDURE create_customer(p_name VARCHAR2, p_email VARCHAR2) IS
	BEGIN
		INSERT INTO customers(name, email) VALUES (p_name, p_email);
	END;

	FUNCTION get_customer(p_id NUMBER) RETURN customer_obj IS
		l_result customer_obj;
	BEGIN
		SELECT customer_obj(id, name, email)
		INTO l_result
		FROM customers
		WHERE id = p_id;

		RETURN l_result;
	END;
END customer_api;
/

Seguridad en PL/SQL

Definer Rights vs Invoker Rights:
- Definer Rights: el código usa los permisos del autor.
- Invoker Rights: ejecuta con permisos del usuario que lo llama.
Wrappers y ofuscación: proteger lógica sensible.
Privilege Minimization: evitar otorgar más permisos que los necesarios.
Roles en PL/SQL: recordatorio crucial: los roles no están activos dentro del código, siempre usar privilegios directos.

Ejemplo de Invoker Rights

CREATE OR REPLACE PROCEDURE report_data AUTHID CURRENT_USER IS
BEGIN
	-- ejecuta con permisos del usuario que llama
	NULL;
END;
/

Tipos Avanzados

Colecciones anidadas
VARRAY
Objetos y métodos
Table Functions con objetos
Tipos para integración: JSON_OBJECT_T, JSON_ARRAY_T

JSON nativo en PL/SQL

DECLARE
	j JSON_OBJECT_T := JSON_OBJECT_T('{"name":"John","age":30}');
BEGIN
	DBMS_OUTPUT.PUT_LINE(j.get_String('name'));
END;
/

Patrones de Diseño Recomendados

API por paquetes: separar lectura/escritura, validaciones y utilidades en módulos claros.
Dominio + Validación: crear funciones de validación reutilizables antes de cada INSERT/UPDATE.
Control de Transacciones: centralizar commits/rollbacks en niveles superiores.
Excepciones personalizadas: crear un paquete errors_pkg para estandarizar errores.

Excepción personalizadas

DECLARE
	e_invalid_status EXCEPTION;
	PRAGMA EXCEPTION_INIT(e_invalid_status, -20010);
BEGIN
	RAISE e_invalid_status;
END;
/

Testing Profesional en PL/SQL

UTPLSQL (framework estándar)
Test de procedimientos
Mock de tablas mediante entornos temporales
Pruebas determinísticas con fixtures

Ejemplo UTPLSQL

BEGIN
	ut.expect(customer_api.get_customer(1).name).to_equal('John');
END;
/

Integraciones Modernas

PL/SQL + REST (Oracle REST Data Services — ORDS):
- Exponer procedimientos como endpoints REST.
- Controlar JSON IN/OUT con APEX_JSON o tipos nativos.
Eventos y colas (AQ / Advanced Queuing):
- Integración asíncrona entre sistemas.
Programación con DBMS_SCHEDULER:
- Crear jobs automáticos avanzados, dependencias y flujos.

Job programado

BEGIN
	DBMS_SCHEDULER.create_job(
		job_name => 'daily_customer_sync',
		job_type => 'PLSQL_BLOCK',
		job_action => 'BEGIN sync_customers; END;',
		repeat_interval => 'FREQ=DAILY;BYHOUR=3;',
		enabled => TRUE
	);
END;
/

Depuración e Instrumentación

DBMS_OUTPUT: básico, evitar en producción.
DBMS_DEBUG: depuración paso a paso.
DBMS_PROFILER / PL/SQL Hierarchical Profiler: medir rendimiento real.
Logs estructurados: registrar parámetros, errores y tiempos.

Errores Comunes a Evitar

Declarar cursores cuando un bucle implícito sería suficiente.
Realizar commit dentro de procedimientos internos.
Exponer lógica interna en especificaciones de paquetes.
Excepciones genéricas sin diagnóstico.
No usar %TYPE / %ROWTYPE.

Conceptos Avanzados a Vigilar

Gestión de memoria interna de colecciones.
Diferencias entre SQL Engine y PL/SQL Engine.
Context Switching (CPU killer si no se controla).
PL/SQL en entornos multitenant (CDB/PDB).

PLSQL — Nota actualizada a 2025 con recursos recientes

Qué es PL/SQL y por qué sigue vigente

PL/SQL es la extensión procedimental de SQL para Oracle Database, diseñada para permitir lógica compleja, modular y eficiente directamente en la base de datos. Combina la potencia de manipulación de datos de SQL con las capacidades de un lenguaje de programación: variables, estructuras de control, excepciones, subprogramas, etc.
Gracias a su integración nativa con el motor de Oracle, PL/SQL mantiene ventajas clave hoy en día: alto rendimiento, portabilidad, escalabilidad, reutilización de código, facilidad para mantenimientos centrales y seguridad.

Novedades, prácticas actuales y optimización (2025)

Buenas prácticas y optimización recomendadas

Evita operaciones una-fila-a-una: usa operaciones bulk (BULK COLLECT / FORALL) para procesar lotes de datos, reduciendo los context switches entre SQL y PL/SQL y aumentando el rendimiento.
Utiliza correctamente los tipos de datos: evita conversiones implícitas de tipo, dimensiona VARCHAR2 adecuadamente para evitar desperdicio de memoria.
Prefiere utilidades internas de Oracle antes de reinventar lógica del sistema.
Usa herramientas de perfilado como DBMS_PROFILER para detectar cuellos de botella.
Diseña paquetes con interfaz pública mínima y lógica privada clara.

Temas y capacidades modernas dentro del ecosistema Oracle (2025)

Soporte completo para tipos SQL (VARCHAR2, DATE, LOB…), variables con %TYPE/%ROWTYPE.
Uso de SQL estático y dinámico para mayor flexibilidad.
Estructuras modulares: procedimientos, funciones, paquetes, triggers.
Manejo de excepciones avanzado y homogéneo en toda la aplicación.

Recursos recomendados (2024-2025)

**PL/SQL para desarrolladores Oracle España** — guía base del lenguaje, contexto, ventajas y recursos de inicio.
Database PL/SQL Language Reference (2025) — documentación oficial completa y actualizada del lenguaje.
PL/SQL Best Practices — prácticas modernas de optimización, rendimiento y estilo.
Curso fundamentos PL/SQL (OpenWebinars) — formación introductoria en español.
Libro Oracle PL/SQL (César Pérez López, Ra-Ma) — base teórica recomendable para dominar fundamentos.

Estado actual del uso de PL/SQL en 2025

Aunque la publicación de nuevos libros se ha ralentizado, la documentación oficial de Oracle continúa actualizándose regularmente, manteniendo PL/SQL como una pieza central en entornos empresariales con Oracle Database.
En comunidades técnicas se observa que la demanda laboral especializada exclusivamente en PL/SQL ha disminuido, pero crece su valor combinado con otras tecnologías (frontend, backend generalista, cloud, ORDS, APEX).
Sigue siendo altamente útil en proyectos con lógica compleja, integraciones internas y sistemas críticos, especialmente en el sector bancario, seguros, fintech y organismos públicos.

Conclusión

PL/SQL continúa siendo en 2025 un lenguaje potente, seguro, escalable y profundamente integrado con Oracle Database. Para aprovechar su máximo potencial es necesario aplicar buenas prácticas modernas: operaciones bulk, diseño modular, uso de paquetes, profiling y control cuidadoso de transacciones.
Este stack sigue siendo relevante en entornos corporativos y críticos, aunque combinado cada vez más con tecnologías externas.

Enlaces utilizados

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/overview.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/database-pl-sql-language-reference.pdf
https://docs.oracle.com/en/industries/retail/retail-analytics-planning/24.1.201.0/rapig/pl-sql-best-practice.htm
https://www.oracle.com/es/database/technologies/appdev/plsql.html
https://openwebinars.net/cursos/fundamentos-pl-sql
https://www.ra-ma.es/libro/oracle-pl-sql_141216
https://www.reddit.com/r/oracle/comments/1idmlbg

Glosario PL/SQL (Listado)

Fundamentos del Lenguaje

PL/SQL: Extensión procedural de SQL integrada en Oracle.
Bloque PL/SQL: Estructura con DECLARE, BEGIN, EXCEPTION, END.
Bloque anónimo: No almacenado, ejecución directa.
Bloque nombrado: Procedimientos, funciones o paquetes almacenados.
Declaración de variables.
Tipos escalares: NUMBER, VARCHAR2, DATE, BOOLEAN.
Tipos anclados: %TYPE, %ROWTYPE.
Constantes: Uso de CONSTANT.
Estructuras de control: IF, CASE, FOR, WHILE, LOOP.

Estructuras de Programación

Procedimientos:
- Parámetros IN, OUT, IN OUT.
- Sobrecarga (overload).
Funciones:
- Devuelven valor.
- Usables en SQL si son deterministas o seguras.
Packages:
- Specification (interfaz pública).
- Body (implementación privada).
- Variables de paquete persistentes.

SQL en PL/SQL

SELECT INTO: Asignación de resultados.
DML directo: INSERT, UPDATE, DELETE.
RETURNING INTO: Obtener valores tras un DML.
Cursores:
- Implícitos.
- Explícitos: OPEN / FETCH / CLOSE.
- FOR cursor IN ... LOOP.
- Cursores con parámetros.
Bulk processing:
- BULK COLLECT.
- FORALL.
- SAVE EXCEPTIONS.

Manejo de Excepciones

Excepciones predefinidas: NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX.
Excepciones definidas por el usuario.
RAISE y RAISE_APPLICATION_ERROR.
SQLCODE y SQLERRM.

Tipos Avanzados

Registros:
- %ROWTYPE.
- TYPE ... IS RECORD.
Colecciones:
- Associative Arrays (INDEX BY).
- Nested Tables.
- VARRAYs.

Rendimiento

Bulk operations para evitar row-by-row.
Reducción de context switching SQL ↔ PL/SQL.
Caching con variables de paquete.
Minimizar queries dentro de loops.
Bind variables en SQL dinámico.
Uso de DBMS_PROFILER y DBMS_HPROF.

Seguridad y Acceso

Definer’s Rights (por defecto).
Invoker’s Rights (AUTHID CURRENT_USER).
SQL Injection en SQL dinámico.
Mitigación mediante parámetros bind.

SQL Dinámico

EXECUTE IMMEDIATE:
- USING para bind variables.
- INTO para resultados.
- RETURNING INTO para DML.
Paquete DBMS_SQL:
- Ejecución de SQL dinámico complejo.
- Compatibilidad con columnas variables.

Paquetes del Sistema

DBMS_OUTPUT: Mostrar texto.
DBMS_RANDOM: Valores aleatorios.
DBMS_SCHEDULER: Jobs programados.
DBMS_UTILITY: Utilidades varias.
DBMS_LOCK: Locks a nivel PL/SQL.
UTL_FILE: Acceso a ficheros.
UTL_HTTP: Llamadas HTTP.

Transacciones

COMMIT y ROLLBACK.
SAVEPOINT.
Transacciones autónomas:
- PRAGMA AUTONOMOUS_TRANSACTION.

Herramientas Habituales

SQL*Plus.
SQL Developer.
SQLcl.
Oracle APEX.
Oracle REST Data Services (ORDS).

PL/SQL — Ejemplos de Código Explicados (2025)

Bloques PL/SQL

Bloque Anónimo

DECLARE
	v_msg VARCHAR2(50) := 'Hola desde PL/SQL';
BEGIN
	DBMS_OUTPUT.PUT_LINE(v_msg);
END;
/

Explicación:
Un bloque anónimo no se almacena en la base de datos; se ejecuta directamente. Es ideal para pruebas rápidas, scripts o lógica temporal.

Variables y Tipos

Uso de %TYPE

DECLARE
	v_emp_name employees.last_name%TYPE;
BEGIN
	SELECT last_name INTO v_emp_name
	FROM employees
	WHERE employee_id = 100;

	DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;
/

Explicación:
%TYPE hereda el tipo de columna, garantizando consistencia incluso si la estructura de la tabla cambia.

Uso de %ROWTYPE

DECLARE
	v_emp_record employees%ROWTYPE;
BEGIN
	SELECT * INTO v_emp_record
	FROM employees
	WHERE employee_id = 100;

	DBMS_OUTPUT.PUT_LINE(v_emp_record.last_name);
END;
/

Explicación:
%ROWTYPE crea un registro con todas las columnas de la tabla o vista, útil para consultas completas.

Estructuras de Control

IF / ELSIF / ELSE

DECLARE
	v_num NUMBER := 7;
BEGIN
	IF v_num > 10 THEN
		DBMS_OUTPUT.PUT_LINE('Mayor que 10');
	ELSIF v_num = 10 THEN
		DBMS_OUTPUT.PUT_LINE('Igual a 10');
	ELSE
		DBMS_OUTPUT.PUT_LINE('Menor que 10');
	END IF;
END;
/

Explicación:
Permite evaluar condiciones múltiples y tomar decisiones lógico-procedimentales.

FOR Loop

BEGIN
	FOR i IN 1..5 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteración: ' || i);
	END LOOP;
END;
/

Explicación:
El FOR define automáticamente el contador y sus límites.

Procedimientos

Procedimiento con Parámetros IN y OUT

CREATE OR REPLACE PROCEDURE get_salary (
	p_emp_id IN employees.employee_id%TYPE,
	p_salary OUT employees.salary%TYPE
) AS
BEGIN
	SELECT salary INTO p_salary
	FROM employees
	WHERE employee_id = p_emp_id;
END;
/

Explicación:
El parámetro OUT devuelve datos al llamador, permitiendo que el procedimiento actúe como una operación que modifica el estado externo.

Funciones

Función Determinista

CREATE OR REPLACE FUNCTION yearly_salary (
	p_monthly NUMBER
) RETURN NUMBER DETERMINISTIC AS
BEGIN
	RETURN p_monthly * 12;
END;
/

Explicación:
DETERMINISTIC indica que la función devolverá siempre el mismo resultado con la misma entrada, permitiendo optimizaciones internas.

Packages

Package Specification y Body

CREATE OR REPLACE PACKAGE math_pkg AS
	FUNCTION square(p_num NUMBER) RETURN NUMBER;
END math_pkg;
/

CREATE OR REPLACE PACKAGE BODY math_pkg AS
	FUNCTION square(p_num NUMBER) RETURN NUMBER IS
	BEGIN
		RETURN p_num * p_num;
	END;
END math_pkg;
/

Explicación:
Los paquetes separan la interfaz (spec) de la implementación (body), facilitando el mantenimiento y ocultando detalles.

Cursores

Cursor Explícito

DECLARE
	CURSOR c_emps IS
		SELECT employee_id, last_name FROM employees;
	v_id employees.employee_id%TYPE;
	v_name employees.last_name%TYPE;
BEGIN
	OPEN c_emps;
	LOOP
		FETCH c_emps INTO v_id, v_name;
		EXIT WHEN c_emps%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
	END LOOP;
	CLOSE c_emps;
END;
/

Explicación:
Permite recorrer fila a fila el resultado de una consulta, ideal cuando se necesita procesar datos individualmente.

Cursor FOR Loop

BEGIN
	FOR rec IN (SELECT employee_id, last_name FROM employees) LOOP
		DBMS_OUTPUT.PUT_LINE(rec.employee_id || ': ' || rec.last_name);
	END LOOP;
END;
/

Explicación:
Simplifica la sintaxis: Oracle maneja OPEN, FETCH y CLOSE automáticamente.

Bulk Processing

BULK COLLECT

DECLARE
	TYPE t_names IS TABLE OF employees.last_name%TYPE;
	v_names t_names;
BEGIN
	SELECT last_name BULK COLLECT INTO v_names
	FROM employees;

	DBMS_OUTPUT.PUT_LINE('Total: ' || v_names.COUNT);
END;
/

Explicación:
Permite obtener muchas filas a la vez, eliminando múltiples cambios de contexto SQL↔PLSQL y acelerando procesos masivos.

FORALL

DECLARE
	TYPE t_ids IS TABLE OF NUMBER;
	v_ids t_ids := t_ids(1, 2, 3, 4, 5);
BEGIN
	FORALL i IN v_ids.FIRST .. v_ids.LAST
		UPDATE employees
		SET salary = salary * 1.05
		WHERE employee_id = v_ids(i);
END;
/

Explicación:
Ejecuta DML masivo en lotes, mucho más rápido que ejecutar UPDATE dentro de un loop.

Excepciones

Manejo de Excepciones

DECLARE
	v_sal NUMBER;
BEGIN
	SELECT salary INTO v_sal
	FROM employees
	WHERE employee_id = 9999; -- No existe

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Empleado no encontrado.');
END;
/

Explicación:
EXCEPTION intercepta errores y permite un manejo controlado.

SQL Dinámico

EXECUTE IMMEDIATE Básico

DECLARE
	v_sql VARCHAR2(200) := 'UPDATE employees SET salary = salary * 1.10 WHERE employee_id = :id';
BEGIN
	EXECUTE IMMEDIATE v_sql USING 100;
END;
/

Explicación:
SQL dinámico permite construir y ejecutar sentencias en tiempo de ejecución, con soporte para bind variables.

EXECUTE IMMEDIATE con INTO

DECLARE
	v_count NUMBER;
BEGIN
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees' INTO v_count;
	DBMS_OUTPUT.PUT_LINE('Total: ' || v_count);
END;
/

Transacciones

Uso de SAVEPOINT y ROLLBACK

BEGIN
	UPDATE employees SET salary = salary + 100 WHERE employee_id = 101;
	SAVEPOINT sp1;

	UPDATE employees SET salary = salary + 100 WHERE employee_id = 9999; -- error

	COMMIT;

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK TO sp1;
		DBMS_OUTPUT.PUT_LINE('Se revierte al savepoint.');
END;
/

Explicación:
Los savepoints permiten revertir parcialmente una transacción sin perder operaciones anteriores.

Transacciones Autónomas

PRAGMA AUTONOMOUS_TRANSACTION

CREATE OR REPLACE PROCEDURE log_event(p_msg VARCHAR2) AS
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO event_log(message, created_at)
	VALUES (p_msg, SYSDATE);

	COMMIT; -- independiente de la transacción principal
END;
/

Explicación:
Este procedimiento hace commit por sí mismo sin afectar la lógica externa.

Paquetes del Sistema

DBMS_OUTPUT

BEGIN
	DBMS_OUTPUT.PUT_LINE('Mostrando un mensaje');
END;
/

DBMS_RANDOM

DECLARE
	v_num NUMBER;
BEGIN
	v_num := DBMS_RANDOM.VALUE(1, 100);
	DBMS_OUTPUT.PUT_LINE('Número aleatorio: ' || v_num);
END;
/

UTL_HTTP — Petición HTTP

DECLARE
	v_resp CLOB;
BEGIN
	v_resp := UTL_HTTP.REQUEST('https://example.com');
	DBMS_OUTPUT.PUT_LINE(v_resp);
END;
/

Explicación:
Permite acceder a servicios web desde PL/SQL (requiere ACL configurado).

PL/SQL — Casos Reales de Empresa (Ejemplos Extensos y Explicados)

Caso 1: Cálculo de Comisiones Mensuales (Sector Ventas / Retail)

Descripción

Una empresa de retail necesita calcular comisiones mensuales basadas en el total vendido por cada empleado. El sistema debe: - Detectar ventas del mes. - Aplicar reglas diferentes según categoría del vendedor. - Registrar el cálculo en una tabla histórica. - Ser ejecutado automáticamente cada inicio de mes.

Ejemplo de Código

CREATE OR REPLACE PROCEDURE calc_comisiones IS
	CURSOR c_vendedores IS
		SELECT v.vendedor_id,
		       v.categoria,
		       SUM(s.monto) total_mes
		FROM ventas s
		JOIN vendedores v ON v.vendedor_id = s.vendedor_id
		WHERE s.fecha BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
		                   AND TRUNC(SYSDATE, 'MM') - 1
		GROUP BY v.vendedor_id, v.categoria;

	v_comision NUMBER;
BEGIN
	FOR rec IN c_vendedores LOOP
		-- Reglas por categoría
		IF rec.categoria = 'A' THEN
			v_comision := rec.total_mes * 0.12;
		ELSIF rec.categoria = 'B' THEN
			v_comision := rec.total_mes * 0.08;
		ELSE
			v_comision := rec.total_mes * 0.05;
		END IF;

		INSERT INTO historial_comisiones(vendedor_id, total_mes, comision, fecha_calculo)
		VALUES (rec.vendedor_id, rec.total_mes, v_comision, SYSDATE);
	END LOOP;

	COMMIT;
END;
/

Explicación

Este procedimiento reemplaza informes manuales. Puede ejecutarse mediante DBMS_SCHEDULER mensualmente, evitando cálculos externos (Excel, scripts Python) y garantizando consistencia.

Caso 2: Auditoría Automática de Cambios (Sector Bancario)

Descripción

Regulaciones financieras obligan a auditar cada modificación de ciertas tablas (clientes, cuentas, transacciones). La auditoría debe incluir:

Usuario que realizó el cambio.
Timestamp.
Valores antes y después.

Ejemplo de Trigger de Auditoría

CREATE OR REPLACE TRIGGER audit_cuentas_trg
AFTER UPDATE ON cuentas
FOR EACH ROW
BEGIN
	INSERT INTO auditoria_cuentas(
		cuenta_id,
		usuario,
		fecha_cambio,
		saldo_anterior,
		saldo_nuevo
	)
	VALUES(
		:OLD.cuenta_id,
		SYS_CONTEXT('USERENV','SESSION_USER'),
		SYSTIMESTAMP,
		:OLD.saldo,
		:NEW.saldo
	);
END;
/

Explicación

Este patrón es muy común en banca, donde trazabilidad = cumplimiento normativo. PL/SQL es ideal, pues la auditoría reside dentro del motor y no depende de aplicaciones externas.

Caso 3: Envío de Notificaciones a Clientes (Sector Seguros / Telecom)

Descripción

Una compañía quiere avisar a clientes cuyo contrato expira en los próximos 30 días. Las notificaciones deben enviarse mediante ORDS o un API REST.

Ejemplo con `UTL_HTTP`

DECLARE
	CURSOR c_clientes IS
		SELECT cliente_id, email
		FROM contratos
		WHERE fecha_fin BETWEEN SYSDATE AND SYSDATE + 30;

	v_payload VARCHAR2(4000);
	v_response CLOB;
BEGIN
	FOR rec IN c_clientes LOOP
		v_payload := '{"email": "' || rec.email || '", "mensaje": "Su contrato está próximo a expirar."}';

		v_response := UTL_HTTP.REQUEST(
			url => 'https://api.miempresa.com/notificaciones',
			method => 'POST',
			body => v_payload
		);

		INSERT INTO log_notificaciones(cliente_id, fecha_envio, respuesta)
		VALUES (rec.cliente_id, SYSDATE, v_response);
	END LOOP;

	COMMIT;
END;
/

Explicación

Ideal para integraciones sin intermediarios. Requiere ACL, pero evita tener que sacar datos fuera de la BD para enviarlos desde un servicio externo.

Caso 4: Procesamiento Masivo de Datos (Sector Logística)

Descripción

Una empresa logística recibe a diario millones de registros de escaneos de paquetes (IoT). Necesita:

Procesar en lotes (bulk collect).
Consolidar datos y actualizar estados.
Mantener rendimiento alto.

Ejemplo con BULK COLLECT + FORALL

DECLARE
	TYPE t_escaneos IS TABLE OF escaneos%ROWTYPE;
	v_lote t_escaneos;

BEGIN
	SELECT * 
	BULK COLLECT INTO v_lote
	FROM escaneos
	WHERE procesado = 'N'
	FETCH FIRST 50000 ROWS ONLY;

	FORALL i IN v_lote.FIRST .. v_lote.LAST
		UPDATE paquetes
		SET ultima_localizacion = v_lote(i).ubicacion,
		    fecha_ultima = v_lote(i).fecha
		WHERE paquete_id = v_lote(i).paquete_id;

	FORALL i IN v_lote.FIRST .. v_lote.LAST
		UPDATE escaneos SET procesado = 'S'
		WHERE id = v_lote(i).id;

	COMMIT;
END;
/

Explicación

Procesar fila por fila sería inviable. Bulk operations permiten procesar cientos de miles de registros en segundos.

Caso 5: Generación de Informes Diarios (Sector Financiero)

Descripción

Se generan PDFs o CSV con movimientos, saldos y operaciones. La generación se hace dentro de la BD para evitar transferencias.

Ejemplo: Generación de CSV con `UTL_FILE`

DECLARE
	fh UTL_FILE.FILE_TYPE;
BEGIN
	fh := UTL_FILE.FOPEN('DIR_REPORTES', 'movimientos.csv', 'W');

	FOR rec IN (SELECT cuenta_id, monto, fecha FROM movimientos WHERE fecha >= TRUNC(SYSDATE)) LOOP
		UTL_FILE.PUT_LINE(fh, rec.cuenta_id || ';' || rec.monto || ';' || rec.fecha);
	END LOOP;

	UTL_FILE.FCLOSE(fh);
END;
/

Explicación

Usado en ambientes core, donde los informes diarios deben generarse rápido y con datos consistentes.

Caso 6: Limpieza y Archivado Automático (Sector Administración Pública)

Descripción

El sistema debe archivar registros antiguos en una tabla histórica, eliminar registros muy antiguos y mantener índices limpios.

Ejemplo

BEGIN
	INSERT INTO historico_tramites
	SELECT * FROM tramites
	WHERE fecha < ADD_MONTHS(SYSDATE, -24);

	DELETE FROM tramites
	WHERE fecha < ADD_MONTHS(SYSDATE, -24);

	COMMIT;
END;
/

Explicación

Permite mantener el rendimiento de tablas críticas y cumplir políticas de conservación de datos.

Caso 7: Validación Compleja de Datos (Sector Salud)

Descripción

Antes de registrar un acto clínico, deben validarse múltiples reglas:

Formatos.
Rango de valores.
Existencia de datos obligatorios.
Reglas de negocio específicas del centro.

Ejemplo

CREATE OR REPLACE FUNCTION validar_datos (
	p_paciente_id NUMBER,
	p_codigo_acto VARCHAR2,
	p_monto NUMBER
) RETURN VARCHAR2 AS
BEGIN
	IF p_monto < 0 THEN
		RETURN 'Monto inválido';
	END IF;

	IF NOT EXISTS (SELECT 1 FROM pacientes WHERE paciente_id = p_paciente_id) THEN
		RETURN 'Paciente inexistente';
	END IF;

	IF p_codigo_acto NOT IN ('C01','C02','C03') THEN
		RETURN 'Acto no permitido';
	END IF;

	RETURN 'OK';
END;
/

Explicación

Permite centralizar reglas complejas en la base de datos, evitando inconsistencias entre múltiples aplicaciones.

Caso 8: Sincronización de Sistemas (Sector Multinacional / ERP)

Descripción

Un ERP debe sincronizar datos con un sistema externo cada noche:

Extraer datos modificados.
Convertirlos a JSON.
Enviar a un API.

Ejemplo

DECLARE
	v_json CLOB;
BEGIN
	FOR rec IN (SELECT * FROM productos WHERE fecha_mod > SYSDATE - 1) LOOP
		v_json := '{"id": ' || rec.id || ', "name": "' || rec.nombre || '"}';

		-- Llamada HTTP simulada
		UTL_HTTP.REQUEST('https://externo/api/sync', 'POST', v_json);
	END LOOP;

	INSERT INTO log_sync(fecha, registros)
	VALUES (SYSDATE, SQL%ROWCOUNT);

	COMMIT;
END;
/

Explicación

Evita tener procesos ETL externos para sincronizaciones pequeñas y frecuentes.

Caso 9: Control de Inventario en Tiempo Real (Sector Industrial)

Descripción

Sensores notifican cambios de stock. La BD debe actualizar inventario y detectar alertas.

Ejemplo

CREATE OR REPLACE PROCEDURE actualizar_stock (
	p_item_id NUMBER,
	p_cantidad NUMBER
) AS
BEGIN
	UPDATE inventario
	SET stock = stock + p_cantidad
	WHERE item_id = p_item_id;

	IF (SELECT stock FROM inventario WHERE item_id = p_item_id) < 10 THEN
		INSERT INTO alertas(item_id, fecha, mensaje)
		VALUES(p_item_id, SYSDATE, 'Stock bajo');
	END IF;

	COMMIT;
END;
/

Explicación

Procesos de stock críticos requieren que la lógica esté en el motor para ser inmediata.

Caso 10: Logs de Aplicación Centralizados (Cualquier Sector)

Descripción

Se quiere que todas las aplicaciones escriban logs en una tabla única con transacciones autónomas.

Ejemplo

CREATE OR REPLACE PROCEDURE app_log(p_msg VARCHAR2) AS
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO app_logs(mensaje, fecha)
	VALUES(p_msg, SYSTIMESTAMP);
	COMMIT;
END;
/

Explicación

Permite que los logs no dependan del flujo normal ni de commits externos.

Si quieres, puedo generar otra versión con casos extremadamente avanzados, como:

modelado de particiones + PL/SQL,
flujos distribuidos,
PL/SQL como motor de reglas,
automatización con ORDS + PL/SQL,
pipelines de datos internos.

Casos Extremadamente Avanzados en PL/SQL (2025)

1. Procesamiento Masivo con Pipelines, Paralelismo y Bulk Ops

Caso: ETL interno de millones de registros con mínima carga en buffer cache

Explicación:
En entornos de data warehousing, PL/SQL permite crear table functions pipelined combinadas con paralelismo para transformar conjuntos enormes sin materializar tablas intermedias.

Ejemplo — Pipeline + Bulk + Transformación Compleja

CREATE OR REPLACE TYPE t_etl_row AS OBJECT (
	id NUMBER,
	valor NUMBER,
	etiqueta VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE t_etl_tab IS TABLE OF t_etl_row;
/

CREATE OR REPLACE FUNCTION etl_pipeline_f(p_limit NUMBER)
	RETURN t_etl_tab PIPELINED
IS
	TYPE t_src_tab IS TABLE OF src_table%ROWTYPE;
	v_src t_src_tab;
BEGIN
	LOOP
		-- Extracción por lotes
		SELECT * BULK COLLECT INTO v_src
		FROM (
			SELECT * FROM src_table
			WHERE procesado = 0
			AND ROWNUM <= p_limit
		);

		EXIT WHEN v_src.COUNT = 0;

		FOR i IN 1 .. v_src.COUNT LOOP
			-- Lógica avanzada de enriquecimiento + segmentación
			PIPE ROW (t_etl_row(
				v_src(i).id,
				v_src(i).valor * DBMS_CRYPTO.HASH(TO_CHAR(v_src(i).id), 2),
				CASE
					WHEN v_src(i).valor < 100 THEN 'LOW'
					WHEN v_src(i).valor < 500 THEN 'MID'
					ELSE 'HIGH'
				END
			));
		END LOOP;
	END LOOP;

	RETURN;
END;
/

-- Consulta paralela del pipeline
SELECT /*+ PARALLEL(4) */ *
FROM TABLE(etl_pipeline_f(5000));

Qué resuelve:

ETL de alto volumen sin staging tables.
Transformación en streaming.
Uso eficiente de memoria.
Puede ser paralelizado, incluso distribuido.

2. Motor de Reglas Empresariales Dinámicas (Dynamic SQL + JSON + PLSQL)

Caso: Un banco debe cambiar reglas de scoring sin desplegar código

Explicación:
Las reglas se almacenan como JSON en una tabla. PL/SQL las interpreta dinámicamente.

Ejemplo — Regla dinámica

DECLARE
	v_regla JSON_OBJECT_T;
	v_sql   VARCHAR2(4000);
	v_score NUMBER;
BEGIN
	-- Obtener regla JSON
	SELECT JSON_OBJECT_T(regla_json)
	INTO v_regla
	FROM reglas_scoring
	WHERE regla_id = 10;

	-- Construcción dinámica de expresión
	v_sql := '
		SELECT (' || v_regla.get_String('formula') || ')
		FROM dual';

	EXECUTE IMMEDIATE v_sql INTO v_score;

	DBMS_OUTPUT.PUT_LINE('Score resultante = ' || v_score);
END;

Qué resuelve:

Cambios de reglas por negocio sin tocar PL/SQL.
Permite evaluaciones matemáticas o lógicas complejas configurables.

3. Particionamiento Lógico por Código con Result Cache Inteligente

Caso: Sistema que debe servir cálculos pesados a miles de consultas por segundo

Explicación:
RESULT_CACHE + invalidación manual permite reducir tiempos a microsegundos.

Ejemplo — Cache con partición lógica

CREATE OR REPLACE FUNCTION fx_get_riesgo(p_cliente NUMBER)
	RETURN NUMBER
	RESULT_CACHE RELIES_ON(clientes)
IS
	v_riesgo NUMBER;
BEGIN
	SELECT riesgo INTO v_riesgo
	FROM clientes
	WHERE id = p_cliente;

	RETURN v_riesgo;
END;
/

-- Invalida el valor del cache al actualizar
CREATE OR REPLACE TRIGGER trg_upd_cliente
AFTER UPDATE ON clientes
BEGIN
	DBMS_RESULT_CACHE.FLUSH;
END;

Qué resuelve:

Consultas repetitivas ultra rápidas.
Control total sobre invalidación.
Ideal para dashboards en tiempo real.

4. Optimización de Pricing Avanzado usando Colecciones Indexadas + Hashes

Caso: Motor de precios que debe recalcular tarifas según cientos de reglas jerarquizadas

Explicación:
Las associative arrays permiten crear estructuras hash ultra rápidas para búsquedas complejas en memoria PL/SQL.

Ejemplo — Motor de pricing en memoria

DECLARE
	TYPE t_price_map IS TABLE OF NUMBER INDEX BY VARCHAR2(200);
	v_prices t_price_map;

	v_key VARCHAR2(200);
BEGIN
	-- Cargar reglas en memoria
	FOR r IN (SELECT categoria, subcat, coef
	          FROM pricing_reglas) LOOP
		v_key := r.categoria || ':' || r.subcat;
		v_prices(v_key) := r.coef;
	END LOOP;

	-- Aplicar reglas a transacción
	DBMS_OUTPUT.PUT_LINE(
		'Precio final = ' ||
		(100 * v_prices('ELEC:HIGH'))
	);
END;

Qué resuelve:

Reglas jerárquicas rápidas (tiempo O(1)).
Sistemas de pricing, scoring, tarifas y segmentación.
Evita JOINs costosos.

5. Procesos de Conciliación Inter-Sistemas a Alta Velocidad

Caso: Comparación masiva de datos entre dos sistemas externos

Explicación:
Uso de BULK COLLECT, hashing y procesamiento batch para medir diferencias entre millones de registros.

Ejemplo — Conciliación con hashing

DECLARE
	TYPE t_rows IS TABLE OF VARCHAR2(4000);

	v_src1 t_rows;
	v_src2 t_rows;

	v_diff t_rows := t_rows();
BEGIN
	-- Cargar datos (simplificado)
	SELECT DBMS_CRYPTO.HASH(col1 || col2, 2)
	BULK COLLECT INTO v_src1
	FROM tabla_a;

	SELECT DBMS_CRYPTO.HASH(col1 || col2, 2)
	BULK COLLECT INTO v_src2
	FROM tabla_b;

	-- Comparación avanzada
	FOR i IN 1 .. v_src1.COUNT LOOP
		IF v_src2.EXISTS(i) THEN
			IF v_src1(i) != v_src2(i) THEN
				v_diff.EXTEND;
				v_diff(v_diff.COUNT) := 'DIF: ' || i;
			END IF;
		END IF;
	END LOOP;

	DBMS_OUTPUT.PUT_LINE('Total diferencias: ' || v_diff.COUNT);
END;

Qué resuelve:

Conciliación masiva entre sistemas heterogéneos.
Uso eficiente de CPU y memoria.
Detección rápida de divergencias.

6. Orquestación de Microservicios desde PL/SQL (HTTP + JSON + Lógica Transaccional)

Caso: Core bancario llama a microservicios externos pero controla la transacción en Oracle

Explicación:
PL/SQL actúa como coordinador de operaciones externas asegurando consistencia.

Ejemplo — Llamada avanzada a microservicios

DECLARE
	v_req   UTL_HTTP.req;
	v_resp  UTL_HTTP.resp;
	v_body  VARCHAR2(32767);
	v_json  JSON_OBJECT_T;
BEGIN
	v_req := UTL_HTTP.begin_request('https://api.interno/cliente/score', 'POST');
	UTL_HTTP.set_header(v_req, 'Content-Type', 'application/json');

	UTL_HTTP.write_text(v_req, '{"id":1234}');
	v_resp := UTL_HTTP.get_response(v_req);

	LOOP
		UTL_HTTP.read_text(v_resp, v_body, 32000);
		EXIT WHEN v_body IS NULL;
	END LOOP;

	UTL_HTTP.end_response(v_resp);

	v_json := JSON_OBJECT_T.parse(v_body);
	DBMS_OUTPUT.PUT_LINE('Score recibido: ' || v_json.get_Number('score'));
END;

Qué resuelve:

Orquestación transaccional híbrida.
Integración con APIs REST.
Lógica compleja sin exponer Oracle directamente.

7. Implementación de un Sistema de Auditoría Criptográfica Interna

Caso: Empresa de seguros requiere auditoría no repudiable dentro de Oracle

Explicación:
PL/SQL + DBMS_CRYPTO generan huellas digitales encadenadas (tipo blockchain ligera).

Ejemplo — Cadena de auditoría

DECLARE
	v_last_hash RAW(256);
	v_new_hash  RAW(256);
BEGIN
	SELECT hash INTO v_last_hash
	FROM auditoria
	ORDER BY id DESC
	FETCH FIRST 1 ROW ONLY;

	v_new_hash := DBMS_CRYPTO.HASH(
		UTL_I18N.STRING_TO_RAW('evento=' || SYSTIMESTAMP || '|' || v_last_hash),
		DBMS_CRYPTO.HASH_SH256
	);

	INSERT INTO auditoria(id, timestamp, hash)
	VALUES (auditoria_seq.NEXTVAL, SYSTIMESTAMP, v_new_hash);
END;

Qué resuelve:

Auditoría encadenada estilo blockchain.
No repudiable.
Inmutable sin herramientas externas.

¿Quieres la versión siguiente?

Puedo generar:

Versión con casos de ciberseguridad extrema
Versión con patrones arquitectónicos PL/SQL + microservicios
Versión con optimizaciones de rendimiento extremo para 2025
Versión con anti-patterns avanzados (qué NO hacer)

Patrones Arquitectónicos PL/SQL + Microservicios (2025)

1. Pattern: Orquestador Centralizado

Descripción

PL/SQL actúa como nodo central que coordina múltiples microservicios y bases de datos externas, asegurando consistencia transaccional.

Ejemplo

DECLARE
	v_response1 CLOB;
	v_response2 CLOB;
BEGIN
	-- Llamada a microservicio A
	v_response1 := UTL_HTTP.REQUEST('https://api.servicioA.com/operacion', 'POST', '{"id":1001}');

	-- Llamada a microservicio B solo si A fue exitoso
	IF v_response1 IS NOT NULL THEN
		v_response2 := UTL_HTTP.REQUEST('https://api.servicioB.com/validar', 'POST', '{"ref":1001}');
	END IF;

	-- Registro interno de transacción
	INSERT INTO log_orquestacion(fecha, servicioA_resp, servicioB_resp)
	VALUES(SYSDATE, v_response1, v_response2);

	COMMIT;
END;

Ventaja: Control total de la lógica empresarial desde Oracle, manteniendo integridad sin depender de middleware externo.

2. Pattern: Event-Driven Architecture

Descripción

PL/SQL actúa como productor y consumidor de eventos usando tablas de cola o triggers; los microservicios reaccionan a cambios en la base de datos.

Ejemplo — Trigger Event Queue

CREATE OR REPLACE TRIGGER trg_evento_cliente
AFTER INSERT OR UPDATE ON clientes
FOR EACH ROW
BEGIN
	INSERT INTO event_queue(event_type, payload, fecha)
	VALUES(
		'CLIENTE_MODIFICADO',
		'{"id":' || :NEW.cliente_id || ',"nombre":"' || :NEW.nombre || '"}',
		SYSTIMESTAMP
	);
END;
/

Explicación: Microservicios externos leen event_queue y procesan eventos de forma asíncrona, permitiendo desacoplar sistemas.

3. Pattern: API Gateway Interno

Descripción

Oracle PL/SQL expone endpoints internos mediante ORDS o REST Data Services, actuando como gateway para microservicios y clientes internos.

Ejemplo — ORDS PL/SQL Endpoint

BEGIN
	-- Endpoint: /clientes/get
	ORDS.ENABLE_SCHEMA(
		p_enabled => TRUE,
		p_schema  => 'APP_SCHEMA'
	);

	-- Procedimiento que se mapea como REST GET
	CREATE OR REPLACE PROCEDURE get_cliente(p_id IN NUMBER) AS
		v_name VARCHAR2(100);
	BEGIN
		SELECT nombre INTO v_name FROM clientes WHERE cliente_id = p_id;
		HTP.P('{"cliente_id":' || p_id || ',"nombre":"' || v_name || '"}');
	END;
END;

Ventaja:

Exposición segura de datos internos.
Simplifica acceso de microservicios a datos críticos.
Permite control de autenticación y throttling en Oracle.

4. Pattern: Transacciones Distribuidas / Saga Pattern

Descripción

PL/SQL gestiona pasos locales con commits parciales o autonómicos, coordinando microservicios externos en transacciones distribuidas tipo saga.

Ejemplo — Saga Simplificada

BEGIN
	-- Paso 1: Actualizar inventario
	UPDATE inventario SET stock = stock - 1 WHERE item_id = 101;

	-- Paso 2: Llamada a microservicio de facturación
	BEGIN
		UTL_HTTP.REQUEST('https://api.facturacion.com/generar', 'POST', '{"item":101}');
	EXCEPTION
		WHEN OTHERS THEN
			-- Revertir inventario si falla facturación
			UPDATE inventario SET stock = stock + 1 WHERE item_id = 101;
			RAISE;
	END;

	COMMIT;
END;

Ventaja:

Garantiza consistencia eventual sin depender de XA o middleware complicado.
Cada paso maneja su propio rollback en caso de fallo.

5. Pattern: Bulk + Queue para Alta Concurrencia

Descripción

PL/SQL actúa como motor de procesamiento de lotes para microservicios, usando BULK COLLECT y FORALL para disminuir latencia y bloqueo.

Ejemplo

DECLARE
	TYPE t_jobs IS TABLE OF job_queue%ROWTYPE;
	v_jobs t_jobs;
BEGIN
	SELECT * BULK COLLECT INTO v_jobs
	FROM job_queue
	WHERE status = 'PENDING';

	FORALL i IN v_jobs.FIRST .. v_jobs.LAST
		UPDATE job_queue
		SET status = 'PROCESSING'
		WHERE job_id = v_jobs(i).job_id;

	-- Enviar a microservicio de procesamiento
	FOR i IN v_jobs.FIRST .. v_jobs.LAST LOOP
		UTL_HTTP.REQUEST('https://api.procesador.com/job', 'POST', '{"job_id":' || v_jobs(i).job_id || '}');
	END LOOP;

	COMMIT;
END;

Ventaja:

Escalabilidad para cientos de miles de jobs diarios.
Reducción de context switches.
Control total desde Oracle, sin saturar microservicios externos.

6. Pattern: Orquestación de Lógica de Negocio Compleja

Descripción

Centralizar reglas críticas de negocio en PL/SQL y exponerlas a microservicios mediante funciones REST o colas, evitando duplicación en varios lenguajes.

Ejemplo — Motor de Reglas

CREATE OR REPLACE FUNCTION calcular_descuento(
	p_cliente_id NUMBER,
	p_monto NUMBER
) RETURN NUMBER AS
	v_desc NUMBER := 0;
BEGIN
	SELECT CASE
		WHEN tipo_cliente = 'VIP' THEN p_monto * 0.15
		WHEN tipo_cliente = 'REGULAR' THEN p_monto * 0.05
		ELSE 0
	END INTO v_desc
	FROM clientes
	WHERE cliente_id = p_cliente_id;

	RETURN p_monto - v_desc;
END;

Ventaja:

Microservicios externos llaman a la función vía REST.
Evita inconsistencias entre sistemas.
Toda la lógica de negocio permanece centralizada y auditada.

7. Pattern: Integración Híbrida con Legacy Systems

Descripción

PL/SQL orquesta interacciones entre microservicios modernos y sistemas legacy (bases antiguas, mainframes), actuando como puente.

Ejemplo — Adaptador de Legacy

DECLARE
	v_data CLOB;
BEGIN
	-- Extraer datos legacy vía DB Link
	SELECT datos_xml INTO v_data FROM legacy_db.remote_table@DBLINK_LEGACY;

	-- Transformar y enviar a microservicio
	UTL_HTTP.REQUEST('https://api.nuevo.com/import', 'POST', v_data);

	COMMIT;
END;

Ventaja:

Evita reescribir sistemas antiguos.
Centraliza integración y control de errores.
Permite migración incremental a microservicios.

8. Pattern: Observabilidad / Logging Centralizado

Descripción

Todos los microservicios reportan eventos a Oracle, que centraliza logs, métricas y alertas usando procedimientos autónomos.

Ejemplo — Logging Autónomo

CREATE OR REPLACE PROCEDURE log_microservicio(
	p_servicio VARCHAR2,
	p_evento VARCHAR2
) AS
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO log_microservicios(servicio, evento, fecha)
	VALUES(p_servicio, p_evento, SYSTIMESTAMP);
	COMMIT;
END;

Ventaja:

Logs consistentes, independientes de transacciones externas.
Facilita observabilidad y alertas en tiempo real.
Ideal para microservicios distribuidos.

Conclusión

Estos patrones muestran cómo PL/SQL puede:

Ser nodo central de orquestación.
Gestionar eventos y colas para microservicios.
Exponer REST / ORDS de forma segura.
Mantener consistencia transaccional incluso en entornos distribuidos.
Servir como motor de reglas de negocio y de procesamiento masivo.

Integrar microservicios con PL/SQL permite modernizar arquitectura sin sacrificar el poder y la consistencia de Oracle Database.

PL/SQL — Anti-Patterns Avanzados (Qué NO hacer en 2025)

1. Bloques Monolíticos Gigantes

Descripción

Escribir un único bloque con cientos de líneas, múltiples loops, DML y lógica de negocio mezclada.

Ejemplo

BEGIN
	FOR i IN 1..10000 LOOP
		UPDATE empleados SET salario = salario * 1.05 WHERE dept_id = i;
		INSERT INTO log_emp VALUES(i, SYSDATE, 'update');
		-- más lógica compleja, condiciones, llamadas a APIs
	END LOOP;
	COMMIT;
END;

Problemas:

Difícil de mantener y depurar.
Alta probabilidad de errores.
Consume demasiados recursos y puede bloquear la base de datos.

Qué hacer en su lugar:

Modularizar en procedimientos, funciones y paquetes.
Usar BULK COLLECT / FORALL.

2. Cursor Row-by-Row (Slow-by-Slow / “Slow by Row”)

Descripción

Procesar fila por fila cuando hay miles o millones de registros, sin usar BULK COLLECT.

Ejemplo

DECLARE
	v_name empleados.last_name%TYPE;
BEGIN
	FOR rec IN (SELECT last_name FROM empleados) LOOP
		-- Procesamiento fila a fila
		v_name := rec.last_name;
		DBMS_OUTPUT.PUT_LINE(v_name);
	END LOOP;
END;

Problemas:

Cambios de contexto SQL ↔ PL/SQL frecuentes.
Rendimiento extremadamente bajo.

Qué hacer en su lugar:

BULK COLLECT + FORALL o procesamiento en SQL directo.

3. Dynamic SQL sin Bind Variables

Descripción

Concatenar cadenas para construir consultas SQL dinámicas, incluyendo valores de entrada.

Ejemplo

DECLARE
	v_sql VARCHAR2(4000);
	v_count NUMBER;
BEGIN
	v_sql := 'SELECT COUNT(*) FROM empleados WHERE dept_id = ' || :dept_id;
	EXECUTE IMMEDIATE v_sql INTO v_count;
END;

Problemas:

Vulnerable a SQL Injection.
Pierde performance por falta de reutilización de plan de ejecución.

Qué hacer en su lugar:

Usar bind variables con EXECUTE IMMEDIATE … USING.

4. Excepciones Silenciosas

Descripción

Capturar todas las excepciones sin registrar ni manejar.

Ejemplo

BEGIN
	UPDATE empleados SET salario = salario * 1.05;
EXCEPTION
	WHEN OTHERS THEN
		NULL; -- Ignora todos los errores
END;

Problemas:

Dificulta debugging.
Puede corromper datos o pasar errores inadvertidos a producción.

Qué hacer en su lugar:

Manejar excepciones específicas.
Registrar errores y tomar acciones correctivas.

5. Commit Frecuente o Dentro de Loops

Descripción

Ejecutar COMMIT dentro de un bucle fila por fila.

Ejemplo

BEGIN
	FOR i IN 1..1000 LOOP
		UPDATE empleados SET salario = salario * 1.05 WHERE emp_id = i;
		COMMIT;
	END LOOP;
END;

Problemas:

Reduce performance.
Fragmenta undo y redo.
Difícil de revertir transacciones.

Qué hacer en su lugar:

Hacer COMMIT fuera de loops, usar BULK.

6. Hardcoding de Parámetros / Valores Mágicos

Descripción

Incluir strings, números o rutas directamente en código PL/SQL.

Ejemplo

BEGIN
	INSERT INTO log VALUES ('/home/oracle/logs/app.log', SYSDATE);
END;

Problemas:

Difícil mantener entre entornos (dev, test, prod).
Difícil de parametrizar.

Qué hacer en su lugar:

Usar tablas de configuración, constantes o variables de entorno.

7. Dependencia Directa de Tablas Externas

Descripción

Hacer SELECT o DML directo en tablas de otros sistemas o legacy desde procesos críticos sin intermediarios.

Ejemplo

BEGIN
	SELECT nombre INTO v_name FROM legacy_db.clientes@dblink;
END;

Problemas:

Fallas externas detienen procesos críticos.
Bloqueos y latencias inesperadas.

Qué hacer en su lugar:

Replicar datos en tablas locales o usar colas/eventos.

8. Triggers Complejos y Multi-Nivel

Descripción

Triggers que disparan otros triggers, con lógica de negocio pesada.

Ejemplo

CREATE OR REPLACE TRIGGER trg_emp AFTER INSERT ON empleados
FOR EACH ROW
BEGIN
	-- Hace múltiples llamadas, cálculos, updates
END;

Problemas:

Difícil depuración.
Riesgo de recursión infinita.
Impacto severo en performance.

Qué hacer en su lugar:

Usar procedimientos almacenados llamados explícitamente.
Evitar lógica pesada en triggers.

9. Uso Excesivo de Autonomous Transactions

Descripción

Hacer commit autónomo en muchos lugares sin control.

Ejemplo

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	INSERT INTO log VALUES('evento', SYSDATE);
	COMMIT;
END;

Problemas:

Dificulta rollback global.
Puede corromper integridad de datos en transacciones distribuidas.

Qué hacer en su lugar:

Usar autonómicos solo para logging crítico o auditoría.
Mantener control central de transacciones.

10. Generación de Informes Dentro de PL/SQL con Lógica Compleja

Descripción

Crear PDFs, CSV o HTML en PL/SQL con loops y formateo masivo.

Problemas:**

Consumo excesivo de CPU y memoria.
Dificultad de mantenimiento.
Mejor delegar a ETL o microservicios especializados.

Qué hacer en su lugar:

Extraer datos desde PL/SQL y procesarlos en un microservicio o aplicación dedicada.

Conclusión

Evitar estos anti-patterns avanzados permite que tu PL/SQL sea:

Mantenible y legible.
Eficiente y escalable.
Seguro y confiable.

Siempre modulariza, usa BULK, maneja errores correctamente y separa lógica de negocio de integraciones externas.

Guía Completa de Entrevistas PL/SQL (2025)

1. Conceptos Clave que Debes Dominar

Diferencia SQL vs PL/SQL:
- SQL: Lenguaje declarativo para manipulación y consulta de datos.
- PL/SQL: Lenguaje procedimental que extiende SQL con variables, loops, condiciones, excepciones y subprogramas.
Bloques PL/SQL:
- Anónimos, procedimientos, funciones, paquetes, triggers.
Tipos de Datos:
- Escalares: NUMBER, VARCHAR2, DATE, BOOLEAN.
- %TYPE / %ROWTYPE para mantener compatibilidad con columnas y tablas.
Control de flujo: IF, CASE, LOOP, WHILE, FOR, EXIT, GOTO (poco usado).
Manejo de errores: Excepciones predefinidas (NO_DATA_FOUND, TOO_MANY_ROWS) y excepciones personalizadas.
Colecciones y tipos avanzados:
- VARRAY, Nested Tables, Associative Arrays.
Operaciones Bulk: BULK COLLECT / FORALL para optimizar procesamiento masivo.
SQL dinámico: EXECUTE IMMEDIATE, DBMS_SQL para consultas flexibles.
Paquetes: Encapsulan funciones, procedimientos, tipos y variables globales.
Triggers: BEFORE, AFTER, INSTEAD OF; fila a fila o statement level.
Integraciones: UTL_HTTP, UTL_FILE, DBMS_SCHEDULER, DBMS_ALERT, JSON/REST con ORDS.

2. Preguntas Técnicas Comunes

SQL vs PL/SQL

¿Cuándo usar SQL y cuándo PL/SQL?
Ejemplo: INSERT masivo, cálculo de comisiones, transformaciones de datos.

Bloques y Procedimientos

Diferencia entre procedimiento y función.
Ventajas de usar paquetes.

Manejo de Errores

¿Cómo se captura una excepción en PL/SQL?
Diferencia entre excepciones predefinidas y definidas por el usuario.
Ejemplo: manejo de NO_DATA_FOUND en SELECT INTO.

Optimización

¿Qué es un BULK COLLECT y cuándo usarlo?
¿Cómo evitar context switches SQL ↔ PL/SQL?

Cursores

Diferencia entre cursores implícitos y explícitos.
Cómo recorrer resultados y liberar recursos.

SQL Dinámico

Ventajas de EXECUTE IMMEDIATE vs DBMS_SQL.
Uso seguro con bind variables para evitar SQL Injection.

Triggers

Tipos de triggers y ejemplos de uso.
Riesgos de triggers complejos y recursivos.

Integraciones

¿Cómo se envía un HTTP POST desde PL/SQL?
Uso de UTL_FILE para generación de CSV o logs.
Integración con JSON y REST usando ORDS.

3. Preguntas Avanzadas / Escenarios Reales

Diseñar un procedimiento que calcule bonificaciones y guarde resultados en tabla histórica.
Detectar cambios en tablas críticas y almacenar auditoría (trigger + tabla).
Procesar millones de registros sin saturar memoria (bulk collect + forall).
Exponer una función PL/SQL a microservicios mediante REST.
Integrar PL/SQL con sistemas legacy o APIs externas sin perder consistencia.
Crear un motor de reglas dinámico configurable desde JSON.

4. Preguntas de Optimización y Buenas Prácticas

Diferencia entre row-by-row y BULK processing.
Cuándo usar paquetes vs procedimientos sueltos.
Cómo evitar locks y deadlocks en transacciones masivas.
Uso adecuado de commit, autononomous transaction y manejo de rollback.
Evitar triggers complejos que disparen otros triggers.

5. Preguntas de Diseño y Arquitectura

Cómo diseñar un paquete para lógica de negocio reutilizable.
Cómo exponer PL/SQL a microservicios sin duplicar lógica.
Diseño de auditoría o logs centralizados.
Patrones de ETL internos con pipelines, bulk y cache de resultados.
Integración con ORDS y RESTful services.

6. Ejercicios Prácticos Comunes

Escribir un procedimiento que:
1. Seleccione clientes con contratos próximos a vencer.
2. Genere CSV con sus datos.
3. Inserte log de envío de notificaciones.
Crear un trigger que:
1. Detecte cambios de saldo en cuentas bancarias.
2. Inserte registro de auditoría.
Optimizar un loop que actualiza 1 millón de registros en tabla ventas.
Generar un motor de scoring que:
1. Reciba parámetros dinámicos.
2. Devuelva calificación calculada según reglas configurables en JSON.

7. Recursos para Preparación

[PL/SQL para desarrolladores Oracle España](https://www.oracle.com/es/database/technologies/appdev/plsql.html)
Oracle Database PL/SQL Language Reference 2025.
Oracle Live SQL — Practicar ejercicios online.
Cursos online: OpenWebinars, Udemy, Coursera.
Libros recomendados: Oracle PL/SQL Programming de Steven Feuerstein, última edición.

8. Tips Finales para la Entrevista

Sé capaz de explicar tus decisiones de diseño y optimización.
Familiarízate con casos reales: comisiones, auditoría, ETL, integración microservicios.
Practica escribir procedimientos, funciones, triggers y paquetes en Oracle Live SQL.
Prepárate para preguntas sobre errores comunes y anti-patterns.
Explica cómo mantienes consistencia y performance en sistemas críticos.

PL/SQL — Respuestas y Hoja de Trucos para Entrevistas (2025)

1. Conceptos Clave — Respuestas Rápidas

SQL vs PL/SQL:
- SQL: declarativo, solo consulta/manipula datos.
- PL/SQL: procedimental, combina SQL con variables, loops, excepciones y subprogramas.
Bloques PL/SQL:
- Anónimo: Ejecutable inmediato, no reutilizable.
- Procedimiento: Realiza acciones, puede recibir parámetros, no devuelve valor.
- Función: Devuelve un valor, se puede usar en SQL.
- Paquete: Agrupa procedimientos, funciones, tipos y variables globales.
- Trigger: Ejecuta lógica automáticamente ante eventos DML o DDL.
Tipos de Datos:
- Escalares: NUMBER, VARCHAR2, DATE, BOOLEAN.
- %TYPE / %ROWTYPE: para mantener compatibilidad automática con columnas y tablas.
Control de flujo: IF, CASE, LOOP, WHILE, FOR, EXIT.
Manejo de errores: Excepciones predefinidas (NO_DATA_FOUND, TOO_MANY_ROWS), excepciones definidas por usuario.
Colecciones: VARRAY, Nested Tables, Associative Arrays.
Operaciones Bulk: BULK COLLECT y FORALL para optimización de procesamiento masivo.
SQL dinámico: EXECUTE IMMEDIATE + bind variables para seguridad y performance.
Integraciones: UTL_HTTP, UTL_FILE, DBMS_SCHEDULER, DBMS_ALERT, JSON, REST/ORDS.

2. Preguntas Técnicas — Respuestas Cortas

Diferencia SQL vs PL/SQL: SQL = consulta; PL/SQL = lógica + SQL.
Procedimiento vs Función: Procedimiento = no retorna valor; Función = retorna valor.
Paquetes: Encapsulan y organizan código, permiten variables globales, modularidad y mantenimiento.
Excepciones: NO_DATA_FOUND captura SELECT INTO vacío; TOO_MANY_ROWS captura más de una fila.
BULK COLLECT / FORALL: Para evitar procesamiento fila a fila y context switches SQL↔PL/SQL.
Cursor implícito vs explícito: Implícito = automático en SELECT INTO; explícito = OPEN/FETCH/LOOP/ CLOSE.
SQL Dinámico: Usa bind variables para seguridad y plan reutilizable.
Trigger: Evitar lógica pesada y recursión.

3. Escenarios Reales — Respuestas / Tips

Procedimiento de bonificaciones: Usar BULK COLLECT para leer empleados, FORALL para updates masivos.
Auditoría en tabla histórica: Trigger AFTER INSERT/UPDATE + autononomous transaction para logging seguro.
Procesamiento millones de registros: Bulk + FORALL, evitar row-by-row.
Exposición a microservicios: ORDS REST endpoints para funciones PL/SQL.
Motor de reglas dinámico: Almacenar reglas en JSON + EXECUTE IMMEDIATE seguro con bind.

4. Optimización — Trucos

Evitar loops fila a fila → BULK COLLECT / FORALL.
Commit fuera de loops; autonómicos solo para logging/auditoría.
Usar paquetes para centralizar lógica.
Evitar triggers multi-nivel complejos.
SQL dinámico = bind variables.
Profiling: DBMS_PROFILER, DBMS_TRACE.
Result Cache para cálculos repetitivos.

5. Diseño / Arquitectura — Hoja de Trucos

Orquestación Centralizada: PL/SQL coordina microservicios y transacciones.
Event-Driven: Tabla de colas + triggers → microservicios reaccionan a eventos.
API Gateway / ORDS: Exponer procedimientos y funciones como REST.
Saga / Transacciones Distribuidas: Cada paso controlado; rollback parcial si falla.
Bulk + Queue: Procesamiento masivo eficiente.
Motor de reglas: JSON configurable; función PL/SQL centralizada.
Integración con legacy: DB Link o ETL incremental.
Logging centralizado: Pragma autonomous_transaction para logs independientes.

6. Anti-Patterns — Recordatorio

Bloques monolíticos gigantes → modularizar.
Row-by-row loops → BULK COLLECT / FORALL.
Dynamic SQL sin bind → riesgo SQL Injection.
Excepciones silenciosas → siempre manejar y loggear.
Commit dentro de loops → mover fuera.
Hardcoding → usar tablas de configuración.
Dependencia directa en sistemas externos → replicar/localizar datos.
Triggers complejos multi-nivel → usar procedimientos explícitos.
Autonómicos excesivos → solo logs críticos.
Informes complejos dentro de PL/SQL → delegar a microservicios o ETL.

7. Ejercicios Comunes — Respuestas Rápidas

Clientes próximos a vencer: SELECT INTO BULK COLLECT, generar CSV con UTL_FILE, log con autonómicos.
Trigger auditoría: AFTER UPDATE → insert en tabla log.
Optimización loop 1 millón registros: BULK COLLECT + FORALL + COMMIT en batches.
Motor de scoring dinámico: JSON reglas + EXECUTE IMMEDIATE + bind.

8. Atajos y Comandos Útiles

DECLARE ... BEGIN ... END; → bloque anónimo.
CREATE OR REPLACE PROCEDURE nombre IS ... END; → procedimiento.
CREATE OR REPLACE FUNCTION nombre RETURN tipo IS ... END; → función.
%TYPE / %ROWTYPE → mantiene consistencia de tipos.
BULK COLLECT INTO → cargar varias filas en colecciones.
FORALL → ejecutar DML masivo.
EXECUTE IMMEDIATE → SQL dinámico.
DBMS_OUTPUT.PUT_LINE() → debug.
PRAGMA AUTONOMOUS_TRANSACTION → commit independiente.
DBMS_PROFILER / DBMS_TRACE → perfilado de performance.
ORDS → exponer funciones como REST.
JSON_OBJECT_T / JSON_ARRAY_T → manipulación JSON en PL/SQL.

Tip final:
Antes de la entrevista, repasar:

Sintaxis rápida de bloque/procedimiento/función/paquete.
Ejemplos BULK COLLECT / FORALL.
Manejo de excepciones comunes y custom.
Triggers simples vs complejos y cómo evitarlos.
Casos de integración microservicios / REST / JSON.
Anti-patterns para mostrar conocimiento avanzado y buenas prácticas.