Capítulo 5 - Sentencias DML en PL/SQL

Como se indicó desde el principio, la utilidad de PL/SQL es la manipulación de datos, es por éste motivo que se pueden usar las siguientes sentencias DML de SQL para consultar, insertar o manipular datos:

SELECT INTO

INSERT INTO

UPDATE

EXECUTE IMMEDIATE

SELECT INTO

La sentencia SELECT INTO permite asignar valores a una variable a partir de una consulta SELECT. Por cada ítem o columna seleccionada debe existir una variable del mismo tipo de dato en la cláusula INTO:

DECLARE
  V_PROMEDIO NUMBER;
BEGIN
  SELECT AVG(salary) INTO V_PROMEDIO
    FROM  hr.employees;
  DBMS_OUTPUT.PUT_LINE('El promedio de salario es: ' || V_PROMEDIO);
END;

El promedio de salario es: 6461,831775700934579439252336448598130841

En el script anterior, la función AVG (Average - Promedio) obtiene el sueldo(salary) promedio de los empleados(hr.employees), el valor calculado es asignado a la variable V_PROMEDIO a través de la cláusula INTO.

Ahora veámos un ejemplo obteniendo los datos personales y valor de comisión de un determinado empleado:

DECLARE
  V_NOMBRES   VARCHAR2(200);
  V_APELLIDOS VARCHAR2(200);
  V_COMISION  NUMBER;
  V_ID_EMPLE  NUMBER := 161; --Para probar, puede cambiar el valor
                             --de la variable V_ID_EMPLE(ejem,168).
BEGIN
  -- Obtenemos los apellidos, nombres y comisión
  -- del empleado V_ID_EMPLE, en éste caso con id 100
  SELECT last_name, first_name, commission_pct
    INTO V_APELLIDOS, V_NOMBRES, V_COMISION
    FROM hr.employees
   WHERE employee_id = V_ID_EMPLE; --Se pueden usar variables
                                   --para filtrar registros con WHERE

  DBMS_OUTPUT.PUT_LINE('Apellidos:'  || V_APELLIDOS);
  DBMS_OUTPUT.PUT_LINE('Nombres:'  || V_NOMBRES);

  IF V_COMISION IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('El empleado no tiene comisión asignada');
  ELSE
    DBMS_OUTPUT.PUT_LINE('La comisión por venta del empleado es: '
                         || (V_COMISION*100) || '%');
  END IF;
END;

Apellidos:Sewall
Nombres:Sarath
La comisión por venta del empleado es: 25%

INSERT, UPDATE y DELETE

Las sentencias DML INSERT y UPDATE funcionan de manera directa en PL/SQL, adicionalmente puede hacer uso de variables y valores constantes. Para realizar una prueba:

--  Created with Kata Kuntur - Data Modeller
--  Version: 2.5.0
--  Web Site: http://katakuntur.jeanmazuelos.com/
--  If you find a bug, please report it at:
--  http://pm.jeanmazuelos.com/katakuntur/issues/new

--  Database Management System: Oracle DataBase
--  Diagram: PL/SQL Tutorial
--  Author: Jean Mazuelos
--  Date and time: 21/04/2014 15:07:51

-- GENERATING TABLES
CREATE TABLE HR.CLIENTE (
	id NUMBER NOT NULL,
	nombres VARCHAR(200) NOT NULL,
	apellidos VARCHAR(200) NOT NULL,
	fecha_afiliacion DATE NOT NULL,
	PRIMARY KEY(id)
);

Ahora que hemos creado la tabla CLIENTE, procedemos a llenarla con sentencias INSERT desde PL/SQL:

DECLARE
  V_NOMBRES   VARCHAR2(200);
  V_APELLIDOS VARCHAR2(200);
  V_FECHA     DATE;
