Módulo 03: Uso de Sentencias DML en PL/SQL

Objetivos del módulo

• Ejecutar sentencias INSERT, UPDATE y DELETE dentro de bloques PL/SQL
• Utilizar SELECT INTO para recuperar datos en variables
• Conocer y usar los atributos implícitos del cursor SQL (SQL%ROWCOUNT, SQL%FOUND, etc.)
• Gestionar transacciones con COMMIT, ROLLBACK y SAVEPOINT
• Aplicar buenas prácticas de DML dentro de PL/SQL

---

1. Sentencias DML dentro de PL/SQL

Una de las grandes ventajas de PL/SQL es que puedes usar sentencias SQL de manipulación de datos (INSERT, UPDATE, DELETE, SELECT) directamente dentro de tus bloques, mezclándolas con lógica procedural.

📘 Concepto: En PL/SQL, las sentencias DML se escriben tal cual como en SQL, pero con la ventaja de poder usar variables PL/SQL en lugar de valores literales. Esto hace que el código sea dinámico y reutilizable.

---

2. SELECT INTO

SELECT INTO es la forma de obtener datos de la base de datos y almacenarlos en variables PL/SQL:

2.1. Seleccionar un único valor

DECLARE
    v_num_multas NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_num_multas
    FROM multa;
    
    DBMS_OUTPUT.PUT_LINE('Total de multas: ' || v_num_multas);
END;
/

2.2. Seleccionar múltiples columnas

DECLARE
    v_nombre    conductor.nombre%TYPE;
    v_fec_nac   conductor.fec_nacimiento%TYPE;
    v_fec_car   conductor.fec_carnet%TYPE;
BEGIN
    SELECT nombre, fec_nacimiento, fec_carnet
    INTO v_nombre, v_fec_nac, v_fec_car
    FROM conductor
    WHERE ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('Nombre:     ' || v_nombre);
    DBMS_OUTPUT.PUT_LINE('Nacimiento: ' || v_fec_nac);
    DBMS_OUTPUT.PUT_LINE('Carnet:     ' || v_fec_car);
END;
/

2.3. Seleccionar en un registro %ROWTYPE

DECLARE
    r_vehiculo vehiculo%ROWTYPE;
BEGIN
    SELECT * INTO r_vehiculo
    FROM vehiculo
    WHERE ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('Matrícula: ' || r_vehiculo.matricula);
    DBMS_OUTPUT.PUT_LINE('Marca:     ' || r_vehiculo.marca);
    DBMS_OUTPUT.PUT_LINE('Color:     ' || NVL(r_vehiculo.color, 'Sin color'));
END;
/

⚠️ Importante: SELECT INTO debe devolver exactamente una fila:

• Si no devuelve ninguna fila → excepción NO_DATA_FOUND
• Si devuelve más de una fila → excepción TOO_MANY_ROWS

Para procesar múltiples filas, usa cursores (Módulo 04).

---

3. INSERT en PL/SQL

Las sentencias INSERT se escriben igual que en SQL, pero pueden usar variables:

3.1. INSERT con valores de variables

DECLARE
    v_matricula   VARCHAR2(10) := '1234ABC';
    v_marca       VARCHAR2(30) := 'Toyota';
    v_color       VARCHAR2(20) := 'Rojo';
BEGIN
    INSERT INTO vehiculo (matricula, marca, color)
    VALUES (v_matricula, v_marca, v_color);
    
    DBMS_OUTPUT.PUT_LINE('Vehículo insertado correctamente');
    
    COMMIT;
END;
/

3.2. INSERT con SELECT

BEGIN
    -- Insertar datos que provienen de otra consulta
    INSERT INTO vehiculo_backup (matricula, marca, color)
    SELECT matricula, marca, color
    FROM vehiculo
    WHERE color = 'Rojo';
    
    DBMS_OUTPUT.PUT_LINE('Registros copiados: ' || SQL%ROWCOUNT);
    
    COMMIT;
END;
/

3.3. INSERT con RETURNING INTO

RETURNING INTO permite obtener valores generados por la inserción (por ejemplo, IDs de secuencias):

DECLARE
    v_nuevo_id NUMBER;
