User-Defined Functions

---- Start

We can create five types of user-defined functions:

1. Source (or template) <br/> 2. SQL scalar, table or row <br/> 3. External scalar <br/> 4. External table <br/> 5. OLE DB External table 

I. source function: sourced Function

As the name suggests, an active function is a function constructed from an existing function. It is usually used to operate user-defined data types. Because existing functions and operators (+-*/) do not support user-defined data types, when we need to operate user-defined data types, you can define an active function based on the existing function.

1. Syntax:

Create Function [functionname] (<[parametername]> [inputdatatype],...>) <br/> Returns [outputdatatype] <br/> <specific [specificname]> <br/> source [sourcefunction] <([datatype],...)> <br/> <as template> </P> <p> Note: <br/> functionname indicates the name of the source function to be created. <Br/> parametername specifies the name of one or more function parameters. <Br/> inputdatatype specifies the data type required for parameters recognized by parametername. <Br/> outputdatatype indicates the type of data returned by the function. <Br/> specificname specifies the specific name assigned to this UDF. This name can be used to reference or delete a function. However, it cannot be used to call a function. <Br/> sourcefunction specifies the existing function used to create the source function. <Br/> datatype specifies the data type that each parameter of an existing function expects to receive.

2. Example:

-- Connect to the database <br/> connect to sample; </P> <p> -- defines the USD data type <br/> create distinct type us_dollars as DEC () with comparisons; </P> <p> -- Define a table <br/> Create Table MERs <br/> (<br/> ID smallint not null, <br/> balance us_dollars not null <br/>); </P> <p> -- insert value <br/> insert into MERs values (1,111.11), (2,222.22 ); </P> <p> -- Query SQL <br/> select ID, balance * 10 from MERs MERS; </P> <p> -- the preceding statement has an exception, the reason operator * does not support us_dollars. To execute the preceding statement, define the following function: <br/> create function "*" (us_dollars, INT) <br/> Returns us_dollars <br/> source sysibm. "*" (decimal, INT); </P> <p> -- run the query SQL statement again <br/> select ID, balance * 10 from MERs MERS; </P> <p> -- delete a user-defined function <br/> drop function "*" (us_dollars, INT ); </P> <p> -- delete a table <br/> drop table MERs MERS; </P> <p> -- delete a dollar data type <br/> drop distinct type us_dollars; </P> <p> -- disconnect <br/> connect reset;

3. Running example:

Save the above Code as C:/test. SQL, and then execute the following command in the DB2 command window <br/> DB2-tvf C:/test. SQL 

Ii. SQL scalar, table, or row Function

Scalar functionsOnly one value is returned. The insert, update, and delete statements are not allowed in scalar functions.

Table FunctionsReturns a table or several rows and calls them in the from clause. Unlike scalar functions, table functions can use insert, update, and delete statements.

1. Syntax

Create Function [functionname] (<[parametername]> [inputdatatype],...>) <br/> Returns [[outputdatatype] | <br/> table ([columnname] [columndatatype],...) | <br/> row ([columnname] [columndatatype],...)] <br/> <specific [specificname]> <br/> <language SQL> <br/> <deterministic | not deterministic> <br/> <External Action | no external action> <br/> <contains SQL | reads SQL data | modifies SQL data> <br/> <static dispatch> <br/> <called on Null input> <br/> [sqlstatements] | return [returnstatement] </P> <p> note: <br/> functionname indicates the name of the SQL function to be created. <Br/> parametername specifies the name of one or more function parameters. <Br/> inputdatatype specifies the data type required for parameters recognized by parametername. <Br/> outputdatatype indicates the type of data returned by the function. <Br/> columnname specifies the name of one or more columns returned by the function (if this function returns a table or row ). <Br/> columndatatype specifies the data type returned by columnname. <Br/> specificname specifies the specific name assigned to this UDF. This name can be used to reference or delete a function. However, it cannot be used to call a function. <Br/> sqlstatements indicates one or more SQL statements executed when a function is called. These statements are combined into a dynamic composite SQL statement. <Br/> returnstatement indicates the return SQL statement of the application that calls the function. (If an SQL function is composed of dynamic composite statements, it must contain at least one return statement. When calling a function, it must execute one return statement. If a function is a table function or a row function, only one return statement can be included, and this statement must be the last statement used ). <Br/> <language SQL> the specified function is written in SQL. <Br/> <deterministic | not deterministic> indicates whether the function always returns the same scalar value, table, or row when calling a function with the same parameter value (deterministic indicates a deterministic function, not deterministic indicates a non-deterministic function ). If these two clauses are not specified, the function may return different results when calling a function with the same parameter value. <Br/> <External Action | no external action> indicates whether the operations performed by the function change the state of objects not managed by DB2. (External Action indicates that the state of objects is changed, no external action indicates no change ). External operations include sending emails or writing records in external files. If these two clauses are not specified, this means that the function may execute some external operation. <Br/> <contains SQL | reads SQL data | modifies SQL data> indicates the type of SQL statements written in the UDF body. Three values are available: <br/> contains SQL: the executable SQL statements in the UDF body do not read or modify data. <Br/> reads SQL data: Contains executable SQL statements in the UDF body that read data without modifying the data. <Br/> modifies SQL data: the executable SQL statement contained in the UDF can read or modify data. <Br/> <static dispatch> indicates that DB2 selects a function based on the static type (declared type) of function parameters during function parsing. <Br/> <called on Null input> This function is called no matter whether or not any parameter contains a null value. 

2. Example

-- Connect to the database <br/> connect to sample! </P> <p> -- create a scalar function <br/> Create Function check_len (instr varchar (50 )) <br/> Returns smallint <br/> begin atomic <br/> If instr is null then <br/> return NULL; <br/> end if; </P> <p> If length (instr) <6 then <br/> signal sqlstate '000000' set message_text = 'input string is <6 '; <br/> elseif length (instr) <7 then <br/> return-1; <br/> end if; </P> <p> return length (instr ); <br/> end! </P> <p> -- test the scalar function <br/> values check_len ('wave ')! <Br/> values check_len ('123 ')! </P> <p> -- delete a scalar function <br/> drop function check_len! </P> <p> -- create a table function <br/> Create Function testtf () <br/> Returns table (ID smallint, name varchar (9 )) <br/> return select * from (values (1, 'zhang san'), (2, 'Li si '))! </P> <p> -- test table function: You need to apply the table () function and alias <br/> select * from table (testtf () as temp! </P> <p> -- delete a table function <br/> drop function testtf! </P> <p> -- disconnect <br/> connect reset;

3. Running example:

Save the above Code as C:/test. SQL, and then execute the following command in the DB2 command window <br/> DB2-Td! -Vf c:/test. SQL

Iii. Other functions

External scalar functions, External table functions, and Microsoft ole db External table functions are written in C, C ++, Java, and other advanced external programming languages, for more details, see DB2 Information Center.

---- For more information, see:SQL pl

----Statement: indicate the source for reprinting.

---- Last updated on 2010.2.9

---- Written by shangbo on 2010.2.8

---- End