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 la página 6 sería la siguiente:

CREATE TABLE FACTURA(

REFERENCIA NUMBER(30) 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 Oracle Error Messages.

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.

Así mismo Oracle recomienda que los índices residan en un tablespace separado al de las tablas. Esto es debido a la siguiente razón: Dos tablespaces distintos están soportados 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 meter 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 porque la condición más habitual en una consulta a cualquier tabla es por los campos de su clave primaria. De esta forma se aceleran la gran mayoría de las consultas (recordar que un índice 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.

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 es necesario ejecutarla 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.

Ejemplo:

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’;

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};

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;

Acceso a secuencias:

Las secuencias al ser tablas se acceden a través de consultas SELECT. La única diferencia es que se utilizan pseudocolumnas para recuperar tanto el valor actual como el siguiente de la secuencia. Al ser pseudocolumnas se puede incluir en el FROM cualquier tabla o bien la tabla DUAL.

Nombre_secuencia.CURRVAL: retorna el valor actual de la secuencia. Nombre_secuencia.NEXTVAL: incrementa la secuencia y retorna el nuevo valor. 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;

SELECT REF_FACTURA.CURRVAL FROM DUAL;

SELECT COD_CLIENTE.NEXTVAL FROM DUAL;

SELECT COD_CLIENTE.NEXTVAL, D_CLIENTE FROM CLIENTE;

UPDATE CLIENTE

SET CODIGO = SECUENCIA_CLIENTE.NEXTVAL;

INSERT INTO CLIENTE

VALUES( SECUECIA_CLIENTE.NEXTVAL, ‘Juancito Pérez Pí’ );

Toda sentencia de creación CREATE tiene su equivalente para eliminar el objeto creado.

Todas estas sentencias tienen la misma sintaxis:

DROP tipo_objeto objeto_a_borrar.

Ejemplos:

DROP TABLE FACTURA;

DROP SEQUENCE COD_CLIENTE;

DROP SYNONYM BILL;

DROP VIEW TOTAL_FACTURA_CLIENTE; DROP TABLESPACE tab_indices;

Ciertas sentencias DROP (como DROP TABLE o DROP TABLESPACE) tienen cláusulas adicionales para ciertas situaciones especiales. Para más información buscar la ayuda de la sentencia necesitada en el Oracle SQL Reference.

Al igual que existe una sentencia DROP para cada objeto creado, también existe una sentencia ALTER para cada objeto de base de datos. Con estos tres grupos de sentencias se hace la gestión completa de los objeto: creación, modificación y borrado.

La sintaxis básica de las sentencias ALTER es:

ALTER tipo_objeto nombre_objeto Cláusulas específicas de cada tipo de ALTER;

Las cláusulas propias de cada sentencia ALTER son muchas y variadas, por lo que aquí no se citarán más que ciertos ejemplos. Para más información dirigirse la ayuda de la sentencia necesitada en el Oracle SQL Reference.

Ejemplos:

ALTER TABLE FACTURA ADD(

NUEVA_COLUMNA VARCHAR2(10) NOT NULL );

ALTER VIEW BILL COMPILE;

ALTER SEQUENCE NOCYCLE;

La sentencia TRUNCATE pertenece al conjunto de las sentencias DDL, y permite vaciar todos los registros de una tabla. Aparentemente es equivalente a hacer un DELETE sin condición, pero en realidad no es igual, ya que DELETE pertenece al subconjunto de DDL y TRUNCATE al DML.

La sintaxis básica es:

TRUNCATE nombre_tabla {DROP|REUSE STORAGE}

La cláusula DROP STORAGE eliminará todas las extents creadas durante la vida de la tabla.

Ejemplos:

TRUNCATE FACTURA DROP STORAGE;

TRUNCATE CLIENTE;

Las funciones SQL permiten mostrar columnas calculadas dentro de sentencias DML (SELECT, INSERT, DELETE y UPDATE).

Función Descripción ADD_MONTHS( d, n ) Suma un número (positivo o negativo) de meses a una fecha. LAST_DAY( d ) Retorna el ultimo día de mes de la fecha pasada. MONTHS_BETWEEN( d1, d2 ) Retorna la diferencia en meses entre dos fechas. ROUND( d, s ) Redondea la fecha d según el formato indicado en s. (*) TRUNC( d, s ) Trunca la fecha d según el formato indicado en s. (*)

Formatos para ROUND y TRUNC para fechas:

Formato Descripción ‘MONTH’, ‘MON’, ‘MM’ Principio de mes ‘DAY’, ‘DY’, ‘D’ Principio de semana ‘YEAR’, ‘YYYY’, ‘Y’ Principio de año

Estas funciones actúan sobre un conjunto de valores, retornando sólo un registro.

Función Descripción SUM( valores ) Retorna la suma. AVG( valores ) Retorna la media aritmética MAX( valores ) Retorna el máximo. MIN( valores ) Retorna el mínimo COUNT(valores|* ) Retorna la cuenta. Todas estas funciones permite incluir el modificador DISTINCT delante de la lista de valores para que omita los repetidos.

Función Descripción CHARTOROWID( s ) Convierte una cadena en tipo de dato ROWID. ROWIDTOCHAR( rowid ) Convierte un tipo de dato ROWID en cadena de caracteres. TO_CHAR( *[, s] ) Convierte el tipo de dato * en cadena de caracteres. Si * es una fecha, se podrá utilizar la cadena s como formato de conversión. TO_DATE( s1[, s2] ) Convierte la cadena s1 en fecha, conforme al formato de convesión s2. TO_NUMBER( s ) Convierte una cadena de caracteres en valor numérico.

Función Descripción DUMP( columna ) Retorna información de almacenamiento para la columna indicada. GREATEST( expr1, expr2, … exprN ) Retorna la expresión mayor. LEAST( expr1, expr2, … exprN ) Retorna la expresión menor. NVL( expr1, expr2 ) Retorna expr2 si expr1 es NULL, sino retorna expr1. USEREVN( s ) Retorna opciones de entorno de la sesión activa: Los valores para s pueden ser: • ‘ISDBA’: Retorna ‘TRUE’ si el usuario activo tiene el rol DBA. • ‘LANGUAGE’: Idioma activo. • ‘TERMINAL’: Nombre de terminal donde se realiza la consulta. • ‘SESSIONID’: Número de sesión activa. DECODE( expr_ev, Caso_1, ret_1, Caso_2, ret_2, … Caso_N, ret_N, Caso_else ) Permite hace una evaluación de valores discretos. Es similar a la estructura switch de C/C++ o case of de Pascal. Ejemplo: DECODE( COLOR, ‘R’, ‘Rojo’, ‘V’, ‘Verde’, ‘A’, ‘Azul’, ‘Color desconocido’ )

Debido a que en las operaciones normales con la base de datos puede corromper la información, todas las bases de datos tiene un sistema de control de transacción.

Se denomina transacción al espacio de tiempo que hay desde que se hace la primera sentencia DML que no sea SELECT (INSERT, UPDATE, DELETE), hasta que damos por finalizada la transacción explícitamente (con las sentencias apropiadas) o implícitamente (terminando la sesión).

Durante la transacción, todas las modificaciones que hagamos sobre base de datos, no son definitivas, más concretamente, se realizan sobre un tablespace especial que se denomina tablespace de ROLLBACK, o RBS (RollBack Segment). Este tablespace tiene reservado un espacio para cada sesión activa en el servidor, y es en ese espacio donde se almacenan todas las modificaciones de cada transacción. Una vez que la transacción se ha finalizado, las modificaciones temporales almacenadas en el RBS, se vuelcan al tablespace original, donde está almacenada nuestra tabla. Esto permite que ciertas modificaciones que se realizan en varias sentencias, se puedan validar todas a la vez, o rechazar todas a la vez.

Las sentencias de finalización de transacción son:

COMMIT: la transacción termina correctamente, se vuelcan los datos al tablespace original

y se vacía el RBS.

ROLLBACK: se rechaza la transacción y el vacía el RBS.

Ejemplo de transacción:

Histórico de sentencias:

SELECT;

SELECT; SELECT; UPDATE; SELECT; INSERT; UPDATE;

SELECT; UPDATE; COMMIT;

Transacción

Si nuestro número de sentencias es tan grande que el RBS se llena, Oracle hará un ROLLBACK, por lo que perderemos todos los datos. Así que es recomendable hacer COMMIT cada vez que el estado de la base de datos sea consistente.

Si terminamos la sesión con una transacción pendiente, Oracle consultará el parámetro AUTOCOMMIT, y si éste está a TRUE, se hará COMMIT, si está FALSE se hará ROLLBACK;

Ya dijimos en los primeros capítulos que SQL es un lenguaje de comandos, no un lenguaje de programación con todas las estructuras de control típicas. Así, SQL sólo contempla instrucciones, más o menos simples, pero no tiene ningún tipo de instrucciones de control de flujo o de otro tipo más propias de los lenguajes de programación 3GL. Para subsanar esta carencia, Oracle definió un lenguaje de programación de tercera generación, que admitía sentencias SQL embebidas. Este lenguaje se llama PL/SQL (Programming Language/SQL)

La idea básica sobre la que se sustenta el PL/SQL es aplicar las estructuras típicas de un lenguaje de programación (bifurcaciones, bucles, funciones…) a las sentencias SQL típicas.

Así podemos tener el siguiente pseudocódigo:

Sentencia SELECT que recupera el total de sueldos

Si el total de sueldos > 1.000.000

Sentencia UPDATE que incrementa un 10% los sueldos

Si no

Sentencias UPDATE que incrementa un 5% los sueldos

Fin-si

Cuando se escribe código en PL/SQL, este debe estar agrupado en unidades denominadas "bloques de código". Un bloque de código puede contener otros sub-bloques de código y así sucesivamente.

Un bloque de código queda delimitado por las palabras reservadas BEGIN y END. Por ejemplo:

BEGIN

Sentencias . . .

Sentencias . . .

Sentencias . . .

BEGIN

Sentencias . . .

Sentencias . . .

Sentencias . . .

END;

Sentencias . . .

Sentencias . . .

Sentencias

END;

En este ejemplo podemos ver que hay un bloque de código externo que contiene un bloque de código interno. Un bloque de código opcionalmente puede contar con las siguientes secciones:

DECLARE

Declaración de VARIABLES

BEGIN

Sentencias SQL y PL/SQL

EXCEPTION

Manejadores de excepciones

END;

La única sección obligatoria es la contenida dentro de BEGIN y END;

Comentarios

Los comentarios pueden ser multilínea encerrados entre /* y */ o monolínea, que comienzan por –

Las variables deben declararse dentro de la sección DECLARE y deben seguir la siguiente sintaxis:

Nombre_de_variable {CONSTANT} TIPO {:= inicialización};

Los tipos posibles son todos aquellos válidos para SQL añadiendo algunos propios de PL/SQL. Para más información sobre los tipos propios de PL/SQL consultar el PL/SQL User’s Guide and Reference

Ejemplos:

Interes NUMBER(5,3); Descripcion VARCHAR2(50) := ‘inicial’; Fecha_max DATE; Contabilizado BOOLEAN := TRUE; PI CONSTANT REAL := 3.14159

Como PL/SQL es un lenguaje 3GL, cuenta con las estructuras típicas de control de flujo: bifurcaciones condicionales y bucles:

La sintaxis básica es:

IF condición_1 THEN

Se ejecuta si se cumple condicion_1

ELSIF condicion_2 THEN – la palabra reservada es ELSIF y no es ELSEIF

Se ejecuta si no se cumple condicion_1 y se cumple condicion_2

ELSE

Se ejecuta si no se cumple condicion_1 ni condicion_2

END IF;

Como en cualquier lenguaje de programación, las estructuras IF se pueden anidar unas dentro de otras.

Bucles Existen varias variantes de la estructura bucle. La más sencilla es la siguiente:

LOOP

sentencias

END LOOP;

Las sentencias de dentro del bucle se ejecutarán durante un número indefinido de vueltas, hasta que aparezca la instrucción EXIT; que finalizará el bucle. Este tipo de bucle se denomina bucle incondicional. Otra opción es incluir la estructura EXIT WHEN condición, se terminará el bucle cuando la condición se cumpla:

LOOP Sentencias

EXIT WHEN condicion;

Sentencias

END LOOP;

El bucle anterior es equivalente al siguiente:

LOOP

Sentencias

IF condicion THEN EXIT;

END IF;

Sentencias

END LOOP;

Un tipo de bucle más común son los bucles condicionales:

WHILE condicion LOOP

Sentencias

END LOOP;

Y por último el bucle FOR:

FOR contador IN {REVERSE} limite_inferior..limite_superior LOOP

sentencias

END LOOP;

Contador deberá ser una variable de tipo numérico que sea capaz de contener los valores comprendidos entre limite_inferior y limite_superior. Limite_inferior y limite_superior deberán ser expresiones numéricas, ya sean constantes (1,10…) o funciones (ROUND(max,0), ASCII(‘A’)…)

Si la variable contador no está definida, PL/SQL definirá una variable de tipo INTEGER al iniciar el bucle, y la liberará al finalizar el bucle.

Existen dos tipos de datos que no hemos mencionado anteriormente: los registros (o estructuras) y las tablas (o arrays o vectores).

Los dos tipos deben ser definidos en un como un nuevo tipo antes de declarar variables de ese nuevo tipo. El modo de definir nuevos tipos de variables en PL/SQL es a través de la palabra reservada TYPE:

TYPE nuevo_tipo IS tipo_original.

Una vez definido en nuevo tipo, ya se pueden definir variables de ese nuevo tipo:

Una_variable nuevo_tipo;

Registros:

Los registros no son más que agrupaciones de tipos de variables que se acceden con el mismo nombre.

La sintaxis de definición de registros es:

TYPE nombre_registro IS RECORD( Campo1 tipo,

Campo2 tipo, Campo3 tipo );

Por ejemplo:

TYPE alumno IS RECORD( n_alumno VARCHAR2(5), nombre VARCHAR2(25),

apellido_1 VARCHAR2(25), apellido_2 VARCHAR2(25), tlf VARCHAR2(15) );

Tablas:

Una tabla no es más que una colección de elementos identificados cada uno de ellos por un índice. En muchos lenguajes se les denomina arrays.

La sintaxis de definición de tablas es:

TYPE nombre_tabla IS TABLE OF tipo_de_elementos;

El tamaño de la tabla se define durante la declaración de la variable

Nombre_variable nombre_tabla := nombre_variable( lista de elementos );

Por ejemplo:

DECLARE

TYPE array_enteros IS TABLE OF INTEGER;

Un_array array_enteros := array_enteros( 0, 0, 0, 0 );

BEGIN

END;

El ejemplo anterior define un tipo de array de enteros y después declara una variable de ese tipo, inicializándola a 4 elementos (todos con 0).

Anteriormente dijimos que un bloque de código puede contener una sección denominada EXCEPTION. Esta sección es la encargada de recoger todas las anomalías que se puedan producir dentro del bloque de código.

Una excepción es una situación especial dentro de la ejecución de un programa, que puede ser capturada para asignar un nuevo comportamiento. Una excepción puede ser un error de ejecución (una división entre 0) o cualquier otro tipo de suceso.

Las excepciones deben ser declaradas dentro de la sección DECLARE, como si de una variable se tratasen:

DECLARE

e_sin_alumnos EXCEPTION;

Una vez que la excepción está definida, ésta debe ser lanzada, ya sea automáticamente por Oracle, o lanzada manualmente a través de la instrucción RAISE.

SELECT COUNT(*) INTO num_alumnos;

IF num_alumnos = 0 THEN RAISE e_sin_alumnos;

END IF;

Una vez que la excepción ha sido lanzada, la ejecución continua en la sección EXCEPTION, concretamente en el manejador apropiado (o el manejador WHEN OTHERS cuando no exista el específico).

Un manejador de excepciones es una sub-sección dentro de la sección EXCEPTION que se encarga de capturar una excepción concreta.

La sintaxis para escribir manejadores es:

EXCEPTION

WHEN <excepción> THEN

WHEN <otra_excepción> THEN WHEN OTHERS THEN

END;

Las líneas de código debajo del manejador específico se ejecutarán cuando esa excepción se produzca. Un ejemplo completo:

DECLARE

e_sin_alumnos EXCEPTION;

num_alumnos NUMBER(5); BEGIN

SELECT COUNT(*)

INTO num_alumnos;

IF num_alumnos = 0 THEN

RAISE e_sin_alumnos; END IF;

EXCEPTION

WHEN e_sin_alumno

INSERT INTO ERROR( FECHA, DESCRIPCION )

VALUES( SYSDATE, ‘No se han encontrado alumnos en la tabla ALUMNO.’);

WHEN OTHERS

Raise_application_error( -20000, ‘Error en bloque de codigo PL/SQL’ );

-- este error se transmite a la aplicación que llame a este bloque de código (PL/SQL,

-- Java, C/C++, etc.)

END;

Anteriormente habíamos dicho que las excepciones puede lanzarse automáticamente o manualmente a través de la instrucción RAISE.

Algunas excepciones se lanzarán automáticamente cuando se produzcan ciertos tipos de errores en la ejecución del bloque de código. Cada excepción automática tiene asociado un código de error ORA-XXXX el cual si se produce, hará que se lance la excepción correspondiente.

A continuación se muestra una lista de las excepciones automáticas predefinidas por Oracle:

Cuando dentro de un intérprete SQL escribimos una consulta SELECT, el intérprete nos muestra las distintas filas de resultados para que podamos verlas. Sin embargo, dentro de un lenguaje de programación tenemos un problema, ya que lo más común no es mostrar el resultado, sino almacenarlo en variables para su posterior tratamiento.

Ahora tenemos que dividir el problema en dos partes, dependiendo del número de filas que nos retorna la consulta SELECT:

Si retorna cero o una fila: El valor se podrá almacenar en tantas variables como columnas

consultadas. Es decir, si escribimos un SELECT de tres columnas, y sólo retorna una fila

(matriz 1x3), podremos almacenar el valor dentro de tres variables definidas para este uso.

El modo de hacer esto en PL/SQL es:

SELECT col1, col2, col3

INTO var1, var2, var3

FROM TABLA;

De este modo se almacenará en las variables var1, var2 y var3 los valores recuperados por la consulta SELECT o NULL si esta consulta no retorna ninguna fila.

Si retorna más de una fila: En este caso no es posible almacenar directamente los

valores en variables. Para ello existen los cursores, que no son más que consultas

SELECT que se recuperar fila a fila y no todo su conjunto de resultados a la vez.

De las maneras que tiene PL/SQL para tratar el tema de los cursores, el más sencillo y seguro son los cursores implícitos, puesto que al no tener que declararlos son más sencillos, no se tiene que estar pendiente del cierre, del control de las excepciones, que pueden dejar los cursores abiertos, etc.

A continuación se describe la forma de utilizar un cursor implícito en PL/SQL:

FOR <contador> IN <SELECT….> LOOP

Sentencias;

END LOOP;

Ejemplo:

DECLARE

vn_ID_ALUMNO NUMBER(5);

vv_NOMBRE VARCHAR2(30);

BEGIN

FOR I IN (SELECT ID_ALUMNO, NOMBRE FROM ALUMNOS) LOOP

vn_ID_ALUMNO := i.ID_ALUMNO;

vv_NOMBRE :=i.NOMBRE;

.

 END LOOP

 EXCEPTION

WHEN OTHERS

 END;

Una vez que tenemos escrito un bloque de código, podemos guardarlo en un fichero .SQL para su posterior uso, o bien guardarlo en base de datos para que pueda ser ejecutado por cualquier aplicación.

A la hora de guardar un bloque de código hay que tener en cuenta ciertas normas:

1.- Palabra reservada DECLARE desaparece

2.- Podremos crear procedimientos y funciones. Los procedimientos no podrán retornar ningún valor, mientras que las funciones deben retornar un valor de un tipo de dato básico.

Para crear un procedimiento (stored procedure: procedimiento almacenado) usaremos la siguiente sintaxis:

CREATE {OR REPLACE} PROCEDURE nombre_proc( tipo_dato param1, tipo dato_param2... ) IS

<Sección DECLARE>

BEGIN

{EXCEPTION}

END;

Para crear una función usaremos la siguiente sintaxis:

CREATE {OR REPLACE} FUNCTION nombre_func( tipo_dato param1, tipo dato_param2... ) RETURN tipo_dato IS

<Sección DECLARE>

BEGIN

{EXCEPTION}

END;

Una vez que tenemos creados nuestros procedimientos y funciones podemos almacenarlos dentro de librerías de funciones. Estas librerías tienen el nombre de paquetes o packages.

Un paquete puede contener procedimientos, funciones, variables, tipos y subtipos, en general, cualquier objeto que se pueda declarar dentro de la sección DECLARE de un bloque de código.

La creación de un paquete pasa por dos fases:

1.- Crear la cabecera del paquete donde se definen que procedimientos, funciones, variables, etc. Contendrá el paquete.

2.- Crear el cuerpo del paquete, donde se definen los bloques de código de las funciones y procedimientos definidos en la cabecera del paquete.

Para crear la cabecera del paquete utilizaremos la siguiente instrucción:

CREATE {OR REPLACE} PACKAGE nombre_de_paquete IS < declaraciones > END;

Para crear el cuerpo del paquete utilizaremos la siguiente instrucción:

CREATE {OR REPLACE} PACKAGE BODY nombre_paquete IS

< Bloques de código> END;

Hay que tener en cuenta que toda declaración de función o procedimiento debe estar dentro del cuerpo del paquete, y que todo bloque de código contenido dentro del cuerpo debe estar declarado dentro de la cabecera de paquete.

Cuando se quiera acceder a las funciones, procedimientos y variables de un paquete se debe anteponer el nombre de este:

Nombre_paquete.función(x) Nombre_paquete.procedimiento(x) Nombre_paquete.variable

Oracle define los siguientes paquetes de funciones predefinidos:

DBMS_ALERT

DBMS_APPLICATION_INFO

DBMS_AQ

DBMS_AQADM

DBMS_DDL

DBMS_DEFER

DBMS_DEFER_QUERY

DBMS_DEFER_SYS

DBMS_DESCRIBE

DBMS_JOB

DBMS_LOB

DBMS_LOCK

DBMS_OUTPUT

DBMS_PIPE

DBMS_REFRESH

DBMS_REPCAT

DBMS_REPCAT_ADMIN

DBMS_REPCAT_AUTH

DBMS_ROWID

DBMS_SESSION

DBMS_SHARED_POOL

DBMS_SNAPSHOT

DBMS_SQL

DBMS_UTILITY UTL_FILE

Para más información sobre PL/SQL consultar el Oracle PL/SQL User’s Guide and Reference

Los disparadores (o triggers) son bloques de código almacenados en base de datos y que se ejecutan automáticamente. Un disparador está asociado a una tabla y a una operación DML específica (INSERT, UPDATE o DELETE). En definitiva, los disparadores son eventos a nivel de tabla que se ejecutan automáticamente cuando se realizan ciertas operaciones sobre la tabla.

Para crear un disparador utilizaremos la siguiente instrucción:

CREATE {OR REPLACE} TRIGGER nombre_disp

[BEFORE|AFTER|INSTEAD OF] [DELETE|INSERT|UPDATE {OF columnas}] ON tabla

{DECLARE}

BEGIN

{EXCEPTION}

END;

Para más información sobre los disparadores consultar el Oracle SQL Reference

Los Jobs, son la forma que tiene Oracle de planificar trabajos. Están almacenados en la base de datos y se ejecutan automáticamente. Un Job es un objeto independiente, y puede ejecutar tanto una función o procedimiento almacenado como una sentencia SQL o un Bloque de PL/SQL.

Para crear un Job utilizaremos la siguiente instrucción:

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

( job => X

,what => '<Sentancia(s) a Ejecutar>'

,next_date => TO_DATE('<Fecha y hora de la siguiente ejecución>','dd/mm/yyyy hh24:mi:ss')

,INTERVAL => '<Intervalo de tiempo>'

,no_parse => TRUE

,INSTANCE => 1

,FORCE => TRUE

);

SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));

