Sentencia CREATE FUNCTION (tabla, fila o escalar de SQL)

La sentencia CREATE FUNCTION (tabla, fila o escalar de SQL) se utiliza para definir una función de tabla, fila o escalar de SQL definida por el usuario.

Una función escalar devuelve un solo valor cada vez que se invoca y en general es válida cuando una expresión SQL es válida. Se puede utilizar una función de tabla en una cláusula FROM y devuelve una tabla. Se puede utilizar una función de fila como función de transformación y devuelve una fila.

Invocación

Esta sentencia se puede incorporar a un programa de aplicación o emitir mediante el uso de sentencias de SQL dinámico. Es una sentencia ejecutable que puede prepararse de forma dinámica sólo si el comportamiento de ejecución de DYNAMICRULES está en vigor para el paquete (SQLSTATE 42509).

Autorización

Los privilegios que posee el ID de autorización de la sentencia debe tener al menos una de las autorizaciones siguientes:

Autorización IMPLICIT_SCHEMA en la base de datos, si el nombre de esquema implícito o explícito de la función no existe
Privilegio CREATEIN para el esquema, si el nombre de esquema de la función hace referencia a un esquema existente
Autoridad SCHEMAADM en el esquema, si existe el nombre de esquema de la función
Autorización DBADM

y, como mínimo, una de las autorizaciones siguientes en cada tabla, vista o apodo que se identifica en cualquier selección completa:

Privilegio CONTROL para esa tabla, vista o apodo
Privilegio SELECT para esa tabla, vista o apodo
Privilegio SELECTIN en el esquema que contiene la tabla, vista o apodo
Autorización DATAACCESS de esquema que contiene la tabla, vista o apodo
Autorización DATAACCESS para la base de datos

Los privilegios de grupo distintos de PUBLIC no se consideran para ninguna tabla o vista que se haya especificado en la sentencia CREATE FUNCTION.

Los requisitos de autorización de la fuente de datos para la tabla o vista a la que hace referencia el apodo se aplican al invocarse la función. El ID de autorización de la conexión se puede correlacionar con un ID de autorización remoto diferente.

Los privilegios que mantiene el ID de autorización de la sentencia deben incluir también todos los privilegios necesarios para invocar las sentencias de SQL especificadas en el cuerpo de la función.

Para sustituir una función existente, el ID de autorización de la sentencia debe ser el propietario de la función existente (SQLSTATE 42501).

Si se especifica la opción SECURED, el ID de autorización de la sentencia debe incluir la autorización SECADM o CREATE_SECURE_OBJECT (SQLSTATE 42501).

Sintaxis

declaración-parámetro

tipo1-datos, tipo2-datos

tipo-incorporado

tipo-datos-anclados

tipo-datos-fila-anclados

cláusula-por-omisión

lista-columnas

tipo3-datos

lista-opciones

cuerpo-función-SQL

Notas:

¹ OUT e INOUT sólo son válidos si RETURNS especifica un resultado escalar y cuerpo-función-SQL es una sentencia de SQL compuesto (compilado).
² La cláusula FOR BIT DATA se puede especificar en cualquier orden con las restricciones de columna siguientes. La cláusula FOR BIT DATA no se puede especificar con unidades de serie CODEUNITS32 (SQLSTATE 42613).
³ DB2SECURITYLABEL es el tipo diferenciado incorporado que debe utilizarse para definir la columna de etiqueta de seguridad de fila de una tabla protegida.
⁴ Solo es válido para la definición de función escalar compilada y una definición de función de tabla en línea. Una función escalar compilada definida como MODIFIES SQL DATA solo puede utilizarse como el único elemento del lado derecho de una sentencia de asignación que se encuentra dentro de una sentencia de SQL compuesto (compilado).
⁵ Sólo es válido si RETURNS especifica un resultado escalar (tipo-datos2)
⁶ A la especificación de una sentencia de SQL compuesto (compilado) se aplica lo siguiente: a) Debe utilizarse si los tipos de datos de los parámetros o tipos de datos devueltos incluyen un tipo de fila, tipo de matriz o tipo de cursor; b) Debe utilizarse si la cláusula RETURNS TABLE especifica cualquier sintaxis que no sea una lista-columnas; c) No recibe soporte si se especifica RETURNS ROW; d) No recibe soporte cuando se define una función de tabla en un entorno de base de datos particionada.

Descripción

OR REPLACE

Especifica que se debe sustituir la definición de la función si existe una en el servidor actual. La definición existente se descarta de forma efectiva antes de que la nueva definición se sustituya en el catálogo, con la excepción de que los privilegios que se han otorgado sobre la función no se ven afectados por ello. Esta opción sólo puede especificarla el propietario del objeto. Esta opción se ignora si no existe una definición para la función en el servidor actual. Para sustituir una función ya existente, el nombre específico y el nombre de función de la nueva definición tienen que ser los mismos que el nombre específico y el nombre de función de la antigua definición, o la signatura de la nueva definición debe coincidir con la signatura de la antigua definición. De lo contrario, se creará una nueva función.

