lopezatienza.com

1.1.  Creación de una base de datos.

 

La creación de una base de datos se puede efectuar de manera interactiva usando la opción Create database del menú IBConsole.

La sintaxis usada en Interbase es la siguiente:

CREATE {DATABASE | SCHEMA} 'filespec'

[USER 'username' [PASSWORD 'password']]

[PAGE_SIZE [=] int]

[LENGTH [=] int [PAGE[S]]]

[DEFAULT CHARACTER SET charset]

[<secondary_file>];

<secondary_file> = FILE 'filespec' [<fileinfo>] [<secondary_file>]

<fileinfo> = LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int

[<fileinfo>]

 

La creación de una base de datos permite especificar las siguientes parámetros opcionales:

• Nombre de usuario y password.
• Cambiar el tamaño de las paginas.
• Especificar el conjunto de caracteres que usara la B.D. para realizar p. ej. ordenaciones.
• Distribuir la B.D. en más de un fichero.

De todos los parámetros que tenemos, solo uno es obligatorio, que es el fichero donde se almacena la B.D. con el nombre del dispositivo, camino de acceso y nombre del mismo.

Por ejemplo con esta orden creamos una base de datos employee.gdb en el directorio actual:

CREATE DATABASE 'employee.gdb';

Podemos especificar el tamaño en páginas de un fichero inicial:

  CREATE DATABASE 'employee.gdb' LENGTH 10000;

También podemos dividir la base de datos en varios ficheros secundarios, especificando el tamaño de cada uno de ellos mediante la opción Length o incluso indicar la página a partir de la cual el fichero va a almacenar datos de la B.D.

Hemos de indicar que Interbase reasigna espacio en el fichero cuando este se llena, con lo cual la opción LENGTH no es necesaria cuando trabajamos con un solo fichero.

CREATE DATABASE 'employee.gdb'

FILE 'employee2.gdb' STARTING AT PAGE 10001 LENGTH 10000 PAGES

FILE 'employee3.gdb' LENGTH 10000 PAGES

FILE 'employee4.gdb';

Usamos la siguiente instrucción para generar un usuario que será el dueño de la base de datos. (solo los 8 primeros caracteres de la password son tenidos en cuenta.

CREATE DATABASE 'employee.gdb' USER 'SALES' PASSWORD 'mycode';

 

Podemos modificar el tamaño de la página con los valores 1024, 2048, 4096 o 8192

 

CREATE DATABASE 'employee.gdb' PAGE_SIZE 2048;

Esto suele ser necesario cuando el tamaño de una fila de un registro en una tabla va a superar el tamaño de la página, lo que supone que en una operación de recuperación de datos sobre esa tabla debemos recuperar 2 páginas o más. También se usa cuando queremos que los índices funciones más eficientemente.

El conjunto de caracteres por defecto para los tipos de datos CHAR VARCHAR y líneas de texto en un campo BLOB   y la forma de ordenar ascendente o descendentemente las columnas se determina mediante la cláusula DEFAULT CHAR SET  como sigue el ejemplo:

CREATE DATABASE 'employee.gdb'

DEFAULT CHARACTER SET 'ISO8859_1';

Que trabaja con el conjunto de caracteres típico en Europa.

1.1.1.      Alterar la definición de una base de datos

La sintaxis que se usa para este fin se muestra a continuación.

ALTER {DATABASE | SCHEMA}

ADD <add_clause>;

<add_clause> = FILE 'filespec' <fileinfo> [<add_clause>]

<fileinfo> = {LENGTH [=] int [PAGE[S]] | STARTING [AT [PAGE]] int }

[<fileinfo>]

Solo lo pueden realizar los usuarios autorizados y esta alteración de la base de datos solo atiende a la inclusión de nuevos ficheros de almacenamiento.

1.1.2.      Eliminar una B.D.

Las bases de datos también se pueden eliminar, y todos los elementos asociados, si tenemos privilegios para hacerlo, por ejemplo con la B.D. actual:

DROP DATABASE;

1.2.  Creación de Shadows

Permite crear copias de seguridad para recuperarnos antes posibles fallos hardware, tiene una serie de limitaciones, como que no podemos situar el fichero de shadow en otro disco que no sea el local. También tiene ventajas, como que el proceso es transparente al usuario de la base de datos, que no tiene que tener un control exclusivo sobre la B.D., es decir, puede estar realizándose otras consultas u operaciones.

CREATE SHADOW set_num [AUTO | MANUAL] [CONDITIONAL]

'filespec' [LENGTH [=] int [PAGE[S]]] [<secondary_file>];

   <secondary_file> = FILE 'filespec' [<fileinfo>] [<secondary_file>]

   <fileinfo> = {LENGTH[=]int [PAGE[S]] | STARTING [AT [PAGE]] int }

   [<fileinfo>]

Y borrarlo

DROP SHADOW set_num;

Para comprobar los ficheros shadows creados por Interbase:

SHOW DATABASE;

Para crear un shadow multifichero por ejemplo:

CREATE SHADOW 1 'D:/shadows/employee.shd' LENGTH 10000

FILE 'D:/shadows/employee2.shd' STARTING AT 10000

FILE 'D:/shadows/employee3.shd' STARTING AT 30000;

1.3.  Tipos de datos en Interbase

Tipos de datos soportados actualmente por Interbase:

 

NOMBRE	TAMAÑO	RANGO/PRECISION	DESCRIPCION
BLOB	variable	limitado a 64k por segmento	gráficos, texto, sonido grandes cantidades de datos
CHAR(n) CARACTER(n)	n caracteres	el conjunto de caracteres elegido determina el nº de caracters que caben en 32K	cadenas de longitud fija.
VARCHAR(n) CHAR VARYING(n) CHARACTER VARYING(n)	n caracteres	igual que el anterior	cadenas de longitud variable
SMALLINT	16 bits	-32768 a 32767	Signed short (word)
INTEGER	32bits	-2147483648 a 2,147,483,647	Signed long (longword)
FLOAT	32 bits	1175  10 -38  a 3402  10 38	IEEE simple precisión: 7 dígitos
DOUBLE PRECISION	64 bits (dependiente de la máquina)	2225  10 –308 a 1797 10 308	IEEE doble precisión: 7 digitos
NUMERIC (precision, escala)	variable 16, 32, 64	precisión: 1 a 18. escala: 0 a 18 lugares decimales	precisión establece el nº  exacto de digitos a almacenar.
DECIMAL (precision, escala)	variable 16, 32, 64	precisión: 1 a 18. escala: 0 a 18 lugares decimales	precisión establece el nº  máximo de digitos a almacenar.
DATE	32 bits	1 Enero 100 a.d. to 29 Feb 32768 a.d.
TIME	32 bits	0:00 AM-23:59.9999 PM	unidades de 0.0001 segundo desde la medianoche
TIMESTAMP	64 bits	1 Enero 100 a.d. to 29 Feb 32768 a.d.	combinación de DATE y TIME

También podemos definir matrices ¿?., pero no vamos a entrar en detalles, pues estamos usando bases de datos.

Cuando Interbase no puede aplicar una conversión de tipos por defecto, debemos hacerla nosotros efectiva mediante el moldeador de tipos CAST, que conocemos muy bien en C++. En este caso la sintaxis es :

CAST (value | NULL AS datatype)

Use el CAST( ) para transformar los siguientes tipos:

• DATE, TIME, o TIMESTAMP a tipo CHARACTER.
• CHARACTER a DATE, TIME o TIMESTAMP.
• TIMESTAMP a TIME o DATE,
• TIME o DATE A TIMESTAMP.

y se suele utilizar en las clausulas Where como la que sigue:

… WHERE CAST(HIRE_DATE AS CHAR) = INTERVIEW_DATE;

que transforma un tipo de dato DATE (HIRE_DATE) a uno CHAR.

1.4.  Definiendo Dominios.

1.4.1.      Creando Dominios

Es util crear dominios cuando hay muchos cmpos en la B.D. con idénticos valores. La sintaxis para crear dominios es la siguiente

CREATE DOMAIN domain [AS] <datatype>

[DEFAULT {literal | NULL | USER}]

[NOT NULL] [CHECK (<dom_search_condition>)]

[COLLATE collation];

Donde datatype es cualquier tipo de dato típico de Interbase.

La cláusula Default, permite al usuario definir un valor por defecto que se puede aplicar a esa columna definida por el dominio. el literal en ese caso se almacena en la columna si no se introduce valor alguno, o el valor NULL es dispuesto en ese lugar o si especificamos USER el valor que se almacena es el del  nombre de usuario que accede a la B.D. Aquí tenemos varios ejemplos:

CREATE DOMAIN USERNAME AS VARCHAR(20) DEFAULT USER;

CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME,

ORDER_AMT DECIMAL(8,2));

INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT)

VALUES ('1-MAY-93', 512.36);

Si especificamos la cláusula NOT NULL impide que el usuario no asigne ningún valor a esa columna.

En cuanto a las restricciones vienen definidas por la cláusula CHECK, y la condición que debe cumplir los valores (que no columnas):

<dom_search_condition> =

VALUE <operator> <val>

| VALUE [NOT] BETWEEN <val> AND <val>

| VALUE [NOT] LIKE <val> [ESCAPE <val>]

| VALUE [NOT] IN (<val> [, <val> …])

| VALUE IS [NOT] NULL

| VALUE [NOT] CONTAINING <val>

| VALUE [NOT] STARTING [WITH] <val>

| (<dom_search_condition>)

| NOT <dom_search_condition>

| <dom_search_condition> OR <dom_search_condition>

| <dom_search_condition> AND <dom_search_condition>

 

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

Sólo puede existir una cláusula check en un dominio, que no debe hacer referencia a otros dominios, y además la cláusula check de definición de tabla no sustituye a esta cláusula para una columna que tenga previamente definido un dominio.

El literal VALUE identifica un valor cualquiera de ese dominio, p. ej.:

CREATE DOMAIN CALIFORNIA

AS VARCHAR(25)

CHECK (VALUE LIKE '%, CA');

1.4.1.1.    Alterar la definición de un dominio

ALTER DOMAIN { name | old_name TO new_name } {

[SET DEFAULT {literal | NULL | USER}]

| [DROP DEFAULT]

| [ADD [CONSTRAINT] CHECK (<dom_search_condition>)]

| [DROP CONSTRAINT]

| new_col_name

| TYPE data_type

};

Esta es la sintaxis para modificar un dominio. Esta sentencia se utiliza cuando:

• Eliminar/establecer un valor por defecto.
• Eliminar/añadir una restricción CHECK
• Modificar el nombre del dominio y/o el tipo de datos.

Aquí tenemos tres ejemplos:

ALTER DOMAIN CUSTNO TO CUSTNUM;

ALTER DOMAIN CUSTNUM TYPE CHAR(20);

ALTER DOMAIN CUSTNO SET DEFAULT 9999;

1.4.1.2.    Eliminar un dominio

Elimina un dominio definido, tienes que ser usuario SYSDBA o usuario con privilegios o el creador. Si el dominio se usa en la definición de alguna columna de una tabla la operación falla.

La sintaxis es:

DROP DOMAIN name

1.5.  Definiendo Tablas

La sintaxis mínima de la sentencia que se encarga de crear tablas exige un nombre único de tabla y una única columna. Interbase impone el esquema por defecto de seguridad que consiste en asignar todos los privilegios al usuario que crea la tabla.

CREATE TABLE table [EXTERNAL [FILE] 'filespec']

(<col_def> [, <col_def> | <tconstraint> ...]);

1.5.1.      Definiendo columnas

Cuando creamos tablas, tu principal labor va a consistir en definir atributos y restricciones para las columnas de la tabla. la sintaxis para definir columnas es

