PL SQL
INTRODUCCIÓN
(continuación)
Lic Cristian Mercado Quispe
Base de Datos II
Lic. Cristian Mercado Quispe - UPEA
Manejos de Estructuras de control
CASE
CASE SELECTOR
WHEN EXPRESION1 THEN OPERACIÓN1;
WHEN EXPRESION2 THEN OPERACIÓN2;
……
ELSE OPERACIÓNN
END CASE;
Lic. Cristian Mercado Quispe - UPEA
Manejos de Estructuras de control
CASE - CONDICIONES
CASE
WHEN CONDICIÓN1 THEN OPERACIÓN1;
WHEN CONDICIÓN2 THEN OPERACIÓN2;
……
ELSE OPERACIÓNN
END CASE;
Lic. Cristian Mercado Quispe - UPEA
EJEMPLO - CASE
DECLARE
nota INT := &var_sus;
BEGIN
CASE
WHEN nota<=50 THEN
dbms_output.put_line('DEFICIENTE');
WHEN nota<=60 THEN
dbms_output.put_line('SUFICIENTE');
WHEN nota<=80 THEN dbms_output.put_line('BUENO');
ELSE dbms_output.put_line('EXCELENTE');
END CASE;
END;
Lic. Cristian Mercado Quispe - UPEA
Manejos de Estructuras de control
LOOP – Permite generar bucles, se repite tantas veces
sea necesario hasta forzar con la instrucción EXIT
LOOP
INSTRUCCIONES
IF(EXPRESIÓN) THEN
INSTRUCCIONES
EXIT;
END IF;
END LOOP;
Lic. Cristian Mercado Quispe - UPEA
EJEMPLO LOOP - Mostrar los números del 1 a n,
ingresando n por teclado.
DECLARE
n INT := &var_sus;
cont int:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(cont);
cont:= cont +1;
IF cont>n THEN
EXIT;
END IF;
END LOOP;
END;
Lic. Cristian Mercado Quispe - UPEA
DECLARE
n INT := &var_sus;
cont int:=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(cont);
cont:= cont +1;
EXIT WHEN cont>n;
END LOOP;
END;
Lic. Cristian Mercado Quispe - UPEA
Manejos de Estructuras de control
WHILE se repite mientras se cumpla la
expresión
WHILE (EXPRESION) LOOP
INSTRUCCIONES…
END LOOP;
Lic. Cristian Mercado Quispe - UPEA
DECLARE
n INT := &var_sus;
cont int:=1;
BEGIN
WHILE (cont<=n)LOOP
DBMS_OUTPUT.PUT_LINE(cont);
cont:=cont+1;
END LOOP;
END;
Lic. Cristian Mercado Quispe - UPEA
Manejos de Estructuras de control
FOR se repite tantas veces como se defina en
los indicadores inicio y final
FOR CONTADOR IN [REVERSE ]INICIO..FINAL LOOP
INSTRUCCIONES…
END LOOP;
Lic. Cristian Mercado Quispe - UPEA
DECLARE
n INT := &var_sus;
cont int;
BEGIN
FOR CONT IN 1..N LOOP
DBMS_OUTPUT.PUT_LINE(CONT);
END LOOP;
END;
Lic. Cristian Mercado Quispe - UPEA
CURSORES
Los cursores son punteros a unas zonas de
memoria llamadas áreas de contexto en las
cuales se almacena información acerca del
resultado de una consulta, así como el conjunto
de registros procesados y no procesados.
Mediante este puntero (cursor), un programa
PL SQL puede controlar el área de contexto,
manejar y procesar el conjunto de registros
devueltos por una consulta.
Lic. Cristian Mercado Quispe - UPEA
Tipos de cursores
Cursores Implícitos
Este tipo de cursores se utiliza para operaciones
SELECT INTO. Se usan cuando la consulta
devuelve un único registro.
Cursores Explícitos
Son los cursores que son declarados y controlados
por el programador. Se utilizan cuando la consulta
devuelve un conjunto de registros.
Lic. Cristian Mercado Quispe - UPEA
Cursor Implícito
DECLARE
NOMBRE employees.first_name%type;
APELLIDO employees.last_name%type;
ID_EMP employees.employee_id%type := &id_empleado ;
BEGIN
SELECT first_name, last_name INTO NOMBRE, APELLIDO
FROM employees
WHERE employee_id = ID_EMP;
DBMS_OUTPUT.PUT_LINE('NOMBRE: ' || NOMBRE ||
'APELLIDO: ' || APELLIDO);
END;
Lic. Cristian Mercado Quispe - UPEA
Cursores Explícitos
Pasos:
[Link] el cursor
cursor c1 is Select * from employees;
[Link] el cursor
open c1;
3. Recuperar datos del cursor
fetch c1 into var1, var2,…., varn;
4. Cerrar el cursor
Close c1;
Lic. Cristian Mercado Quispe - UPEA
Lic. Cristian Mercado Quispe - UPEA
Propiedades de los cursores
%NOTFOUND
Una propiedad del cursor que devuelve FALSE
durante la recuperación de registros y TRUE al
finalizar la recuperación.
%ROWCOUNT
Número de registros recuperados dependiendo
del momento en que se encuentre.(Recuperando
registros o al finalizar la recuperación).
Lic. Cristian Mercado Quispe - UPEA
Propiedades de los cursores
%FOUND
Una propiedad del cursor que devuelve TRUE durante
la recuperación de registros y FALSE al finalizar la
recuperación.
%ISOPEN
Propiedad del cursor que devuelve FALSE antes de
abrir el cursor, devuelve TRUE al abrir y recuperar
datos del cursor y devuelve FALSE después de cerrar
el cursor.
Lic. Cristian Mercado Quispe - UPEA
Ejemplo de cursores
Utilizar un cursor para mostrar el nombre y
apellido de todos los empleados
Lic. Cristian Mercado Quispe - UPEA
Solución
DECLARE
CURSOR C1 is select first_name, last_name from employees;
nombre employees.first_name%type;
apellido employees.last_name%type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO nombre,apellido;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (NOMBRE||' '|| APELLIDO);
END LOOP;
END;
Lic. Cristian Mercado Quispe - UPEA
Ejemplo de Cursores
Modificar el anterior ejercicio para mostrar el
nombre y apellido de todos los empleados que
trabajan en el departamento 80.
Lic. Cristian Mercado Quispe - UPEA
DECLARE
CURSOR C1 is select first_name, last_name from employees WHERE
DEPARTMENT_id=80;
nombre employees.first_name%type;
apellido employees.last_name%type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO nombre,apellido;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (NOMBRE||' '|| APELLIDO);
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMERO DE REGISTROS'||
C1%ROWCOUNT);
CLOSE C1;
END;
/
Lic. Cristian Mercado Quispe - UPEA
Ejemplo
Crear un bloque anónimo que muestre los tres
departamentos que tienen más empleados:
Ejemplo:
NOMBRE: Shipping CANTIDAD: 45
NOMBRE: Sales CANTIDAD: 34
NOMBRE: Finance CANTIDAD: 6
Lic. Cristian Mercado Quispe - UPEA
Ejercicios
EJERCICIO 1
Crear un bloque anónimo que permita mostrar el
nombre del empleado y el nombre del
departamento donde trabaja
Lic. Cristian Mercado Quispe - UPEA
Ejercicios
EJERCICIO 2
Crear un bloque anónimo para obtener el listado
de todos los superiores de un empleado, ingresar
el id del empleado por teclado:
Ejemplo para el id 111, se debe mostrar:
Ismael
Nancy
Neena
Steven
Lic. Cristian Mercado Quispe - UPEA
EJERCICIO 3
Invertir los nombres de todos los empleados
Ejemplo:
Ellen nellE
Sundar radnuS
Mozhe ehzoM
David divaD
Lic. Cristian Mercado Quispe - UPEA
EJERCICIO 4
Crear un bloque anónimo que valide una
contraseña ingresada por teclado, la contraseña
deberá tener como mínimo 5 caracteres y como
máximo 10 caracteres. Además mínimamente
deberá tener una vocal minúscula y una vocal
Mayúscula.
Lic. Cristian Mercado Quispe - UPEA
EJERCICIO 5
Crear la siguiente tabla:
Producto(id_producto, nombre)
En la tabla creada registrar 10000 productos
Id_producto nombre
1 Producto1
2 Producto2
3 Producto3
4 Producto4
… ….
10000 producto10000
Lic. Cristian Mercado Quispe - UPEA
Ejercicios
Crear un bloque anónimo que permita mostrar
a todos los empleados que trabajan en el
departamento de ‘MARKETING’
Crear un bloque anónimo que muestre los
primeros N números de la serie de Fibonacci.
Ingresar N por teclado. Ejemplo si N=4; se
debe mostrar 0,1,1,2
Lic. Cristian Mercado Quispe - UPEA
PL SQL
EXCEPCIONES
Lic Cristian Mercado Quispe
Base de Datos II
Lic. Cristian Mercado Quispe - UPEA
Introducción
En Pl/SQL una excepción es una advertencia o condición
de error que puede ser capturada para asignar un nuevo
comportamiento. Las excepciones tienen su propio bloque
DECLARE
--declaraciones
BEGIN
--ejecución
EXCEPTION
--excepción
END;
Lic. Cristian Mercado Quispe - UPEA
Introducción
Excepciones asociadas a cursores implícitos:
Excepción Explicación
NO_DATA_FOUND Se produce cuando una sentencia SELECT no
devuelve filas
TOO_MANY_ROWS Dado que cada cursor implícito sólo puede
recuperar una fila, esta excepción detecta la
existencia de más de una fila
Lic. Cristian Mercado Quispe - UPEA
Lic. Cristian Mercado Quispe - UPEA
EJEMPLO
DECLARE
NOMBRE EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME INTO NOMBRE FROM
EMPLOYEES;
DBMS_OUTPUT.PUT_LINE(NOMBRE);
END;
Lic. Cristian Mercado Quispe - UPEA
DECLARE
NOMBRE EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME INTO NOMBRE FROM
EMPLOYEES;
DBMS_OUTPUT.PUT_LINE(NOMBRE);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('LA SENTENCIA SQL
DEVUELVE DEMASIADOS REGISTROS');
END;
Lic. Cristian Mercado Quispe - UPEA
El siguiente bloque genera un error para el
empleado 500
DECLARE
NOMBRE EMPLOYEES.FIRST_NAME%TYPE;
IDEMP INT:= &VAR_SUS;
BEGIN
SELECT FIRST_NAME INTO NOMBRE FROM EMPLOYEES
WHERE EMPLOYEE_ID=IDEMP;
DBMS_OUTPUT.PUT_LINE(NOMBRE);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('LA SENTENCIA SQL
DEVUELVE DEMASIADOS REGISTROS'||SQlcode);
END;
Lic. Cristian Mercado Quispe - UPEA
Solución
DECLARE
NOMBRE EMPLOYEES.FIRST_NAME%TYPE;
IDEMP INT:= &VAR_SUS;
BEGIN
SELECT FIRST_NAME INTO NOMBRE FROM EMPLOYEES
WHERE EMPLOYEE_ID=IDEMP;
DBMS_OUTPUT.PUT_LINE(NOMBRE);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('LA SENTENCIA SQL DEVUELVE
DEMASIADOS REGISTROS');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMPLEADO'||IDEMP);
END;
Lic. Cristian Mercado Quispe - UPEA
WHEN OTHERS
Si no existe un bloque de control adecuado al
tipo de excepción, se ejecutara WHEN
OTHERS, el mismo que debe ser el último
manejador de excepciones.
Lic. Cristian Mercado Quispe - UPEA
DECLARE
NOMBRE EMPLOYEES.FIRST_NAME%TYPE;
IDEMP INT:= &VAR_SUS;
BEGIN
SELECT FIRST_NAME INTO NOMBRE FROM
EMPLOYEES
WHERE EMPLOYEE_ID=IDEMP;
DBMS_OUTPUT.PUT_LINE(NOMBRE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error en la ejecución');
END;
Lic. Cristian Mercado Quispe - UPEA
Excepciones predefinidas
Excepción Se Ejecuta cuando.. SQLCODE
ACCESS_INTO_NULL El programa intento asignar valores a los -6530
atributos de un objeto no inicializado
CURSOR_ALREADY_OP El programa intento abrir un cursor que se -6531
EN encontraba abierto
DUP_VAL_ON_INDEX El programa intentó almacenar valores -1
duplicados en una columna Primary Key
INVALID_CURSOR El programa intento realizar una operación -1001
no valida en un cursor
ROWTYPE_MISMATCH Los elementos de asignación tienen tipos de -6540
datos incompatiles
ZERO_DIVIDE El programa intentó efectuar una división -1476
por cero
Lic. Cristian Mercado Quispe - UPEA
Uso de SQLCODE y SQLERRM
SQLCODE devuelve el número de error de
oracle.
SQLERRM devuelve el correspondiente mensaje
de error.
Lic. Cristian Mercado Quispe - UPEA
DECLARE
NOMBRE EMPLOYEES.FIRST_NAME%TYPE;
IDEMP INT:= &VAR_SUS;
BEGIN
SELECT FIRST_NAME INTO NOMBRE FROM
EMPLOYEES
WHERE EMPLOYEE_ID=IDEMP;
DBMS_OUTPUT.PUT_LINE(NOMBRE);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE();
END;
Lic. Cristian Mercado Quispe - UPEA
Uso de SQLCODE y SQLERRM
Se puede pasar un número negativo que
represente un error de oracle y mostrar el
mensaje asociado:
DECLARE
Mensaje VARCHAR2(100);
BEGIN
Mensaje := SQLERRM(-1403);
DBMS_OUTPUT.PUT_LINE(mensaje);
END;
Lic. Cristian Mercado Quispe - UPEA
Ejemplo:
Crear un bloque anónimo que muestre los
mensajes de los siguientes errores
ORA-1
ORA-1722
ORA-6500
ORA-1422
Lic. Cristian Mercado Quispe - UPEA