Si se hace referencia a la función en la definición de un permiso de fila o una máscara de columna, la función no se puede sustituir (SQLSTATE 42893).

nombre-función

Indica el nombre de la función que se está definiendo. Consiste en el nombre, calificado o no calificado, que designa una función. El formato no calificado de un nombre-función es un identificador SQL. En las sentencias de SQL dinámico, el registro especial CURRENT SCHEMA se utiliza como calificador para un nombre de objeto no calificado. En sentencias de SQL estático, la opción de precompilación/vinculación QUALIFIER especifica de forma implícita el calificador para los nombres de objeto no calificados. La forma calificada es un nombre-esquema seguido de un punto y un identificador de SQL.

El nombre, incluidos los calificadores implícitos y explícitos, junto con el número de parámetros y el tipo de datos de cada parámetro (sin tener en cuenta ningún atributo de longitud, precisión o escala del tipo de datos) no debe identificar una función descrita en el catálogo (SQLSTATE 42723). El nombre no calificado, junto con el número y el tipo de datos de los parámetros, que por supuesto es exclusivo en su esquema, no es necesario que lo sea en todos los esquemas.

Si se especifica un nombre de dos partes, el nombre-esquema no puede empezar por 'SYS' (SQLSTATE 42939).

Algunos nombres que se utilizan como palabras clave en los predicados están reservados para que los utilice el sistema y no pueden utilizarse como nombre-función (SQLSTATE 42939). Los nombres son SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH y los operadores de comparación.

Se puede utilizar el mismo nombre para más de una función si hay alguna diferencia en la signatura de las funciones. Aunque no hay ninguna prohibición al respecto, una función de tabla externa definida por el usuario no debe tener el mismo nombre que una función incorporada.

(declaración-parámetro,...)

Identifica el número de parámetros de entrada de la función y especifica la modalidad, el nombre, el tipo de datos y el valor por omisión opcional de cada parámetro. En la lista debe especificarse una entrada por cada parámetro que la función espera recibir. No se permiten más de 90 parámetros (SQLSTATE 54023).

Existe la posibilidad de registrar una función que no tenga ningún parámetro. En este caso, sigue siendo necesaria la codificación de los paréntesis, sin incluir ningún tipo de datos. Por ejemplo:

   CREATE FUNCTION WOOFER() ...

En un esquema, no se permite que dos funciones que se denominen igual tengan exactamente el mismo tipo para todos los parámetros correspondientes. Las longitudes, precisiones y escalas no se consideran en esta comparación de tipos. Por esta razón, se considera que CHAR(8) y CHAR(35) tienen el mismo tipo, al igual que DECIMAL(11,2) y DECIMAL (4,3), así como DECFLOAT(16) y DECFLOAT(34). Se considera que la especificación de un tipo diferenciado con tipo no firme es del mismo tipo de datos que el tipo de fuente del tipo diferenciado. Para una base de datos Unicode, se considera que CHAR(13) y GRAPHIC(8) son del mismo tipo. Hay una agrupación más profunda de los tipos que hace que se traten como el mismo tipo para esta finalidad como, por ejemplo, DECIMAL y NUMERIC. Una signatura duplicada devuelve un error (SQLSTATE 42723).

Si el tipo de datos de un parámetro es tipo de datos booleano, tipo de matriz, tipo de cursor o tipo de fila, el cuerpo de la función de SQL solo puede hacer referencia al parámetro dentro de la sentencia de SQL compuesto (compilado) (SQLSTATE 428H2).

IN | OUT | INOUT

Especifica la modalidad del parámetro. Si la función devuelve un error, los parámetros OUT no se definen y los parámetros INOUT no cambian. El valor por omisión es IN.

IN: Identifica el parámetro como un parámetro de entrada para la función. Los cambios que se realicen en el parámetro dentro de la función no estarán disponibles para el contexto de invocación cuando se devuelva el control.
OUT: Identifica el parámetro como un parámetro de salida para la función.; La función debe ser una función escalar que se ha definido con una sentencia de SQL compuesto (compilado) (SQLSTATE 42613).; A la función sólo puede hacerse referencia en la parte derecha de una sentencia de asignación que se encuentre en una sentencia de SQL compuesto (compilado), y la referencia de función no puede formar parte de una expresión (SQLSTATE 42887).
INOUT: Identifica el parámetro como parámetro de entrada y de salida para la función.; La función debe ser una función escalar que se ha definido con una sentencia de SQL compuesto (compilado) (SQLSTATE 42613).; A la función sólo puede hacerse referencia en la parte derecha de una sentencia de asignación que se encuentre en una sentencia de SQL compuesto (compilado), y la referencia de función no puede formar parte de una expresión (SQLSTATE 42887).

nombre-parámetro

Especifica un nombre para el parámetro. El nombre no puede ser el mismo que el de cualquier otro nombre-parámetro de la lista de parámetros (SQLSTATE 42734).

tipo1-datos

Especifica el tipo de datos del parámetro.

tipo-incorporado

