PL-SQL

Identificadores

Un identificador es un nombre que se le pone a un objeto que interviene en un programa, que puede ser variable, constante, procedimientos, excepciones, cursores… Debe tener un máximo de 30 caracteres que empiece siempre por una letra, y puede contener letras, números, los símbolos $, #, _, y mayúsculas y minúsculas indiferentemente. Los identificadores no pueden ser palabras reservadas (SELECT, INSERT, DELETE, UPDATE, DROP).

Operadores

Operador de asignación

:= (dos puntos + igual)

Operadores aritméticos + (suma)

- (resta)

* (multiplicación)

/ (división)

** (exponente)

Operadores relacionales o de comparación

= (igual a)

<>, != (distinto de)

< (menor que)

> (mayor que)

>= (mayor o igual a)

<= (menor o igual a)

Operador de concatenación || Comentarios /* comentario de dos o más líneas */

– comentario de una línea

Variables

Las variables son nombres para procesar los elementos de los datos. Declaración:

Nombre_variable tipo [NOT NULL] [:= valor | DEFAULT valor]

:= y DEFAULT son lo mismo. Si ponemos NOT NULL es obligatorio inicializar la variable.

Ejemplos:

num_dep NUMBER(2) NOT NULL :=20

num_emple VARCHAR2(15) DEFAULT ‘Pedro’

También se puede definir una variable a partir de un campo mediante los atributos %TYPE y %ROWTYPE, con esto damos el tipo y longitud a la variable de otra variable u objeto ya definido.

%TYPE es la que se utiliza normalmente, %ROWTYPE es para claves de registro. El NOT NULL y el valor inicial no se heredan, sólo el tipo de dato y longitud de ese dato.

Por ejemplo:

num_dep emple.dept_no%TYPE

Constantes

Las constantes son como las variables pero no puede modificarse su valor. Se declaran de la siguiente manera:

nombre_constante CONSTANT tipo_de_dato := valor

Por ejemplo, el IVA es un valor fijo, y para declararlo lo haríamos de la siguiente manera:

Imp_iva constant number(2,2) := 12,5

Funciones integradas de PL/SQL

PL/SQL tiene un gran número de funciones incorporadas, sumamente útiles. A continuación vamos a ver algunas de las más utilizadas.

SYSDATE

    Devuelve la fecha del sistema:

NVL

Devuelve el valor recibido como parámetro en el caso de que expresión sea NULL,o expresión en caso contrario.

El siguiente ejemplo devuelve 0 si el precio es nulo, y el precio cuando está informado:

DECODE

 Decode proporciona la funcionalidad de una sentencia de control de flujo if-elseif-else.

Esta función evalúa una expresión “<expr>”, si se cumple la primera condición “<cond1>” devuelve el valor1 “<val1>”, en caso contrario evalúa la siguiente condición y así hasta que una de las condiciones se cumpla. Si no se cumple ninguna condición se devuelve el valor por defecto.

 Es muy común escribir la función DECODE identada como si se tratase de un bloque IF.

TO_DATE

Convierte una expresión al tipo fecha. El parámetro opcional formato indica el formato de entrada de la expresión no el de salida.

En este ejemplo convertimos la expresion ’01/12/2006′ de tipo CHAR a una fecha (tipo DATE). Con el parámetro formato le indicamos que la fecha está escrita como día-mes-año para que devuelve el uno de diciembre y no el doce de enero.

    Este otro ejemplo muestra la conversión con formato de día y hora.

TO_CHAR

Convierte una expresión al tipo CHAR. El parámetro opcional formato indica el formato de salida de la expresión.

TO_NUMBER

Convierte una expresion alfanumérica en numerica. Opcionalmente podemos especificar el formato de salida.

TRUNC

Trunca una fecha o número.

Si el parámetro recibido es una fecha  elimina las horas, minutos y segundos de la misma.

Si el parámetro es un número devuelve la parte entera.

LENGTH

    Devuelve la longitud de un tipo CHAR.

INSTR

Busca una cadena de caracteres dentro de otra. Devuelve la posicion de la ocurrencia de la cadena buscada.

Su sintaxis es la siguiente:

REPLACE

Reemplaza un texto por otro en un expresion de busqueda.

El siguiente ejemplo reemplaza la palabra ‘HOLA’ por ‘VAYA’ en la cadena ‘HOLA MUNDO’.

SUBSTR

Obtiene una parte de una expresion, desde una posición de inicio hasta una determinada longitud.

UPPER

Convierte una expresion alfanumerica a mayúsculas.

LOWER

Convierte una expresion alfanumerica a minúsculas.

ROWIDTOCHAR

Convierte un ROWID a tipo caracter.

RPAD

Añade N veces una determinada cadena de caracteres a la derecha una expresión. Muy util para generar ficheros de texto de ancho fijo.

El siguiente ejemplo añade puntos a la expresion ‘Hola mundo’ hasta alcanzar una longitud de 50 caracteres.

LPAD

Añade N veces una determinada cadena de caracteres a la izquierda de una expresión. Muy util para generar ficheros de texto de ancho fijo.

El siguiente ejemplo añade puntos a la expresion ‘Hola mundo’ hasta alcanzar una longitud de 50 caracteres.

RTRIM

Elimina los espacios en blanco a la derecha de una expresion

LTRIM

     Elimina los espacios en blanco a la izquierda de una expresion

TRIM

Elimina los espacios en blanco a la izquierda y derecha de una expresion

MOD

Devuelve el resto de la división entera entre dos números.

Bloque PL/SQL

Bloque es la unidad de estructura básica en los programas PL/SQL. Supone una mejora en el rendimiento, pues se envían los bloques completos al servidor para ser procesados en lugar de enviar cada secuencia SQL.

Partes de un bloque:

• Zona de declaraciones: zona opcional. Se declaran los objetos locales (variables, constantes…).
  
• Zona de instrucciones: zona obligatoria.
  
• Zona de tratamiento de excepciones: zona opcional. Se tratan excepciones en el programa.
  

Forma de crear un bloque:

[ DECLARE | IS / AS ]

<declaraciones>

BEGIN

<instrucciones>

[ EXCEPTION ]

<tratamiento de excepciones>

END;

/

La barra “/” siempre se pone al final para ejecutar el bloque.

Excepciones en PL/SQL

    En PL/SQL una advertencia o condición de error es llamada una excepción.

    Las excepciones se controlan dentro de su propio bloque.La estructura de bloque de una excepción se muestra a continuación.

Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias.

El siguiente ejemplo muestra un bloque de excepciones que captura las excepciones NO_DATA_FOUND y ZERO_DIVIDE. Cualquier otra excepcion será capturada en el bloque WHEN OTHERS THEN.

Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior.

Si existe un bloque de excepcion apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepcion se ejecutará el bloque de excepcion WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones.

Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos: ZERO_DIVIDE y STORAGE_ERROR.

Las excepciones definidas por el usuario deben ser alcanzadas explícitamente utilizando la sentencia RAISE.

Con las excepciones se pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores de forma separada de la lógica del negocio.

Excepciones predefinidas

PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales.

Las excepciones predefinidas no necesitan ser declaradas. Simplemente se utilizan cuando estas son lanzadas por algún error determinado.

La siguiente es la lista de las excepciones predeterminadas por PL/SQL y una breve descripción de cuándo son accionadas:

Excepciones definidas por el usuario

PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE.

Las excepciones deben ser declaradas en el segmento DECLARE de un bloque, subprograma o paquete. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones.

Reglas de Alcance

Una excepcion es válida dentro de su ambito de alcance, es decir el bloque o programa donde ha sido declarada. Las excepciones predefinidas son siempre válidas.

Como las variables, una excepción declarada en un bloque es local a ese bloque y global a todos los sub-bloques que comprende.

La sentencia RAISE

La sentencia RAISE permite lanzar una excepción en forma explícita. Es posible utilizar esta sentencia en cualquier lugar que se encuentre dentro del alcance de la excepción.

Con la sentencia RAISE podemos lanzar una excepción definida por el usuario o predefinida, siendo el comportamiento habitual lanzar excepciones definidas por el usuario.

Recordar la existencia de la excepción OTHERS, que simboliza cualquier condición de excepción que no ha sido declarada. Se utiliza comúnmente para controlar cualquier tipo de error que no ha sido previsto. En ese caso, es común observar la sentencia ROLLBACK en el grupo de sentencias de la excepción o alguna de las funciones SQLCODE – SQLERRM, que se detallan en el próximo punto.

Uso de SQLCODE y SQLERRM

Al manejar una excepción es posible usar las funciones predefinidas SQLCode y SQLERRM para aclarar al usuario la situación de error acontecida.

SQLcode devuelve el número del error de Oracle y un 0 (cero) en caso de exito al ejecutarse una sentencia SQL.

Por otra parte, SQLERRM devuelve el correspondiente mensaje de error.

Estas funciones son muy útiles cuando se utilizan en el bloque de excepciones, para aclarar el significado de la excepción OTHERS.

Estas funciones no pueden ser utilizadas directamente en una sentencia SQL, pero sí se puede asignar su valor a alguna variable de programa y luego usar esta última en alguna sentencia.

También es posible entregarle a la función SQLERRM un número negativo que represente un error de Oracle y ésta devolverá el mensaje asociado.

Excepciones personalizadas en PL/SQL

RAISE_APPLICATION_ERROR

En ocasiones queremos enviar un mensaje de error personalizado al producirse una excepción PL/SQL.
Para ello es necesario utilizar la instruccion RAISE_APPLICATION_ERROR;

La sintaxis general es la siguiente:

Siendo:

• error_num es un entero negativo comprendido entre -20001 y -20999
  
• mensaje la descripcion del error
  

  DECLARE v_div NUMBER; BEGIN SELECT 1/0 INTO v_div FROM DUAL; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,‘No se puede dividir por cero’); END;

   

Cursores en PL/SQL

Introducción a cursores PL/SQL

PL/SQL utiliza cursores para gestionar las instrucciones SELECT. Un cursor es un conjunto de registros devuelto por una instrucción SQL. Técnicamente los cursores son fragmentos de memoria que reservados para procesar los resultados de una consulta SELECT.

Podemos distinguir dos tipos de cursores:

• Cursores implicitos. Este tipo de cursores se utiliza para operaciones SELECT INTO. Se usan cuando la consulta devuelve un único registro.
  
• Cursores explicitos. Son los cursores que son declarados y controlados por el programador. Se utilizan cuando la consulta devuelve un conjunto de registros. Ocasionalmente también se utilizan en consultas que devuelven un único registro por razones de eficiencia. Son más rápidos.
  

