Instrucción UPDATE
La sentencia UPDATE se encarga de modificar registros ya existentes en una tabla. Es equivalente a la sentencia DELETE, pero en vez de borrar, actualiza las columnas indicadas que cumplan la condición impuesta.
Sintaxis:
UPDATE tabla
SET campo = valor, campo = valor, . . .
{WHERE condición};
El valor puede ser tanto un valor discreto (1, 'pepe', '1-jan-2000', etc), un valor dependiente de otra una columna (IMPORTE*10) o un subselect que retorne un conjunto de resultados de 1x1 (1 fila y 1 una columna). Si se utiliza un subselect se puede hacer join entre este subselect y la tabla del UPDATE. Si se omite la cláusula WHERE, se actualizarán todas las filas de la tabla.
Ejemplos:
UPDATE FACTURA SET IMPORTE = 1000 WHERE C_PAIS = 1 AND C_CLIENTE = 5;
UPDATE FACTURA SET IMPORTE = IMPORTE * 0.5 WHERE C_PAIS = 1 AND C_CLIENTE = 5;
UPDATE FACTURA F1
SET IMPORTE = ( SELECT AVG(IMPORTE) * 1.10 FROM FACTURA F2 WHERE F1.C_PAIS = F2.C_PAIS AND
F1.C_CLIENTE = F2.C_CLIENTE );
UPDATE FACTURA F1
SET IMPORTE = ( SELECT AVG(F2.IMPORTE) + F1.IMPORTE FROM FACTURA F2 WHERE F1.C_PAIS = F2.C_PAIS AND
F1.C_CLIENTE = F2.C_CLIENTE );
Lenguaje de definición de datos: DDL
El DDL (Data Definition Language) es el conjunto de sentencias que está orientadas a la creación, modificación y configuración de objetos en base de datos. El DDL es el subconjunto más extenso dentro de SQL así que sólo vamos a hacer una referencia rápida a algunas sentencias. Se puede encontrar una descripción detallada del todo el DDL dentro del Oracle8 SQL Reference.
CREATE TABLE
Crea una tabla en base de datos. La sintaxis básica es:
CREATE TABLE nombre_tabla( COLUMNA TIPO [NOT NULL], COLUMNA TIPO [NOT NULL], . . .
{CONSTRAINT nombre_clave_primaria PRIMARY KEY (columnas_clave)} {CONSTRAINT nombre_clave_foránea FOREIGN KEY(columnas_clave) REFERENCES tabla_detalle( columnas_clave )
{ON DELETE CASCADE} } ) {TABLESPACE tablespace_de_creación} {STORAGE( INITIAL XX{K|M} NEXT XX{K|M} )}
La creación de la tabla FACTURA, definida en el capítulo “Concepto de índice”, sería la siguiente:
CREATE TABLE FACTURA( REFERENCIA VARCHAR2(10) NOT NULL, DESCRIPCION VARCHAR2(50), C_PAIS NUMBER(3), C_CLIENTE NUMBER(5), IMPORTE NUMBER(12),
CONSTRAINT PK_FACTURA PRIMARY KEY( REFERENCIA ) CONSTRAINT FK_CLIENTE(C_PAIS,C_CLIENTE) REFERENCES CLIENTE(C_PAIS, C_CLIENTE) ON DELETE CASCADE
TABLESPACE tab_facturas STORAGE( INITIAL 1M NEXT 500K );
Los campos que van a formar parte de la clave se tienen que definir como NOT NULL ya que no tiene sentido que éstas columnas carezcan de valor. Además se crea la clave primaria y la clave foránea que hace referencia a CLIENTE (como ya dijimos en el apartado de claves foráneas). Con la cláusula ON DELETE CASCADE hacemos que si se borra un cliente, se borren automáticamente todas sus facturas. Si no la incluyésemos, al borrar el cliente, nos daría el siguiente error:
ORA-02292: integrity constraint (FK_CLIENTE) violated - child record found
Hay que tener en cuenta que aunque la clave foránea se crea sobre la tabla FACTURA, también actúa cuando se hacen operaciones sobre CLIENTE.
Al intentar insertar una factura a un cliente inexistente nos dará el siguiente error:
ORA-02291: integrity constraint (FK_CLIENTE) violated - parent key not found
Se puede encontrar una descripción detallada de todos los errores en el Oracle8 Error Messages.
Sobre la cláusula STORAGE hablaremos al final de este capítulo.
CREATE INDEX
Crea un índice sobre una tabla de base de datos. La sintaxis básica es:
CREATE {UNIQUE} INDEX nombre_índice ON tabla( columnas_indexadas ) {TABLESPACE tab_indices} {STORAGE( INITIAL XX{K|M} NEXT XX{K|M} )}
La cláusula UNIQUE actúa como si los campos indexados fuesen clave primaria, es decir, no permite que el conjunto de campos indexados se repita en la tabla. Ya dijimos que un índice es como una tabla auxiliar que sólo contiene ciertas columnas de búsqueda. Por eso también es posible (y recomendable) indicar tanto el tablespace como las cláusula STORAGE para las características de almacenamiento de disco. Si no se incluyera, se utilizará el STORAGE indicado en la creación del tablespace sobre el que se crea el índice.
Además, Oracle recomienda que los índices residan en un tablespace y datafile separado al de las tablas. Esto es debido a la siguiente razón: Dos tablespaces distintos pueden están almacenados físicamente por, al menos, un datafile cada uno. Si nuestro servidor de base de datos tiene más de un disco duro (algo muy normal), es posible crear un tablespace con sus datafiles en un disco y otro tablespace con los datafiles en otro disco. Esto permite que se puedan hacer lecturas de disco simultáneamente sobre dos disco físicos, ya que cada disco tiene su propio bus de datos. Al crear los índices en un disco físico y los datos en otro, se facilita que se puedan hacer lecturas simultaneas. Este proceso (de poner los índices y datos en discos separados), se denomina balanceado.
Oracle crea automáticamente un índice cuando se define la clave primaria. Esto es debido a que la condición más habitual en una consulta a cualquier tabla es a través de los campos de su clave primaria. De esta forma se aceleran la gran mayoría de las consultas (recordar que el índice de una tabla actúa del mismo modo que el de un libro). Pero pueden darse casos en los que se hagan gran cantidad de consultas por campos distintos a los de la clave primaria. En este caso es necesario crear un índice por los campos por lo que se accede. Por ejemplo, puede ser que en nuestra tabla FACTURA sea muy común recuperar aquellas facturas de un cierto cliente. En este caso la consulta SELECT ha realizar sería la siguiente:
SELECT * FROM FACTURA WHERE C_PAIS = 1 AND
C_CLIENTE = ‘A111’;
En este caso se está accediendo la tabla FACTURA por campos distintos a la clave primaria (que es Referencia). Si este tipo de consultas son muy habituales es necesario crear un índice por estos campos:
CREATE INDEX ind_factura_cliente ON FACTURA( C_PAIS, C_CLIENTE ) TABLESPACE tab_factura_ind STORAGE( INITIAL 500K NEXT 500K );
No podemos poner la cláusula UNIQUE porque si no, no podríamos insertar más de una factura por cliente.
CREATE VIEW
Una vista (view) es una consulta SELECT almacenada en base de datos con un cierto nombre. Si tenemos la siguiente consulta:
SELECT C.D_CLIENTE, SUM( F.IMPORTE )
|| FROM || FACTURA F, CLIENTE C ||
|| WHERE || F.C_PAIS || = C.C_PAIS AND ||
|| || F.C_CLIENTE = C.C_CLIENTE ||
GROUP BY F.C_PAIS, F.C_CLIENTE, C.D_CLIENTE;
Si esta consulta se repite es necesaria muchas veces, entonces podemos guardar esta definición en base de datos con un nombre (crear una vista), y después hacer la consulta sobre la vista.
CREATE VIEW TOTAL_FACTURA_CLIENTE AS SELECT C.D_CLIENTE, SUM( F.IMPORTE ) FROM FACTURA F, CLIENTE C WHERE F.C_PAIS = C.C_PAIS AND
F.C_CLIENTE = C.C_CLIENTE GROUP BY F.C_PAIS, F.C_CLIENTE, C.D_CLIENTE;
Y después hacer la consulta sobre la vista:
SELECT * FROM TOTAL_FACTURA_CLIENTE;
La sintaxis de creación de vista es:
CREATE {OR REPLACE} {FORCE} VIEW nombre_vista AS Subconsulta;
La cláusula OR REPLACE permite sobrescribir una definición existente con otra nueva definición. La cláusula FORCE permite crear una vista aunque las tablas de la subconsulta no existan.
Ejemplos:
CREATE OR REPLACE FORCE VIEW VISTA_INCORRECTA AS SELECT * FROM FACTURA_ADICIONAL;
CREATE OR REPLACE VIEW FACTURA_CLIENTE_A111 AS SELECT * FROM FACTURA WHERE C_PAIS = 1 AND C_CLIENTE = ‘A111’;
CREATE SYNONYM
Un sinónimo (synonym) es una redefinición de nombre de un objeto en base de datos. Así al objeto FACTURA podemos crearle un sinónimo y entonces se podrá acceder a él tanto con el nombre de FACTURA como con el nombre del sinónimo.
Sintaxis:
CREATE {PUBLIC} SYNONYM nombre_sinónimo FOR {usuario.}objeto;
La cláusula PUBLIC permite que todos los usuarios de base de datos puedan acceder al sinónimo creado. Para más información sobre los usuario de base de datos ir a la sección Administración básica y seguridad en Oracle.
Ejemplos:
CREATE SYNONYM FACTURAS FOR FACTURA;
CREATE SYNONYM FACTURA_SCOTT FOR SCOTT.FACTURA;
CREATE PUBLIC SYNONYM BILL
FOR FACTURA;
CREATE SEQUENCE
Una secuencia (sequence) es un objeto de base de datos que genera números secuenciales. Se suele utilizar para asignar valores a campos autonuméricos.
En realidad una secuencia no es más que una tabla con una columna numérica en la que se almacena un valor. Cada vez que se consulta la secuencia se incrementa el número para la siguiente consulta.
Sintaxis:
CREATE SEQUENCE nombre_secuencia {START WITH entero} {INCREMENT BY entero} {MAXVALUE entero | NOMAXVALUE} {MINVALUE entero | NOMINVALUE } {CYCLE | NOCYCLE};
|| ƒ || La cláusula START WITH define el valor desde el que empezará la generación de números. Si ||
|| || no se incluye, se empezará a partir de MINVALUE. ||
|| ƒ || La cláusula INCREMENT BY indica la diferencia que habrá entre un número y el siguiente. ||
|| || Puede ser cualquier número entero (positivo o negativo) distinto de 0. ||
|| ƒ || La cláusula MAXVALUE indica el valor máximo que podrá alcanzar la secuencia. Se podrá ||
|| || incluir la cláusula NOMAXVALUE para no definir máximo de 1027. ||
|| ƒ || La cláusula MINVALUE indica el valor mínimo de la secuencia. Se podrá incluir la cláusula ||
|| || NOMINVALUE para definir un mínimo de –1026. ||
|| ƒ || La cláusula CYCLE permite que se empiece a contar en MINVALUE cuando se llegue || a ||
|| || MAXVALUE. Por defecto las secuencias se crean NOCYCLE. ||
Ejemplos:
CREATE SEQUENCE REF_FACTURA START WITH 1 INCREMENT BY 1 MAXVALUE 999999 MINVALUE 1;
CREATE SEQUENCE COD_CLIENTE INCREMENT BY 10;
CREATE SEQUENCE COD_PAIS INCREMENT BY 10 CYCLE;
