Help with PL / SQL insert in Oracle

Question

Help with PL / SQL insert in Oracle

Navigation

#1 by (1 votes)

4

What happens is that I am trying to do a search in two tables and through cursors with parameters do the calculation of a tax and insert the data in a new table called taxes. I have three tables, AUTOMOVIL , AVALUOS e IMPUESTOS .

These would be the tables Automovil and Avaluos . The idea is through a program PL / SQL insert PLACA , VIGENCIA and% Impuesto that is calculated by applying the percentage of the tax to the value of the table automovil . I do not know if there is an error with my logic; but when I try to make the inserts it does not leave any data in the table Impuestos .

This is the script that I used:

set serveroutput on
set verify off

declare

cursor a is
select marca, placa, valor
from automovil;

cursor av (marc varchar2) is
select *
from avaluos 
where marca = marc;

cursor imp is
select * from impuestos;

impu number;
pla varchar2(150);
vige number;


begin
for ra in a loop
pla := ra.placa;
  for rav in av(ra.marca) loop
    impu := ra.valor - (ra.valor * (rav.porcentaje/100));
    vige := rav.vigencia;
        for rimp in imp  loop
        insert into impuestos (placa, vigencia, impuesto)
            values (pla, vige, impu);
            commit;
            end loop;
        end loop;
           end loop;

     end;
     /

They help me to see Where is the problem with the?

oracle oracle-11g plsql

asked by Raidmaster 04.10.2018 в 16:48

source

1 answer

Position objects in HTML and CSS Consult twice the same table

score 1 · Answer 1

The error is not syntax, however it is unnecessary to iterate the tax table, then the problem is logical, since not having records the tax table, the imp cursor, will never insert data even if there are appraisals for the different brands of cars. let's see:

DECLARE 
  CURSOR a IS
  SELECT marca,
         placa, 
         valor
    FROM automovil;

   CURSOR av (marc VARCHAR2) IS
   SELECT *
     FROM avaluos 
    WHERE marca = marc;

   --CURSOR imp IS
   --SELECT * 
   --FROM impuestos;

    impu  NUMBER;
     pla  automovil.placa%TYPE;
    vige  avaluos.vigencia%TYPE;

BEGIN

FOR ra IN a LOOP
    pla := ra.placa;
    FOR rav IN av(ra.marca) LOOP
        impu := ra.valor - (ra.valor * (rav.porcentaje/100));
        vige := rav.vigencia;
        --FOR rimp IN imp LOOP
          INSERT INTO impuestos (placa, vigencia, impuesto) 
               VALUES (pla, vige, impu);            
        --END LOOP;
    END LOOP;
END LOOP;

COMMIT;
EXCEPTION 
WHEN OTHERS THEN 
  RAISE_APPLICATION_ERROR(-20010,'Error insertando impuestos, causado por '||SQLERRM 
  || ' -- Linea: ' || dbms_utility.format_error_backtrace());       
END;
/

Also become accustomed to using the handle and capture the exceptions, if your error is syntax you can locate the problem quickly. This block can be considerably improved by joining the appraisal and automobile tables, using an INNER JOIN, LEFT JOIN, etc. according to the case. Keep in mind always use both primary and foreign keys in your tables normalization of database even if they are examples of study. A functional example of the above would be:

DECLARE 

 CURSOR curImp IS
 SELECT at.placa, 
        av.vigencia,
        (at.valor - (at.valor * (av.porcentaje/100))) imp        
   FROM automovil at
  INNER JOIN avaluos av ON av.marca = av.marca;

 BEGIN

   FOR i IN curImp LOOP
      INSERT INTO impuestos (placa, vigencia, impuesto) 
           VALUES (i.placa, i.vigencia, i.imp);                    
   END LOOP;
   COMMIT;

 EXCEPTION 
 WHEN OTHERS THEN 
   RAISE_APPLICATION_ERROR(-20010,'Error insertando impuestos, causado por: 
   '||SQLERRM || ' -- Linea: ' ||dbms_utility.format_error_backtrace());        
 END;

I hope that even if the answer is late, it serves as an object of study.