• Author / Uploaded
• Sheldon Cooper

Citation preview

3 TEMA 3. LENGUAJES DEFINICION DE DATOS

3

DE

CONSULTA Y

TEMA 3. LENGUAJES DE CONSULTA Y DEFINICION DE DATOS .............. 1 3.1 Introducción...................................................................................................... 3 3.2 Estructuras lógicas de datos.............................................................................. 4 3.2.1 Tablas ....................................................................................................... 4 3.2.2 Columnas .................................................................................................. 4 3.2.3 Filas o registros......................................................................................... 4 3.2.4 Valor ......................................................................................................... 4 3.2.5 Vistas ........................................................................................................ 4 3.2.6 Indices....................................................................................................... 5 3.2.7 Clusters ..................................................................................................... 5 3.3 Expresiones....................................................................................................... 5 3.4 Tipos de datos................................................................................................... 6 3.4.1 CHAR y VARCHAR ............................................................................... 6 3.4.2 LONG ....................................................................................................... 6 3.4.3 NUMBER ................................................................................................. 7 3.4.4 DATE ....................................................................................................... 7 3.4.5 RAW y LONG RAW ............................................................................... 7 3.4.6 NULL ....................................................................................................... 7 3.5 Operadores y Funciones ................................................................................... 7 3.5.1 Operadores................................................................................................ 8 3.5.2 Funciones.................................................................................................. 9 3.6 Sentencias SQL ................................................................................................ 9 3.6.1 /* … */ COMENTARIO .......................................................................... 9 3.6.2 CREATE TABLE (DDL)....................................................................... 10 3.6.3 CREATE VIEW (DDL) ......................................................................... 10 3.6.4 CREATE INDEX (DDL) ....................................................................... 10 3.6.5 DROP object (DDL)............................................................................... 11 3.6.6 DESCRIBE (DDL) ................................................................................. 11 3.6.7 ALTER TABLE (DDL) ......................................................................... 11 3.6.8 RENAME (DDL) ................................................................................... 12 3.6.9 SELECT (DML) ..................................................................................... 12 3.6.10 INSERT (DML)...................................................................................... 13 3.6.11 DELETE (DML) .................................................................................... 13 3.6.12 UPDATE (DML).................................................................................... 14 3.6.13 LOCK TABLE (DML)........................................................................... 14 3.6.14 COMMIT (DML) ................................................................................... 14 3.6.15 ROLLBACK (DML) .............................................................................. 15 3.6.16 SAVEPOINT (DML) ............................................................................. 15 3.6.17 GRANT (DDL) ...................................................................................... 16 3.6.18 REVOKE (DDL) .................................................................................... 16 3.6.19 CONSTRAINT CLAUSE ...................................................................... 17 3.7 PL/SQL........................................................................................................... 19

Tema 3: Lenguajes de consulta y definición de datos

1

3.7.1 3.7.2 3.7.3 3.7.4 3.7.5 3.7.6 3.7.7 3.7.8 3.7.9 3.7.10 3.7.11 3.7.12 3.7.13 3.7.14

Estructura de un programa en PL/SQL .................................................. 20 Variables y constantes ............................................................................ 21 Cursores .................................................................................................. 21 %TYPE, %ROWTYPE .......................................................................... 22 Estructuras de control ............................................................................. 22 Gestión de errores................................................................................... 24 Delimitadores ......................................................................................... 24 Tipos de datos......................................................................................... 25 Operadores de comparación ................................................................... 25 Funciones................................................................................................ 26 Control de transacciones......................................................................... 27 Trabajar con Cursores............................................................................. 28 Subprogramas (Procedimientos y Funciones) ........................................ 31 Programas de ejemplo ............................................................................ 33

Tema 3: Lenguajes de consulta y definición de datos

2