Especifica un tipo de datos incorporado. Para obtener una descripción más completa de cada tipo de datos incorporado, excepto BOOLEAN y CURSOR, que no pueden especificarse para una tabla, consulte el apartado CREATE TABLE.

BOOLEAN: Para un booleano.
CURSOR: Para una referencia a un cursor subyacente.

tipo-datos-anclados

Identifica otro objeto que se utiliza para definir el tipo de datos de parámetro. El tipo de datos del objeto de anclaje puede ser cualquiera de los tipos de datos explícitamente permitidos como tipo-datos1. El tipo de datos del objeto de anclaje tiene las mismas limitaciones que se aplican a la especificación del tipo de datos directamente o, en el caso de una fila, a la creación de un tipo de fila.

ANCHOR DATA TYPE TO

Indica que se utiliza un tipo de datos anclados para especificar el tipo de datos.

nombre-variable1

Identifica una variable global. El tipo de datos de la variable global se utiliza como tipo de datos para el nombre-parámetro.

nombre-tabla.nombre-columna

Identifica un nombre de columna de una tabla o vista existente. El tipo de datos de la columna se utiliza como tipo de datos para el nombre-parámetro.

ROW OF nombre-tabla o nombre-vista

Especifica una fila de campos con nombres y tipos de datos que se basan en los nombres de columna y los tipos de datos de columna de la tabla identificada por nombre-tabla o la vista identificada por nombre-vista. El tipo de datos del nombre-parámetro es un tipo de fila sin nombre.

ROW OF nombre-variable-cursor

Especifica una fila de campos con nombres y tipos de datos que se basan en los nombres de campo y los tipos de datos de campos de la variable de cursor identificada por nombre-variable-cursor. La variable de cursor especificada debe ser uno de los elementos siguientes (SQLSTATE 428HS):

Una variable global con un tipo de datos de cursor con tipo firme.
Una variable global con un tipo de datos de cursor con tipo no firme que se creó o declaró con una cláusula CONSTANT especificando una sentencia-select en la que todas las columnas de resultados tienen nombre.

Si el tipo de cursor de la variable de cursor no es de tipo firme que utiliza un tipo de fila con nombre, el tipo de datos de nombre-parámetro es un tipo de fila sin nombre.

nombre-tipo-matriz: Especifica el nombre de un tipo de matriz definido por el usuario. Si se especifica el nombre-tipo-matriz sin un nombre de esquema, el tipo de matriz se resuelve buscando en los esquemas de la vía de acceso de SQL.

nombre-tipo-cursor: Especifica el nombre de un tipo de cursor. Si se especifica el nombre-tipo-cursor sin un nombre de esquema, el tipo de cursor se resuelve buscando en los esquemas de la vía de acceso de SQL.

nombre-tipo-diferenciado: Especifica el nombre de un tipo diferenciado. La longitud, precisión y la escala del parámetro son, respectivamente, la longitud, la precisión y la escala del tipo de fuente del tipo diferenciado. Un parámetro de tipo diferenciado se pasa como tipo fuente del tipo diferenciado. Si se especifica el nombre-tipo-diferenciado sin un nombre de esquema, el tipo diferenciado se resuelve buscando en los esquemas de la vía de acceso de SQL.

REF (nombre-tipo): Especifica un tipo de referencia sin ámbito. El nombre-tipo especificado debe identificar un tipo estructurado definido por el usuario (SQLSTATE 428DP). El sistema no intenta deducir el ámbito del parámetro o resultado. Dentro del cuerpo de la función, se puede utilizar un tipo de referencia en una operación de eliminación de referencia sólo si primero se convierte para que tenga un ámbito. Similarmente, una referencia devuelta por una función SQL se puede utilizar en una operación de eliminación de referencia sólo si primero se convierte para que tenga un ámbito. Si se especifica un nombre de tipo sin un nombre de esquema, el nombre-tipo se resuelve buscando los esquemas en la vía de acceso de SQL.

nombre-tipo-fila: Especifica el nombre de un tipo de fila definido por el usuario. Los campos de cada parámetro son los campos del tipo de fila. Si se especifica el nombre-tipo-fila sin un nombre de esquema, el tipo de fila se resuelve buscando en los esquemas de la vía de acceso de SQL.

nombre-tipo-estructurado: Especifica el nombre de un tipo estructurado definido por el usuario. Si se especifica el nombre-tipo-estructurado sin un nombre de esquema, el tipo estructurado se resuelve buscando en los esquemas de la vía de acceso de SQL.

DEFAULT

Especifica un valor por omisión para el parámetro. El valor por omisión puede ser una constante, un registro especial, una variable global, una expresión o la palabra clave NULL. Los registros especiales que se pueden especificar como el valor por omisión son los mismos que se pueden especificar para un valor por omisión de columna (véase cláusula-por-omisión en la sentencia CREATE TABLE). Se pueden especificar otros registros especiales como valor por omisión utilizando una expresión.

