Problem with LOOP PL / SQL, Oracle [closed]

Question

Problem with LOOP PL / SQL, Oracle [closed]

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I am currently working on a query with PL / SQL which I have not been able to do, the idea of this query is to be able to traverse (or compare) the rows of a table given the upper and lower limits of the table, that is, for example: there is a table called "employees", where the daily salary of a person (which may vary according to the day) is stored (among others), that is, in this table we will have a user id for a date (20180701) and your daily pay, the idea is that the company administrator enter the id of the employee and for the period of time you want to review (that is, a start date and end date) for that period the query should be able to tell you how many times the difference in salary between every day exceeded a threshold for example of 5%

Then the code in question:

(It is important to keep in mind that for the moment the code has been limited to comparing several days and showing only the number of times where the difference is greater than 5% or not)

DECLARE
u NUMBER :=0;
t NUMBER :=0;
r NUMBER :=0; 
k NUMBER:= 0;

FOR i IN 0... 5 LOOP 
u:= 0;
k:= k+2;
BEGIN  
(SELECT * FROM empleados WHERE FECHA = to_number(to_char(To_Date('20180809', 'yyyymmdd') - u, 'yyyymmdd')))a,
(SELECT * FROM empleados WHERE FECHA = to_number(to_char(To_Date('20180809', 'yyyymmdd') - k, 'yyyymmdd')))b,
IF (Trunc (((b.salario - a.salario)/b.salario)*100, 2) < 5%)
t:=t+1;
END IF;
ELSE IF (Trunc (((b.salario - a.salario)/b.salario)*100, 2) > 5%)
r:=r+1:
END IF;

u AS Veces_que_supero_5%,
t AS Veces_que_no_supero_5%

END LOOP
/

When executing the Script the following error is obtained:

ORA-06550: línea 6, columna 1:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior
The symbol "begin" was substituted for "FOR" to continue.
ORA-06550: línea 6, columna 11:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   * & - + / at mod remainder rem .. <an exponent (**)> ||
   multiset
The symbol ".. was in
DECLARE
u NUMBER :=0;
t NUMBER :=0;
r NUMBER :=0; 
k NUMBER:= 0;

FOR i IN 0... 5 LOOP 
u:= 0;
k:= k+2;
BEGIN  
(SELECT * FROM empleados WHERE FECHA = to_number(to_char(To_Date('20180809', 'yyyymmdd') - u, 'yyyymmdd')))a,
(SELECT * FROM empleados WHERE FECHA = to_number(to_char(To_Date('20180809', 'yyyymmdd') - k, 'yyyymmdd')))b,
IF (Trunc (((b.salario - a.salario)/b.salario)*100, 2) < 5%)
t:=t+1;
END IF;
ELSE IF (Trunc (((b.salario - a.salario)/b.salario)*100, 2) > 5%)
r:=r+1:
END IF;

u AS Veces_que_supero_5%,
t AS Veces_que_no_supero_5%

END LOOP

Excuse me but I do not understand why you mention the declare comparing it with the for loop. On the other hand, change the begin as you suggested and the problem persists with the error

ORA-06550: línea 8, columna 11:
PLS-00103: Encountered the symbol "." when expecting one of the following:

   * & - + / at mod remainder rem .. <an exponent (**)> ||
   multiset
The symbol ".. was inserted before "." to continue.
ORA-06550: línea 12, columna 2:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue avg count current exists max min prior sql stdde
DECLARE
u NUMBER :=0;
t NUMBER :=0;
r NUMBER :=0; 
k NUMBER:= 0;

BEGIN 
FOR i IN 0... 5 LOOP 
u:= 0;
k:= k+2;

(SELECT * FROM empleados WHERE FECHA = to_number(to_char(To_Date('20180809', 'yyyymmdd') - u, 'yyyymmdd')))a,
(SELECT * FROM empleados WHERE FECHA = to_number(to_char(To_Date('20180809', 'yyyymmdd') - k, 'yyyymmdd')))b,
IF (Trunc (((b.salario - a.salario)/b.salario)*100, 2) < 5%)
t:=t+1;
END IF;
ELSE IF (Trunc (((b.salario - a.salario)/b.salario)*100, 2) > 5%)
r:=r+1:
END IF;

u AS Veces_que_supero_5%,
t AS Veces_que_no_supero_5%

END LOOP

sql oracle plsql

asked by lcazzadore 09.08.2018 в 17:54

source

2 answers

Problems printing a PDF on 000webhost Function Load () Jquery loads me on the div but, not its jquery events

score 0 · Answer 1

You do not need to create a loop to solve that problem you ask for.

You can do this by pulling the analytic functions and perform the calculation directly in a query. In this specific case, the lag function returns the value of a column in the previous row of the query. With this you have everything you need to do the calculation.

It's easier for me to explain it with an example, so look at what I've prepared for this answer:

with
EmpleadoSalarioDiario as (
          select 101 idEmpleado, to_date('2018-01-01', 'yyyy-mm-dd') Fecha, 100.0 Salario from dual
union all select 101, to_date('2018-01-02', 'yyyy-mm-dd'), 110 from dual
union all select 101, to_date('2018-01-03', 'yyyy-mm-dd'), 112 from dual
union all select 101, to_date('2018-01-04', 'yyyy-mm-dd'), 100 from dual
union all select 101, to_date('2018-01-05', 'yyyy-mm-dd'), 105 from dual
union all select 101, to_date('2018-01-06', 'yyyy-mm-dd'), 99 from dual
union all select 101, to_date('2018-01-07', 'yyyy-mm-dd'), 100 from dual
union all select 101, to_date('2018-01-08', 'yyyy-mm-dd'), 101 from dual
union all select 101, to_date('2018-01-09', 'yyyy-mm-dd'), 102 from dual
union all select 101, to_date('2018-01-10', 'yyyy-mm-dd'), 110 from dual
union all select 101, to_date('2018-01-11', 'yyyy-mm-dd'), 111 from dual
union all select 101, to_date('2018-01-12', 'yyyy-mm-dd'), 120 from dual
union all select 101, to_date('2018-01-13', 'yyyy-mm-dd'), 121 from dual
union all select 101, to_date('2018-01-14', 'yyyy-mm-dd'), 100 from dual
union all select 101, to_date('2018-01-15', 'yyyy-mm-dd'), 106 from dual
)
select   a.*
       , lag(a.Salario) over (partition by a.idEmpleado order by a.Fecha) SalarioDiaAnterior 
       , abs(a.Salario - lag(a.Salario) over (partition by a.idEmpleado order by a.Fecha)) DiferenciaSalario
       , abs(a.Salario - lag(a.Salario) over (partition by a.idEmpleado order by a.Fecha)) / lag(a.Salario) over (partition by a.idEmpleado order by a.Fecha) * 100 DiferenciaPorcentualSalario
  from EmpleadoSalarioDiario a;

The result is as follows:

idE Fecha       Sal SlA Dif DifPorcentual
=== ========= ===== === === ========================================
101 01-JAN-18   100  -   -   - 
101 02-JAN-18   110 100 10  10
101 03-JAN-18   112 110 2   1.81818181818181818181818181818181818182
101 04-JAN-18   100 112 12  10.71428571428571428571428571428571428571
101 05-JAN-18   105 100 5   5
101 06-JAN-18   99  105 6   5.71428571428571428571428571428571428571
101 07-JAN-18   100 99  1   1.01010101010101010101010101010101010101
101 08-JAN-18   101 100 1   1
101 09-JAN-18   102 101 1   .9900990099009900990099009900990099009901
101 10-JAN-18   110 102 8   7.84313725490196078431372549019607843137
101 11-JAN-18   111 110 1   .9090909090909090909090909090909090909091
101 12-JAN-18   120 111 9   8.10810810810810810810810810810810810811
101 13-JAN-18   121 120 1   .8333333333333333333333333333333333333333
101 14-JAN-18   100 121 21  17.35537190082644628099173553719008264463
101 15-JAN-18   106 100 6   6

With this result, it is trivial to obtain the rows (or count them) in which the percentage variation is greater than 5%. That exercise remains for the reader.

If you do not have Oracle on hand, you can find and run the script directly in liveSQL

score 0 · Answer 2

Good morning. I see several inconsistencies in your code, you make an anonymous block but I do not see that you close it, and the select's to get the data, you do not see where the values will be left (some arrangement or temporary table?).

The solution that I suggest is to use the LEAD analytical function, for more information you can consult the following link: LEAD function

And making some assumptions based on the code that you include, I built the following code, try it, it only replaces the employee ID in the table EMPELADO_ID :

DECLARE
  CURSOR c1(EMP_ID NUMBER, FECHA_INI DATE, FECHA_FIN DATE) IS
    SELECT
      EMP.EMPLEADO_ID
      EMP.FECHA,
      EMP.SALARIO,
      /*Esta función agregada da el salario en n registros, si es un registro por fecha, o sea un pago por día por empleado, 
        entonces el resutlado es el empleado dado, los salarios de la fecha del registro y la columna salario_2 con el salario 
        del día siguiente, explicado cada parametro:
        LEAD ( --función que regresa valores hacia adelante
              EMP.SALARIO,  -- la columna a obtener el valor
              1,            -- número de registros hacia adelante según clausula OVER
              null)         -- si ya no hay más registros para adelante regresa el valor especificado aquí
         OVER (ORDER BY EMP.FECHA) -- En esta clausula se especifica como se va a ordenar el resutlado para obtener valores hacía adelante, en este caso es 1
        */
      LEAD(EMP.SALARIO,1,NULL) OVER (ORDER BY EMP.FECHA) AS SALARIO_2 
    FROM EMPLEADOS EMP
    WHERE
      EMP.EMPLEADO_ID = EMP_ID AND
      EMP.FECHA BETWEEN FECHA_INI AND FECHA_FIN;

  NEMP_ID NUMBER;
  DFECHA_INI DATE;
  DFECHA_FIN DATE;
  NVECES_SUP_5POR PLS_INTEGER;
  NVECES_NO_SUP_5POR PLS_INTEGER;
  FPOR_DIF NUMBER;
BEGIN

  NVECES_SUP_5POR  := 0;
  NVECES_NO_SUP_5POR := 0;

  --Empleado id
  NEMP_ID := 580;
  --rango de fechas
  DFECHA_INI := TO_DATE('20180801','YYYYMMDD');
  DFECHA_FIN := TO_DATE('20180810','YYYYMMDD');

  FOR C1REC IN C1(NEMP_ID, DFECHA_INI, DFECHA_FIN) LOOP
    FPOR_DIF := TRUNC(((C1REC.SALARIO_2 - C1REC.SALARIO)/C1REC.SALARIO_2)*100, 2);
    IF FPOR_DIF > 5 THEN
      NVECES_SUP_5POR := NVECES_SUP_5POR + 1;
    ELSE
      NVECES_NO_SUP_5POR := NVECES_NO_SUP_5POR + 1;  
    END;
  END LOOP;

END; --FIN DE BLOQUE ANONIMO

I hope you find it useful.