Sentencias SQL en Bloques PL/SQL
Cuando se consulta información o se efectúan cambios a la Base de Datos se debe usar SQL. PL/SQL soporta el Lenguaje de Manipulación de datos
(DML) y los comandos del control de transacciones de SQL. En un bloque PL/SQL se pueden recuperar una o varias filas desde la Base de
Datos utilizando el comando SELECT. Para realizar cambios a una ó varias filas de la Base de Datos en un bloque PL/SQL se deben utilizar los
comandos DML INSERT, UPDATE, DELETE y/o MERGE. Para controlar una transacción en el bloque PL/SQL de debe utilizar el comando
COMMIT, ROLLBACK ó SAVEPOINT.
PL/SQL no admite directamente el uso del lenguaje de definición de datos (DDL), como CREATE TABLE, ALTER TABLE o DROP TABLE. Estas sentencias
pueden ejecutar a través de SQL Dinámico.
PL/SQL no admite directamente del lenguaje de control de datos (DCL), como las sentencias GRANT o REVOKE. Para ejecutar estas sentencias se puede
utilizar SQL dinámico.
SELECT lista_seleccionar
INTO {nombre_variable [, nombre_variable] ...
| nombre_registro}
FROM tabla
[WHERE condición];
Sentencia SELECT en PL/SQL
Se debe utilizar la sentencia SELECT para recuperar datos de la base de datos.
En la Sintaxis:
- lista_seleccionar : lista de a lo menos una columna y puede incluir expresiones SQL, funciones de columnas o funciones de grupo.
- nombre_variable : variable escalar que almacenará el valor recuperado.
- nombre_registro : es un registro PL/SQL que almacenará el valor recuperado.
- tabla : especifica el nombre de tabla de la Base de Datos.
- condición : está compuesta de nombres de columnas, expresiones, constantes y operadores de comparaciones.
Las consultas deben retornar sólo una fila cuando se utilizan variables escalares.
- La cláusula INTO es requerida ya que los datos que retorna la sentencia SELECT deben ser almacenados en variables.
- Se debe especificar el mismo número de variables en la cláusula INTO como columnas de Base de Datos en la cláusula SELECT. Además sus tipos de datos deben ser compatibles.
- La cláusula WHERE es opcional y se puede utilizar para especificar variables de entrada, constantes, literales y expresiones PL/SQL. Sin embargo, cuando se utiliza la cláusula INTO, se debe buscar una sola fila; en estos casos se requiere utilizar la cláusula WHERE.
- Cuando una sentencia SELECT en PL/SQL retorna más de una fila o no retorna filas se genera un error. Esos errores son excepciones estándares que pueden ser incorporadas en la sección de excepciones. Estas pueden ser NO_DATA_FOUND y TOO_MANY_ROWS.
- Una sentencia SELECT con la cláusula INTO puede recuperar sólo una fila a la vez. Si se requiere recuperar múltiples filas y operar sobre los datos, entonces se puede hacer uso de Cursores Explícitos.
DECLARE
v_fname VARCHAR2(25);
BEGIN
SELECT first_name
INTO v_fname
FROM employees
WHERE employee_id > 200;
DBMS_OUTPUT.PUT_LINE('El primer nombre del empleado 200 es : '|| v_fname);
END;
El bloque PL/SQL, obtiene el primer nombre de los empleados con identificación mayor a 200. Esto visualiza el error ORA-01422 ya que la sentencia retorna más de una fila y una sentencia SELECT con cláusula INTO debe retornar una fila. La opción es utilizar una comparación que asegure el retorno de una fila o utilizar un cursor explícito.
DECLARE
v_fname VARCHAR2(25);
BEGIN
SELECT first_name
INTO v_fname
FROM employees
WHERE employee_id=200;
DBMS_OUTPUT.PUT_LINE('El primer nombre del empleado 200 es : '|| v_fname);
END;
El bloque PL/SQL anterior ahora obtiene el primer nombre del empleado 200 y se almacena en la variable v_fname para posteriormente mostrarlo.
DECLARE
v_contrato employees.hire_date%TYPE;
v_salario employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_contrato, v_salario
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('La fecha de contrato del empleado 100 es: ' || v_contrato);
DBMS_OUTPUT.PUT_LINE('El salario del empleado 100 es: ' || v_salario);
END;
En el ejemplo, el bloque recupera la fecha de contrato y salario del empleado 100.
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary)
INTO v_sum_sal FROM employees
WHERE department_id > v_deptno;
DBMS_OUTPUT.PUT_LINE ('La suma de los salarios es ' || TO_CHAR(v_sum_sal,
'$999,999'));
END;
En el bloque PL/SQL, se obtiene la sumatoria de los salarios de todos los empleados que trabajan en el departamento 60.
DECLARE v_hire_date employees.hire_date%TYPE; v_fecha_actual v_hire_date%TYPE; employee_id employees.employee_id%TYPE := 176; BEGIN SELECT hire_date, sysdate INTO v_hire_date, v_fecha_actual FROM employees WHERE employee_id = employee_id; END;
En el bloque PL/SQL, se recupera la fecha de contrato del empleado 176 desde tabla employees además de la fecha actual del sistema. Sin embargo se produce un error porque en la cláusula WHERE, el nombre de la variable PL/SQL es el mismo nombre de la columna en la tabla employees por lo tanto Oracle asume que las dos ocurrencias de employee_id en la cláusula WHERE se refieren a la columna de la Base de Datos y retorna TODAS las filas de la tabla, en este caso 107 filas.
Convenciones de Nombres
- Utilizar las convenciones de nombres de variables o identificadores para evitar ambigüedades en la cláusula WHERE.
- Evitar usar nombre de columnas de Base Datos como identificadores.
- Los nombres de columnas de tablas de la Base de Datos tienen precedencia por sobre los nombres de variables locales.
- Los nombres de variables locales y parámetros formales tienen precedencia por sobre los nombres de tabla de la Base de Datos.
- La posibilidad de ambigüedad de nombres en la cláusula SELECT no es posible porque cualquier identificador en la cláusula SELECT debe ser una columna de Base de Datos.
- Tampoco es ambiguo en la cláusula INTO porque los identificadores en la cláusula INTO deben ser variables PL/SQL. Esta confusión sólo es posible en la cláusula WHERE.
Manipulación de Datos en PL/SQL
Se manipulan los datos en la base de datos mediante el uso de los comandos DML. Se pueden ejecutar comandos DML de INSERT, UPDATE, DELETE y MERGE sin restricciones en PL/SQL. Los bloqueos de fila (y bloqueos de tabla) se liberan mediante la inclusión de COMMIT O ROLLBACK en las sentencias del bloque PL/SQL.
- La sentencia INSERT agrega nuevas filas a la tabla.
- La sentencia UPDATE modifica filas existentes en la tabla.
- La sentencia DELETE elimina filas de la tabla.
- La sentencia MERGE selecciona las filas de una tabla a actualizar o insertar en otra tabla. La decisión de actualizar o insertar en la tabla de destino se basa en una condición en la cláusula ON.
-- Ejemplo 1
BEGIN
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date,
job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores','RCORES', sysdate,
'AD_ASST', 4000);
END;
-- Ejemplo 2
CREATE TABLE bono
(id_empleado NUMBER(6),
bono NUMBER(8,2));
BEGIN
INSERT INTO bono
(SELECT employee_id, ROUND(salary * 0.20)
FROM employees);
END;
Inserción de Datos en PL/SQL
En el primer ejemplo, el bloque PL/SQL agrega información de un nuevo empleado a la tabla employees.
En el segundo ejemplo, se crea la tabla BONO la que es utilizada en el bloque PL/SQL para insertar información obtenida desde la tabla EMPLOYEES. En este caso es la identificación del empleado y el 20% del salario valores que serán almacenados en las columnas id_empleado y bono de la tabla BONO respectivamente.
-- Ejemplo 1
DECLARE
v_sal_incrementado employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + v_sal_incrementado
WHERE job_id = 'ST_CLERK';
END;
-- Ejemplo 2
DECLARE
v_depto employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = v_depto;
END;
Modificación de Datos en PL/SQL
En el bloque PL/SQL del primer ejemplo, se incrementa el salario actual en 800 de todos los empleados cuyo trabajo es ST_CLERK.
En el bloque PL/SQL del segundo ejemplo, se eliminan, desde tabla employees, las filas de los empleados que pertenezcan al departamento 10.
CREATE TABLE copia_emp AS SELECT * FROM employees;
TRUNCATE TABLE copía_emp;
DECLARE
BEGIN
MERGE INTO copia_emp c
USING employees e
ON (e.employee_id = c.empno)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
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;
Combinación de Datos en PL/SQL
Las instrucción MERGE inserta o actualiza las filas de una tabla usando los datos de otra tabla. Cada fila se inserta o se actualiza en la tabla de destino en función de una condición de unión de igualdad (Join). El ejemplo, el bloque PL/SQL agrega o actualiza filas en la tabla copia_emp a partir de los datos que existen en tabla employees (la tabla copia_emp primero se crear con las misma estructura y datos de tabla employees y luego ser trunca para poder ser utilizada en el bloque PL/SQL). La coincidencia que se busca es que el valor de la columna employee_id existan en la tabla copia_emp al (en la columna empno). Si se encuentra una coincidencia, la fila se actualiza para que coincida con la fila de la tabla empleados. Si no se encuentra la fila de la tabla employees se inserta en la tabla copia_emp.
Control de Transacciones en PL/SQL
Una transacción es una serie de sentencias SQL de manipulación de datos que provee una unidad lógica de trabajo. Las sentencias de control de transacciones permiten asegurar la consistencia de la Base de Datos. Las sentencias de control de transacciones usadas son COMMIT, ROLLBACK y SAVEPOINT.
Las sentencias que se ejecutan después del último COMMIT o ROLLBACK comprenden la transacción (o grupo de transacciones activas o actual). De forma implícita se inicia una nueva transacción después de un COMMIT o ROLLBACK. La sentencia COMMIT finaliza la transacción actual y efectúa los cambios en la Base de Datos en forma permanente.
La sentencia ROLLBACK finaliza la transacción actual y deshace todos los cambios realizados en la Base de Datos por la transacción actual. Con la sentencia SAVEPOINT es posible nombrar y marcar un punto determinado donde se podrá retornar el control luego de ejecutarse una sentencia ROLLBACK.
BEGIN
UPDATE employees
SET salary = salary + 100
WHERE employee_id = 100;
DELETE FROM job_history
WHERE employee_id = 101;
SAVEPOINT inserta;
INSERT INTO departments
VALUES(departments_seq.NEXTVAL, 'DEPTO NUEVO', '110', 1700);
ROLLBACK TO inserta;
COMMIT;
END;
Control de Transacciones en PL/SQL
En el en el bloque del ejemplo, se “marcan” las instrucciones que insertan una nueva fila a tabla departments con el nombre inserta. Por lo tanto el rollback se efectúa sólo a la sentencia INSERT y COMMIT afectará a las sentencias UPDATE y DELETE del bloque PL/SQL.
Cursor SQL
Como se ha explicado anteriormente, las sentencias SQL que devuelven una sola fila se pueden incluir en un bloque PL/SQL. Los datos recuperados por la sentencia SQL se almacenan en las variables mediante la cláusula INTO. ¿Cómo las sentencias SQL son procesadas por el servidor Oracle? El servidor de Oracle asigna un área de memoria privada denominada área de contexto para el procesamiento de sentencias SQL. La sentencia SQL se analiza y se procesa en esta área. La información necesaria para el procesamiento y la información obtenida tras el procesamiento se almacenan en esta área. No setiene control sobre esta área, ya que se gestiona internamente por el servidor Oracle. Un cursor es un puntero a la zona de contexto. Sin embargo, este cursor es un cursor implícito y es gestionado automáticamente por el servidor Oracle. Cuando el bloque PL/SQL ejecuta una sentencia SQL, PL/SQL crea un cursor implícito. Existen dos tipos de cursores:
- Cursor Implícito: se crea y está gestionado por el servidor Oracle. No se tiene acceso a él. El servidor de Oracle crea un cursor como cuando tiene que ejecutar una sentencia SQL.
- Cursor Explícito: como programador, es posible que desee recuperar varias filas de una tabla de base de datos, En estos casos, se puede declarar cursores explícitamente en función de las necesidades de negocio. Un cursor declarado por los programadores se denomina cursor explícito. Se debe declara un cursor en la sección declarativa de un bloque PL/SQL.
Atributos de Cursor SQL para Cursores Implícitos
Los atributos de cursor de SQL permiten evaluar lo que sucedió cuando un cursor implícito se ha utilizado por última vez. Estos atributos se deben utilizar en las sentencias PL/SQL, pero no en las sentencias SQL. Se pueden utilizar los atributos SQL%ROWCOUNT, SQL%FOUND, and SQL%NOTFOUND en la sección ejecutable de un bloque después que se ha ejecutado las sentencia DML. PL/SQL no devuelve un error si una sentencia DML no afecta a las filas de la tabla. Sin embargo, si una sentencia SELECT no recupera ninguna fila, PL/SQL devuelve una excepción. Los atributos tienen el prefijo SQL. Estos atributos del cursor se utilizan con los cursores implícitos que son creados automáticamente por PL/SQL y para los cuales no se conoce los nombres. Por lo tanto, se utiliza SQL en lugar del nombre del cursor. El atributo SQL%NOTFOUND es contrario a SQL%FOUND. Este atributo puede ser utilizado como la condición de salida en un loop. Es útil en UPDATE y DELETE cuando no se modifica ninguna fila porque en estos casos no se retornan excepciones.
CREATE TABLE empleados
AS SELECT * FROM employees;
DECLARE
v_filas_elim VARCHAR2(30);
v_empno empleados.employee_id%TYPE := 176;
BEGIN
DELETE FROM empleados
WHERE employee_id = v_empno;
v_filas_elim := (SQL%ROWCOUNT || ' filas eliminadas.');
DBMS_OUTPUT.PUT_LINE (v_filas_elim);
END;
Atributos de Cursor SQL para Cursores Implícitos
En el ejemplo, se eliminan las filas de la tabla EMPLEADOS que tienen el ID de empleado 176. Posteriormente se visualiza en total de filas eliminas. Par esto se usa el atributo %ROWCOUNT que retorna el total de filas afectadas por la última sentencia SQL ejecutada.
BEGIN
UPDATE employees
SET salary = salary + (salary * NVL(commission_pct,0))
WHERE salary < 1000;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No se actualizaron filas');
ELSE
DBMS_OUTPUT.PUT_LINE ('Se actualizaron ' || SQL%ROWCOUNT || ' filas');
END IF;
END;
Atributos de Cursor SQL para Cursores Implícitos En el ejemplo, se aumenta el salario actual sumándole el valor de la comisión a los empleados con salario menor a 1000. Posteriormente se pregunta si la sentencia no afectó ninguna fila (con el atributo SQL%NOTFOUND) se mostrará el mensaje No se actualizaron filas , de lo contrario se visualizará el total de filas que fueron actualizadas (con el atributo SQL%ROWCOUNT).
No hay comentarios:
Publicar un comentario