Buenas practicas PL / SQL

Pauta para variables y estructuras de datos

Declarar, definir el tipo, inicializar y asignar valor por defecto a las estructura de datos antes de trabajar con ellas

PL/SQL es un lenguaje fuertemente tipificado. Esto significa que antes de trabajar con cualquier tipo de estructura de datos, es necesario declarar las mismas, definir el tipo y opcionalmente inicializarlas o asignarles un valor por defecto. Las declaraciones deben realizarse en la sección de declaración de variables de un bloque anónimo, procedimiento, función o paquete.

En las declaraciones que se relacionan a tablas y columnas utilizar la cláusula

%TYPE y %ROWTYPE.

Esta forma permite al código adaptarse a cambios de estructuras de datos yautodocumentarse, ya que al leer el código se puede saber a qué tipo de dato hace referencia. Para el tipo VARCHAR2, Oracle separa la memoria necesaria según la longitud definida. Es muy común ‘Hardcodear’ la longitud del mismo a su máxima tamaño para evitar problemas, a costo de utilizar más memoria de la necesaria. Aquí nuevamente se hace conveniente el uso de%TYPE o %SUBTYPE para un mejor aprovechamiento de los recursos de memoria.

En los datos numéricos, definir la precisión.

Oracle soporta hasta 38 dígitos de precisión en los tipos NUMBER .

Si no se define en forma correcta se está derrochando memoria.

Siempre que sea posible, utilizar el tipo de dato RECORD para manejar estructuras.

La declaración de variables individuales o dispersión de datos, muchas veces, complica  la lectura del código. La agrupación de los mismos bajo estructuras, facilita la administración y el mantenimiento del código.

Los errores de un código solo pueden ser capturados en la sección ejecutable de un bloque. Si la inicialización de una variable en la sección de declaración falla, el error no se puede manejado. Para ello hay que asegurar que la inicialización lógica no falle y esto se asegura haciendo las inicializaciones al comienzo de la sección de ejecución. Si se produce un error, se puede capturar el error y decidir su tratamiento.

Reemplazar expresiones complejas con variables booleanas y funciones.

Las expresiones booleanas se pueden evaluar con tres valores: TRUE, FALSE o NULL

.Se pueden usar variables de este tipo para ocultar expresiones complejas. Como consecuencia se puede leer más fácilmente el código y es más simple su mantenimiento.

Tener cuidado con las conversiones implícitas de tipos de datos.

Si bien es sabido que PL/SQL maneja las conversiones implícitas, existen al menos dos grandes problemas con esto. Las conversiones no son intuitivas, a veces se realizan de formas no esperadas y ocurren problemas, especialmente dentro de sentencias SQL. Las reglas de conversión no están bajo el control de desarrollador. Pueden cambiar con el upgrade a una versión de Oracle o con el cambio de parámetros como NLS_DATE_FORMAT.

  

Se puede convertir con el uso explícito de funciones como son:

TO_DATE, TO_CHAR,TO_NUMBER y CAST.

Pauta para declaración y uso de variables de paquetes.

Agrupar los tipos de datos, evitar su exposición y controlar su manipulación.

Los paquetes requieren ciertos tipos de recaudos en la forma de declaración y uso de variables. Agrupar los tipos de datos, evitar su exposición y controlar su manipulación permiten obtener las ventajas del uso de los mismos.

Definir las constantes que son referenciadas por toda la aplicación en un paquete único.

Definir las constantes que corresponden a un área específica dentro de un paquete que encapsula esa funcionalidad nunca colocar literales ‘en duro’, como ‘SI’ o 150 en el código. Es conveniente crear un paquete para mantener estos nombres y valores publicados en reemplazo de los literales.

Este tipo de práctica permite que el código no luzca ‘hard codeado’, lo cual lo hace más legible y mantenible y además evita que los literales sean modificados.

Centralizar las definiciones de Types en las especificaciones del paquete. A medida que se usan los features del lenguaje, se definirán distintos TYPE entre los que podemos incluir:

SUBTYPEs que definen tipos específicos de la aplicación.

Collection TYPEs, como lista de números, fechas y records.

Cursores referenciados

