PL/SQL
Parte 2
Procedural Language extension to Structured
Query Language (SQL)
Agenda
Cursores implícitos
Cursores explícitos
Excepciones
SQL dinámico
Bulk Collect
Cursores Implícitos
Se crean al realizar cualquier operación DML
Se antepone la palabra SQL
Generan automáticamente los siguientes atributos:
%FOUND: devuelve TRUE si INSERT, UPDATE, o DELETE
afecta a 1 o más filas o el SELECT INTO devuelve 1 o más filas.
%ISOPEN: Siempre falso. Funciona en cursores no implícitos.
%NOTFOUND: Funciona como opuesto a %FOUND.
%ROWCOUNT: Devuelve la cantidad de filas afectadas por la
sentencia DML.
BEGIN
DELETE FROM employees WHERE manager_id = mgr_no;
DBMS_OUTPUT.PUT_LINE(‘Se borraron: ' || TO_CHAR(SQL
%ROWCOUNT) || ‘ filas.’);
END;
Cursores Explícitos
Se declaran en la zona de declaración
Se controlan a través de los comandos OPEN,
FETCH y CLOSE.
CURSOR nombre IS select;
...
OPEN nombre;
FETCH nombre INTO variables;
CLOSE nombre;
Para identificar la finalización de un cursor se utilizar
la variable nombre_cursor%NOTFOUND;
Pueden recibir y utilizar parámetros
CURSOR nombre (parámetros) IS select;
...
OPEN nombre (valores parámetros);
Cursores Explícitos
Se pueden utilizar variables directamente en el
select
Se puede omitir las clausulas OPEN, FETCH y
CLOSE con la estructura FOR.
FOR registro IN (SELECT) LOOP
sentencias utilizando registro.
END LOOP;
También se puede utilizar FOR abriendo un
cursor existente:
FOR registro IN cursor LOOP
sentencias utilizando registro.
END LOOP;
Cursores Explícitos
Se pueden utilizar variables directamente en el
select
Se puede omitir las clausulas OPEN, FETCH y
CLOSE con la estructura FOR.
FOR registro IN (SELECT) LOOP
sentencias utilizando registro.
END LOOP;
También se puede utilizar FOR abriendo un
cursor existente:
FOR registro IN cursor LOOP
sentencias utilizando registro.
END LOOP;
Excepciones
Las excepciones constituyen una de las partes en que se divide un bloque.
Existen excepciones pre-definidas por Oracle.
• CURSOR_ALREADY_OPEN
• DUP_VAL_ON_INDEX
• INVALID_CURSOR
• NO_DATA_FOUND
• SYS_INVALID_ROWID
• TOO_MANY_ROWS
• VALUE_ERROR
• ZERO_DIVIDE
Se pueden declarar excepciones propias:
DECLARE;
mi_excepcion EXCEPTION;
BEGIN
...
RAISE mi_excepcion;
...
EXCEPTION
WHEN mi_excepcion THEN ...
Se pueden disparar excepciones pre-definidas con RAISE
RAISE_APPLICATION_ERROR
Se pueden disparar excepciones creadas por
nosotros.
Se utiliza la instrucción
RAISE_APPLICATION_ERROR.
Formato:
raise_application_error(error_number, message[, {TRUE |
FALSE}]);
Error_number debe ser un número que va entre -
20000 y -20999
El último parámetro indica si el error se debe agregar
a la pila de errores (true) o si reemplaza a todos los
errores previos (false – valor por defecto).
Reglas de Propagación
Se ejecuta la excepción A del bloque hijo y
luego continúa en la línea siguiente del bloque
Hijo.
Reglas de Propagación
Se ejecuta la excepción B y luego finaliza.
Reglas de Propagación
Se produce un error de aplicación ya que la
excepción C no esta manejada o se propaga el
error hacia el procedimiento llamador.
Reglas de Propagación
Se puede volver a disparar una excepción dentro de un bloque
de excepciones para que lo tome la excepción padre.
Se utiliza la sentencia RAISE sin parámetros.
DECLARE
...
BEGIN
BEGIN
...
RAISE mi_excepcion;
...
EXCEPTION
WHEN mi_excepcion THEN
Codigo_excepcion_hijo;
RAISE;
END;
EXCEPTION
WHEN mi_excepcion THEN
Codigo_excepcion_padre;
END;
Se ejecutarán los códigos Codigo_excepcion_hijo y luego
Codigo_excepcion_padre.
Reglas de Propagación
Los errores en las zonas de declaración se
propagan al bloque llamador o padre.
Los errores se visualizan en las variables
SQLCODE y SQLERRM.
Las variables deben ser asignadas a variables
locales para poder ser utilizadas.
SQLCODE tiene un número negativo que
indica el número de error.
SQLERRM tiene el mensaje del error asociado.
La función SQLERRM(nro_error) devuelve el
mensaje asociado al error.
SQL Dinámico
Un SQL dinámico es aquel que se compila en
tiempo de ejecución.
EXECUTE IMMEDIATE ‘codigo PL/SQL’;
Se utiliza para crear objetos, asignar permisos
o modificar parámetros de la sesión entre otras
cosas.
Se puede ejecutar cualquier código SQL o
PL/SQL.
Se pueden pasar parámetros de entrada o
salida.
USING valor1, valor2
INTO variable1, variable2
RETURNING INTO var1, var2
SQL Dinámico
DECLARE
str_sql VARCHAR2(255);
l_cnt VARCHAR2(20);
BEGIN
str_sql := 'SELECT count(*) FROM PAISES';
EXECUTE IMMEDIATE str_sql INTO l_cnt;
dbms_output.put_line(l_cnt);
END;
Las varibles host tambien se pueden utilizar en los cursores.
DECLARE
TYPE cur_typ IS REF CURSOR;
c_cursor CUR_TYP;
fila PAISES%ROWTYPE;
v_query VARCHAR2(255);
codigo_pais VARCHAR2(3) := 'ESP';
BEGIN
v_query := 'SELECT * FROM PAISES WHERE CO_PAIS = :cpais';
OPEN c_cursor FOR v_query USING codigo_pais;
LOOP
FETCH c_cursor INTO fila;
EXIT WHEN c_cursor%NOTFOUND;
dbms_output.put_line([Link]);
END LOOP;
CLOSE c_cursor;
END;
Bulk collect
PL/SQL nos permite leer varios registros en una tabla de PL con un
único acceso a través de la instrucción BULK COLLECT.
Esto nos permitirá reducir el número de accesos a disco, por lo que
optimizaremos el rendimiento de nuestras aplicaciones. Como
contrapartida el consumo de memoria será mayor.
DECLARE
TYPE t_descripcion IS TABLE OF [Link]%TYPE;
TYPE t_continente IS TABLE OF [Link]%TYPE;
v_descripcion t_descripcion;
v_continente t_continente;
BEGIN
SELECT DESCRIPCION,
CONTINENTE
BULK COLLECT INTO v_descripcion, v_continente
FROM PAISES;
FOR i IN v_descripcion.FIRST .. v_descripcion.LAST LOOP
dbms_output.put_line(v_descripcion(i) || ', ' ||
v_continente(i));
END LOOP;
END;
/
Bulk collect
Podemos utilizar BULK COLLECT con registros de PL.
DECLARE
TYPE PAIS IS RECORD (CO_PAIS NUMBER ,
DESCRIPCION VARCHAR2(50),
CONTINENTE VARCHAR2(20));
TYPE t_paises IS TABLE OF PAIS;
v_paises t_paises;
BEGIN
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
BULK COLLECT INTO v_paises
FROM PAISES;
FOR i IN v_paises.FIRST .. v_paises.LAST LOOP
dbms_output.put_line(v_paises(i).DESCRIPCION ||
', ' || v_paises(i).CONTINENTE);
END LOOP;
END;
/
DECLARE
TYPE t_paises IS TABLE OF PAISES%ROWTYPE;
v_paises t_paises;
BEGIN
SELECT CO_PAIS, DESCRIPCION, CONTINENTE
BULK COLLECT INTO v_paises
FROM PAISES;
FOR i IN v_paises.FIRST .. v_paises.LAST LOOP
dbms_output.put_line(v_paises(i).DESCRIPCION ||
', ' || v_paises(i).CONTINENTE);
END LOOP;
END;
/
FIN