3.1 Introducción El SQL es un lenguaje que permite expresar operaciones diversas, por ejemplo aritméticas, combinatorias y lógicas, con datos almacenados en Bases de Datos Relacionales, que son aquellas que se caracterizan porque la información está contenida en estructuras, llamadas tablas, donde los datos están dispuestos en filas y columnas. SQL significa Structured Query Language (Lenguaje Estructurado de Consultas). El concepto de Base de Datos Relacional arranca de un artículo publicado en 1970 por Codd, empleado de IBM, donde se sentaban los conceptos básicos de un modelo relacional de datos y de un sublenguaje para acceder a ellos basado en el cálculo de predicados. La idea se desarrolló en IBM, dando lugar a un primer prototipo llamado System R que utilizaba un lenguaje llamado SEQUEL (que posteriormente daría lugar al SQL). El ANSI (American National Standards Institute) ha adoptado este lenguaje como estándar, publicando y desarrollando unas especificaciones para este lenguaje, que has sido posteriormente aceptadas por ISO (International Standards Organization). No significa esto que los productos existentes sigan estrictamente esta norma, principalmente porque el estándar no cubre todas las necesidades planteadas. Del mismo modo, existen diferencias entre distintos productos comerciales. Las peticiones de datos se expresan en SQL mediante sentencias que deben seguir las normas sintácticas y semánticas del lenguaje. Estas sentencias se pueden escribir directamente en la pantalla de un terminal interactivo, o pueden ser utilizadas embebidas en programas, incorporándose así su capacidad expresiva a la lógica y funciones de éstos. A ésta última forma de utilizar el SQL se le llama SQL dinámico o embebido. El SQL permite la realización de consultas y actualizaciones sobre datos almacenados en tablas relacionales. Este es el principal uso que harán de él usuarios y programadores. Pero también hay otras tareas que se pueden realizar mediante sentencias SQL, aunque pertenecen más a las responsabilidades de los administradores de las bases de datos (DBA). Entre estas funciones adicionales se encuentran la definición y destrucción de objetos, y la gestión de autorizaciones de acceso. Existen dos tipos de sentencias SQL: •

Sentencias de manipulación de datos (Data Manipulation Language). Permiten realizar consultas y mantenimiento de los datos. Comienzan con las siguientes palabras del lenguaje: SELECT, INSERT, UPDATE y DELETE.

•

Sentencias de definición de datos (Data Definition Language). Permiten definir nuevos objetos y/o destruir otros existentes. Algunos ejemplos de sentencias son las de tipo CREATE y DROP.

Vamos a realizar una descripción detallada del lenguaje SQL, y de aquí en adelante consideraremos el SGBD ORACLE como el sistema relacional base para nuestros ejemplos. Esto implica que no todas las sentencias SQL serán estándar, pero puesto que ORACLE es la herramienta base en las prácticas de la asignatura, se ilustrará con mayor Tema 3: Lenguajes de consulta y definición de datos

3

claridad la funcionalidad del lenguaje y dará la posibilidad de explotar de forma más eficiente el sistema relacional empleado en las prácticas.

3.2 Estructuras lógicas de datos. 3.2.1 Tablas Una tabla es la estructura de datos que contiene los datos en una base de datos relacional. Una tabla se compone de filas y columnas. Una tabla puede representar una única entidad que se desee representar en el sistema. También puede representar una relación entre dos entidades. Aunque es posible que una tabla represente al mismo tiempo una entidad y una relación con otra entidad, se debe tratar de separar esta funcionalidad en la medida de lo posible. El nombre formal para una tabla es una relación.

3.2.2 Columnas Cada columna de una tabla representa un (y sólo un) atributo de la entidad. El nombre de la columna debe indicar su naturaleza, en la medida de lo posible. Una columna se identifica por su nombre, no por su posición. El orden interno de las columnas de una tabla carece de importancia. De hecho, jamás se conocerá el orden físico de las columnas. Se puede especificar el orden en el que mostrar las columnas tras una selección, y por supuesto, esto no afectará al orden interno de las columnas en la tabla.

3.2.3 Filas o registros Los registros almacenan los datos de una tabla. Cada fila o registro representa una ocurrencia de la entidad o relación representada en la tabla. Los registros no se deben duplicar en una tabla, y existen mecanismos de seguridad que pueden garantizar esta premisa (claves primarias). El orden interno de las filas dentro de una tabla carece de importancia. De hecho, jamás se conocerá el orden físico de las filas. Es más, no es necesario insertar filas en un determinado orden; sólo es necesario preocuparse por el orden de recuperación de las mismas tras una consulta.

3.2.4 Valor Un valor es el dato referenciado por la intersección de una fila y una columna determinadas. Los valores pertenecen al tipo de datos al que pertenece la columna, y pueden carecer de valor (NULL).

