Oracle 10G -Apuntes #1.

Creando paquetes de Base de Datos.

Un día de estos en el trabajo me pidieron que creara un paquete a partir de una ejcución de PL/SQL que se generaba mensualmente, dicho paquete se llamaría desde una forma del forms Developer 6i, invocando al procedimiento que se fuera a utilizar dentro del paquete.

Entonces partiendo que se realizaban 5 procesos se creo un Store Procedure para cada proceso. Luego se tenia que evaluar si en una tabla habían datos para un módulo (cada módulo tenía su código único y se evaluaba en cada uno de los SP) no realizaba el procedimiento que le correspondía y pasaba al siguiente, de la misma forma para el resto.

En sí los requerimientos:

1. Construir un store procedure para cada uno de los módulos, hagamos de cuenta y caso que cada módulo tenia un correlativo del 01-Módulo 01, 02-Módulo 02... hasta 05-Módulo 05.
2. Construir un paquete que almacene a cada Store Procedure.
3. Evaluar si existen datos para cada módulo teniendo en cuenta la fecha del proceso, el correlativo único, la empresa y el módulo obviamente.
4. Capturar las Excepciones si existen.

1. Construimos el Store Procedure para cada módulo:

PROCEDURE MODULO01(

recibeParametro1 IN VARCHAR2,

recibeParametro2 IN NUMBER,

recibeParametro3 IN DATE,

codigoError OUT VARCHAR2)

CURSOR CModulo1 (recibeParametro1 IN VARCHAR2, recibePrametro3 DATE)

IS

SELECT * FROM TablaOrigenDatos

WHERE columna1 = recibeParametro1

AND columna3 =  recibeParametro3;

BEGIN

FOR i IN cModulo1(recibeParametro1, recibeParametro3)

LOOP

variable 1 := i.algo;

DBMS_OUTPUT.put_line ('valor de variable 1 en: ' || i || ‘ es: ’ ||variable1);

END LOOP;

COMMIT;

END;

…

De la misma forma creamos el resto de SP

2. Creación de paquete.

2.1 Creamos el Spec:

CREATE OR REPLACE PACKAGE miPaquete

IS

/*Este cursor hara los conteos para cada uno de los Procedures ver requerimiento 3, se mandara el parametro de módulo desde la forma , para que el conteo sea único para cada modulo.*/

CURSOR CvalidaRegistros(

  recibeParametro1  IN   VARCHAR,

  recibeParametro2  IN   VARCHAR,

recibeParametro3  IN   VARCHAR2,

  recibeParametro4  IN   DATE

  )

  IS

  SELECT COUNT (*)

    FROM tablaDeConteo

   WHERE columna1 = recibeParametro1

     AND columna2 = recibeParametro2

     AND columna3 >= recibeParametro3

     AND columna4 = recibeParametro4

     AND ROWNUM = 1;

--Declaracion de Variables Globales, se utilizarán en todos los SP

variable1         NUMBER       := 0;

variable2         NUMBER       := 0;

…

variableN NUMBER :=0;

--Especificacion de cada SP

PROCEDURE MODULO01(

recibeParametro1 IN VARCHAR2,

recibeParametro2 IN NUMBER,

recibeParametro3 IN DATE,

codigoError OUT VARCHAR2);

PROCEDURE MODULO02(

recibeParametro1 IN VARCHAR2,

recibeParametro2 IN NUMBER,

recibeParametro3 IN DATE,

codigoError OUT VARCHAR2);

--… El resto de SP

PROCEDURE MODULO05(

recibeParametro1 IN VARCHAR2,

recibeParametro2 IN NUMBER,

recibeParametro3 IN DATE,

codigoError OUT VARCHAR2);

END;

/

2.1 Creamos el Body:

CREATE OR REPLACE PACKAGE BODY miPaquete

AS

--Ejecuta modulo01

PROCEDURE MODULO01(

recibeParametro1 IN VARCHAR2,

recibeParametro2 IN NUMBER,

recibeParametro3 IN DATE,

codigoError OUT VARCHAR2)

CURSOR CModulo1 (recibeParametro1 IN VARCHAR2, recibePrametro3 DATE)

IS

SELECT * FROM TablaOrigenDatos

WHERE columna1 = recibeParametro1

AND columna3 =  recibeParametro3;

BEGIN

FOR i IN cModulo1(recibeParametro1, recibeParametro3)

LOOP

variable 1 := i.algo;

DBMS_OUTPUT.put_line ('valor de variable 1 en: ' || i || ‘ es: ’ ||variable1);

END LOOP;

COMMIT;

END;

--...el resto de modulos.

4.  Las excepciones se capturarán desde la forma, la llamada será desde la forma de la siguiente manera:

--Llamada a SP 01 desde Trigger en la forma, por ejemlo yo lo puse en un When-Button-Pressed.

DECLARE

vParametro1 NUMBER(5) := valorquerecibe; /*probablemente desde un campo en el formulario*/

vParametro2 DATE := SYSDATE; */Por ejemplo el dia de ahora, sino se le manda una fecha en un campo /*

BEGIN

miPaquete.Modulo01(vParametro1,

                                           '01',

parametroEnvio2,

                                            vError

                                           );

--Llamada a SP 02 desde Trigger en la forma.

miPaquete.Modulo01(vParametro1,

                                           '02',

parametroEnvio2,

                                            vError

                                           );

--…Al fin de completar todos los llamados

EXCEPTION

  WHEN OTHERS

  THEN

MESSAGE(‘Error: ’ || SQLERRM);

RAISE form_trigger_failure;

END;

NOTAS: Podria ser que los nombres de los parámetros y variables no coincidan en nombre y orden a como se ha vendio colocando acá pero habría que asociarlas a manera que sean compatibles tanto en el valor que reciben como en el tipo y tamaño que deberán recibir también, esto implica que si se invoca un sp desde la forma:

--invocacion, variables de envio

DECLARE

var1 varchar2 (10) := ‘String’;

var2 number := ‘01’;

var3 DATE := SYSDATE;

BEGIN

paquete.procedure(var1, var2, var3);

END;

--recepcion de parametros

procedure(para1 IN VARCHAR2,

para2 IN NUMBER,

para3 IN DATE)

IS

…

Si se dan cuenta las variables de envio coinciden en tipo con los parametros de recepcion y en orden el var1 tipo varchar2 es enviado al para1 de tipo varchar2 tambien, el var2 es enviado al para2 de tipo number tambien y lo mismo para la var3 que es enviado como date para el para3. Esto evitará errores de Compilación.