• Author / Uploaded
• Rodrigo Oviedo

• Categories
• Tabla (base de datos)
• Pl / Sql
• SQL
• Ingeniería de software
• Datos

Citation preview

FEBRERO - 2009 CURSO BASE DE DATOS

PL / SQL

Resumen PL/SQL II Resumen PL/SQL II

Curso Base de Datos Página 1

Sentencia SELECT en PL/SQL PL/SQL admite el uso de un SELECT que permite almacenar valores en variables. Es el llamado SELECT INTO. Su sintaxis es: SELECT

INTO

FROM

La cláusula INTO es obligatoria en PL/SQL y además la expresión SELECT sólo puede devolver una única fila, de otro modo, ocurre un error.

Resumen PL/SQL II

Página 2

Sentencias DML y de transición Se pueden utilizar instrucciones DML dentro del código ejecutable. Se permiten las instrucciones INSERT, UPDATE y DELETE, con la ventaja de que en PL/SQL pueden utilizar variables. Las instrucciones de transacción ROLLBACK y COMMIT también están permitidas para anular o confirmar instrucciones.

Resumen PL/SQL II

Página 3

Sentencia de control - IF Toma de decisiones. Permite especificar que ciertas instrucciones se ejecuten o no dependiendo de una condición IF simple Sintaxis: IF THEN

END IF; Las instrucciones se ejecutan en el caso de que la condición sea verdadera. La condición es cualquier expresión que devuelva verdadero o falso.

DECLARE V_STOCK NUMBER; V_CODIGO VARCHAR2(13); BEGIN V_CODIGO := '&V_CODIGO'; SELECT STOCK_ART INTO V_STOCK FROM TARTICULO WHERE CODIGO_ART = V_CODIGO ; IF V_STOCK > 0 THEN DBMS_OUTPUT.PUT_LINE('STOCK '|| V_STOCK ); END IF; END;

IF-THEN-ELSE Sintaxis: IF THEN

ELSE

END IF; En este caso las instrucciones bajo el ELSE se ejecutan si la condición es falsa.

Resumen PL/SQL II

Página 4

DECLARE V_STOCK NUMBER; V_CODIGO VARCHAR2(13); BEGIN V_CODIGO := '&V_CODIGO'; SELECT STOCK_ART INTO V_STOCK FROM TARTICULO WHERE CODIGO_ART = V_CODIGO ; IF V_STOCK > 0 THEN DBMS_OUTPUT.PUT_LINE('STOCK '|| V_STOCK ); ELSE DBMS_OUTPUT.PUT_LINE('No hay existencias de éste artículo' ); END IF; END;

IF-THEN-ELSIF Cuando se utilizan sentencias de control es común desear anidar un IF dentro de otro IF.

IF THEN

ELSIF THEN

ELSIF THEN

ELSE

END IF; DECLARE V_STOCK NUMBER; V_CODIGO VARCHAR2(13); BEGIN V_CODIGO := '&V_CODIGO'; SELECT STOCK_ART INTO V_STOCK FROM TARTICULO WHERE CODIGO_ART = V_CODIGO ; IF V_STOCK > 1000 THEN DBMS_OUTPUT.PUT_LINE('Demasiadas existencias STOCK '|| V_STOCK ); ELSIF V_STOCK > 100 THEN DBMS_OUTPUT.PUT_LINE('Existencias correctas STOCK '|| V_STOCK ); ELSIF V_STOCK > 0 THEN DBMS_OUTPUT.PUT_LINE('Escasas existencias STOCK '|| V_STOCK ); ELSE DBMS_OUTPUT.PUT_LINE('No hay existencias de éste artículo' ); END IF; END;

Resumen PL/SQL II

Página 5

Resumen PL/SQL II

Página 6

Sentencia de control - CASE La sentencia CASE devuelve un resultado tras evaluar una expresión.

