20.
Trabajando con tipos de Datos compuestos
_________________________________________________________________________
ORACLE SQL y PL/SQL
1
20. Trabajando con tipos de Datos compuestos
Objetivos
Objetivos de la Leccin
En esta leccin, aprender mas detalles acerca de los tipos de datos compuestos y sus
usos.
_________________________________________________________________________
ORACLE SQL y PL/SQL
2
20. Trabajando con tipos de Datos compuestos
Registros y Tablas
Los tipos de datos compuestos (tambin conocidos como colecciones) son Registro,
tabla, tabla anidada y Varray. Use el tipo de dato RECORD (registro) para tratar como una
unidad lgica, datos relacionados pero distintos. Use el tipo de dato TABLE (Tabla) para
manipular colecciones de datos como un solo objeto. Los tipos de datos de tablas
anidadas y VARRAY no son cubiertos en este curso.
Un registro es un grupo de elementos de datos relacionados, almacenados en campos,
cada uno con su propio nombre y tipo de datos. Una tabla contiene una columna y una
llave primaria que se utiliza para accesar a los registros. Una vez definido, las tablas y
registros pueden ser reutilizados.
_________________________________________________________________________
ORACLE SQL y PL/SQL
3
20. Trabajando con tipos de Datos compuestos
Registros de PL/SQL
Un registro es un grupo relacionado de elementos almacenados en campos, cada uno
con su propio nombre y tipo de datos. Por ejemplo, suponga que tienen datos distintos
sobre un empleado como el nombre, el sueldo, fecha de contratacin, etctera. Estos
datos son distintos en tipo, pero estn lgicamente relacionados. Un registro que puede
contener tales campo: el nombre, el sueldo, y la fecha de contratacin de un empleado y
tratar los datos como una unidad lgica. Cuando declara un tipo de registro para estos
campos, pueden ser manipulados como una unidad.
Cada registro definido puede tener tantos campos como sea necesario.
Los registros pueden ser asignados con valores iniciales y pueden ser definidos como
NOT NULL.
Los campos sin valores iniciales son inicializados a NULL.
La palabra DEFAULT tambin puede ser usado al definir los campos.
Puede definir tipos de registro y declarar registros definidos por usted mismo, en la parte
declarativa de cualquier bloque, subprograma, o paquete.
Puede declarar y referenciar registros anidados. Un registro puede ser un componente de
otro registro.
_________________________________________________________________________
ORACLE SQL y PL/SQL
4
20. Trabajando con tipos de Datos compuestos
Definiendo y Declarando un registro PL/SQL
Para crear un registro, defina un tipo RECORD y declare los registros de este tipo.
En la sintaxis,
type_name
Es el nombre del tipo RECORD. Este identificador es
usado para declarar registros.
field_name
Es el nombre del campo dentro del registro.
field_type
Es el tipo de dato del campo. Esto representa
cualquier
tipo
de
dato
PL/SQL
excepto
REF
CURSOR. Puede usar los atributos %ROWTYPE y el
%TYPE
expr
es el valor inicial del campo.
El constraint NOT NULL previene la asignacin del valor nulo a aquellos campos.
Asegrese de inicializar los campos no nulos.
_________________________________________________________________________
ORACLE SQL y PL/SQL
5
20. Trabajando con tipos de Datos compuestos
Creando un registro PL/SQL
Las declaraciones de campos son como declaraciones de variables. Cada campo tiene un
nombre nico y un tipo de datos especfico. No hay ningn tipo de dato predefinido para
los registros de PL/SQL, como lo hay para variables escalares. Por lo tanto, debe crear el
tipo de dato primero y luego declarar un identificador que use este tipo de dato. El ejemplo
siguiente muestra que puede usar el atributo %TYPE para especificar el tipo de dato del.
Campo:
DECLARE
TYPE emp_record_type IS RECORD
(empno NUMBER(4) NOT NULL := 100,
ename [Link]%TYPE,
job
[Link]%TYPE);
emp_record
emp_record_type;
...
Nota: Puedes agregar el constraint NOT NULL a cualquier declaracin de campo para
prevenir la asignacin de nulos al mismo. Sin embargo, todos los campos NOT NULL
tienen que ser inicializados.
_________________________________________________________________________
ORACLE SQL y PL/SQL
6
20. Trabajando con tipos de Datos compuestos
Referenciando e Inicializando Registros
Los campos de un registro son accesados por su nombre. Para referir o inicializar un
campo individual, use el punto . y la siguiente sintaxis:
nombre_registro.nombre_campo
Por ejemplo, refirase al campo job en el registro emp_record as:
emp_record.job ...
Entonces, puede asignar un valor al campo de registro as:
emp_record.job := 'CLERK';
En un bloque o un subprograma, los registros definidos por el usuario son instanciados
cuando entra en ejecucin el bloque o el subprograma y dejan de existir cuando termina
el bloque o el subprograma.
Valores de Asignacin a Registros
Puede asignar una lista de valores comunes a un registro usando las sentencias SELECT
o FETCH. Asegrese de que los nombres de columna aparecen en el mismo orden que
los campos en su registro. Tambin puede asignar un registro a otro si tienen el mismo
tipo de datos. Un registro definido por usuario y un registro de %ROWTYPE nunca tienen
el mismo tipo de dato.
_________________________________________________________________________
ORACLE SQL y PL/SQL
7
20. Trabajando con tipos de Datos compuestos
Declarando Registros con el Atributo %ROWTYPE.
Para declarar un registro basado en una coleccin de columnas de una tabla o vista de
base de datos, use el atributo de %ROWTYPE. Los campos en el registro toman sus
nombres y tipos de dato de las columnas de la tabla o la vista. El registro tambin puede
almacenar una fila entera de datos trados de una variable de cursor o un cursor.
En el ejemplo siguiente un registro es declarado usando %ROWTYPE como un tipo de
dato especfico.
DECLARE
emp_record
emp%ROWTYPE;
El registro emp_record, consistir en los siguientes campos, que reflejan todos las
columnas pertenecientes a la tabla EMP.
(ename
job
sal
comm
VARCHAR2(10),
VARCHAR2(9),
NUMBER,
NUMBER)
_________________________________________________________________________
ORACLE SQL y PL/SQL
8
20. Trabajando con tipos de Datos compuestos
Declarando Registros con el Atributo %ROWTYPE (continuacin)
DECLARE
identifier
Donde :
reference%ROWTYPE;
identifier
Es el nombre escogido para el registro.
reference
Es el nombre de la tabla, vista, cursor, o la variable de
cursor sobre la que se base el registro. Debe
asegurarse que esta referencia es vlida cuando
declara el registro (es decir la tabla o la vista debe
existir).
Para referirse un campo individual, use la notacin y la sintaxis siguiente:
Nombre_registro.nombre_campo
Por ejemplo, si requiere la comisin del registro emp_record, la sintaxis es:
emp_record.comm
Entonces puede asignar un valor al campo de registro as:
emp_record.sal := 75000;
_________________________________________________________________________
ORACLE SQL y PL/SQL
9
20. Trabajando con tipos de Datos compuestos
El atributo %TYPE
Ejemplos
La primera declaracin en la diapositiva crea un registro con los mismos nombres y tipos
de datos de los campos de la tabla de DEPT. Los campos son DEPTNO, DNAME, Y LA
LOCATION.
La segunda declaracin crea un registro con los mismos nombres y tipos de datos como
un registro de la tabla EMP. Los campos son EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, y DEPTNO.
En lo siguiente, selecciona valores de columna en un registro llamado item_record.
DECLARE
item_record item%ROWTYPE;
...
BEGIN
SELECT * INTO item_record
FROM
item
WHERE ...
_________________________________________________________________________
ORACLE SQL y PL/SQL
10
20. Trabajando con tipos de Datos compuestos
Tablas PL/SQL
Los objetos tipo tabla son llamados tablas PL/SQL. Son modelados como (pero no son lo
mismo) tablas de la base de datos. Las tablas PL/SQL utilizan una llave primaria que
proporciona el acceso al contenido de la tabla.
Una tabla de PL/SQL:
Es similar a una coleccin
Debe contener dos componentes:
o Una llave primaria de tipo de dato BINARY_INTEGER que indexa la tabla
PL/SQL.
o Una columna de tipo escalar o de registro, la cual almacena los elementos
de la tabla PL/SQL.
Puede aumentar dinmicamente porque es libre.
_________________________________________________________________________
ORACLE SQL y PL/SQL
11
20. Trabajando con tipos de Datos compuestos
Declarando tablas PL/SQL
Hay dos pasos implicados en la creacin de una tabla PL/SQL.
1. Declarar un tipo TABLE
2. Declarar una variable del tipo anterior.
En la sintaxis,
type_name
es el nombre del tipo tabla. Es un tipo especificado que se
utiliza en una declaracin subsecuente de tabla PL/SQL.
column_type
es cualquer tipo escalar (no compuesto) tal como
VARCHAR2, DATE, o NUMBER. Puedes utilizar el atributo
%TYPE para heredar la caracterstica de una columna
identifier
es el nombre del identificador que representa toda la tabla
PL/SQL.
_________________________________________________________________________
ORACLE SQL y PL/SQL
12
20. Trabajando con tipos de Datos compuestos
Declarando tablas PL/SQL (Continuacin)
El constraint NOT NULL previene que valores nulos sean asignados a los campos de la
tabla PL/ SQL para este tipo.
No inicialices la TABLE PL/SQL.
Declara una variable PL/SQL para almacenar la fecha.
DECLARE
TYPE date_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
date_table date_table_type;
_________________________________________________________________________
ORACLE SQL y PL/SQL
13
20. Trabajando con tipos de Datos compuestos
Estructura de la tabla PL/SQL
Al igual que el tamao de una tabla de base de datos, el tamao de una tabla PL/SQL es
libre. Es decir el nmero de registros en una tabla PL/SQL puede aumentar
dinmicamente, entonces la tabla PL/SQL crece con las nuevas filas que son agregadas.
Las tablas de PL/SQL pueden tener una columna y una llave primaria, ninguna de las
cuales pueden ser nombradas. La columna puede pertenecer cualquier tipo de datos
escalar o un tipo de dato de registro, pero la llave primaria debe pertenecer al tipo
BINARY_INTEGER. No puede inicializar una tabla PL/SQL en su declaracin.
_________________________________________________________________________
ORACLE SQL y PL/SQL
14
20. Trabajando con tipos de Datos compuestos
Creando una tabla PL/SQL
No hay ningn tipo de dato predefinido para los registros de PL/SQL, tal como para las
variables escalares. Por lo tanto debe crear el tipo dato primero y luego declarar un
identificador que usa este tipo de dato.
Referenciando una tabla PL/SQL.
Sintaxis
pl/sql_table_name(primary_key_value)
Donde:
primary_key_value
pertenece al tipo BINARY_INTEGER.
Refirase a la tercera fila en la tabla PL/SQL ename_table
ename_table(3) ...
La el rango de magnitud para un tipo BINARY_INTEGER es -2147483647 ... 2147483647,
por tanto, el valor de la llave primaria puede ser negativa. No es necesario que el indice
inicie en 1.
Nota: El atributo EXISTS(i) regresa TRUE si al menos hay un registro con el nmero
indice i. Utiliza el comando EXISTS para prevenir un error al referenciar un elemento de
tabla que no exista.
_________________________________________________________________________
ORACLE SQL y PL/SQL
15
20. Trabajando con tipos de Datos compuestos
Tablas PL/SQL de Registros
Debido a que solo es necesaria una tabla para alojar informacin de todos los registros
de una tabla de la base de datos, la tabla de registros aumenta la funcionalidad de tablas
PL/SQL.
Referenciando una tabla de registros
En el ejemplo de la diapositiva, te puedes referir a los campos de cada registro de la tabla
dept_table, ya que cada elemento de la tabla es un registro.
Sintaxis
table(index).field
Ejemplo
dept_table(15).location := 'Atlanta';
Location representa un campo en la tabla DEPT.
Nota: Puedes utilizar el atributo %ROWTYPE para declarar un registro que represente un
registro alguna tabla de base de datos. La deferencia entre %ROWTYPE y el tipo de dato
RECORD es que el ltimo te permite especificar los tipos de dato de cada campo o
declarar los tipos a tus necesidades.
Mtodos de Tabla PL/SQL
_________________________________________________________________________
ORACLE SQL y PL/SQL
16
20. Trabajando con tipos de Datos compuestos
Un mtodo de tabla PL/SQL es un procedimiento o funcin ya creado que opera sobre
tablas y es llamado con un punto (.)
Sintaxis:
_________________________________________________________________________
ORACLE SQL y PL/SQL
17
20. Trabajando con tipos de Datos compuestos
_________________________________________________________________________
ORACLE SQL y PL/SQL
18
20. Trabajando con tipos de Datos compuestos
_________________________________________________________________________
ORACLE SQL y PL/SQL
19
20. Trabajando con tipos de Datos compuestos
Practice 20
1.
Ejecuta el script lab20_1.sql para crear una nueva tabla que almacene el nombre
de empleados y sus salarios.
SQL> CREATE TABLE
2
(name
2.
top_dogs
salary
VARCHAR2(25),
NUMBER(11,2));
Escribe un bloque de PL/SQL para obtener el nombre del empleado y el salario de
los empleados de la tabla EMP basado en los nmeros del empleado, guarda esta
informacin en la tabla PL/SQL.
a. Declara dos tablas PL/SQL, ENAME_TABLE y SAL_TABLE, para almacenar
temporalmente los nombres y los salarios.
b. Para cada nombre y salario regresados dentro del loop, almacenalos en las
tablas PL/SQL.
c. Termina el loop, transfiere los nombre y los salarios de las tablas PL/SQL dentro
de la tabla TOP_DOGS.
d. Vaca la tabla TOP_DOGS y prueba tu bloque.
Please enter the employee number: 7934
PL/SQL procedure successfully completed.
NAME
SALARY
--------------- --------MILLER
1300
Please enter the employee number: 7876
PL/SQL procedure successfully completed.
NAME
SALARY
--------------- --------ADAMS
1100
_________________________________________________________________________
ORACLE SQL y PL/SQL
20