BEGIN
    INSERT INTO infraccion (id, codigo, descripcion, puntos, valor_economico, vigente)
    VALUES (seq_infraccion.NEXTVAL, 'INF-NEW', 'Infracción de prueba', 2, 100, 1)
    RETURNING id INTO v_nuevo_id;
    
    DBMS_OUTPUT.PUT_LINE('Nueva infracción creada con ID: ' || v_nuevo_id);
    
    ROLLBACK;  -- Deshacemos para no ensuciar los datos
END;
/

💡 Truco: RETURNING INTO es muy útil para obtener el valor de columnas con valores por defecto, secuencias o SYSDATE sin necesidad de hacer una consulta adicional.

---

4. UPDATE en PL/SQL

4.1. UPDATE básico con variables

DECLARE
    v_nuevo_color VARCHAR2(20) := 'Azul';
    v_matricula   VARCHAR2(10) := '1234ABC';
BEGIN
    UPDATE vehiculo
    SET color = v_nuevo_color
    WHERE matricula = v_matricula;
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Vehículo actualizado. Filas afectadas: ' || SQL%ROWCOUNT);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No se encontró el vehículo con matrícula: ' || v_matricula);
    END IF;
    
    COMMIT;
END;
/

4.2. UPDATE con subconsulta

BEGIN
    -- Actualizar el valor económico de infracciones incrementándolo un 10%
    UPDATE infraccion
    SET valor_economico = valor_economico * 1.10
    WHERE vigente = 1;
    
    DBMS_OUTPUT.PUT_LINE('Infracciones actualizadas: ' || SQL%ROWCOUNT);
    
    ROLLBACK;  -- Deshacemos la subida de precios de prueba
END;
/

4.3. UPDATE con RETURNING INTO

DECLARE
    v_importe_anterior infraccion.valor_economico%TYPE;
    v_importe_nuevo    infraccion.valor_economico%TYPE;
BEGIN
    UPDATE infraccion
    SET valor_economico = valor_economico * 1.05
    WHERE ROWNUM = 1
    RETURNING valor_economico INTO v_importe_nuevo;
    
    DBMS_OUTPUT.PUT_LINE('Nuevo importe: ' || v_importe_nuevo);
    
    ROLLBACK;
END;
/

---

5. DELETE en PL/SQL

5.1. DELETE básico

DECLARE
    v_fecha_limite DATE := ADD_MONTHS(SYSDATE, -60);  -- Hace 5 años
BEGIN
    DELETE FROM multa
    WHERE fecha < v_fecha_limite;
    
    DBMS_OUTPUT.PUT_LINE('Multas antiguas eliminadas: ' || SQL%ROWCOUNT);
    
    ROLLBACK;  -- Deshacemos para no perder datos
END;
/

5.2. DELETE con condición compuesta

BEGIN
    DELETE FROM infraccion
    WHERE vigente = 0
    AND valor_economico < 50;
    
    IF SQL%ROWCOUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Infracciones obsoletas eliminadas: ' || SQL%ROWCOUNT);
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No se encontraron infracciones que cumplan los criterios');
    END IF;
END;
/

---

6. Atributos implícitos del cursor SQL

Después de cada sentencia DML, Oracle actualiza automáticamente unos atributos implícitos que proporcionan información sobre el resultado de la operación:

Atributo	Tipo	Descripción
SQL%ROWCOUNT	NUMBER	Número de filas afectadas por la última sentencia DML
SQL%FOUND	BOOLEAN	TRUE si la última sentencia DML afectó al menos a una fila
SQL%NOTFOUND	BOOLEAN	TRUE si la última sentencia DML no afectó a ninguna fila
SQL%ISOPEN	BOOLEAN	Siempre FALSE para cursores implícitos (se cierran automáticamente)

📘 Concepto: Estos atributos se refieren siempre a la última sentencia DML ejecutada. Si ejecutas un UPDATE seguido de un INSERT, los atributos reflejarán el resultado del INSERT, no del UPDATE.

Ejemplo completo con atributos

