Declaración de Variables PL/SQL

Uso de Variables

Con PL / SQL, puede declarar variables y luego usarlos usadas en las sentencias SQL y sentencias procedurales del bloque PL/SQL. Las variables se utilizan principalmente para el almacenamiento de datos y la manipulación de los valores almacenados. En la declaración de PL / SQL del ejemplo, la sentencia recupera el first_name y department_id de la tabla EMPLOYEES. Si se tiene que manipular el first_name o department_id entonces se debe almacenar el valor recuperado. Las variables se utilizan para almacenar temporalmente el valor y poder utilizarlos para el procesamiento y la manipulación de los datos. Las variables pueden almacenar cualquier objeto PL / SQL, tales como variables, tipos, cursores y subprogramas. La reutilización es otra ventaja de la declaración de variables. Después de que se declaran las variables, se pueden utilizar varias veces en una aplicación haciendo referencia a ellas varias veces en diferentes declaraciones.

-- ejemplo
SELECT
 first_name,
 departament_id
INTO
 emp_fname,
 emp_deptno
FROM employees
WHERE employee_id = 200;

Manejo de Variables en PL/SQL

Se pueden utilizar las variables de las siguientes maneras.

• Declararlas e inicializarlas en la sección de declaración: se pueden declarar variables en la sección declarativa de cualquier bloque PL / SQL o subprograma. Las declaraciones de variables permiten asignar espacio de almacenamiento para un valor y se debe especificar su tipo de datos y el nombre de la ubicación de almacenamiento para que pueda hacer referencia a ella. En las declaraciones también se puede asignar un valor inicial e utilizar la restricción NOT NULL en la variable. Se debe declarar una variable antes de hacer referencia a ella en otras secciones del bloque PL/SQL.
  
• Usarlas y asignarles nuevos valores en la sección ejecutable: en la sección ejecutable del bloque PL/SQL, el valor actual de la variable puede ser sustituido por un nuevo valor.
  
• Usarlas como parámetros a subprogramas PL/SQL: los subprogramas pueden recibir parámetros. Se pueden pasar variables como parámetros a subprogramas.
  
• Usarlas para almacenar la salida de un subprograma PL/SQL: las variables pueden ser usadas para almacenar el valor retornado por una función.
  

Declarar e Inicializar Variables PL/SQL

Declarar e Inicializar Variables PL/SQL

Se deben declarar todos los identificadores de variables PL/SQL en la sección de declaración antes de hacer referencia de ellas en el bloque PL/SQL. Existe la opción de asignar un valor inicial a una variable (como se muestra en el ejemplo). No es necesario asignar un valor a una variable cuando se declara. Si se hace referencia otras variables en una declaración, se deben haber declarado con anterioridad.

En la Sintaxis:

• identificador: es el nombre de la variable.
  
• CONSTANT: indica que el valor de la variable no puede ser modificado. Las variables constantes deben ser inicializadas.
  
• tipo_dato: indica que la variable es del tipo escalar, compuesto o del tipo LOB.
  
• NOT NULL: indica que la variable debe contener un valor. Una variable NOT NULL debe ser inicializada con algún valor.
  
• expr: es cualquier expresión PL/SQL que puede ser una expresión literal, otra variable ó incluso una expresión que utiliza una función.
  El bloque del ejemplo muestra para la variable v_comm dos valores, el asignado inicialmente en la sección de Declaración de variables (200) y el nuevo valor asignado en la sección de Ejecución (50). Para la variable c_comm muestra el valor asignado inicialmente (1400) y el que no se puede modificar ya que se definió como constante.
  

-- ejemplo
DECLARE
  v_comm   NUMBER(3) DEFAULT 200;
  c_comm CONSTANT NUMBER(4) := 1400; 
BEGIN
   DBMS_OUTPUT.PUT_LINE('Valor inicial de variable v_comm es : ' || v_comm);
   DBMS_OUTPUT.PUT_LINE('Valor inicial de variable c_comm es : ' || c_comm);