3.2.5 Vistas Una vista es la representación lógica de otra tabla o combinación de tablas. Una vista obtiene sus datos de las tablas en las que se basa, que se llaman tablas base. Estas tablas base pueden ser tablas reales u otras vistas.

Tema 3: Lenguajes de consulta y definición de datos

4

Las vistas se pueden utilizar casi de igual forma que las tablas. Sin embargo, las vistas no contienen datos, sino que, como ya hemos dicho, los obtienen de las tablas base. Por tanto, todas las operaciones realizadas sobre las vistas afectan realmente a las tablas base. Las vistas se utilizan para proporcionar diferentes representaciones de los datos que residen en otras tablas o vistas. En general, las vistas se utilizan también con los siguientes propósitos: • • • • •

Proporcionar un nivel adicional de seguridad para las tablas a través de la restricción a cierto numero de registros y atributos. Ocultar la complejidad de los datos. Reducir la complejidad sintáctica. Presentar los datos desde otra perspectiva. Proporcionar un nivel de integridad referencial.

3.2.6 Indices Los índices se utilizan principalmente con dos propósitos: • •

Permitir un acceso rápido a las filas de una tabla Forzar la unicidad de filas en una tabla

Los índices dan un acceso más rápido a los datos para operaciones que devuelven una pequeña porción de las filas de una tabla. Una regla importante para determinar cuando es interesante indexar un atributo de una tabla: Las consultas SQL que devuelven menos del 15% de las filas de una tabla pueden ser realizadas más rápidamente cuando se utilizan índices. Los índices únicos también contribuyen a garantizar la no duplicidad de filas en una tabla. Como regla general, siempre se debería crear un índice sobre la clave primaria de una tabla.

3.2.7 Clusters El clustering es un medio de estructurar datos en una o más tablas, de forma que las filas están físicamente más juntas. El clustering es beneficioso si una aplicación selecciona frecuentemente el mismo grupo de filas de una tabla o tablas. El clustering puede mejorar el rendimiento de algunas operaciones; sin embargo, también puede empeorar el de otras. Es conveniente asegurarse de las condiciones de trabajo antes de decidir si se aplica o no clustering, y donde debe aplicarse

3.3 Expresiones Por su importancia en los siguientes apartados, vamos a definir con cierta rigurosidad el concepto de expresión. Su importancia es tal que una expresión forma parte de las funciones SQL que más se utilizan en el trabajo cotidiano. Existen varias formas de identificar una expresión: Tema 3: Lenguajes de consulta y definición de datos

5

[table.] { column | ROWID } text number sequence.CURRVAL sequence.NEXTVAL NULL ROWNUM LEVEL SYSDATE UID USER : { n | variable } [ :ind_variable ] function_name ( [DISTINCT | ALL] expr [, expr] … ) (expr) +expr, -expr, PRIOR expr expr * expr, expr / expr expr + expr, expr – expr, expr || expr (expr [, expr] … )

Las expresiones se usan en: • • • • •

Como lista de valores en la sentencia SELECT Como condición en las cláusulas WHERE y HAVING En la cláusula ORDER BY En la cláusula VALUE del comando INSERT En la cláusula SET del comando UPDATE

3.4 Tipos de datos 3.4.1 CHAR y VARCHAR Los tipos CHAR y VARCHAR se usan para definir cadenas de texto genéricas, y puede contener caracteres con cualquier valor ASCII. El número máximo de caracteres que admiten es 255. La diferencia entre ambos es que mientras CHAR proporciona un tamaño fijo para una cadena, VARCHAR admite una cantidad variable de caracteres (Nota: al trabajar con SQL embebido esta diferencia es importante a la hora de especificar condiciones en una sentencia). La sintaxis para este tipo es CHAR[(n)] o VARCHAR[(n)]. Sus contenidos se especifican entre comillas simples. Ejemplo: ‘Hola’, ‘09-MAR-98’, ‘Jackie’’s’.

3.4.2 LONG Es un tipo de datos que se utiliza para almacenar cadenas de hasta 65,535 caracteres. Funciona exactamente igual que el VARCHAR, pero no se puede utilizar en las cláusulas WHERE, GROUP BY, ORDER BY, CONNECT BY y DISTINCT, ni como índices ni dentro de funciones.

Tema 3: Lenguajes de consulta y definición de datos