CASE WHEN THEN WHEN THEN WHEN THEN ... [ELSE ] END; Hay que tener en cuenta que la sentencia CASE sirve para devolver un valor y no para ejecutar una instrucción.

Resumen PL/SQL II

Página 7

Otra posibilidad de la sentencia CASE, es utilizar en las expresiones, condiciones. Este segundo formato facilita la escritura de sentencias CASE más complicadas.

CASE WHEN THEN WHEN THEN ... [ELSE ] END;

Resumen PL/SQL II

Página 8

Sentencia de control repetitiva - LOOP Se trata de una instrucción que contiene instrucciones que se repiten indefinidamente (bucle infinito). Se inicia con la palabra LOOP y finaliza con la palabra END LOOP y dentro de esas palabras se colocan las instrucciones que se repetirán. Lógicamente no tiene sentido utilizar un bucle infinito, por eso existe una instrucción llamada EXIT que permite abandonar el bucle. Cuando Oracle encuentra esa instrucción, el programa continúa desde la siguiente instrucción al END LOOP. Lo normal es colocar EXIT dentro de una sentencia IF a fin de establecer una condición de salida del bucle. También se puede acompañar a la palabra EXIT de la palabra WHEN seguida de una condición. Si se condición es cierta, se abandona el bucle, sino continuamos dentro. LOOP

... EXIT [ WHEN ] END LOOP;

DECLARE CONT NUMBER ; BEGIN CONT :=101; LOOP DBMS_OUTPUT.PUT_LINE(CONT); CONT:=CONT+1; EXIT WHEN CONT>105; END LOOP; END;

Resumen PL/SQL II

Página 9

Sentencia de control repetitiva - WHILE Genera un bucle cuyas instrucciones se repiten mientras la condición que sigue a la palabra WHILE sea verdadera. WHILE LOOP

... END LOOP;

DECLARE CONT NUMBER ; BEGIN CONT :=101; WHILE CONT< 106 LOOP DBMS_OUTPUT.PUT_LINE(CONT); CONT:=CONT+1; END LOOP; END;

Resumen PL/SQL II

Página 10

Sentencia de control repetitiva - FOR Se utilizar para bucles con contador, bucles que se recorren un número concreto de veces. Para ello se utiliza una variable (contador) que no tiene que estar declarada en el DECLARE, esta variable es declarada automáticamente en el propio FOR y se elimina cuando éste finaliza. Se indica el valor inicial de la variable y el valor final (el incremento irá de uno en uno). Si se utiliza la cláusula REVERSE, entonces el contador cuenta desde el valor alto al bajo restando 1.

FOR IN [REVERSE] . . LOOP

... END LOOP;

BEGIN FOR CONT IN 101 .. 105 LOOP DBMS_OUTPUT.PUT_LINE(CONT); END LOOP; END;

Resumen PL/SQL II

Página 11

Sentencia de control - Bucles anidados Se puede colocar un bucle dentro de otro sin ningún problema, puede haber un WHILE dentro de un FOR, un LOOP dentro de otro LOOP, etc. Hay que tener en cuenta que en ese caso, la sentencia EXIT abandonaría el bucle en el que estamos. No obstante hay una variante de la instrucción EXIT que permite salir incluso del bucle más exterior. Eso se consigue poniendo una etiqueta a los bucles que se deseen. Una etiqueta es un identificador que se coloca dentro de los signos > delante del bucle. Eso permite poner nombre al bucle. No es obligatorio poner la etiqueta en la instrucción END LOOP, pero se suele hacer por dar mayor claridad al código.

BEGIN > FOR CONT_1 IN 101 .. 200 LOOP > FOR CONT_2 IN 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE(CONT_1||' - '|| CONT_2); EXIT BUCLE_1 WHEN CONT_2 = 3; END LOOP BUCLE_2; END LOOP BUCLE_1; END;

Resumen PL/SQL II

Página 12