La expresión puede ser cualquier expresión del tipo que se describe en el apartado "Expresiones". Si no se especifica un valor por omisión, el parámetro no tendrá ningún valor por omisión y no se podrá omitir el argumento correspondiente al invocar el procedimiento. El tamaño máximo de la expresión es de 64 K bytes.

La expresión por omisión no debe modificar datos SQL (SQLSTATE 428FL o SQLSTATE 429BL). La expresión debe ser compatible con asignaciones con el tipo de datos del parámetro (SQLSTATE 42821).

No se puede especificar un valor por omisión en las situaciones siguientes:

Para parámetros INOUT o OUT (SQLSTATE 42601)
Para un parámetro del tipo ARRAY, ROW o CURSOR (SQLSTATE 429BB)
Para un parámetro de una definición de función donde también se ha especificado una cláusula RETURNS ROW o una cláusula PREDICATES (SQLSTATE 42613)

RETURNS

Esta cláusula obligatoria identifica el tipo de salida de la función.

Si el tipo de datos de la salida de la función es un tipo de datos booleano, tipo de matriz, tipo de cursor o tipo de fila, el cuerpo de la función de SQL debe ser una sentencia de SQL compuesto (compilado) (SQLSTATE 428H2).

tipo2-datos

Especifica el tipo de datos de la salida.

En esta sentencia, son válidas exactamente las mismas consideraciones que para los parámetros de funciones de SQL descritos anteriormente en tipo1-datos para los parámetros de función.

ROW

Especifica que la salida de la función es una única fila. Si la función devuelve más de una fila, se devolverá un error (SQLSTATE 21505).

Esta forma de función de fila sólo puede utilizarse como función de transformación para un tipo estructurado (que tenga un tipo estructurado como parámetro y que sólo devuelva tipos de datos incorporados).

lista-columnas

La lista de nombres de columnas y tipos de datos devueltos para una función ROW. La lista-columnas debe incluir, como mínimo, dos columnas (SQLSTATE 428F0).

nombre-columna: Especifica el nombre de esta columna. El nombre no puede estar calificado y no puede utilizarse el mismo nombre para más de una columna de la lista.
tipo-datos3: Especifica el tipo de datos de la columna y puede ser cualquier tipo de datos soportado por un parámetro de la función SQL.
Se aplican las mismas consideraciones que para los parámetros de funciones de SQL descritos anteriormente en tipo1-datos para los parámetros de función. Sin embargo, el tipo-datos3 no da soporte a: tipo-datos-anclados, nombre-tipo-matriz, nombre-tipo-cursor y nombre-tipo-fila.

TABLE

Especifica que el resultado de la función es una tabla.

lista-columnas

La lista de nombres de columnas y tipos de datos devueltos para una función TABLE.

nombre-columna: Especifica el nombre de esta columna. El nombre no puede estar calificado y no puede utilizarse el mismo nombre para más de una columna de la lista.
tipo-datos3: Especifica el tipo de datos de la columna y puede ser cualquier tipo de datos soportado por un parámetro de la función SQL.
Se aplican las mismas consideraciones que para los parámetros de funciones de SQL descritos anteriormente en tipo1-datos para los parámetros de función. Sin embargo, el tipo-datos3 no da soporte a: tipo-datos-anclados, nombre-tipo-matriz, nombre-tipo-cursor y nombre-tipo-fila.

nombre-tipo-fila

Especifica un tipo de fila cuyos campos se utilizan para derivar la lista de columnas. Los nombres de campo de los tipos de fila se utilizan como nombres de columna.

tipo-datos-fila-anclados

Identifica la información de fila de otro objeto que ha de utilizarse como columnas de la tabla devuelta.

ANCHOR DATA TYPE TO

Indica que se utiliza un tipo de datos anclados para especificar el tipo de datos.

nombre-variable

Identifica una variable global. El tipo de datos de la variable a la que se hace referencia debe ser un tipo de fila.

ROW OF nombre-tabla o nombre-vista

Especifica una fila de campos con nombres y tipos de datos que se basan en los nombres de columna y los tipos de datos de columna de la tabla identificada por nombre-tabla o la vista identificada por nombre-vista. Los tipos de datos de las columnas de objeto de anclaje tienen las mismas limitaciones que se aplican a tipo-datos3.

ROW OF nombre-variable-cursor

Una variable global con un tipo de datos de cursor con tipo firme.
Una variable global con un tipo de datos de cursor con tipo no firme que se creó o declaró con una cláusula CONSTANT especificando una sentencia-select en la que todas las columnas de resultados tienen nombre.

ELEMENT OF nombre-tipo-matriz

Especifica un tipo de matriz cuyo tipo de datos de elemento se utiliza para derivar la lista de columnas. Si nombre-tipo-matriz identifica un tipo de matriz con elementos que son un tipo de fila, los nombres de campo del tipo de fila se utilizan como los nombres de columna. Si el nombre-tipo-matriz identifica un tipo de matriz con elementos que no son tipos de fila, el resultado único del nombre de columna es COLUMN_VALUE.

tipo-incorporado

