Anatomía de un Bloque PL/SQL

Un programa en PL/SQL se estructura fundamentalmente en bloques. Cada bloque se divide en tres secciones lógicas: la sección de declaración, la sección ejecutable y la sección de manejo de excepciones.

La sintaxis básica de un bloque es la siguiente:

DECLARE
   -- Sección de declaraciones: variables, tipos, cursores, procedimientos y funciones locales
BEGIN
   -- Sección ejecutable: sentencias SQL y lógica del programa (obligatoria)
EXCEPTION
   -- Sección de excepciones: manejo de errores
END;

La sección BEGIN ... END; es la única que no puede omitirse. Los bloques en PL/SQL se clasifican en:

• Bloques anónimos: Se construyen dinámicamente y se ejecutan una sola vez. Pueden invocar otros programas, pero no pueden ser llamados por ellos.
• Bloques nombrados: Bloques anónimos que incluyen una etiqueta o nombre.
• Subprogramas: Procedimientos y funciones almacenados en la base de datos que pueden ser reutilizados.
• Triggers (Disparadores): Bloques que se ejecutan automáticamente en respuesta a eventos específicos en la base de datos.
• Paquetes (Packages): Conjuntos de subprogramas y variables almacenados juntos en la base de datos.

Los bloques pueden anidarse; un subbloque puede existir dentro de cualquier sección de un bloque principal y se comporta como una instrucción más dentro del flujo del programa.

Identificadores y Convenciones de Nomenclatura

Los identificadores en PL/SQL deben seguir reglas estrictas similares a las de SQL:

• Longitud máxima de 30 caracteres.
• El primer carácter debe ser una letra.
• No distinguen entre mayúsculas y minúsculas.
• No pueden contener el guion (-).
• No pueden ser palabras reservadas de SQL.

Nota importante: Evite usar nombres de variables idénticos a los nombres de las columnas de las tablas para prevenir ambigüedades y resultados inesperados en las consultas.

Se recomienda seguir una convención de nomenclatura estandarizada:

Tipos de Datos y Variables

Tipos de Datos Escalares

Oracle proporciona diversos tipos de datos escalares para PL/SQL:

Uso de la Cláusula RETURNING

La cláusula RETURNING INTO permite recuperar datos de las filas afectadas por sentencias DML directamente en variables PL/SQL.

DECLARE
   v_row_identifier ROWID;
   v_department_info VARCHAR2(100);
BEGIN
   INSERT INTO departments (department_id, department_name, location)
   VALUES (105, 'Recursos Humanos', 'Madrid')
   RETURNING rowid, department_name || ':' || department_id || ':' || location
   INTO v_row_identifier, v_department_info;
   
   DBMS_OUTPUT.PUT_LINE('ROWID: ' || v_row_identifier);
   DBMS_OUTPUT.PUT_LINE('Info: ' || v_department_info);
END;

Esta misma lógica aplica para UPDATE y DELETE. Restricciones: no se puede usar con objetos remotos, no recupera tipos LONG, y en vistas solo funciona si se inserta/borra en una sola tabla base.

Tipos de Datos Compuestos

Registros (RECORD)

Permiten agrupar variables de diferentes tipos bajo un solo nombre, similar a las estructuras en otros lenguajes.

DECLARE
   TYPE developer_profile IS RECORD(
      full_name VARCHAR2(50) NOT NULL := 'Carlos Ramirez',
      specialization VARCHAR2(100)
   );
   v_dev developer_profile;
BEGIN
   v_dev.specialization := 'Arquitectura de Base de Datos';
   DBMS_OUTPUT.PUT_LINE(v_dev.full_name || ' - ' || v_dev.specialization);
END;

Arreglos de Tamaño Variable (VARRAY)

Colecciones de elementos del mismo tipo con un tamaño máximo definido.

DECLARE
   TYPE tech_stack_array IS VARRAY(4) OF VARCHAR2(30);
   v_skills tech_stack_array;
BEGIN
   v_skills := tech_stack_array('Oracle', 'Java', 'Python', 'Docker');
   DBMS_OUTPUT.PUT_LINE('Primera tecnología: ' || v_skills(1));
   v_skills(4) := 'Kubernetes';
   DBMS_OUTPUT.PUT_LINE('Cuarta tecnología actualizada: ' || v_skills(4));
END;

Atributos %TYPE y %ROWTYPE

El atributo %TYPE declara una variable con el mismo tipo de datos que una columna de tabla o otra variable, garantizando independencia ante cambios en el esquema.

DECLARE
   v_employee_id staff.emp_id%TYPE := 101;
   v_employee_name staff.emp_name%TYPE;
BEGIN
   SELECT emp_name INTO v_employee_name FROM staff WHERE emp_id = v_employee_id;
   DBMS_OUTPUT.PUT_LINE('Nombre: ' || v_employee_name);
END;

El atributo %ROWTYPE crea un registro que representa una fila completa de una tabla o cursor.

DECLARE
   v_target_id staff.emp_id%TYPE := 102;
   r_employee staff%ROWTYPE;
BEGIN
   SELECT * INTO r_employee FROM staff WHERE emp_id = v_target_id;
   DBMS_OUTPUT.PUT_LINE('Nombre: ' || r_employee.emp_name || ', Salario: ' || r_employee.salary);
