Oracle 1 resumen.sql

Manual de SQL para Oracle 9i
Manual de referencia


*************************************************************************************
                                 TABLAS
*************************************************************************************

- Cada usuario de una base de datos posee un esquema. El esquema tiene el mismo
nombre que el usuario

- El comando DESCRIBE, permite obtener la estructura de una tabla. Ejemplo:
DESCRIBE proveedores;

- INSERT INTO proveedores(nombre, CIF)
VALUES (‘Araja SA’,’14244223Y’);

- SELECT * FROM USER_TABLES 
obtiene una vista de las tablas del usuario actual.

- El borrado de una tabla es irreversible, y no hay ninguna petición de confirmación,
por lo que conviene ser muy cuidadoso con esta operación.

Sólo es posible realizar DROP TABLE si se es el propietario de la tabla o se posee el
privilegio DROP ANY TABLE

*** TIPOS DE DATOS

** Textos

- VARCHAR2. Para textos de longitud variable de hasta 4000 caracteres
- CHAR. Para textos de longitud fija de hasta 2000 caracteres.
- NCHAR. Para el almacenamiento de caracteres nacionales de texto fijo
- NVARCHAR2. Para el almacenamiento de caracteres nacionales de longitud
variable.

En todos estos tipos se indican los tamaños entre paréntesis tras el nombre del tipo. Ese
tamaño en el caso de los tipos VARCHAR2 es obligatorio, en el caso de los tipos CHAR son
opcionales (de no ponerlos se toma el uno).
En el caso de los VARCHAR, Oracle no malgasta espacio por poner más espacio del deseado.

** Numeros

Los números decimales se indican con NUMBER(p,s) donde p es la precisión máxima y 
s es la escala (número de decimales a la derecha de la coma).
Los decimales en Oracle se presenta con el punto y no con la coma.

Para números enteros se indica NUMBER(p) donde p es el número de dígitos. Eso es
equivalente a NUMBER(p,0).

Para números de coma flotante (equivalentes a los flota o double de muchos
lenguajes de programación) simplemente se indica el texto NUMBER sin precisión ni
escala.

* Ejemplos:

Formato 	Número escrito por el usuario		       Se almacena como…
NUMBER 			345255.345 				345255.345
NUMBER(9) 		345255.345 				345255
NUMBER(9,2) 		345255.345 				345255.36
NUMBER(7) 		345255.345 				Da error de precisión
NUMBER(7,-2) 		345255.345 				345300
NUMBER(7,2) 		345255.345 				Da error de precisión


La escala sólo indica los decimales que se respetarán del número, pero si es
negativa indica ceros a la izquierda del decimal.

** tipo LONG
Se trata de un tipo de datos que actualmente se mantiene por compatibilidad. Hasta 2GB.
Usar CLOB

** DATE
--> SYSDATE

** TIMESTAMP

representa un instante concreto en el tiempo. 
Un ejemplo de TIMESTAMP sería ‘2/2/2004 18:34:23,34521’.

** intervalos

-- INTERVAL YEAR TO MONTH

Este tipo de datos almacena años y meses.
Tras la palabra YEAR se puede indicar la precisión de los años (cifras del año), 
por defecto es de dos.

CREATE TABLE tiempos (meses INTERVAL YEAR(3) TO MONTH);
INSERT INTO tiempos VALUES(’3-2’);

En el ejemplo se inserta un registro que representa 3 años y dos meses.

-- INTERVAL DAY TO SECOND

Representa intervalos de tiempo que expresan días, horas, minutos y segundos.
Se puede indicar la precisión tras el texto DAY y el número de decimales de los segundos tras el
texto SECOND

CREATE TABLE tiempos (dias INTERVAL DAY(3) TO SECOND(0));
INSERT INTO tiempos VALUES(’2 7:12:23’);

En el ejemplo anterior DAY(3) Significa que habrá una precisión de 3

** RAW
Sirve para almacenar valores binarios de hasta 2000 bytes (se puede especificar el tamaño
máximo entre paréntesis). El valor LONG RAW almacena hasta 2GB.

** LOB
Son varios tipos de datos que permiten almacenar valores muy grandes. Más adelante se
comentan en su totalidad. Incluye a BLOB, CLOB, NCLOB y BFILE.


** ROWID
Valor hexadecimal que representa la dirección única de una fila en su tabla.


*** Modificar Tablas

RENAME nombreViejo TO nombreNuevo

TRUNCATE TABLE seguida del nombre de una tabla, hace que se elimine el
contenido de la tabla, pero no la tabla en sí. No se puede hacer Rollback

