Sentencias SQL en PL/SQL
Hemos visto como usar la programación de bloques anónimos o de subprogramas para
que se ejecuten todas las instrucciones que contienen. Pero aunque esto es ventajoso
el objetivo no es solo tener unas nociones de programación, lo que nos interesa es saber
cómo integrar en los programas de PL/SQL sentencias SQL para usar la potencia
combinada de ambas facetas y poder trabajar eficazmente con bases de datos Oracle.
Aunque en las entregas anteriores ya han salido ejemplos de sentencias SQL integradas
en los bloques de código que hemos usado vamos a verlas con más detenimiento.
Recuperar datos de la BD con SELECT
Esta operación se va a usar para asignar a nuestras variables en el código valores que
son resultado de una consulta realizada con una sentencia SELECT.
La sintaxis es:
SELECT lista_columnas
INTO {variable_nombre[, ...]| nombre_registro}
FROM nombre_tabla WHERE condicion;
Hay que detallar que el resultado de la consulta debe ser una sola fila, ya que si
devuelve más se produciría un error. También es importante indicar que la consulta
debe coincidir en número y tipo de la lista de columnas que devuelve la consulta con el
número de variables o los campos de la variable registro.
En el siguiente ejemplo se puede ver cómo recuperar la suma de los pagos que ha
realizado el cliente 1 y luego se imprime:
DECLARE
Pagado NUMBER;
BEGIN
SELECT SUM(CANTIDAD) INTO Pagado
FROM PAGOS WHERE CODIGOCLIENTE=1;
DBMS_OUTPUT.PUT_LINE(‘El cliente 1 ha pagado: ‘||Pagado);
END;
/
En este otro ejemplo se recupera un registro entero de la tabla Clientes sobre un
registro declarado igual que los registros de la tabla Clientes usando %ROWTYPE e
imprime algunos campos.
DECLARE
RegCli CLIENTES%ROWTYPE;
BEGIN
SELECT * INTO RegCli
FROM CLIENTES WHERE CODIGOCLIENTE=1;
DBMS_OUTPUT.PUT_LINE(‘Nombre: ‘||[Link]);
DBMS_OUTPUT.PUT_LINE(‘Telefono: ‘||[Link]);
DBMS_OUTPUT.PUT_LINE(‘Ciudad: ‘||[Link]);
END;
/
Inserción de datos en PL/SQL
Otra de las operaciones que podemos hacer es añadir registros a una tabla. Para ello
podemos hacer uso de la orden INSERT de SQL que ya conocemos.
La sintaxis es:
INSERT INTO nombre_tabla
[(campo1[.campo2,…])]
VALUES
(valor1, valor2, …);
Un ejemplo de uso en el que se añade el pago de un cliente a una tabla Pagos:
DECLARE
CANTIDADPAGO NUMBER:=30000;
BEGIN
INSERT INTO PAGOS VALUES
(1, ‘PayPal’, ‘el-0024501’, ’12-JUN-2020’, CANTIDADPAGO);
END;
/
Actualización de datos en PL/SQL
Las actualizaciones de las tablas también se pueden hacer podemos modificando los
valores de los campos que contienen.
La sintaxis es:
UPDATE nombre_tabla
SET campo1 = valor1 {[,campo2 = valor2, ..., campoN = valorN]}
[WHERE condicion];
Un ejemplo, en este procedimiento que aumenta el tanto por ciento indicado el precio
de venta de los productos de una gama que también se indica por parámetro:
CREATE OR REPLACE
PROCEDURE Sube_Producto(AUMENTO IN INT, TIPO IN [Link]%TYPE)
IS
BEGIN
UPDATE PRODUCTOS
SET PRECIOVENTA = PRECIOVENTA*AUMENTO/100
WHERE GAMA = TIPO;
END Actualiza_Saldo;
Cursores
Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente
los cursores son áreas de memoria que almacenan datos extraídos de la base de datos
mediante una consulta SELECT, o por manipulación de datos con sentencias de
actualización o inserción de datos. Podemos imaginar los cursores como una lista de
resultados y un apuntador (cursor) a alguno de los resultados de esa lista, así nos
resultará más fácil entender su funcionamiento.
Podemos distinguir dos tipos de cursores:
Cursores implícitos. No necesitan ser declarados por el programador, están en todas las
sentencias SELECT de PL/SQL que devuelven una sola fila. En caso de que devuelva más
de una fila se produciría un error que habría que tratar en el bloque de excepciones .
Cursores explícitos. Son los cursores que son declarados y controlados por el
programador. Se utilizan cuando la consulta devuelve un conjunto de registros.
Ocasionalmente también se utilizan en consultas que devuelven un único registro por
razones de eficiencia. Son más rápidos.
Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de
acuerdo a los mismos convenios que cualquier otra variable. Los cursores implícitos no
necesitan declaración.
Para declarar un cursor explícito usamos la siguiente sintaxis:
CURSOR nombre_cursor IS sentencia SELECT; /*Sin INTO*/
El siguiente ejemplo declara un cursor explícito:
DECLARE
varNombre [Link]%TYPE;
varCiudad [Link]%TYPE;
CURSOR cursorCliente IS SELECT nombre, ciudad
FROM Clientes WHERE Pais=’España’;
BEGIN
…
END;
Es importante señalar que con la declaración del cursor solo se reserva el espacio para
recuperar la consulta, pero para trabajar con el cursor en el bloque de código va a ser
necesario realizar varias operaciones: apertura del cursor, recuperación de datos y
cierre del cursor.
Para abrir el cursor es necesario escribir:
OPEN nombreCursor;
Es justo en el momento de su apertura cuando se ejecuta la consulta SQL indicada en la
declaración del cursor. El cursor quedaría situado justo en la primera fila devuelta por la
consulta.
Ojo, en caso de que la consulta no devuelva ninguna fila no se produciría un error, esto
se debe controlar mediante programación. Preguntando por el valor de los atributos de
estado del cursor se puede conocer entre otras cosas: cuantas filas devuelve la consulta,
si el cursor está abierto, si la última lectura tuvo éxito, …
Para recuperar los datos, es decir, para hacer una lectura de una fila hacemos uso de la
sentencia FETCH. Se debe hacer sobre tantas variables del mismo tipo que campos tenga
la fila que se recupera, o bien sobre una variable de tipo registro con los mismos campos.
Lógicamente también tienen que coincidir el orden, los tipos de datos de las variables y
de los campos de la consulta.
Sintaxis:
FETCH nombreCursor INTO {[var1, var2, …] | nombre_registro};
Cada vez que se realiza un FETCH el cursor avanza a la siguiente fila recuperada por lo
que es necesario comprobar, mediante los atributos del cursor, si sigue habiendo filas
para leer o se ha llegado al final. Debemos recorrer el cursor hasta llegar a la información
que nos interese o no haya más filas, por lo que será necesario emplear un bucle para
repetir estas acciones hasta llegar al resultado esperado.
Después de trabajar con el cursor deberemos cerrarlo con la orden:
CLOSE nombreCursor;
Con esta orden se desactiva el cursor liberando la memoria que ocupaban los datos
recuperados por éste al abrirlo. Una vez cerrado no es posible recuperar datos hasta que
no se abra de nuevo.
Se deben abrir y cerrar los cursores según se necesiten, ya que hay un número límite de
cursores que pueden estar abiertos a la vez en la base de datos.
Para conocer el estado de un cursor se pueden comprobar sus atributos, se hace
escribiendo: nombreCursor%atributoDeseado.
Los atributos podemos verlos en la siguiente tabla:
Atributo Funcionamiento
%ISOPEN Devuelve TRUE si el cursor está abierto y FALSE en caso contrario.
%NOTFOUND Devuelve TRUE si tras la recuperación más reciente no se obtuvo
ninguna fila.
%FOUND Devuelve TRUE si tras la recuperación más reciente se obtuvo una
fila.
%ROWCOUNT Número de filas devueltas hasta ese momento.
La mejor manera de ver el funcionamiento de los cursores es con un ejemplo
comentado:
DECLARE
CURSOR cursorEmpleado IS SELECT Nombre, Cargo, Oficina
FROM Empleados;
-- Usamos %ROWTYPE para tener el mismo tipo que la fila que devuelve el cursor
registroEmpleado cursorEmpleado%ROWTYPE;
BEGIN
OPEN cursorEmpleado; /* Abrimos el cursor */
FETCH cursorEmpleado INTO registroEmpleado; /* Leemos la primera fila */
-- Iniciamos el proceso de la consulta
WHILE cursorEmpleado%FOUND LOOP /* Mientras haya filas */
DBMS_OUTPUT.PUT_LINE([Link]||’ ‘
|| [Link] ||’ ‘
|| [Link]); /* Procesamos la información */
FETCH cursorEmpleado INTO registroEmpleado; /* Leemos la siguiente fila */
END LOOP;
CLOSE cursorEmpleado; /* Cerramos el cursor */
END;
/
Hay que observar que después de abrir el cursor se hace el primer FETCH, como la
condición del bucle es que la operación de recuperación sea cierta
(cursorEmpleado%FOUND), en caso de que la consulta estuviera vacía no procesa nada,
en cualquier otro caso va a procesar registros hasta que haya una operación FETCH que
no devuelva nada, es decir, cuando se llegue al final del cursor.
Se puede simplificar el código para recorrer un cursor, usando el FOR para cursores, cuya
sintaxis es:
FOR variableRegistro IN nombreCursor LOOP
instrucción1;
instrucción2;
…..
END LOOP;
Esta sintaxis ofrece muchas ventajas:
· No es necesario abrir ni cerrar el cursor.
· La variableRegistro se declara implícitamente, es decir, no es necesario declararla
previamente. Solo está disponible dentro del bucle FOR.
· En cada iteración del bucle se hace automáticamente una operación FETCH sobre
variableRegistro.
· Por último, el bucle finaliza automáticamente cuando recorre todas las filas del cursor.
El mismo ejemplo anterior usando esta sintaxis quedaría así:
DECLARE
CURSOR cursorEmpleado IS SELECT Nombre, Cargo, Oficina
FROM Empleados;
BEGIN
-- Iniciamos el proceso de la consulta
FOR registroEmpleado IN cursorEmpleado LOOP
-- Dentro del bucle se procesa la información
DBMS_OUTPUT.PUT_LINE([Link]||’ ‘
|| [Link] ||’ ‘
|| [Link]);
-- En cada iteración se hace una lectura automática sin usar FETCH
END LOOP; /* Cuando no hay más filas acaba el bucle */
END;
/