END;

/

COMMIT;

Oracle cuenta con una serie de tablas y vistas que conforman una estructura denominada catálogo. La principal función del catálogo de Oracle es almacenar toda la información de la estructura lógica y física de la base de datos, desde los objetos existentes, la situación de los datafiles, la configuración de los usuarios, etc.

El catálogo sigue un estándar de nomenclatura para que su memorización sea más fácil:

Prefijos:

Existe una tabla de catálogo para cada tipo de objeto posible. Su nombre aparecerá en plural

TABLES, VIEWS, SEQUENCES, TABLESPACES…

Sabiendo qué objetos existen, y qué prefijos podemos utilizar, ya podemos acceder a los objetos del catálogo de Oracle.

Ejemplos:

Los objetos de catálogo también guardan relaciones entre ellos. Por ejemplo, el objeto ALL_TABLES guarda una relación 1-N con el objeto ALL_TAB_COLUMNS: Una tabla tiene N columnas.

Existe un pseudo usuario llamado PUBLIC el cual tiene acceso a todas las tablas del catálogo público. Si se quiere que todos los usuarios tengan algún tipo de acceso a un objeto, debe darse ese privilegio a PUBLIC y todo el mundo dispondrá de los permisos correspondientes.

El catálogo público son aquellas tablas (USER_ y ALL_) que son accesibles por todos los usuarios. Normalmente dan información sobre los objetos creados en la base de datos.