/* A la variable v_comm se le modifica el valor asignado inicialmente (200) por el valor 50 */
   v_comm := 50;
   DBMS_OUTPUT.PUT_LINE('Nuevo valor de variable v_comm es : ' || v_comm);
END;

Declarar e Inicializar Variables PL/SQL

En el primer ejemplo, el bloque inicialmente sólo muestra el mensaje Mi nombre es ya que la variable v_Minombre no fue inicializada con valor en la sección de Declaración de variables (posee valor NULL). La segunda vez, además de mostrar el mensaje Mi nombre es muestra el valor asignado a la variable v_Minombre (Juan) en la sección de Ejecución.

El bloque del segundo ejemplo, muestra para la variable Minombre el valor Roberto, ya que a pesar de haber sido inicializa en la sección de Declaración de variables con el valor Juan en la sección de Ejecución antes de mostrar el mensaje se le modifica el valor a Roberto.

-- ejemplo
DECLARE
  v_Minombre VARCHAR2(20);
BEGIN DBMS_OUTPUT.PUT_LINE('Mi nombre es: ' || v_minombre);
  v_Minombre := 'Juan';
  DBMS_OUTPUT.PUT_LINE('Mi nombre es: ' || v_minombre);
END;

-- ejemplo
DECLARE
  v_Minombre VARCHAR2(20):= 'Juan';
BEGIN
  v_Minombre:= 'Roberto';
  DBMS_OUTPUT.PUT_LINE('Mi nombre es: ' || v_minombre);
END; 

Consideraciones para Declarar e Inicializar Variables PL/SQL

Algunas pautas a seguir cuando se declaran variables PL/SQL son:

• Seguir las convenciones de nomenclatura para nombres de variables, por ejemplo, para representar una constante que almacene el nombre c_nombre. Del mismo modo, para nombrar una variable que almacene el nombre v_nombre.
  
• Utilizar identificadores significativas y apropiados para las variables. Por ejemplo, si se van a almacenar el salario mínimo y máximo como nombre de variables v_salario_min y v_salario_max en lugar de salary1 y salary2.
  
• Si se utiliza la restricción NOT NULL, se debe asignar un valor cuando se declara la variable.
  
• En declaraciones de constantes, la palabra clave CONSTANT debe preceder a la especificación del tipo de dato. Una constante se debe inicializar en su declaración, de lo contrario, se obtendrá un error de compilación. Después de inicializar una constante, no puede cambiar su valor. Ejemplo: c_valor_bono CONSTANT NUMBER(8) := 50000;
  
• Inicializar las variables de una expresión con el operador de asignación (: =) o con la palabra reservada DEFAULT. Si no se asigna un valor inicial , la variable contendrá NULL por defecto hasta que se le asigne un valor. Para asignar o reasignar un valor a una variable se debe escribir una sentencia de asignación PL/SQL. Es una buena práctica de programación inicializar todas las variables.
  
• Dos objetos pueden tener el mismo nombre sólo si están definidos en diferentes bloques. Se pueden calificar con etiquetas y utilizarlas.
  
• Evitar el uso de nombres de columna como identificadores. Si se utilizan las variables PL/SQL en sentencias SQL y tienen el mismo nombre que una columna de tabla, el servidor Oracle asume que es la columna a la que se está haciendo referencia . Aunque la sentencia puede funcionar, esta práctica hace que el código no sea fácil de leer y mantener.
  
• Utilizar la restricción NOT NULL cuando la variable debe contener un valor . No es posible asignar valores nulos a una variable definida como NOT NULL. La restricción NOT NULL debe ser seguida por una cláusula de inicialización.
  

Tipos de Variables

Cada variable PL/SQL tiene un tipo de datos, que especifica un formato de almacenamiento, restricciones y un rango de valores válidos. PL/SQL admite cinco categorías: escalares, compuesto, de referencia, de objetos grandes ( LOB ), de tipos de datos y objetos que se pueden usar para declarar variables, constantes y punteros:

