Entendiendo las Funciones de Usuario en Adaptive Server Enterprise

Introducción

La versión 15.0.2 de Adaptive Server Enterprise (ASE) incorpora la posibilidad de crear funciones SQL de usuario (también llamadas SQL User Defined Functions o símplemente SQL UDF). Las funciones de usuario, en contraste a las funciones SQLJ (ver Uso de Métodos Java como Funciones y Procedimientos Almacenados SQLJ en Adaptive Server Enterprise), son rutinas construidas con una o más sentencias Transact-SQL y que encapsulan lógica de aplicación.

Hasta ahora, ASE había soportado funciones del sistema, tales como getdate(), object_name() y object_id(), pero no le daba al usuario la capacidad de crear nuevas funciones. La versión 15.0.2 extiende el comando create function, permitiendo ahora crear funciones de usuario (en adición a las funciones SQLJ). Para ésta versión las funciones de usuario son “escalares”, lo cual quiere decir que están en capacidad de arrojar un solo valor, de cualquier tipo.

Obviamente, los procedimientos almacenados tradicionales también pueden arrojar un valor (usando la sentencia return o parámetros out), pero éstos no se pueden usar en un select ni como parte de una expresión de una cláusula where, índice funcional, ni columna computada, lo que es un claro indicio de porqué necesitamos las funciones de usuario. Adicionalmente, la sentencia return de las funciones de usuario, puede retornar cualquier tipo de dato, como lo veremos más adelante.

El Comando create function

La sintaxis básica del comando create function para funciones de usuario es la siguiente:

create function nombre( @parametro tipo_de_dato [ = valor ], ... ) returns tipo_de_dato [ with recompile ] as   Sentencias T-SQL   return valor

Para invocar la función de usuario se usa la siguiente sintaxis:

[ base_de_datos. ]dueño.nombre(parametro, ... )

Hay que anotar que el uso del prefijo “dueño.” es obligatorio.

Veamos un ejemplo sencillo; la siguiente función determina el último día del mes, dada una fecha:

use pubs3 go create function UltimoDiaDelMes (@fecha datetime) returns int as begin     declare @UltimoDia int     set @UltimoDia = day(dateadd(day,-1,dateadd(month,1,dateadd(day,1-day(@fecha),@fecha))))     return @UltimoDia end go

Una vez creada la función, uno puede invocarla de la siguiente manera:

select dbo.UltimoDiaDelMes( '2/1/2004') go ---------        29

También uno podría usar la función como parte de una expresión del where de una sentencia select:

select title from titles where dbo.UltimoDiaDelMes( pubdate ) = 31 go

Valores Predeterminados para los Parámetros

De manera similar a los procedimientos almacenados convencionales, es posible definir valores predeterminados para los parámetros de las funciones; por ejemplo, para la siguiente función el parámetro @b tomaría el valor 0, si no se especifica ningún valor para éste al invocar la función:

create function func1(@a varchar(30), @b int = 0) returns varchar(30) as    ....   return @str go

Para poder hacer uso del valor predeterminado, se utiliza la palabra clave default:

select dbo.func1( 'Hola Mundo', default ) go

Las funciones de usuario no permiten (al contrario de los procedimientos almacenados) omitir parámetros; al hacerlo se genera un mensaje de error como el siguiente:

select dbo.func1( 'Hola Mundo' ) go Msg 11021, Level 16, State 17: Server 'SYBASE', Line 1: Function pubs3.dbo.func1 invoked with wrong number or type of argument(s).

Columnas Computadas e Índices Funcionales

Complementando la habilidad de ASE 15 de crear columnas computadas e índices funcionales, en la versión 15.0.2 es posible usar funciones de usuario como base de dichas definiciones. Este es un ejemplo de una columna computada que usa la función de usuario UltimoDiaDelMes(), creada arriba:

create table mi_tabla( a int, b as dbo.UltimoDiaDelMes( getdate() ) ) go

Y si quisiéramos usar la misma función como parte de la definición de un índice funcional, ejecutaríamos algo como:

create index f_idx1 on titles( title_id, dbo.UltimoDiaDelMes(pubdate) ) go

