[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

pl/sql sort code

Posted on 2011-10-21
4
Medium Priority
?
320 Views
Last Modified: 2012-06-27
Hi experts, I have a piece of code that I am calling 2 times,
INSERT INTO Table1  ...;
INSERT INTO Table2  ...;
INSERT INTO Table3  ...;
 can help me improve my logic to be called only once

i attached the code
optimizar-code.txt
0
Comment
Question by:enrique_aeo
4 Comments
 
LVL 3

Assisted Solution

by:John_Arifin
John_Arifin earned 800 total points
ID: 37010120
Make an store procedure:

Create procedure sp_insert_my_tables
As
Begin
     INSERT INTO Table1  ...;
     INSERT INTO Table2  ...;
     INSERT INTO Table3  ...;
End

Change your code to:

Else
     Exec sp_insert_my_tables
Endif

------------------------------------------------------------------------------

If you want to put to many tables you can iterate the table name:

Create procedure sp_insert_my_tables
As
Begin
   Declare @i int, @table_name varchar(6), @SqlStr varchar(100)
   Set @i = 1
   While @i <=9
   Begin
   Set @table_name = 'Table'+ LTRIM(STR(@i))
      Set @SqlStr = 'Select * INTO ' + @table_name + ' from bm_delete'
      Exec(@SqlStr)
      Set @i = @i + 1
   End
End

-----------------------------------------------------------------------------------

Anyway, if you just need to code as in your example. I think you code is simpler.
0
 
LVL 3

Assisted Solution

by:gajmp
gajmp earned 400 total points
ID: 37010688
Write Sub procedure
create or replace procedure mainproc
is
procedure insrt_proc (p_table)
is
begin
    if p_table = 'table1' then
         insert into table1
    elsif p_table = 'table2' then
         insert into table2
    elsif p_table = 'table3' then
         insert into table2
    end if;
end;
BEGIN
      IF VL_TIPOACUERDO='8' THEN -- ALTA
                IF V_FECHA_CONTRATO <= VL_FECHA_DEVOLUCION  THEN
                            insrt_proc ('table1');
                ELSE
                     insrt_proc ('table2');
                     insrt_proc ('table3');
                     insrt_proc ('table4');
                END IF;
            END IF;
END
0
 

Author Comment

by:enrique_aeo
ID: 37011501
still not very clear to me, I am attaching 2 files
 _repeat.txt: portion of code that is called 2 times
 two.txt _if full code
-if-two.txt
-repeat.txt
0
 
LVL 21

Accepted Solution

by:
flow01 earned 800 total points
ID: 37012049
In the code snippet  the procedure uses the variables defined in your main procedure



declare
  v1 number;

  procedure x
  is
  begin
     insert into table t1 (col1) values (v1);  -- v1 is defined outside the procedure
  end;

begin
   procedure x;
   procedure x;
end;

It works but its nice programming to pass the external variables as arguments to a procedure

declare
  v1 number;

  procedure x(p1 number)
  is
  begin
     insert into table t1 (col1) values (p1); -- p1 is known as an argument within the procedure
  end;

begin
   procedure x(v1);
   procedure x(v1);
end;

however : in your case you need so much arguments that repeating this way gives you a lott of work
 
procedure do_repeated_procedure
is
begin
                        PKG_SIGA_TRANSACCIONES.SP_REGISTRAR_ACUERDO
                               (
                                 VL_TIPOACUERDO, V_NUMERO_TELEFONO, SYSTIMESTAMP, VL_CODACUERDO, NULL, V_NUMERO_CONTRATO, 
                                 V_FECHA_CONTRATO, V_TIPO_CLIENTE, V_NUMERO_PCS, V_PLAZO_ACUERDO, V_PLAN_TARIFARIO, V_CAMPANA, 
                                 NULL, V_COD_EQUIPO, V_SERIE_EQUIPO, V_PRECIO_LISTA, V_PRECIO_VENTA, V_SERIE_SIMCARD, to_number(V_CO_ID),
                                 V_CUSTOMER_ID, V_CUSTOMER_CODE, V_TIPO_DOC_CLIENTE, V_DES_DOC_CLIENTE, N_REASON_ID, NULL, 
                                 V_MESES, NULL, NULL, N_CARGO_FIJO, NULL,
                                 NULL, NULL, NULL, NULL ,NULL,
                                 18, V_ESTADO, 'SAP', V_IMEI,
                                 NULL, V_ICCD, NULL, P_USUARIO, SYSDATE , P_USUARIO , SYSDATE , 
                                 V_CLIENTE, V_PLAN2, V_RAZON_SOCIAL, V_DIRECCION, D_FECHA_ACTIVACION
                               );

                        --registra historial acuerdo
                        INSERT INTO SIGAT_HISTORIAL_ACUERDO
                              (N_IDHISTORIAL,N_IDACUERDO,N_CO_ID,N_CUSTOMER_ID,V_CUSTCODE_CODE,V_COD_EQUIPO,
                               N_PRECIO_LISTA,N_CF_TOT,N_PRECIO_VENTA,V_USR_REGISTRO,D_FECHA_REGISTRO)
                        VALUES(SEQ_SIGA_HISTORIAL_ACUERDO.NEXTVAL, VL_CODACUERDO, V_CO_ID, V_CUSTOMER_ID,
                               NULL, V_COD_EQUIPO, V_PRECIO_LISTA, 0, V_PRECIO_VENTA, P_USUARIO, SYSDATE);
                        
                        -- registro de servicios
                        INSERT INTO SIGAT_ACUERDO_SERVICIO
                             (N_IDACUERDO_SERVIC,
                              N_IDACUERDO,
                              N_IDSERVICIO,
                              V_DESCRIPCION,
                              V_USR_REGISTRO,
                              D_FECHA_REGISTRO)
                           VALUES
                             (SEQ_SIGA_ACUERDO_SERVICIO.NEXTVAL, 
                              VL_CODACUERDO, 
                              nvl(V_SERVICIO_SOLICIT,0), 
                              V_DESC_SERVICIO, 
                              P_USUARIO, 
                              SYSDATE);
end;

begin



--------------------first IF
            IF VL_TIPOACUERDO='8' THEN -- ALTA
                SELECT DISTINCT D_FECHA_DEVOLUCION INTO VL_FECHA_DEVOLUCION
                FROM SIGAT_ZPV_DEVOLUCIONES_TMP A
                WHERE  V_SERIE_EQUIPO = V_SERIE_EQUIPO; 
                
                IF V_FECHA_CONTRATO <= VL_FECHA_DEVOLUCION  THEN

                   VL_MENSAJE:='ERROR: GENERAR ACUERDO SAP - EL ALTA ES UNA DEVOLUCION' + SQLERRM;
                     PKG_SIGA_TRANSACCIONES.SP_REGISTRAR_LOG(VL_MENSAJE,VG_COD_ERROR,V_NUMERO_TELEFONO,VL_TIPOACUERDO,V_CO_ID,
                                         V_CUSTOMER_ID,TO_DATE(SYSDATE,'DD/MM/YYYY'),P_USUARIO,VL_PROCESO,VL_OBJETO);
                                         
                ELSE
                     --registra acuerdo siga
                     SELECT SEQ_SIGA_ACUERDO.NEXTVAL INTO VL_CODACUERDO FROM DUAL;

                     do_repeated_procedure;
                      
                     -- registro de servicios
                     PKG_SIGA_TRANSACCIONES.SP_REGISTRAR_ACUERDO_SERVICIO
                             (
                               VL_CODACUERDO, NVL(V_SERVICIO_SOLICIT,0), V_DESC_SERVICIO, P_USUARIO, V_NUMERO_TELEFONO,
                               V_FECHA_CONTRATO, D_FECHA_ACTIVACION, F_OBTENER_CF(V_PLAN_TARIFARIO),
                               F_OBTENER_DEVIATING(to_number(V_PLAN_TARIFARIO),to_number(V_SERVICIO_SOLICIT), to_number(V_CO_ID)) 
                             );                                    
                     --Si el proceso es exitoso se actualiza la columna FLAG_CARGA a procesado (FLAG_CARGA = '2')
                     UPDATE SIGAT_ZPV_CONTRATO_TMP
                     SET C_FLAG_CARGA = '2'
                     WHERE C_NUMERO_CONTRATO = V_NUMERO_CONTRATO;
                     
                     IF VL_CONT=500 THEN
                         COMMIT;
                         VL_CONT:=0;
                      END IF;
                
                END IF;

---------------second if                
            IF (VL_TIPOACUERDO= '9' OR VL_TIPOACUERDO= '10' OR VL_TIPOACUERDO= '11')  THEN
            
            SELECT 
                   VALOR_N INTO V_NUMERO_DIAS
            FROM 
                   SIGAT_PARAMETRO_DET
            WHERE   
                   N_COD_PARAMETRODET = 59
                   AND N_ESTADO = 1;
                   
            IF V_NUMERO_TELEFONO IS NOT NULL AND V_NUMERO_CONTRATO IS NOT NULL THEN                                
               BEGIN
                 SELECT 
                       A.D_CH_VALIDFROM, A.N_TMCODE, A.N_CO_ID, A.N_CUSTOMER_ID, A.V_CUSTCODE, A.V_PRGCODE, A.N_CH_REASON, A.N_CARGO_FIJO 
                           INTO
                       D_FECHA_ACTIVACION, V_PLAN_TARIFARIO, V_CO_ID, V_CUSTOMER_ID, V_CUSTOMER_CODE, V_TIPO_CLIENTE, N_REASON_ID, N_CARGO_FIJO
                  FROM SIGAT_ZPV_ACTIVACIONES_TMP A
                  WHERE A.V_DN_NUM = V_NUMERO_TELEFONO AND A.N_CO_ID = V_NUMERO_CONTRATO;
                  
                  VL_MESES := FLOOR (MONTHS_BETWEEN (V_FECHA_CONTRATO, D_FECHA_ACTIVACION));
                  VL_DIAS := D_FECHA_ACTIVACION - ADD_MONTHS(V_FECHA_CONTRATO, VL_MESES);
                  IF (VL_DIAS > V_NUMERO_DIAS) 
                        OR (D_FECHA_ACTIVACION IS NULL AND V_PLAN_TARIFARIO IS NULL AND V_CO_ID IS NULL 
                            AND V_CUSTOMER_ID IS NULL AND V_CUSTOMER_CODE IS NULL AND V_TIPO_CLIENTE IS NULL 
                            AND N_REASON_ID IS NULL AND N_CARGO_FIJO IS NULL) THEN
                            
                       VL_MENSAJE:='ERROR: GENERAR ACUERDO SAP - EL NUMERO DE DIAS ENTRE LA FECHA DE VENTA Y ACTIVACION ES MAYOR AL VALOR CONFIGURADO' + SQLERRM;
                       PKG_SIGA_TRANSACCIONES.SP_REGISTRAR_LOG(VL_MENSAJE,VG_COD_ERROR,V_NUMERO_TELEFONO,VL_TIPOACUERDO,V_CO_ID,
                                           V_CUSTOMER_ID,TO_DATE(SYSDATE,'DD/MM/YYYY'),P_USUARIO,VL_PROCESO,VL_OBJETO);

                       UPDATE SIGAT_ZPV_CONTRATO_TMP
                       SET C_FLAG_CARGA = '1'
                       WHERE C_NUMERO_CONTRATO = V_NUMERO_CONTRATO; 
                       
                  ELSE
                        --registra acuerdo siga
                        SELECT SEQ_SIGA_ACUERDO.NEXTVAL INTO VL_CODACUERDO FROM DUAL;
                        
                        do_repeated_procedure;

                        
                        -- registro de servicios
                        INSERT INTO SIGAT_ACUERDO_SERVICIO
                             (N_IDACUERDO_SERVIC,
                              N_IDACUERDO,
                              N_IDSERVICIO,
                              V_DESCRIPCION,
                              V_USR_REGISTRO,
                              D_FECHA_REGISTRO)
                           VALUES
                             (SEQ_SIGA_ACUERDO_SERVICIO.NEXTVAL, 
                              VL_CODACUERDO, 
                              nvl(V_SERVICIO_SOLICIT,0), 
                              V_DESC_SERVICIO, 
                              P_USUARIO, 
                              SYSDATE);
                                      
                        --Si el proceso es exitoso se actualiza la columna FLAG_CARGA a procesado (FLAG_CARGA = '2')
                        UPDATE SIGAT_ZPV_CONTRATO_TMP
                        SET C_FLAG_CARGA = '2'
                        WHERE C_NUMERO_CONTRATO = V_NUMERO_CONTRATO;
                 END IF;                                     
                  IF VL_CONT=500 THEN
                     COMMIT;
                     VL_CONT:=0;
                  END IF;
               EXCEPTION                                 
                  WHEN NO_DATA_FOUND THEN
                  --Verificar que tenga su correspodiente Activación                   
                    VL_MENSAJE:='ERROR: GENERAR ACUERDO SAP - NO ESTÁ UBICANDO SU CORRESPONDIENTE ACTIVACIÓN' + SQLERRM;
                    PKG_SIGA_TRANSACCIONES.SP_REGISTRAR_LOG(VL_MENSAJE,VG_COD_ERROR,V_NUMERO_TELEFONO,VL_TIPOACUERDO,V_CO_ID,
                                           V_CUSTOMER_ID,TO_DATE(SYSDATE,'DD/MM/YYYY'),P_USUARIO,VL_PROCESO,VL_OBJETO);

                    UPDATE SIGAT_ZPV_CONTRATO_TMP
                    SET C_FLAG_CARGA = '1'
                    WHERE C_NUMERO_CONTRATO = V_NUMERO_CONTRATO;     
               END;
                                  
             END IF;
            
            END IF;

       END IF;

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses
Course of the Month19 days, 2 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question