PL/SQL Procedures, Functions, and Triggers

Posted on Mar 11, 2025 in Computing and Communications

PL/SQL Procedure EJ1

This PL/SQL procedure inserts data into the PERMISOS table based on data from the USUARIOS table.

CREATE OR REPLACE PROCEDURE EJ1(
    p_ciudad usuarios.ciudad%TYPE,
    p_dpto usuarios.dpto%TYPE,
    p_total OUT number
) AS
    CURSOR c_usuarios IS
        SELECT idu, dpto
        FROM usuarios
        WHERE dpto = p_dpto AND ciudad = p_ciudad;
BEGIN
    p_total := 0;
    FOR v_registro IN c_usuarios LOOP
        INSERT INTO PERMISOS VALUES (v_registro.idu, p_dpto, 'DELETE', 'MITABLA');
        p_total := p_total + 1;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLCODE || ' message: ' || SQLERRM);
END;

Example Usage of Procedure EJ1

DECLARE
    v_dpto usuarios.dpto%TYPE;
    v_ciudad usuarios.ciudad%TYPE;
    v_total number;
BEGIN
    v_dpto := 'INF';
    v_ciudad := 'Murcia';
    EJ1(v_ciudad, v_dpto, v_total);
    DBMS_OUTPUT.PUT_LINE('The total number of users requested is: ' || v_total);
END;

PL/SQL Function EJ2

This PL/SQL function returns the total number of rows in PERMISOS with tipo ‘DELETE’ for a given department (p_dpto).

CREATE OR REPLACE FUNCTION EJ2 (
    p_idu usuarios.idu%TYPE,
    p_dpto usuarios.dpto%TYPE
)
RETURN number IS
    totalTablas NUMBER(5) := 0;
    contador NUMBER(3) := 0;
BEGIN
    SELECT COUNT(*) INTO contador FROM usuarios WHERE dpto = p_dpto;
    IF contador = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'User does not exist.');
    END IF;

    SELECT COUNT(*) INTO totalTablas FROM PERMISOS WHERE dpto = p_dpto AND tipo = 'DELETE';
    RETURN totalTablas;
EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

PL/SQL Trigger EJ3

This PL/SQL trigger logs operations on the PERMISOS table and executes a revoke command.

CREATE OR REPLACE TRIGGER EJ3
BEFORE INSERT OR UPDATE OR DELETE ON PERMISOS
FOR EACH ROW
DECLARE
    CAD VARCHAR2(200);
BEGIN
    IF INSERTING OR UPDATING THEN
        INSERT INTO LOGS VALUES (:OLD.IDU, :OLD.TIPO, :OLD.DPTO, :OLD.TABLA, SYSDATE);
        CAD := 'REVOKE ' || :OLD.TIPO || ' ON ' || :OLD.TABLA || ' FROM ' || :OLD.IDU;
        EXECUTE IMMEDIATE CAD;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        NULL; -- Consider better error handling
END;

PL/SQL Function EJ4

Calculates the total amount of orders for a specific product and store.

CREATE OR REPLACE FUNCTION EJ4 (
    v_tid pedidos.tid%TYPE,
    v_pid pedidos.pid%TYPE
) RETURN NUMBER AS
    total_ped NUMBER := 0;
BEGIN
    SELECT SUM(cant * precio) INTO total_ped
    FROM pedidos, productos
    WHERE v_tid = pedidos.tid AND v_pid = pedidos.pid AND pedidos.pid = productos.pid;
    RETURN total_ped;
    -- DBMS_OUTPUT.PUT_LINE is not allowed inside a function used in SQL.
END EJ4;

PL/SQL Procedure StockCorrecto

Checks and updates product stock.

CREATE OR REPLACE PROCEDURE StockCorrecto (
    p_pid pedidos.pid%TYPE,
    correcto OUT VARCHAR2(2)
) IS
    stockActual productos.stock%TYPE;
    stockCorrecto productos.stock%TYPE;
    totalPedido pedidos.cant%TYPE;
BEGIN
    SELECT stock INTO stockActual FROM productos WHERE pid = p_pid;
    SELECT SUM(cant) INTO totalPedido FROM pedidos WHERE pid = p_pid;
    stockCorrecto := 1000 - totalPedido;
    IF stockCorrecto < 0 THEN
        correcto := 'uf';
        UPDATE productos SET stock = 0;
    ELSIF stockActual = stockCorrecto THEN
        correcto := 'ok'; -- Added missing assignment
    ELSIF stockActual <> stockCorrecto THEN
        correcto := 'nk';
    END IF;
END;

Example Usage:

DECLARE
  idPr productos.pid%TYPE;
  v_correcto VARCHAR2(2); -- Corrected variable name
BEGIN
  idPr := 3;
  StockCorrecto(idPr, v_correcto); -- Corrected variable name
  DBMS_OUTPUT.PUT_LINE(v_correcto);
END;

PL/SQL Trigger stockActualizado

This trigger updates the stock in the productos table when a new order is inserted, updated, or deleted in the pedidos table.

CREATE OR REPLACE TRIGGER stockActualizado
BEFORE INSERT OR UPDATE OR DELETE ON pedidos
FOR EACH ROW
DECLARE
    stockActual productos.stock%TYPE;
BEGIN
    IF INSERTING THEN
        SELECT stock INTO stockActual FROM productos WHERE pid = :NEW.PID;
        IF (stockActual - (:NEW.CANT)) < 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Stock cannot be negative.');
        ELSE
            UPDATE productos SET stock = stock - :NEW.cant WHERE pid = :NEW.pid;
        END IF;
    END IF;

    IF UPDATING THEN -- Added missing update logic
        SELECT stock INTO stockActual FROM productos WHERE pid = :NEW.PID;
        IF (stockActual - (:NEW.CANT - :OLD.CANT)) < 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Stock cannot be negative.');
        ELSE
            UPDATE productos SET stock = stock - (:NEW.cant - :OLD.cant) WHERE pid = :NEW.pid;
        END IF;
    END IF;

    IF DELETING THEN
        UPDATE PRODUCTOS SET stock = stock + (:OLD.cant) WHERE pid = :OLD.pid;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred...');
END;

PL/SQL Anonymous Block (Cursor Example)

Calculates the total weight of orders from Barcelona with a price greater than 50.

DECLARE
    CURSOR pedBarcelona IS
        SELECT p.cant, pr.peso
        FROM pedidos p, productos pr, almacenes a
        WHERE p.pid = pr.pid AND p.aid = a.aid AND a.aloc = 'Barcelona' AND pr.precio > 50;
    total NUMBER(15, 2) := 0;
BEGIN
    FOR v IN pedBarcelona LOOP
        total := total + (v.cant * v.peso);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('The total is ' || total);
END;