Cómo utilizar Stored Procedures

La importación de datos puede utilizar stored-procedure, para procesos más complejos o que necesitan de desempeño. Deben considerarse las siguientes observaciones para crear la stored-procedure.

Nombre de la procedure:

El nombre de la stored-procedure debe tener el formato sp_xxxxxx, donde:

• sp_ es el prefijo constante de identificación
• xxxxxx, cualquier nombre válido en el SGDB

Ejemplos:

SP_CALCSALDO

SP PREP_PRODUTO

SP_MONTAFLUXODECAIXA

Parámetros:

Si hubieran parámetros para la ejecución de la stored-procedure, los parámetros de entrada deben ser precedidos de IN_ y los parámetros de retorno, deben ser precedidos de OUT_.

• Para el SIGADW es obligatorio el retorno de por lo menos un parámetro. Vea ejemplos al final de este texto.

Tablas

• La stored-procedure utilizará una tabla, convencionalmente llamada tabla de transferencia, cuyos datos son volátiles, es decir, a cada ejecución de la stored-procedure, sus datos deben retirarse, para después ponerse a disposición para el SIGADW.
• La tabla de transferencia y la stored-procedure deben crearse previamente por herramientas disponíbles del SGDB, p.e., el iSQL Plus para el Oracle, Query Analyzer para el MS-SQL, o cualquier otra que el usuario desee, siempre y cuando tenga acceso a la base de datos del SigaDW o a la base de datos origen.
• Para el nombre de la tabla de transferencia, no hay estándar, sinembargo, recomendamos que se utilice el mismo nombre de la stored-procedure, sin el prefijo sp_.

Cómo ejecutar la stored-procedure, en el SIGADW:

• En el evento Al iniciar de la fuente de datos, soli citará la ejecución del stored-procedure, a través de una de las funciones mostradas en el ítem Rutinas para ejecución de strored-procedures.
• El bloque de código vinculado al evento Al iniciar, debe retornar “.T.”, indicando que el proceso de importación debe continuar o “.F.”, para indicar que el proceso debe interrumpirse.

Cómo acceder a los datos de la tabla de transferencia:

• En el campo SQL (datos), informe el comando SQL para leer la tabla de transferencia, como si fuese una tabla tradicional, p.e. select * from CALCPEDIDO.
• En el campo SQL (estructura), informe el comando SQL para leer la estructura de la tabla de transferencia, p.e. select top 1 * from PREPIMP.

Rutinas para ejecución de stored-procedures:

• Stored-procedure almacenada en la base de datos del SIGADW DWEXECSP(<cNomeSP> , <lista de parámetros>[,...]) -> <array>

<cNomeSP> - String que identifica la stored-procedure que se ejecutará.

<lista de parámetros> - Expresión, parámetros que se pasarán a la stored-procedure, es obligatorio por lo menos un parámetro de retorno.

<array> - Array de strings, con los valores atribuidos en los parámetros de output.

• Stored-procedure almacenada en la base de datos de origen DWREMOTESP(<cNomeSP>, <lista de parámetros>[,...]) -> <array>

<cNomeSP> - String que identifica el stored-procedure que se ejecutará, sin el sufijo de la empresa.

<lista de parâmetros> - Expresión, parámetros que se pasarán al stored-procedure, es obligatorio por lo menos un parámetro de retorno.

<array> - Array de strings, con los valores atribuidos en los parámetros de output

Ejemplos:

MS-SQL

create SP_CALCPEDIDO ( @IN_NUMPED varchar(8), @OUT_Ret varchar(1) output) as 
select @OUT_Ret = '1'; -- indica que comenzó la SP ; 
delete from CALCPEDIDO; -- limpia la tabla de transferencia 
if @@ERROR = 0 // fue posible efectuar el borrado 
begin 
 // aquí se consigna el código necesario, 
 // para alimentar la tabla CALCPEDIDO 
 // Para cada punto/comando donde hay la necesidad 
 // de verificar la ocurrencia de error, basta probar 
 // la variable “reservada” @@ERROR. Vea el help del 
 // MS-SQL para ejemplos más completos 
end else 
begin 
 select @OUT_Ret = '9'; -- indica que terminó con éxito 
end

Oracle

CREATE OR REPLACE PROCEDURE SP_CALCPEDIDO (IN_NUMPED IN varchar2, OUT_RET OUT varchar2) is begin 
OUT_RET := '1'; -- indica que comenzó la SP 
 truncate table CALCPEDIDO; -- limpia la tabla de transferencia 
 EXCEPTION -- En caso de error en la ejecución del procedure 
 // aquí se consigna el código necesario, 
 // para alimentar la tabla CALCPEDIDO 
 // Para tratamiento de errores, utilice la cláusula Excpetion 
 // Vea el help del Oracle para ejemplos más completos 
 OUT_Ret = '9'; -- indica que terminó con éxito 
WHEN OTHERS then -– tratar todo y cualquier error 
BEGIN 
 OUT_Ret = '2'; -- indica que ocurrió un error 
END 
end SP_CALCPEDIDO_01;

Código Adv/PL del evento Al iniciar

dw_Ret := DWEXECSP(\"SP_CALCPEDIDO\",”12345678”,”OUT_RET”) 
if len(dw_Ret) == 0 // no existe la SP o hay error en esta 
 DWLOG(“SP_CALCPEDIDO no disponible”) // log falla 
 return .f. // interrumpir 
elseif dw_Ret[1] == '1'// iniciada, pero se interrumpió por error grave 
 DWLOG(“SP_CALCPEDIDO finalizada con error”) // log falla 
 return .f. // interrumpir 
elseif dw_ret[2] == ‘2’ // indica que ocurrió un error controlado 
 DWLog(“SP_CALCPEIDOD finalizó con error del tipo ‘2’”) 
 return .f. 
Endif 
return .t. // continúa la importación

 

Se recomienda que las stored-procedures tengan comandos de captura y tratamiento de error para un buen control de la ejecución y para facilitar en la solución de problemas en el proceso.