• Tipos de datos escalares: los tipos de datos escalares tienen un solo valor. El valor depende del tipo de datos de la variable. PL / SQL también permite variables booleanas.
  
• Tipos de datos compuestos: los tipos de datos compuestos contienen elementos internos que son escalar o compuesto. Los Registros y Tablas son ejemplos de tipos de datos compuestos.
  
• Tipos de datos de referencia: los tipos de datos de referencia tienen valores llamados punteros que apuntan a un lugar de almacenamiento.
  
• Tipos de datos LOB: los tipos de datos LOB tienen valores llamados localizadores que especifican la ubicación de los objetos grandes (como imágenes gráficas ) que se almacenan fuera de la tabla.
  
• Variables Bind: son variables de enlace que se declara en el entorno del servidor y se puede utilizar después para transmitir valores de ejecución.
  

• TRUE representa un valor booleano.
• 25-ENE-09 representa una fecha.
• La imagen representa un BLOB.
• El texto del cuento de Blancanieves puede representar un tipo de dato VARCHAR2 o CLOB.
• 256120.08 representa un tipo de datos NUMBER con precisión y escala.
• El cinta de película representa un BFILE.
• El nombre de la ciudad de Atlanta representa un tipo de datos VARCHAR2.

Tipos de Datos Escalares

PL/SQL proporciona una variedad de tipos de datos predefinidos. Por ejemplo, se puede elegir desde un tipo de dato entero, punto flotante, caracter, booleano, fecha, colección, y LOB. A continuación, se describirán los tipos básicos que se utilizan con frecuencia en los programas de PL/SQL.
Un tipo de datos escalar posee un valor único y no tiene componentes internos. Los tipos de datos escalares se pueden clasificar en cuatro categorías: número, caracter, fecha y booleanos. Los tipos de datos de caracteres y número tienen subtipos que asocian un tipo base. Por ejemplo, INTEGER y POSITIVE son subtipos del tipo base NUMBER.

CHAR [(largo_máximo)]: Tipo base para datos caracter de largo fijo hasta 32.767 bytes. Si no se especifica un valor máximo, el largo por defecto 1.

VARCHAR2(largo_máximo): Tipo base para datos caracter de largo variable hasta 32.767 bytes. No existe un tamaño por defecto para este tipo.

NUMBER[(presición,escala)]: Número de precisión p dígitos (total de dígitos) y escala s (del total de dígitos cuántos son decimales). La precisión de p va entre 1 y 38. La escala puede tener un rango de -84 a 127 y puede ser omitido, con lo cual se considerará que su valor por defecto es 0. El valor por defecto de p es 38.

BINARY_INTEGER: Tipo base para enteros entre -2.147.483.647 y 2.147.483.647.

PLS_INTEGER: Tipo base para números enteros entre -2.147.483.647 y 2.147.483.647. Necesitan menos almacenamiento y son más rápidos que los BINARY_INTEGER. Las operaciones aritméticas sobre valores PLS_INTEGER y BINARY_INTEGER son más rápidas que sobre valores NUMBER.

BINARY_FLOAT: Representa el número de punto flotante en formato IEEE 754. Requiere 5 bytes para almacenar el valor.

BINARY_DOUBLE: Representa el número de punto flotante en formato IEEE 754. Se requiere 9 bytes para almacenar el valor.

DATE: Tipo base fecha y hora. Date incluye la hora del día en segundos. El rango de fechas está entre 4712 A.C y 9999 D.C.

TIMESTAMP [(precisión)]: Este tipo de dato es una extensión de DATE, almacena año, mes, día, hora, minuto, segundo y fracción de segundo. La precisión indica el número de dígitos para la fracción de segundos. El rango es de 0 a 9 y el valor por defecto es 6.