Un cursor se define como cualquier otra variable de PL/SQL y debe nombrarse de acuerdo a los mismos convenios que cualquier otra variable. Los cursores implicitos no necesitan declaración.

El siguiente ejemplo declara un cursor explicito:

Para procesar instrucciones SELECT que devuelvan más de una fila, son necesarios cursores explicitos combinados con un estructura de bloque.

Un cursor admite el uso de parámetros. Los parámetros deben declararse junto con el cursor.

El siguiente ejemplo muestra la declaracion de un cursor con un parámetro, identificado por p_continente.

El siguiente diagrama representa como se procesa una instrucción SQL a través de un cursor.

Fases para procesar una instrucción SQL

Cursores Implicitos en PL/SQL

Cursores Explicitos en PL/SQL

 

 

Cursores de actualización en PL/SQL

Declaración y utiización de cursores de actualización.

Los cursores de actualización se declarán igual que los cursores explicitos, añadieno FOR UPDATE al final de la sentencia select.

Para actualizar los datos del cursor hay que ejecutar una sentencia UPDATE especificando la clausula WHERE CURRENT OF <cursor_name>.

    El siguiente ejemplo muestra el uso de un cursor de actualización:

Cuando trabajamos con cursores de actualización debemos tener en cuenta las siguientes consideraciones:

Los cursores de actualización generan bloqueos en la base de datos.

Bloques Anonimos (sin nombre)

Siempre comienza con DECLARE o directamente con BEGIN.

Ejemplo 1:

BEGIN

DBMS_OUTPUT.PUT_LINE (‘Hola’);

END;

/

DBMS_OUTPUT es un depurador de Oracle que sirve para visualizar cualquier cosa, pero antes lo debemos tener activado:

SET SERVEROUTPUT ON;

Ejemplo 2:

DECLARE

v_precio number;

BEGIN

select pvp into v_precio

from tarticulos

where codigo=100;

dbms_output.put_line (v_precio);

END;

/

Ejemplo 3:

El siguiente bloque anónimo nos muestra la fecha actual con el formato “Martes, 18 de Marzo de 1998, a las 13:04:55″.

DECLARE

fecha date;

BEGIN

select sysdate into fecha from dual;

dbms_output.put_line (to_char(sysdate,

‘day”, “dd” de “month” de “yyyy”, a las “hh24:mi:ss’));

END;

/

Subprogramas

Se pueden almacenar en la base de datos.

Existen dos tipos de subprogramas:

Procedimientos

Los procedimientos tienen la utilidad de fomentar la reutilización de programas que se usan comúnmente. Una vez compilado, queda almacenado en la base de datos y puede ser utilizado por múltiples aplicaciones.

La sintaxis es la siguiente

CREATE [OR REPLACE] PROCEDURE nombre_procedimiento

[nombre_parametro modo tipodatos_parametro ]

IS | AS

bloque de código

Donde “modo” puede contener los valores IN, OUT, IN OUT. Por defecto tiene el valor IN si no se pone nada. IN indica que el parámetro es de entrada y no se podrá modificar. OUT indica que el parámetro es de salida con lo que el procedimiento devolverá un valor en él. IN OUT indica que el parámetro es de entrada/salida. Con lo que al llamar al procedimiento se le dará un valor que luego podrá ser modificado por el procedimiento y devolver este nuevo valor.

“tipodatos_parametro indica el tipo de datos que tendrá el parámetro según lo indicado en Tipos de datos Oracle/PLSQL

Para borrar un procedimiento almacenado de la base de datos

DROP PROCEDURE nombre_procedimiento

Para utilizar un procedimiento almacenado de la base de datos

Simplemente se lo llama desde un bloque anónimo (desde la línea de comandos), previamente habiendo inicializado el/los parametro/s (en caso que existan).

DECLARE

nombre_parametro tipodatos_parametro;

BEGIN

nombre_parametro tipodatos_parametro := valor_de_inicializacion;

nombre_procedimiento (nombre_parametro => nombre_parametro);

END;

/

Funciones

Una función es un bloque de código PL/SQL que tiene las mismas características que un procedimiento almacenado. La diferencia estriba que una función devuelve un valor al retornar. Al devolver un valor puede ser llamada como parte de una expresión.

La sintaxis sería

CREATE [OR REPLACE] FUNCTION nombre_función

[nombre_parámetro modo tipodatos_parametro ]

RETURN tipodatos_retorno IS | AS

bloque de código

Donde “modo” puede contener los valores IN, OUT, IN OUT. Por defecto tiene el valor IN si no se pone nada. IN indica que el parámetro es de entrada y no se podrá modificar. OUT indica que el parámetro es de salida con lo que el procedimiento devolverá un valor en él. IN OUT indica que el parámetro es de entrada/salida. Con lo que al llamar al procedimiento se le dará un valor que luego podrá ser modificado por el procedimiento y devolver este nuevo valor. Sin embargo, en este caso solo tendría sentido( por el concepto de función en sí mismo) declarar parámetros del tipo IN y devolver el valor como retorno de la función.

“tipodatos_parametro” y “tipodatos_retorno” indican el tipo de datos que tendrá el parámetro y el valor de retorno de la función respectivamente según lo indicado en Tipos de datos Oracle/PLSQL

Para borrar una función de la base de datos

DROP FUNCTION nombre_función

Los procedimientos y funciones se pueden agrupar en unas estructuras llamadas Paquetes

Paquetes

Los paquetes se utilizan para agrupar procedimiento y funciones,

Ejemplo de declaración de un Paquete de base de datos:

CREATE [OR REPLACE] PACKAGE package_name

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS}

[PRAGMA SERIALLY_REUSABLE;]

[collection_type_definition ...]

[record_type_definition ...]

[subtype_definition ...]

[collection_declaration ...]

[constant_declaration ...]

[exception_declaration ...]

[object_declaration ...]

[record_declaration ...]

[variable_declaration ...]

[cursor_spec ...]

[function_spec ...]

[procedure_spec ...]

[call_spec ...]

[PRAGMA RESTRICT_REFERENCES(assertions) ...]

END [package_name];

[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

[PRAGMA SERIALLY_REUSABLE;]

[collection_type_definition ...]

[record_type_definition ...]

[subtype_definition ...]

[collection_declaration ...]

[constant_declaration ...]

[exception_declaration ...]

[object_declaration ...]

[record_declaration ...]

[variable_declaration ...]

[cursor_body ...]

[function_spec ...]

[procedure_spec ...]

[call_spec ...]

[BEGIN

sequence_of_statements]

END [package_name];]

Para Borrar un paquete:

DROP PACKAGE nombre_paquEte;

DROP PACKAGE BODY nombre_paquEte; –Elimina el cuerpo del paquete

El uso de OR REPLACE permite sobreescribir un paquete existente. Si se omite, y el paquete existe, se producirá, un error.

Disparadores (Triggers)

Declaración de triggers

Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinada instrucción  SQL (una operación DML: INSERT, UPDATE o DELETE) sobre dicha tabla.

La sintaxis para crear un trigger es la siguiente:

El uso de OR REPLACE permite sobreescribir un trigger existente. Si se omite, y el trigger existe, se producirá, un error.

Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de la operación.   El modificador BEFORE AFTER indica que el trigger se ejecutará antes o despues de ejecutarse la sentencia SQL definida por DELETE INSERT  UPDATE. Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista.

El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.

La siguiente tabla resume los contenidos anteriores.

La cláusula WHEN sólo es válida para los disparadores con nivel de fila.

Dentro del ambito de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando  o borrando.

El siguiente ejemplo muestra un trigger que inserta un registro en la tabla PRECIOS_PRODUCTOS cada vez que insertamos un nuevo registro en la tabla PRODUTOS:

El trigger se ejecutará cuando sobre la tabla PRODUCTOS se ejecute una sentencia INSERT.

Orden de ejecución de los triggers

Una misma tabla puede tener varios triggers. En tal caso es necesario conocer el orden en el que se van a ejecutar.

Los disparadores se activan al ejecutarse la sentencia SQL.

• Si existe, se ejecuta el disparador de tipo BEFORE (disparador previo) con nivel de orden.
  
• Para cada fila a la que afecte la orden:
  
• Se ejecuta si existe, el disparador de tipo BEFORE con nivel de fila.
  
• Se ejecuta la propia orden.
  
• Se ejecuta si existe, el disparador de tipo AFTER (disparador posterior) con nivel de fila.
  
• Se ejecuta, si existe, el disparador de tipo AFTER con nivel de orden.
  

Restricciones de los triggers

El cuerpo de un trigger es un bloque PL/SQL. Cualquier orden que sea legal en un bloque PL/SQL, es legal en el cuerpo de un disparador, con las siguientes restricciones:

Un disparador no puede emitir ninguna orden de control de transacciones: COMMIT, ROLLBACK o SAVEPOINT. El disparador se activa como parte de la ejecución de la orden que provocó el disparo, y forma parte de la misma transacción que dicha orden. Cuando la orden que provoca el disparo es confirmada o cancelada, se confirma o cancela también el trabajo realizado por el disparador.

Por razones idénticas, ningún procedimiento o función llamado por el disparador puede emitir órdenes de control de transacciones.

El cuerpo del disparador no puede contener ninguna declaración de variables LONG o LONG RAW

Utilización de :OLD y :NEW

