CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Samuel Van Bladel
Samuel Van Bladel

Reputation: 67

PL/SQL Trigger with a cursor and loop

I'm very new to PL/SQL and i'm trying to have a DBMS output on the console of the total amount of cars for each brand. I've been breaking my head over this but don't seem to get out of it...

What am I doing wrong?


ERROR#
--------- -------------------------------------------------------------
3/5       PL/SQL: SQL Statement ignored
3/36      PLS-00201: identifier 'CUENTA' must be declared
4/5       PL/SQL: ORA-00904: : invalid identifier
9/5       PL/SQL: Statement ignored
9/17      PLS-00201: identifier 'TOTALMARACAS' must be declared


CREATE OR REPLACE TRIGGER t_mostrarmarcas 
AFTER INSERT OR UPDATE 
ON coches_seg_mano
FOR EACH ROW
DECLARE
    CURSOR totalmarcas IS
        SELECT matriculo,COUNT(*) INTO CUENTA
        FROM coches_seg_mano 
        GROUP BY matriculo;

    fila coches_seg_mano%rowtype;
    vmarca coches_seg_mano.marca%TYPE;
BEGIN
    FOR fila IN totalmaracas LOOP
        SELECT marca INTO vmarca FROM coches_seg_mano WHERE fila.cuenta=matriculo; 
        DBMS_OUTPUT.PUT_LINE('Para la marca '|| vmarca ||' tenemos '|| fila.cuenta ||' vehiculos');
    END LOOP;
END;

Upvotes: 0

Views: 2489

Answers (2)

Popeye
Popeye

Reputation: 35920

INTO is not needed in cursor. Instead you can give alias to count(*). Also you dont need to declare variable fila.

Your code should look like following:

CREATE OR REPLACE TRIGGER t_mostrarmarcas 
AFTER INSERT OR UPDATE 
ON coches_seg_mano
FOR EACH ROW
DECLARE
    CURSOR totalmarcas IS
    SELECT matriculo,COUNT(*) as CUENTA -- this
    FROM coches_seg_mano 
    GROUP BY matriculo;
    -- fila coches_seg_mano%rowtype; -- not needed 
    vmarca coches_seg_mano.marca%TYPE;
BEGIN
    FOR fila IN totalmaracas LOOP
    SELECT marca INTO vmarca FROM coches_seg_mano WHERE fila.cuenta=matriculo; 
    DBMS_OUTPUT.PUT_LINE('Para la marca '|| vmarca ||' tenemos '|| fila.cuenta ||' vehiculos');
    END LOOP;
END;

Cheers!!

Upvotes: 0

alexherm
alexherm

Reputation: 1362

You are trying to select into an undeclared variable CUENTA

You need to define CUENTA with a datatype before trying to use it in the cursor.

Upvotes: 1

Related Questions