6

3.4.3 NUMBER Se utiliza para almacenar números con una precisión máxima de 38 dígitos (lo que incluye números desde 1.0E-129 hasta 9.99E124), y una escala que va desde –84 hasta 127. La sintaxis para este tipo es NUMBER[(precisión [, escala])]. Algunos ejemplos: 7,456,123.89 7,456,123.89 7,456,123.89 7,456,123.89 7,456,123.89

NUMBER NUMBER(9) NUMBER(9,1) NUMBER(9,2) NUMBER(9,-2)

7,456,123.89 7,456,123 7,456,123.9 7,456,123.89 7,456,100

Otros tipos de datos válidos que se incluyen por compatibilidad son DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, y DOUBLE_PRECISION. Algunos ejemplos de datos numéricos: 7, 255, 29K, 6M, 3.56, 7E5, 3e-2.

3.4.4 DATE Se usa para almacenar información de fechas y horas. Almacena información de siglo, año, mes, día, hora, minuto y segundo. Por defecto se toma la hora 00:00:00, y el formato de fecha por defecto es DD-MON-YY. Existen funciones TO_DATE y TO_CHAR para convertir entre distintos formatos. Por ejemplo, TO_DATE(‘13-NOV-85 10:56 A.M.’, ‘DD-MON-YY HH:MI A.M.’)

3.4.5 RAW y LONG RAW Se utilizan para tipos de datos binarios, es decir, con caracteres que no pertenecen todos al código ASCII. RAW tiene un máximo de contenido de 255, y LONG RAW hasta 65,535. Funcionan igual que el VARCHAR y LONG VARCHAR. Hay que introducir los datos en formato hexadecimal.

3.4.6 NULL No se trata de un tipo de datos concreto. Es más bien un valor que indica la ausencia de cualquier valor para un determinado literal (o columna). Para comparar valores con NULL sólo se pueden usar los operadores IS NULL o IS NOT NULL.

3.5 Operadores y Funciones Existen en ORACLE muchos operadores y funciones. Nosotros sólo vamos a tratar las más corrientes y que más uso pueden tener dentro del uso habitual del sistema (para más información se puede consultar el SQL Language Reference Manual).

Tema 3: Lenguajes de consulta y definición de datos

7

3.5.1 Operadores 3.5.1.1 Operadores aritméticos () +*/ +-

Operador de precedencia Denota un número positivo o negativo Multiplicar o dividir Sumar o restar

3.5.1.2 Operadores de comparación = !=, ^=, >, < >=, , >=, =, ; :=

SIGNIFICADO Operador suma Indicador de atributo Delimitador de cadenas o caracteres Delimitador de componentes Operador división Delimitador de expresión Delimitador de expresión Indicador de variable huésped Separador de items Operador multiplicación Delimitador de identificadores Operador comparador igual que Operador comparador menor que Operador comparador mayor que Final de sentencia Operador resta Operador asignación

Tema 3: Lenguajes de consulta y definición de datos

24

|| ** /* */ .. !=

= --

Operador concatenación de cadenas Operador elevado Inicio de comentario Fin de comentario Operador de rango Operador de comparación distinto Operador de comparación distinto Operador de comparación menor o igual Operador de comparación mayor o igual Comentario de una línea

3.7.8 Tipos de datos Los tipos de datos que se pueden utilizar en un programa PL/SQL son los siguientes:

Es posible también definir subtipos utilizando la siguiente sentencia: SUBTYPE subtype_name IS base_type [NOT NULL]; pero siempre se tratará de tipos definidos en base a tipos predefinidos o subtipos definidos por el usuario.

3.7.9 Operadores de comparación Además de los operadores que hemos visto como delimitadores o símbolos que utiliza PL/SQL como operadores, existen una serie de operadores para realizar operaciones no

Tema 3: Lenguajes de consulta y definición de datos

25

numéricas, pero que tienen perfecto sentido dentro del ámbito de trabajo en el que estamos. A continuación se presentan los principales operadores adicionales: IS NULL variable. Operador que devuelve un valor booleano verdadero o falso según la variable sea nula o no nula. variable LIKE valor. Operador que devuelve un valor booleano verdadero o falso según la cadena almacenada en variable sea igual al dato valor. No se debe confundir este operador con el de igualdad, que aunque se puede aplicar a cadenas, no permite el uso de caracteres universales (%, _). variable BETWEEN valor1 AND valor2. Operador que devuelve un valor booleano verdadero o falso según la variable esté o no comprendida entre los valores valor1 y valor2 variable IN conjunto. Operador que devuelve un valor booleano según el valor de la variable se encuentre dentro de un conjunto de valores.