Consulte en "CREATE TABLE" la descripción de los tipos de datos incorporados.

SPECIFIC nombre-específico

Proporciona un nombre exclusivo para la instancia de la función que se está definiendo. Este nombre específico puede utilizarse cuando se utiliza esta función como fuente, al descartar la función o bien al comentarla. Nunca puede emplearse para invocar a la función. El formato no calificado de un nombre-específico es un identificador SQL. La forma calificada es un nombre-esquema seguido de un punto y un identificador de SQL. El nombre (incluido el calificador implícito o explícito) no debe designar otra instancia de función que exista en el servidor de aplicaciones; de lo contrario se produce un error (SQLSTATE 42710).

El nombre-específico puede ser el mismo que un nombre-función ya existente.

Si no se especifica ningún calificador, se emplea el que se haya utilizado para el nombre-función. Si se especifica un calificador, debe ser el mismo que el calificador explícito o implícito de nombre-función; de lo contrario se produce un error (SQLSTATE 42882).

Si no se especifica el nombre-específico, el gestor de bases de datos genera un nombre exclusivo. El nombre exclusivo es SQL seguido de una indicación de fecha y hora en forma de serie caracteres: SQLaammddhhmmssxxx.

LANGUAGE SQL

Especifica que la función está escrita en SQL.

PARAMETER CCSID

Especifica el esquema de codificación que se debe utilizar para todos los datos de serie que se pasan a la función y desde ella. Si no se especifica la cláusula PARAMETER CCSID, el valor por omisión es PARAMETER CCSID UNICODE para las bases de datos Unicode y PARAMETER CCSID ASCII para todas las demás bases de datos.

ASCII: Especifica que los datos de serie están codificados en la página de códigos de la base de datos. Si la base de datos es Unicode, no se puede especificar PARAMETER CCSID ASCII (SQLSTATE 56031).
UNICODE: Especifica que los datos de caracteres están en UTF-8, y que los datos gráficos están en UCS-2. Si la base de datos no es Unicode, no se puede especificar PARAMETER CCSID UNICODE (SQLSTATE 56031).

DETERMINISTIC o NOT DETERMINISTIC

Esta cláusula opcional especifica si la función siempre devuelve los mismos resultados para unos valores argumento determinados (DETERMINISTIC) o si la función depende de ciertos valores de estado que afectan a los resultados (NOT DETERMINISTIC). Es decir, una función DETERMINISTIC siempre debe devolver la misma tabla ante invocaciones sucesivas con entradas idénticas. Con la especificación de NOT DETERMINISTIC, se evitan las optimizaciones que aprovechan el hecho de que las entradas idénticas siempre producen los mismos resultados.

EXTERNAL ACTION o NO EXTERNAL ACTION

Especifica si la función puede realizar una acción que cambie el estado de un objeto que el gestor de bases de datos no gestiona. Un ejemplo de una acción externa es enviar un mensaje o grabar un registro en un archivo. El valor por omisión es EXTERNAL ACTION.

EXTERNAL ACTION: Especifica que la función realiza una acción que cambia el estado de un objeto que el gestor de bases de datos no gestiona.
NO EXTERNAL ACTION: Especifica que la función no realiza ninguna acción que cambie el estado de un objeto que el gestor de bases de datos no gestiona. El gestor de bases de datos utiliza esta información durante la optimización de las sentencias de SQL.

READS SQL DATA, CONTAINS SQL, o MODIFIES SQL DATA

Especifica la clasificación de sentencias SQL que puede ejecutar la función. El gestor de base de datos verifica que las sentencias SQL que emite la función son coherentes con esta especificación.

Para la clasificación de cada sentencia, consulte Sentencias SQL que se pueden ejecutar en rutinas y activadores

READS SQL DATA: Especifica que la función puede ejecutar sentencias con la clasificación de acceso de datos de READS SQL DATA, CONTAINS SQL o NO SQL. La función no puede ejecutar sentencias SQL que modifican datos (SQLSTATE 42985). Es el valor por omisión.
CONTAINS SQL: Especifica que la función solo puede ejecutar sentencias SQL con una clasificación de acceso de datos de CONTAINS SQL. La función no puede ejecutar sentencias SQL que lean o modifiquen datos (SQLSTATE 42985).
MODIFIES SQL DATA: Especifica que la función puede ejecutar cualquier sentencia SQL excepto aquellas sentencias que no se admiten en las funciones.

ALLOW PARALLEL o DISALLOW PARALLEL

Esta cláusula especifica si UDF se puede paralelizar, es decir, si una única invocación de UDF puede hacer que varias instancias de UDF (normalmente una instancia por partición) se ejecuten en paralelo. La ejecución en paralelo suele mejorar el rendimiento global, pero solo está permitida cuando se cumplen todas las condiciones siguientes:

Se ha especificado la cláusula CONTAINS SQL.
Todas las invocaciones de UDF son completamente independientes entre sí.

DISALLOW PARALLEL es el valor predeterminado.

STATIC DISPATCH