REGISTROS PL/SQL Los registros de PL/SQL proporcionan un mecanismo para tratar con variables de distintos tipos, pero relacionadas entre sí. Es un tipo de dato compuesto, que debe ser definido por el propio usuario. Antes de poder declarar una variable, debemos definir primero el tipo compuesto.

TYPE IS RECORD ( [NOT NULL] [ := ] , [NOT NULL] [ := ] , ......... [NOT NULL] [ := ] ,);

Donde es el nombre del nuevo tipo definido, a son los nombres de los campos incluidos en el registro, y son los tipos correspondientes a dichos campos. Un registro podrá tener tantos campos como se desee. Cada declaración de un campo dentro de un registro es semejante a la declaración de una variable, pudiendo incluir la restricción NOT NULL y la especificación de un valor inicial, siendo ambas opcionales. DECLARE TYPE REGISTRO_EJEMPLO IS RECORD ( CAMPO1 NUMBER NOT NULL, CAMPO2 VARCHAR2(5), CAMPO3 VARCHAR2(5) := ‘GETAFE’ V_REC1

REGISTRO_EJEMPLO;

V_REC2

REGISTRO_EJEMPLO;

);

Para hacer referencia a uno de los campos de un registro se emplea la notación de punto. La sintaxis será . v_Rec1.Field1

Resumen PL/SQL II

Página 13

Para poder asignar un registro a otro, ambos registro deben de ser del mismo tipo. Una asignación de registros usa la semántica de copia, es decir, los valores de los campos del registro de la derecha en la asignación, son asignados a los campos correspondientes del registro de la izquierda. No es permitida la asignación de registros de distinto tipo, aunque estos tuvieran exactamente la misma definición de campos.

V_REC1 := V_REC2;

También podemos asignar un valor a un registro mediante una instrucción SELECT, que obtenga datos de una tabla y los almacene en un registro. Los campos del registro deben corresponderse con los campos en la lista de selección de la consulta.

Resumen PL/SQL II

Página 14

Resulta bastante habitual definir un registro con los mismos tipos que una fila de una tabla, para facilitar esta tarea tenemos el operador %ROWTYPE. De forma similar al operador %TYPE, %ROWTYPE devuelve un tipo basándose en la definición de la tabla.

No se incluyen las restricciones NOT NULL, pero si modificamos la estructura de la tabla, %ROWTYPE también lo hace si llamamos a un bloque anónimo o compilando el objeto almacenado.

Resumen PL/SQL II

Página 15

Cursores INTRODUCCION El área de contexto es la memoria designada para procesar una instrucción SQL, la cual incluye El número de registros procesados. Un apuntador a la representación de la instrucción SQL analizada. En el caso de una consulta, el conjunto de registros que regresan de la consulta. Un cursor es un manejador o apuntador para el área de contexto. Por medio de éste un programa PL/SQL puede controlar el área de contexto. Los cursores explícitos son aquellos que se declaran, generalmente por medio de una consulta SQL. Los cursores implícitos son creados por Oracle para manejar alguna instrucción SQL y no son declarados por el programador. El cursor implícito sirve para procesar las órdenes INSERT, UPDATE, DELETE y SELECT.....INTO de una sola fila. Puesto que es el motor de PL/SQL quien abre y cierra el cursor, las instrucciones OPEN, FETCH y CLOSE, no son necesarias para este tipo de cursor.

Pasos para procesar un cursor explícito Declaración del cursor La consulta no debe contener la cláusula INTO. Se puede hacer referencia a variables dentro de la cláusula WHERE.

CURSOR IS

También es posible declarar una lista de parámetros: CURSOR ( , ..., ) IS

Resumen PL/SQL II

Página 16

Abrir el cursor La sintaxis es:

OPEN ;

Se examinan los valores de las variables. El apuntador para el conjunto activo se establece en el primer registro. Recuperar los resultados en variables PL/SQL Tenemos dos formas: FETCH INTO ; FETCH INTO ;