TIMESTAMP [(precisión)] WITH TIME ZONE: Este tipo de dato amplía el tipo de datos TIMESTAMP, incluye un desplazamiento de zona horaria. El desplazamiento de la zona horaria es la diferencia (en horas y minutos) entre la hora local y la hora universal coordinada (UTC), anteriormente conocido como meridiano de Greenwich. En la sintaxis, el parámetro de precisión es opcional y especifica el número de dígitos de la parte fraccionaria del campo de los segundos. Para especificar la precisión se debe utilizar un número entero en el rango de 0-9. El valor predeterminado es 6.

TIMESTAMP [(precisión)] WITH LOCAL TIME ZONE: También amplía el tipo de datos TIMESTAMP, incluye un desplazamiento de zona horaria. El desplazamiento de la zona horaria es la diferencia (en horas y minutos) entre la hora local y la hora universal coordinada (UTC), anteriormente conocido como meridiano de Greenwich. En la sintaxis, el parámetro de precisión opcional especifica el número de dígitos de la parte fraccionaria del campo de los segundos. No se puede utilizar una constante simbólica o variable para especificar la precisión, es necesario utilizar un literal entero en el rango de 0-9. El valor predeterminado es 6. Este tipo de datos difiere de TIMESTAMP WITH TIME ZONE en que al insertar un valor en una columna de base de datos, el valor se normaliza con la zona horaria de base de datos, y el desplazamiento de zona horaria no se almacena en la columna. Al recuperar el valor, el servidor Oracle devuelve el valor de su zona horaria sesión local.

INTERVAL YEAR [(precisión)] TO MONTH: este tipo de dato se usa para almacenar y manipular los intervalos de años y meses. En la sintaxis, la precisión especifica el número de dígitos en el campo año. No se puede utilizar una constante simbólica o variable para especificar la precisión, es necesario utilizar un literal entero en el rango 0-4. El valor predeterminado es 2.

INTERVAL DAY [(precisión1)] TO SECOND [(precisión2)]: Se utiliza este tipo de dato para almacenar y manipular los intervalos de días, horas, minutos y segundos. En la sintaxis, precisión1 y precisión2 especifican el número de dígitos en el campo de día y en el campo segundo, respectivamente. En ambos casos, no se puede utilizar una constante simbólica o variable para especificar la precisión, es necesario utilizar un literal entero en el rango de 0-9. Los valores por defecto son 2 y 6, respectivamente.

BOOLEAN: Tipo base que almacena uno de tres posibles valores usados para cálculos lógicos: TRUE, FALSE o NULL.

Declarando Variables Escalares

DECLARE
  v_trabajo_emp      VARCHAR2(9);
  v_cont_loop          BINARY_INTEGER := 0;
  v_dept_total_sal   NUMBER(9,2) := 0;
  v_fecha_orden      DATE := SYSDATE + 7;
  c_porc_impto        CONSTANT NUMBER(3,2) := 8.25;
  v_valido                BOOLEAN NOT NULL := TRUE;

En el ejemplo, las variables se definen de la siguiente manera:
v_trabajo_emp: Variable para almacenar un puesto de trabajo del empleado.
v_cont_loop: Variable para contar las iteraciones de un bucle o loop; inicializado a 0.
v_dept_total_sal: Variable para acumular el salario total de un departamento; inicializada con valor 0 (cero).
v_fecha_orden: Variable para almacenar la fecha de envío de la orden; inicializada a una semana más de la fecha actual.
c_porc_impto: Constante para el porcentaje de impuesto (que no cambia nunca durante todo el bloque PL/SQL); se establece con valor 8,25.
v_valido: Flag para indicar si un dato es válido o inválido; inicializado en TRUE

Declarando Variables con Atributo %TYPE

Generalmente las variables PL/SQL son declaradas para guardar y manipular datos almacenados en la Base de Datos. Cuando se declaran variables PL/SQL para guardar valores de columnas, se debe asegurar que la variable es del tipo de dato y largo correcto. Si esto no es así un error PL/SQL ocurrirá durante la ejecución. Se puede usar el atributo %TYPE para declarar una variable según otra variable declarada previamente o según una columna de una tabla de la Base de Datos.