BEGIN
    -- Intentar actualizar infracciones con código inexistente
    UPDATE infraccion
    SET valor_economico = 999
    WHERE codigo = 'INEXISTENTE';
    
    DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
    
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('SQL%FOUND: TRUE - Se actualizaron filas');
    ELSE
        DBMS_OUTPUT.PUT_LINE('SQL%FOUND: FALSE - No se actualizó ninguna fila');
    END IF;
    
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND: TRUE - No se encontraron coincidencias');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN: ' || 
        CASE WHEN SQL%ISOPEN THEN 'TRUE' ELSE 'FALSE' END);
    
    ROLLBACK;
END;
/

---

7. Gestión de transacciones

7.1. COMMIT (confirmar cambios)

COMMIT hace permanentes todos los cambios realizados desde el último COMMIT o ROLLBACK:

BEGIN
    INSERT INTO vehiculo (matricula, marca, color)
    VALUES ('9999ZZZ', 'Ford', 'Negro');
    
    UPDATE vehiculo
    SET color = 'Blanco'
    WHERE matricula = '9999ZZZ';
    
    -- Ambas operaciones se confirman de forma atómica
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Transacción confirmada');
END;
/

7.2. ROLLBACK (deshacer cambios)

ROLLBACK deshace todos los cambios no confirmados:

BEGIN
    DELETE FROM multa WHERE ROWNUM <= 5;
    DBMS_OUTPUT.PUT_LINE('Filas eliminadas: ' || SQL%ROWCOUNT);
    
    -- Deshacemos los cambios
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Cambios deshechos con ROLLBACK');
END;
/

7.3. SAVEPOINT (puntos de guardado)

SAVEPOINT permite hacer ROLLBACK parcial, deshaciendo solo hasta un punto concreto:

BEGIN
    INSERT INTO vehiculo (matricula, marca, color)
    VALUES ('1111AAA', 'Seat', 'Rojo');
    
    SAVEPOINT sp_despues_insert;
    
    UPDATE vehiculo
    SET color = 'Verde'
    WHERE matricula = '1111AAA';
    
    -- Solo deshacemos el UPDATE, el INSERT se mantiene
    ROLLBACK TO sp_despues_insert;
    
    -- Confirmamos el INSERT
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Solo el INSERT fue confirmado');
END;
/

⚠️ Importante: Un COMMIT o ROLLBACK completo elimina todos los SAVEPOINT definidos. Además, las sentencias DDL (CREATE, ALTER, DROP) ejecutan un COMMIT implícito, por lo que no se pueden deshacer con ROLLBACK.

---

8. DML con lógica procedural: ejemplo completo

Un ejemplo que combina todas las operaciones DML con lógica de control:

DECLARE
    v_num_conductores  NUMBER;
    v_num_multas       NUMBER;
    v_ratio            NUMBER(10,2);
BEGIN
    -- Obtener estadísticas
    SELECT COUNT(*) INTO v_num_conductores FROM conductor;
    SELECT COUNT(*) INTO v_num_multas FROM multa;
    
    DBMS_OUTPUT.PUT_LINE('=== Informe de Multas ===');
    DBMS_OUTPUT.PUT_LINE('Conductores: ' || v_num_conductores);
    DBMS_OUTPUT.PUT_LINE('Multas:      ' || v_num_multas);
    
    -- Calcular ratio
    IF v_num_conductores > 0 THEN
        v_ratio := v_num_multas / v_num_conductores;
        DBMS_OUTPUT.PUT_LINE('Ratio multas/conductor: ' || TO_CHAR(v_ratio, '990.00'));
        
        -- Evaluar la situación
        CASE
            WHEN v_ratio < 1  THEN DBMS_OUTPUT.PUT_LINE('Situación: BUENA');
            WHEN v_ratio < 3  THEN DBMS_OUTPUT.PUT_LINE('Situación: REGULAR');
            WHEN v_ratio >= 3 THEN DBMS_OUTPUT.PUT_LINE('Situación: PREOCUPANTE');
        END CASE;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No hay conductores registrados');
    END IF;
END;
/

---

9. Buenas prácticas con DML en PL/SQL

💡 Truco: Sigue estas recomendaciones para escribir código DML robusto:

