NORMATIVA DE PL/SQL

Buenas prácticas.
Control de Calidad
Enero 2015
Versionado
Versión Fecha Descripción Generado por Revisado por Aprobado por
v1 06/01/2015 Creación del Documento.
Ciclo de Vida
QA
v2 10/02/2015 Mejorar Escenarios,
Ejemplos, Fundamentos.
ARQ ARQ;QA
v2 13/02/2015 Mejorar Documento DBA DBA v3 20/02/2015 Formato y Redacción. Jefe Control
Calidad
Jefe QA
V4 25/02/2015 Aprobación QA;ARQ; DBA
V5 29/01/2015 Type of data, For ALL
Pruebas de Cargas, Alcances Proy. Y Mant.
QA DBA; ARQ
Fecha Publicación Fecha Próxima Revisión
06/enero/2015 06/enero/2019

Tabla de contenido
OBJETIVO .............................................................................................................................
................ 4

ALCANCE ..............................................................................................................................
................ 4

NORMATIVAS .......................................................................................................................
............... 5

PL/SQL TIPOS DE
DATOS.............................................................................................................. 5

CÓMO TRABAJAR CON CADENAS EN

PL/SQL: ............................................................................. 5

CÓMO TRABAJAR CON NÚMEROS EN PL /

SQL ......................................................................... 11

CÓMO TRABAJAR CON FECHAS EN

PL/SQL: .............................................................................. 13

CAMBIOS DE CONTEXTOS (Entre PL/SQL y

SQL). .................................................................... 16
ACCEDER EXCESIVAMENTE A LA TABLA
DUAL ......................................................................... 17

EXCESIVAS LLAMADAS DE
FUNCIONES ..................................................................................... 19

LLAMADAS DE DATABASE
LINK ................................................................................................. 20

BULK COLLECT AND

FORALL ...................................................................................................... 20

EXCESIVOS
COMMITS ................................................................................................................ 22

CONSULTAS
DINAMICAS ........................................................................................................... 23

QUERY
DINAMICA .................................................................................................................
23

VALIDAR
PACKAGE ....................................................................................................................
25

CABECERA ................................................................................................................
.............. 25

SENTENCIAS DDL EN
PACKAGES. ............................................................................................... 25

DDL .............................................................................................................................
........... 25

SENTENCIA INSERT
INTO ........................................................................................................... 26

SENTENCIA
SELECT .................................................................................................................... 26

SELECT
* ................................................................................................................................
26

VARIABLES ....................................................................................................................
............. 27

NOMENCLATURA DE LAS
VARIABLES .................................................................................... 27
 VARIABLES CONSTANTES, REUTILIZACION DE
VARIABLES ........................................................ 28

EXCEPCIONES EN PL
SQL ........................................................................................................... 28

EXCEPCIÓN ...................................................................................................................
............. 29

REGLAS DE
ALCANCE ................................................................................................................. 29

EXCEPCIONES
PERSONALIZADAS ............................................................................................... 31

HINTS .............................................................................................................................
............ 31
ADMINISTRACIÓN DE
CURSORES: ............................................................................................. 31
PARA LA SENTENCIA SELECT-INTO: .......................................................................................
32
ASIGNAR UN CURSOR EXPLÍCITO: .........................................................................................
32
CURSOR FOR LOOP ................................................................................................................
32
EXECUTE IMMEDIATE INTO PARA QUERYS DINÁMICAS. ......................................................
32
VARIABLE DE CURSOR ...........................................................................................................
32
CHEQUEAR CURSORES ..............................................................................................................
34
LAS RESTRICCIONES A LAS VARIABLES DE
CURSOR ............................................................... 34
ATRIBUTOS DE LOS
CURSORES .................................................................................................. 35
TUNNING A
QUERYS: ......................................................................................................................... 36
ALCANCE DE
MANTENCIONES ....................................................................................................... 36
ALCANCE DE
PROYECTOS .............................................................................................................. 37
BIBLIOGRAFIA: ..............................................................................................................................
..... 37
LINK DE APOYO: ........................................................................................................................
37
LIBROS DE APOYO: ....................................................................................................................
37
ANEXO A. PROCEDIMIENTO DE PRUEBAS DE
CARGAS ................................................................. 39
ANEXO B: ANALIZANDO EL PLAN DE
EJECUCIÓN .......................................................................... 39
ANEXO C: LA LECTURA DE UN PLAN DE
EJECUCIÓN ..................................................................... 40
ANEXO D: INESTABILIDAD DE LOS PLANES DE
EJECUCIONES ...................................................... 42
Normativa de PL/SQL. Buenas practicas

Página 3 de 42 3

OBJETI
VO
Normalizar y estandarizar todo desarrollo de aplicaciones que tengan directa relación con las bases de
datos de XXXX. Sentar las bases restrictivas en desarrollos de aplicaciones que tienen directa interacción
con las bases de datos. Esto referente a la construcción de sentencias SQL y PL/SQL encontradas en
packages, triggers, Store Procedure, Function, variables, cursores, SQL, etc. La definición de estos
estándares, tiene como fin reconocer y analizar las sentencias y el costo que tienen asociado en la base
de datos.

Sin duda, estas restricciones nos ayudan a estandarizar el desarrollo de este tipo de componentes y
normar su utilización bajo las buenas prácticas.

ALCAN
CE
Este documento aplica para todo tipo de revisión tanto para los sistemas nuevos y sistemas legados
implementados en CCLA.

Este proceso se aplicará a toda la infraestructura soportada por IT, tales

como:

✓ Base de Datos Oracle (BD). Esto aplica para todo código PL/SQL que interactué con
las
estructuras definidas a nivel de base de datos (tablas, funciones, packages, DMLs en
general)

Es muy importante establecer que esta información está relacionada al tipo de plataforma Oracle
actual en la compañía, en consecuencia este documento permanecerá en constante modificación
conforme a las mejoras de plataforma y también en una mejora continua.
NORMATIVAS
Los estándares a controlar por el área de Control de Calidad en el desarrollo de aplicaciones y los posteriores
pasos a los ambientes QA de Oracle, son las siguientes:
PL/SQL TIPOS DE DATOS.
CÓMO TRABAJAR CON CADENAS EN PL/SQL: Hay tres tipos de cadenas en PL / SQL:
• Las cadenas de longitud fija.
• Las cadenas de longitud variable. Se especifica una longitud máxima de la cadena (y que no debe ser mayor
que 32.767).
• objetos grandes de caracteres (BLOB). CLOBs son cadenas de longitud variable que puede ser de hasta 128
terabytes.
Declarando cadenas.
DECLARE l_company_name VARCHAR2(100);
NOTA: Debe proporcionar la longitud máxima; si lo deja fuera, base de datos Oracle lanza un error de
compilación, como se muestra a continuación.
SQL> DECLARE
2 l_company_name VARCHAR2; 3 BEGIN 4 l_company_name := 'Oracle Corporation'; 5 END; 6 /
l_company_name VARCHAR2; * ERROR at line 2: ORA-06550: line 2, column 21: PLS-00215: String length
constraints must be in range (1 .. 32767)
Para declarar una cadena de longitud fija, utilice el tipo de datos CHAR:
DECLARE l_yes_or_no CHAR(1) := 'Y';
Con CHAR (a diferencia con VARCHAR2) que no tiene que especificar una longitud máxima para una variable
de longitud fija. Si deja fuera de la restricción de longitud, Oracle utiliza automáticamente una longitud máxima
de 1. En otras palabras, las dos declaraciones son idénticas a continuación:
DECLARE l_yes_or_no1 CHAR(1) := 'Y';
l_yes_or_no2 CHAR := 'Y';
Normativa de PL/SQL. Buenas practicas

Página 5 de 42 5
Por último, para declarar un objeto grande de caracteres, utilice el tipo de datos CLOB. No se especifica
una longitud máxima; la longitud se determina automáticamente por Oracle de base de datos y se basa
en el tamaño del bloque de base de datos. Aquí hay un ejemplo:

DECLARE l_lots_of_text CLOB;

Así que, ¿cómo determinar qué tipo de datos a utilizar en sus programas? Aquí hay algunas
pautas:

• Si la cadena podría contener más de 32.767 caracteres, utilice el tipo de datos CLOB (o
NCLOB).
• Si el valor asignado a una cadena siempre tiene una longitud fija, utilice CHAR (o
NCHAR).
• De lo contrario (y, por lo tanto, la mayoría de las veces), utilice el tipo de datos VARCHAR2 (o
NVARCHAR2, cuando se trabaja con datos Unicode).

Utilizando el tipo de datos CHAR para nada más que cadenas que siempre tienen un número fijo de
caracteres puede conducir a resultados inesperados e indeseables. Ejemplo el siguiente bloque, que
mezcla cadenas de variables y de longitud fija:
 1.1. Usando las funciones incorporadas con
cadenas:

Concatenar varias cadenas. Una de las operaciones más básicas y se necesitan con frecuencia en
cadenas es combinar o concatenar juntos. PL / SQL ofrece dos maneras de hacer esto:

• La función integrada CONCAT

• El operador || (Concatenación)
Ejemplo:
DECLARE
l_first VARCHAR2 (10) := 'Steven'; l_middle VARCHAR2 (5) := 'Eric'; l_last VARCHAR2 (20) := 'Feuerstein'; BEGIN
DBMS_OUTPUT.put_line (CONCAT ('Steven', 'Feuerstein')); DBMS_OUTPUT.put_line ( l_first|| ' '|| l_middle|| ' '||

l_last); END; / Cambiar el caso de una cadena. Tres funciones incorporadas cambian el caso de caracteres

de una cadena:
UPPER: Cambia todos los caracteres en mayúsculas. LOWER: Cambia todos los caracteres en minúsculas.
INITCAP: Cambia el primer carácter de cada palabra en mayúsculas.
Ejemplo:
Extraer parte de una cadena:
Una de las funciones integradas más comúnmente utilizados para las cadenas es SUBSTR, que se utiliza para
extraer una sub-cadena de una cadena. Cuando se llama a SUBSTR, que proporcione la cadena, la posición
en la que comienza la sub-cadena deseada, y el número de caracteres de la sub-cadena.
Ejemplo:
Normativa de PL/SQL. Buenas practicas

Página 7 de 42 7

Encuentra una cadena dentro de otra

cadena.

Utilice la función INSTR para determinar dónde (y sí) una cadena aparece dentro de otra cadena.
INSTR acepta hasta cuatro argumentos:
• La cadena que desea buscar (requerido).
• La sub-cadena de interés (requerido).
• La posición inicial de la búsqueda (opcional). Si el valor es negativo, contar desde el final de la
cadena. Si no se proporciona ningún valor, base de datos Oracle comienza al principio de la
cadena; es decir, la posición de partida es 1.
• La enésima ocurrencia de la sub-cadena (opcional). Si no se proporciona ningún valor, base de datos
Oracle busca la primera ocurrencia. Ejemplo:
 Rellena una cadena con espacios (u otros
caracteres).

Hay veces, sobre todo cuando la generación de informes, cuando se quiere poner espacios (u otros
caracteres) delante o después del final de la cadena. Para estas situaciones, base de datos Oracle
ofrece LPAD y RPAD en la declaración.

Ejempl
o:
Reemplazar caracteres de una cadena.
Base de Datos Oracle proporciona una serie de funciones que le permiten cambiar selectivamente uno o más
caracteres de una cadena. Puede que sea necesario, por ejemplo, para reemplazar todos los espacios en una
cadena con el equivalente HTML ("") por lo que el texto se muestra correctamente en el navegador. Dos
funciones se encargan de este tipo:
• REPLACE: Sustituye a un conjunto o patrón de caracteres con otro conjunto.
• TRANSLATE: Se traduce o sustituir caracteres individuales.
Ejemplo:
Quitar los caracteres de una cadena. Lo de LPAD y RPAD da, RTRIM, LTRIM, y RTRIM quita espacios.
Utilice estas funciones de ajuste para eliminar caracteres, ya sea del principio (izquierda) o al final (derecha)
de la cadena.
Ejemplo:
Normativa de PL/SQL. Buenas practicas

Página 9 de 42 9

NOTA: Debe especificar una longitud máxima cuando se declara una variable en función del tipo
VARCHAR2. ¿Qué ocurre, entonces, cuando intenta asignar un valor a la variable cuya longitud es
mayor que el máximo? Base de Datos de Oracle eleva el error ORA-06502, que también se define en
PL / SQL como la excepción VALUE_ERROR.
CÓMO TRABAJAR CON NÚMEROS EN PL / SQL
1.2. Los números en PL / SQL
PL / SQL ofrece una variedad de tipos de datos numéricos para adaptarse a diferentes propósitos:
NÚMERO. Un cierto tipo de datos decimal que es ideal para trabajar con cantidades monetarias. NÚMERO es
el único de los tipos numéricos PL / SQL que se ejecutan de manera independiente de la plataforma.
PLS_INTEGER. tipo de datos entero conforme a la representación entera subyacente de su hardware. La
aritmética se realiza con las instrucciones nativas de la máquina de su hardware. No se puede almacenar
valores de este tipo en las tablas; se trata de un / tipo de datos SQL PL específica. SIMPLE_INTEGER.
Introducido como de Oracle 11g Release base de datos 1. Los resultados SIMPLE_INTEGER de tipo de datos
en los tiempos de ejecución significativamente más cortos para el código compilado de forma nativa. Este tipo
de datos no se analiza en este artículo. BINARY_FLOAT y BINARY_DOUBLE. Simple y doble precisión, IEEE-
754, los tipos de datos de punto flotante binario. Estos tipos de datos binarios son altamente especializados y
son útiles cuando se necesita para mejorar el rendimiento de las operaciones de computación intensiva.
Funciones Internas Numéricas.
ROUND: La función ROUND acepta un número y devuelve otro número redondeado al número especificado
de posiciones a la derecha del punto decimal. Si no se especifica ese número, REDONDO devolverá un
número redondeado al entero más cercano.
Ejemplo:
Normativa de PL/SQL. Buenas practicas

Página 11 de 42 11

TRUN
C

TRUNC es similar a la función ROUND, en la que se puede especificar el número de dígitos a la

derecha o la izquierda del punto decimal. La diferencia es que TRUNC simplemente elimina o dígitos
truncado. Y, al igual que ROUND, puede especificar un número negativo, que trunca dígitos (les hace
cero) a la izquierda de la coma decimal.

FLOOR, CEIL, MOD Y

REMAINDER

FLOOR: función devuelve el mayor entero igual o menor que el número especificado. CEIL: devuelve el
entero más pequeño igual o mayor que el número especificado. MOD y REMAINDER: devuelven el resto
de un número dividido por otro, pero que resto se calculan de forma diferente para cada función.
 TO_CHAR: Utilice TO_CHAR para convertir un número
en una cadena.
CÓMO TRABAJAR CON FECHAS EN PL/SQL:
Una fecha es también un tipo de datos considerablemente más compleja que una cadena o un número.
Cuenta con múltiples partes (año, mes, día, hora, etc.), y hay muchas reglas acerca de lo que constituye una
fecha válida.
Se necesita muy a menudo trabajar con fechas en las siguientes situaciones.
• Declarar variables y constantes de fechas
• Utilizar las funciones incorporadas para visualizar y modificar los valores de fecha
• Realizar cálculos sobre fechas
Hay tres tipos de datos que puede utilizar para trabajar con fechas y horas:
DATE: Este tipo de datos almacena una fecha y una hora, resolvió el segundo. No incluye la zona horaria.
FECHA es el tipo de dato más antiguo y más utilizado para trabajar con fechas en las aplicaciones de Oracle.
TIMESTAMP: Son similares a las fechas, pero con estas dos distinciones fundamentales: (1) se pueden
almacenar y manipular los tiempos resueltos a la mil millonésima parte de segundo más cercana (9 decimales
de precisión), y (2) se puede asociar una vez zona con una marca de tiempo y de base de datos Oracle se
llevará a esa zona horaria en cuenta en la manipulación de la marca de tiempo.
INTERVAL: Considerando que el registro de fecha y hora de un punto específico en el tiempo, los registros de
intervalo y calcula un tiempo de duración. Se puede especificar un intervalo en términos de años y meses, o
días y segundos.
Ejemplo:
DECLARE
l_today_date DATE := SYSDATE; l_today_timestamp TIMESTAMP := SYSTIMESTAMP; l_today_timetzone
TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; l_interval1 INTERVAL YEAR (4) TO MONTH := '2011-11';
l_interval2 INTERVAL DAY (2) TO SECOND := '15 00:30:44'; BEGIN
Normativa de PL/SQL. Buenas practicas