Cerrar el cursor La sintaxis es:

CLOSE ;

Cuando se cierra el cursor, es ilegal tratar de usarlo. Es ilegal tratar de cerrar un cursor que ya está cerrado o no ha sido abierto.

Atributos de cursores explícitos

Toman los valores TRUE, FALSE o NULL dependiendo de la situación: Durante la recuperación

Al finalizar la recuperación

Después de cerrar

%NOTFOUND ORA-1001 NULL

FALSE

TRUE

ORA-1001

%FOUND

ORA-1001 NULL

TRUE

FALSE

ORA-1001

%ISOPEN

FALSE

TRUE TRUE

TRUE

FALSE

**

ORA-1001

Atributo

Antes de abrir

Al abrir

%ROWCOUNT ORA-1001 0 *

*

Número de registros que ha recuperado hasta el momento Número de total de registros

**

Resumen PL/SQL II

Página 17

Manejo del cursor por medio de ciclos Por medio de ciclo LOOP. Debe tenerse cuidado de agregar una condición para salir del ciclo: OPEN ; LOOP FETCH INTO ; EXIT WHEN %NOTFOUND; /* Procesamiento de los registros recuperados */ END LOOP; CLOSE ;

Resumen PL/SQL II

Página 18

Por medio de un ciclo WHILE LOOP. La instrucción FETCH aparece dos veces. OPEN ; FETCH INTO ; WHILE %FOUND LOOP /* Procesamiento de los registros recuperados */ FETCH INTO ; END LOOP; CLOSE ;

Resumen PL/SQL II

Página 19

Por medio de un ciclo FOR LOOP. Es la forma más corta ya que el cursor es implícito, se ejecutan las instrucciones OPEN, FETCH y CLOSE.

FOR variable IN LOOP /* Procesamiento de los registros recuperados */ END LOOP;

Resumen PL/SQL II

Página 20

Derivada de esta última forma, se puede utilizar un FOR, donde, sin previa declaración del cursor, se puede incluir la SELECT destinada a dicho cursor.

FOR variable IN ( SELECT . . . ) LOOP /* Procesamiento de los registros recuperados */ END LOOP;

Resumen PL/SQL II

Página 21

CURSORES CON PARÁMETROS En muchas ocasiones se podría desear que el resultado de un cursor dependa de una variable. Para hacer que el cursor varíe según esos parámetros, se han de indicar los mismos en la declaración del cursor. Para ello se pone entre paréntesis su nombre y tipo sin longitud de campo, tras el nombre del cursor en la declaración. Es al abrir el cursor cuando se indica el valor de los parámetros, lo que significa que se puede abrir varias veces el cursor y que éste obtenga distintos resultados dependiendo del valor del parámetro.

Se pueden indicar los parámetros también en el bucle FOR.

Resumen PL/SQL II

Página 22

ACTUALIZACIONES AL RECORRER REGISTROS En muchas ocasiones se realizan operaciones de actualización de registros sobre el cursor que se está recorriendo. Para evitar problemas se deben bloquear los registros del cursor a fin de detener otros procesos que también desearan modificar los datos. El método consta de dos partes: la cláusula FOR UPDATE en la declaración del cursor, y la cláusula WHERE CURRENT OF en una instrucción UPDATE o DELETE. La cláusula FOR UPDATE se coloca al final de la sentencia SELECT del cursor, iría detrás de ORDER BY. Opcionalmente se puede colocar el texto NOWAIT para que el programa no se quede esperando en caso de que la tabla esté bloqueada por otro usuario. Se usa el texto OF seguido del nombre del campo que se modificará, no es necesaria esa cláusula, pero se mantiene para clarificar el código. CURSOR ... SELECT... FOR UPDATE [ OF ] [NOWAIT]

