Oracle Simple y Rapido

ORACLE - Funciones de Caracteres: Rtrim

2008-12-19T18:34:00.000-08:00

La funcion rtrim remueve todos los caracteres especificados comenzando desde la derecha

La sintaxis es:

rtrim( cadena, [ cadena elimina] )

Cadena es la cadena de caracteres de la cual eliminar los valores

Cadena elimina es la cadena de caracteres que tenes que buscar para eliminar

Ejemplos :

rtrim('hola '); retorna 'hola'
rtrim('hola ',''); retorna 'hola'
rtrim('Rosarioddd',d) retorna 'Rosario'

ORACLE - Funciones de Caracteres: Length

2008-12-19T18:25:00.000-08:00

La funcion length retorna la longitud de la cadena expresada en cantidad de caracteres

La sintaxis es:

length(cadena)

Cadena es la cadena de caracteres de la cual queres evaluar su longitud

Ejemplos :

replace(''); retorna null

replace('Hola Mundo'); retorna 10

ORACLE - Funciones de Caracteres: Replace

2008-12-19T18:22:00.000-08:00

La funcion Replace de un cadena de caracteres un caracter por otro.

La sintaxis es:

replace( cadena, caracter a reemplazar, caracter que reemplaza)

cadena1: cadena en la cual se va a buscar el caracter a reemplazar
caracter a reemplazar : es el caracter que se tiene que buscar en la cadena de caracteres y se quiere reemplazar
caracter que reemplaza es opcional : En todos los lugares donde se detecta la existencia de un caracter a reemplazar se reemplaza por el caracter incluido en este parametro, en caso de que se omita este parametro, simplemente se va a eliminar el caracter detectado.

Ejemplos :

replace('12hola12', '12'); retorna 'hola'
replace('mundo22', '2', '3'); return 'mundo33'
replace('0000123', '0'); retorna '123'

ORACLE - Funciones de Caracteres: Concat

2008-12-19T18:06:00.000-08:00

La funcion Concat une dos cadena de caracteres.

La sintaxis es:

to_char( cadena1,cadena2)

cadena1:es el primer string de la cadena
cadena2:es el segundo string de la cadena

Ejemplos :

concat('Hola', ' Mundo'); retorna 'Hola Mundo'.
concat('A', 'AA'); retorna 'AAA'

ORACLE - Funciones Conversión : To_Char

2008-12-19T11:55:00.000-08:00

La funcion To_char convierte un numero o una fecha en un campo string o caracter.

La sintaxis es:
to_char( valor,[mascara],[ nls language])

valor : es el valor que usted numerico o date que ustede quiere convertir a caracter.
mascara es opcional : es el formato que usted quiere usar para convertir el valor a caracter.
nls language es opcional : Es el lenguaje usado para realizar la conversión del valor a caracter.

Ejemplos usando campos numericos

to_char(45.31, '99.9') retorna '45.3'
to_char(9,125.33, '9,999.99') retorna '9,125.33'
to_char(77, '0099') retorna '0077'

Ejemplo usando campos fecha

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

ORACLE : Modificar Estructura de Tablas

2008-09-26T13:39:00.000-07:00

La modificación de la estructura de las tablas con el uso de ALTER permite:
- Agregar nuevas columnas a las tablas.
- Modificar el ancho de la columna.
- Modificar el tipo de datos de la columna(en este caso la columna no tiene que tener datos).
- Modificar la longitud de una columna.

EJEMPLO 1:
ALTER TABLE Clientes
ADD ( saldo integer, sucursal char )

En el ejemplo anterio le agregamos a la tabla CLientes la columna Saldo de tipo entero y la columna Sucursal de tipo caracter.

EJEMPLO 2 :
ALTER TABLE Clientes
MODIFY ( Nombre char(50) );

En el Ejemplo anterior modificamos la longitud del campo Nombre de la tabla clientes que era de tipo caracter que era de char(40) a char(50)

ORACLE - Creación de Secuencias para campos autonumericos

2008-09-26T13:19:00.000-07:00

La codificación numérica de por ejemplo un producto se puede realizar con una creación de una secuencia que automáticamente genera los números enteros en orden ascendente, no siendo necesario recordar cuál fue el último número asignado ; esto evita la duplicidad de códigos.

La secuencia es un objeto que genera valores enteros únicos y se emplean para crear claves primarias numéricas, con el uso del siguiente mandato:

CREATE SEQUENCE codigo_producto INCREMENT BY 1 START WITH 100;

En este caso el primer numero generado será 100, porque le pusimos que comience desde 100

Para registrar un nuevo código con la secuencia definida anteriormente se usa la columna codigo_producto.NEXTVAL, la cual nos dará el siguiente valor que le corresponde a la secuencia.

Para conocer el valor actual de la secuencia, o sea, el último código asignado, se usa la columna codigo_producto.CURRVAL, desde la tabla DUAL del sistema, cuyo fin es poder consultar pseudo_columnas :

SELECT usuario,sysdate,codigo_producto.currval FROM DUAL;

donde:

1.- usuario es el nombre del usuario
2.- sysdate es la fecha del sistema
3.- codigo_producto.currval es el último valor asignado a la secuencia.

EJEMPLO:
INSERT INTO PRODUCTOS_SUC VALUES( codigo_producto.NEXTVAL,'ZAPATOS' );

ORACLE - Funciones de Conversión : To_number

2008-09-22T19:40:00.000-07:00

La funcion to_number convierte un texto en un numero.

La sintaxis es :
to_number( texto1, [ format_mask ], [ nls_language ] )
texto1 es un texto que querra ser convertido en numero.
format_mask es opcional. Este es el formato que será usado para convertir el texto1 en numero
nls_language es opcional. Este es el lenguaje usado en convertir texto1 en numero.

Ejemplo:
to_number('981.53', 9999.99') retornará el numero 981.53
to_number('225', '999') retornará el numero 225
to_number('23', '99') retornará el numero 23

Tanto el formato como el lenguaje son opcionales, por lo cual usted podrá convertir un texto en numero de una forma simple, por ejemplo:
to_number('1210.73'') would return the number 1210.73

ORACLE - Funciones Fecha : Add_Months

2008-08-29T11:36:00.000-07:00

La funcion add_months la fecha inicial mas el numero de meses correspondiente.

La sintaxis es:
add_months( fecha,numero meses)
date : es la fecha de comiendo del calculo (before the n months have been added).
numero de meses : es el numero de meses que hay que sumarle a la fecha.

Example:
add_months('10-Feb-08', 1) would return '10-Mar-08'
add_months('01-Ago-03', 3) would return '01-Nov-03'
add_months('01-Sep-08', -3) would return '01-Jun-08'

Aplica :
* Oracle 8i
* Oracle 9i
* Oracle 10g
* Oracle 11g

ORACLE - Tipos de SQL (DELETE, INSERT, UPDATE)

2008-07-11T11:22:00.000-07:00

Tipos de SQL de Acción (DELETE, INSERT, UPDATE ):
Los SQL son aquellas que no devuelven ningún registro, son las encargadas de acciones como añadir y borrar y modificar registros.

DELETE
Crea un SQL que elimine los registros de una o más de las tablas listadas en la cláusula FROM que cumpla la cláusula WHERE. Este SQL elimina los registros completos, no es posible eliminar el contenido de algún campo en particular.
Su sintaxis es:

DELETE FROM NombreTabla WHERE “criterio selección de datos”

DELETE es especialmente útil cuando se desea eliminar varios registros. En una instrucción DELETE con múltiples tablas, debe incluir el nombre de tabla (Tabla.*). Si especifica más de una tabla desde la que eliminar registros, todas deben ser tablas de muchos a uno. Si desea eliminar todos los registros de una tabla, eliminar la propia tabla es más eficiente que ejecutar una consulta de borrado.

Se puede utilizar DELETE para eliminar registros de una única tabla o desde varios lados de una relación uno a muchos. Las operaciones de eliminación en cascada en una consulta únicamente eliminan desde varios lados de una relación.
Por ejemplo, en la relación entre las tablas Clientes y Pedidos, la tabla Pedidos es la parte de muchos por lo que las operaciones en cascada solo afectaran a la tabla Pedidos. Una consulta de borrado elimina los registros completos, no únicamente los datos en campos específicos. Si desea eliminar valores en un campo especificado, crear una consulta de actualización que cambie los valores a Null.

Una vez que se han eliminado los registros utilizando una consulta de borrado, no puede deshacer la operación. Si desea saber qué registros se eliminarán, primero examine los resultados de una consulta de selección que utilice el mismo criterio y después ejecute la consulta de borrado. Mantenga copias de seguridad de sus datos en todo momento. Si elimina los registros equivocados podrá recuperarlos desde las copias de seguridad.

DELETE FROM Proveedor WHERE Ciudad = 'Cordoba';

INSERT INTO
Agrega un registro en una tabla. Se la conoce como una consulta de datos añadidos. Esta consulta puede ser de dos tipo: Insertar un único registro ó Insertar en una tabla los registros contenidos en otra tabla.

Para insertar un único Registro:
En este caso la sintaxis es la siguiente:

INSERT INTO NombreTabla (Nombrecampo1, Nombrecampo2, Nombrecampo3, .., campoN)

VALUES (valor1, valor2,valor3,..., valorN)

Esta consulta graba en el campo1 el valor1, en el campo2 y valor2 y así sucesivamente. Hay que prestar especial atención a acotar entre comillas simples (') los valores literales (cadenas de caracteres) y las fechas indicarlas en formato mm-dd-aa y entre caracteres de almohadillas (#).

Para insertar Registros de otra Tabla:
En este caso la sintaxis es:

INSERT INTO Tabla [IN base_externa] (campo1, campo2, ..., campoN)

SELECT TablaOrigen.campo1, TablaOrigen.campo2, ….., TablaOrigen. campoN FROM TablaOrigen

En este caso se seleccionarán los campos 1,2, ..., n de la tabla origen y se grabarán en los campos 1,2,.., n de la Tabla. La condición SELECT puede incluir la cláusula WHERE para filtrar los registros a copiar. Si Tabla y TablaOrigen poseen la misma estructura podemos simplificar la sintaxis a:

INSERT INTO Tabla SELECT TablaOrigen.* FROM TablaOrigen

De esta forma los campos de TablaOrigen se grabarán en Tabla, para realizar esta operación es necesario que todos los campos de TablaOrigen estén contenidos con igual nombre en Tabla. Con otras palabras que Tabla posea todos los campos de TablaOrigen (igual nombre e igual tipo).

En este tipo de consulta hay que tener especial atención con los campos contadores o autonuméricos puesto que al insertar un valor en un campo de este tipo se escribe el valor que contenga su campo homólogo en la tabla origen, no incrementándose como le corresponde.

Se puede utilizar la instrucción INSERT INTO para agregar un registro único a una tabla, utilizando la sintaxis de la consulta de adición de registro único tal y como se mostró anteriormente. En este caso, su código específica el nombre y el valor de cada campo del registro. Debe especificar cada uno de los campos del registro al que se le va a asignar un valor así como el valor para dicho campo. Cuando no se especifica dicho campo, se inserta el valor predeterminado o Null. Los registros se agregan al final de la tabla.

También se puede utilizar INSERT INTO para agregar un conjunto de registros pertenecientes a otra tabla o consulta utilizando la cláusula SELECT ... FROM como se mostró anteriormente en la sintaxis de la consulta de adición de múltiples registros. En este caso la cláusula SELECT especifica los campos que se van a agregar en la tabla destino especificada.

La tabla destino u origen puede especificar una tabla o una consulta.

Si la tabla destino contiene una clave principal, hay que asegurarse que es única, y con valores no-Null ; si no es así, no se agregarán los registros. Si se agregan registros a una tabla con un campo Incremental , no se debe incluir el campo Incremental en la consulta. Se puede emplear la cláusula IN para agregar registros a una tabla en otra base de datos.

Se pueden averiguar los registros que se agregarán en la consulta ejecutando primero una consulta de
selección que utilice el mismo criterio de selección y ver el resultado. Una consulta de adición copia los registros de una o más tablas en otra. Las tablas que contienen los registros que se van a agregar no se verán afectadas por la consulta de adición. En lugar de agregar registros existentes en otra tabla, se puede especificar los valores de cada campo en un nuevo registro utilizando la cláusula VALUES. Si se omite la lista de campos, la cláusula VALUES debe incluir un valor para cada campo de la tabla, de otra forma fallará INSERT.

INSERT INTO Clientes SELECT Clientes_Viejos.* FROM Clientes_Nuevos;

INSERT INTO Empleados ( NombreApellido, Sueldo, Cargo)

VALUES ('Alberto Sanches', 12500, 'Gerente');

INSERT INTO Empleados SELECT Vendedores.* FROM Vendedores

WHERE Fecha_Contratacion <>UPDATE

Crea una consulta de actualización que cambia los valores de los campos de una tabla especificada basándose en un criterio específico. Su sintaxis es:

UPDATE Tabla SET Campo1=Valor1, Campo2=Valor2, ... CampoN=ValorN
WHERE Criterio;

UPDATE es especialmente útil cuando se desea cambiar un gran número de registros o cuando éstos se
encuentran en múltiples tablas. Puede cambiar varios campos a la vez. El ejemplo siguiente incrementa los
valores Cantidad en un 15 por ciento y los valores Transporte en un 5 por ciento para aquellos que se hayan enviado a la cuidad de Córdoba.:

UPDATE Facturas SET Cantidad = Cantidad * 1.15, Transporte = Transporte * 1.05

WHERE CiudadEnvío = 'Córdoba';

UPDATE no genera ningún resultado. Para saber qué registros se van a cambiar, hay que examinar primero el resultado de una consulta de selección que utilice el mismo criterio y después ejecutar la consulta de actualización.

UPDATE Empleados SET Organigrama = 5 WHERE Organigrama = 2;

UPDATE Productos SET Stock = Stock * 1.1 WHERE Proveedor = 8 AND Familia = 3;

Si en una consulta de actualización suprimimos la cláusula WHERE todos los registros de la tabla señalada serán actualizados.

UPDATE Empleados SET SalarioNeto = SalarioNeto * 1.1

Oracle Tuning : Paso 4 - Reestructurar los SQL

2008-04-23T06:20:00.000-07:00

* Evitar el uso de transformaciones en la cláusula WHERE
* Evitar el uso de funciones en la clausula WHERE
* Evitar expresiones de distinto tipo de datos
* Evitar conversiones de datos implícitas
* Escribir consultas separadas para tareas específicas.
* Utilizar UNION ALL
* Utilizar EXISTS en lugar de IN para las subconsultas
* Controlar las formas de acceso y el orden de los joins con hints
* Escribir los predicados de la WHERE usando AND e =, no usar LIKE en lo posible

Oracle Tuning : Paso 3 - Revisar planes de Ejecución

2008-04-23T06:15:00.000-07:00

* El tipo de Join debe ser apropiado al número de filas a procesar
* La tabla guía es la que tiene mejor filtro
* Deben derivarse al siguiente paso la menor cantidad de filas
* Las vistas deben ser usadas eficientemente
* Examinar la cláusula where respecto a las filas procesadas en cada tabla
* Un Full Table Scan no implica ineficiencia
* Cada tabla debe ser accedida eficientemente
* No deben existir productos cartesianos que no hayan sido hechos a propósito

Oracle Tuning : Paso 2- Verificar y Controlar las Estadisticas

2008-04-23T06:12:00.000-07:00

* Para todas las tablas e indices se deben Recopilar estadísticas.
* Hacer pruebas y analizar variantes de forma tal de poder recalcular nuevamente las stadísticas cada vez que los datos existentes varíen en forma significativa para que éstas sean representativas del estado de la base de datos.

Oracle Tuning : Paso 1- Indentificar las Consultas con problemas

2008-04-23T06:09:00.000-07:00

* Identificar programas con problemas de performance.
* Analizar el código SQL de los programas, tanto en la base de datos como en la aplicación
* Hacerle pequeñas variantes al SQL y analizar los cambios en lso tiempos de respuesta:.
* Utilizar SQL_Trace y TKPROF para identificar las consultas conflictivas.
* Hacerle pequeñas variantes al SQL y analizar los cambios en lso tiempos de respuesta

Oracle : Tuning

2008-04-15T12:16:00.000-07:00

Para realizar un correcto Tuning hay que realizar los siguientes pasos :

* Identificar el SQL problemático de la base
* Verificar estadísticas del optimizador correspondiente
* Revisar planes de ejecución
* Reestructurar sentencias SQL
* Reestructurar índices

En breve se agregará el detalle de cada uno de estos pasos

ORACLE - Funciones Fecha : To_Date

2007-09-11T10:00:00.001-07:00

La funcion to_date convierte un string en fecha.

La sintaxis es:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 es la cadena que va a ser convertido a fecha.

format_mask is optional. este es el formato que va a ser usado para convertir de cadena a fecha.

nls_language is optional. Es el lenguaje usado para convertir de cadena a fechaa.

Los posibles paraemtros usados como format_mask .

Parameter Explanation
YEAR Year, spelled out
YYYY 4-digit year
YYY
YY
Y Last 3, 2, or 1 digit(s) of year.
IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
RRRR Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M. Meridian indicator
AD or A.D AD indicator
BC or B.C. BC indicator
TZD Daylight savings information. For example, 'PST'
TZH Time zone hour.
TZM Time zone minute.
TZR Time zone region.

Ejemplos:

to_date('2008/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2008.
to_date('070908', 'MMDDYY') would return a date value of July 9, 2008.
to_date('20080315', 'yyyymmdd') would return a date value of Mar 15, 2008.

More Oracle
Mas Oracle

ORACLE - DBA : Roles y Responsabilidades

2007-07-17T08:57:00.001-07:00

DBA Oracle: Roles y Responsabilidades

El DBA o adminitrador de la BD de una empresa es siempre considerado como la persona con más experiencia en el área de bases de datos. Por lo anterior, es conveniente tener muy claras las expectativas que se generan en torno a su trabajo y cuáles son los principales roles que debe asumir dentro del marco corporativo o de un proyecto.

Tareas básicas :

Garantizar la seguridad del sistema
El DBA debe siempre monitorear y administrar la seguridad del sistema. Esto involucra la incorporación y eliminación de usuarios, administración de espacios de disco (cuotas), auditorias y una revisión periódica para detectar probables problemas de seguridad.
Prevención de riesgos
Otra tarea del DBA es la de calendarizar mantenciones a las bases de datos (archivos lógicos) o cooperar en el mantenimiento de las máquinas al administrador del sistema. El DBA debe fortalecer sus esfuerzos en orden a eliminar problemas o situaciones potencialmente peligrosas.

Instalación de nuevos componentes del software

Una de las tareas principales del DBA consiste en la instalación periódica de nuevas actualizaciones de software de Oracle, tanto en lo referente a programas de aplicaciones como a herramientas administrativas. También es recomendable que el propio DBA y otros usuarios de Oracle prueben la instalación y nuevas configuraciones antes de migrarlas a los ambientes de producción.

Interacción con el administrador del sistema

En la mayoría de los casos los programas sólo pueden ser instalados o accedidos por el administrador del sistema. En este caso, el DBA debe trabajar siempre muy bien coordinado con él para garantizar que tanto la instalación y configuración de software como de hardware permita un adecuado funcionamiento del motor de base de datos y de las aplicaciones.

Monitorización

El DBA debe monitorear continuamente el rendimiento del sistema y estar preparado para efectuar ajustes de sintonización de éste. En ciertas oportunidades esto involucra cambiar sólo algunos parámetros y otras veces reconstruir índices o reestructurar tablas.

Respaldos

Debido a que la tarea más importante del DBA es proteger la integridad de los datos, se deberá desarrollar una estrategia efectiva de respaldos y recuperación de datos para mantener la estabilidad de toda la información guardada. Las frecuencias de estos respaldos deberán decidirse dependiendo de la cantidad de procesos que alteran los datos a través del tiempo.

Tareas adicionales

Otras tareas de importancia que corresponden con frecuencia realizar a un DBA son:

Apoyar en el diseño y optimización de modelos de datos.
Analizar datos y efectuar recomendaciones concernientes a mejorar el rendimiento y la eficiencia en el manejo de aquellos datos que se encuentran almacenados.
Asistir a los desarrolladores con sus conocimientos de SQL y de construcción de procedimientos almacenados y triggers, entre otros.
Documentar y mantener un registro periódico de las mantenciones, actualizaciones de hardware y software, cambios en las aplicaciones y, en general, todos aquellos eventos relacionados con cambios en el entorno de utilización de una base de datos.
Apoyar en la definición de estándares de diseño y nomenclatura de objetos.

More Oracle
Mas Oracle

ORACLE - Creación y Manejo de Tablas

2007-06-06T11:22:00.001-07:00

Creación de tablas

Como expusimos en nuestro artículo anterior, en Oracle cada estructura de información se denomina TABLA las cuales, junto a los índices y al diccionario de datos del sistema, componen la base de datos. Por lo tanto, la creación de las tablas en el proceso de programación en Oracle juegan un papel muy importante. En el momento de crear las tablas se definen características a dos niveles: Tabla y Columna, como se muestra a continuación:

A nivel de Tabla

Nombre: Nombre de la tabla puede ser de 1 a 30 caracteres.

Propietario: La tabla tiene como propietario al usuario que las crea En nuestro caso somos el usuario EIDOS. Otro usuario que desee usar nuestras tablas debe tener autorización para ello y hacer referencia a la tabla como eidos.clientes (propietario.tabla)

Cantidad de Columnas: Una tabla puede tener un máximo de 254 columnas.

A nivel de Columna

Nombre: Puede tener de 1 a 30 caracteres.

Tipo de dato y su ancho

CHAR Máximo de 255. Por defecto 1.

NUMBER Máximo de 105 dígitos. Por defecto 44.

INTEGER Numérico sin decimal. Por defecto 38.

DATE Hasta el 31 de diciembre de 4712.

LONG Tipo caracter con tamaño variable hasta 65535 bytes. Permite una sola columna LONG por tabla. No se puede usar en subconsultas, funciones o índices.

RAW Dato en binario puro (imágenes y sonido) con un ancho máximo de 255.

LONGRAW Igual que LONG, pero para almacenar datos en binario puro.

Restricciones: Su función es definir reglas de validación de la columna.

Para facilitar la continuidad del análisis, usaremos como ejemplo las tablas definidas en el artículo anterior: Clientes y VENTAS.

La definición de restricciones al crear las tablas permite establecer reglas de validación de datos, así como los controles necesarios para mantener la integridad referencial entre tablas a través de las columnas claves. Las restricciones que se pueden definir son:

Valor obligatorio: En Oracle existe el concepto de valor nulo (NULL), como un valor indefinido o ausencia de valor y que es diferente al numero 0 o al carácter espacio. Por lo tanto, para que una columna siempre tenga valor (sea obligatoria) se define como NOT NULL.

Rango de valores: Sirven para chequear que el valor sea mayor a un valor determinado o para que se encuentre entre dos valores.

Clave Primaria: Columnas que identifican de forma única al registro, es un valor único y no nulo (NOT NULL). Por ejemplo: el código del cliente es una clave primaria que identifica de forma única e irrepetible a cada cliente.

Clave Externa: Columna de la tabla que hace referencia a un valor que tiene que estar registrado en otra tabla. Por ejemplo: la columna código de la tabla VENTAS es una clave externa que hace referencia a un valor de la columna código (clave primaria) de la tabla Clientes.

En la versión 6 de Oracle (que dado lo reciente de la versión 7 aún se usa ampliamente) la única restricción que estaba activa era la de valor obligatorio (NOT NULL), siendo las otras restricciones sólo declarativas, o sea, que quedaban registradas en la definición de la tabla, pero no se podían activar. En la versión 6, para garantizar la unicidad de la clave primaria, era necesario crear índices con claves únicas, aspecto éste que retomaremos más adelante. En la versión 7 de Oracle estas restricciones están implementadas, garantizándose la verificación y correción de datos en cualquier momento sin tener que programar estos controles.

Destacadas estas cuestiones veamos, entonces cómo se procede para crear las tablas Clientes y Ventas.

Tabla Clientes

Objetivo: Ficha con datos para identificar al cliente. Consta del código del cliente (número secuencial), fecha de alta al sistema, nombre, teléfono, dirección y alguna anotación.

Requisitos: Se debe identificar a cada cliente con un código único (clave primaria), registrando su nombre, teléfono y fecha de registro (estos datos son obligatorios). La dirección y anotaciones son campos opcionales.

Creación de la tabla: Ver el fuente 1

Fuente 1

CREATE TABLE clientes

(

codigo integer NOT NULL

PRIMARY KEY,

fecha date NOT NULL,

nombre char(30) NOT NULL,

telefono char(20) NOT NULL,

direccion char(100),

anotacion LONG

);

Tabla Ventas

Objetivo: Registrar las ventas con al siguiente información: Código del cliente, fecha de la venta, artículo y valor de la venta.

Requisitos: El número del cliente es una clave externa que hace referencia a la columna codigo en la tabla Clientes. En este caso, todos los datos son obligatorios. Se controla que la columna valor sea mayor a cero.

Creación de la tabla: ver fuente 2

Fuente 2

CREATE TABLE ventas

(

codigo INTEGER NOT NULL

REFERENCES clientes(codigo),

fecha DATE NOT NULL,

articulo CHAR(20) NOT NULL,

valor NUMBER(10,2) NOT NULL

CHECK (valor>0)

);

Las restricciones de Claves Primaria y Clave Externa se definieron a nivel de columna, pero se pueden definir a nivel de tabla, al final de la misma, como se muestra en el fuente 3:

Fuente 3

CREATE TABLE clientes

(codigo INTEGER NOT NULL,

nombre CHAR(30) NOT NULL,

direccion CHAR(100),

anotacion LONG,

PRIMARY KEY (codigo));

CREATE TABLE ventas

(codigo INTEGER NOT NULL,

fecha DATE NOT NULL,

articulo CHAR(10),

valor NUMBER(6,2) NOT NULL

CHECK (valor>0),

FOREIGN KEY (codigo) REFERENCES clientes(codigo));

La definición de la clave a nivel de tabla es necesaria cuando la misma está formada por más de una columna.

Unicidad de la clave con índices

Para garantizar la unicidad de los valores de la clave primaria de la tabla Clientes (en la versión 6 donde esta restricción sólo es declarativa y no está activa), se debe crear un índice que garantice la unicidad de la clave principal. Un requisito importante para la unicidad de la clave principal es que las columnas de la clave se definen como NOT NULL.

A continuación mostraremos cómo crear el índice cliente_codigo para garantizar la unicidad de la clave primaria:

CREATE UNIQUE INDEX cliente_codigo

ON clientes(código);

Secuencias: codificación numérica

La codificación numérica del cliente se puede realizar con una secuencia que automáticamente genera los números enteros en orden ascendente, no siendo necesario recordar cuál fue el último número asignado ; esto evita la duplicidad de códigos.

La secuencia es un objeto que genera valores enteros únicos y se emplean para crear claves primarias numéricas, con el uso del siguiente mandato:

CREATE SEQUENCE codigo_cliente

INCREMENT BY 1

START WITH 1;

Para registrar un nuevo código con la secuencia definida anteriormente se usa la pseudo-columna codigo_cliente.NEXTVAL, la cual nos dará el siguiente valor que le corresponde a la secuencia, la forma en que esto se realiza se explicará más adelante, cuando analicemos el ingreso de datos.

Para conocer el valor actual de la secuencia, o sea, el último código asignado, se usa la pseudo-columna codigo_cliente.CURRVAL, desde la tabla DUAL del sistema, cuyo fin es poder consultar pseudo_columnas (como se muestra a continuación):

SELECT user,sysdate,codigo_cliente.currval

FROM DUAL;

donde:

1.- user es el nombre del usuario

2.- sysdate es la fecha del sistema

3.- codigo_cliente.currval es el último valor asignado a la secuencia.

Ingreso de datos

Una vez creadas las tablas, índices y secuencias, estamos en condiciones de ingresar datos en la tabla.

El ingreso, modificación y eliminación de registros se realiza fundamentalmente con el diseño de pantallas (formularios) desde el módulo SQLFORMS (que será tema de análisis específico en otro artículo). No obstante, en este artículo veremos el uso de los mandatos INSERT UPDATE y DELETE.

Para ingresar un nuevo registro debemos ensayar lo que se muestra en la tabla 1:

Nombre de la Tabla (acciones)	Columnas
INSERT INTO CLIENTES
VALUES(
codigo_cliente.NEXTVAL,	codigo= secuencia
'PINTURERIAS PROPIOS',	nombre
'45 67 89'	teléfono
TO_DATE('10/04/95','DD/MM/YY'),	fecha
'Uruguay 1234',	dirección
'LIBRERIA'	anotación
);

Tabla1: Inserción de nuevos registros

Como se podrá observar, en este ejemplo no se especificó la lista de columnas a insertar, lo que indica que se van a ingresar datos para todas las columnas. Por lo tanto, los valores para cada columna se tienen que ingresar en el orden en que están definidos en la tabla. Además, es de destacar que la palabra reservada VALUES indica la lista de valores a ingresar; que los datos tipo carácter van entre comillas; que la fecha se registra como una cadena de caracteres usando la función TO_DATE (encargada de transformar la cadena de caracteres '10/04/95' en fecha, a partir de un formato de fecha especificado -'DD/MM/YY'-).

También observamos que a la columna codigo se le asignó el siguiente valor de la secuencia codigo_cliente (codigo_cliente.NEXTVAL).

En caso de que sólo se asignaran valores a algunas columnas se debe dar la lista de columnas como se muestra en el fuente 4 correspondiente a la lista de columnas.

Fuente 4

INSERT INTO CLIENTES (codigo,nombre,teléfono,fecha)

VALUES

(codigo_cliente.NEXTVAL,'CASA AUGE DEPORTES',

'598768',TO_DATE('15/04/95','DD/MM/YY'));

Las columnas a las que se les ingresa información se listan después del nombre de la tabla, en el orden deseado. Las columnas no listadas tendrán valor NULL, por ello todas las columnas definidas como obligatorias (NOT NULL) deben estar en la lista.

Los siguientes ejemplos muestran posibles errores y sus correspondientes mensajes en el registro de datos:

1.- Falta dato del teléfono que es obligatorio:

INSERT INTO CLIENTES(codigo,nombre)

VALUES (codigo_cliente.NEXTVAL,

'EMPRESA D'

);

Mensaje de error:

ORA‑01400: mandatory (NOT NULL) column is missing or NULL during insert.

2.- Intento de registrar cliente con código ya existente:

INSERT INTO CLIENTES(codigo,nombre,teléfono)

VALUES (1,'EMPRESA TTT','341234');

Mensaje de error:

ORA‑00001: duplicate key in index

Obsérvese en el caso 2 que la secuencia codigo_cliente no fue usada al ingresar el valor del código, y sí en el caso 1, provocando error de duplicidad de código. Esto ocurre porque la creación de la secuencia no garantiza la unicidad del código, ya que podemos registrar un código de cliente sin su uso. Sin embargo, la unicidad esta garantizada por la definición del índice único visto anteriormente. Si siempre se usa la secuencia la unicidad por supuesto que está garantizada, pero la simple definición de la secuencia no es garantía de su uso.

Listados de registros

A continuación veremos cómo obtener listados para revisar la información registrada, para lo cual seleccionaremos (select) registros desde (from) una tabla. En realidad el mandato SELECT será tema de análisis más detallado en la próxima entrega, por lo que ahora sólo lo trataremos con el objetivo de visualizar los datos ingresados.

Para obtener un listado de todas las columnas y todos los registros de la tabla Clientes debemos seguir este procedimiento:

Nombre de la Tabla

SELECT * FROM clientes;

Donde * = Todas las columnas

El resultado es el que se muestra en la tabla 2:

Código	Fecha	Nombre	Teléfono	Dirección	Anotación
1	10‑Apr‑95	Pinturerías propios	45 67 89	Uruguay 1234	Ferretería
2	15‑Apr‑95	Casa Auge deportes	598768
3	20‑Apr‑95	Feria del libro	(0567)845677
4	30‑Apr‑95	Club de tenis	905877

Tabla 2: Datos de la tabla Clientes

Para listar sólo algunas columnas de la tabla clientes el procedimiento a seguir es:

SELECT codigo,nombre FROM clientes;

El resultado es el que se muestra en la tabla 3:

Código	Nombre
1	Pinturerías propios
2	Casa Auge deportes
3	Feria del libro
4	Club de tenis

Tabla 3: Datos de la tabla Clientes para Codigo y Nombre

Modificación de registros.

Para modificar valores de la tabla usaremos el mandato UPDATE, con el objetivo de modificar el teléfono y la dirección del cliente Feria del libro. Para ello, basta con definir:

UPDATE clientes

SET telefono='234567',

direccion='Andes 945'

WHERE nombre='Feria del libro';

donde:

Clientes es el nombre de la tabla

SET es para indicar el inicio de la lista de columnas y sus nuevos valores.

WHERE garantiza la selección de la fila del cliente.

Es importante destacar que si no se usa la cláusula WHERE, se modificará el valor de la columna en todas las filas de la tabla.

Eliminación de registros

La eliminación de registros se realiza con el mandato DELETE. El siguiente ejemplo eliminará los clientes con el código cero:

DELETE FROM clientes

WHERE codigo=0;

En este caso, si se omite la cláusula WHERE serán eliminados todos los registros de la tabla.

SQLPlus

Todas las tareas anteriormente estudiadas se realizan con el módulo SQLPlus de Oracle, que trabaja en forma interactiva. A continuación enunciaremos los pasos necesarios para usar SQLPLUS y poder crear tablas, índices o secuencias, así como insertar datos y obtener listados:

1.- Llamar al programa

SQLPLUS

2.- Identificación del usuario

Enter user‑name: EIDOS

Enter password:

Si la identificación es correcta se obtiene mensaje de:

Connected to: ORACLE

Si, por el contrario, la identificación es incorrecta se recibe el siguiente mensaje:

ERROR: ORA‑01017:

invalid username/password;

logon denied

3.- Indicador en pantalla de que SQLPlus está a la espera de la orden:

SQL>_

4.- Escribir los mandatos de creación de tablas, índices y secuencia en un archivo (TABLAS.SQL) con el uso del editor.

SQL>edit tablas

5.- Ejecutar los mandatos escritos en el archivo TABLAS.SQL

SQL>@tablas

6.- Para salir de SQLPlus

SQL>exit

Otras tareas

A continuación examinaremos una serie de mandatos, a nivel de definición de las tablas, gracias a los cuales se puede:

1.- Listar estructuras de las tablas (DESCRIBE)

2.- Modificar la estructura de las tablas (ALTER TABLE)

3.- Renombrar las tablas (RENAME)

4.- Eliminar una tabla (DROP TABLE)

5.- Eliminar un indice (DROP INDEX)

6.- Consultar las tablas del diccionario

7.- Listado de tablas, Índices y secuencias propiedad del usuario.

Detengámonos en los detalles más significativos de cada una de dichas tareas:

1.- Listar estructura de las tablas (DESCRIBE)

Para obtener la estructura (descripción de una tabla) el mandato que se debe emplear es:

SQL>DESCRIBE clientes;

con lo que el resultado será el que se ofrece en la tabla 4.

Name	Null?	Type
NUMERO	NOT NULL	NUMBER(38)
FECHA	NOT NULL	DATE
NOMBRE	NOT NULL	CHAR(30)
TELEFONO	NOT NULL	CHAR(20)
DIRECCION		CHAR(100)
ANOTACION		LONG

Tabla 4: Resultado del uso del mandato DESCRIBE

2.- Modificar la estructura de las tablas (ALTER TABLE)

La modificación de la estructura de las tablas con el uso de ALTER permite:

- Añadir nuevas columnas.

- Añadir restricciones a una columna, en este caso la tabla no debe contener datos.

- Modificar el ancho de la columna.

- Modificar el tipo de datos de la columna sólo si la columna no contiene datos o está vacía.

- Modificar al tipo LONG sólo una columna sin restricciones.

El siguiente ejemplo muestra cómo añadir, en la tabla Ventas, las columnas Factura (para registrar el número de factura) y Cobro (tipo carácter con 2 posibles valores, N=NO cobrada, NULL=cobrada) y modificar la columna valor para ampliar su ancho.

ALTER TABLE ventas

ADD (

factura integer,

cobro char

)

MODIFY (

valor number(10,2)

);

3.- Renombrar las tablas (RENAME)

Para cambiar el nombre de la tabla Clientes a EMPRESAS se usa el siguiente mandato:

SQL>RENAME clientes TO empresas;

4.- Eliminar una tabla (DROP TABLE)

Le eliminación de la tabla es como sigue:

SQL>DROP TABLE clientes;

En este caso se eliminan, también, todos los índices de la tabla.

5.- Eliminar un índice (DROP INDEX)

SQL>DROP INDEX cliente_codigo;

6.- Consultar las tablas del diccionario.

Toda la información de las tablas está registrada en el diccionario del sistema (Data Dictionary), que son tablas especiales que se crean en la instalación de ORACLE (que son administradas por el sistema).

Para consultar la lista de tablas que componen el diccionario se escribe:

SQL>HELP DATA DICT

Gracias a lo cual se muestra una lista con la información de la tabla 5:

	Nombre de la tabla	Descripción
	ACCESSIBLE_COLUMNS	columns of all tables, views, and clusters
	ACCESSIBLE_TABLES	tables and views accessible to the user
	AUDIT_ACTIONS	maps action type numbers to action type names
	ALL_INDEXES	descriptions of indexes on accessible
	ALL_SEQUENCES	descriptions of the user's own sequences
	ALL_TABLES	description of tables accessible to the user
	.....
	USER_TABLES	descriptions of the user's own tables
	USER_TAB_COLUMNS	columns of the user's tables, views, and clusters
	USER_TAB_GRANTS	grants on objects where the user is the owner, grantor, or grantee

Tabla 5: Consulta de las tablas que componen el diccionario

También podemos ver la estructura de una tabla del diccionario como se muestra a continuación:

SQL>DESCRIBE ALL_TABLES;

SQL>DESCRIBE all_indexes;

SQL>DESCRIBE all_sequences;

7.- Listar las tablas, índices y secuencias definidas por un usuario

Para las tablas:

SQL>SELECT TABLE_NAME "TABLA"

FROM ALL_TABLES

WHERE OWNER='EIDOS';

Resultado: Clientes y Ventas

Para los índices:

SQL>SELECT table_name,index_name

FROM all_indexes

WHERE owner='EIDOS';

Resultado: Clientes (con índice Cliente_Nombre y Cliente_Numero) y Ventas (con índice Venta_Numero)

Para las secuencias:

SQL>SELECT sequence_name

FROM all_sequences

WHERE sequence_owner='EIDOS';

Resultado: el nombre de la secuencia usada (SEQUENCE_NAME) Codigo_Cliente

Como hemos visto, la creación de las tablas constituye el fundamento del diseño de cualquier sistema a desarrollar en Oracle. Una vez definida las tablas el paso lógico siguiente es conocer las técnicas para realizar un adecuado uso de la información contenida en el sistema. Por ello, el próximo artículo lo dedicaremos al lenguaje de Consulta SQL.

More Oracle
Mas Oracle

to_date('2008/07/09', 'yyyy/mm/dd')	would return a date value of July 9, 2008.
to_date('070908', 'MMDDYY')	would return a date value of July 9, 2008.
to_date('20080315', 'yyyymmdd')	would return a date value of Mar 15, 2008.