Esta cláusula opcional indica que, durante la resolución de la función, se elige una función basada en los tipos estáticos (tipos declarados) de los parámetros de la función.

CALLED ON NULL INPUT

Esta cláusula indica que se invoca la función con independencia de si cualquiera de sus argumentos es nulo. Puede devolver un valor nulo o un valor no nulo. En este caso, la función definida por el usuario debe comprobar si los valores de los argumentos son nulos.

Puede especificarse NULL CALL en lugar de CALLED ON NULL INPUT.

INHERIT SPECIAL REGISTERS

Esta cláusula opcional indica que los registros especiales que pueden actualizarse de la función heredarán sus valores iniciales del entorno de la sentencia que realiza la invocación. Para una función que se invoca en la sentencia-select de un cursor, los valores iniciales se heredan del entorno cuando se abre el cursor. Para una rutina que se invoca en un objeto anidado (por ejemplo, un activador o una vista), los valores iniciales se heredan del entorno de ejecución (no de la definición del objeto).

Al proceso que llama a la función no se le devolverá ninguno de los cambios que se han realizado en los registros especiales.

Algunos registros especiales, como los registros especiales de hora y fecha,reflejan una propiedad de la sentencia que se está ejecutando y, por consiguiente, nunca se heredan de quien llama.

PREDICATES

Para los predicados que hacen uso de esta función, esta cláusula indica quiénes pueden explotar las extensiones de índice y pueden utilizar la cláusula opcional SELECTIVITY para la condición de búsqueda del predicado. Si se especifica la cláusula PREDICATES, la función debe definirse como DETERMINISTIC con NO EXTERNAL ACTION (SQLSTATE 42613). Si se especifica la cláusula PREDICATES y la base de datos no es Unicode, no se debe especificar PARAMETER CCSID UNICODE (SQLSTATE 42613). No se puede especificar PREDICATES si el cuerpo-función-SQL es una sentencia de SQL compuesto (compilado) (SQLSTATE 42613).

especificación-predicado: Para obtener información detallada acerca de la especificación del predicado, consulte CREATE FUNCTION (escalar externa).

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST o INHERIT ISOLATION LEVEL WITH LOCK REQUEST

Especifica si una petición de bloqueo puede asociarse o no a la cláusula de aislamiento de la sentencia cuando la función hereda el nivel de aislamiento de la sentencia que invoca la función. El valor por omisión es INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST.

INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST: Especifica que, como la función hereda el nivel de aislamiento de la sentencia que invoca, no se puede invocar en el contexto de una sentencia de SQL que incluya una cláusula de petición de bloqueo como parte de la cláusula de aislamiento especificada (SQLSTATE 42601).
INHERIT ISOLATION LEVEL WITH LOCK REQUEST: Especifica que, como la función hereda el nivel de aislamiento de la sentencia que invoca, también hereda la cláusula de petición de bloqueo especificada.

cuerpo-función-SQL

Especifica el cuerpo de la función. En el cuerpo-función-SQL se puede hacer referencia a nombres de parámetros. Los nombres de parámetros pueden calificarse con el nombre de función para evitar referencias ambiguas.

Para más información sobre la secuencia RETURN, consulte la sección Sentencia RETURN.

Para SQL compuesto (compilado), consulte: Sentencia de SQL compuesto (compilado).

Para SQL compuesto (en línea), consulte: Sentencia de SQL compuesto (en línea).

NOT SECURED o SECURED

Especifica si la función se considera segura para el control de acceso a filas y columnas. El valor por omisión es NOT SECURED.

NOT SECURED: Indica que la función no se considera segura. Cuando se invoca la función, los argumentos de la función no deben hacer referencia a una columna para la que haya habilitada una máscara de columna y el control de acceso de nivel de columna esté activado para su tabla (SQLSTATE 428HA). Esta norma se aplica a las funciones definidas por el usuario no seguras que se invocan en algún punto de la sentencia.
SECURED: Indica que la función se considera segura. La función debe ser segura cuando se hace referencia a ella en un permiso de fila o una máscara de columna (SQLSTATE 428H8).

Normas

Utilización de tipos de datos anclados: Un tipo de datos anclado no puede hacer referencia a los siguientes objetos (SQLSTATE 428HS): un apodo, una tabla con tipo, una vista con tipo, una vista estadística asociada con un índice basado en la expresión, una tabla temporal declarada, una definición de fila asociada con un cursor de tipo débil, un objeto con una página de códigos o una clasificación que es diferente de la página de códigos de la base de datos o la asignación de base de datos.
Uso de tipos de fila y de cursor: una función que utiliza un tipo de cursor o un tipo de fila para un parámetro o que devuelve un tipo de cursor o un tipo de fila sólo puede invocarse desde una sentencia de SQL compuesto (compilado) (SQLSTATE 428H2).
Restricciones de acceso a las tablas: si se define una función como READS SQL DATA, ninguna sentencia de la función podrá acceder a una tabla que la sentencia que invocó la función esté modificando (SQLSTATE 57053). Por ejemplo, supongamos que la función definida por el usuario BONUS() se ha definido como READS SQL DATA. Si se invoca la sentencia UPDATE EMPLOYEE SET SALARY = SALARY + BONUS(EMPNO), no podrá leerse ninguna sentencia de SQL de la función BONUS desde la tabla EMPLOYEE.
Si una función definida con MODIFIES SQL DATA contiene sentencias CALL anidadas, no se permite el acceso de lectura a las tablas que la función está modificando (por la definición de función o la sentencia que ha invocado la función) (SQLSTATE 57053).
Uso en un entorno de base de datos particionada: En un entorno de base de datos particionada, solo puede hacerse referencia a una función escalar definida mediante la utilización de una sentencia de SQL compuesto (compilado) en la parte derecha de una sentencia de asignación, y la referencia de función no puede formar parte de una expresión. Una sentencia de asignación de este tipo no puede aparecer en una sentencia de SQL compuesto (en línea).