ALTER TABLE nombreTabla ADD(nombreColumna TipoDatos
[Propiedades]
[,columnaSiguiente tipoDatos [propiedades]...)

ALTER TABLE nombreTabla DROP(columna);

ALTER TABLE nombreTabla MODIFY(columna tipo [propiedades]
[columnaSiguiente tipo [propiedades] ...]

COMMENT ON { TABLE NombreTabla | COLUMN tabla.nombreColumna }
IS ‘Comentario’

-- Para mostrar los comentarios puestos se usan las siguientes vistas del diccionario de datos
mediante la instrucción SELECT:

 USER_TAB_COMMENTS. Comentarios de las tablas del usuario actual.
 USER_COL_COMMENTS. Comentarios de las columnas del usuario actual.
 ALL_TAB_COMMENTS. Comentarios de las tablas de todos los usuarios (sólo administradores)
 ALL_COL_COMMENTS. Comentarios de las columnas de todos los usuarios (sólo administradores).

-- Valor por defecto:

CREATE TABLE articulo (cod NUMBER(7), nombre VARCHAR2(25),
precio NUMBER(11,2) DEFAULT 3.5);

-- Restricciones:

{CREATE TABLE nombreTabla |
ALTER TABLE nombreTabla {ADD | MODIFY}}
(campo tipo [propiedades] [,...]
CONSTRAINT nombreRestricción tipoRestricción (columnas)
[,CONSTRAINT nombrerestricción tipoRestricción (columnas) ...)

Los nombres de restricción no se pueden repetir para el mismo esquema, por lo que es
buena idea incluir de algún modo el nombre de la tabla, los campos involucrados y el tipo
de restricción en el nombre de la misma. Por ejemplo pieza_id_pk podría indicar que el
campo id de la tabla pieza tiene una clave principal (PRIMARY KEY).

* Prohibir Nulos:

CREATE TABLE cliente(dni VARCHAR2(9) NOT NULL);

La otra forma (que admite nombre) es:
CREATE TABLE cliente(dni VARCHAR2(9)
CONSTRAINT dni_sinnulos NOT NULL(dni));

* Valores únicos:

CREATE TABLE cliente(dni VARCHAR2(9) UNIQUE);

Otra forma es:
CREATE TABLE cliente(dni VARCHAR2(9) CONSTRAINT dni_u UNIQUE);

Si la repetición de valores se refiere a varios campos, la forma sería:
CREATE TABLE alquiler(dni VARCHAR2(9),
cod_pelicula NUMBER(5),
CONSTRAINT alquiler_uk UNIQUE(dni,cod_pelicula) ;

* Clave Primaria:
Debe ser no nula y única.

CREATE TABLE cliente(
dni VARCHAR2(9) PRIMARY KEY,
nombre VARCHAR(50)) ;

- poniendo nombre restricción:
CREATE TABLE cliente(
dni VARCHAR2(9) CONSTRAINT cliente_pk PRIMARY KEY,
nombre VARCHAR(50)) ;

- para más de un campo:
CREATE TABLE alquiler(dni VARCHAR2(9),
cod_pelicula NUMBER(5),
CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula) ;

* clave secundaria o foránea

Una clave secundaria o foránea, es uno o más campos de una tabla que están relacionados
con la clave principal de los campos de otra tabla. La forma de indicar una clave foránea
es:
CREATE TABLE alquiler(dni VARCHAR2(9),
cod_pelicula NUMBER(5),
CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula),
CONSTRAINT dni_fk FOREIGN KEY (dni)
	REFERENCES clientes(dni),
CONSTRAINT pelicula_fk FOREIGN KEY (cod_pelicula)
	REFERENCES peliculas(cod)
);

También hubiera bastado con indicar sólo la tabla a la que hacemos referencia, 
si no se indican los campos relacionados de esa tabla, se toma su clave principal (que es lo normal).
Esto forma una relación entre dichas tablas, que además obliga al cumplimiento de la
"integridad referencial". Esta integridad obliga a que cualquier dni incluido en la tabla
alquiler tenga que estar obligatoriamente en la tabla de clientes.

Otra forma de crear claves foráneas (sólo válida para claves de un solo campo) sin usar el 
texto FOREIGN KEY
CREATE TABLE alquiler(
 dni VARCHAR2(9) CONSTRAINT dni_fk
   REFERENCES clientes(dni),
 cod_pelicula NUMBER(5) CONSTRAINT pelicula_fk
   REFERENCES peliculas(cod)
 CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicu ));

La integridad referencial trae problemas:

1) si borramos un registro en la tabla principal.
Por ello Oracle nos ofrece dos soluciones a añadir tras la cláusula REFERENCES:
 1.A) ON DELETE SET NULL. Coloca nulos todas las claves secundarias relacionadas
con la borrada.
 1.B) ON DELETE CASCADE. Borra todos los registros cuya clave secundaria es igual
que la clave del registro borrado.