Dentro del ambito de un trigger disponemos de las variables OLD y NEW . Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas, son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y despues(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando  o borrando.

La siguiente tabla muestra los valores de OLD y NEW.

Los registros OLD y NEW son sólo válidos dentro de los disparadores con nivel de fila.

Podemos usar OLD y NEW como cualquier otra variable PL/SQL.

Utilización de predicados de los triggers: INSERTING, UPDATING y DELETING

Dentro de un disparador en el que se disparan distintos tipos de órdenes DML (INSERT, UPDATE y DELETE), hay tres funciones booleanas que pueden emplearse para determinar de qué operación se trata. Estos predicados son INSERTING, UPDATING y DELETING.

Su comportamiento es el siguiente:

Para eliminar un trigger:

DROP TRIGGER nombre_trigger

Estructuras de control en PL-SQL

Sentencia IF

En PL/SQL solo disponemos de la estructura condicional IF. Su sintaxis se muestra a continuación:

   Un aspecto a tener en cuenta es que la instrucción condicional anidada es ELSIF
y no “ELSEIF”.

Sentencia GOTO

    PL/SQL dispone de la sentencia GOTO. La sentencia GOTO desvia el flujo de ejecució a una determinada etiqueta.

    En PL/SQL las etiquetas se indican del siguiente modo: << etiqueta >>

    El siguiente ejemplo ilustra el uso de GOTO.

Sentencia Case

En PL/SQL solo disponemos de la estructura condicional Case. Su sintaxis se muestra a continuación:

    El siguiente ejemplo ilustra el uso de CASE.

Bucles

   En PL/SQL tenemos a nuestra disposición los siguientes iteradores o bucles:

• LOOP
  
• WHILE
  
• FOR
  

El bucle LOOP, se repite tantas veces como sea necesario hasta que se fuerza su salida con la instrucción EXIT. Su sintaxis es la siguiente

   El bucle WHILE, se repite mientras que se cumpla expresion.

El bucle FOR, se repite tanta veces como le indiquemos en los identificadores inicio y final.

En el caso de especificar REVERSE  el bucle se recorre en sentido inverso.

Registros PL/SQL

Cuando vimos los tipos de datos, omitimos intencionadamente ciertos tipos de datos.    Estos son:

• Registros
  
• Tablas de PL
  
• VARRAY
  

Declaración de un registro.

Un registnslpwdro es una estructura de datos en PL/SQL, almacenados en campos, cada uno de los cuales tiene su propio nombre y tipo y que se tratan como una sola unidad lógica.

Los campos de un registro pueden ser inicializados y pueden ser definidos como NOT NULL. Aquellos campos que no sean inicializados explícitamente, se inicializarán a NULL.

La sintaxis general es la siguiente:

El siguiente ejemplo crea un tipo PAIS, que tiene como campos el código, el nombre y el continente.

Los registros son un tipo de datos, por lo que podremos declarar variables de dicho tipo de datos.

Los registros pueden estar anidados. Es decir, un campo de un registro puede ser de un tipo de dato de otro registro.

Pueden asignarse todos los campos de un registro utilizando una sentencia SELECT. En este caso hay que tener cuidado en especificar las columnas en el orden conveniente según la declaración de los campos del registro. Para este tipo de asignación es muy frecuente el uso del atributo %ROWTYPE que veremos más adelante.

Puede asignarse un registro a otro cuando sean del mismo tipo:

Declaración de registros con el atributo %ROWTYPE

Se puede declarar un registro basándose en una colección de columnas de una tabla, vista o cursor de la base de datos mediante el atributo %ROWTYPE.

Por ejemplo, si tengo una tabla PAISES declarada como:

Puedo declarar una variable de tipo registro como PAISES%ROWTYPE;

Lo cual significa que el registro miPAIS tendrá la siguiente estructura: CO_PAIS NUMBER, DESCRIPCION VARCHAR2(50), CONTINENTE VARCHAR2(20).

De esta forma se crea el registro de forma dinamic y se podrán asignar valores a los campos de un registro a través de un select sobre la tabla, vista o cursor a partir de la cual se creo el registro.

Varrays

Definición de VARRAYS.

Un varray se manipula de forma muy similar a las tablas de PL, pero se implementa de forma diferente. Los elementos en el varray se almacenan comenzando en el índice 1 hasta la longitud máxima declarada en el tipo varray.

La sintaxis general es la siguiente:

Una consideración a tener en cuenta es que en la declaración de un varray el tipo de datos no puede  ser de los siguientes tipos de datos:

BOOLEAN

NCHAR

NCLOB

NVARCHAR(n)

REF CURSOR

TABLE

VARRAY

Sin embargo se puede especificar el tipo utilizando los atributos %TYPE y %ROWTYPE.

Los VARRAY deben estar inicializados antes de poder utilizarse. Para inicializar un VARRAY se utiliza un constructor (podemos inicializar el VARRAY en la sección DECLARE o bien dentro del cuerpo del bloque):

El tamaño de un VARRAY se establece mediante el número de parámetros utilizados en el constructor, si declaramos un VARRAY de cinco elementos pero al inicializarlo pasamos sólo tres parámetros al constructor, el tamaño del VARRAY será tres. Si se hacen asignaciones a elementos que queden fuera del rango se producirá un error.

El tamaño de un VARRAY podrá aumentarse utilizando la función EXTEND, pero nunca con mayor dimensión que la definida en la declaración del tipo. Por ejemplo, la variable v_lista que sólo tiene 3 valores definidos por lo que se podría ampliar hasta cinco elementos pero no más allá.

Un VARRAY comparte con las tablas de PL todas las funciones válidas para ellas, pero añade las siguientes:

• LIMIT . Devuelve el número maximo de elementos que admite el VARRAY.
  
• EXTEND .Añade un elemento al VARRAY.
  
• EXTEND(n) .Añade (n) elementos al VARRAY.
  

Varrays en la base de datos

Los VARRAYS pueden almacenarse en las columnas de la base de datos. Sin embargo, un varray sólo puede manipularse en su integridad, no pudiendo modificarse sus elementos individuales de un varray.

Para poder crear tablas con campos de tipo VARRAY debemos crear el VARRAY como un objeto de la base de datos.

La sintaxis general es:

Una vez que hayamos creado el tipo sobre la base de datos, podremos utilizarlo como un tipo de datos más en la creacion de tablas, declaración de variables ….

Vease el siguiente ejemplo:

Para modificar un varray almacenado, primero hay que seleccionarlo en una variable PL/SQL. Luego se modifica la variable y se vuelve a almacenar en la tabla.

La utilización de VARRAYS en la base de datos está completamente desaconsejada.

Transacciones autónomas

En ocasiones es necesario que los datos escritos por parte de una transacción sean persistentes a pesar de que la transaccion se deshaga con ROLLBACK.

PL/SQL permite marcar un bloque con PRAGMA AUTONOMOUS_TRANSACTION. Con esta directiva marcamos el subprograma para que se comporte como transacción diferente a la del proceso principal, llevando el control de COMMIT o ROLLBACK independiente.

Observese el siguiente ejemplo. Primero creamos un procedimiento y lo marcamos con PRAGMA AUTONOMOUS_TRANSACTION.

    A continuación utilizamos el procedimiento desde un bloque de PL/SQL:

Es muy común que, por ejemplo, en caso de que se produzca algún tipo de error queramos insertar un registro en una tabla de log con el error que se ha produccido y hacer ROLLBACK de la transacción. Pero si hacemos ROLLBACK de la transacción tambien lo hacemos de la insertción del log.

 

Buenas prácticas trabajando con PL-SQL

Porque necesitamos Buenas Practicas en el desarrollo del Back End de las aplicaciones?

Simplemente, por las mismas razones que se necesitan en el desarrollo de cualquier tipo de software: Mantención, Legibilidad, Rehúso y Modificabilidad del código. Pero, los programadores de PL/SQL, debemos estar especialmente atentos en este sentido, por las siguientes dos razones:

Con el potencial que nos ofrece PL/SQL es importante estar atentos al abanico de posibilidades del que disponemos. No sucede lo mismo en otros lenguajes PL, ya que estos se encuentran mucho mas limitados en sus alternativas de expresión.

Es sumamente fácil, tal vez demasiado a juicio de los gurúes, escribir SQL en PL/SQL, por cuanto no necesitamos de una interfaz ODBC/JDBC etc. Esto, sumado a la creencia de que el modelo de datos esta libre de la evolución propia del software, genera código difícil de mantener y evolucionar.

EL TESTING DE UNIDAD

Algunas consideraciones al respecto:

LOS DEVELOPERS GASTAN EL 80% DEL TIEMPO DE DESARROLLO EN IDENTIFICAR Y CORREGIR DEFECTOS!

Si bien existen muchas clases de test, solamente existe un responsable para los Test de Unidad: el Programador!

Nuestro código no se va a testear solo, por lo que debemos aceptar la responsabilidad y ser disciplinados!!

Seis pasos hacia un testing de unidad exitoso:

• Describir toda la funcionalidad requerida del programa
  
• Definir el Header del programas: Nombre, Lista de Parámetros, Valor de retorno.
  
• Elaborar los casos de test para el programa
  
• Construir el programa que testee nuestro programa
  
• Programar la unidad de programa
  
• Testear, Debaguear y Corregir. Testear, Debaguear y Corregir…
  

  Repetir del 3-6 generando un Bug Report.
  

No debemos preocuparnos por comprar o desarrollar código para hacer nuestros test de unidad, podemos descargar gratuitamente del sitio oficial de Oracle un sencillo frame PL/SQL a tales efectos.

Es interesante mencionar que los casos de uso deben escribirse ANTES que la unidad de programa. Si escribimos las pruebas después de la unidad, vamos a inclinar la balanza hacia la búsqueda de aciertos y no de los fallos, existe de hecho, una tendencia psicológica a seleccionar casos de test que el desarrollador sepa tratados en su unidad.

Ahora, que ocurre cuando nuestra aplicación en producción necesita una modificación leve? Podemos hacerla y comprobar con nuestro test de unidad si la unidad es correcta, pero existe un beneficio mayor: Como sabemos que la aplicación en conjunto sigue funcionando correctamente? Los test de Unidad almacenados nos permitirán realizar lo que se denomina Test de Regresión. Se vuelven a disparar los test de unidad y de esta manera nos aseguramos que el cambio en la funcionalidad de las partes no afectó el funcionamiento del todo.

Aumentando el rendimiento con FORALL y BULL COLLECT

FORALL: Resulta sumamente útil en operaciones DML, por cuanto la sentencia FORALL nos permite tomar todos los datos de una colección y llevarlos a la base de datos de una sola vez.

Cuando se ejecuta un UPDATE, se parsea el bloque en el motor de pl/sql y después en el motor SQL una vez por cada fila updeteada, estos N cambios de contexto representan mucho overhead. En cambio, en el FOR ALL UPDATE se produce solo un cambio de contexto en cada parser.

Ejemplo:

Esta sentencia por estar dentro de un bloque PL/SQL requiere que la interprete el motor PL/SQL y después el de SLQ POR CADA fila.

FOR rec IN emp_cur LOOP

UPDATE employee

SET …

WHERE …

LOOP;

Se parsea solo una vez, pues se hace un cambio de contexto entre el motor PL y el moto SQL, solamente.

ORALL indice IN coleccion.FIRST .. coleccion.LAST

UPDATE employee

SET …

WHERE …

Observaciones sobre el uso de FORALL:

El motor lee el contenido de la colección de manera secuencial, por lo que si encuentra una posición sin un valor definido disparará la excepción ORA-22160. Si no queremos este problema, y disponemos de Oracle 10G è podemos agregar la sentencia INDICES OF en la sentencia FORALL. Por ejemplo:

FORALL i IN INDICES OF miColección

INSERT INTO …

Solo se permiten DML simples para FORALL.

SQL%BULK_ROWCOUNT retorna el número de filas procesadas para la consulta.

Antes de Oracle 10G, las colecciones debían llenarse secuencialmente.

BULK COLLECT INTO:

Como es sabido, cuando queremos recuperar el valor de una fila utilizamos la sentencia

INTO, pero esto no es posible cuando el resultado de la consulta retorna mas de una fila. Existe a tales efectos, dos soluciones. Una, es el uso de cursores. La otra, es la sentencia BULK COLLECT INTO que podrá recuperar N filas y colocarlas en una estructura adecuada, siendo esta una colección de lo que sea que necesitemos, Rowtypes, Objects, etc.

También podemos hacer abrir una variable de cursor sobre una estructura, usando la sentencia BULK COLLECT INTO.

Un caso en que podemos usar BULK COLLECT INTO

Si necesitamos lleva datos masivamente a la base, entonces necesitaremos hacer un FORALL pero esta sentencia solo funciona con colecciones, no con cursores, por lo que para usarla necesitamos haber hecho preferiblemente un BULK COLLECT INTO sobre alguna colección o array.

Resumiendo, siempre que necesitemos realizar una DML por cada fila de un cursor, o una DML dentro de un LOOP, es mejor utilizar un BULK COLLECT INTO mas allá de si lo hacemos sobre una variable de cursor o directamente con una sentencia SQL.

Cuando usar esta sentencia? Cuando se necesite ejecutar SQL dentro de un LOOP, preferentemente si el número de filas es mayor a 5.

 declaramos una tabla de algún tipo de dato, preferentemente un ROWTYPE: MiTabla

SELECT * BULK COLLECT INTO MiTabla

Iteramos sobre la tabla.

PAGINACIÓN DE SENTENCIAS DML

Declarar un cursor y abrirlo

En ves de hacer FETCH rec, hacemos FETCH rec BULK COLLECT INTO MiTabla [LIMIT cantFilasQueQuieraTraer].

Procesamos la tabla, y seguimos iterando pero nos traemos de a N filas, en vez de a una.

Los BULK COLLECT pueden ser usados con cursores Implícitos o Explícitos. Las colecciones siempre se rellenan secuencialmente, comenzando de 1.

SOBRE LOS ABUSOS DE SQL

Pl/sql nos permite interactuar con el motor SQL sin otra capa intermedia, como ODBC/JDBC etc. De esta manera, facilita tal vez demasiado el uso de SQL.

Pero, la capa PL/SQL debería permitir una fácil manipulación del modelo de datos, y no constituir un obstáculo para la correcta evolución del mismo. Si todos escriben SQL donde y cuando quieren, entonces el código se torna inmanejable.

Recordemos hardcodear nuestro código, es malo. Por otro lado, es una verdad innegable que TODA SENTENCIA SQL ES HARD-CODING! Por lo que podemos afirmar por transitividad y sin pérdida de generalidad, que SQL es MALO! Que mas sabemos sobre SQL?

La mayoría de los problemas de performance suelen estar en las sentencias SQL.

Provocan errores en tiempo de ejecución

Las sentencias SQL estan sujetas a las reglas del negocio, y las reglas del negocio cambian por lo que nuestras sentencias SQL cambian.

El modelo de datos, es una de los modelos mas volátiles de cualquier aplicación; las sentencias SQL cablean esas estructuras! Sería conveniente que regáramos por toda la aplicación las mismas sentencias SQL cableadas que probablemente mañana tengamos que cambiar? Claro que no lo sería, si hacemos tal cosa, deberemos debaguear, optimizar y mantener las mismas líneas de código donde quiera que aparezcan.

Si abstraemos el modelo de datos en estructuras que lo encapsulen será mucho mas fácil realizar las modificaciones del modelo de datos. Además, se puede hacer un manejo consistente de las excepciones, pues de otra manera, pueden hacerse N manejos de una excepción en distintos lugares.

Para abstraer consultas que retornen una sola fila:

Es importante la utilización de generadores de código para las sentencias simples, como la recuperación de una fila por su PK o por algún índice. Se sugiere la abstracción de una tabla mediante métodos de un paquete que retornen el valor de una columna en particular. También puede ser útil escribir un paquete que se inicialice con la PK de una instancia de la entidad, por ejemplo si tenemos la tabla Empleados, podemos desarrollar un paquete que nos permita acceder cada uno de las columnas mediante una función retornarColumna(PK), por ejemplo. Facilitará esta abstracción, el uso de un TYPE declarado público en el paquete que abstrae la entidad del modelo que estamos implementando.

Para abstraer consultas que retornen mas de una fila:

Tenemos dos opciones:

• Encapsular retornando una colección: Podemos hacer un BULK COLLECT en una colección de tipos ROWTYPE y retornar la coleccion.
  
• Variable Cursor: Esto último es sumamente útil para retornar datos en entornos no PL/SQL. Una variable cursor es un ptero que apunta a un result set. Podemos pasar variables cursor de una unidad de programa a otra, inclusive si no son programas PL/SQL.
  

Pensar en SQL como un SERVICIO provisto por el gestor relacional, y no como líneas de código, puede contribuir a que no rescribamos una y otra vez sentencias cableadas. Repetir SQL es repetir lógica asociada al negocio. Mejor solicitar el servicio cuando se necesite y no redefinirlo cada vez. Esto se logra con una apropiada encapsulación de los objetos de base de datos.

PROGRAMAR PARA EL CAMBIO

Cambian las estructuras de datos è Los programas se marcan como inválidos è Hay que re compilar el código inválido

Por esto es sumamente importante no cablear la definición de una variable.

Usar %TYPE y %ROWTYPE.

Hacer los FETCH de un cursor en un registro de tipo cursor y no en variables!

Asegurarnos de que ninguna variable entrará en conflicto con un objeto existente de la base de datos. Pero como podemos adelantarnos a un futuro renombramiento de algún objeto de la base? Las convenciones de nomenclatura no nos ofrecen garantías è
SIEMPRE CUALIFICAR LAS REFERENCIAS A VARIABLES DENTRO DE UNA SENTENCIA SQL!!

Ejemplo:

Anteponemos el nombre del Procedure a la variable para no tener conflictos.

También podemos arriesgarnos a que las reglas de nomenclatura nos ayuden, esto por supuesto, si no existe la posibilidad de que una columna se renombre con nuestra nomenclación…

MANEJAR LOS ERRORES EFECTIVA Y CONCISTENTEMENTE

Las excepciones pueden ser disparadas y manejadas, pero no pasadas como argumentos a otros programas.

Para comunicarle un mensaje de error a la aplicación que usa nuestro paquete, tenemos que usar RAISE_APLICATTION_ERROR (NUMERO, MENSAJE). Es importante definir de manera centralizada los números de error y los mensajes correspondientes para hacer un manejo consistente.

UN PAR DE CONCEPTOS AVANZADOS SOBRE PACKAGES

La participación de los packages en el diseño de alto nivel:

La fortaleza de los packages radica en la posibilidad que brindan implementar abstracción para nuestras aplicaciones. Nos permiten ocultar la complejidad y los detalles de una funcionalidad determinada. Por esta razón, es el proceso de diseño debe haber una instancia para generar una visión arquitectural de la definición e interrelación de los packages. Una manera efectiva de lograr esto, es asociar un package a cada necesidad funcional compleja de nuestra aplicación.

No es desatinado intentar una relación entre un diagrama de clases , uno de Entidad / Relación y el diagrama de packages. De hecho, un paquete intenta implementar las principales cualidades de una clase al definir una interfase pública y una implementación privada.

Sobre las interdependencias:

Un diagrama nos permitirá establecer dependencias y relaciones entre los mismos. Nunca deberíamos permitir que el package A tenga una dependencia con el package B si este último tiene una dependencia con el package A. Si no es posible evitar esta interdependencia tenemos que recordar que compilando primero la especificación de ambos paquetes podemos compilar la interdependencia existente. Es importante recordar esto a la hora de escribir un script de instalación. De esto surge como una buena practica mantener en archivos separados la especificación de un package de su body. Si ponemos todo junto quizá habrá problemas en los scripts de instalación.

Por supuesto, no todas las interdependencias son viables. Si la especificación del package A referencia a un elemento del package B, y la especificación del package B referencia un elemento del package A è Sencillamente será imposible compilar estos paquetes, por lo que dicha situación tiene que ser claramente evitada.

Buenas Prácticas:

Un principio fundamental es considerar al resto de developers como Usuarios. Este detalle psicológico tendrá un impacto sustancial en la calidad de nuestras interfaces. Todos los programadores somos concientes de la opinión generalizada que tenemos de los usuarios de nuestras aplicaciones. Para lograr el confort en el uso de nuestras aplicaciones, debemos tener en mente algunas consideraciones.

Hacer nuestras aplicaciones CASE INSENSITIVE

Siempre que recibamos parámetros de tipo texto, necesitamos asegurarnos de que la manera en que los recibamos no afecte la conducta de nuestra aplicación, de lo contrario veremos muy seguido errores como por ejemplo el ORA-06503: Function returned without value. Simplemente con un UPPER(Parámetro) podemos evitar la incomodidad de recordar como tienen que ser parametrizada nuestra aplicación.

Evitar la parametrización con literales de nuestra unidad.

El usuario estará mucho mas feliz si no tiene que recordar los literales que guían la conducta de nuestra aplicación. Como logramos esto? Tenemos dos opciones.

Una es proveer programas separados para realizar distintas acciones. Esto es viable solamente si la cantidad de acciones a realizar es fija y estemos seguros de que continuará así…

Proveer constantes basadas en packages, preferiblemente el mismo donde tenemos la unidad. Esta es sin duda la mejor opción. Es cierto que de todas formas el usuario tendrá que recordar el nombre del parámetro por mas que esté en un package, pero si algo sale mal se enterará en tiempo de Compilación lo cual es mucho mas ventajoso que enterarse en tiempo de ejecución, teniendo que depurar el código buscando ese misterioso error no detectado causado por escribir con mayúsculas alguna letra del literal que parametriza la unidad.

Flexibilizar nuestros packages mediante Toggles.

La aproximación mas general para flexibilizar una unidad de programa es hacerla altamente parametrizada. El problema es que este enfoque aumenta considerablemente el coupling de la unidad y complejiza la interface. Numerosos estudios comprueban que el cerebro humano no esta bien equipado para tratar con mas de 7 ítems a la vez… Por lo que tenemos que cuirdarnos de sobrecargar en exceso las firmas de nuestras unidades de programa. Además, que pasaría si quisiéramos afectar TODA la conducta de un package y no solo la de una unidad?

Una mejor aproximación es el uso de Toggles o suichs. Un toggle es un set de tres programas: Dos procedimientos para prender o apagar y una función para retornar el valor del toggle.

Sacar provecho del OverLoad de nombre

Las ventajas de sobrecargar nombres de unidades son claras. Pensemos en la funcion To_Char(). Tenemos que llamar a funciones distintas si el argumento es de tipo integer o de tipo real? No, gracias a su OverLoad.Cuando sobrecargamos el nombre de una unidad, estamos ofreciendo muchas versiones de una misma unidad, simplificando la interface de la unidad, lo que resulta crítico. En esto de ver a los developers como usuarios de nuestros desarrollos, notamos la importancia de programar unidades intuitivas y versátiles.

Sucede a veces, que nos encontramos con 3 o 4 unidades de programas que básicamente realizan la misma tarea a nivel semántico pero que devuelven distintos tipos de datos: Como transformar esas N unidades de diferentes nombres en N unidades de igual nombre sobrecargadas? Un primer intento pudiera ser generar solo una función y pasarle un parámetro enumerado. Una mejor aproximación sería generar las N unidades con igual nombre, proveyéndoles a cada una un parámetro VALOR_DEFAULT para el retorno, de manera que los diferentes tipos de datos permitirán evadir la ambigüedad de la firma de las unidades sobrecargadas.

El verdadero desafío es desarrollar una sensibilidad que nos permita hacer del overloading nuestra primera solución intuitiva frente a los problemas donde la facilidad de uso sea un objetivo.

Modularizando para desarrollar Packages mantenibles

Tenemos que resistir la tentación de cortar/pegar código creyendo que así estamos construyendo aplicaciones rápidamente, porque lo que realmente estamos haciendo es cavar una fosa muy profunda donde enterraremos las características evolutivas de nuestra aplicación. Tenemos entonces, una regla: Nunca repitamos una línea de código. En cambio, hemos de construir una unidad privada y llamarla 2 o N veces.

Vale aclarar, que Overloading y modularidad deben considerarse dos caras de la misma moneda si queremos implementar el package correctamente.

El modelo del Diamante:

Debemos estar atentos para que las N versiones de nuestra unidad sobrecargada no este repitiendo líneas de código. Debemos factorizar esas repeticiones y de esa manera lograr la regla mencionada. Normalmente, se llama a este diseño Diamante, si vemos que en la capa superior tenemos una vista de usuario (Que no se corresponde exactamente a una unidad de programa, sino a las N unidades sobrecargadas). En un nivel medio tenemos N versiones de la unidad, y en la capa inferior tenemos una unidad que factoriza lo común de las anteriores (que normalmente contendrá un IF desarrollado, a esto se le llama Efecto de Lampara de Lava, porque si abrimos los if da la sensación de modelar una de estas lámparas). Es altamente recomendable construir nuestro package con este criterio.

Ocultando datos en el Package

Llamamos Packages Data a cualquier estructura de datos implementada en la especificación (los públicos) o el cuerpo (privados) de un package.

Manejar datos públicos es ciertamente conveniente para algunos casos. Pero puede presentar problemas:

Perdida de Control: Cualquier programador puede cambiar los valores de la estructura por ser ésta pública. Es posible que existan reglas de negocios que afecten el valor de esta variable, pero no podremos hacer nada para proteger esa regla de negocios pues nuestra variable es pública, y cualquiera puede modificarla.

Perdida de Flexibilidad: Al ser conocida su estructura, todos hacen suposiciones sobre la misma. La posible necesidad de hacer un cambio en la estructura generará muchos problemas en los usuarios.

Se recomienda nunca definir variables en la especificación de un package si es que tenemos otra alternativa. Lo mejor es hacer públicos los datos pero no las estructuras que lo contengan. Como se logra esto? Declarando las variables en el cuerpo del package y definiendo métodos Get y Set para manipular sus valores.

Una muy interesante consecuencia de esta buena práctica, es la posibilidad de conocer Que y Cuando se esta modificando el valor de un dato. Como es esto posible? Como solo existe un punto por el que se puede afectar el valor de una variable privada, es decir su método Set, podemos en el mismo realizar una traza. Antes de modificar su valor, mostramos en pantalla o en un archivo el nuevo valor que tomará. Esta aproximación puede ser muy poderosa si mantenemos en un package especialmente dedicado a las trazas, manteniendo el nombre del programa que esta realizando el cambio y suministrando un método al que podamos llamar desde los Sets para realizar la traza. A este package podemos añadirle un Toggle o un Suich para activar a desactivar la traza a nuestro gusto. De esta manera, no pasaremos horas debagueando nuestra aplicación para saber donde y que provocó el cambio del valor de una variable.

Otra ventaja, es cuando cambie la estructura de la variable, no habrá que recompilar todos los packages que la utilicen, cosa que sí ocurre cuando declaramos la variable en la especificación del package.

Al mover los datos al cuerpo del paquete, estaremos simplificando la interface de los métodos allí contenidos, pues estos datos serán ahora globales al package, de modo que probablemente nos ahorremos un parámetro en todos aquellas unidades de código que lo necesiten. Es cierto que esto hace nuestros packages dependiente de los datos, pero como contrapartida solidifica la idea de que los packages son mas un entorno que una colección de unidades.

Sobre cuando y donde empaquetar funcionalidad:

Cuando hay que empaquetar funcionalidad? Casi siempre.

Cuando no hay que empaquetar funcionalidad? Las siguientes condiciones dadas simultáneamente pueden modelar una sugerencia:

La unidad de programa es muy usada desde muchos lugares

La unidad de programa, nunca cambia è no necesita ser recompilada

El resto de las unidades de programa dentro del package suele cambiar y en consecuencia el package entero necesita ser recompilado.

En este caso, sería importante separar esa unidad de programa del package, porque si no lo hacemos, cada vez que se modifique el package en cuestión se marcará como inválidos todos los programas que lo usen, aunque estos no hallan cambiado.

NOTA: Sobre lo anterior, es importante saber que si recompilamos el BODY de un package, no habrá necesidad de recompilar todos los programas que referencien el package, pero si recompilamos la especificación del mismo, entonces sí habrá que recompilar todas los objetos de la base que lo referencien.

Entonces, como regla: Es conveniente separar aquella funcionalidad que rara vez cambia, de aquella que suele hacerlo. Si no lo hacemos, habrá que compilar el package, acción que invalidará todos los programas que lo usen, y estos también necesitaran ser recompilados.

Sobre la inicialización de packages:

Supongamos una sección de inicialización de un package que tiene también una sección de manejo de excepciones:

CREATE OR REPLACE PACKAGE valerr

IS

FUNCTION private_variable RETURN VARCHAR2;

END valerr;

/

CREATE OR REPLACE PACKAGE BODY valerr

IS

g_private VARCHAR2(1) := ‘ABCDE’; –PROBOCARÁ EL DISPARO DE UNA EXCEPCIÓN!!!

FUNCTION private_variable RETURN VARCHAR2

IS

BEGIN

RETURN g_private;

END private_variable;

BEGIN

DBMS_OUTPUT.PUT_LINE (‘Before I show you v…’);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE (‘Trapped the error!’);

END valerr;

/

Que ocurrirá cuando ejecutemos el programa?

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or

value error: character string

buffer too small

Pero no había una sección de cacheo de errores? Por otro lado, para hacerlo mas confuso, si corremos este programa una vez mas, este no dará error! Porque sucede esto?

La sección EXCEPTION de la inicialización de un package, solo captura errores que se produzcan en la sección ejecutable del bloque de inicialización.

Cuando la inicialización de un package falla, Oracle marca el paquete como inicializado. Y como todos los paquetes se inicializan solo una vez por sesión, este no vuelve a inicializarse durante la vida de la sesión que lo inicializó.

 Resumiendo el enfoque orientado a la Performance

• Mirar las áreas donde pueda ser posible la aplicación de FOR ALL & BULK COLLECT.
  
• Buscar todas las sentencias DML. Si estas están dentro de algún tipo de LOOP, externalizarlas en un Procedure de ámbito Local en la sección de declaración, buscando aumentar el uso del procesamiento BULK.
  

10 Mitos que los programadores de PL/SQL deberíamos conocer!

Nunca pedir ayuda.

Grave error, sin importar nuestro nivel de Senority siempre debemos hacerlo. La siguiente sugerencia puede producir un equilibrio práctico: Si no podemos encontrar el problema después de 30 minutos… Pidamos ayuda!

La cantidad de cafeína consumida incide en forma directamente proporcional a nuestras facultades de concentración y productividad.

Siempre es mejor beber agua para hidratar nuestro cuerpo que abusar de la cafeína…

Nunca compartir nuestro conocimiento! Esto nos da poder y seguridad.

De hecho, desde Oracle.Com podemos disponer de muchos recursos tales como Papers, Código, Sugerencias, etc de manera libre y gratuita, algunas de las presentes sugerencias están tomadas de allí.

Nunca dudar de los gurus.

Siempre debemos dejar que nuestra realidad termine de aceitar los consejos que recibimos, ellos también se equivocan.

Nunca ocultar los detalles de nuestro código

Pero ocultar no es sinónimo de algo negativo en el contexto de la programación? No si refiere a detalles de la implementación. Siempre debemos ocultar los detalles, solo así podremos programar para el cambio.

Nunca permitamos que alguien mas lea y observe nuestro código!

La revisión de código constituye un arma sumamente útil contra los bugs y problemas de diseño a bajo nivel.

Nunca te preocupes por el mañana, codifiquemos para el hoy!

Es vital comprender la naturaleza dinámica y cambiante de las reglas del negocio representadas desde el modelo de datos hacia el front end.

Nunca corrijas bugs que otros programadores no hallan encontrado…

Falso. El Bug de hoy, es el bug de mañana. Por supuesto que un repositorio de código soportado por un controlador de versiones nos dará mas valentía para tal empresa.

Nunca asumamos que un Bug de Oracle, fue corregido.

Oracle sugiere confiar en la disponibilidad de la plataforma y la evolución positiva de las misma.

Nunca digas Nunca!

En el contexto del desarrollo de software, podemos asegurar que ese enunciado es falso. Sirva de ejemplo, las siguientes verdades universales en el mundo del desarrollo:

Las cosas nunca permanecerán iguales… o a lo que es equivalente

Las cosas siempre cambiaran…

Unas palabras sobre Refactorizar

Al terminar de desarrollar una unidad, no esta de mas dedicar unos minutos al análisis detenido de nuestro código buscando secciones que requieran una refactorización. La belleza esta en los detalles…

Podemos definir Refactorización, como el proceso de transformación del código escrito mediante el cual sin modificar la conducta externa y esperada, se mejora la estructura y la escritura del código. Es decir, nos abstraemos del Que, y nos concentramos en el Como.

Dos objetivos son sumamente relevantes en el proceso de refactorización:

Simplificar DONDE las cosas son realizadas. (Reorganizar las estructuras del código, moviendo este de un lugar a otro a efectos de aumentar su legibilidad, modificabilidad y rehuso)

Simplicar COMO las cosas son realizadas (Haciendo el código mas elegante, mas compacto y fácil de seguir).

Pautas para refactorizar nuestros desarrollos:

Si en una unidad se llama con mucha frecuencia a la funcionalidad de un package, debemos preguntarnos si no sería mas apropiado que la unidad este contenida en dicho paquete.

Prestar atención a los puntos donde se utilice lógica booleana compleja. Es muy probable que mediante las reglas matemáticas de la lógica Booleana normalmente conocida por los profesionales, una gran expresión sea equivalente a otra mucho mas simple. Por ejemplo supongamos una función que retorna lo siguiente

RETURN ((V_booleana1 And V_booleana2) OR (NOT V_booleana1 And NOT V_booleana2))

El sentido común asi como la lógica booleana nos dicen que esa expresión es equivalente a la siguiente:

RETURN (V_booleana1 = V_booleana2)

Observar aquellos lugares donde el código se extiende demasiado. Esta claro que existen problemas que requieren la escritura de miles de líneas, no es el volumen de las líneas lo que esta en discusión, sino la estructura que se le de a las mismas. Es muy probable que podamos ocultar detalles del mismo en Procedure o Function anidadas y de esta manera simplificar las cosas. Como referencia, debemos pensar que desde el BEGIN al END nunca deberíamos tener mas de 50 líneas, es mas, un promedio de 20 a 30 líneas es ampliamente recomendado. Si esto se hace con un criterio Top Down, seguramente será mucho mas sencillo de seguir que un monolítico bloque ejecutable, o muchas unidades encadenadas sin criterio arquitectural.

Cuidar el acoplamiento de las unidades. Si tenemos unidades de programa exageradamente parametrizadas, además de restarle claridad al código, es muy probable que tengamos un problema de coupling en nuestro diseño.

Cuidar de mantener simples las estructuras condicionales:

Las expresiones son a menudo mas legibles y entendibles cuando se escriben en forma positiva, es decir el abuso del operador NOT puede restar claridad a nuestro código.

Mantener el nivel de anidamientos bajo. Preferiblemente una estructura If anidada no debería tener mas de dos o tres niveles. Si es necesario ocultar mas niveles de anidamiento, deberíamos usar Procedimientos Locales Anidados.

Cuando una vez no es suficiente:. Los loops o interacciones, se cuentan entre los elementos mas sensibles a la performance y los errores. Por supuesto, Oracle provee varias estructuras para implementar iteraciones, y el uso de cada una dependerá de las circunstancias y no es materia de discusión de las presentes líneas. Ahora bien, existe una guía que bien puede ser tomada como una máxima a la hora de diseñar nuestras estructuras condicionales: Una forma de entrar, Una forma de salir. Esto significa que solo existe una manera de entrar a una iteración o de comenzar ese Loop, y que existe un solo punto donde la iteración termina. Una estructura Loop que tenga tres RETURN, no estaría respetando este principio.

Optimización de SQL-Pl-SQL Trazas – tkprof

Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL

Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle. En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL, utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el formato de dicho comando y lo complejo que resulta analizar el contenido de la tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el uso del comando SQL*Plus AUTOTRACE.

En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:

SET AUTOTRACE OFF – Deshabilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE ON – Habilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE TRACEONLY – Habilita el análisis (traceado) de las sentencias SQL pero no devuelve la salida de dicha sentencia. Su uso es recomendable si sólo estamos analizando el rendimiento de la sentencia y no nos interesa conocer los registros que pueda devolver.

SET AUTOTRACE ON/TRACEONLY EXPLAIN – Muestra el plan de ejecución de la sentencia pero no muestra las estadísticas.

SET AUTOTRACE ON STATISTICS – Muestra las estadísticas pero no muestra el plan de ejecución de la sentencia.

Nota: Si se omiten las opciones EXPLAIN y STATISTICS, entonces al ejecutar una sentencia SQL se mostrarán tanto el plan de ejecución como las estadísticas.

Para poder utilizar la opción EXPLAIN del comando AUTOTRACE, es necesario crear la tabla PLAN_TABLE en el esquema del usuario, es por eso que este comando sólo puede ser ejecutado por determinados usuarios, aquellos para los que la mencionada tabla ya ha sido creada. Es importante pues, conocer los usuarios Oracle que han sido configurados para poder ejecutar el comando AUTOTRACE.

Por otro lado, para acceder a las estadísticas, hay que tener acceso a varias tablas del sistema en las que se almacenan los datos del rendimiento de las sentencias SQL. Los DBA pueden dar este acceso utilizando el script plustrce.sql. El nombre de este script puede variar dependiendo del sistema operativo. El DBA tiene que ejecutar dicho script como usuario SYS y, asignar al usuario en cuestión, el papel (role) correspondiente.

Una vez que se ha configurado convenientemente un usuario para que pueda acceder al plan de ejecución y a las estadísticas, basta habilitar el AUTOTRACE para que, al ejecutar una sentencia SQL, nos aparezca el plan de ejecución así como los correspondientes valores estadísticos.

Los valores estadísticos más importantes mostrados por la base de datos Oracle, una vez activado el comando AUTOTRACE, son los siguientes:

- DB block gets: Número de operaciones de entrada/salida realizadas sobre la memoria caché.
- Consistent gets: Número de operaciones de entrada/salida realizadas sobre los segmentos de rollback debido a cambios en la memoria caché.
- Physical reads: Número de bloques leídos desde el disco.
- Sorts (memory): Número de operaciones realizadas en memoria para ordenar los datos.
- Sorts (disk): Número de operaciones realizadas en disco para ordenar los datos.

A la hora de mejorar el rendimiento de una sentencia SQL o PL/SQL, debemos conseguir que el número de db block gets, consistent gets y physical reads sea bajo comparado con el número de registros devueltos por dicha sentencia. Por otro lado, la ordenación de los datos debe realizarse, siempre que sea posible, en memoria.

En cuanto a lo que se refiere al plan de ejecución, desde este enlace podéis acceder a un ejemplo de sentencia SQL con su correspondiente plan de ejecución y una breve interpretación de dicho plan: Ejemplo de plan de ejecución.

Como un primer consejo a la hora de analizar un plan de ejecución, me gustaría indicar que lo primero que hay que evitar son los FULL SCAN (recorrido de todos los registros de una tabla). No obstante, hay determinadas circunstancias bajo las que un FULL SCAN puede ser recomendable; así, cuando una tabla tiene pocos registros, puede ser conveniente realizar un FULL SCAN, en vez de acceder a la misma a través de un índice.

El optimizador PL/SQL basado en normas (Rule-Based Optimizer)

Veremos algunas de las características del optimizador PL/SQL basado en normas (Rule-Based Optimizer). Lo primero que quiero mencionar es que Oracle recomienda utilizar el optimizador PLSQL basado en costes (cost-based optimizer), no obstante, en algunos casos, el hecho de tener que activar las estadísticas de la base de datos para poder utilizar este último optimizador, puede hacer que resulte interesante utilizar el optimizador basado en normas y dejar las estadísticas desactivadas para no afectar al rendimiento de la base de datos.

El optimizador PLSQL basado en normas utiliza siempre que puede los índices, incluso cuando las tablas son pequeñas o cuando el número de registros que devuelve la sentencia SELECT es un porcentaje elevado con respecto al número total de registros de la tabla, casos para los que es mejor realizar un escaneado total (full scan) ya que la respuesta es más rápida (mejora el rendimiento). Esto es debido a que el optimizador basado en normas no hace uso de valores estadísticos, tales como el número total de registros de una tabla.

El optimizador PL/SQL basado en normas hace uso del siguiente orden de prioridades para determinar cual va a ser la forma de acceder a las tablas y determinar finalmente cual va a ser el plan de ejecución:

Prio  Forma de acceso
1  Single row by ROWID
2  Single row by cluster join
3  Single row by hash cluster key with unique or
primary key
4  Single row by unique or primary key
5  Cluster join
6  Hash cluster key
7  Indexed cluster key
8  Composite index
9  Single-column index
10  Bounded range search on indexed column
11  Unbounded range search on indexed column
12  Sort-merge join
13  MAX or MIN of indexed column
14  ORDER BY on indexed column
15  Full table scan

Los distintos métodos de acceso los he dejado en inglés, ya que es bastante complicado traducir esta terminología. En el presente artículo no voy a explicar cuales son las diferencias existentes entre las distintas formas de acceso. No obstante, en sucesivos artículos pondré algunos ejemplos que permitirán diferenciar estos conceptos.

Siguiendo con el tema que concierne a este post, el optimizador basado en normas analiza la sintaxis de la sentencia SQL para establecer los distintos métodos de acceso a las tablas. Básicamente lo que hace es determinar todas las formas de acceso posibles y escoger aquella que tiene una prioridad menor.

Este esquema siempre asume que un escaneado total (full scan) es el peor método de accesso (prioridad 15). Sin embargo, ya he mencionado al principio del artículo que esto no siempre es verdad.

Estos métodos de acceso, así como otros adicionales, están también disponibles para el optimizador PL/SQL basado en costes. Sin embargo, este optimizador ignora las prioridades, y determina el coste esperado de ejecución de la sentencia SQL para cada uno de las formas de acceso posibles basándose en las estadísticas, escogiendo después aquella forma de acceso con el menor coste estimado. Muchas funcionalidades del Oracle, como los hash joins, star queries e histogramas, sólo están disponibles para el optimizador PLSQL basado en costes.

 

Hints en PL/SQL para el modo de optimización

Los hints son pistas que se dan al optimizador SQL de Oracle para que elabore el plan de ejecución de una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc) según nosotros le aconsejemos. En este primer artículo sobre los hints voy a empezar hablando de aquellos que se utilizan para seleccionar el modo de trabajar del optimizador Oracle. Estos hints, hablando desde un punto de vista práctico, no son muy utilizados, aunque no por ello pueden dejar de ser útiles en determinadas circunstancias.

