Exercise in PL / SQL

0

Encode a procedure that receives as parameters a department number, an amount and a percentage and that raises the salary to all the employees of the department indicated in the call. The increase will be the percentage or amount indicated in the call (whichever is the most beneficial for the employee in each case). Indicate when finishing the number of updated rows.

CREATE OR REPLACE PROCEDURE actualizacion (numeroDepartamento NUMBER, importe NUMBER, porcentaje NUMBER)
AS
CURSOR c_empleados IS
SELECT salario FROM emple WHERE dept_no=numeroDepartamento FOR UPDATE;
v_regEmpleados c_empleados%ROWTYPE;
v_importe NUMBER(3);
v_porcentaje NUMBER(4,1);
BEGIN
OPEN c_empleados;
    FETCH c_empleados INTO v_regEmpleados;
    WHILE c_empleados %FOUND LOOP
    v_porcentaje:=(v_regEmpleados.salario/100)*porcentaje;
    v_importe:=v_regEmpleados.salario+importe;
        IF (v_importe > v_porcentaje) THEN
          UPDATE emple SET salario=salario+v_importe
            WHERE CURRENT OF c_empleados;
        ELSE
            UPDATE emple SET salario=salario+v_porcentaje
                WHERE CURRENT OF c_empleados;
          END IF;      
    FETCH c_empleados INTO v_regEmpleados;
    END LOOP;
    CLOSE c_empleados;
END;

I can not find the error, and I do not know where I would have to put% rowcount to tell me the updated rows.

    
asked by AntonioJZP 07.04.2018 в 19:42
source

2 answers

0

For these cases, indicating the error message would make it easier for us to tell you that it may fail. Still, I see an error in the way you receive the parameters, you need to indicate if it is input, output or input / output. It would be something like this:

CREATE OR REPLACE PROCEDURE actualizacion (numeroDepartamento IN NUMBER, importe IN NUMBER, porcentaje IN NUMBER)

On the other hand, I would not use the rowcount. I would add a countable variable that increases in each iteration of the loop. Finally, adding the recommendations of B. Pato, the procedure would be something like this (there may be some error, I do not have Oracle installed on this machine and it is impossible to prove):

CREATE OR REPLACE PROCEDURE actualizacion (numeroDepartamento IN NUMBER, importe IN NUMBER, porcentaje IN NUMBER)
IS
    CURSOR c_empleados IS
        SELECT salario FROM emple WHERE dept_no=numeroDepartamento FOR UPDATE;
    v_regEmpleados c_empleados%ROWTYPE;
    v_importe NUMBER(3);
    v_porcentaje NUMBER(4,1);
    v_affectedRow NUMBER DEFAULT 0;
BEGIN
    OPEN c_empleados;
    FETCH c_empleados INTO v_regEmpleados;
    WHILE c_empleados%FOUND LOOP
        v_porcentaje:=(v_regEmpleados.salario/100)*porcentaje;
        v_importe:=v_regEmpleados.salario+importe;

        IF (v_importe > v_porcentaje) THEN
            UPDATE emple SET salario=salario+v_importe
            WHERE CURRENT OF c_empleados;
        ELSE
            UPDATE emple SET salario=salario+v_porcentaje
            WHERE CURRENT OF c_empleados;
        END IF;    

        v_affectedRow := v_affectedRow + 1;

        FETCH c_empleados INTO v_regEmpleados;
    END LOOP;

    CLOSE c_empleados;
END;
    
answered by 08.04.2018 / 08:23
source
1

The procedures I think were structured with IS not AS:

CREATE OR REPLACE PROCEDURE...
IS
BEGIN
END;

Then in the loop to go around the cursor I think you should go together with% FOUND employees

Add that to use cursors I used more:

LOOP
   FETCH [cursor] INTO [variable];
   EXIT WHEN [cursor]%NOTFOUND;
   ...
END LOOP;

I have not played it since the course, I hope I have given you some light

    
answered by 07.04.2018 в 22:38