A continuación en la instrucción UPDATE que modifica los registros se puede utilizar una nueva cláusula llamada WHERE CURRENT OF seguida del nombre de un cursor, que hace que se modifique sólo el registro actual del cursor. WHERE CURRENT OF

Resumen PL/SQL II

Página 23

Excepciones Introducción al manejo de errores La implementación que hace Oracle para el manejo de errores es por medio de excepciones y manejadores de excepciones. Los errores pueden ser clasificados de acuerdo a la siguiente tabla:

Tipo de error

Reportado por

Cómo es manejado

En tiempo de compilación

Compilador de PL/SQL

El programador corrige los errores que reporta el compilador

En tiempo de ejecución

El motor de ejecución Las excepciones son lanzadas y capturadas por los de PL/SQL manejadores de excepciones

Las excepciones están diseñadas para manejar errores en tiempo de ejecución. Cuando un error ocurre, una excepción es lanzada y el control pasa al manejador de la excepción.

Declaración de excepciones Hay dos tipos de excepciones: las predefinidas y las definidas por el usuario.

Excepciones definidas por el usuario Son errores que están definidas en el programa, el cual no necesariamente es un error de Oracle. Las excepciones se declaran en la sección declarativa de un bloque PL/SQL. Las excepciones tienen un tipo (EXCEPTION) y un ambiente. DECLARE E_ERROR1 EXCEPTION; BEGIN . . . END;

Resumen PL/SQL II

Página 24

Excepciones predefinidas Las excepciones predefinidas corresponden a errores comunes en SQL. Error de Oracle

Excepción equivalente

Descripción

ORA-0001

DUP_VAL_ON_INDEX

Restricción de unicidad violada

ORA-0051

TIMEOUT_ON_RESOURCE

Tiempo fuera ocurrido mientras esperaba un recurso

ORA-0061

TRANSACTION_BACKED_OUT

La transacción fue desecha por un bloqueo

ORA-1001

INVALID_CURSOR

Operación ilegal con un cursor

ORA_1012

NOT_LOGGED_ON

Sin conexión a Oracle

ORA-1017

LOGIN_DENIED

Nombre de usuario o password inválido

ORA-1403

NO_DATA_FOUND

No se encontraron datos

ORA-1422

TOO_MANY_ROWS

La instrucción SELECT ... INTO devuelve más de un registro

ORA-1476

ZERO_DIVIDE

División por cero

ORA-1722

INVALID_NUMBER

Conversión inválida a un número

ORA-6500

STORAGE_ERROR

Error PL/SQL interno lanzado al exceder la memoria

ORA-6501

PROGRAM_ERROR

Error PL/SQL interno

ORA-6502

VALUE_ERROR

Error al trucar o convertir valores, o en una operación aritmética

ORA-6532

SUBSCRIPT_OUTSIDE_LIMIT

Una referencia a una tabla anidada o índice de varray se encuentra fuera del rango declarado.

ORA-6533

SUBSCRIPT_BEYOND_COUNT

Una referencia a una tabla anidada o índice de varray es mayor que el número de elementos de la colección.

ORA-6511

CURSOR_ALREADY_OPEN

Al intentar abrir un cursor que ya está abierto

Resumen PL/SQL II

Página 25

Lanzamiento y manejo de excepciones Las excepciones definidas por el usuario son lanzadas por medio de la instrucción RAISE, mientras que las excepciones predefinidas son lanzadas cuando ocurre el error de Oracle asociado. Cuando la excepción es lanzada, el control pasa a la sección de excepciones de un bloque PL/SQL. Si no existe esta sección, en el caso de bloques anidados el error se propaga al bloque que contiene al que reporta el error. DECLARE -- Declaración de la excepción a EXCEPTION; BEGIN ... RAISE a; -- El resto del código de esta sección no es ejecutado ... EXCEPTION -- El control pasa al manejador cuando se lanza a WHEN a THEN -- Código a ejecutar para manejar la excepción END; La sección de excepciones consiste de una serie de manejadores. Un manejador de excepción consiste del código que es ejecutado cuando la excepción es lanzada. La sintaxis es la siguiente: EXCEPTION WHEN [ OR ] THEN ; WHEN [ OR ] THEN ; WHEN OTHERS THEN ; END;