El catálogo de sistema (DBA_ y V_$) es accesible sólo desde usuarios DBA y contiene tanto información de objetos en base de datos, como información específica de la base de datos en sí (versión, parámetros, procesos ejecutándose…)

Ciertos datos del catálogo de Oracle están continuamente actualizados, como por ejemplo las columnas de una tabla. Cuando se crea una columna nueva se añade la entrada correspondiente al catálogo. Sin embargo hay otros datos que no pueden actualizarse en tiempo real porque penalizarías mucho el rendimiento general de la base de datos, como por ejemplo el número de registros de una tabla, el tamaño de los objetos, etc. Para actualizar el catálogo de este tipo de datos es necesario ejecutar una sentencia especial que se encarga de volcar la información recopilada al catálogo:

ANALYZE [TABLE|INDEX] nombre

[COMPUTE|ESTIMATE|DELETE] STATISTICS;

La cláusula COMPUTE hace un cálculo exacto de la estadísticas (tarda más en realizarse en ANALYZE), la cláusula ESTIMATE hace una estimación partiendo del anterior valor calculado y de un posible factor de variación y la cláusula DELETE borra las anteriores estadísticas.

El catálogo público contiene ciertas tablas encargadas de almacenar información adicional sobre tablas, vistas y columnas. La información que se suele almacenar es información de análisis, valores posibles para las columnas y en general todo aquello que se haya concluido durante la etapa de análisis.

