CREATE function-body
function-body::=FUNCTION [authorization-identifier.] routine-identifier
([SQL-parameter-name data-type
[, SQL-parameter-name data-type] ...])
RETURNS data-type
[LANGUAGE {SQL|JAVA}]
[SQL-compilation-option]
{SQL-procedure-statement
|external-routine-specification}
SQL-compilation-option::=SUBSTR LENGTH maximum-character-length
external-routine-specification ::= EXTERNAL NAME external-routine-name
PARAMETER STYLE parameter-style
parameter-style ::= JAVA
- [authorization-identifier.] routine-identifier
- authorization-identifier
- Specifies the authorization identifier of the owner of the function that is being defined.
- routine-identifier
- Specifies a routine identifier for the function being defined. The same routine identifier can be used in all the owner's routines. However, the owner of the function being defined cannot define routines that are identical in terms of the routine identifier, number of SQL parameters, and positions at which the data types of the SQL parameters occur.
- ([SQL-parameter-name data-type [,SQL-parameter-name data-type] ...])
- SQL-parameter-name
- Specifies the name of a parameter for the function. The same SQL parameter name cannot be specified more than once for the same function.
- data-type
- Specifies the data type of the paired parameter for the function. The BOOLEAN data type cannot be specified.
- If the specified data type is an abstract data type, no authorization identifier is specified, and the default authorization identifier does not have an abstract data type of the same name, and if there is an abstract data type of the same name in the 'MASTER' authorization identifier, that abstract data type is assumed to have been specified.
- When JAVA is specified in the LANGUAGE clause, an abstract data type cannot be specified as a data type. See 1.9.2 Type mapping for the specifiable data types.
- data-type
- Specifies the data type for return values of the function.
- The following data types cannot be specified:
- BOOLEAN
- An abstract data type when JAVA is specified in the LANGUAGE clause (see 1.9.2 Type mapping for the specifiable data types).
- If the data type being specified is the abstract data type and the authorization identifier is omitted, and the default authorization identifier does not have an abstract data type of the same name, the specified abstract data type is assumed, provided that the authorization identifier 'MASTER' has an identically named abstract data type.
Specifies the language used to write the function.
For an external routine, specify JAVA.
- SQL
- Specifies that the processing part of the function is made up of SQL statements.
- JAVA
- Specifies that the processing part of the function is specified as an external routine and the function is to be implemented as a Java class method.
Which of these operands is specified determines the other operands that can be specified. Table 3-19 shows the operands that can be specified in conjunction with the LANGUAGE clause.
Table 3-19 Specifiable operands depending on the specification of the LANGUAGE clause of CREATE FUNCTION
Other operand | LANGUAGE clause specification |
---|
SQL | JAVA |
---|
SQL parameter data type | Y | Y1 |
RETURNS clause data type | Y2 | Y1, 2 |
EXTERNAL NAME | ![[Figure]](figure/zueng033.gif) | Y |
PARAMETER STYLE | ![[Figure]](figure/zueng033.gif) | Y |
SQL procedure statement | Y | ![[Figure]](figure/zueng033.gif) |
1 Governed by the type mapping rules; for details of type mapping, see 1.9.2 Type mapping.
2 BOOLEAN cannot be specified.
- SQL-compilation-option::=SUBSTR LENGTH maximum-character-length
- [SUBSTR LENGTH maximum-character-length]
- Specifies the maximum number of bytes for representing a single character.
- The value specified for the maximum character length must be in the range from 3 to 6.
- This operand is valid only when utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of the UNIX version); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(21) SUBSTR.
- Relationships to system definition
- When SUBSTR LENGTH is omitted, the value specified in the pd_substr_length operand in the system definition is assumed. For details about the pd_substr_length operand, see the manual HiRDB Version 8 System Definition.
- Relationship to client environmental definition
- The specification of PDSUBSTREN has no applicability to CREATE FUNCTION. For details about PDSUBSTRLEN, see the manual HiRDB Version 8 UAP Development Guide.
- Relationship to the character code type specified in the pdntenv or pdsetup command
- This operand is valid only when utf-8 is specified for the character code type.
- For all other character code types, only a syntax check is performed and the specification is ignored.
Specifies the SQL procedure statements to be executed by the SQL function (for details of SQL procedure statements, see the General rules section in 7. Routine Control SQL). Only compound statements can be specified in an SQL procedure statement. The last SQL procedure statement executed in an SQL function must be the RETURN statement.
- EXTERNAL NAME external-routine-name
Specifies the external routine, written in Java, that constitutes a Java method. For the external routine name specification conventions, see 1.9.1 Specification of external routines.
- PARAMETER STYLE parameter-style
Specifies items to be passed as parameters when an external routine is called.
Java function parameters that are defined as an SQL data type are passed as Java method parameters in the Java data type that is associated with the SQL data type.
Return values from the Java method defined in the Java data type are returned as return values from the Java function of the SQL data type associated with the Java data type.