Esto permite tener estandarizados los tipos de datos para que sean usados por múltiples programas. Los desarrolladores pueden escribir más rápidamente y disminuir los bugs. También simplifica el mantenimiento de los types, ya que solo es necesario modificarle/los paquetes donde están declarados.

Disminuir el uso de variables globales en paquetes y en caso de hacerlo, solo en el cuerpo del paquete. Una variable global es una estructura de datos que se puede referenciar fuera del alcance o bloque en donde está declarada. Cuando se declara una variable en un paquete, existe y retiene su valor durante la duración de la sesión. Son peligrosas porque crean dependencias ocultas o efectos laterales. El seguimiento delas mismas es complejo, ya que es necesario ver la implementación para ver sus instancias. Una solución general para esto es pasar estas variables globales como un parámetropara no referenciarlas directamente en el programa.

Exponer las variables globales de los paquetes usando ‘get and set’. Cualquier estructura de datos declarada en la especificación del paquete puede ser referenciada por cualquier programa con autorización de EXECUTE. Esto hace que en forma deliberada se pueda hacer uso de esas estructuras globales. Para evitar eso, es conveniente declarar estos datos en el cuerpo del paquete y proveer al paquete de métodos get y set, declarados en la especificación. Esta forma le permite a los desarrolladores, acceder a los datos a través de estos programas y manipular los datos según las reglas de estos procedimientos.

Pautas para el uso de estructuras de Control

Las estructuras de control pueden convertirse en un punto de complejidad de un código. La normalización del uso de RETURN y el EXIT garantiza rapidez en la comprensión de las lógicas escritas y facilidad en su mantenimiento.

Nunca se sale de una estructura repetitiva con RETURN o con EXIT. Cada estructura repetitiva tiene un punto de control por donde se debe producir su salida.

Un FOR loop itera desde el valor de comienzo hasta el valor de terminación (ciclo N).

Un WHILE loop itera mientras no se cumpla la condición de terminación. (Ciclo0).

Existe un LOOP con control de la condición al final de ciclo (ciclo 1). Es LOOP … EXIT WHEN condición lógica.

Combinando estos ciclos se puede hacer que las estructuras tengan un único punto de control para el ciclo repetitivo. Esto facilita la lectura, comprensión y mantenimiento del programa y permite hacer modificaciones en forma más simple.

Una función debe tener un único RETURN exitoso como última línea de la sección ejecutable. Normalmente, cada manejador de excepciones puede retornar un valor. En los programas largos donde se evalúan muchas condiciones, la existencia de múltiples salidas dificulta el entendimiento y mantenimiento del código.

Nunca declarar el índice de un FOR .. LOOP.

PL/SQL ofrece dos tipos de FOR LOOP: Numéricos y cursores.

Ambos tienen este formato general:

FOR indice_loop IN loop range LOOP

Loop body

END LOOP;

El índice del loop se declara implícitamente durante la ejecución. El ámbito de existencia está restringido al cuerpo del loop. Si se declarase el índice, se estaría generando otra variable completamente separada que en el mejor de los casos nunca será usada, pero si se usa fuera del loop, puede introducir errores.

Pautas para el manejo de excepciones

Aunque se escriba un código perfecto, que no contenga errores y que nunca realice operaciones inapropiadas, el usuario podría usar el programa incorrectamente, produciendo una falla que no estaba contemplada. El uso de excepciones permite capturar y administrar los errores que se pueden producir dentro del código. Su buen uso trae como resultado un código con menos bugsy  más fácil de corregir.

Establecer los lineamientos para el manejo de errores antes de comenzar a codificar. Es impráctico definir secciones de excepciones en el código después de que el programa fue escrito. La mejor forma de implementar un manejo de errores en toda la aplicación es usando paquetes que contengan al menos los siguientes elementos.

Procedimientos que realicen el manejo de tareas de excepciones, como por ejemplo escribir un log de errores.

Un programa que oculte la complejidad de RAISE_APPLICATION_ERROR y los números de aplicación-error.

Una función que retorne el mensaje de error para un código de error.