Las tablas existentes son:

La información se debe almacenar en base de datos según la siguiente sintaxis:

COMMENT ON TABLE [tabla|vista] IS ‘texto’;

COMMENT ON COLUMN [tabla|vista].columna IS ‘texto’;

Una vez que esta información está en base de datos, se puede escribir procedimientos o scripts SQL que muestren la información para sacar informes de documentación de base de datos.

Una de las tareas más importantes de las propias de un desarrollador de bases de datos es la de puesta a punto o tuning. Hay que tener en cuenta que las sentencias SQL pueden llegar a ser muy complejas y conforme el esquema de base de datos va creciendo las sentencias son más complejas y confusas. Por es difícil escribir la sentencia correcta a la primera. Por todo ello después de tener cada uno de los procesos escrito, hay que pasar por una etapa de tuning en la que se revisan todas las sentencias SQL para poder optimizarlas conforme a la experiencia adquirida.

Tanto por cantidad como por complejidad, la mayoría de las optimizaciones deben hacerse sobre sentencias SELECT, ya que son (por regla general) las responsables de la mayor pérdida de tiempos.

A continuación se dan unas normas básicas para escribir sentencias SELECT optimizadas.

Toda consulta SELECT se ejecuta dentro del servidor en varios pasos. Para la misma consulta, pueden existir distintos caminos para conseguir el mismo resultado, por lo que el servidor es el responsable de decidir qué camino seguir para conseguir el mejor tiempo de respuesta. La parte de la base de datos que se encarga de estas decisiones se llama Optimizador. El camino seguido por el servidor para la ejecución de una consulta se denomina "Plan de ejecución" En Oracle existen dos optimizadores para la decisión del plan de ejecución:

1. Optimizador por costes (CHOOSE): se basa en las reglas básicas, pero teniendo en cuenta el estado actual de la base de datos. Es decir, tiene en cuenta el número de registros de las tablas, el número de usuarios accediendo a ellas, etc. Por ejemplo, si se hace una consulta utilizando un campo indexado, mirará primero el número de registros y si es suficientemente grande entonces merecerá la pena acceder por el índice, si no, accederá directamente a la tabla.

Para averiguar el estado actual de la base de datos se basa en los datos del catálogo público, por lo que es recomendable que esté lo más actualizado posible (a través de la sentencia ANALYZE), ya que de no ser así, se pueden tomar decisiones a partir de datos desfasados (la tabla tenía 10 registros hace un mes pero ahora tiene 10.000).

Plan de ejecución

Aunque en la mayoría de los casos no hace falta saber cómo ejecuta Oracle las consultas, existe una sentencia especial que nos permite ver esta información.

Básicamente se trata en rellenar una tabla especial (llamada PLAN_TABLE) con los un registro para cada paso en el plan de ejecución.

La tabla PLAN_TABLE debe tener la siguiente estructura (en Oracle)

CREATE TABLE PLAN_TABLE (

STATEMENT_ID VARCHAR2 (30),

TIMESTAMP DATE,

REMARKS VARCHAR2 (80),

OPERATION VARCHAR2 (30),

OPTIONS VARCHAR2(30),

OBJECT_NODE VARCHAR2(128),

OBJECT_OWNER VARCHAR2 (30),

OBJECT_NAME VARCHAR2 (30),

OBJECT_INSTANCE INTEGER,

OBJECT_TYPE VARCHAR2 (30),

OPTIMIZER VARCHAR2 (255),

SEARCH_COLUMNS INTEGER,

ID INTEGER,

PARENT_ID INTEGER,

POSITION INTEGER,

COST INTEGER,

CARDINALITY INTEGER,

BYTES INTEGER,

OTHER_TAG VARCHAR2 (255),

OTHER LONG);