BEGIN
  --Podemos insertar registros a partir de variables
  V_NOMBRES   := 'Jean';
  V_APELLIDOS := 'Mazuelos';
  V_FECHA     := SYSDATE;
  INSERT INTO hr.cliente(id,nombres,apellidos,fecha_afiliacion)
       VALUES (1,V_NOMBRES,V_APELLIDOS, V_FECHA);
  DBMS_OUTPUT.PUT_LINE('Se ha insertado al cliente' || V_NOMBRES || ' ' || V_APELLIDOS);

  -- O también se pueden usar valores constantes
  INSERT INTO hr.cliente(id,nombres,apellidos,fecha_afiliacion)
       VALUES (2,'Pedro', 'GONZALES', TO_DATE('13/12/1986','DD/MM/YYYY'));
  DBMS_OUTPUT.PUT_LINE('Se ha insertado al cliente Pedro Gonzales');
END;

Se ha insertado al cliente Jean Mazuelos
Se ha insertado al cliente Pedro Gonzales

Para verificar los cambios podemos usar la sentencia:

SELECT *
  FROM hr.cliente

Al igual que el caso de las sentencias INSERT, podemos usar sentencias UPDATE.

DECLARE
   V_APELLIDOS VARCHAR2(200);
   V_ID        NUMBER;
BEGIN
  --Podemos usar variables tanto para asignar los nuevos valores
  --o usarlos en el filtro WHERE.
  V_APELLIDOS := 'Mazuelos Mendez';
  V_ID        := 1;
  UPDATE hr.cliente SET apellidos = V_APELLIDOS
   WHERE id = V_ID;
  DBMS_OUTPUT.PUT_LINE('Se ha modificado el cliente con id ' || V_ID);

  --O podemos usar valores constantes
  UPDATE hr.cliente SET apellidos = 'Gonzales Lurés'
   WHERE id = 2;
  DBMS_OUTPUT.PUT_LINE('Se ha modificado el cliente con id 2.');
END;

Se ha modificado el cliente con id 1
Se ha modificado el cliente con id 2.

SELECT *
  FROM hr.cliente

La sentencia DELETE funciona de igual manera

BEGIN
  DELETE FROM hr.cliente WHERE id=2;
END;

SELECT *
  FROM hr.cliente

CURSOR IMPLÍCITO - SELECT

En algunos casos necesitaremos realizar una sentencia SELECT. La única manera de manipular registros a través de una sentencia SELECT es usando cursores. En PL/SQL existen varios tipos de cursores, uno de los más sencillos de usar son los cursores implícitos a través de la sentencia FOR - LOOP.

BEGIN
  DBMS_OUTPUT.PUT_LINE('Mostrando los empleados con comisión mayor al 30%');
  -- Con la sentencia FOR .. LOOP se pueden crear cursores implícitos
  FOR V_REGISTRO IN (SELECT first_name, last_name, commission_pct
                       FROM hr.employees
                      WHERE commission_pct > 0.3)
  LOOP
    --El bloque LOOP ... END LOOP; ejecutará el mismo código
    -- por cada registro de la sentencia SELECT del LOOP
    DBMS_OUTPUT.PUT_LINE('======================================================');
    DBMS_OUTPUT.PUT_LINE('Nombres: ' || V_REGISTRO.first_name);
    DBMS_OUTPUT.PUT_LINE('Apellidos: ' || V_REGISTRO.last_name);
    DBMS_OUTPUT.PUT_LINE('Comisión: ' || (V_REGISTRO.commission_pct*100) || '%');
  END LOOP;
END;

Mostrando los empleados con comisión mayor al 30%
======================================================
Nombres: John
Apellidos: Russell
Comisión: 40%
======================================================
Nombres: Janette
Apellidos: King
Comisión: 35%
======================================================
Nombres: Patrick
Apellidos: Sully
Comisión: 35%
======================================================
Nombres: Allan
Apellidos: McEwen
Comisión: 35%

En el ejemplo anterior, la sentencia de control FOOR.. LOOP sirve para "recorrer" todos los registros obtenidos de una sentencia SQL (SELECT first_name, last_name, commission_pct FROM hr.employees WHERE commission_pct > 0.3). La variable V_REGISTRO es declara dentro de las cláusulas FOR .. IN, y ésta variable será utilizada para acceder a las columnas de la consulta.