Los hints se incorporan a una sentencia DML en forma de comentario y deben ir justo detrás del comando principal. Por ejemplo, si se tratara de una sentencia SELECT el formato sería el siguiente:

SELECT /*+ COMANDO-HINT */ …

Estos son los hints que se pueden utilizar para cambiar el modo de optimización del optimizador Oracle:

/*+ RULE */ – Fuerza a que se utilice el optimizador basado en normas (rule-based optimizer). Con este optimizador los planes de ejecución cambían según sea la sintaxis de la sentencia DML, no utiliza las estadísticas asociadas con las tablas de la base de datos Oracle y no calcula los costes del plan de ejecución. El optimizador basado en normas no ha cambiado desde la versión 6 de Oracle.

/*+ CHOOSE */ – Fuerza a que se utilice el optimizador basado en costes (cost-based optimizer). Este optimizador construye los planes de ejecución basándose en las estadísticas almacenadas en el diccionario de datos. Tiene en consideración el número de lectura lógicas (el factor más importante), la utilización de la CPU junto con los accesos a disco y a memoria, y el uso de la red (cuando los datos residen en diferentes servidores). Una de las ventajas de utilizar el optimizador basado en costes es que Oracle lo está mejorándolo continuamente.

/*+ ALL_ROWS */ – Fuerza a que se utilice el optimizador basado en costes y optimiza el plan de ejecución de la sentencia DML para que devuelva todas las filas en el menor tiempo posible. Es la opción por defecto del optimizador basado en costes y es la solución apropiada para procesos en masa e informes, en los que son necesarias todas las filas para empezar a trabajar con ellas.

