domingo, 11 de noviembre de 2007

Indices condicionales

Hay tablas para las cuales desearíamos hacer valer condiciones de unicidad pero dadas ciertas condiciones que dependan de alguna otra columna de control.

Un ejemplo típico se da cuando mantenemos registros en una tabla con borrado lógico, y queremos que exista un único registro activo (pudiendo aparecer el registro inactivo más de una vez). Digamos que tenemos la tabla t, con dos columnas fundamentales: un id y un indicador de registro activo Y/N. Queremos que el Id sea único solamente cuando Activo es 'Y'.

Muchas veces, mucha gente se enfrenta a este problema y lo resuelve creando una tabla auxiliar para hacer valer la condición de unicidad, almacenando en ella las tuplas que no desea repetir para lograr dicho control.
Todo el problema que implica crear (y mantener) estructuras adicionales y la complejidad de manipulación, puede evitarse utilizando lo que siempre estuvo al alcance: el índice de función y la sentencia CASE.

SQL> CREATE TABLE t (sec int primary key, id VARCHAR2(100), activo VARCHAR2(1));

Table created.

SQL> CREATE UNIQUE INDEX ix_t ON t
2 (CASE WHEN activo='Y' THEN id ELSE NULL END);

Index created.

SQL> insert into t values (1, 'COD1', 'Y');

1 row created.

SQL> insert into t values (2, 'COD1', 'N');

1 row created.

SQL> insert into t values (3, 'COD1', 'N');

1 row created.

SQL> insert into t values (4, 'COD1', 'Y');
insert into t values (4, 'COD1', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (LFER.IX_T) violated

Los índices condicionales no son un feature especial de Oracle, sino que se construyen utilizando los mismos índices de función que usualmente definimos para todas las filas de una tabla. En este caso, la función CASE permite que el índice único se construya sobre un subconjunto de registros, aquellos en el que el campo activo sea igual a 'Y'.

Ver también:
11g y sus índices invisibles
Mis índices no funcionan!
Indices de función para mejorar un LIKE

miércoles, 7 de noviembre de 2007

Variables en SQL*Plus (parte 3)

Anteriormente repasando variables en sqlplus, vimos las variables de sustitución y las de usuario. Finalmente llegamos al tercer tipo de variables, las variables bind (o de ligadura).

VARIABLES BIND

Estas variables tienen la particularidad de poder ser definidas en sqlplus, usadas y/o asignadas dentro de nuestros programas PL/SQL y luego leídas una vez terminado el programa. Son convenientes cuando queremos tener comunicación entre scripts de sqlplus y bloques de PL/SQL.

Las variables bind se definen así:
SQL> VARIABLE bvar NUMBER;
Pueden utilizarse también los tipos: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, BINARY_FLOAT y BINARY_DOUBLE

Si queremos asignar estas variables antes de llamar a nuestros bloques de PL/SQL, debemos hacerlo desde otro bloque BEGIN END o simplemente usando la sentencia EXEC:

SQL> exec :bvar := 1;

PL/SQL procedure successfully completed.

Las variables dentro de PL/SQL se referencian con dos puntos seguidos del nombre de la misma.
Ahora ya podemos utilizar las variables dentro de nuestro programa e imprimir el resultado:
SQL> BEGIN
2 IF :bvar = 1 THEN
3 :bvar := 0;
4 ELSE
5 :bvar := 1;
6 END IF;
7 END;
8 /
PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
0

SQL> /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
1

Y con esto culminamos el repaso de variables en sql*plus. Finalmente como complemento, veremos brevemente los cursores referenciales los cuales son también útiles para recuperar un conjunto de tuplas de nuestros SELECTs, desde los programas PL/SQL hacia fuera, y así tener más flexibilidad.

REFCURSORS

Se declaran como una variable bind, pero colocamos REFCURSOR en el tipo:

SQL> VARIABLE rcur REFCURSOR;

SQL> begin
2 OPEN :rcur FOR select segment_name, segment_type from user_segments order by segment_name;
3 end;
4 /

PL/SQL procedure successfully completed.

A diferencia de los cursores tradicionales que se abren y requieren del uso de fetch, al abrir estos cursores bind ya son ejecutados automáticamente y sus resultados almacenados en memoria. Solo resta imprimir las filas de la consulta.

SQL> print rcur
SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
ACUMULADO_CONSUMO TABLE
BIG_TABLE TABLE
BIG_TABLE_PK INDEX
CLIENTE TABLE
COUNTRY_C_ID_PK INDEX
CUENTA TABLE
INVENTARIO TABLE
IX_T INDEX
MUNICIPIO TABLE
NDX_ACUMCON INDEX
NDX_ACUMRENT INDEX
NDX_ACUMTERC INDEX
NDX_ACUMVAR INDEX

13 rows selected.


Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 2) - Sobre variables de sustitución