Resumen PL/SQL II

Página 26

Cuando se usa WHEN OTHERS y se desea saber que error ocurrió, las funciones SQLCODE y SQLERRM proporcionan esta información. SQLCODE Regresa el código del error que lanzó la excepción SQLERRM Regresa el mensaje de error correspondiente al error que lanzó la excepción. Esta función también acepta un argumento numérico, el cual debe de corresponder al texto asociado con ese número.

Resumen PL/SQL II

Página 27

Excepciones sin definir Pueden ocurrir otras muchas excepciones que no están en la lista de ORACLE. En ese caso aunque no tienen un nombre asignado, sí tienen un número asignado. Ese número es el que aparece cuando Oracle muestra el mensaje de error tras la palabra ORA. Por ejemplo en un error por restricción única Oracle lanza un mensaje encabezado por el texto: “ORA-00001: restricción única (A01.PK_TCATEGORIA_CODIGO_CAT) violada”

Por lo tanto el error de integridad única es el -00001. Si deseamos capturar excepciones sin definir hay que declarar un nombre para la excepción que capturaremos. Eso se hace en el apartado DECLARE: EXCEPTION; Debemos asociar ese nombre al número de error correspondiente en el apartado DECLARE mediante la siguiente sintaxis: PRAGMA EXCEPTION_INIT( , ); En el apartado EXCEPTION capturaremos el nombre de la excepción como si fuera una excepción normal.

Resumen PL/SQL II

Página 28

Definición de mensajes de error propios

El programador puede lanzar sus propias excepciones simulando errores del programa. Para ello hay que declarar un nombre para la excepción en el apartado DECLARE, al igual que para las excepciones sin definir.

EXCEPTION; En la sección BEGIN utilizar la instrucción RAISE para lanzar la excepción: RAISE ; En el apartado de excepciones capturar el nombre de excepción declarado. EXCEPTION WHEN THEN … …. …

Resumen PL/SQL II

….

Página 29

Para definir mensajes de error se usa la función RAISE_APPLICATION_ERROR. Esta instrucción se coloca en la sección ejecutable o en la de excepciones. Lo que hace es lanzar un error cuyo número debe de estar entre el -20000 y el -20999 y hace que Oracle muestre el mensaje indicado. El tercer parámetro opciones puede ser TRUE o FALSE (por defecto TRUE) e indica si el error se añade a la pila de errores existentes. RAISE_APPLICATION_ERROR( , [, ]);

Es un numero entre -20.000 y -20.999.

Es el texto asociado con este error. Su longitud debe ser menor de 512 caracteres.

Es booleano y opcional. Si su valor es TRUE, el error es agregado a la lista de errores lanzados. Si es FALSE, el error reemplaza a la lista actual de errores.

Propagación de excepciones Cuando ocurre se lanza una excepción dentro de la sección ejecutable de un bloque ocurre lo siguiente: 1. Si el bloque actual tiene un manejador para la excepción, se ejecutan las instrucciones asociadas al manejador y control pasa al bloque que engloba a éste. 2. Si no hay un manejador para la excepción, éste se propaga lanzándola en el bloque que engloba al actual. El primer paso se repite para el bloque que engloba.

Resumen PL/SQL II

Página 30

DECLARE A EXCEPTION; BEGIN BEGIN RAISE A; EXCEPTION WHEN A THEN ... END; END;