/*+ FIRST_ROWS (n) */ – También fuerza a que se utilice el optimizador basado en costes y optimiza el plan de ejecución de la sentencia DML para que devuelva las “n” primeras filas en el menor tiempo posible. Esto es idóneo para procesos iterativos y bucles, en los que podemos ir trabajando con las primeras filas mientras se recuperan el resto de resultados. Obviamente este hint no será considerado por el optimizador si se utilizan funciones de grupo como MAX, SUM, AVG, etc.

Hints en PL/SQL para determinar el método de acceso

Ya hemos hablado de los hints para el modo optimización. En este apartado continuaremos hablando de los hints pero, en concreto, de aquellos que permiten indicar al optimizador Oracle el modo en que se debe acceder a los datos de las tablas. Este tipo de hints resultan extremadamente eficaces a la hora de optimizar una sentencia SQL.

En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la muestre de nuevo:

{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */

o

{ DELETE | INSERT | SELECT | UPDATE } –+ HINT (parámetros)

Los hints básicos que sirven para determinar el metodo de acceso a los datos de una tabla Oracle son los siguientes:

/*+ FULL (nombre_tabla) */ – Fuerza a que se realice la búsqueda accediendo a todos los registros de la tabla indicada. Cuando las tablas tienen un número reducido de registros puede resultar bueno para el rendimiento de una sentecia DML el forzar un escaneado completo de la tabla en lugar de que el optimizador decida acceder a dicha tabla mediante un índice, ya que, en estos casos, el acceso por índice suele ser más lento.

/*+ ROWID (nombre_tabla) */ – Fuerza a que se acceda a la tabla utilizando el ROWID (identificativo único de los registros de una tabla). Este tipo de hint, por si solo, no es muy útil.

/*+ INDEX (nombre_tabla [nombre_índice] …) */ – Fuerza a que se acceda a la tabla utilizando, en sentido ascendente, el índice indicado. Muchos problemas de rendimiento vienen causados por el hecho de que el optimizador Oracle decide acceder a una tabla utilizando un índice incorrecto. Mediante este hint podemos indicarle al optimizador que utilice el índice que nosotros consideremos adecuado.

/*+ INDEX_DESC (nombre_tabla [nombre_índice] …) */ – Idéntico al anterior hint pero en este caso el acceso a través del índice se hace en sentido descendente.

/*+ AND_EQUAL (nombre_tabla [nombre_índice] …) */ – Este hint se utiliza para forzar el uso de más de un índice (se utilizarían los índices indicados como parámetros) y, después, fusionar los índices quedándose con los registros encontrados en todas las búsquedas por índice realizadas.

/*+ INDEX_FFS (nombre_tabla [nombre_índice] …) */ – Fuerza el acceso a los datos de la tabla mediante una búsqueda (Scan) rápida (Fast) y total (Full) sobre el índice indicado. Es parecido a utilizar el hint FULL pero sobre un índice en lugar de una tabla, lo cual, difícilmente, puede ser bueno para el rendimiento de una sentencia DML.

/*+ NO_INDEX (nombre_tabla [nombre_índice] …) */ – Indica al optimizador que no se utilicen los índices indicados. Puede ser útil cuando no tengamos claro cual es el mejor índice que debe ser utilizado para acceder a una tabla pero, por contra, sepamos que podemos tener problemas de rendimiento si se accede a la tabla por un determinado índice y queramos evitar que esto ocurra.

Puesta a punto de sentencias SQL

Si la siguiente sentencia SELECT:

SELECT *
FROM empleados
WHERE nombre = ‘Francisco’
AND estado_civil = ‘S’ — Soltero

nos está dando tiempos de ejecución largos, esto querrá decir que:

1. Obviamente la tabla empleados es de un tamaño considerable.

2. La tabla no está adecuadamente indexada o que, aún habiéndose creado el índice adecuado, lógicamente un índice sobre la columna “nombre”, el optimizador SQL decide utilizar el índice sobre otra columna.

Si tenemos problemas de “performance” con la sentencia SELECT de arriba y suponiendo que tenemos un índice sobre la columna “nombre”, esto nos debe llevar a pensar que existe otro índice sobre la columna “estado_civil” que es el que el optimizador está utilizando para ejecutar la sentencia.

Los índices sobre columnas de este tipo, con un rango de valores pequeño (soltero, casado, viudo,…) y con una distribución de valores más o menos homogénea para algunos de estos valores (podemos pensar que el número de solteros va a ser similar al de casados y bastante mayor que el de viudos), van a causar problemas en los tiempos de ejecución. Este tipo de índices sólo son interesantes cuando uno de los posibles valores que puede tomar la columna es mucho menos numeroso que el resto y la sentencia SQL la vamos a limitar en base a dicho valor. Por ejemplo, si nuestra sentencia fuera:

SELECT *
FROM empleados
WHERE nombre = ‘Francisco’
AND estado_civil = ‘V’ — Viudo

Podemos tener la certeza de que los tiempos de ejecución no serían tan malos. En cambio, cuando hacemos la comparación “estado_civil = ‘S’”, estamos practicamente haciendo un “full scan” sobre un índice y esto es incluso peor que hacer un “full scan” sobre una tabla.

¿Qué tenemos que hacer para arreglar la primera sentencia? Debemos forzar al optimizador a usar el índice sobre la columna “nombre”, para ello tenemos dos opciones:

SELECT *
FROM empleados
WHERE nombre = ‘Francisco’
AND estado_civil!!” = ‘S’ — Soltero

Al añadir !!” el optimizador no podrá utilizar el índice sobre la columna estado_civil.

También, suponiendo que el identificador del índice sobre la columna “nombre” es INXEMPLEADOSNOMBRE, podemos añadir el siguiente “hint” en nuestra sentencia SELECT:

SELECT * /*+ INDEX(empleados INXEMPLEADOSNOMBRE) */
FROM empleados
WHERE nombre = ‘Francisco’
AND estado_civil = ‘S’ — Soltero

Esto hace que el optimizador utilice el índice sobre la columna “nombre” aunque exista otro índice sobre la columna “estado_civil”.

Cómo usar la utilidad de trazado del SQL de Oracle

La utilidad de trazado del SQL de las bases de datos Oracle nos permite analizar el rendimiento de un determinado programa PL/SQL. Esta funcionalidad nos va a permitir obtener información acerca del rendimiento de todas las sentencias SQL que se ejecuten durante la ejecución del programa PLSQL.

Para utilizar la herramienta de trazado del PL/SQL de Oracle deberemos seguir cinco pasos:

1) Inicializar los parámetros relativos a esta funcionalidad SQL.
2) Activar la traza SQL.
3) Ejecutar la aplicación que queremos analizar y desactivar la traza cuando termine.
4) Formatear el fichero producido por la traza SQL con el comando TKPROF.
5) Interpretar la salida del comando TKPROF y, si es necesario, optimizar nuestro programa PLSQL.