3.7.10 Funciones PL/SQL permite la utilización de funciones predefinidas para trabajar con varios tipos de datos: números, caracteres, fechas, referencias de objetos, etc. Además existe un conjunto de funciones de propósito general y otro conjunto de funciones que permiten realizar conversiones entre tipos de datos. Se presenta a continuación una lista que muestra un esquema de las funciones predefinidas del ámbito de aplicación de cada una de ellas: Funciones sobre errores: SQLCODE, SQLERRM. Funciones sobre números: ABS, ACOS, ASIN, ATAN, ATAN2, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, MOD, POWER, ROUND, SIGN, SIN, SINH, SQRT, TAN, TANH, TRUNC. Funciones sobre caracteres: ASCII, CHR, CONCAT, INITCAP, INSTR, INSTRB, LENGTH, LENGTHB, LOWER, LPAD, LTRIM, NLS_INITCAP, NLS_LOWER, NLSSORT, NLS_UPPER, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, SUBSTRB, TRANSLATE, TRIM, UPPER. Funciones de conversión: CHARTOROWID, CONVERT, HEXTORAW, RAWTOHEX,

ROWIDTOCHAR, TO_CHAR, TO_DATE, TO_MULTI_BYTE, TO_NUMBER, TO_SINGLE_BYTE.

Funciones de fecha: ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, ROUND, SYSDATE, TRUNC.

Funciones de referencia de objetos: DEREF, REF, VALUE Funciones de propósito general: BFILENAME, DECODE, DUMP, EMPTY_BLOB,

EMPTY_CLOB, GREATEST, LEAST, NLS_CHARSET_DECL_LEN, NLS_CHARSET_ID, NLS_CHARSET_NAME, NVL, SYS_CONTEXT, SYS_GUID, UID, USER, USERENV, VSIZE.

Tema 3: Lenguajes de consulta y definición de datos

26

3.7.11 Control de transacciones El control de transacciones consiste en una serie de técnicas que permiten salvaguardar la consistencia de la base de datos, incluyendo la manera de controlar cuando los cambios realizados en una base de datos deben ser permanentes o deben desecharse. Una transacción consiste en una serie de sentencias DML de SQL que componen una unidad lógica. Normalmente, las bases de datos tratan cada una de las sentencias SQL como unidades lógicas, pero es necesario que las bases de datos proporcionen mecanismos para poder agrupar conjuntos de sentencias como unidades lógicas. Las sentencias que se pueden utilizar para trabajar con estas agrupaciones lógicas o transacciones son las siguientes: COMMIT. Valida los cambios realizados en la base de datos por la transacción actual. ROLLBACK. Deshace todos los cambios realizados en la base de datos por la transacción actual. SAVEPOINT. Sitúa puntos de control dentro de la transacción y permite deshacer cambios en la base de datos de forma parcial por la transacción. SET TRANSACTION. Permite configurar las propiedades de la transacción, como lectura/escritura y nivel de aislamiento. Una transacción, pues, está definida como el conjunto de sentencias entre un COMMIT y el siguiente COMMIT. COMMIT se debe utilizar para validar los cambios realizados en una base de datos, siempre y cuando la consistencia de la base de datos esté asegurada. ROLLBACK se debe utilizar para recuperar el estado de la base de datos al comienzo de la transacción si alguna de las sentencias de la transacción ha fallado o ha generado resultados no deseados. SAVEPOINT se debe utilizar para marcar puntos de control que ofrezcan estados consistentes de la base de datos, y poder volver a ellos cuando sea necesario con la sentencia ROLLBACK TO SAVEPOINT sp. Por último, SET TRANSACTION se debe utilizar para configurar ciertos parámetros de las transacciones para determinar niveles de aislamiento y modo de trabajo lectura/escritura. El comportamiento que cabe esperar de la base de datos ante las transacciones lo definiremos a continuación. En primer lugar, cuando falla una sentencia SQL simple, la base de datos hará un rollback de la sentencia exclusivamente. Si por algún motivo el programa terminase debido a una excepción no tratada, la base de datos no hará un rollback de forma implícita. Por consiguiente, habrá que tener especial cuidado en la gestión de las excepciones. Cuando se realiza un rollback hasta un savepoint determinado, todos los savepoints definidos con posterioridad son borrados y no es posible volver a acceder a ellos. Si al finalizar un programa PL/SQL no se especifica que acción realizar sobre la transacción (ROLLBACK o COMMIT), el resultado de la transacción dependerá de lo que realicemos con posterioridad a la finalización de la transacción. Es por ello que una buena práctica en la creación de programas PL/SQL consiste en la definición explícita de los COMMIT y ROLLBACK en aquellos puntos que sean necesarios, sobre todo en los puntos de finalización de la transacción (final del bloque, tratamiento de excepciones).

