• Determinar las sentencias SQL que se pueden incluir directamente en un bloque ejecutable de PL/SQL.
• Manipular los datos con instrucciones DML en PL/SQL.
• Utilizar sentencias de control de transacciones en PL/SQL.
• Hacer uso de la cláusula INTO para contener los valores devueltos por una instrucción SQL.
• Diferenciar entre cursores implícitos y explícitos cursores.
En un bloque PL/SQL, se utiliza sentencias SQL para recuperar y modificar los datos de tablas de Base de Datos. PL/SQL soporta el Lenguaje de Manipulación de Datos (DML) y los comandos de control de transacciones. Puede utilizar los comandos DML para modificar los datos de una tabla de Base de Datos. Sin embargo, tenga presente los siguientes puntos cuando use sentencias DML y comandos de control de transacciones en bloques de PL/SQL:
La palabra clave END indica el final de un bloque PL/SQL, no el final de una transacción. Del mismo modo que un bloque puede abarcar varias transacciones, una transacción puede abarcar varios bloques.
PL/SQL no soporta directamente el Lenguaje de Definición de Datos (DDL) como CREATE TABLE, ALTER TABLE, DROP TABLE, ETC... PL/SQL admite el enlace temprano o early binding, Lo cual no puede suceder si las aplicaciones tienen que crear objetos de Base de Datos en tiempo de ejecución mediante el paso valores. Las sentencias DDL no pueden ser ejecutadas directamente. Estas instrucciones son sentencias dinámicas de SQL. Las sentencias dinámicas de SQL se construyen como cadenas de caracteres en tiempo de ejecución y pueden contener marcadores de posición(placeholders) para parámetros. Por lo tanto, puede utilizar SQL dinámico para ejecutar sus sentencias DDL en PL/SQL.
PL/SQL no soporta directamente las sentencias de Lenguaje de Control de Datos (DCL), tales como GRANT o REVOKE. Puede utilizar SQL dinámico para ejecutarlos.
Utilice una instrucción SELECT para recuperar datos de la Base de Datos.
Sintaxis:
SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
[WHERE condition];
En la Sintaxis:
select_list
|
Lista de al menos una columna; puede incluir expresiones SQL, funciones de fila, o funciones de grupo.
|
variable_name
|
variable escalar que sostendrá el valor recuperado.
|
record_name
|
Record de PL/SQL que sostendrá los valores recuperados.
|
table
|
Es el nombre de la tabla.
|
condition
|
Se compone de nombres de columnas, expresiones y operadores de comparación, incluyendo las variables y constantes de PL/SQL.
|
Directrices para la Recuperación de datos en PL/SQL.
• Termine cada sentencia SQL con un punto y coma (;).
• Cada valor recuperado debe ser almacenado en una variable utilizando la cláusula INTO.
• La cláusula WHERE es opcional y se puede utilizar para especificar las variables de entrada, constantes, literales, y expresiones PL/SQL. Sin embargo, cuando se utiliza la cláusula INTO, deberías tomar sólo una fila; por lo cual la cláusula WHERE es requerida en estos casos.
• Especificar el mismo número de variables en la cláusula INTO que el número de columnas en la cláusula SELECT. Asegúrese de que correspondan posicionalmente y que sus tipos de datos sean compatibles.
• Utilice las funciones de grupo, tales como SUM, en una sentencia SQL, ya que las funciones de grupo aplican a grupos de filas de una tabla.
Detalles:
Cláusula INTO.
La cláusula INTO es obligatoria y se especifica entre el SELECT y cláusula FROM. Se utiliza para especificar los nombres de las variables que alojaran los valores retornados por las columnas en la cláusula SELECT. Debe especificar una variable para cada elemento seleccionado, y el orden de las variables deben corresponder con los elementos seleccionados.
Utilice la cláusula INTO para rellenar tanto variables PL/SQL como las variables de entorno(Bind Variables).
Las consultas deben retornar sólo una fila.
Las instrucciones SELECT dentro de un bloque PL/SQL caen dentro de la clasificación ANSI de SQL incorporado, para el que se aplica la siguiente regla: Las consultas deben devolver una sola fila. Una consulta que devuelve más de una fila o ninguna fila genera un error.
PL/SQL gestiona estos errores al elevar excepciones estándar, que se pueden controlar en la sección EXCEPTION del bloque PL/SQL con las excepciones: NO_DATA_FOUND y TOO_MANY_ROWS. Debe incluir una condición WHERE en la instrucción SQL para que la instrucción retorne una sola fila.
Ejemplos:
SET SERVEROUTPUT ON
DECLARE
v_nombre VARCHAR2(25);
BEGIN
SELECT first_name INTO v_nombre
FROM employees
WHERE employee_id = 200;
DBMS_OUTPUT.PUT_LINE('El nombre es: '||v_nombre);
END;
/*El ejemplo consulta el nombre del empleado numero 200 y almacena el valor en la variable v_nombre, posteriormente se imprime el valor.*/
---
SET SERVEROUTPUT ON
DECLARE
v_fecha_contrato employees.hire_date%TYPE;
v_salario employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_fecha_contrato, v_salario
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE ('Fecha de Contratación :'|| v_fecha_contrato);
DBMS_OUTPUT.PUT_LINE ('Salario :'|| v_salario);
END;
/*El ejemplo extrae la fecha de contratación y salario del empleado numero 100 y luego muestra sus respectivos valores mediante el uso de variables..*/
---
SET SERVEROUTPUT ON
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- Función de grupo
INTO v_sum_sal
FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE ('La suma total de los salarios del departamento '||v_deptno||' es: '|| v_sum_sal);
END;
/*El ejemplo muestra como usar una función grupal de SQL dentro de un bloque PL/SQL.*/
---
Una instrucción SELECT con la cláusula INTO puede recuperar sólo una fila a la vez. Si sue requiere recuperar varias filas y operar sobre los datos, puede hacer uso de cursores explícitos. Los Cursores Explícitos serán presentados en futuras publicaciones.
____________________________________________________________________________________
____________________________________________________________________________________
Ambigüedades en la Nomenclatura.
Ejemplo:
SET SERVEROUTPUT ON
DECLARE
last_name VARCHAR2(25) := 'OConnell';
BEGIN
DELETE FROM employees
WHERE last_name = last_name;
END;
Manipulando Datos en PL/SQL.
En las sentencias SQL potencialmente ambiguas, los nombres de las columnas de Base de Datos tienen prioridad sobre los nombres de las variables locales.
• Utilice una buena convención de nombres para evitar ambigüedades en la cláusula WHERE.
• Evite el uso de nombres de columna de Base de Datos como identificadores.
• Errores de sintaxis pueden surgir ya que PL/SQL primero busca nombres de columnas de Base de Datos.
• Los nombres de las variables locales y parámetros formales tienen prioridad sobre los nombres de las tablas de Base de Datos.
• Los nombres de las columnas en tablas de Bases de Datos tienen prioridad sobre los nombres de las variables locales.
• Los nombres de las variables tienen prioridad sobre los nombres de las funciones.
Evite ambigüedad en la cláusula WHERE apegándose a un convenio de nomenclatura que distinga los nombres de columnas de Base de Datos de nombres de variables de PL/SQL.
Nota: No hay posibilidad de ambigüedad en la cláusula SELECT, ya que cualquier identificador en la cláusula SELECT debe ser un nombre de columna de Base de Datos. No hay posibilidad de ambigüedad en la cláusula INTO porque los identificadores en la cláusula INTO deben ser variables de PL/SQL. La posibilidad de confusión está presente sólo en la cláusula WHERE.
SET SERVEROUTPUT ON
DECLARE
last_name VARCHAR2(25) := 'OConnell';
BEGIN
DELETE FROM employees
WHERE last_name = last_name;
END;
/*Este SCRIPT trata de eliminar los empleados de la tabla employees que tengan OConnell como apellido, sin embargo al ejecutar este SCRIPT, todos los registros serian eliminados debido a que existe Ambigüedad en la cláusula WHERE; en lugar de comparar cada registro con la cadena OConnell, se compararía cada registro con sigo mismo. Nota: Si ejecuta el SCRIPT fallaría debido a un CONSTRAINT de integridad en la tabla employees.*/
---
____________________________________________________________________________________Manipulando Datos en PL/SQL.
Para manipular los datos en la Base de Datos usamos comandos DML. Puede emitir comandos DML como INSERT, UPDATE, DELETE y MERGE sin restricciones en PL/SQL. Los bloqueos de fila (y los bloqueos de tabla) se liberan mediante la inclusión de las sentencias COMMIT y ROLLBACK en el código PL/SQL.
• La sentencia INSERT añade nuevas filas a la tabla.
• La instrucción UPDATE modifica filas existentes en la tabla.
• La instrucción DELETE elimina filas de la tabla.
• La instrucción MERGE selecciona filas de una tabla para actualizarla o insertarla en otra tabla. La decisión de actualizar o insertar en la tabla destino se basa en una condición en la cláusula ON.
Nota: MERGE es una sentencia determinista. Es decir, no se puede actualizar la misma fila de la tabla destino varias veces en la misma instrucción MERGE. Debe tener los privilegios INSERT y UPDATE en la tabla destino y el privilegio SELECT en la tabla origen.
Ejemplos:
BEGIN
INSERT INTO employees
(
employee_id,
first_name,
last_name,
email,
hire_date,
salary
)
VALUES
(
employees_seq.NEXTVAL,
'Ruth',
'Torres',
'RTORRES@',
CURRENT_DATE,
4000
);
END;
/*Este ejemplo nos muestra como ejecutar una sentencia INSERT en un bloque PL/SQL.*/
---
DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE employee_id =
(
SELECT MAX(employee_id)
FROM employees
);
END;
/*Ahora en este ejemplo usamos una sentencia UPDATE en un bloque PL/SQL para actualizar el salario de la empleada insertada en el ejemplo anterior a este.*/
---
DECLARE
v_nombre VARCHAR2(35) := 'Ruth Torres';
BEGIN
DELETE FROM employees
WHERE first_name||' '||last_name = v_nombre;
END;
/*Este ejemplo nos muestra como usar una sentencias DELETE en un bloque PL/SQL; Acá eliminamos el registro que anteriormente insertamos y luego actualizamos.*/
---
CREATE TABLE emp_copia
AS
SELECT *
FROM employees
WHERE department_id = 80;
/*Creamos una copia de la tabla employees solo con los empleados del departamento 80, a continuación usaremos esta tabla para mostrar un ejemplo con la sentencia MERGE.
Nota: la anterior sentencia no esta en un bloque PL/SQL, es una sentencia dinámica de SQL*/
---
BEGIN
MERGE INTO emp_copia c
USING employees e
ON (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
c.phone_number = e.phone_number,
c.hire_date = e.hire_date,
c.job_id = e.job_id,
c.salary = e.salary,
c.commission_pct = e.commission_pct,
c.manager_id = e.manager_id,
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES
(
e.employee_id,
e.first_name,
e.last_name,
e.email,
e.phone_number,
e.hire_date,
e.job_id,
e.salary,
e.commission_pct,
e.manager_id,
e.department_id
);
END;
/*Finalmente este es un ejemplo de una sentencia MERGE dentro de una bloque PL/SQL.*/
---
____________________________________________________________________________________
Cursores SQL.
Un cursor es un puntero a la zona privada de memoria asignada por el servidor Oracle. Se utiliza para manejar el conjunto de resultados de una instrucción SELECT.
Hay dos tipos de cursores:
• Implícito: Creado y gestionado internamente por el servidor Oracle para procesar instrucciones SQL.
• Explícito: Declarado explícitamente por el programador.
Dónde procesa el Servidor Oracle las sentencias SQL?
El Servidor Oracle asigna un área de memoria privada llamada el área de contexto para el procesamiento de instrucciones SQL. La instrucción SQL se analiza y procesa en esta área. Tanto la información requerida para el procesamiento como la información recuperada después de su transformación se almacenan en esta área. Usted no tiene control sobre esta zona, ya que es gestionada internamente por el servidor Oracle.
Atributos SQL para Cursores Implícitos.
Los atributos SQL para cursores permiten evaluar que ocurrió la ultima vez que un cursor implícito fue utilizado. Es posible utilizar estos atributos en sentencias de PL/SQL, no sentencias SQL.
Es común el uso de SQL%ROWCOUNT, SQL%FOUND, y SQL%NOTFOUND en la sección ejecutable de un bloque PL/SQL para recoger información después que un comando DML es ejecutado. PL/SQL no retorna error si una instrucción DML no afecta filas en una tabla. Sin embargo, si una instrucción SELECT no recupera ninguna fila, PL/SQL devuelve una excepción.
Observe que los atributos tienen el prefijo SQL. Estos atributos se utilizan con cursores implícitos creados automáticamente por el Servidor Oracle, por lo cual sus nombres son desconocidos. Es por ello, que se utiliza SQL en lugar del nombre del cursor.
El atributo SQL%NOTFOUND es opuesto a SQL%FOUND. Estos atributos pueden ser utilizados como la condición de salida en un bucle. Son útiles en UPDATE y DELETE cuando ninguna fila es afectada, ya que no se levantan excepciónes en estos casos.
Ejemplos:
SET SERVEROUTPUT ON
DECLARE
v_cantidad NUMBER(3);
v_dept employees.department_id%TYPE := 80;
BEGIN
UPDATE emp_copia
SET salary = 6954
WHERE department_id = v_dept;
v_cantidad := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('Registros Actualizados: '||v_cantidad);
COMMIT;
END;
/*El ejemplo muestra como determinar la cantidad de registros actualizados luego de ejecutar la sentencia UPDATE, para ello usamos el atributo SQL%ROWCOUNT.*/
---OUTPUT:
SET SERVEROUTPUT ON
DECLARE
v_cantidad NUMBER(3);
BEGIN
DELETE FROM emp_copia;
v_cantidad := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('Registros Eliminados: '||v_cantidad);
COMMIT;
END;
/*El ejemplo muestra como determinar la cantidad de registros eliminados luego de ejecutar la sentencia DELETE.*/
---OUTPUT:
• La sentencia INSERT añade nuevas filas a la tabla.
• La instrucción UPDATE modifica filas existentes en la tabla.
• La instrucción DELETE elimina filas de la tabla.
• La instrucción MERGE selecciona filas de una tabla para actualizarla o insertarla en otra tabla. La decisión de actualizar o insertar en la tabla destino se basa en una condición en la cláusula ON.
Nota: MERGE es una sentencia determinista. Es decir, no se puede actualizar la misma fila de la tabla destino varias veces en la misma instrucción MERGE. Debe tener los privilegios INSERT y UPDATE en la tabla destino y el privilegio SELECT en la tabla origen.
Ejemplos:
BEGIN
INSERT INTO employees
(
employee_id,
first_name,
last_name,
email,
hire_date,
salary
)
VALUES
(
employees_seq.NEXTVAL,
'Ruth',
'Torres',
'RTORRES@',
CURRENT_DATE,
4000
);
END;
/*Este ejemplo nos muestra como ejecutar una sentencia INSERT en un bloque PL/SQL.*/
---
DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE employee_id =
(
SELECT MAX(employee_id)
FROM employees
);
END;
/*Ahora en este ejemplo usamos una sentencia UPDATE en un bloque PL/SQL para actualizar el salario de la empleada insertada en el ejemplo anterior a este.*/
---
DECLARE
v_nombre VARCHAR2(35) := 'Ruth Torres';
BEGIN
DELETE FROM employees
WHERE first_name||' '||last_name = v_nombre;
END;
/*Este ejemplo nos muestra como usar una sentencias DELETE en un bloque PL/SQL; Acá eliminamos el registro que anteriormente insertamos y luego actualizamos.*/
---
CREATE TABLE emp_copia
AS
SELECT *
FROM employees
WHERE department_id = 80;
/*Creamos una copia de la tabla employees solo con los empleados del departamento 80, a continuación usaremos esta tabla para mostrar un ejemplo con la sentencia MERGE.
Nota: la anterior sentencia no esta en un bloque PL/SQL, es una sentencia dinámica de SQL*/
---
BEGIN
MERGE INTO emp_copia c
USING employees e
ON (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.email = e.email,
c.phone_number = e.phone_number,
c.hire_date = e.hire_date,
c.job_id = e.job_id,
c.salary = e.salary,
c.commission_pct = e.commission_pct,
c.manager_id = e.manager_id,
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES
(
e.employee_id,
e.first_name,
e.last_name,
e.email,
e.phone_number,
e.hire_date,
e.job_id,
e.salary,
e.commission_pct,
e.manager_id,
e.department_id
);
END;
/*Finalmente este es un ejemplo de una sentencia MERGE dentro de una bloque PL/SQL.*/
---
____________________________________________________________________________________
Cursores SQL.
Un cursor es un puntero a la zona privada de memoria asignada por el servidor Oracle. Se utiliza para manejar el conjunto de resultados de una instrucción SELECT.
Hay dos tipos de cursores:
• Implícito: Creado y gestionado internamente por el servidor Oracle para procesar instrucciones SQL.
• Explícito: Declarado explícitamente por el programador.
El Servidor Oracle asigna un área de memoria privada llamada el área de contexto para el procesamiento de instrucciones SQL. La instrucción SQL se analiza y procesa en esta área. Tanto la información requerida para el procesamiento como la información recuperada después de su transformación se almacenan en esta área. Usted no tiene control sobre esta zona, ya que es gestionada internamente por el servidor Oracle.
Atributos SQL para Cursores Implícitos.
Los atributos SQL para cursores permiten evaluar que ocurrió la ultima vez que un cursor implícito fue utilizado. Es posible utilizar estos atributos en sentencias de PL/SQL, no sentencias SQL.
Es común el uso de SQL%ROWCOUNT, SQL%FOUND, y SQL%NOTFOUND en la sección ejecutable de un bloque PL/SQL para recoger información después que un comando DML es ejecutado. PL/SQL no retorna error si una instrucción DML no afecta filas en una tabla. Sin embargo, si una instrucción SELECT no recupera ninguna fila, PL/SQL devuelve una excepción.
Observe que los atributos tienen el prefijo SQL. Estos atributos se utilizan con cursores implícitos creados automáticamente por el Servidor Oracle, por lo cual sus nombres son desconocidos. Es por ello, que se utiliza SQL en lugar del nombre del cursor.
El atributo SQL%NOTFOUND es opuesto a SQL%FOUND. Estos atributos pueden ser utilizados como la condición de salida en un bucle. Son útiles en UPDATE y DELETE cuando ninguna fila es afectada, ya que no se levantan excepciónes en estos casos.
SQL%FOUND
|
Atributo Booleano que evalúa como TRUE si la última sentencia SQL afectó al menos una fila.
|
SQL%NOTFOUND
|
Atributo Booleano que evalúa como TRUE si la última sentencia SQL no afectó incluso una fila.
|
SQL%ROWCOUNT
|
Un valor entero que representa el número de filas afectadas por la última sentencia SQL.
|
SET SERVEROUTPUT ON
DECLARE
v_cantidad NUMBER(3);
v_dept employees.department_id%TYPE := 80;
BEGIN
UPDATE emp_copia
SET salary = 6954
WHERE department_id = v_dept;
v_cantidad := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('Registros Actualizados: '||v_cantidad);
COMMIT;
END;
/*El ejemplo muestra como determinar la cantidad de registros actualizados luego de ejecutar la sentencia UPDATE, para ello usamos el atributo SQL%ROWCOUNT.*/
---OUTPUT:
DECLARE
v_cantidad NUMBER(3);
BEGIN
DELETE FROM emp_copia;
v_cantidad := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE ('Registros Eliminados: '||v_cantidad);
COMMIT;
END;
/*El ejemplo muestra como determinar la cantidad de registros eliminados luego de ejecutar la sentencia DELETE.*/
---OUTPUT:
_____________________________________________________________________________________