<col_def> = col {datatype | COMPUTED [BY] (<expr>) | domain}

[DEFAULT {literal | NULL | USER}]

[NOT NULL] [<col_constraint>]

[COLLATE collation]

Vamos a diferenciar los elementos requeridos de los que no lo son.

Requerido:

• Nombre de columna
• Tipo de dato de SQL o una expresión de columna calculada (computed) o una definición de dominio.

Los elementos opcionales son:

• Valor por defecto
• Restricciones de integridad

o             Primary key

o             Unique

o             Foreign Key

• Obligar a insertar valores con NOT NULL
• Restricción CHECK  que debe cumplirse antes de insertar o actualizar la fila.
• Un conjunto de caracteres aceptado diferente del definido por defecto CARÁCTER SET.

Sobre estas columnas aplicaremos los tipos básicos definidos anteriormente, o los dominios previamente creados. También podemos definir una secuencia especial de ordenación con la cláusula COLLATE. (ver ayuda).

Veamos algunos ejemplos aclaratorios:

CREATE TABLE EMPLOYEE

(FIRST_NAME VARCHAR(10) NOT NULL,

LAST_NAME VARCHAR(15) NOT NULL,

FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME));

Este ejemplo usa la cláusula COMPUTER [BY] para crear un campo calculado, que se calcula cada vez que accedemos a la columna en tiempo de ejecución.

CREATE TABLE SALARY_HISTORY

(EMP_NO EMPNO NOT NULL,

CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,

UPDATER_ID VARCHAR(20) NOT NULL,

SH_OLD_SALARY SALARY NOT NULL,

PERCENT_CHANGE DOUBLE PRECISION

DEFAULT 0

NOT NULL

CHECK (PERCENT_CHANGE BETWEEN –50 AND 50),

NEW_SALARY COMPUTED BY

(OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),

CONSTRAINT PK_SH PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),

FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO)

ON UPDATE CASCADE

ON DELETE CASCADE);

En cuanto a los valores por defecto DEFAULT  podemos especificar un valor numérico o de fecha o string, el valor NULL o USER que pondrá el nombre del usuario conectado. Se usa cuando creamos una fila y no asignamos valor alguno a la columna en cuestión.

Cuando especificamos que la columna no puede contener nulos, usamos la cláusula NOT NULL, pero esta debe ir después de la cláusula DEFAULT y cuidado con cosas como esta:

CREATE TABLE MY_TABLE (COUNT INTEGER DEFAULT NULL NOT NULL);

Las restricciones de integridad son mantenidas por el sistema.

PRIMARY KEY  y UNIQUE:

Ambas aseguran que el valor introducido en una columna o conjunto de columnas son únicos para cada fila. Una tabla solo puede tener una clave primaria, que puedes especificar a nivel de columna o a nivel de tabla (detrás de la definición de una columna o al final de la definición de la tabla). Las claves primarias obligan a que se defina la cláusula NOT NULL para las columnas que forman parte de la clave primaria, esto último también es necesario para la cláusula UNIQUE, que define las claves alternativas sobre las que Interbase definirá una serie de índices que facilitan el acceso a la información.

Las columnas que forman parte de ambas cláusulas pueden ser referenciadas por la cláusula FOREIGN KEY  de otra tabla.

FOREIGN KEY  o la INTEGRIDAD REFERENCIAL.

Se trata de un conjunto de columnas o columna que corresponden exactamente a un conjunto de columna.

 En otra tabla que han sido definidas como clave primaria. La razón de uso de esta cláusula es el mantenimiento de la integridad.

Cuando definimos una Foreign key podemos decir que pasará cuando la clave primaria cambie. Las opciones son:

• NO ACTION  : Por defecto.  La clave ajena no cambia porque no se permite la operación.
• CASCADE : El valor correspondiente de la clave ajena es actualizado o borrado según lo sea el valor de la clave primaria.
• SET DEFAULT :  Toda columna de la clave ajena se pone a un valor por defecto. que hace fallar la integridad referencial si ese valor por defecto no se encuentra en la clave primaria.
• SET NULL : Todas las columnas de la clave ajena se ponen a NULL.

Si no usamos las cláusulas ON UPDATE o ON DELETE debemos usar TRIGGERS para asegurarnos que los cambios producidos en las claves primarias son recogidos por las ajenas para mantener la integridad referencial.

Las referencias circulares necesitan usar el comando ALTER TABLE para poder insertar valores.

 

1.5.2.      Definiendo restricciones

Las restricciones se pueden nombrar con la cláusula CONSTRAINT pero si no se nominan, el sistema le proporciona un nombre que se almacena en la tabla del sistema RDB$RELATION_CONSTRAINTS.

La sintaxis varía dependiendo de si la restricción es a nivel de columna o a nivel de tabla.

La sintaxis a nivel de columna:

<col_constraint> = [CONSTRAINT constraint] <constraint_def>

[<col_constraint> ...]

<constraint_def> =

UNIQUE | PRIMARY KEY

| CHECK (<search_condition>)