Utilizar el modelo por defecto de manejo de excepciones para propagar la comunicación de errores. Aprovechar la arquitectura de manejo de errores de PL/SQL y separar las excepciones en bloques.

La sección de código ejecutable debe estar limpia, simple y fácil de seguir. No es necesario controlar el estado después de cada llamada a un programa. Simplemente debe incluirse una sección de excepción, capturar el error y determinar la acción a realizar.

Capturar todas las excepciones y convertir el significado de los códigos de error en los retornos a programas que no son PL/SQL. Cuando los programas PL/SQL son llamados por otros lenguajes de programación (Java, Visual Basic, etc.), al menos es necesario regresar el estado de error (código y mensaje)para que administren los mismos .Una manera sencilla de hacer esto es sobrecargar el programa original con otro del mismo nombre y dos parámetros adicionales.

Los desarrolladores pueden llamar al procedimiento que deseen y chequear los errores de la manera más apropiada para sus programas.

Usar procedimientos propios de RAISE  en lugar de las llamadas explicitas a

RAISE_APPLICATION_ERROR. Cuando se está controlando excepciones de sistemas como

NO_DATA_FOUND, se usa RAISE, pero cuando se quiere controlar un error de aplicación específico, se usa RAISE_APPLICATION_ERROR. Para el último caso, se debe asignar un número de error y mensaje, lo cual termina en un ‘hardcodeo’.Para ello se puede utilizar constantes para especificar un número. Una manera más clara es proveer un procedimiento que automáticamente controle el número de error y determina la forma de ejecutar el error.

Los desarrolladores no tienen que determinar cuál es el número de error que tienen que usar, solo pasan la constante que desean usar y dejan que la rutina determine cuál es el RAISE correspondiente.

No sobrecargar una EXCEPTION con múltiples errores al menos que la pérdidade información sea intencional.

No declarar una excepción genérica como ERROR_GENERAL y luego ejecutar la excepción en diferentes circunstancias. La lectura del código traerá problemas alintentar cual fue la causa de problema. La excepción NO_DATA_FOUND se puede utilizar para indicar que no se encontraron filas en la ejecución de un query o para indicar que se llegó al final de un archivo.

No Manejar excepciones que no pueden evitarse, pero si anticiparse. Si cuando se está escribiendo un programa, se puede predecir que un error va a ocurrir, se debería incluir un manejador para ese caso.

Evitar exponer código en duro para el manejo de errores, reemplazar por procedimientos que administren los mismos. Evitar exponer código en duro para el manejo de errores, reemplazar por procedimientos que administren los mismos. La mejor manera de registrar en forma consistente y tener mantener la calidad del seguimiento de errores por toda la aplicación es ofreciendo un conjunto de procedimientos predefinidos que canalicen el manejo de los mismos. Es fundamental que el equipo de desarrollo use siempre solamente estos procedimientos en sus cláusulas WHEN.

Usar nombres constantes para flexibilizar los números y mensajes de errores de aplicación, Oracle define 1000 números de error, entre –20000 y –20999 para usar para nuestra aplicación. Se recomienda definir todos los errores en una tabla o un archivo del sistema operativo, construir paquetes para manipular esos datos y ejecutar el RAISE usando esos nombres y no códigos en duro.

Usar WHEN OTHERS solo para excepciones desconocidas que deben serejecutadas. No es conveniente usar WHEN OTHERS para cualquier error. Si se puede conocer el tipo de excepción que se quiere ejecutar, es conveniente administrarla en forma específica.

Pautas mínimas para uso de SQL dentro de PL/SQL

La escritura de SQL dentro del código es uno de los aspectos más flexibles de PL/SQL y también puede ser uno de los más peligrosos. El establecimiento de reglas claras para su inclusión permite asegurar beneficios y buenos resultados.

Usar transacciones autónomas para aislar los efectos del ROLLBACK y COMMIT Permite grabar o realizar rollback dentro de un código PL/SQL sin afectar la sesión principal.