En este artículo voy a hablar sobre los tres primeros apartados.

Inicialización de los parámetros de trazado Oracle

La utilidad de trazado puede, opcionalmente, proporcionar información acerca de los tiempos de ejecución. Para que esta información quede almacenada es necesario activar el parámetro TIMED_STATISTICS.

Dicho parámetro se puede activar a nivel de base de datos mediante su inclusión en el fichero de parámetros de la base de datos Oracle (nota: una vez incluido el parámetro es necesario reinicializar la base de datos para que el cambio tenga efecto):

TIMED_STATISTICS = TRUE

Este parámetro también se puede asignar dinámicamente a nivel de sesión ejecutando el siguiente comando:

SQL> ALTER SESSION SET timed_statistics=true;

La activación de este parámetro puede afectar ligeramente al rendimiento de la base de datos por lo que normalmente este parámetro está desactivado

Existen otros dos parámetros que nos permiten controlar el tamaño y el nombre del directorio donde se generará el fichero de trazado:

MAX_DUMP_FILE_SIZE = n

USER_DUMP_DEST = nombre_directorio

El valor por defecto del parámetro MAX_DUMP_FILE_SIZE es 500, es decir, que nuestro fichero de trazado podrá ocupar 500 bloques del disco duro. Este parámetro puede cambiar también a nivel de sesión con el comando ALTER SESSION.