Notas

La resolución de las llamadas de función dentro del cuerpo de la función se realiza de acuerdo con la vía de acceso de SQL que está vigente para la sentencia CREATE FUNCTION y no cambia una vez creada la función.
Si una función SQL contiene varias referencias a cualquiera de los registros especiales de fecha u hora, todas las referencias devuelven el mismo valor, y será el mismo valor devuelto por la invocación de registro en la sentencia donde se invocó la función.
El cuerpo de una función SQL no puede contener una llamada recursiva a sí misma o a otra función o método que la llame, dado que una función de este tipo no puede existir para llamarla.
Si un objeto al que se hace referencia en el cuerpo de la función de SQL no existe o se ha marcado como no válido o el definidor no tiene temporalmente los privilegios para acceder al objeto y, si el parámetro de configuración de la base de datos auto_reval no se ha establecido en DISABLED, la función de SQL seguirá creándose satisfactoriamente. La función SQL se marcará como no válida y se volverá a validar la próxima vez que se invoque.
Todas las sentencias que crean funciones o métodos aplican las normas siguientes:
- Una función no puede tener la misma signatura que un método (cuando se compara el primer tipo-parámetro de la función con el tipo-sujeto del método).
- Una función y un método no pueden estar en una relación de alteración temporalmente. Esto significa que si la función fuera un método con su primer parámetro como sujeto, no debe alterar a ni ser alterado temporalmente por otro método. Para obtener más información acerca de los métodos de alteración temporal, consulte la sentencia CREATE TYPE (estructurado).
- Puesto que la alteración temporal no se aplica a las funciones, pueden existir dos funciones de tal forma que, si fueran métodos, una alteraría temporalmente a la otra.
Por lo que respecta a la comparación de tipos de parámetros en las normas anteriores:
- Los nombres de parámetros, longitudes, AS LOCATOR y FOR BIT DATA no se tienen en cuenta.
- Un subtipo se considera que es diferente que su supertipo.
Privilegios: el definidor de una función siempre recibe el privilegio EXECUTE de la función, así como el derecho de descartar la función. El usuario que define la función también recibe WITH GRANT OPTION para la función si dicho usuario dispone de WITH GRANT OPTION para todos los privilegios necesarios para definir la función o si el usuario que define la función dispone de autorización SYSADM o DBADM.
El usuario que define una función sólo adquiere privilegios si, en el momento de crearse la función, existen los privilegios de los que estos privilegios se obtienen. El usuario que define el método debe disponer de estos privilegios directamente o bien porque PUBLIC tiene los privilegios. Los privilegios que tienen los grupos de los que es miembro el usuario que define la función no se consideran. Cuando se utiliza la función, el ID de autorización del usuario conectado debe disponer de los privilegios válidos para la tabla o vista a la que hace referencia el apodo en la fuente de datos.
Establecimiento del valor por omisión: los parámetros de una función que están definidos con un valor por omisión se establecen en su valor por omisión cuando se invoca la función, aunque sólo si no se suministra un valor para el argumento correspondiente o se especifica como DEFAULT, cuando se invoca la función.
Funciones EXTERNAL ACTION: Si se invoca una función EXTERNAL ACTION en otra lista que no sea la lista de selección más externa, los resultados son imprevisibles, ya que el número de veces que se invoca la función variará en función del plan de acceso utilizado.
Creación de una función segura: Por lo general, los usuarios con autorización SECADM no tienen privilegios para crear objetos de base de datos, como activadores o funciones. Suelen examinar los datos a los que accede una función, comprobar que son seguros y, a continuación, otorgar la autorización CREATE_SECURE_OBJECT a quien ya tenga los privilegios necesarios para crear una función segura definida por el usuario. Una vez creada la función, revocarán la autorización CREATE_SECURE_OBJECT al propietario de la función.
El atributo SECURED se considera una aserción que declara que el usuario ha establecido un procedimiento de auditoría de control de todos los cambios en la función definida por el usuario. El gestor de bases de datos presupone que ese procedimiento de auditoría de control está en vigor para todas las sentencias ALTER FUNCTION posteriores o cambios en los paquetes externos.
Invocación de otras funciones definidas por el usuario en una función segura: Si una función segura definida por el usuario invoca otras funciones definidas por el usuario, el gestor de bases de datos no valida si esas funciones definidas por el usuario anidadas tienen el atributo SECURED. Si esas funciones anidadas pueden acceder a datos confidenciales, el usuario con autorización SECADM tiene que garantizar que esas funciones pueden acceder a esos datos y que se ha establecido un procedimiento de auditoría de control de cambios para todos los cambios en esas funciones.
Sustitución de una función existente de manera que el atributo seguro cambia (de SECURED a NOT SECURED y viceversa): Los paquetes y las sentencias de SQL colocadas dinámicamente en memoria caché que dependen de la función pueden quedar invalidadas porque el atributo seguro afecta a la selección de la vía de acceso de las sentencias que implican tablas para las que se ha activado el control de acceso de nivel de fila o columna.
Volver a vincular paquetes dependientes: Cada función SQL compilada tiene un paquete dependiente. El paquete se puede volver a vincular en cualquier momento utilizando el procedimiento REBIND_ROUTINE_PACKAGE. Volver a vincular el paquete dependiente no revalida una función no válida. Revalide una función no válida con una revalidación automática o utilizando explícitamente el procedimiento ADMIN_REVALIDATE_DB_OBJECTS. La revalidación de función vuelve a vincular automáticamente el paquete dependiente.
Alternativas de sintaxis: La sintaxis siguientes solo está soportada para la compatibilidad entre productos. Estas alternativas no son estándar y no deberían utilizarse:
- NULL CALL puede especificarse en lugar de CALLED ON NULL INPUT
La sintaxis siguiente se acepta como comportamiento por omisión:
- CCSID UNICODE en una base de datos Unicode
- CCSID ASCII en una base de datos que no es Unicode