Página 13 de 42 13

null; END; 1.3. La conversión de fechas en cadenas y

cadenas de fechas.

1.3.1. EJEMPLO
TO_CHAR:

BEGI
N
DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, 'Day, DDth Month YYYY')); END; /

Sunday , 07TH August 2011

1.3.2. EJEMPLO
EXTRACT:

BEGIN SELECT EXTRACT (YEAR FROM SYSDATE)

FROM DUAL; END / ---- 2016

1.3.3. EJEMPLO
TO_DATE:

BEGI
 N

DBMS_OUTPUT.put_line ( TO_DATE (SYSDATE, 'dd-MM-yyyy HH:MI:SS')); END ; / 24-

05-2016
1.3.4. EJEMPLO TRUNC:
Establecer l_date hasta la fecha de hoy, pero con el tiempo establecido en 00:00:00:
l_date: = TRUNC (SYSDATE);
Obtener el primer día del mes de la fecha especificada:
l_date: = TRUNC (SYSDATE, 'MM');
Obtener el primer día del trimestre de la fecha especificada:
l_date: = TRUNC (SYSDATE, 'Q');
Obtener el primer día del año para la fecha especificada:
l_date: = TRUNC (SYSDATE, 'Y');
1.4. Fechas Aritmeticas
Base de datos de Oracle que permite realizar operaciones aritméticas sobre las fechas y las marcas de tiempo
de varias maneras:
• Añadir un valor numérico o restarlo de una fecha, como en SYSDATE + 7; Base de Datos de Oracle trata el
número como el número de días.
• Añadir una fecha o restarlo de otro, como en l_hiredate - SYSDATE.
• Utilice una función incorporada para "mover" una fecha por un número determinado de meses o para otra
fecha en una semana.
Ejemplos:
Establecer una variable local para la fecha de mañana:
l_date: = SYSDATE + 1;
Retroceda una hora:
l_date: = SYSDATE - 1/24;
Avanzar 10 segundos:
l_date: = SYSDATE + 10 / (60 * 60 * 24);
Normativa de PL/SQL. Buenas practicas

Página 15 de 42 15

1.5. Otras Funciones de

Fechas

ADD_MONTHS: Añade el número especificado de meses a o lo resta de una fecha (o un sello de

tiempo)

NEXT_DAY: Devuelve la fecha del primer día de la semana nombrado en la llamada a la

función

LAST_DAY: Devuelve la fecha del último día del mes de la fecha

especificada

CAMBIOS DE CONTEXTOS (Entre PL/SQL

y SQL).

Recomendaci
ón:
 Los códigos SQL se ejecutan en el compilador de SQL. El código PL / SQL se ejecuta por el compilador
de PL / SQL. El dominio de PL / SQL proviene de la capacidad de combinar las capacidades de
procedimiento de PL / SQL con las capacidades de procesamiento conjunto de SQL.

Ejemplo: --

Antes
DECLA
RE
CURSOR c1 AS SELECT n1
FROM t1;
CURSOR c2 (p_n1) AS SELECT n1, n2 FROM t2 WHERE
n1=p_n1;
CURSOR c3 (p_n1, p_n2) AS SELECT text FROM t3 WHERE n1=p_n1 AND
n2=p_n2; BEGIN
FOR c1_rec IN c1
LOOP
FOR c2_rec IN c2 (c1_rec.n1)
LOOP

FOR c3_rec IN c3(c2_rec.n1, c2_rec.n2) LOOP UPDATE ...

SET ..where n1=c3_rec.n1 AND n2=c3_rec.n2;

EXCEPTION WHEN no_data_found
THEN
INSERT into ... END; END LOOP; END LOOP;

END LOOP; COMMIT; END; / --

Después MERGE INTO fact1

USING (SELECT DISTINCT
c3.n1,c3.n2
FROM t1, t2, t3 WHERE t1.n1 = t2.n1
AND t2.n1 = t3.n1 AND t2.n2 = t3.n2 ) t
ON (fact1.n1=t.n1 AND fact1.n2=t.n2)
WHEN matched THEN
UPDATE SET .. WHEN NOT
matched THEN
INSERT .. ;
COMMIT;
La instrucción MERGE sirve para seleccionar registros de una o más fuentes de actualización o inserción en
una tabla o vista. Se puede especificar condiciones para determinar si se debe actualizar o insertar en la tabla
de destino o vista. MERGE es recomendable usar cuando se extrae información de una o más fuentes para
insertar o actualizar un solo destino.
En resumen, la sintaxis de la sentencia MERGE debe incorporar:
Cláusula INTO, especifica la tabla destino donde los registros serán actualizados o insertados.
Cláusula USING, especificando el origen de los datos que van a ser insertados o que van a servir para
actualizar la tabla destino. El origen de los datos puede tratarse de una tabla, una vista, o del resultado de la
ejecución de una consulta SELECT.
Cláusula ON, que especifica la condición bajo la cual se realizará, UPDATE (si la condición se cumple), o
INSERT (si la condición no se cumple).
Las cláusulas WHEN MATCHED | NOT MATCHED, que son las que indican a la base de datos Oracle que
acción realizar si se cumple o no se cumple la condición del ON. Estas dos cláusulas se pueden poner en
cualquier orden.
Ventajas: Las ventajas de la sentencia SQL MERGE son claras:
1. Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de
un nuevo registro. 2. La sentencia puede paralelizarse de forma transparente. 3. Se evita la necesidad de
realizar actualizaciones múltiples. 4. Es especialmente útil para realizar operaciones en multitud como por
ejemplo en aplicaciones
de data warehousing. 5. El rendimiento de la base de datos mejora ya que, al necesitarse menos sentencias
SQL para realizar
las mismas operaciones, también se necesitan menos accesos a las tablas fuente.
Fundamento: Se recomienda ver, analizar, examinar, indagar, si se puede escribir dicho código
en un formato SQL estándar en lugar de usar estructura de PL/SQL, ya que mejorará los
tiempos y costos de los planes de ejecuciones. Resolver la mayoría de los problemas con consulta
mediante un formato SQL estándar hace más fácil para el compilador y el motor de datos elegir un plan de
ejecución más óptimo. Se recomienda pensar en términos de conjuntos de datos. Es más fácil ajustar
consultas escritas en formato SQL estándar que afinar un bloque de PL / SQL.
ACCEDER EXCESIVAMENTE A LA TABLA DUAL
Política:
Normativa de PL/SQL. Buenas practicas

Página 17 de 42 17
NO se debe codificar una llamada SYSDATE en la sentencia SQL directamente en
lugar de ello se debe seleccionar SYSDATE en una variable.