Se aplica la regla 1 para manejar la excepción DECLARE A EXCEPTION; B EXCEPTION; C EXCEPTION; BEGIN BEGIN RAISE C; EXCEPTION WHEN A THEN . . . END; EXCEPTION WHEN B THEN ... END; La excepción se propaga y el bloque no concluye exitosamente. BEGIN DECLARE v1 NUMBER(3):='abc'; BEGIN ... EXCEPTION WHEN OTHERS THEN ... END; EXCEPTION WHEN OTHERS THEN ... END; En este caso, el bloque interno lanza la excepción, y el bloque externo la maneja.

DECLARE A EXCEPTION; B EXCEPTION; BEGIN BEGIN RAISE A; EXCEPTION WHEN A THEN RAISE B; WHEN B THEN ... END; EXCEPTION WHEN B THEN ... END; En este caso, si se tiene una conclusión exitosa.

Resumen PL/SQL II

DECLARE A EXCEPTION; B EXCEPTION; BEGIN BEGIN RAISE B; EXCEPTION WHEN A THEN ... END; EXCEPTION WHEN B THEN ... END; Se aplica la regla 2 al bloque interno

DECLARE v1 NUMBER(3) := 'abc'; BEGIN ... EXCEPTION WHEN OTHERS THEN ... END;

Esta excepción es inmediatamente propagada, sin ejecutar las instrucciones asociadas al manejador WHEN OTHERS DECLARE A EXCEPTION; B EXCEPTION; BEGIN RAISE A; EXCEPTION WHEN A THEN RAISE B; WHEN B THEN ... END;

La excepción A es lanzada y manejada, pero su manejador lanza B y esta es propagada hacia un bloque exterior, por lo que este bloque no termina exitosamente. DECLARE A EXCEPTION; BEGIN RAISE A; EXCEPTION WHEN A THEN ... RAISE; END;

Cuando RAISE no tiene argumento, la excepción actual es propagada a un bloque externo.

Página 31

COLECCIONES Frecuentemente es conveniente manipular muchas variables a la vez como si fueran una única unidad. Estos tipos de datos se les denominan como colecciones. Oracle7 proporciona la tabla indexada. Oracle8 incorpora las tablas anidadas y los varrays. Oracle9i contempla la posibilidad de crear colecciones multinivel, es decir, colecciones de colecciones. Las colecciones son tipos compuestos, como los registros, sin embargo no combinan variables de diferentes tipos, sino que deben ser del mismo tipo.

TABLAS INDEXADAS Las tablas indexadas son similares sintácticamente a los arrays (matrices) de C o JAVA. Para declarar una tabla indexada, primero se define el tipo de tabla dentro de un bloque y luego se declara la variable de dicho tipo. La sintaxis de una tabla indexada es:

TYPE IS TABLE OF INDEX BY BINARY_INTEGER | PLS_INTEGER;

Donde es el nombre del nuevo tipo que se está definiendo y tipo es un determinado tipo de dato existente o una referencia a un tipo mediante %TYPE o %ROWTYPE. La cláusula INDEX BY BINARY_INTEGER es obligatoria como parte de la definición de la tabla. Dicha cláusula no está disponible para las tablas anidadas. Una vez declarado el tipo y la variable, podemos hacer referencia a un elemento individual de la tabla, utilizando la siguiente sintaxis:

( )

Donde , es el nombre que hemos dado al definir una variable de e es una variable de tipo BINARY_INTEGER | PLS_INTEGER o una variable o expresión que pueda convertirse al tipo BINARY_INTEGER | PLS_INTEGER.

Resumen PL/SQL II

Página 32

Aunque la asignación de valores a elementos de una tabla es sintácticamente similar a la asignación de valores en arrays de C o JAVA, las tablas indexadas se implementan de otra manera. Una tabla indexada es similar a una tabla de base de datos con dos columnas: key (clave) y value (valor). El tipo de clave es BINARY_INTEGER | PLS_INTEGER y el valor podrá ser cualquier tipo de dato especificado en la declaración.