El atributo %TYPE es usado con frecuencia cuando el valor almacenado en la variable será obtenido desde una tabla en la Base de Datos. Cuando se usa el atributo %TYPE para declarar una variable se debe usar como prefijo el nombre de la tabla y columna de la tablas de la Base de Datos a la que se hace referencia. Si se hace referencia a una variable declarada previamente, el prefijo es el nombre de la variable.

En la Sintaxis:

• identificador: es el nombre de la variable para almacenar los datos.
• tabla.nombre_columna: hace referencia al tipo de dato de la columna en la tabla sobre la cual la variable está basada.
• variable: hace referencia a la variable sobre la cual la nueva variable que se define está basada.

En el ejemplo, la variable v_emp_apellido se define con el mismo tipo de datos que la columna last_name de la tabla employees. El atributo % TYPE proporciona el tipo de dato la columna de la tabla a la cual se está haciendo referencia. La variable v_min_balance se define para ser del mismo tipo de dato de la variable de v_balance. El atributo % TYPE también proporciona el tipo de dato de una variable ya definida. La restricción NOT NULL de la columna de la tabla a la cual se hace referencia no se aplica a las variables que se declaran utilizando % TYPE. Por lo tanto, si se declara una variable utilizando el atributo % TYPE que utiliza una columna de una tabla definida como NOT NULL se le puede asignar el valor NULL a la variable.

Ventajas del Atributo %TYPE

Se pueden evitar errores causados por tipo de datos erróneo o por el largo incorrecto.

Se puede evitar codificar en duro el tipo de datos de una variable. Esto significa que no se necesita modificar la declaración de la variable si la definición de la columna cambia. Si se tiene alguna variable para una tabla en particular sin usar el atributo %TYPE el bloque PL/SQL arrojará un error si la columna para la cual la variable fue declarada se ha modificado.

Cuando se utiliza el atributo %TYPE, PL/SQL determina el tipo y tamaño de la variable cuando el bloque es compilado.

Variables Bind

Son variables que se crean en un entorno de host. Por esta razón, a veces se llaman variables Host.

Se crean en el ambiente donde se está trabajando y no en la sección declarativa de un bloque PL/SQL. Las variables declaradas en un bloque PL/SQL sólo están disponibles al ejecutar el bloque. Después de ejecutar el bloque, la memoria utilizada por la variable se libera. Sin embargo, las variables Bind son accesibles incluso después de ejecutar el bloque. Por lo tanto, cuando se crean, las variables Bind se pueden utilizar y manipular por múltiples subprogramas. Pueden ser utilizados en sentencias SQL y bloques PL/SQL como cualquier otra variable. Estas variables se pueden pasar como valores en tiempo de ejecución dentro o fuera de subprogramas PL/SQL. Para crear una variable Bind en SQL Developer, se debe utilizar el comando VARIABLE. Para imprimir su resultado se debe utilizar el comando PRINT. Para mostrar el resultado de todas las variables Binf definidas se debe usar el comando PRINT sin indicar un nombre de variable.

Si se define una variable Bind de tipo numérico, no puede especificar la precisión y escala. Sin embargo, se puede especificar el tamaño si se define una variable Bind de tipo carácter.

En el ejemplo, se define la variable Bind b_resultado en la cual se almacena el resultado de la expresión de la sentencia Select.

-- ejemplo

VARIABLE b_emp_salario NUMBER
BEGIN
   SELECT salary  
        INTO :b_emp_salario 
      FROM  employees 
    WHERE employee_id = 178;  
END;
/
PRINT b_emp_salario

SELECT first_name, last_name 
   FROM employees 
 WHERE salary = :b_emp_salario;

En el ejemplo, se define la variable Bind b_emp_salario la que es usada en el bloque Pl/SQL para almacenar el salario del empleado 178. Al imprimir el valor de la variable b_emp_salario se muestra el valor 7000. Posteriormente esta misma variable se usa en la sentencia Select que muestra el primer nombre y apellido de los empleados que poseen un salario igual al valor de la variable Bind b_emo_salario. Es decir, la sentencia Select muestra a los empleados que poseen un salario igual a 7000.