Tema 3: Lenguajes de consulta y definición de datos

27

3.7.12 Trabajar con Cursores PL/SQL utiliza dos tipos de cursores: implícitos y explícitos. PL/SQL declara un cursor implícitamente para todas las sentencias DML de SQL, incluyendo consultas que devuelven sólo un registro. Sin embargo, para consultas que devuelven más de un registro, es necesario definir un cursor explícito o utilizar un bucle FOR para un cursor. Cuando se trabaja con cursores explícitos, existen tres sentencias para controlar los cursores: OPEN, FETCH y CLOSE. En primer lugar, y una vez declarado el cursor, es necesario inicializarlo con la sentencia OPEN, con lo que se identifica ya el conjunto de resultados. Posteriormente, se utiliza la sentencia FETCH para recuperar el primer registro del resultado. Se puede ejecutar de forma repetitiva la sentencia FETCH para ir pasando por el resto de registros que componen el conjunto de resultados, hasta que se termina de completar un recorrido por todos los registros encontrados. Y una vez se han procesado todos los registros, hay que liberar el cursor con la sentencia CLOSE. La declaración de un cursor se hace siguiendo la sintaxis: CURSOR cursor_name [(parameter[, parameter]...)] [RETURN return_type] IS select_statement;

donde el return_type debe representar un registro de una base de datos, y los parámetros son tipos de datos válidos SQL. Algunos ejemplos de declaraciones de cursores válidas: DECLARE

CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10;

Un cursor puede aceptar parámetros, que pueden aparecer en la consulta asociada del mismo modo que pueden aparecer constantes. Dicho de otro modo, es una forma de pasar variables a las consultas SQL que ejecutan los cursores. Los parámetros sólo pueden ser de entrada, se especifican con la palabra IN, y no sirven para devolver resultados de la ejecución de una sentencia. La sintaxis de los parámetros es: cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]

y un ejemplo de definición de cursores con parámetros sería: DECLARE

CURSOR c1 (low INTEGER DEFAULT 0, high INTEGER DEFAULT 99) IS SELECT ...

Cuando se abre un cursor (OPEN) se ejecuta la consulta y se identifica el conjunto de resultados. Para los cursores declarados con la cláusula FOR UPDATE, además se bloquean los registros recuperados. Un ejemplo de uso de OPEN es: DECLARE BEGIN END;

CURSOR c1 IS SELECT ename, job FROM emp WHERE sal < 3000; ... OPEN c1; ...

Tema 3: Lenguajes de consulta y definición de datos

28

Si se desea pasar parámetros a un cursor, el momento de hacerlo es al utilizar la sentencia OPEN, tal y como se muestra en este ejemplo: DECLARE

BEGIN

emp_name emp.ename%TYPE; salary emp.sal%TYPE; CURSOR c1 (name VARCHAR2, salary NUMBER) IS SELECT ... ... ... OPEN c1(emp_name, 3000); OPEN c1('ATTLEY', 1500); OPEN c1(emp_name, salary);

La sentencia FETCH sigue la siguiente estructura: FETCH c1 INTO variable_1,variable_2,...,variable_n;

La sentencia FETCH, como ya hemos mencionado, debe ser utilizada dentro de un bucle. Un ejemplo de uso de FETCH es el siguiente: LOOP

FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; -- procesamiento del registro END LOOP;

La sintaxis de cierre de cursor (CLOSE) sigue la siguiente estructura: CLOSE cursor_name;