| REFERENCES other_table [(other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Y a nivel de tabla:

<tconstraint> = [CONSTRAINT constraint] <tconstraint_def>

[<tconstraint> ...]

<tconstraint_def> = {PRIMARY KEY | UNIQUE} (col [, col …])

| FOREIGN KEY (col [, col …])

    REFERENCES other_table [(other_col [, other_col …])]

       [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

       [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

| CHECK (<search_condition>)

Tenemos en cuenta que el dar un nombre a una restricción es útil cuando queremos cambiarla o eliminarla, y cuando se muestra el error correspondiente por incumplimiento de la restricción.

CREATE TABLE STOCK

(MODEL SMALLINT NOT NULL UNIQUE,

MODELNAME CHAR(10) NOT NULL,

ITEMID INTEGER NOT NULL,

CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));

Vemos una restricción que obliga a que sea única una combinación de columnas.

Pero el elemento fundamental de las restricciones reside en la cláusula CHECK.

         Esta cláusula verifica que el valor introducido en una columna no corresponde a un rango previo definido de valores correctos o verifica que pertenece a un conjunto de valores permitidos. Podemos usar valores de otras columnas en la condición de búsqueda.

La cláusula CHECK  garantiza la integridad únicamente entre los valores de una misma filas. Podemos incluir a otras filas u otras tablas, pero otro usuario o usted mismo puede modificar posteriormente los valores en las otras filas o tablas perdiendo la integridad antes supuesta.

La sintaxis es la siguiente:

CHECK (<search condition>);

<search_condition> =

<val> <operator> {<val> | (<select_one>)}

| <val> [NOT] BETWEEN <val> AND <val>

| <val> [NOT] LIKE <val> [ESCAPE <val>]

| <val> [NOT] IN (<val> [, <val> ...] | <select_list>)

| <val> IS [NOT] NULL

| <val> {[NOT] {= | < | >} | >= | <=}

   {ALL | SOME | ANY} (<select_list>)

| EXISTS (<select_expr>)

| SINGULAR (<select_expr>)

| <val> [NOT] CONTAINING <val>

| <val> [NOT] STARTING [WITH] <val>

| (<search_condition>)

| NOT <search_condition>

| <search_condition> OR <search_condition>

| <search_condition> AND <search_condition>

Debemos tener en cuenta las siguientes limitaciones:

Una restricción CHECK no puede referenciar a un dominio.

Una columna solo puede tener una cláusula CHECK.

Podemos añadir una restricción adicional a una columna que tenga un dominio con una cláusula CHECK, pero no podemos eliminarla.

CREATE TABLE JOB

(JOB_CODE JOBCODE NOT NULL,

JOB_GRADE JOBGRADE NOT NULL,

JOB_COUNTRY COUNTRYNAME NOT NULL,

JOB_TITLE VARCHAR(25) NOT NULL,

MIN_SALARY SALARY NOT NULL,

MAX_SALARY SALARY NOT NULL,

JOB_REQUIREMENT BLOB(400,1),

LANGUAGE_REQ VARCHAR(15) [5],

PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),

FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)

ON UPDATE CASCADE

ON DELETE CASCADE,

CHECK (MIN_SALARY < MAX_SALARY));

Las palabras BETWEEN , LIKE, IN, SOME, ANY, etc podemos buscar su significado en la ayuda.

1.5.3.      Modificar tablas

Podemos modificar la definición de las tablas usando la sentencia ALTER TABLE, que nos permite:

• Añadir columnas y restricciones
• Eliminar columnas y eliminar restricciones de integridad de una tabla o una columna.
• Modificar el nombre, tipo de datos y posición de una columna.

Nosotros podemos diseñar un conjunto de operaciones de las anteriores dentro de una única sentencia. Debemos contar con los privilegios necesarios.

Las modificaciones pueden ser hasta 255 antes que la B.D. obligue a un backup y restauración de la misma.

Antes de modificar una tabla, debemos hacer una copia de seguridad siguiendo los pasos que vamos a dar, para evitar la pérdida de los datos. Por ejemplo, la tabla employee contiene la columna office_no  de tipo char(3)  y vamos a modificarlo para que sea de tipo char(4), los pasos a seguir serían:

1.- ALTER TABLE EMPLOYEE ADD TEMP_NO CHAR(3);

 

2.- UPDATE EMPLOYEE

SET TEMP_NO = OFFICE_NO;

 

3.- ALTER TABLE ALTER OFFICE_NO TYPE CHAR(4);

 

4.- UPDATE EMPLOYEE

SET OFFICE_NO = TEMP_NO;

 

5.- ALTER TABLE DROP TEMP_NO;

Estos 5 pasos se pueden convertir en uno solo usando la siguiente sentencia:

ALTER TABLE EMPLOYEE ALTER COLUMN OFFICE_NO TYPE CHAR(4)

que realiza los mismos 5 pasos de una sola vez.

Para eliminar una columna debemos antes pensar en las cosas que pueden hacer fallar la operación:

• Falta de privilegios.
• La columna interviene en una restricción de la tabla CHECK.
• Es parte de una restricción primary key unique o foreign key
• La columna se usa en una vista, trigger o columna calculada.
• Al eliminar una columna la restricción primary key o unique genera valores duplicados.

La sintaxis para añadir columnas es la siguiente:

ALTER TABLE table ADD <col_def>

<col_def> = col {<datatype> | [COMPUTED [BY] (<expr>) | domain}

[DEFAULT {literal | NULL | USER}]

[NOT NULL] [<col_constraint>]

[COLLATE collation]

<col_constraint> = [CONSTRAINT constraint] <constraint_def>

[<col_constraint>]

<constraint_def> =

PRIMARY KEY

| UNIQUE

| CHECK (<search_condition>)

| REFERENCES other_table [(other_col [, other_col …])]

   [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

   [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

Podemos añadir columnas, con restricciones de integridad, con campos calculados basados incluso en otras columnas ya insertadas.

ALTER TABLE EMPLOYEE

ADD EMP_NO EMPNO NOT NULL,

ADD FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME);

 

ALTER TABLE COUNTRY

ADD CAPITAL VARCHAR(25) UNIQUE,

ADD LARGEST_CITY VARCHAR(25) NOT NULL;

Podemos añadir restricciones de integridad usando la siguiente sintaxis:

ALTER TABLE name ADD [CONSTRAINT constraint] <tconstraint_opt>;

Como por ejemplo:

ALTER TABLE EMPLOYEE

ADD CONSTRAINT DEPT_NO UNIQUE(PHONE_EXT);

La sintaxis necesaria para eliminar una columna es la siguiente:

ALTER TABLE name DROP colname [, colname ...];

Por ejemplo, si emp_no y full_name no forman parte de una cláusula UNIQUE, PRIMARY KEY, FOREIGN KEY podemos eliminarlas, o si forman parte de alguna restricción eliminar primero la restricción.

ALTER TABLE EMPLOYEE

 DROP EMP_NO,

 DROP FULL_NAME;

La modificación de columnas atiende a la siguiente sintaxis:

ALTER TABLE table ALTER [COLUMN]simple_column_name alter_rel_field

alter_rel_field = new_col_name | new_col_type | new_col_pos

   new_col_name = TO simple_column_name

   new_col_type = TYPE datatype_or_domain

   new_col_pos = POSITION integer

Por ejemplo, modificamos la posición de emp_no desde la tercera a la segunda posición:

ALTER TABLE EMPLOYEE ALTER EMP_NO POSITION 2;

Incluso podemos modificar el nombre:

ALTER TABLE EMPLOYEE ALTER EMP_NO TO EMP_NUM;

Podemos cambiar también el tipo de datos como decíamos al principio de este apartado, teniendo en cuenta las restricciones por conversiones.

1.5.4.      Eliminar tablas

La sintaxis para eliminar una tabla es la siguiente:

DROP TABLE name;

Si tiene privilegios suficientes puedes eliminar una tabla con esta instrucción, eliminando de paso los índices asociados, los triggers, metadatos etc.

No puedes eliminar una tabla que actualmente esta implicada en una transacción, o que esta referenciada por una columna calculada, una vista, una restricción de integridad o un procedimiento almacenado.

1.6.  Definiendo Índices.

SQL proporciona un procedimiento para crear índices de acceso rápido a los datos de una o mas tablas. La sintaxis es la siguiente:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index> ON

table (col [, col ...]);

De todas formas Interbase genera índices de sistema para las columnas definidas con la restricción UNIQUE o PRIMARY KEY.

Ejemplo:

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

Podemos especificar que solo admitan valores únicos, como el ejemplo anterior o podemos establecer un orden de almacenamiento:

CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);

1.7.  Definiendo Vistas.

Una vista es una tabla virtual creada a partir de un subconjunto de tablas de la B.D. Se usan para restringir el acceso a algunos usuarios y para mostrar datos de dos o mas tabla de una forma más sencilla para que algunos programas puedan usarlos. La vista no se almacena como una tabla en la B.D. sino que se almacena su definición que se ejecuta inmediatamente.

Para crear una vista usamos la sintaxis siguiente:

CREATE VIEW name [(view_col [, view_col ...)] AS

<select> [WITH CHECK OPTION];

Ejemplo:

CREATE VIEW PHONE_LIST AS

SELECT EMP_NO, FIRST_NAME, LAST_NAME, LOCATION, PHONE_NO

FROM EMPLOYEE, DEPARTMENT

WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO;

Debemos tener en cuenta ciertas restricciones cuando la vista puede ser modificda por otros usuarios.

Las columnas deben proceder de una única tabla

• No podemos definir un join en la misma tabla.
• Las columnas no pueden estar creadas con campos calculados.
• El SELECT de la vista  no debe tener una cláusula

o    WHERE que contenga una cláusula DISTINCT.

o    Una cláusula HAVING.

o    Procedimientos almacenados.

1.8.  Definiendo Procedimientos almacenados.

Un procedimiento almacenado es un programa autocontenido escrito en el lenguaje específico de procedimientos y triggers de interbase u almacenado como una parte de la definición de la base de datos (metadatos).

Una vez que se ha creado un procedimiento almacenado, podemos llamarlo directamente desde la aplicación o como si se tratase de una tabla en una consulta SELECT. Los procedimientos almacenados pueden recibir parámetro y devolverlos a las aplicaciones.

El lenguaje de triggers y procedimientos almacenados incluye instrucciones de manipulación de datos de SQL y algunas potentes extensiones, incluyendo IF... THEN ... ELSE, WHILE … DO, FOR SELECT … DO, excepciones y manejadores de errores.

Las ventajas de usar procedimientos almacenados incluyen:

• Diseño modular. Las  distintas aplicaciones que pueden acceder a la misma base de datos pueden compartir procedimientos, eliminando y reduciendo su tamaño.
• Mantenimiento en línea. Cuando un procedimiento se actualiza, los cambios son reflejados automáticamente en todas las aplicaciones que lo usan sin necesidad de recompilar o relinkar.
• Mejor funcionamiento. Los procedimientos almacenados se ejecutan en el servidor, no en el cliente, con lo que el tráfico se reduce, y mejora el funcionamiento del sistema especialmente para los clientes remotos.

1.8.1.      Trabajando con procedimientos almacenados.

Con isql, podemos crear, alterar u eliminar prodedimientos y excepciones. Cada una de estas operaciones se explicarán en adelante.

Hay dos formas de crear, modificar y eliminar procedimientos almacenados en isql:

Interactivamente o con un script, es preferible usar scripts, porque es mas fácil modificar las sentencias, incluso podemos introducir procedimientos almacenados sin necesidad de usar el IBConsole, mediante el comando:

isql -input filename database_name

Donde filename es el nombre del script, y database es el nombre de la base de datos.

Los procedimientos almacenados se pueden llamar desde el SQL y DSQL, incluso desde isql. Hay dos tipos de procedimientos almacenados:

Procedimientos SELECT  que una aplicación usa en lugar de una tabla o una vista en una instrucción SELECT. Debe ser definida para devolver un o más de un valor (parámetros de salida) o ocurrirá un error.

Procedimientos Ejecutables que una aplicación puede llamar directamente en una sentencia EXECUTE PROCEDURE. Un procedimiento ejecutable puede opcionalmente devolver valores al programa que lo llama.

Ambos tipos de procedimientos son definidos por la sentencia CREATE PROCEDURE y tienen esencialmente la misma sintaxis. La diferencia es como se escribe el procedimiento y como esta preparado para ser usado. Los select pueden devolver más que una sola fila, asi que el programa que lo llama lo ve como una tabla o vista. Los ejecutables son rutinas invocadas por  un programa que pueden opcionalmente devolver valores.

1.8.2.      LENGUAJE DE PROCEDIMIENTOS Y TRIGGERS.

Se trata de un completo lenguaje de programación para los procedimientos almacenados y triggers, que incluye:

Sentencias de manipulación de datos SQL : INSERT, UPDATE, DELETE y SELECT simples, también Cursores ( ya hablaremos de ellos).

Operadores y expresiones de SQL.

Extensiones de SQL que lo dotan de gran potencia, como instrucciones de asignación, de control de flujo, variables de contexto, excepciones, manejadores de errores y activadores por eventos.

Pero como el lenguaje es comun a triggers y procedimientos almacenados debemos decir que las variables de contexto solo se usan en triggers y las instrucciones SUSPEND y EXIT son propias de los procedimientos almacenados. Además mostramos una serie de instrucciones que no se pueden usar en este lenguaje:

• INSTRUCCIONES DE DEFINICIÓN DE DATOS. CREATE, ALTER, ... ETC.
• INSTRUCCIONES DE CONTROL DE TRANSACCIONES.
• DSQL.
• CONNECT/DISCONNECT BASES DE DATOS.
• GRANT/REVOKE.
• SET GENERATOR.
• EVENT INIT/WAIT.
• BEGIN/END DECLARE SECTION.
• BASED ON.
• WHENEVER.
• DECLARE CURSOR.
• OPEN
• FETCH.

A continuación mostraremos brevemente las instrucciones que se pueden usar en este lenguaje en una tabla resumen.

BEGIN ... END	bloque de instrucciones, no terminan con ;
Variable = expresión	instrucción de asignación.
/* comentario */	sin comentarios
EXCEPTION nombre excepcion	un error de usuario que puede ser gestionado con una sentencia WHEN.
EXECUTE PROCEDURE procedimiento [var,...] [RETURNING_VALUES var [,var...]]	Permite procedimientos anidados y recursión
EXIT	Salta al final del procedimiento.
FOR instrucción select DO  instrucciones	Repite el conjunto de instrucciones por cada fila que devuelva la instrucción select.
IF (condicion) THEN instrucciones [ELSE instrucciones]	control de flujo típico.
POST_EVENT nombre del evento	genera un evento.
SUSPEND	se suspende la ejecución del procedimiento hasta que el que lo llama pueda procesar los datos que el actual procedimiento genera.
WHILE (condición) DO instrucciones	La condición se testea, y si es cierta se ejecuta la instrucción
WHEN {error[,error...] | ANY} DO instrucciones	Manejador de errores, cuando uno de los especificados ocurre se ejecuta la instrucción. Debe estar antes justamente de la instrucción END del procedimiento.

Estas instrucciones las iremos tratando individualmente posteriormente.

1.8.3.      Creación de procedimientos

              Los procedimientos se crean con la sentencia CREATE PROCEDURE de isql. se componen de cabecera y cuerpo.

La cabecera contiene el nombre del procedimiento y una lista opcional de parámetros de entrada con los tipos de datos asociados y si el procedimiento devuelve valores al programa que lo llama RETURNS  seguido de una lista de parámetros de salida (si, pueden ser varios) y sus tipos de datos asociados.

El Cuerpo contiene una lista opcional de variables locales y sus tipos de datos y un bloque BEGIN ... END , que puede contener en su interior más bloques.

Debido a que cada sentencia de un procedimiento almacenado debe terminar con un punto y coma, el carácter terminador debe modificarse en  isql, para ello se usa la sentencia SET TERM que define un terminador diferente que el punto y coma, debemos recordar cambiarlo antes y después de la definición del procedimiento almacenado.

CREATE PROCEDURE name

[(param datatype [, param datatype …])]

[RETURNS (param datatype [, param datatype …])]

AS

[DECLARE VARIABLE var datatype; …]

BEGIN

<compound_statement>

[<compound_statement> …]

END

1.8.3.1.    La cabecera de un procedimiento.

Se trata de todo lo que está situado antes de la palabra AS. El nombre del procedimiento (único en la B.D.), una lista opcional de parámetros de entrada y tipos de datos asociados que son recibidos por el procedimiento en el momento de la llamada y opcionalmente, una lista de parámetros de salida después de la palabra RETURNS.

Declaración de parámetros de entrada. Se situan entre paréntesis, separados por comas, después del nombre del procedimiento.

CREATE PROCEDURE name (var datatype [, var datatype …])

El tipo de datos es uno de los estándar de SQL, y, cuidado ! no podéis mandar más de 1400 parámetros de entrada a un procedimiento.

Declaración de parámetros de salida. La sintaxis es la siguiente :

… [RETURNS (var datatype [, var datatype …])] AS …

1.8.3.2.    El cuerpo del procedimiento.

Se sitúa después de la palabra AS consiste en una lista opcional de variables locales seguidas por un bloque (BEGIN ... END).

Hay 3 tipos de variables, los parámetros de entrada, de salida y las locales. En SQL se debe poner : antes de las variables para distinguirlas de las columnas de una tabla, en este caso no hay que hacerlo.

Ya hemos visto como se declaran los parámetros. Las variables locales se declaran usando la siguiente sintaxis:

DECLARE VARIABLE var datatype;

SELECT

Si usamos una sentencia SELECT que devuelve una única fila podemos hacerlo con la cláusula INTO, que en este caso debe aparecer al final de la sentencia y asignar el resultado a variables previamente definidas:

SELECT SUM(BUDGET), AVG(BUDGET)

FROM DEPARTMENT

WHERE HEAD_DEPT = :head_dept

INTO :tot_budget, :avg_budget;

Sin embargo, al usar una sentencia SELECT que devuelva un conjunto de filas, debemos usar la instrucción FOR...DO:

 

FOR

<select_expr>

DO

<compound_statement>;

Tener en cuenta que la FOR SELECT  difiere de una cláusula SELECT , primero porque es un bucle que ejecuta las instrucciones del bloque posterior a DO por cada fila recuperada, y después la necesidad de la cláusula INTO que debe aparecer al final y obligatoriamente. Por Ej.

FOR SELECT DEPT_NO

FROM DEPARTMENT

WHERE HEAD_DEPT = :DNO

INTO :RDNO

DO

BEGIN

/*EXECUTE PROCEDURE DEPT_BUDGET :RDNO RETURNS :SUMB;*/

TOT = TOT + RDNO;/*SUMB*/

END

… ;

WHILE ... DO

La instrucción WHILE ... DO. Es un bucle que repite el conjunto de instrucciones mientras la condición sea cierta. Su sintaxis es la siguiente:

WHILE (<condition>) DO

<compound_statement>

<compound_statement> =

{<block> | statement;}

Por ejemplo veamos un procedimiento almacenado sencillo:

SET TERM !!;

 

CREATE PROCEDURE SUM_INT (I INTEGER) RETURNS (S INTEGER)

AS

BEGIN

s = 0;

WHILE (i > 0) DO

BEGIN

s = s + i;

i = i - 1;

END

END!!

 

SET TERM ; !!

Si lo ejecutamos desde isql:

EXECUTE PROCEDURE SUM_INT 4;

S

===========

10

¿o no?

IF THEN ELSE.

La instrucción La sintaxis es :

IF (<condition>)

THEN <compound_statement>

[ELSE <compound_statement>]

<compound_statement> = {<block> | statement;}

Por ejemplo.

IF (FIRST IS NOT NULL)

THEN LINE2 = FIRST || ' ' || LAST;

ELSE LINE2 = LAST;

. . .

Eventos.

La posibilidad de activar eventos nominados es util en algunos casos. la sintaxis es la siguiente:

POST_EVENT <event_name>;

El nombre del evento puede ser una cadena de caracteres o una variable. Por ejemplo:

POST_EVENT 'new_order';

POST_EVENT event_name;

Recursión

Un procedimiento almacenado puede ejecutarse a si mismo. Un procedimiento almacenado puede tambien ejecutar otros procedimientos creando los llamados procedimientos anidados.

Para prevenir bucles infinitos el nivel de profundidad de recursividad permite solo 1000 llamadas recursivas.

Veamos un ejemplo ¿a que os suena?

SET TERM !!;

 

CREATE PROCEDURE QUEHACE (NUM INT)

RETURNS (DEVUELVE DOUBLE PRECISION)

AS

DECLARE VARIABLE NUM_LESS_ONE INT;

BEGIN

IF (NUM = 1) THEN

BEGIN

   DEVUELVE = 1;

   SUSPEND;

END

ELSE

BEGIN

   NUM_LESS_ONE = NUM - 1;

   EXECUTE PROCEDURE QUEHACE NUM_LESS_ONE

   RETURNING_VALUES DEVUELVE;

   DEVUELVE = DEVUELVE * NUM;

   SUSPEND;

END

END!!

 

SET TERM ;!!

 Uso de SUSPEND, EXIT y END.

Quizas SUSPEND sea la más difícil de comprender, ya que para la ejecución del procedimiento, devolviendo los parámetros de salida, hasta que el procedimiento que lo llama no “busca” nuevos datos, continuando posteriormente la ejecución.

EXIT Se usa con los procedimientos para llevar al END del mismo, y END genera el SQLCODE 100 que indica que la instrucción no tiene más columnas que recuperar.

SET TERM !!;

 

CREATE PROCEDURE P RETURNS (R INTEGER)

AS

BEGIN

   R = 0;

               WHILE (R < 5) DO

      BEGIN

                         R = R + 1;

             SUSPEND;

             IF (R = 3) THEN

           EXIT;

                  END

END;

SET TERM ;!!

 Lo interesante para ver la diferencia de SUSPEND en las dos formas de ejecutar procedimientos almacenados lo tenemos aquí. Si ejecuto

EXECUTE PROCEDURE P;  El resultado es 1. En cambio si ejecuto:

SELECT * FROM P; El resultado es 1,2,3.

 

ERRORES.

Cuando ocurre un error, ya sea de SQLCODE, GDSCODE  (ver referencia del lenguaje en la ayuda de InterBase) o una excepción definida por el usuario, todas las sentencias desde el último SUSPEND son deshechas. Si es un procedimiento ejecutable (con EXECUTE PROCEDURE) y no tiene sentencia SUSPEND como se recomienda, todo el procedimiento se deshace.

Excepciones.

Digamos que una excepción es un error que genera el usuario, se identifica por un nombre y lleva asociado un mensaje que el usuario define.

Para crear una excepción usamos la sintaxis siguiente:

CREATE EXCEPTION name '<message>';

También podemos modificarla con ALTER y eliminarla con DROP.

La importancia de las excepciones es que podemos usarlas en procedimientos almacenados y activarlas en ellos. Para esto usamos la sintaxis siguiente:

EXCEPTION name:

Cuando se ejecuta esta instrucción  el procedmiento termina y se deshacen todas las operaciones realizadas.

Devuelve un mensaje de error.

WHEN ... DO.

Los procedimientos almacenados pueden gestionar 3 clases de errores dentro de la instrucción WHEN ... DO

• Excepciones creadas por la instrucción EXCEPTION del procedimiento actual o de los anidados o de un trigger disparado por alguna acción realizada por el procedimiento.
• Errores SQL  con código SQLCODE.
• Errores de InterBase con código GDSCODE.

La sintaxis de la instrucción es la siguiente:

WHEN {<error> [, <error> …] | ANY}

DO <compound_statement>

<error> =

{EXCEPTION exception_name | SQLCODE number | GDSCODE errcode}

 

La instrucción debe ser la última del bloque y después de una instrucción SUSPEND.

Con lo que respecta a las excepciones el procedimiento almacenado puede gestionar la excepción y corregir el posible error generado. Cuando ocurre una excepción se hace lo siguiente:

• Búsqueda una instrucción WHEN ... DO para gestionar la excepción. si no la encuentra, el bloque BEGIN ... END  que contiene la excepción se deshace y termina.
• Subimos un nivel de anidamiento y hacemos lo mismo que en el paso anterior.
• Si encontramos una instrucción WHEN ... DO se ejecuta el conjunto de instrucciones que están definidas para ese error.
• Continua la ejecución del procedimiento en la instrucción posterior a la instrucción WHEN.

Una excepción que es gestionada no genera ningún mensaje de error.

Lo mismo ocurre con SQLCODE y GDSCODE en vez de una excepción.

Veamos un ejemplo :

SET TERM !! ;

CREATE PROCEDURE NEW_PROJECT

(id CHAR(5), name VARCHAR(20), product VARCHAR(12))

RETURNS (result VARCHAR(80))

AS

BEGIN

INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)

VALUES (:id, :name, :product);

result = 'Values inserted OK.';

INSERT INTO PROJECT (PROJ_ID, PROJ_NAME, PRODUCT)

VALUES (:id, :name, :product);

result = 'Values Inserted Again.';

EXIT;

 

WHEN SQLCODE -803 DO

BEGIN

   result = 'Could Not Insert Into Table - Duplicate Value';

   EXIT;

END

END!!

SET TERM ; !!

Al ejecutarlo como un  procedimiento ejecutable

EXECUTE PROCEDURE NEW_PROJECT 'XXX', 'Project X', 'N/A';

genera el mensaje 'Could Not Insert Into Table - Duplicate Value' y el procedimiento completo se deshace.

SELECT * FROM SIMPLE('XXX', 'Project X', 'N/A');

Muestra el mensaje 'Values inserted OK.' indicando que el mensaje ha sido correctamente insertado, y después genera el mismo mensaje que el anterior, pero por lo menos se ha insertado una fila.

 

1.9.  Definiendo Triggers.

Un trigger es una rutina asociada a una tabla o vista que se ejecuta automáticamente cuando una fila de la tabla o vista es insertada, actualizada o eliminada.

Nunca podemos llamar a un trigger directamente, sólo cuando una aplicación o usuario realiza una de las acciones anteriores, los triggers asociados (puede haber más de uno) se disparan o ejecutan automáticamente.

Los triggers pueden hacer uso de las excepciones para manejar errores. Si un trigger dispara una excepción devuelve un mensaje de error, el trigger termina y todas los cambios hechos dentro del trigger hasta ese momento se deshacen, para mantener la consistencia de la base de datos. Todo esto ocurre si no hay una cláusula WHEN que recoja la excepción.

Ventajas:

• Refuerzo de la restricción de datos en los valores de columna.
• Reducción de las aplicaciones de mantenimiento.
• Aviso automático de cambios en la base de datos en conjunción con los alertadores de eventos.

1.9.1.      Creación de triggers.

Un trigger se define con la sentencia CREATE TRIGGER que está compuesta de cabecera y cuerpo. La cabecera contiene:

• El nombre único dentro de la base de datos.
• Un nombre de tabla, identificando la tabla asociada al trigger
• Instrucciones que determinan cuando se dispara.
• Por otro lado el cuerpo contiene:
• Opcionalmente una lista de variables locales y sus tipos de datos.
• Un bloque de instrucciones en el lenguaje de procedimientos y triggers .

 

Sintaxis:

CREATE TRIGGER name FOR {table | view}

[ACTIVE | INACTIVE]

{BEFORE | AFTER} {DELETE | INSERT | UPDATE}

[POSITION number]

AS

 

[DECLARE VARIABLE variable datatype; …]

BEGIN

<compound_statement> [<compound_statement> …]

END

El valor BEFORE/AFTER  indica cuando se disparará el trigger, si antes o después de la operación asociada.

POSITION especifica el orden de ejecución de diferentes triggers asociados a la misma acción (previa o posterior) el menor número indica el primero a disparar, a igualdad de número se ejecutarán en orden alfabetico por nombre.

CREATE TRIGGER A FOR ACCOUNTS BEFORE UPDATE POSITION 5 AS …

CREATE TRIGGER B FOR ACCOUNTS BEFORE UPDATE POSITION 0 AS …

CREATE TRIGGER C FOR ACCOUNTS AFTER UPDATE POSITION 5 AS …

CREATE TRIGGER D FOR ACCOUNTS AFTER UPDATE POSITION 3 AS …

  UPDATE ACCOUNTS SET C = 'canceled' WHERE C2 = 5;

Estos triggers ante esta operaciones ejecutan en el orden B,A, actualización, D y C.

Hay una serie de instrucciones que no se pueden incluir en el cuerpo de un trigger, como las típicas de DDL y DCL y control de transacciones etc. estas instrucciones las pueden consultar en la ayuda de Interbase.

Dos nuevas instrucciones aparecen asociadas a los triggers :

NEW.columna y OLD. columna, que hacen referencia a un valor nuevo en una operación de INSERT O UPDATE o al valor que la columna contenía en esas mismas operaciones.

NEW  se usa en los triggers asociados a INSERT Y UPDATE.

OLD se usa en los triggers asociados a UPDATE Y DELETE.

Los nuevos valores (NEW.columna) para una fila solo se pueden modificar en los BEFORE triggers, un trigger que se dispare DESPUÉS de insertar y intente asignar un valor a NEW.columna  no tendrá ningún efecto. Los valores actuales de las columnas no se alterarán hasta después de la acción, asi que los triggers que pregunten por  valores nuevos no podrán preguntar por el nuevo valor insertado o actualizada a menos  que se hayan disparado después de una UPDATE O INSERT.

Las operaciones de alteración de triggers  ALTER TRIGGER y eliminación de los mismos DROP TRIGGER siguen las normas típicas de operaciones similares, a excepción de la eliminación de un trigger, que requiere que éste esté inactivo.

Autor: Antonio Lopez Atienza