No utilizar COMMIT o ROLLBACK dentro de los procedimientos PL/SQL que son utilizados por procesos en otros leguajes. El COMMIT o ROLLBACK debe utilizarse dentro del proceso que llama los procedimientos PL/SQL. Cuando los procedimientos PL/SQL son llamados por otros lenguajes de programación (Java, Visual Basic, etc.) no deben utilizar las sentencias de manejo de transaccionesCOMMIT o ROLLBACK de modo de posibilitar un ordenado control de transacciones.

Usar COUNT solo cuando es necesario saber el número de ocurrencias. No usar

COUNT para saber si hay una o más de una fila para una condición dada. Solo se debe usar

COUNT para saber cuántas filas hay.

Usar cursores implícitos para realizar operaciones sobre muchos registros este constructor realiza la apertura y cierre en forma automática. Es fácil de usar y su lectura es más simple.

Nunca usar cursores implícitos para una fila El FOR LOOP está diseñado para múltiples filas de un cursor. Para un solo registro es más eficiente un SELECT INTO o un cursor explícito.

Parametrizar cursores explícitos. Los cursores retornan información como lo haría una función, por lo cual también pueden aceptar parámetros. Si se define un cursor con parámetros, se puede hacer quesea reutilizable en diferentes circunstancias de un programa. Esto es mejor si se define en un paquete.

Usar RETURNING para obtener información sobre filas modificada

Referenciar atributos del cursor inmediatamente después de ejecutar la operación de SQL. Las sentencias INSERT, UPDATE y DELETE se ejecutan como cursores implícitos en PL/SQL. Es decir, no se puede declarar, abrir y procesar este tipo de operaciones en forma explícita. El motor de Oracle SQL toma el control y administración del cursor. Se puede obtener información acerca de los resultados de estas operaciones implícitas más recientemente utilizada consultando los siguientes atributos de cursor.

SQL%ROWCOUNT: Números de filas afectadas por la instrucción DML.

SQL%ISOPEN: Siempre es falso en los manejos de cursores implícitos.

SQL%FOUND: Retorna TRUE si la operación afecta al menos una fila.

SQL%NOTFOUND: Retorna FALSE si la operación afecta al menos una fila. Hay un solo set de atributos SQL% en una sesión, el cual refleja la última operación implícita realizada. Por lo cual el uso de estos atributos deberá hacerse con el mínimo uso de código entre la operación y la referencia al atributo. De otra manera el valor retornado por el atributo podría no corresponder al SQL deseado.

Usar BULK COLLECT para mejorar la performance de los querys multiregistros. En algunas oportunidades es necesario recuperar un gran número de filas desde la base de datos.

BULK COLLECT permite obtener el set de filas en una sola llamada al motor y recuperar el resultado en un arreglo (COLLECTION).Para usar este método es necesario declarar un arreglo para mantener los datos recuperados y luego anteponer al INTO la cláusula BULK COLLECT.

Esta forma de recuperación de datos mejora (en algunos casos enormemente) la performance del query .Se debe tener cuidado cuando el

SELECT retorna miles de filas ya que si el mismo programa está corriendo con diferentes usuarios en una misma instancia, puede haber problemas de memoria. Se podría restringir usando ROWNUM o LIMIT para cada instancia.

Usar FORALL para mejorar la performance de DML basados en arreglos.

  

Para los casos en que es necesario modificar (INSERT, DELETE o UPDATE) un gran número de filas en una base de datos dentro de un PL/SQL, se recomienda la sentencia FORALL

. La performance mejora notablemente ya que se reduce el número de llamadas entre PL/SQL y el SQL engine.

Reglas generales para performance

Para el manejo de datos dentro de un código PL/SQL es necesario tener en cuenta las siguientes premisas.

Una sentencia SQL es más rápida que un programa. Siempre es preferible utilizar la potencia del SQL antes que emular su funcionamiento con un PL/SQL largo y complejo.

Un programa pequeño es más rápido que uno extenso. El uso de programas largos agrega más tiempo de ejecución. El uso de paquetes pequeños permite al servidor disponer de ellos de manera más rápida y eficiente

Evitar llamadas repetitivas al sql engine Mientras menos solicitudes de servicios se hagan al sql engine, más rápido se ejecutará un programa. Para el caso de operaciones de SQL similares o repetitivas, utilizarFORALL, BULKS, variables BIND, etc.