El valor por defecto del parámetro USER_DUMP_DEST depende del sistema operativo y no puede ser cambiado a nivel de sesión. Por lo tanto, al ser un parámetro global del sistema, su valor sólo pueden cambiarlo los administradores de la base de datos utilizando el comando ALTER SYSTEM.

Para obtener información acerca de los valores que toman los distintos parámetros podemos ejecutar la siguiente sentencia:

SELECT name, value
FROM v$parameter
WHERE name LIKE ‘%dump%’

Obviamente se necesita tener acceso a la vista (view) V$PARAMETER para poder visualizar esta información.

Activación de la traza SQL

Podemos activar la traza a nivel de sistema mediante la inclusión en el fichero de parámetros de la siguiente línea:

SQL_TRACE = TRUE

Esta posibilidad es poco recomendable y no se debe implementar en ningún sistema en producción. La activación de la traza a nivel de sistema puede afectar seriamente el rendimiento de la base de datos Oracle.

Por ello es mucho más recomendable activar la traza a nivel de sesión con el comando:

SQL> ALTER SESSION SET sql_trace = true;

También se puede utilizar el paquete estándar DBMS_SESSION. Esta posibilidad es particularmente útil si queremos activar y desactivar la traza dentro del un procedimiento o función PL/SQL.

SQL> EXECUTE dbms_session.set_sql_trace (true);

Por otro lado, los DBA (Database Administrators) pueden activar la traza sobre una sesión de usuario concreta utilizando el siguiente comando:

SQL> EXECUTE dbms_system.set_sql_trace_in_session (session_id, serial_id, true);