Recursividad

Muchos se preguntarán si es posible crear funciones recursivas en ASE. La respuesta es si, pero se requiere de un “truco” para hacerlo. Pensemos en el ejemplo típico de la función factorial(); lo primero que se vendría a la mente es algo así:

create function factorial( @x bigint ) returns bigint as declare @factorial bigint if @x = 0   set @factorial = 1 else   set @factorial = @x * dbo.factorial( @x - 1 ) return @factorial go

Sin embargo, aunque el código anterior debería funcionar, ASE genera el siguiente mensaje de error:

Msg 14217, Level 16, State 1: Server 'SYBASE', Procedure 'factorial', Line 8: SQL function not found. If you are attempting to call a java UDF, Java services are not enabled.

El mensaje indica que la funcion dbo.factorial() no existe. Es decir, no es posible crear una función que invoque otra que no exista (como ella misma). Entonces ¿qué hacer? Básicamente lo que debemos hacer es lo siguiente:

1. Crear una función “auxiliar” con el mismo número y tipo de parámetros que nuestra función y que arroje el mismo tipo de dato; el código NO debe ser recursivo y puede ser tan sencillo como arrojar una expresión constante:

create function fact_aux( @x bigint ) returns bigint as return 1 go

2. Crear nuestra función recursiva, pero invocando, en donde se hace el llamado recursivo, la función auxiliar que se creó en el punto 1:

create function factorial( @x bigint ) returns bigint as declare @factorial bigint if @x = 0    set @factorial = 1 else    set @factorial = @x * dbo.fact_aux( @x - 1 ) return @factorial go

3. Eliminar la función auxiliar y volverla a crear usando el mismo código de la función principal creada en el punto 2 e invocando dicha función en el llamado recursivo:

drop function fact_aux go create function fact_aux( @x bigint ) returns bigint as declare @factorial bigint if @x = 0    set @factorial = 1 else    set @factorial = @x * dbo.factorial( @x - 1 ) return @factorial go

4. Como resultado, tenemos una función recursiva que podemos invocar así:

select dbo.factorial( 5 ) go ---------       120

Es importante recalcar que el uso de funciones recursivas puede ser exigente desde el punto de vista de uso recursos, así que es posible que sea necesario aumentar el parámetro estático stack size de ASE, usando el procedimiento sp_configure.

Tipos de Dato de Usuario

Como lo mencionamos antes, una función de usuario puede retornar cualquier tipo de dato, incluyendo un tipo de dato de usuario. Por ejemplo, la siguiente función usa el tipo de dato ShortString, definido como tipo de dato de usuario:

sp_addtype ShortString, 'varchar(30)' go create function LowerCase( @string ShortString ) returns ShortString as   return lower( @string ) go

Para invocar la función haríamos lo siguiente:

select dbo.LowerCase( 'HOLA MUNDO' ) go -------------------------------------- hola mundo

Permisos

Al igual que con los procedimientos almacenados, es posible conceder o revocar permisos de ejecución de una función a otros usuarios, grupos o roles, usando los comandos grant y revoke. En el siguiente ejemplo se otorga permiso de ejecución sobre la función func1 al grupo public:

grant exec on func1 to public go

Funciones “Globales”

Como sabemos, los procedimientos almacenados del sistema (cuyo nombre comienza por “sp_” y se almacenan en la base de datos sybsystemprocs) pueden ser invocados desde cualquier base de datos. Usando ésta misma regla (aunque ésta capacidad no está documentada), podríamos entonces crear una función “global”, así:

1. Crear la función en la base de datos sybsystemprocs, usando el prefijo “sp_” en su nombre:

use sybsystemprocs go create function sp_func1( @a varchar(1024) ) returns int as begin   ...   return @result end go grant exec on sp_func1 to public go

2. Usar la función sp_func1() desde cualquier otra base de datos:

use pubs3 go select dbo.sp_func1( 'Hola Mundo!' ) go

El comando drop function

La versión 15.0.2 extiende la funcionalidad del comando drop function, soportando ahora funciones de usuario (versiones anteriores sólo soportaban funciones SQLJ):

drop function func1 go