CODEWITHSUNDEEP
sqloracle-databaseplsql
Nicolas23
Nicolas23

Reputation: 19

How do I enter the data in this table in the insert?

I am new in pl / sql , I want to enter this data in the insert someone help me please? I do not know how to enter this data in the insert without making a join I can not find the way to enter the data in the insert

these     SELECT PORCENTAJE 
          FROM RANGO_SUBIDA_COMISION;





    VARIABLE B_ANIO NUMBER;

    EXECUTE :B_ANIO := 042018;

    DECLARE
    V_EMPLEADO_ID    EMPLEADOS.EMPLEADO_ID%TYPE;
    V_NOMBRE         EMPLEADOS.NOMBRE%TYPE;
    V_APELLIDO       EMPLEADOS.APELLIDO%TYPE;
    V_VALOR_COMISION COMISIONES.VALOR_COMISION%TYPE;
    V_SUELDO         EMPLEADOS.SUELDO%TYPE;

BEGIN

    FOR R IN

    (
    SELECT DISTINCT E.EMPLEADO_ID , E.NOMBRE , E.APELLIDO , C.VALOR_COMISION , E.SUELDO
    FROM EMPLEADOS E JOIN COMISIONES C
    ON (C.EMPLEADO_ID = E.EMPLEADO_ID)
    )
    LOOP

    V_EMPLEADO_ID      := R.EMPLEADO_ID;
    V_NOMBRE           := R.NOMBRE;
    V_APELLIDO         := R.APELLIDO;
    V_VALOR_COMISION   := R.VALOR_COMISION;
    V_SUELDO           := R.SUELDO;

    INSERT INTO INFORME_SUBIDA_COMISION 
    VALUES ( :B_ANIO ,V_EMPLEADO_ID , V_NOMBRE , V_APELLIDO , V_VALOR_COMISION , V_SUELDO  );

    END LOOP;
    END;

Upvotes: 0

Views: 55

Answers (2)

Debapriya Biswas
Debapriya Biswas

Reputation: 1349

you can use this insert in Bulk Mode if you have huge data to insert 

SET SERVEROUTPUT ON

/*use this script to insert data in bulk mode.*/
DECLARE
    TYPE array IS
        TABLE OF informe_subida_comision%rowtype;
    v_data          array;
    dml_errors EXCEPTION;
    PRAGMA exception_init ( dml_errors,-24381 );
    CURSOR l_cur IS
        SELECT DISTINCT
            e.empleado_id,
            e.nombre,
            e.apellido,
            c.valor_comision,
            e.sueldo
        FROM
            empleados e
            JOIN comisiones c ON (
                c.empleado_id = e.empleado_id
            );

    v_error_count   NUMBER;
    v_err           VARCHAR2(500);
    v_err_indx      NUMBER;
BEGIN
    OPEN l_cur;
    LOOP
        BEGIN
            FETCH l_cur BULK COLLECT INTO v_data LIMIT 1000;
            FORALL i IN 1..v_data.count SAVE EXCEPTIONS
      --insert data into the table 
                INSERT INTO informe_subida_comision VALUES v_data ( i );

       -- log any dml errors 

        EXCEPTION
            WHEN dml_errors THEN
                v_error_count := SQL%bulk_exceptions.count;
                FOR i IN 1..v_error_count LOOP
                    v_err_indx := SQL%bulk_exceptions(i).error_index;
                    v_err := sqlerrm(-SQL%bulk_exceptions(i).error_code);
                    dbms_output.put_line(v_data(v_err_indx).empleado_id
                     || ''
                     || v_err);
                END LOOP;

        END;

        COMMIT;
        EXIT WHEN l_cur%notfound;
    END LOOP;

    CLOSE l_cur;
END;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

I'm not quite sure what your question is. But I don't understand why you are using a cursor for this. Why not just execute a query like this?

INSERT INTO INFORME_SUBIDA_COMISION (ANIO, EMPLEADO_ID, NOMBRE, APELLIDO, VALOR_COMISION, SUELDO)
SELECT DISTINCT :B_ANIO, E.EMPLEADO_ID, E.NOMBRE, E.APELLIDO, 
       C.VALOR_COMISION, E.SUELDO
    FROM EMPLEADOS E JOIN COMISIONES C
         ON C.EMPLEADO_ID = E.EMPLEADO_ID;

Note: I'm guessing what the column names are in INFORME_SUBIDA_COMISION, but you should explicitly list them.

Upvotes: 5

Related Questions