Los valores session_id y serial_id adecuados los tiene que identificar el DBA mediante el análisis de los registros incluidos en la vista V$SESSION, los campos de esta vista que se corresponden con estos valores son SID y SERIAL#.

Desactivación de la traza SQL

Cuando la ejecución del programa que estamos optimizando termina, debemos proceder a desactivar la traza utilizando cualquiera de los métodos mencionados anteriormente, sustituyendo la palabra TRUE por FALSE.

Si la traza se activó a nivel de sesión, entonces cuando la sesión termina, la traza se desactiva automáticamente.

Identificación del fichero de trazado

Para identificar los ficheros de trazado debemos ir al directorio especificado por el parámetro USER_DUMP_DEST y, normalmente, el fichero de trazado será aquel que se ha generado más recientemente.

La identificación puede complicarse cuando hay varios usuarios generando ficheros de trazado al mismo tiempo. En este caso podemos utilizar un script estándar denominado readtrace.sql. Este script crea un procedimiento que abre nuestro fichero de trazado utilizando el paquete UTL_FILE. El nombre por defecto del fichero de trazado que se genera es username.trc pero puede cambiarse fácilmente.

SQL> @readtrace.sql
SQL> ALTER SESSION SET sql_trace = true;
SQL> SELECT * FROM nombre_tabla;
SQL> execute gettrace (‘nombre_fichero_trazado’);

El siguiente paso sería utilizar el programa TKPROF para poder interpretar el contenido binario del fichero de trazado, pero esto será objeto de otro artículo.

Sql loader

Sql Loader es una utilidad que proporciona Oracle para cargar datos a una base de datos desde un fichero externo, normalmente un fichero de texto aunque tambien pueden ser ficheros binarios.

Al SQLLoader ( sqlldr) se le pasan como parametros (los más importanes) el fichero que contiene los datos que se van a cargar y la ruta del fichero de control que contiene las acciones a realizar. El formato de los datos, donde se cargaran y cualquier otro tipo de control.

? Durante la ejecución genera tres tipos de ficheros:

 1.- Fichero Log.
2.- Fichero Bad.
3.- Fichero Discard.

Las características de estos ficheros son:

 1.- Log ? Muestra estadísticas de la carga.
2.- Bad ? Almacena los registros rechazados por datos incorrectos.
3.- Discard ?  Almacena los registros que no han cumplido los criterios de
selección de la cláusula WHEN.

 La cláusula WHEN carga aquellas filas que cumplen los criterios
especificados en ella.

? SQL-Loader utiliza dos mecanismos para cargar datos, estos mecanismos son:

 1.- Path Convencional.
2.- Path Directo.

1.- Path Convencional

Este mecanismo utiliza un buffer llamado BIND ARRAY. El mecanismo se basa en lo siguiente: Se leen varios registros que son almacenados en el Bind array, cuando este se llena o no quedan más datos para leer se pasan a Oracle para que inserte los datos en las tablas.

2.- Path Directo.

Este mecanismo consiste en colocar los registros en bloques de la Base de datos ya formatados y escritos directamente en la Base de datos, pasando por alto la mayor parte de procesamiento RDBMS.

Este mecanismo es más rápido que el convencional, pero tiene muchas más restricciones.

? Para realizar una carga correcta de la información se necesita un fichero que interprete los campos de los registros con el fin de almacenarlos de forma adecuada. Pues este fichero es el fichero de control.
? Por otra parte tenemos los datos. Estos pueden ir en el mismo fichero de control. Los datos se pueden cargar en diferentes formatos:

1.- Formato Binario ? No puede tratar campos o columnas de longitud variable.
2.- Formato Carácter ? Puede tratar tanto campos o columnas de longitud fija como      variable.

Para los formatos variables, el Sistema Operativo utiliza una marca de final de registro (como el newline).

? Existen dos tipos de limitadores que se utilizan para delimitar los campos:

1.- Terminated. ? Campos seguidos por un carácter especificado.
Ejemplo la coma: 1,2,3……

2.- Enclosed.  ? Campos precedidos y seguidos por un carácter especificado.
Ejemplo las comillas: “ENCLOSED”.

? Se distingue entre dos tipos de registros:

1.- Registros físicos ? Se consideran registros físicos cada linia del fichero de
donde deseamos cargar los datos a Oracle.

2.- Registros lógicos ? Se consideran registros lógicos cada fila de la tabla de
Base de datos.

 Se conoce como continuation fields aquella combinación de registros físicos
que forman un sólo registro lógico.

ESTRUCTURA DEL ARCHIVO DE CONTROL

El archivo de control suele tener como mínimo la siguiente estructura:

LOAD DATA

INFILE              ………………

INTO TABLE   ………………

[BEGIN DATA]
……………………….
……………………….
……………………….

Explicación del uso de las sentencias anteriores:

LOAD DATA ? Esta declaración es obligatoria en el fichero de control. Siempre debe aparecer al principio de este.

INFILE ? En este parámetro se especifica de donde se van a extraer los datos a almacenar en las tablas. Es decir, si se pone un “*”, le estamos diciendo al SQL-Loader que los datos se encuentran en el mismo fichero de control, justo después de la sentencia BEGIN DATA. Por otro lado, se puede indicar, al SQL-Loader, que coja los datos de un fichero de la siguiente manera: INFILE  ‘nombre_fichero’.

- Se pueden poner tantos INFILE´s como ficheros de datos deseemos cargar.

- Sí no se especifica la extensión del fichero en INFILE, por defecto se utilizará “.dat”.

Ejemplos:

? Carga de datos a partir de unos datos incluidos en el fichero de control:

INFILE *
…
…
BEGIN DATA
…………..
…………..
? Carga de datos a partir de un fichero de datos especificado:

INFILE ‘C:\fichero_de_datos.dat’
…..
…..

[BEGIN DATA] ? No se debe poner

INTO TABLE   ?  En esta sentencia, se especifican las tablas que seran cargadas con la información. Dependiendo del tipo de carga que se vaya a realizar, se deben especificar los campos (columnas) de la tabla y su correspondiente formato.

[BEGIN DATA]   ?  Se pone esta sentencia cuando los datos a cargar en las tablas se encuentran en el fichero de control. Esta sentencia se debe poner siempre despues de la última especificación de control, sino es así, podría suceder que se interpretasen datos como sentencias de control.
Seguidamente, se muestra un fichero de control más detallado y con los parámetros más usuales:

LOAD DATA   ? Sentencia obligatoria

INFILE ‘<fichero_datos.dat>’
[INFILE]        ‘<fichero_datos2.dat>’
…………
…………
[BADFILE] ‘<fichero_bad.bad>’

[DISCARDFILE] ‘<fichero_discard.dsc>’

[INSERT]  ? Por defecto, la tabla debe estar vacia sino dá error.
[APPEND]  ? Añade nuevas filas a la tabla.
[REPLACE]  ? Elimina las filas existentes y añade las nuevas.

INTO TABLE   <nombre_tabla>

[BEGIN DATA]  ? Obligatorio sí y sólo sí, se ha especificado ‘*’ en  INFILE.

[WHEN] ? Obligatorio sí y sólo sí,  se ha declarado DISCARDFILE.

¿Cómo ejecutar SQL-Loader?

Dependiendo de la versión de Oracle en la que estemos trabajando, existe un determinado SQL-Loader. Normalmente, el fichero ejecutable para Windows NT suele llamarse sqlldrXX.exe donde XX es el número de la versión del producto. Este fichero se suele almacenar en el directorio: C:\orant\bin .

Ejemplos:

 Para la versión de Oracle 7.3.4.0.0 seria sqlldr73.exe
Para la versión de Oracle 8.0.5.0.0 seria sqlldr80.exe
Etc…

Para ejecutar el SQL-Loader, debemos llamar al programa anterior desde la linia de comandos. La llamada seria algo como:

 C:\> sqlldrXX argumento=valor [, argumento=valor,] …

Para saber que argumentos podemos utilizar con SQL-Loader, podemos utilizar la ayuda de la siguiente manera:

 C:\> sqlldrXX help=y

Imaginemos que los argumentos que estamos utilizando, son los que  utilizamos en la mayoría de ejecuciones de SQL-Loader. Pues, existe una manera de hacer que estos argumentos sean almacenados en un fichero externo y que por medio de la cláusula PARFILE sea llamado como si estuviese incluido en el mismo fichero de control. Este método nos ahorra el tener que escribir largas líneas de argumentos en la línea de comandos. Además de facilitar su entendimiento.

Lista de Argumentos

USERID ? Se debe poner el usuario y clave de Oracle, sino se pone el programa lo solicita.

CONTROL ? Se debe poner el nombre del fichero de control. La extensión es .CTL.

LOG ? Se debe poner el nombre del fichero de Log que guarda información del proceso de carga. La extensión por defecto es .LOG.

BAD ?  Se debe poner el nombre del fichero donde se almacenarán las causas de los errores que provocaron las inserciones durante la carga. La extensión por defecto es .BAD.

DATA ?  Se debe poner el nombre del fichero de datos a cargar. Por defecto se coge el nombre del fichero de control + la extensión .DAT.

[DISCARD] ? Nombre del fichero opcional de descartados. La extensión es .DSC.

[DISCARDMAX] ? Parámetro que índica el número de descartados que se permiten antes de acabar la carga. Para parar en el primer registro se debe poner “0″. Por defecto su valor es “todos”.

[SKIP] ? Especifica el número de registros o filas que desde el principio del fichero no deben ser cargados en la ejecución del SQL-Loader. Por defecto es “0″.

[LOAD] ? Especifica el número máximo de registros que serán cargados, después de saltar los especificados en la cláusula SKIP. Por defecto cargará todos.

[ERRORS] ? Especifica el número máximo de errores que permiten que se produzcan antes de acabar la carga. Para no permitir ningún error poner =0, para permitirlos todos poner un valor extremadamente elevado. Por defecto, este argumento permite 50 errores.

[ROWS] ? En Path convencional, este argumento especifica el número de filas del BIND ARRAY (por defecto 64). Por otra parte, en Path directo este argumento índica la cantidad entera de registros que deseamos cargar en la Base de datos (Por defecto es salvar todos de una sóla vez al final de la carga).

[BINDSIZE] ? Especifica el tamaño máximo en Bytes del BIND ARRAY, este argumento machaca el valor del argumento ROWS.

[SILENT] ? Suprime los mensajes que aparecen durante la ejecución. Tales como cabeceras, pies de página, errores, descartes etc…

[DIRECT] ? Índica el modo Path a utilizar. Si su valor es TRUE usará el Path Directo, si el valor es FALSE usará el Path Convencional (Por defecto).

[PARFILE] ? Especifica el nombre del fichero que contiene otros parámetros de la línea de comandos.