BLOQUES
MULTIBLOQUES
SET SERVEROUTPUT ON;
<<principal>>
DECLARE
x PLS_INTEGER := 5;
BEGIN
DECLARE
x PLS_INTEGER := 3;
y PLS_INTEGER;
BEGIN
y := x + principal.x;
DBMS_OUTPUT.PUT_LINE('El resultado es: ' || y);
END;
DBMS_OUTPUT.PUT_LINE('El valor de X es: ' || x);
END;
Excepciones
EXCEPTION
SET SERVEROUTPUT ON;
DECLARE ...
BEGIN ...
EXCEPTION
WHEN ... THEN
DECLARE
BEGIN
EXCEPTION
END;
END;
WHEN-THEN
….
EXCEPTION
WHEN ZERO_DIVIDE THEN
-- Tratemos de resolver el problema
DECLARE
BEGIN
EXCEPTION
END;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No hay resultados');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Recuerda que sólo se permite un resultado!!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Ni idea….' || SQLERRM);
END;
RAISE
SET SERVEROUTPUT ON;
DECLARE
PEDIDO_MUY_BAJO EXCEPTION;
cantidad PLS_INTEGER := 5;
precio DEC(7,2) := -14.99;
BEGIN
IF cantidad < 5 THEN
RAISE PEDIDO_MUY_BAJO;
END IF;
IF precio < 0 THEN
RAISE INVALID_NUMBER;
END IF;
END;
Stored Procedures
PROCEDURE
CREATE [OR REPLACE] PROCEDURE MI_PROCEDIMIENTO1
(X IN PLS_INTEGER, Y IN PLS_INTEGER, Z OUT PLS_INTEGER) AS
BEGIN
Z := X + Y;
END MI_PROCEDIMIENTO1;
CREATE [OR REPLACE] PROCEDURE MI_PROCEDIMIENTO2
(X IN OUT PLS_INTEGER, Y IN PLS_INTEGER DEFAULT 7) AS
BEGIN
X := X + Y;
END MI_PROCEDIMIENTO2;
CALL PROCEDURES
DECLARE
param1 PLS_INTEGER := 5;
param2 PLS_INTEGER := 3;
resultado PLS_INTEGER;
BEGIN
MI_PROCEDIMIENTO1(param1, param2, resultado);
DBMS_OUTPUT.PUT_LINE('El resultado es….' || resultado);
MI_PROCEDIMIENTO2(resultado); -- por defecto suma 7
DBMS_OUTPUT.PUT_LINE('El resultado es….' || resultado);
[EXCEPTION]
…
END;
TRANSACTIONS
CREATE [OR REPLACE] PROCEDURE MI_PROCEDIMIENTO3 (X IN PLS_INTEGER) AS
BEGIN
DELETE FROM USUARIOS WHERE ID > 5;
MI_PROCEDIMIENTO4;
ROLLBACK;
END MI_PROCEDIMIENTO3;
CREATE [OR REPLACE] PROCEDURE MI_PROCEDIMIENTO4 AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE USUARIOS SET NOMBRE = 'Oliver' WHERE ID = 15;
COMMIT;
END MI_PROCEDIMIENTO4;
Stored Functions
FUNCTION
CREATE [OR REPLACE]
FUNCTION MI_FUNCION (X PLS_INTEGER, Y PLS_INTEGER) RETURN PLS_INTEGER AS
DECLARE
Z PLS_INTEGER;
BEGIN
Z := X + Y;
RETURN Z;
END MI_FUNCION;
CALL FUNCTIONS
DECLARE
param1 PLS_INTEGER := 5;
param2 PLS_INTEGER := 3;
resultado PLS_INTEGER;
BEGIN
resultado := MI_FUNCION(param1, param2);
DBMS_OUTPUT.PUT_LINE('El resultado es….' || resultado);
resultado := MI_FUNCION(&entrada1, &entrada2); -- variables de sustitución
DBMS_OUTPUT.PUT_LINE('El resultado es….' || resultado);
[EXCEPTION]
…
END;
Permisos
GRANT
GRANT UPDATE ON HR.EMPLOYEES TO CURSO;
GRANT INSERT,DELETE ON HR.EMPLOYEES TO PUBLIC;
GRANT ALL ON HR.EMPLOYEES TO PUBLIC;
GRANT ALL ON HR.EMPLOYEES TO CURSO WITH GRANT OPTION;
GRANT EXECUTE ON MI_PROCEDIMIENTO TO CURSO;
GRANT EXECUTE ON MI_FUNCION TO PUBLIC;
GRANT CONNECT TO CURSO;
GRANT DBA TO NUEVO_USUARIO IDENTIFIED BY p@$$w0rd;
REVOKE
REVOKE UPDATE ON HR.EMPLOYEES FROM CURSO;
REVOKE INSERT,DELETE ON HR.EMPLOYEES FROM PUBLIC;
REVOKE ALL ON HR.EMPLOYEES FROM PUBLIC;
REVOKE ALL ON HR.EMPLOYEES FROM CURSO;
REVOKE EXECUTE ON MI_PROCEDIMIENTO FROM CURSO;
REVOKE EXECUTE ON MI_FUNCION FROM PUBLIC;
REVOKE CONNECT FROM CURSO;
REVOKE DBA FROM NUEVO_USUARIO;
ORACLE CURSO
1. Obtener mediante procedimiento
• Cuántos pedidos ha realizado Edward Logan
• Las fechas en formato dd-mm-yy
• El número de personas que han pedido los mismos artículos
• El total de ingresos por las ventas de dichos artículos (pedidos completos)
2. Crear un procedimiento que permita crear cuentas de usuario a
partir de clientes
3. Dividir el procedimiento anterior en funciones y procedimientos
con control de excepciones
• Buscar un cliente por apellido (error si hay más de uno)
• Convertir una fila DEMO_CUSTOMER en DEMO_USERS
• Solicitando el password por consola
• Sólo permitir clientes existentes