2) Si se desea cambiar el valor de la clave principal en un registro
relacionado con claves secundarias. 
En muchas bases de datos se implementan soluciones
consistentes en añadir ON UPDATE CASCADE o ON UPDATE SET NULL. 
Oracle no implementa directamente estas soluciones. Por lo que hay que hacerlo de otra forma. Las
soluciones son:
 2.A) Implementar un TRIGGER para que cuando se actualice el registro se actualicen las
claves secundarias (el mecanismo de funcionamiento es parecido al que se muestra
en el siguiente párrafo).
 2.B) Añadir un registro igual que el que se quiere cambiar en la tabla principal, pero con
el nuevo valor de la clave. Mediante una instrucción UPDATE actualizar a ese valor
de clave todos los registros de la tabla secundaria cuyo valor coincida con la antigua
clave. Finalmente borrar el registro en la tabla principal con el valor antiguo de la
clave.

La sintaxis completa para añadir claves foráneas es:
CREATE TABLE tabla(lista_de_campos
  CONSTRAINT nombreRestriccion FOREIGN KEY (listaCampos)
    REFERENCES tabla(clavePrincipalRelacionada)
      [ON UPDATE {SET NULL | CASCADE}]
);

Si es de un solo campo existe esta alternativa:
CREATE TABLE tabla(lista_de_campos tipos propiedades,
  nombreCampoClaveSecundaria
    CONSTRAINT nombreRestriccion
      REFERENCES tabla(clavePrincipalRelacionada)
        [ON UPDATE {SET NULL | CASCADE}]
);

* Restricciones de validación:

Son restricciones que dictan una condición que deben cumplir los contenidos de una
columna.

La expresión de la condición es cualquier expresión que devuelva verdadero o
falso, pero si cumple estas premisas:
>No puede hacer referencia a números de fila
>No puede hacer referencia a objetos de SYSTEM o SYS
>No se permiten usar las funciones SYSDATE, UID, USER y USERENV
>No se permiten referencias a columnas de otras tablas (si a las de la misma tabla)

Una misma columna puede tener múltiples CHECKS en su definición:
CREATE TABLE ingresos(cod NUMBER(5) PRIMARY KEY,
  concepto VARCHAR2(40) NOT NULL,
  importe NUMBER(11,2) CONSTRAINT importe_min
    CHECK (importe>0)
  CONSTRAINT importe_max
    CHECK (importe<8000)
);


Para poder hacer referencia a otras columnas hay que construir la restricción de forma
independiente a la columna:
CREATE TABLE ingresos(cod NUMBER(5) PRIMARY KEY,
concepto VARCHAR2(40) NOT NULL,
importe_max NUMBER(11,2),
importe NUMBER(11,2),
CONSTRAINT importe_maximo
CHECK (importe<importe_max)
);

* Añadir restricciones

ALTER TABLE tabla
ADD [CONSTRAINT nombre] tipoDeRestricción(columnas);

tipoRestricción es el texto CHECK, PRIMARY KEY o FOREIGN KEY

* Borrar restricciones:

ALTER TABLE tabla
DROP PRIMARY KEY | UNIQUE(campos) |
CONSTRAINT nombreRestricción [CASCADE]


* desactivar/activar restricciones
A veces conviene temporalmente desactivar una restricción para saltarse las reglas que
impone. La sintaxis es:
ALTER TABLE tabla DISABLE CONSTRAINT nombre [CASCADE]

ALTER TABLE tabla ENABLE CONSTRAINT nombre [CASCADE]

* cambiar de nombre a las restricciones
ALTER TABLE table RENAME CONSTRAINT
nombreViejo TO nombreNuevo;

* mostrar restricciones:

Tabla:
USER_CONSTRAINTS ó
ALL_CONSTRAINTS (sólo administradores)

Columnas:
OWNER
CONSTRAINT_NAME
CONSTRAINT_TYPE ( C.(CHECK o NOT NULL), P.(PRIMARY KEY), R. (FOREIGN KEY), U. (UNIQUE))
TABLE_NAME

Tabla: USER_CONS_COLUMNS
muestra información sobre las columnas que participan en una restricción y con su orden de aparición.

Ejemplo:

OWNER   CONSTRAINT_NAME      TABLE_NAME      COLUMN_NAME      POSITION
JORGE      EXIS_PK           EXISTENCIAS       TIPO              1
JORGE      EXIS_PK           EXISTENCIAS       MODELO            2
JORGE      EXIS_PK           EXISTENCIAS       N_ALMACEN         3

Para saber de qué tipo son esas restricciones, habría que acudir a la vista
USER_CONSTRAINTS.


*************************************************************************************
                                 SELECT
*************************************************************************************

Ver pagina 27 a 50

*************************************************************************************
                     Comandos internos en SQL e iSQL*Plus
*************************************************************************************