Ejemplos

Ejemplo 1: Defina una función escalar que devuelve la tangente de un valor utilizando las funciones de seno y coseno existentes.

   CREATE FUNCTION TAN (X DOUBLE)
     RETURNS DOUBLE
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN SIN(X)/COS(X)

Ejemplo 2: Defina una función de transformación para el tipo estructurado PERSON.

     
   CREATE FUNCTION FROMPERSON (P PERSON)
     RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN VALUES (P..NAME, P..FIRSTNAME)

Ejemplo 3: Defina una tabla de función que muestra los empleados que trabajan en un número de departamento especificado.

     
   CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
         FROM EMPLOYEE
         WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO

Ejemplo 4: Defina la función de tabla del Ejemplo 3 con auditoría.

   CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     MODIFIES SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
     BEGIN ATOMIC
       INSERT INTO AUDIT 
       VALUES (USER, 
               'Table: EMPLOYEE Prd: DEPTNO = ' CONCAT DEPTNO);
       RETURN
         SELECT EMPNO, LASTNAME, FIRSTNME
           FROM EMPLOYEE
           WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO
     END

Ejemplo 5: Defina una función escalar que invierta una serie de caracteres.

   CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
     RETURNS VARCHAR(4000)
     DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
     BEGIN ATOMIC
     DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
     DECLARE LEN INT;
     IF INSTR IS NULL THEN
     RETURN NULL;
     END IF;
     SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
     WHILE LEN > 0 DO
     SET (REVSTR, RESTSTR, LEN) 
       = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
       SUBSTR(RESTSTR, 2, LEN - 1),
       LEN - 1);
     END WHILE;
     RETURN REVSTR;
   END

Ejemplo 6: crear una función que incremente una variable pasada como parámetro INOUT y devuelva un error como código de retorno.

   CREATE FUNCTION increment(INOUT result INTEGER, IN delta INTEGER)
     RETURNS INTEGER
     BEGIN
       DECLARE code INTEGER DEFAULT 0;
       DECLARE SQLCODE INTEGER;
       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
       SET code = SQLCODE;
       RETURN code;
     END;
     SET result = result + delta;
     RETURN code;
   END@

Ejemplo 7: Crear una función de SQL compilado que tome un documento XML como entrada y devuelva el nombre de cliente.

   CREATE FUNCTION get_customer_name_compiled(doc XML)
     RETURNS VARCHAR(25)
     BEGIN
       RETURN XMLCAST(XMLQUERY
          ('$d/customerinfo/name' PASSING doc AS "d")AS VARCHAR(25));
     END

Ejemplo 8: Crear una función de SQL compilado que tome un número de teléfono y un número de región pasado como parámetros IN y devuelva el número completo en un parámetro OUT de XML.

   CREATE FUNCTION construct_xml_phone
     (IN  phoneNo VARCHAR(20),
      IN  regionNo VARCHAR(8),
      OUT full_phone_xml XML)
     RETURNS VARCHAR(28)
     LANGUAGE SQL
     NO EXTERNAL ACTION
     BEGIN
       SET full_phone_xml = XMLELEMENT (NAME "phone", regionNo || phoneNo);
       RETURN regionNo || phoneNo;
     END