Variables Tipos de Datos Compuestos

Un tipo de dato Compuesto tiene componentes internos (campos) que pueden ser manipulados individualmente. Los tipos de datos compuestos (también conocidos como colecciones) son de TABLE, RECORD, NESTED TABLE y VARRAY. Se debe usar tipo dato RECORD para tratar datos relacionados, pero diferentes, como una unidad lógica. Usar tipo de dato TABLE para referenciar y manipular un grupo de datos como un solo objeto.

Creación de un Registro PL/SQL

Como estos no son tipos de datos predefinidos como son para las variables escalares, primero de debe definir el tipo registro y después declarar un identificador usando el tipo definido. Por lo tanto, para poder trabajar con un Registro PL/SQL se deben efectuar dos pasos:

• Definir el Tipo Registro en la sección declarativa de un bloque PL/SQL.
• Declarar una variable del Tipo Registro definido.

En la sintaxis para definir el Tipo Registro:

• nombre_tipo : es el nombre del tipo registro PL/SQL (RECORD), este identificador es usado para declarar registros.
• declaración_campo :
  
  • nombre_campo : es el nombre del o los campos en el registro.
  • tipo_campo : es el tipo de dato del campo (cualquier tipo de dato PL/SQL excepto REF CURSOR. Se puede usar el atributo %TYPE y %ROWTYPE). Se puede usar NOT NULL y DEFAULT.
    
  • expr : valor inicial.

En la sintaxis para declarar una variable del Tipo de Registro definido:

• identificador: es el nombre de la variable a la que se asociará el tipo registro PL/SQL.
• nombre_tipo: es el tipo registro PL/SQL definido anteriormente

DECLARE
  TYPE tipo_registro_empleado IS RECORD
    (last_name   VARCHAR2(25),
     job_id          VARCHAR2(10),
     salary           NUMBER(8,2));
  registro_empleado     tipo_registro_empleado;

DECLARE
 TYPE tipo_reg_empleado IS RECORD
    (id_emp           NUMBER(6) NOT NULL := 100,
     apell_emp      employees.last_name%TYPE,
     job_emp         employees.job_id%TYPE);
  reg_empleado     tipo_reg_empleado;

En el primer ejemplo, se define un tipo Registro PL/SQL tipo_registro_empleado con los campos last_name, job_id y salary. Posteriormente se define la variable registro_empleado a la que se le asocia el tipo Registro PL/SQL definido anteriormente.
En el segundo ejemplo, se define el tipo Registro PL/SQL tipo_reg_empleado con los campos id_emp no nulo con valor por defecto de 100, apell_emp y job_emp en los que se utiliza el atributo %TYPE para definir sus tipos de datos que en este caso corresponden a las columnas last_name y job_id de tabla employees respectivamente. Posteriormente se define la variable reg_empleado a la que se le asocia el tipo Registro PL/SQL definido anteriormente.

Los campos en el registro deben ser accesados por su nombre. Para referenciar o inicializar un campo individual.
En el primer ejemplo, se hace referencia al campo job_id del registro_empleado.
En el segundo ejemplo, se asigna al valor ST_CLERK al campo job_id del registro registro_empleado.

Registros usando Atributo %ROWTYPE

• El atributo %ROWTYPE es usado para declarar un registro que almacena una fila completa de un tabla o vista.
• Declara una variable de acuerdo a una colección de columnas de una tabla o columna de Base de Datos.
• Los campos en el registro toman los nombres y tipos de dato desde las columnas de la tabla o vista al la que se referencia.
• Es precedido con el nombre de la tabla o vista de la Base de Datos.
• El registro también almacena una fila completa obtenidas desde un cursor o variable cursor.

En la sintaxis para definir el Tipo Registro:

• identificador: es el nombre escogido para el registro.
• referencia: es el nombre de la tabla, vista, cursor o variable cursor sobre el cual registro está basado. La tabla o vista debe existir para que esta referencia sea valida.
  

DECLARE  
registro_emp  employees%ROWTYPE;

BEGIN   
registro_emp.salary:=10000;

En el ejemplo, se declara la variable registro_emp que será un registró cuyos campos serán todas las columnas (nombres y tipos de datos) de la tabla employees. Si posteriormente se quiere referenciar algunos de los campos del registro se debe hacer mención al registro.campo. En este caso se está haciendo referencia al salario.

CREATE TABLE empleados_retirados AS SELECT * FROM employees;
TRUNCATE TABLE empleados_retirados;

DECLARE
 reg_emp employees%ROWTYPE; 
 BEGIN SELECT * INTO reg_emp
       FROM employees WHERE employee_id = 124;
/* Se hace referencia en forma explícita cada campo del registro para insertar sus valores en la tabla */ INSERT INTO empleados_retirados
       VALUES (reg_emp.employee_id, reg_emp.first_name, reg_emp.last_name,
                      reg_emp.email, reg_emp.phone_number, reg_emp.hire_date, 
                      reg_emp.job_id, reg_emp.salary,  reg_emp.commission_pct, 
                      reg_emp.manager_id, reg_emp.department_id);
/* Otra opción para insertar es usando el registro completo como se muestra a continuación */
       INSERT INTO empleados_retirados VALUES reg_emp;
END;

En el bloque del ejemplo, se declara la variable reg_emp que es un registro cuyos campos son todas las columnas (nombres y tipos de datos) de la tabla
employees. Este registro es utilizado para almacenar la fila completa del empleado 124. Los valores almacenados en los campos del registro son utilizados
para insertar una nueva fila en la tabla empleados_retirados (creada con anterioridad). En este ejemplo existen dos opciones para insertar lo valores
almacenados en el registro. La primera es referenciar cada campo del registro en forma explícita y la segunda es utilizar el registro completo.

Creación de Tablas INDEX BY

Son tipos de datos compuestos (colecciones) definidas por el usuario. Usan una clave primaria para proveer un acceso a las filas similar a como se leen los valores de un arreglo.

Son estructuras PL/SQL que pueden tener una columna para almacenar un valor y un identificador único para esa columna de datos:

• La clave primaria de tipo BINARY_INTEGER, PLS_INTEGER. También puede ser del tipo VARCHAR2 (o subtipos) que cumple la función de índice ó identificador para la columna de datos.
  
• Una columna de datos escalares o records, la cual almacena los elementos de la tabla INDEX BY.

Al igual que el tamaño de una tabla de base de datos, el tamaño de una tala INDEX BY no tiene restricciones. Es decir, el número de filas en una tabla
INDEX BY puede aumentar de forma dinámica, de modo que la tabla crece a medida que se agregan nuevas filas. Sin embargo, la clave primaria está
restringido al valor máximo que un PLS_INTEGER puede contener.
Los elementos que se encuentran en la tabla no guardan ningún orden en particular, no se almacenen de manera contigua en memoria. No son pobladas
automáticamente cuando se declaran. Esto se debe programar en el bloque PL/SQL.
Para poder trabajar con una tabla INDEX BY se deben efectuar dos pasos:

• Declarar un tipo de dato TABLE.
• Declarar una variable de ese tipo de dato.

Sintaxis para declarar un tipo de dato TABLE:

• nombre_tipo: es el nombre del tipo es el nombre del tipo TABLE.
• tipo_columna: tipo de dato escalar o compuesto como VARCHAR2, DATE, NUMBER o %TYPE (se puede usar el atributo %TYPE para proporcionar el tipo de dato columna).
  

Sintaxis para declarar una variable del tipo TABLE definido:

Estos no son tipos de datos predefinidos (para registros PL/SQL) como son para las variables escalares. Por eso se debe crear primero el tipo registro y
entonces declarar un identificador usando el tipo.