-- Antes DECLARE l_epoch INTEGER; BEGIN SELECT ((SYSDATE-TO_DATE('10-02-2015

00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 ) INTO l_epoch FROM DUAL;
dbms_output.put_line(l_epoch); END; -- Despues DECLARE l_epoch INTEGER; BEGIN
l_epoch := (SYSDATE- TO_DATE('10-02-2015 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 *60 *60;
dbms_output.put_line(l_epoch); END;

Polític
a:

Se debe utilizar las secuencias directamente y NO en una variable que use la

tabla dual.

-- Antes DECLARE l_cust_id NUMBER; BEGIN FOR c1 in ( SELECT

cust_first_name, cust_last_name FROM customers
WHERE cust_marital_status !='married') LOOP SELECT
cust_hist_id_seq.nextval INTO l_cust_id FROM dual; INSERT INTO
customers_hist (cust_hist_id, first_name, last_name ) VALUES
(l_cust_id, c1.cust_first_name, c1.cust_last_name);

END LOOP; END; / -- Despues INSERT INTO customers_hist SELECT

cust_hist_id_seq.nextval, cust_first_name, cust_last_name FROM
customers WHERE cust_marital_status!='married';

Fundament
o:

Se debe evitar el uso excesivo de acceso tabla DUAL. Dado que provoca el cambio de contexto,
perjudicando el rendimiento del servidor.
EXCESIVAS LLAMADAS DE FUNCIONES
CREATE TABLE log_table ( message_seq NUMBER, message VARCHAR2(512)); CREATE SECUENCE message_id_seq;
DECLARE
l_debug BOOLEAN := FALSE; r1 INTEGER; FUNCTION log_entry( v_message IN VARCHAR2, v_debug in BOOLEAN) RETURN
NUMBER IS BEGIN
IF(v_debug) THEN
INSERT INTO log_table (message_seq, MESSAGE) VALUES(message_id_seq.nextval, v_message); END IF; RETURN 0; END;
BEGIN FOR c1 IN (
SELECT s.prod_id, s.cust_id,s.time_id, c.cust_first_name, c.cust_last_name, s.amount_sold FROM sales s, customers c WHERE
s.cust_id = c.cust_id and s.amount_sold > 100) LOOP
IF c1.cust_first_name IS NOT NULL THEN
r1:= log_entry ('first_name IS NOT NULL', l_debug ); END IF; IF c1.cust_last_name IS NOT NULL THEN
r1:= log_entry ('Last_name IS NOT NULL ', l_debug); END IF; END LOOP; END;
Considere la siguiente opción:
... IF first_name IS NULL AND l_debug = TRUE THEN
log_entry ('first_name is null '); END IF; ... FUNCTION log_entry( v_message IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO log_table (message_seq, MESSAGE) VALUES (message_id_seq.nextval); RETURN 0; END;
Recomendación:
Se recomienda validar, analizar las llamadas de funciones para mantener un bajo impacto en la cantidad de
errores en la compilación nativa de PL / SQL.
Fundamento:
El uso excesivo de funciones y de sus llamadas, dado que genera un alto consumo de CPU.
Normativa de PL/SQL. Buenas practicas

Página 19 de 42 19
LLAMADAS DE
DATABASE LINK

Política: NO se deben crear database link, ni sinónimo que hagan referencia a una
database link.

DECLARE V_customer_name
VARCHAR2(32); BEGIN ... FOR c1 IN
(SELECT ...) LOOP ...
SELECT customer_name INTO
v_customer_name FROM
customers@remotedb WHERE account_id =
c1.account_id; ... END LOOP; END;

Fundament
o:

Base de datos con excesivos llamados de database link pueden afectar al rendimiento de la aplicación.
El acceso a una tabla remota o modificar una tabla remota a través de un enlace de bases de datos no
es un enfoque escalable. Para cada acceso a una tabla remota varios paquetes se intercambian entre
las bases de datos que participan en el enlace de bases de datos. Por lo tanto, NO se deben crear
database link, ni sinónimo que hagan referencia a una database link por el alto impacto que conlleva.
Como alternativa no use integración de datos, use integración de servicios como por ejemplo Web
Services, ETL, etc., y validarlo con el área de arquitectura de sistemas.

BULK COLLECT AND

FORALL
 1. BULK COLLECT: Recuperan varias filas con una sola búsqueda, mejorar la velocidad de
recuperación
de datos. 2. FORALL: inserciones, actualizaciones y eliminaciones que utilizan colecciones de cambiar
varias filas
de datos muy
rápidamente.

Casi todos los programas PL / SQL, incluye tanto PL / SQL y SQL. Un programa en PL / SQL son
dirigidos por el compilador de PL / SQL. En cambio, las sentencias SQL se ejecutan por el compilador
SQL. Esta transferencia de control se llama un cambio de contexto, y cada uno de estos interruptores
provoca una sobrecarga que ralentiza el rendimiento global de sus programas.
Figura 1: El cambio entre PL / SQL y motores SQL.
Ejemplo: CREATE OR REPLACE PROCEDURE increase_salary (
department_id_in IN employees.department_id%TYPE, increase_pct_in IN NUMBER) IS
TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
INDEX BY PLS_INTEGER; l_employee_ids employee_ids_t; l_eligible_ids employee_ids_t; l_eligible BOOLEAN;
BEGIN
SELECT employee_id BULK COLLECT INTO l_employee_ids limit 1000 FROM employees WHERE department_id =
increase_salary.department_id_in;
FORALL indx IN 1 .. l_eligible_ids.COUNT
UPDATE employees emp SET [Link] = [Link] + [Link] * increase_salary.increase_pct_in WHERE
emp.employee_id = l_eligible_ids (indx); END increase_salary;
Normativa de PL/SQL. Buenas practicas

Página 21 de 42 21
Algunas cosas que debe saber sobre BULK
COLLECT:

Para aprovechar las ventajas de procesamiento bulk para las consultas, sólo tiene que poner la palabra
clave antes de BULK COLLECT... INTO y luego proporciona una o más colecciones después de la
palabra clave INTO. Aquí hay algunas cosas que debe saber acerca de cómo funciona
BULK_COLLECT:

• Se puede utilizar con todos los tres tipos de colecciones: matrices asociativas, tablas
anidadas, y VARRAYs.
• Se puede recuperar en colecciones individuales (uno para cada expresión en la lista SELECT) o
una sola colección de registros.
• La colección está siempre densamente poblada, a partir del valor del índice
1.
• Si no hay filas se capturan, entonces la colección se vacía de todos los
elementos.

Algunas cosas que debe saber sobre

FORALL:

• Cada declaración FORALL puede contener una sola instrucción DML. Si el bucle contiene
dos actualizaciones y una eliminación, entonces usted tendrá que escribir tres estados
FORALL.
 • PL / SQL declara el iterador FORALL como un entero, tal como lo hace con un bucle FOR.
Usted no necesita y no debe declarar una variable con este mismo nombre.
• En por lo menos un lugar en la instrucción DML, es necesario hacer referencia a una
colección y utilizar el iterador FORALL como el valor del índice en esa colección.
• Cuando se utiliza el low_value . . . high_value sintaxis en la cabecera FORALL, las colecciones
de referencia dentro de la instrucción FORALL debe estar densamente llena. Es decir, cada
valor del índice entre el LOW_VALUE y high_value deben ser definidas.
• Si su colección está vacío, debe utilizar los INDICES OF ó VALUES OF sintaxis o en su
cabecera FORALL.

Ejemplo: Using SAVE EXCEPTIONS with

FORALL

BEGI
N
FORALL indx IN 1 .. l_eligible_ids.COUNT SAVE EXCEPTIONS
UPDATE employees emp
SET [Link] = [Link] + [Link] * increase_pct_in WHERE emp.employee_id =
l_eligible_ids (indx); EXCEPTION
WHEN
OTHERS
THEN
IF SQLCODE = -24381
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ‘:’|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE); END LOOP; ELSE
RAISE; END IF; END
increase_salary;

EXCESIVOS
COMMITS

Fundament
o:
Se deben aplicar las recomendaciones dependiendo del escenario si es batch o no. Commit frecuentes
generan más registros de rehacer (redo log), puede conducir a problemas de integridad de los datos, y
consumen más recursos.
Recomendación:
• Si no es un proceso batch, el commit o rollback se debe dejar el programa que lo invoca. También debe
analizarse el volumen de datos a insertar y aplicar criterios y análisis de impacto por los bloqueos que puede
causar a otros usuarios en línea al ejecutar commits muy frecuentes sobre tablas de gran uso y de gran
volumen.
• Para un escenario de proceso batch se debe aplicar criterio, pero dependiendo del volumen de datos que se
están insertando. Se debe elegir un valor de acuerdo a la cantidad de registros procesados y lo ideal es que el
proceso batch tenga como entrada un parámetro. Ejemplo:
CREATE TABLE accounts (account_id NUMBER(6), balance NUMBER (10,2)); INSERT INTO accounts VALUES (7715, 6350.00);
INSERT INTO accounts VALUES (7720, 5100.50); DECLARE transfer NUMBER(8,2) := 250; BEGIN
UPDATE accounts SET balance = balance - transfer WHERE account_id = 7715; UPDATE accounts SET balance = balance +
transfer WHERE account_id = 7720; COMMIT; END;
CONSULTAS DINAMICAS
QUERY DINAMICA
CREATE FUNCTION f_getSumSal_nr (i_empno_tx VARCHAR2) RETURN NUMBER IS v_out_nr NUMBER:=0; v_sql_tx
VARCHAR2(2000); BEGIN IF i_empno_tx IS NOT NULL THEN v_sql_tx:='SELECT sum(sal) FROM emp WHERE empno IN ('||
i_empno_tx||')'; EXECUTE IMMEDIATE v_sql_tx INTO v_out_nr; END IF; RETURN v_out_nr; END;
Política:
NO se deben usar sentencias de SQL dinámico, debe usar querys estáticas de acuerdo a requerimientos
funcionales.
Fundamento:
Normativa de PL/SQL. Buenas practicas

Página 23 de 42 23
Ya que afecta el rendimiento del motor de datos e incluir que los tiempos de ejecución no son confiables
y costosos. Además, el uso de sentencias SQL Dinámicas son una fuente de vulnerabilidades de las
aplicaciones.

DESVENTAJ
AS:

No siempre se forman las consultas más óptimas.

Problemas de seguridad por ataques de inyección
SQL

Nota: 1. Se validarán aquellas querys dinámicas que sean como excepciones a la

norma.
2. Se recomienda usar query estáticas de acuerdo a requerimientos funcionales detallados y no
genéricos
.
VALIDAR PACKAGE
CABECERA
Política: Se debe tener un encabezado con una breve descripción o propósito del package con su historial de
modificaciones.
CREATE OR REPLACE PACKAGE BODY demo_pkg IS -- -- PROPOSITO: Demonstración package para llamada interna. --
METODOS: PROC1(); PROC2() -- instantiate_error_context() -- HISTORIAL DE MODIFICACION -- Persona Fecha Comentarios. --
--------- -------- ------------------------------------------- -- DKK 01/02/2014 Creación de Package
SENTENCIAS DDL EN PACKAGES.
DDL
Política:
No se deben utilizar sentencia DDL (CREATE, DROP, GRANT, and REVOKE) en los packages. Se debe
eliminar la sentencia DDL antes de ser enviado a instalar el package en producción. Se deben enviar el o los
scripts para aplicar estos objetos en ambiente QA al área de ingeniería.
CREATE OR REPLACE PACKAGE BODY add_location (loc VARCHAR2) IS BEGIN
-- insert new location in master table INSERT INTO offices VALUES (loc); -- create an employee information table EXECUTE
IMMEDIATE 'CREATE TABLE ' || 'emp_' || loc || '(
empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), deptno NUMBER(2) )'; END; /

Fundamento:
Normativa de PL/SQL. Buenas practicas

Página 25 de 42 25
Por políticas de seguridad no se debe usar sentencias DDL, ya que se necesitarán permisos
adicionales para ejecutar dichas sentencias.

SENTENCIA INSERT
INTO

Polític
a:

Se debe utilizar de forma correcta la sentencia INSERT. No utilizar INSERT INTO TABLA VALUES
<Lista de Valores>

Fundament
o:

• Si se agrega un campo a la tabla la aplicación se

cae.
• Que se produzca un error, si el campo no acepta valores nulos.
• Que se grave el registro y se deje nulo el campo, cuando el campo acepte valores
nulos.
• Que se grave el registro y se tome el valor por defecto, cuando el campo tenga definido un
valor por defecto.

Ejempl
o:

-- Antes INSERT INTO suppliers VALUES (5000, 'Apple'); -- Despues INSERT INTO
suppliers (supplier_id, supplier_name) VALUES (5000, 'Apple');

Nota: • Al insertar registros en una tabla usando instrucción INSERT de Oracle, se debe proporcionar

un valor
para cada columna NOT
NULL.
• Se puede omitir una columna de instrucción INSERT de Oracle si la columna permite valores
NULL.

SENTENCIA
SELECT

SELEC
T*
 Política: Se debe utilizar de forma correcta la sentencia SELECT. No se puede usar de la forma
SELECT * FROM <TABLA>. Se debe utilizar la sentencia con los campos SELECT <lista de
campos> FROM <TABLA>

Fundament
o:

Cuando es utilizada dentro de un cursor, si se agrega un campo a la tabla la aplicación

se cae.

Ejempl
o:

-- Antes SELECT * FROM homes WHERE home_id < 500 AND home_type = 'two-storey' ORDER BY home_type ASC,
bathrooms DESC; -- Después SELECT home_id, home_type, bathrooms FROM homes WHERE home_id < 500 AND
home_type = 'two-storey' ORDER BY home_type ASC, bathrooms DESC;
VARIABLES
NOMENCLATURA DE LAS VARIABLES
Para toda variable, se debe anteponer un prefijo en el que indique si es una variable global (g), variable local
(v) o parámetro (p), este prefijo, según corresponda. En general, todas las variables debieran ser locales, en
caso de utilizarse en otros procedimientos y funciones se debieran pasar como parámetros. En caso de que la
variable sea producto de un campo de base de datos, la variable debe poseer dicho nombre.
Política:
Tipovariable[TipoDato]_nombre Tipovariable: Corresponde al tipo de la variable (v,g,p) TipoDato: Tipo del
dato. Nombre: Es el nombre de la variable, si corresponde a un campo, será el nombre del campo.
Tipovariable Tipodato (opcional)
v Variable n Numérico
p Parámetro d Date
g Variable global v Varchar
c Char
b Boolean
Ejemplo: v_per_rut → variable asociada al campo per_rut vn_per_rut → variable asociada al campo
per_rut, de tipo numérico p_nombre → variable de tipo parámetro que contiene algún nombre.
pc_nombre → variable de tipo parámetro que contiene algún nombre, de tipo char.
Cada variable y constante que se declara deben tener un propósito y un solo propósito. El nombre de esa
variable o constante debe describir, tan claramente como sea posible, el único propósito solamente.
Normativa de PL/SQL. Buenas practicas

Página 27 de 42 27
VARIABLES CONSTANTES, REUTILIZACION DE
VARIABLES

Política: Se debe usar las declaraciones de constantes para aquellas variables cuyos valores no
cambian.

Fundament
o:
 Las variables constantes, no deben y no puede cambiar. Si sabe que el valor de la variable no va a
cambiar, hay que declararlo como una constante.

Ejempl
o:

DECLARE c_date CONSTANT DATE := TRUNC

(SYSDATE);
c_category CONSTANT [Link]%TYPE := 'MYSTERY';
BEGIN
[Link] (c_date, c_category); ...
-- 75 lineas después FOR rec IN (
SELECT campo1, campo2 FROM book WHERE category = c_category)
LOOP
... END
LOOP;

Política: Los valores literales dentro del where deben ser reemplazados por constantes o variables
literales según sea el caso.

Ejempl
o:

-- Antes DECLARE Pay_per_day NUMBER(6,2);

BEGIN
Pay_per_day := 2290 / 21; DBMS_OUTPUT.PUTLINE(‘The pay per day is ’||

TO_CHAR(pay_per_day)); EXCEPTION WHEN ZERO_DIVIDE THEN

Pay_per_day := 0; END; -- Despues
DECLARE Monthly_salary NUMBER(6);
Number_of_days_worked NUMBER(2);
Pay_per_day NUMBER(6,2); BEGIN
Monthly_Salary := 2290; Number_of_days_worked := 21; Pay_per_day :=
Monthly_Salary / Number_of_days_worked; DBMS_OUTPUT.PUTLINE(‘The pay per

day is ’|| TO_CHAR(pay_per_day)); EXCEPTION WHEN ZERO_DIVIDE THEN

Pay_per_day := 0; END; NOTA: En general, se debe preferir el uso de constantes en vez de

literales y la definición debe hacerse a nivel de package.

EXCEPCIONES EN
PL SQL
EXCEPCIÓN
En PL/SQL una advertencia o condición de error es llamada una excepción. Las excepciones se controlan
dentro de su propio bloque. 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 excepción
será capturada en el bloque WHEN OTHERS THEN.
DECLARE -- Declaraciones BEGIN
-- Ejecución EXCEPTION WHEN NO_DATA_FOUND THEN
-- Se ejecuta cuando ocurre una excepción de tipo NO_DATA_FOUND WHEN ZERO_DIVIDE THEN
-- Se ejecuta cuando ocurre una excepción de tipo ZERO_DIVIDE WHEN OTHERS THEN
-- Se ejecuta cuando ocurre una excepción de un tipo no tratado -- en los bloques anteriores END;
Una vez finalizada la ejecución del bloque EXCEPTION no se continúa ejecutándose el bloque anterior.
Si existe un bloque de excepción apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un
bloque de control de excepciones adecuado al tipo de excepción se ejecutará el bloque de excepción WHEN
OTHERS THEN (si existe). WHEN OTHERS debe ser el último manejador de excepciones.
Las excepciones definidas por el usuario deben utilizar la sentencia RAISE.
DECLARE -- Declaramos una excepción identificada por VALOR_NEGATIVO
VALOR_NEGATIVO EXCEPTION; valor NUMBER; BEGIN
valor := -1; IF valor < 0 THEN
RAISE VALOR_NEGATIVO; END IF;
EXCEPTION -- Excepción WHEN VALOR_NEGATIVO THEN
RAISE podemos lanzar una excepción
dbms_output.put_line('El valor no puede ser negativo'); END; Con la sentencia
definida por el usuario o predefinida, siendo el comportamiento habitual lanzar excepciones definidas por el
usuario.
REGLAS DE ALCANCE
Una excepción es válida dentro de su ámbito 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.
Normativa de PL/SQL. Buenas practicas

Página 29 de 42 29
La siguiente es la lista de las excepciones predeterminadas por PL/SQL y una breve descripción de
cuándo pueden ser gatilladas:

EXCEPCIÓN SE EJECUTA ... SQLCODE

ACCESS_INTO_NULL El programa intentó asignar valores a los atributos de un objeto no inicializado -6530

COLLECTION_IS_NULL El programa intentó asignar valores a una tabla anidada aún no inicializada -6531

CURSOR_ALREADY_OPEN El programa intentó abrir un cursor que DUP_VAL_ON_INDEX

ya se encontraba abierto.
El programa
Recuerde intentó almacenar valores duplicados e
que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar
con con la sentencia OPEN
- restricción de integridad de un índice único (un
6511 index)
-
1

INVALID_CURSOR El programa intentó efectuar una operación no válida sobre un cursor -1001

INVALID_NUMBER En una sentencia SQL, la conversión de una cadena de caracteres hacia un número
válido
falla
cuando esa cadena no representa un número -
1722

LOGIN_DENIED El programa intentó conectarse a Oracle con un nombre de usuario o password inválido -1017

NO_DATA_FOUND Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento
indexada
no inicializado en una tabla 10
0

NOT_LOGGED_ON El programa efectuó una llamada a Oracle sin estar conectado -1012
 PROGRAM_ERROR PL/SQL tiene un problema interno -6501

ROWTYPE_MISMATCH Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) -
tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma 6504
no es del tipo esperado

SELF_IS_NULL El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo -30625

STORAGE_ERROR La memoria se terminó o está corrupta -6500

SUBSCRIPT_BEYOND_COUNT El programa está tratando de referenciar un elemento de un arreglo -

indexado que se encuentra en una posición más grande que el número real de elementos de la colección 6532
-
6533
SYS_INVALID_ROWID La conversión de una cadena de caracteres hacia u
cadena
SUBSCRIPT_OUTSIDE_LIMIT El programa está referenciando un elemento de un arreglo utilizando un
no representa un
número fuera número
del rango permitido (por ejemplo, el elemento -
“-1”) 1410

TIMEOUT_ON_RESOURCE Se excedió el tiempo máximo de espera por un recurso en Oracle -51

TOO_MANY_ROWS Una sentencia SELECT INTO devuelve más de una fila -1422

VALUE_ERROR Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando
pequeña
se intenta calzar un valor muy grande dentro de una variable más -
6502

ZERO_DIVIDE El programa intentó efectuar una división por cero -1476

EXCEPCIONES PERSONALIZADAS
En ocasiones queremos enviar un mensaje de error personalizado al producirse una excepción PLSQL. Para
ello es necesario utilizar la instrucción RAISE_APLICATION_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;
Política:
Todo bloque PL SQL debe ser manejado por una excepción, por lo menos debe manejar la excepción when
others.
HINTS
Política: NO se puede usar sugerencias para el optimizador (hints) en pl/sql.
Fundamento:
Las sugerencias del optimizador se pueden utilizar con sentencias SQL para alterar los planes de ejecución. El
uso de hints implica código adicional que debe ser administrado, verificado y controlado.
CREATE OR REPLACE VIEW v1 AS
SELECT *
FROM employees WHERE employee_id < 150;
CREATE OR REPLACE VIEW v2 AS
SELECT v1.employee_id employee_id, departments.department_id department_id FROM v1, departments WHERE
v1.department_id = departments.department_id;
SELECT /*+ NO_MERGE(v2) INDEX([Link] emp_emp_id_pk) FULL([Link]) */ *
FROM v2 WHERE department_id = 30;
ADMINISTRACIÓN DE CURSORES:
El propósito central del lenguaje de Oracle PL / SQL es que sea lo más fácil y eficiente como sea posible para
consultar y cambiar el contenido de las tablas de una base de datos. Debe, por supuesto, utilizar el lenguaje
SQL para acceder a las tablas, y cada vez que lo haga, se utiliza un cursor para hacer el trabajo. Un cursor es
un puntero a una zona privada de SQL que almacena información sobre el proceso de una sentencia SELECT
o lenguaje de manipulación de datos (DML) (INSERT, UPDATE, DELETE o MERGE). La administración de
cursores
Normativa de PL/SQL. Buenas practicas

Página 31 de 42 31
para las sentencias DML se maneja por parte de Oracle de base de datos, pero PL / SQL ofrece varias
maneras de definir y manipular los cursores para ejecutar instrucciones SELECT.

PARA LA SENTENCIA SELECT-

INTO:
a. se recomienda usar las excepciones NO_DATA_FOUND,
TOO_MANY_ROWS

ASIGNAR UN CURSOR
EXPLÍCITO:
a. Pero cada vez que utilice un cursor explícito, lo mejor es declarar un registro mediante el
uso
de ROWTYPE%, por lo que si la lista SELECT del cursor cambia nunca, esa variable va a
cambiar con él.

Ejemplo:

PROCEDURE assign_bonus ( employee_id_in IN employees.employee_id%TYPE, bonus_pool_io IN OUT

INTEGER)

Aquí hay algunas cosas a tener en cuenta cuando se trabaja con los cursores
explícitos:

• Si la consulta no se identifica ninguna fila, Oracle Database no elevará NO_DATA_FOUND. En

cambio, el atributo%NOTFOUND cursor_name devolverá TRUE.
• Su consulta puede devolver más de una fila, y la base de datos Oracle no elevará
TOO_MANY_ROWS.
• Cuando se declara un cursor en un paquete (es decir, no dentro de un subprograma del paquete)
y se abre el cursor, que permanecerá abierta hasta que se cierre de forma explícita o su sesión ha
finalizado.
• Se recomienda cerrar el cursor explícitamente con la sentencia
CLOSE.

CURSOR FOR
LOOP
a. Lo bueno de el cursor bucle FOR es que Oracle Database se abre el cursor, declara un
registro
 mediante el uso% ROWTYPE contra el cursor, obtiene cada fila en un registro, y luego
cierra el bucle cuando todas las filas han sido extraídas.

Ejempl
o:

CURSOR employees_in_10_cur
IS
SELECT *
FROM employees WHERE department_id =
10;

EXECUTE IMMEDIATE INTO PARA QUERYS

DINÁMICAS.
a. No se puede usar cursores dinámicamente. Se corre el riesgo de
inyección SQL.

VARIABLE DE
CURSOR

Una variable de cursor es, como se puede adivinar por su nombre, una variable que apunta a un cursor
o un conjunto de resultados. A diferencia de un cursor explícito, puede pasar una variable de cursor
como un argumento a un procedimiento o una función. Hay varios casos de uso excelentes para las
variables de cursor, incluyendo los siguientes:

a. Pasar una variable de cursor de vuelta al cliente que llama el programa, el conjunto de
resultados
puede ser "consumido" para la visualización u otro
procesamiento.
b. Construir un conjunto de resultados dentro de una función, y devolver una variable de cursor a ese conjunto.
Esto es especialmente útil cuando se necesita utilizar PL / SQL, además de SQL, para construir el conjunto de
resultados. c. Pasar una variable de cursor a una función de un poderoso pero bastante avanzada técnica de
optimización mesa pipeline. Una explicación completa de variables de cursor, incluyendo las diferencias entre
los tipos REF_CURSOR fuertes y débiles, está más allá del alcance de este artículo. Ejemplo:
1 CREATE OR REPLACE FUNCTION names_for ( 2 name_type_in IN VARCHAR2) 3 RETURN SYS_REFCURSOR

4 IS 5 l_return SYS_REFCURSOR; 6 BEGIN 7 CASE name_type_in 8 WHEN 'EMP' 9 THEN 10 OPEN l_return FOR
11 SELECT last_name 12 FROM employees 13 ORDER BY employee_id; 14 WHEN 'DEPT' 15 THEN 16 OPEN
l_return FOR 17 SELECT department_name 18 FROM departments 19 ORDER BY department_id; 20 END CASE;

21 22 RETURN l_return; 23 END names_for;

Normativa de PL/SQL. Buenas practicas

Página 33 de 42 33
CHEQUEAR
CURSORES

Recomendación: Se debe comprobar si un cursor ya está abierto marcando la ISOPEN propiedad del
cursor. Para incorporar la lógica, se puede reescribir el fragmento de código de ejemplo como:
 BEGI

N ...
IF book_info.bytitle_cur%ISOPEN THEN
CLOSE book_info.bytitle_cur; END IF;
END;

-- Comprobar si una variable cursor está abierto DECLARE TYPE

empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp_cv empcurtyp;
BEGIN
IF NOT emp_cv%ISOPEN THEN -- open cursor variable
OPEN emp_cv FOR SELECT * FROM employees;
END IF; CLOSE emp_cv; END;

Regla
s:

• Nunca asumir que un cursor package está cerrado (y listo para ser
abierto).
• Nunca asumir que un cursor package esté abierto (y está listo para ser
cerrado).
• Siempre asegúrese de cerrar explícitamente el cursor package cuando haya terminado
con él.

LAS RESTRICCIONES A LAS VARIABLES DE

CURSOR

• No se puede declarar variables de cursor en una especificación del

paquete
• Si asocia una variable de cursor dentro de PL/SQL desde un cliente en OCI (es una interfaz
de programación de aplicaciones (API)), no se puede recuperar en el servidor a menos que
también abra el cursor en la misma llamada del servidor.
• No se puede usar operadores de comparación para evaluar las variables de cursor para la
igualdad, la desigualdad o nulidad (NULL).
• Las columnas de bases de datos no pueden almacenar los valores de las variables del
cursor. No existe un tipo equivalente a utilizar en una sentencia CREATE TABLE.
• No se puede almacenar variables de cursores en una array asociativa, tabla anidada o
varray.
• Los cursores y variables de cursor no son interoperables; es decir, no se puede utilizar uno
donde el otro se espera. Por ejemplo, no se puede hacer referencia a una variable de cursor
en un cursor bucle FOR.
ATRIBUTOS DE LOS CURSORES
ATRIBUTO EFECTO cur%ISOPEN Devuelve TRUE si el cursor está abierto, FALSE si esta cerrado el cursor.
cur%FOUND - Devuelve INVALID_CURSOR si el cursor se declara, pero no abre; o si el cursor
ha sido cerrado. - Devuelve NULL si el cursor está abierto, pero fetch no se ha ejecutado. - Devuelve TRUE, si
un correcto fetch se ha ejecutado. - Devuelve FALSE, si no retorna ninguna fila. cur%NOTFOUND - Devuelve
 INVALID_CURSOR si el cursor se declara, pero no abre; o si el cursor
ha sido cerrado. - Devuelve NULL si el cursor está abierto, pero fetch no se ha ejecutado. - Devuelve FALSE,
si un correcto fetch se ha ejecutado. - Devuelve TRUE, si no retorna ninguna fila. cur%ROWCOUNT -
Devuelve INVALID_CURSOR si el cursor se declara, pero no abre; o si el cursor
ha sido cerrado. - Retorna el número de filas captadas (Fetched). - El atributo ROWCOUNT no le entrega el
número de filas reales hasta que haya iterado a través del cursor entero. Por lo tanto, no se debe confiar en
este atributo para saber cuántas filas se encuentran en un cursor después de que se abre.
Normativa de PL/SQL. Buenas practicas

Página 35 de 42 35

TUNNING A
QUERYS:

• Las Querys no deben tener full table scan. En tablas pequeñas o de parámetros un full table scan
puede ser mejor que un índice.
• Si tiene FULL se revisa lo siguiente:
o Si el peso es superior a 30 KB se rechaza (esté o no en un
loop)
Si no, se valida el crecimiento futuro y se estima si se aprueba en base a eso (si va a
superar los 30KB en algún momento se rechaza) o Si es inferior a 30 KB se revisa si está
en un loop y genera accesos multiplicativos.
• Si usa índices se revisará la selectividad de los índices. Si la selectividad es inferior a 80% el
índice no es óptimo (esto es analizable para cada caso, pero esa es la norma oficial). Si es superior
el índice está correcto, por lo tanto se aprueba.
• Para los traces se revisan las querys más costosas y con full según los puntos anteriores, además
se ven los tiempos, modos de ejecución de las querys como alternativas de solución en el caso que
lo amerite.
• Se revisaran algunos casos las siguientes variables frecuencia de ejecución, si la base de datos es
transaccional o gestión, el volumen de los datos.
• Se debe señalar evitar usar los distintos (<>, ¡=), los not in(), not like, like (se recomienda usar
decode()).

ALCANCE DE
MANTENCIONES

Toda modificación realizada a objetos compilados, ya sean triggers, procedimientos,

funciones, Jobs, packages, etc. Debe pasar por una fase de revisión de Código (SQL,
PlSql) por el Área de Control de Calidad.

Cuando las Modificaciones de los Objetos sea mayor a un 25% del todo el código, esta
revisión de código debe ir acompañada de una prueba de Performance en la BD de QA
respectiva.
 Esta prueba tendrá el fin de revisar el impacto de la modificación en lo que se refiere a
consumo de recursos de la Base de Datos, tiempo de respuesta y plan de ejecución de
las modificaciones. La cual puede generar observaciones en cuanto al código modificado
para revisión y mejora, por lo cual debe estar incorporada en el Proyecto de Mantención.

El cálculo de porcentaje de modificación se realizará según las líneas modificadas y/o

añadidas. Mientras que la solicitud de esta Prueba debe ser realizada por Mantis hacia el
Analista de Base de Datos.
ALCANCE DE PROYECTOS
Todo Desarrollo realizado, que contengan sentencias sql ́ s u objetos compilados, ya sean
triggers, procedimientos, funciones, Jobs, packages, etc. Deben pasar por una fase de revisión
de Código (SQL, PlSql) por el Área de Control de Calidad y además por la fase de Pruebas de
Carga, Rendimiento o Stress según corresponda a lo requerido por el proyecto.
Estas pruebas tienen el fin de revisar y analizar el impacto de estos desarrollos en las Distintas
BD con las cuales se comunican, específicamente en lo que se refiere al consumo de recursos
de la Base de Datos, Servidores, tiempo de respuesta, análisis de plan de ejecución, etc.
Esta prueba puede generar observaciones en cuanto al código Sql, por lo cual debe estar
contemplada en la Gantt del proyecto para su revisión y mejora.
Para la solicitud de las prueba de Carga se debe realizar mediante el Anexo A.
BIBLIOGRAFIA:
LINK DE APOYO:
• PL/SQL DEVELOPER'S GUIDE
[Link]
[Link]
• DATABASE SQL REFERENCE
[Link]
• PL/SQL BEST PRACTICES
[Link]
• PL/SQL TUTORIAL
[Link]
LIBROS DE APOYO:
Normativa de PL/SQL. Buenas practicas

Página 37 de 42 37
• EXPERT PL/SQL PRACTICES for Oracle Developers and DBAs. Editorial
Apress.
• Doing SQL from PL/SQL: Best and Worst Practices. Editorial
Oracle.
• Expert Oracle Database Architecture. Editorial
Apress.
ANEXO A. PROCEDIMIENTO DE PRUEBAS DE CARGAS
Procedimiento para generar una instancia de monitoreo para las pruebas de cargas en las
bases de datos Oracle con el área DBAs.
Requisitos para la Solicitud:
1. Informar con un tiempo no menor a 72 hrs de anticipación. 2. Indicar hora de inicio y fin de la
prueba. 3. Indicar tipo de prueba que se desea realizar. (Stress, carga, perfomance) 4. Enviar
iteraciones con distintas cargas a ejecutar: 5. Ejemplo: Prueba de llamada a package con 100,
500 y 1000 usuarios. 6. Indicar BD y ambiente donde se realizaran las pruebas. 7. Enviar lista
de Usuarios de BD con la cual se realizaran las distintas pruebas. 8. Se debe indicar los
Objetos de BD que interactuaran en la prueba (Tablas, triggers, funciones, vistas,
procedimiento, packages, etc) pertenecientes al proyecto y/o que se van a utilizar en la prueba.
9. Indicar el Cliente de donde se realizaran las llamadas hacia la BD ( WL, OSB, Pc u otro) 10.
Enviar los requisitos solicitados con un tiempo no menor a 24 hrs de anticipación
ANEXO B: ANALIZANDO EL PLAN DE EJECUCIÓN
Al examinar un plan de ejecución, busque ineficiencias y oportunidades para mejorar el
rendimiento. Cuando vea a alguno de los siguientes criterios, usted debe afinar la consulta:
• Índice completo o escaneos de tablas ( Full index or table scans )
• Escaneos de rango no selectivos ( Unselective range scans )
• Filtros de predicados tardíos ( Late predícate filters )
• Incorrect o inefficient join order ( Incorrect inefficient join order )
• Operaciones de Filtro Tardía ( Late filter operations )
La clave es buscar operaciones en las que el porcentaje es muy alto: cuando el número de filas
que necesita la operación es un porcentaje muy pequeño del número de filas leídas. Reduce el
número de lecturas, y lo más probable es mejorar el rendimiento de la operación. El plan de
ejecución puede demostrar que una declaración utiliza un índice, pero eso no significa
necesariamente que la
Normativa de PL/SQL. Buenas practicas

Página 39 de 42 39
declaración se ejecuta de manera eficiente. A veces, los índices pueden ser
extremadamente ineficientes debido a las columnas del índice que se utiliza y su
selectividad.

ANEXO C: LA LECTURA DE UN PLAN DE

EJECUCIÓN

Es bastante
fácil.

Ejempl
o:

|Execution Plan | ----------------------------------------------------------------- | 0 | | SELECT

STATEMENT Optimizer=CHOOSE | | | (Cost=1 Card=1 Bytes=62) | 1 | 0 |TABLE ACCESS
(BY INDEX ROWID) OF 'EMPLOYEES' | | | (Cost=1 Card=1 Bytes=62) | 2 | 1 | INDEX
(UNIQUE SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE)
-----------------------------------------------------------------
 En este ejemplo, cada operación se asigna un número de identificación de la operación,
como se ve en los números en el extremo izquierdo. La siguiente serie de números define
el funcionamiento de los padres para la operación de la lista. En este caso, el índice de
línea (UNIQUE SCAN) La línea tiene sangría la más alejada. Esto implica que el recorrido
de índice se ejecutará primero. El resultado del análisis del índice, el rowid de la fila en la
tabla se pasará al siguiente comando, que es un TABLE ACCESS (BY INDEX ROWID).
Se está consiguiendo una fila de la tabla basada en el ROWID pasado de la operación
anterior. Esta fila se devolverá a la consulta del select.

Otro
ejemplo:

SQL> 1 select [Link], [Link] 2

from emp a, dept b 3 where
[Link]=[Link] 4 and
[Link]=21334;

|PLAN_TABLE_OUTPUT| ---------------------------------------------------------------------------- | Id | Operation |

Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------- | 0 | SELECT
STATEMENT | | 31 | 837 | 24 | | * 1 | HASH JOIN | | 31 | 837 | 24 | | * 2 | TABLE ACCESS BY INDEX
ROWID | EMP | 1 | 5 | 21 | | * 3 | INDEX RANGE SCAN | IX_EMP_01 | 1 | | 1 | | 4 | TABLE ACCESS
FULL | DEPT | 409 | 8998 | 2 | ----------------------------------------------------------------------------

En este caso, tenemos una combinación que implica dos tablas, la tabla EMP y la tabla
DEPT. También tenemos un acceso índice en un índice de la tabla EMP con una acceso
Table Access full a la tabla DEPT.
Operaciones Optimizador
Después de ver algunos ejemplos de la salida del Plan, usted debe tener una idea preliminar de
las diferentes operaciones que el optimizador utilizará al acceder y procesar consultas SQL. La
siguiente tabla le proporcionará más información sobre los tipos de operaciones que el
optimizador utilizará en el procesamiento de una instrucción SQL.
Operación Significado bitmap index Un índice de mapa de bits se utiliza para recuperar
rápidamente las filas. bitmap merge Varios mapas de bits se fusionaron juntos para producir
un mapa de bits de las
filas de datos. bitmap or Dos mapas de bits eran lógicamente compradas (OR); luego se juntan
para
producir un mapa de bits. bitmap and Dos mapas de bits eran lógicamente compradas (AND);
luego se juntan para
producir un mapa de bits. connect by Se accede a las filas en orden jerárquico, debido a la
cláusula CONNECT BY. filter Se proporcionó un conjunto de filas y las filas que no participan
en el resultado
final se filtran. hash join El método Hash Join, fue utilizado para unir dos tablas. index unique
scan
Un índice único se utiliza para recuperar ROWIDs para encontrar una fila de datos. index
range scan
Un índice fue escaneado en orden ascendente para recuperar uno o más ROWIDs para
encontrar los datos. index range scan descending
Un índice fue escaneado en orden descendente para recuperar uno o más ROWIDs para
encontrar los datos.
index skip scan Disponible en Oracle 9i y 10g, utiliza un índice, pero no con las columnas
principales, para recuperar ROWIDs. intersection Dos conjuntos de resultados se fusionaron,
y se eliminan las filas duplicadas. merge join El método merge join ordena y se utiliza para unir
dos tablas. Normalmente,
esta operación se ve con una operación de combinación también. minus Un conjunto de
resultados se utiliza para eliminar las filas de otro conjunto de
resultados. nested loops El método Nested Loops fue utilizado para unir dos tablas. partition
single Se accedió a una partición en particular. partition iterator
Se accede a múltiples particiones.
partition all Se accede a todas las particiones. remote Una consulta fue enviada a una base
de datos remota para apoyar la transacción distribuida. La otra columna del cuadro del plan
mostrará la consulta se envía al sistema remoto. sort aggregate Una operación de ordenación
se realizó sobre los datos como el resultado de
una función de agregado.
Normativa de PL/SQL. Buenas practicas

Página 41 de 42 41
sort unique Una operación de ordenación se realizó para eliminar duplicados. sort
group by Una operación de ordenación se realizó como resultado de una cláusula
GROUP BY. sort join Se realizó una operación de ordenación para ser
utilizado en el método sort merge join. Esta operación también se verá con la operación
merge join también. sort order by Una operación de ordenación se realizó como
resultado de una cláusula
ORDER BY. table access full Se realizó un escaneo completo de la tabla.
Esto significa que se accede a todas
las filas de todos los bloques de la tabla. union Dos conjuntos de resultados se
fusionaron, sin quitar filas duplicadas, vistos
debido a la presencia de la cláusula UNION. view La consulta subyacente de un punto
de vista está siendo utilizada para
procesar las filas de
datos.

ANEXO D: INESTABILIDAD DE LOS PLANES DE

EJECUCIONES
Estas son algunas de las consecuencias de la inestabilidad de los planes de
ejecuciones:

• La consulta puede funcionar más lentamente de lo esperado debido a que el plan de ejecución no
es el que se esperaba.

• La consulta puede ejecutarse lentamente hoy a pesar de que corrió ayer rápido, esto porque el
plan de consulta ha cambiado.

• Su consulta puede correr más rápido en la base de datos de QA que en la base de datos de
producción debido a que los planes de consulta son diferentes.

• Su consulta puede funcionar más lentamente después de un parche de software o actualización

debido a que el plan de consulta cambió después del parche o actualización.

• Su consulta puede empezar a correr lentamente después de que las estadísticas del optimizador
se actualizan debido a que el plan de consulta cambió después que las estadísticas fueron
renovadas.