END;

Tipos LOB y Variables de Enlace

Para almacenar grandes volúmenes de datos, Oracle utiliza tipos LOB: BFILE (archivos externos), BLOB (binarios hasta 4GB), CLOB (caracteres hasta 4GB) y NCLOB (caracteres nacionales).

Las variables de enlace (Bind Variables) se crean en el entorno anfitrión (como SQL*Plus) y se referencian en PL/SQL con dos puntos (:).

VARIABLE v_bonus_total NUMBER;
BEGIN
   SELECT (salary * 12) + NVL(commission, 0) INTO :v_bonus_total 
   FROM staff WHERE emp_id = 7844;
END;
/
PRINT v_bonus_total;

Tablas PL/SQL (Associative Arrays)

Las tablas indexadas permiten almacenar múltiples filas de datos en memoria, actuando como arreglos asociativos.

DECLARE
   TYPE branch_table_type IS TABLE OF branches%ROWTYPE INDEX BY PLS_INTEGER;
   v_branches branch_table_type;
BEGIN
   FOR i IN 1..3 LOOP
      SELECT * INTO v_branches(i) FROM branches WHERE branch_id = i * 10;
   END LOOP;
   
   FOR i IN v_branches.FIRST .. v_branches.LAST LOOP
      DBMS_OUTPUT.PUT_LINE('Sucursal: ' || v_branches(i).branch_name);
   END LOOP;
END;

Métodos útiles para colecciones: EXISTS(n), COUNT, FIRST, LAST, NEXT(n), PRIOR(n), EXTEND, TRIM, DELETE.

Operadores, Asignación y Conversiones

Operadores

PL/SQL soporta operadores relacionales (=, <>, <, >, <=, >=), aritméticos (+, -, *, /), de asignación (:=), de concatenación (||) y lógicos (AND, OR, NOT, IS NULL, BETWEEN, IN).

Asignación de Variables

La asignación se realiza mediante :=. Al operar con NULL, cualquier suma aritmética resulta en NULL, pero la concatenación de cadenas trata el NULL como una cadena vacía.

Las asignaciones desde la base de datos se realizan con SELECT ... INTO. No se pueden asignar valores de columnas directamente a variables BOOLEAN desde SQL.

DECLARE
   v_target_id staff.emp_id%TYPE := 103;
   v_name staff.emp_name%TYPE;
   v_total_compensation NUMBER;
BEGIN
   SELECT emp_name, NVL(salary, 0) + NVL(commission, 0) 
   INTO v_name, v_total_compensation 
   FROM staff WHERE emp_id = v_target_id;
   DBMS_OUTPUT.PUT_LINE(v_name || ' - Compensación: ' || v_total_compensation);
END;

Conversiones de Tipo

• CHAR a NUMBER: v_total := TO_NUMBER('150.50') + salary;
• NUMBER a CHAR: v_msg := TO_CHAR(salary) || ' euros';
• CHAR a DATE: v_date := TO_DATE('2023.10.25', 'yyyy.mm.dd');
• DATE a CHAR: v_str := TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss');

Alcance, Visibilidad y Comentarios

El alcance de una variable está limitado al bloque en el que fue declarada. Si un subbbloque declara una varible con el mismo nombre, esta "oculta" la variable del bloque principal dentro de su propio ámbito. Al finalizar el bloque, la memoria asignada a la variable se libera.

DECLARE
   v_error_msg VARCHAR2(100);
BEGIN
   DECLARE
      v_id NUMBER(4);
   BEGIN
      SELECT emp_id INTO v_id FROM staff WHERE LOWER(role) = 'director';
      DBMS_OUTPUT.PUT_LINE('ID Director: ' || v_id);
   EXCEPTION
      WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('Múltiples directores encontrados');
   END;
EXCEPTION
   WHEN OTHERS THEN
      v_error_msg := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
END;

Los comentarios de una sola línea se inician con --, mientras que los comentarios multilínea se encierran entre /* y */. El motor de Oracle suele descartar los comentarios iniciales al compilar procedimientos almacenados.

Ejemplos Prácticos Integrados

A continuación, se muestra un bloque anónimo que ejecuta una inserción y posteriormente elimina el registro insertado, demostrando el flujo completo de transacción.

DECLARE
   v_project_id   projects.proj_id%TYPE := 9901;
   v_project_name projects.proj_name%TYPE := 'Migracion Cloud';
   v_budget       projects.budget%TYPE := 150000.00;
   v_start_date   projects.start_date%TYPE;
BEGIN
   -- Inserción de datos
   INSERT INTO projects (proj_id, proj_name, budget, start_date)
   VALUES (v_project_id, v_project_name, v_budget, TO_DATE('2023.11.01', 'yyyy.mm.dd'));
   
   DBMS_OUTPUT.PUT_LINE('Proyecto ' || v_project_name || ' insertado correctamente.');
   COMMIT;

   -- Eliminación de datos
   DELETE FROM projects WHERE proj_id = v_project_id;
   
   DBMS_OUTPUT.PUT_LINE('Proyecto con ID ' || v_project_id || ' eliminado.');
   COMMIT;
END;