Las tablas indexadas no están restringidas, el único límite que nos encontramos depende directamente del numero de filas permitidas por la clave de tipo BINARY_INTEGER | PLS_INTEGER, pero espero que no os haga falta, crear más elementos, ya que el rango de un BINARY_INTEGER | PLS_INTEGER oscila entre –2.147.483.647 al +2.147.483.647 .

Los elementos de una tabla indexada no necesariamente se encuentran en un orden determinado ya que no se tienen que guardarse en memoria de forma consecutiva como los arrays de C o Java. Las claves utilizadas no tienen por qué ser secuenciales. El índice puede ser un valor negativo.

Resumen PL/SQL II

Página 33

Se puede crear una tabla indexada de registros. Dado que cada elemento de la tabla es un registro, se puede hacer referencia a los campos contenidos en dicho registro, para ello habrá que usar la siguiente sintaxis:

( ) .

Se puede crear una tabla indexada de tipo de objeto.

CREATE OR REPLACE TYPE MYOBJETO AS OBJECT ( CAMPO1 NUMBER(3), CAMPO2 VARCHAR2(20), CAMPO3 DATE); /

DECLARE TYPE OBJETOTAB IS TABLE OF MYOBJETO INDEX BY BINARY_INTEGER; V_OBJETO OBJETOTAB; BEGIN V_OBJETO(1) := MYOBJETO(1, NULL, NULL); V_OBJETO(1).CAMPO2 := '...TEXTO .....'; V_OBJETO(1).CAMPO3 := SYSDATE; DBMS_OUTPUT.PUT_LINE(V_OBJETO(1).CAMPO1||','||V_OBJETO(1).CAMPO2||','||V_OBJETO(1).CAMPO3); END; /

Resumen PL/SQL II

Página 34

Resumen PL/SQL II

Página 35

MÉTODOS PARA LAS TABLAS INDEXADAS

Las tablas indexadas tienen definidos una serie de métodos. Utilizan la siguiente sintaxis:

. [ () ]

EXISTS Se utiliza para averiguar si existe en la colección el elemento indicado a partir de un índice dado. Su sintaxis es EXISTS( ). Devuelve TRUE si la entrada específica existe dentro de la colección.

COUNT Devuelve el número de elementos de una colección.

FIRST Devuelve el índice del primer elemento de una colección.

LAST Devuelve el índice del último elemento de una colección.

NEXT Devuelve el índice del elemento siguiente de una colección respecto a un índice dado. Su sintaxis es NEXT( ).

PRIOR Devuelve el índice del elemento anterior de una colección respecto a un índice dado. Su sintaxis es PRIOR( ).

Resumen PL/SQL II

Página 36

DELETE DELETE Elimina todos los elementos de una tabla PL/SQL. DELETE() Elimina el elemento de la posición indicada por de la tabla PL/SQL. DELETE(, ) Elimina los elementos del rango indicado por y de la tabla PL/SQL.

Resumen PL/SQL II

Página 37

--SET SERVEROUTPUT ON; CREATE OR REPLACE TYPE O_ENTRADAS AS OBJECT ( ARTICULO VARCHAR2(40), CANT NUMBER(5), FECHA_ENTRADA DATE); /

DECLARE TYPE TABLA_ENTRADAS IS TABLE OF O_ENTRADAS INDEX BY BINARY_INTEGER; V_ENT TABLA_ENTRADAS; V_CONT NUMBER:=1; V_NUM NUMBER; V_IND NUMBER; BEGIN V_ENT(1) := O_ENTRADAS('DISCO DURO MULTIMEDIA',50,SYSDATE); V_ENT(3) := O_ENTRADAS('PC COMPAQ PRESARIO',4,SYSDATE+2); V_NUM:=V_ENT.COUNT; V_IND:=V_ENT.FIRST; DBMS_OUTPUT.PUT_LINE('ELEMENTOS EN LA TABLA.....'||V_NUM); WHILE V_CONT