1. Siempre comprueba SQL%ROWCOUNT después de un UPDATE o DELETE para verificar que afectó a las filas esperadas.
2. Usa COMMIT de forma consciente: no hagas COMMIT dentro de un bucle (puede generar incoherencias si falla a mitad). Confirma al final de la unidad lógica de trabajo.
3. Usa SAVEPOINT cuando necesites deshacer parcialmente operaciones complejas.
4. Usa RETURNING INTO en lugar de hacer un SELECT adicional después de INSERT o UPDATE.
5. Usa %TYPE en las variables que reciben datos de columnas para evitar inconsistencias de tipos.

---

10. Ejercicios prácticos

Ejercicio 1: SELECT INTO con cálculos

Obtén el valor medio de las infracciones vigentes y muéstralo formateado:

DECLARE
    v_media infraccion.valor_economico%TYPE;
BEGIN
    SELECT AVG(valor_economico) INTO v_media
    FROM infraccion
    WHERE vigente = 1;
    
    DBMS_OUTPUT.PUT_LINE('Valor medio de infracciones vigentes: ' || 
                         TO_CHAR(v_media, '999G999D00') || ' €');
END;
/

Ejercicio 2: INSERT condicional

Inserta un vehículo solo si su matrícula no existe ya en la tabla:

DECLARE
    v_matricula VARCHAR2(10) := '5555BBB';
    v_existe    NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_existe
    FROM vehiculo
    WHERE matricula = v_matricula;
    
    IF v_existe = 0 THEN
        INSERT INTO vehiculo (matricula, marca, color)
        VALUES (v_matricula, 'Renault', 'Gris');
        
        DBMS_OUTPUT.PUT_LINE('Vehículo insertado correctamente');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('El vehículo con matrícula ' || v_matricula || ' ya existe');
    END IF;
END;
/

Ejercicio 3: UPDATE con verificación

Incrementa el valor económico de las infracciones en un 5% solo si hay más de 10 infracciones vigentes:

DECLARE
    v_num_vigentes NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_num_vigentes
    FROM infraccion
    WHERE vigente = 1;
    
    IF v_num_vigentes > 10 THEN
        UPDATE infraccion
        SET valor_economico = valor_economico * 1.05
        WHERE vigente = 1;
        
        DBMS_OUTPUT.PUT_LINE('Actualizadas ' || SQL%ROWCOUNT || ' infracciones');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Solo hay ' || v_num_vigentes || 
                             ' infracciones vigentes. No se actualiza.');
    END IF;
END;
/

Ejercicio 4: Transacción con SAVEPOINT

Realiza una operación compuesta: inserta un vehículo, crea un savepoint, inserta una multa. Si la multa falla (cantidad no válida), deshaz solo la multa pero mantén el vehículo:

DECLARE
    v_importe NUMBER := -50;  -- Importe inválido: negativo
BEGIN
    -- Paso 1: Insertar vehículo
    INSERT INTO vehiculo (matricula, marca, color)
    VALUES ('7777XXX', 'BMW', 'Azul');
    DBMS_OUTPUT.PUT_LINE('Vehículo insertado');
    
    SAVEPOINT sp_vehiculo_ok;
    
    -- Paso 2: Validar e insertar multa
    IF v_importe > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Multa registrada');
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('Importe de multa no válido. Deshaciendo multa...');
        ROLLBACK TO sp_vehiculo_ok;
        COMMIT;  -- El vehículo sigue insertado
    END IF;
END;
/

---

Resumen

Concepto	Detalle
SELECT INTO	Asigna el resultado de una consulta (una sola fila) a variables PL/SQL
INSERT	Inserta filas usando variables PL/SQL como valores
UPDATE	Modifica filas usando variables PL/SQL en SET y WHERE
DELETE	Elimina filas usando variables PL/SQL en la condición WHERE
RETURNING INTO	Obtiene valores generados tras INSERT o UPDATE sin consulta adicional
SQL%ROWCOUNT	Número de filas afectadas por la última sentencia DML
SQL%FOUND	TRUE si la última DML afectó al menos una fila
SQL%NOTFOUND	TRUE si la última DML no afectó a ninguna fila
SQL%ISOPEN	Siempre FALSE para cursores implícitos
COMMIT	Confirma todos los cambios pendientes de forma permanente
ROLLBACK	Deshace todos los cambios no confirmados
SAVEPOINT	Marca un punto para poder hacer ROLLBACK TO parcial