Los cursores implícitos se abren para procesar cualquier sentencia SQL no asociada con un cursor explícito. Con los cursores implícitos no se pueden utilizar las sentencias OPEN, FETCH y CLOSE. Los cursores (variables) tienen 4 atributos que se pueden utilizar para tener un mayor control sobre el procesamiento de las consultas dentro de los bucles. Dichos parámetros son: %FOUND. Atributo que contiene el valor NULL después de que se abre el cursor, pero antes de realizar el primer FETCH. Después de cada FETCH (incluido el primero), el atributo toma el valor TRUE si se encontró algún registro que satisfaga la consulta definida para el cursor, o FALSE en caso contrario. Ejemplo de uso: LOOP

FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%FOUND THEN -- fetch succeeded ... ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP;

%ISOPEN. Es un atributo que contiene el valor TRUE si el cursor está abierto, y en caso contrario contiene el valor FALSE. Ejemplo de uso: IF c1%ISOPEN THEN

-- cursor is open

Tema 3: Lenguajes de consulta y definición de datos

29

... -- cursor is closed, so open it OPEN c1; END IF; ELSE

%NOTFOUND. Es un parámetro que es lo lógicamente opuesto a %FOUND. Sin embargo, hay que tener cuidado con el uso de este atributo, puesto que si FETCH no se ejecuta nunca de forma satisfactoria (no se encuentra ningún registro) su valor siempre será NULL y no se saldrá nunca del bucle. Un ejemplo de uso es: LOOP

FETCH c1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c1%NOTFOUND; ... END LOOP;

%ROWCOUNT. Es un atributo que, cuando el cursor está abierto, tiene valor 0, y a medida que se van recuperando registros, va incrementando en 1 su valor. Se utiliza para contar los registros recuperados en un bucle con FETCH. Un ejemplo de uso es: LOOP

FETCH c1 INTO my_ename, my_deptno; IF c1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP;

Por último, vamos a ilustrar el uso de los cursores con algunos ejemplos. Ejemplo 1: El programa utiliza un cursor para obtener los valores de tres campos (n1, n2 y n3) de la tabla data_table, y guarda el resultado en tres variables locales del programa. Posteriormente, el programa inserta en otra tabla (temp) una serie de datos que han sido calculados en la información obtenida con el cursor para el procesamiento de la consulta anterior. Obsérvese la finalización del programa con la cláusula COMMIT, que valida todas las inserciones realizadas en la tabla temp de forma conjunta, en lugar de hacer una validación una a una. DECLARE

BEGIN

END;

num1 num2 num3 result CURSOR

data_table.n1%TYPE; -data_table.n2%TYPE; -data_table.n3%TYPE; -temp.col1%TYPE; c1 IS SELECT n1, n2, n3 FROM

Declare variables having same types as database columns data_table WHERE exper_num = 1;

OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT;

Tema 3: Lenguajes de consulta y definición de datos

30

Ejemplo 2: Este programa comprueba todos los contenedores que contienen el item número 5469, retirando sus contenidos hasta acumular un total de 1000 unidades. DECLARE

BEGIN

order

END;

CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin FROM bins WHERE part_num = part_number AND amt_in_bin > 0 ORDER BY bin_num FOR UPDATE OF amt_in_bin; bin_amt bins.amt_in_bin%TYPE; total_so_far NUMBER(5) := 0; amount_needed CONSTANT NUMBER(5) := 1000; bins_looked_at NUMBER(3) := 0; OPEN bin_cur(5469); WHILE total_so_far < amount_needed LOOP FETCH bin_cur INTO bin_amt; EXIT WHEN bin_cur%NOTFOUND; -- if we exit, there's not enough to fill the bins_looked_at := bins_looked_at + 1; IF total_so_far + bin_amt < amount_needed THEN UPDATE bins SET amt_in_bin = 0 WHERE CURRENT OF bin_cur; -- take everything in the bin total_so_far := total_so_far + bin_amt; ELSE -- we finally have enough UPDATE bins SET amt_in_bin = amt_in_bin - (amount_needed - total_so_far) WHERE CURRENT OF bin_cur; total_so_far := amount_needed; END IF; END LOOP; CLOSE bin_cur; INSERT INTO temp VALUES (NULL, bins_looked_at, '