Problem with for (cursor) and if linked Oracle PL / SQL procedure

Question

Problem with for (cursor) and if linked Oracle PL / SQL procedure

Navigation

#1 by (2 votes)

1

What I intend is to create a procedure that updates the sale price of a piece whose price is passed by parameter, this will be done only if the new price is greater than the existing one in the database. For now I want to do it without exceptions, and later add them if it can be.

The issue is that it gives me a fault, indicating that there is an IS of more, but I have similar ones and in principle I think the structure is like that, I declare a cursor because the execution of the query gives more than one row, what I want to enter the cursor locate the price, and if it is higher, update it as a parameter.

CREATE OR REPLACE PROCEDURE actualizarPrecio (precioNuevo PIEZAS.PRECIO_VENTA%TYPE,modeloa PIEZAS.MODELO%TYPE)
IS
cursorx IS
  SELECT PRECIO_VENTA FROM PIEZAS WHERE MODELO=modeloa;
  resulta number;
BEGIN
FOR v_reg IN cursorx LOOP
  IF (precioNuevo > precio_venta) THEN
    UPDATE PIEZAS SET PRECIO_VENTA=precioNuevo WHERE MODELO=modeloa;
    resulta:= cursorx%ROWCOUNT;
  ELSE
  DBMS_OUTPUT.PUT_LINE('No se puede actualizar el precio ');
    END IF;
    END LOOP;
  CLOSE cursorx;
  DBMS_OUTPUT.PUT_LINE('Filas actualizadas ' ||resulta);
END;

sql bucle-for plsql

asked by Mrs Prerobot 12.05.2018 в 16:58

source

1 answer

Android Fragments bundle getArguments () returns null Calculate dimensions of an image using jQuery

score 2 · Accepted Answer

I've seen several things.

I'm not sure if it's mandatory or not, but I consider it a good practice add the type of parameter that is received (input, output or entry / exit). We do this by adding an IN between the name of the parameter and type.

In this case it is not necessary to close the cursor as it closes automatically, just like it opens.

I do not see the rowtype statement v_reg . I'm going to assume that you declare in the package specifications.

As for your error concretely, you missed the word CURSOR before of the name of the cursor.

Try this way:

CREATE OR REPLACE PROCEDURE actualizarPrecio (precioNuevo IN PIEZAS.PRECIO_VENTA%TYPE, modeloa IN PIEZAS.MODELO%TYPE)
IS
    CURSOR cursorx IS
        SELECT PRECIO_VENTA FROM PIEZAS WHERE MODELO=modeloa;

    rowCursorx cursorx%ROWTYPE;
    resulta number;
BEGIN
    FOR rowCursorx IN cursorx LOOP
        IF (precioNuevo > rowCursor.precio_venta) THEN
            UPDATE PIEZAS SET PRECIO_VENTA=precioNuevo WHERE MODELO=modeloa;
            resulta:= cursorx%ROWCOUNT;
        ELSE
            DBMS_OUTPUT.PUT_LINE('No se puede actualizar el precio ');
        END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Filas actualizadas ' ||resulta);
END;

Although I think it has and a simpler way:

CREATE OR REPLACE PROCEDURE actualizarPrecio (precioNuevo IN PIEZAS.PRECIO_VENTA%TYPE, modeloa IN PIEZAS.MODELO%TYPE)
IS

    resulta NUMBER;

BEGIN

    SELECT COUNT(1) INTO resulta
    FROM PIEZAS
    WHERE MODELO = modeloa
        AND precioNuevo > PRECIO_VENTA;

    IF (resulta > 0) THEN
        UPDATE PIEZAS SET 
            PRECIO_VENTA=precioNuevo 
        WHERE MODELO=modeloa
            AND precioNuevo > PRECIO_VENTA;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No se puede actualizar el precio ');
    END IF;

    DBMS_OUTPUT.PUT_LINE('Filas actualizadas ' ||resulta);

END;