Una vez que la tabla está creada en el usuario donde vamos a ejecutar la consulta, de debe ejecutar la siguiente sentencia:

EXPLAIN PLAN

SET STATEMENT_ID = ‘identificador de sentencia’

FOR <consulta SELECT a evaluar>;

El identificador de sentencia tiene que ser una cadena descriptiva para nuestra sentencia. Se utilizará más tarde para recuperar el plan entre todos los almacenados dentro de la consulta SELECT.

Esta sentencia lo que hará es almacenar en la tabla PLAN_TABLE un registro por cada paso en el plan de ejecución. El campo STATEMENT_ID de los pasos de nuestro plan de ejecución estará al valor indicado en ‘identificador de sentencia’.

Para mostrar el plan de ejecución se debe hacer un SELECT filtrando aquellos registros de nuestro plan de ejecución. Una sentencia típica que nos muestra el plan de ejecución formateado podría ser:

SELECT ID, parent_id,

LPAD (' ', 2 * (LEVEL - 1))

|| operation

|| ' '

|| options

|| ' '

|| object_name

|| ' '

|| DECODE(ID,0,'Cost = '|| POSITION) "Plan de consulta"

FROM plan_table

START WITH ID = 0 AND STATEMENT_ID = 'identificador de sentencia'

CONNECT BY PRIOR ID = parent_id

AND STATEMENT_ID = 'identificador de sentencia';

El modo de activar las trazas de ejecución es a través de SQL*Plus, con la instrucción Set auto trace ON/OFF

Una vez activada la auto-traza, toda sentencia ejecutada en SQL*Plus vendrá acompañada de su plan y estadísticas de ejecución.

Con la instrucción:

SET TIMING ON

Se activará el reloj interno de Oracle con el que se podrá cronometrar el tiempo de ejecución de cada consulta.

• Al crear un Clave primaria ya crea un índice único automáticamente.
• Al crear un Clave foránea no lo crea.
• Si no tenemos índice, al hacer un delete en la tabla padre o un update de su PK, como la FK tiene que mantener la integridad referencial, se recorrerá la tabla, por lo que es recomendable que lo haga por índice.
• Si tenemos índice, los bloqueos los hará a nivel de registro y no de tabla.

Por Ejemplo en el siguiente esquema:

Si NO existieran los índices, en el momento que un usuario borrara un alumno o una asignatura, pararía lo siguiente:

Elaborado por:

37 Años de edad.

Trabajo elaborado el 10 de Febrero de 2008.