• identificador: es el nombre de la variable a la que se asociará el tipo Tabla PL/SQL.
• nombre_tipo: es el tipo tabla PL/SQL (TABLE) definido anteriormente

DECLARE
TYPE ename_tabla_type IS TABLE OF 
 employees.last_name%TYPE
 INDEX BY PLS_INTEGER;
... 
v_ename_tabla   ename_tabla_type;

nombre_tabla_INDEX_BY(índice)

En el primer ejemplo, se declara un tipo Tabla ename_tabla_type para almacenar el apellido de los empleados. Posteriormente se define la variable v_ename_tabla a la que se le asocia el tipo Tabla PL/SQL definido anteriormente.

En la sintaxis para hacer referencia a elementos de la Tabla INDEX BY:

• índice : pertenece al tipo PLS_INTEGER que representa la fila de la tabla INDEX BY que se desea referenciar.

Referencia a Elementos de la Tabla INDEX BY

Los métodos para manejar una Tabla INDEX BY son:

• EXISTS(n) : Devuelve TRUE si el elemento de la posición n en la tabla existe.
• COUNT: Retorna el número de elementos que una tabla PL/SQL actualmente contiene.
• FIRST y LAST: Devuelve el primer y último (el más pequeño y más grande) índice en una tabla PL/SQL. Devuelve NULL si la tabla está vacía.
• PRIOR(n): Devuelve el número del índice que precede al índice n en una tabla PL/SQL.
• NEXT(n): Devuelve el número del índice que sigue al índice n en una tabla PL/SQL.
• DELETE: DELETE remueve todos los elementos de una tabla PL/SQL.
  DELETE(n) remueve n elementos de una tabla PL/SQL.
  DELETE (m,n) remueve todos los elementos en un rango m …n de una tabla PL/SQL.

CREATE TABLE datos_tabla 
(nombre  VARCHAR2(25),
 fecha   DATE);

DECLARE
  TYPE ename_tabla_type IS TABLE OF
    employees.last_name%TYPE 
    INDEX BY PLS_INTEGER;
  TYPE hiredate_tabla_type IS TABLE OF DATE
    INDEX BY PLS_INTEGER;
  ename_tabla       ename_tabla_type; 
  hiredate_tabla    hiredate_tabla_type;
BEGIN
  ename_tabla(1) := 'CAMERON';
  hiredate_tabla(8) := SYSDATE + 7;
   INSERT INTO datos_tabla
  VALUES(ename_tabla(1), hiredate_tabla(8));
END; 

En el ejemplo, primero se crea la tabla datos_tabla . Posteriormente, en el bloque PL/SQL se crean dos tablas INDEX BY ename_tabla_type y hiredate_tabla_type . En la primera fila de la tabla BY ename_tabla_type tendrá el valor CAMERON y en la fila 8 de la tabla hiredate_tabla_type tendrá la fecha actual aumentada en 7 días (el ejemplo se ejecutó el 11/02/2014). Posteriormente se insertar en la tabla datos_tabla los valores almacenados en ambas tablas INDEX BY.

Variables Tipos de Datos LOB

Lobs (largo objetos) es el medio para almacenar cantidad de datos grande. Se pueden almacenar bloques de datos (como imágenes de gráficos, videos y formatos de música) por sobre los 4GB:

• CLOB (caracter largo objeto): Usado para almacenar grandes bloques de datos caracteres en la Base de Datos.
  
• BLOB (binar largo objeto): Almacena objetos de estructuras binarias en la Base de Datos. La Base de Datos no interpreta el dato. Las aplicaciones externas que usa este dato debe interpretar el dato.
  
• BFILE (binar fila): Almacena archivos binarios grandes. A diferencia de otros Lobas, BFILES no están almacenados en la Base de Datos, ellos están fuera de la Base de Datos. Sólo un puntero para el BFILE es almacenado en la Base de Datos.
  
• NCLOB (nacional lenguaje caracter largo objeto): Es usado para almacenar bloques de datos que tienen que ver con lenguaje.