CREATE procedure-body
procedure-body::=PROCEDURE [authorization-identifier.] routine-identifier
([{IN|OUT|INOUT} SQL-parameter-name data-type
[, {IN|OUT|INOUT}
SQL-parameter-name data-type]...])
[DYNAMIC RESULT SETS number-of-results-sets]
[LANGUAGE clause]
[SQL-compile-option[SQL-compile-option]...]
{SQL-procedure-statements |external-routine-specification}
LANGUAGE clause ::= LANGUAGE {SQL|JAVA}
SQL-compile-option ::= {ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
|OPTIMIZE LEVEL SQL-optimization-option
[,SQL-optimization-option]...
|ADD OPTIMIZE LEVEL SQL-extension-optimizing-option
[,SQL-extension-optimizing-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 user who owns the procedure being defined.
- routine-identifier
- Specifies the name of a routine in the procedure being defined. The routine identifier must be unique among the procedures owned by this owner.
- ([{IN|OUT|INOUT}SQL-parameter-name data-type [, {IN|OUT|INOUT}SQL-parameter-name data-type]...])
- {IN|OUT|INOUT}
- I/O mode (parameter mode) must be specified for procedure parameters.
- IN
- This keyword specifies that the parameter is an input parameter.
- OUT
- This keyword specifies that the parameter is an output parameter.
- INOUT
- This keyword specifies that the parameter is an input/output parameter.
- SQL-parameter-name
- Specifies the name of the parameter in the procedure. SQL parameter names must be unique within a procedure.
- data-type
- Specifies the data type of the parameter in the procedure.
- 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.
- DYNAMIC RESULT SETS number-of-results-sets
Specifies as an integer the maximum number of results sets that can be returned by the procedure that is being defined.
The specifiable range of values is 0 to 1023. When 0 is specified or this operand is omitted, the default is that the procedure does not return a results set.
Specifies the language used to write the function.
For an external routine, SQL must not be specified.
- 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-22 shows the operands that can be specified in conjunction with the LANGUAGE clause.
Table 3-22 Specifiable operands depending on the specification of the LANGUAGE clause of CREATE PROCEDURE
Other operand | LANGUAGE clause specification |
---|
SQL | JAVA |
---|
SQL-parameter data-type | Y | Y1 |
DYNAMIC RESULTS SETS | Y | Y |
EXTERNAL NAME | ![[Figure]](figure/zueng033.gif) | Y |
PARAMETER STYLE | ![[Figure]](figure/zueng033.gif) | Y |
SQL-procedure-statement | Y | ![[Figure]](figure/zueng033.gif) |
- Legend:
- Y: Specifiable
: Not specifiable
1 Governed by the type mapping rules; for details of type mapping, see 1.9.2 Type mapping.
- SQL-compile-option::={ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
|OPTIMIZE LEVEL SQL-optimization-option
[,SQL-optimization-option]...
|ADD OPTIMIZE LEVEL SQL-extension-optimizing-option
[,SQL-extension-optimizing-option]...
|SUBSTR LENGTH maximum-character-length}
In SQL-compile-option, ISOLATION, OPTIMIZE LEVEL, ADD OPTIMIZE LEVEL, and SUBSTR LENGTH can each be specified only once.
- ISOLATION data-guarantee-level [FOR UPDATE EXCLUSIVE]
- Specifies an SQL data integrity guarantee level.
- data-guarantee-level
- A data integrity guarantee level specifies the point to which the integrity of the transaction data must be guaranteed. The following data integrity guarantee levels can be specified:
- 0
Do not guarantee data integrity. Specifying 0 for a set of data allows the user to reference the data even when it is being updated by another user. However, if the table to be referenced is a shared table, and another user is executing the LOCK statement in the lock mode, the system waits until the lock condition is released.
- 1
Guarantee the integrity of data until a retrieval process is completed. When level 1 is specified, data that has been retrieved cannot be updated by other users until the retrieval process is completed (until HiRDB finishes viewing the current page or row).
- 2
Guarantee the integrity of data that has been retrieved until the transaction is completed. When level 2 is specified, data that has been retrieved cannot be updated by other users until the transaction is completed.
- [FOR UPDATE EXCLUSIVE]
- Specify this operand if WITH EXCLUSIVE LOCK is always to be assumed, irrespective of the data guarantee level specified in SQL-compile-option for a cursor or query in a procedure for which the FOR UPDATE clause is specified or assumed. If level 2 is specified in data-guarantee-level, WITH EXCLUSIVE LOCK is assumed for the cursor or query in a procedure for which the FOR UPDATE clause is specified or assumed, in which case it is not necessary to specify FOR UPDATE EXCLUSIVE.
- Relationship with client environment definition
- Any specification of PDISLLVL or PDFORUPDATEEXLOCK with respect to CREATE PROCEDURE has no effect.
- Relationship to SQL statements
- If a lock option is specified in an SQL statement in a procedure, the lock option specified in the SQL statement takes precedence over any data guarantee level specified in SQL-compile-option or the lock option assumed because of FOR UPDATE EXCLUSIVE.
- The default for data-guarantee-level is 2.
- For data guarantee levels, see the HiRDB Version 8 UAP Development Guide.
- OPTIMIZE LEVEL SQL-optimization-option[,SQL-optimization-option]...
- Specifies the optimization method for determining the most efficient access path by taking the database's status into consideration.
- SQL optimization options can be specified with identifiers (character strings) or numeric values. Hitachi recommends that identifiers be used.
- Specification with identifiers:
OPTIMIZE LEVEL "identifier"[, "identifier"] ...
- Specification examples
- Apply the Prioritized nest-loop-join and the Rapid grouping facility options:
OPTIMIZE LEVEL "PRIOR_NEST_JOIN",
"RAPID_GROUPING"
- Do not apply any optimization:
OPTIMIZE LEVEL "NONE"
- Rules
- At least one identifier must be specified.
- When multiple identifiers are specified, separate them with the comma (,).
- For the optimization option identifiers, see Table 3-23 SQL optimization option specification values below.
- If no optimization options are to be applied, specify "NONE" as the identifier. If "NONE" and some other identifier are both specified, the "NONE" specification is ignored.
- The identifiers are case-sensitive.
- If the same identifier is specified more than once, it is treated as if it was specified only once; however, where possible, precautions should be taken to avoid specifying a given identifier in duplicate.
- Specification with numeric values:
OPTIMIZE LEVEL unsigned-integer[, unsigned-integer] ...
- Specification examples
- Apply the 2. Making multiple SQL objects, 8. Suppressing use of AND multiple indexes, and 13. Forcing use of multiple indexes options:
Specify unsigned integers separated by commas:
OPTIMIZE LEVEL 4,10,16
- Specify a sum of unsigned integers:
OPTIMIZE LEVEL 30
- Add the new value 16 to the previously specified value 14 (4 + 10):
OPTIMIZE LEVEL 14,16
- Do not apply any optimization:
OPTIMIZE LEVEL 0
- Rules
- When HiRDB is upgraded from a version older than Version 06-00 to a Version 06-00 or later, the total value specification in the older version also remains valid. If the optimization option does not need to be modified, the specification value for this operand need not be changed when HiRDB is upgraded to a Version 06-00 or later.
- At least one integer must be specified.
- When multiple integers are specified, separate them with the comma (,).
- For the optimization option integers, see Table 3-23 SQL optimization option specification values below.
- If no optimization options are to be applied, specify 0 as the integer. If 0 and another integer are both specified, the 0 specification is ignored.
- If the same integer is specified more than once, it will be treated as a single instance of the integer. However, avoid multiple specifications of the same integer
- When multiple optimization options are to be applied, you can specify the sum of the appropriate unsigned integers. However, the same optimization option value must not be added in more than once to avoid the possibility of the addition result being interpreted as a different set of optimization options.
- Specifying multiple optimization options by adding their values can be ambiguous as to which optimization options are actually intended, so Hitachi recommends that the option values be specified individually separated by commas. If multiple optimization option have already been specified by the addition method and another optimization option is required, you can specify the new option's value following the previous summed value by separating them with a comma.
- Relationships to system definition
- If no SQL optimization option values are specified, the values specified in the pd_optimize_level operand of the system definition are assumed as the default. For details of the pd_optimize_level operand, see the manual HiRDB Version 8 System Definition.
- When the pd_floatable_bes operand or the pd_non_floatable_bes operand is specified, specification of the Increasing the target floatable servers (back-end servers for fetching data) option or the Limiting the target floatable servers (back-end servers for fetching data) option, respectively, is invalid.
- When KEY is specified in the pd_indexlock_mode operand of the system definition (i.e., in the case of index key value lock), specification of the Suppressing creation of update-SQL work tables option is invalid.
- Relationship to client environment definition
- The specification of PDSQLOPTLVL has no applicability to CREATE PROCEDURE.
- Relationship with SQL
- If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
- SQL optimization option specification values
- Table 3-23 shows the SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 8 UAP Development Guide.
Table 3-23 SQL optimization option specification values (CREATE PROCEDURE)
No. | Optimization option | Specification values |
---|
Identifier | Unsigned integer |
---|
1 | Forced nest-loop-join | FORCE_NEXT_JOIN | 4 |
2 | Making multiple SQL objects | SELECT_APSL | 10 |
3 | Increasing the target floatable servers (back-end servers for fetching data)#1, #2 | FLTS_INC_DATA_BES | 16 |
4 | Prioritized nest-loop-join | PRIOR_NEST_JOIN | 32 |
5 | Increasing the number of floatable server candidates#2 | FLTS_MAX_NUMBER | 64 |
6 | Priority of OR multiple index use | PRIOR_OR_INDEXES | 128 |
7 | Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server#2 | SORT_DATA_BES | 256 |
8 | Suppressing use of AND multiple indexes | DETER_AND-INDEXES | 512 |
9 | Rapid grouping facility | RAPID_GROUPING | 1024 |
10 | Limiting the target floatable servers (back-end servers for fetching data)#1, #2 | FLTS_ONLY_DATA_BES | 2048 |
11 | Separating data collecting servers#1,#2 | FLTS_SEPARATE_COLLECT_SVR | 2064 |
12 | Suppressing index use (forced table scan) | FORCE_TABLE_SCAN | 4096 |
13 | Forcing use of multiple indexes | FORCE_PLURAL_INDEXES | 32768 |
14 | Suppressing creation of update-SQL work tables | DETER_WORK_TABLE_FOR_UPDATE | 131072 |
15 | Deriving rapid search conditions | "DERIVATIVE_COND" | 262144 |
16 | Applying key conditions, including scalar operations | "APPLY_ENHANCED_KEY_COND" | 524288 |
17 | Facility for batch acquisition from functions provided by plug-ins | "PICKUP_MULTIPLE_ROWS_PLUGIN" | 1048576 |
- #1: If the 3. Increasing the target floatable servers (back-end servers for fetching data) option and the 10. Limiting the target floatable servers (back-end servers for fetching data) option are both specified, neither of these options will be applied; instead, the 11. Separating data collecting servers option will be applied.
- #2: This option is ignored if specified for a HiRDB/Single Server.
- ADD OPTIMIZE LEVEL SQL-extension-optimizing-option[,SQL-extension-optimizing-option]...
- Specifies optimizing options for determining the most efficient access path, taking into consideration the status of the database.
- SQL extension optimizing options can be specified with identifiers (character strings) or numeric values. Hitachi recommends that identifiers be used.
- Specification with identifiers:
ADD OPTIMIZE LEVEL "identifier"[, "identifier"] ...
- Specification examples
- Apply the Application of optimizing mode 2 based on cost and Hash join, subquery hash execution options:
ADD OPTIMIZE LEVEL
"COST_BASE_2","APPLY_HASH_JOIN"
- Do not apply any optimizing:
ADD OPTIMIZE LEVEL "NONE"
- Rules
- At least one identifier must be specified.
- When multiple identifiers are specified, separate them with the comma (,).
- For details of specifying optimization option identifiers, see Table 3-24 SQL extension optimizing option specification values below.
- If no extension optimizing options are to be applied, specify "NONE" as the identifier.
- The identifiers are case-sensitive.
- If the same identifier is specified more than once, it is treated as if it was specified only once; however, where possible, precautions should be taken to avoid specifying a given identifier in duplicate.
- Specification with numeric values:
ADD OPTIMIZE LEVEL unsigned-integer[, unsigned-integer] ...
- Specification examples
- Apply the Application of optimizing mode 2 based on cost and Hash join, subquery hash execution options:
ADD OPTIMIZE LEVEL 1,2
- Do not apply any optimizing:
ADD OPTIMIZE LEVEL 0
- Rules
- At least one integer must be specified.
- When multiple integers are specified, separate them with the comma (,).
- For details of specifying extension optimizing option integers, see Table 3-24 SQL extension optimizing option specification values below.
- If no extension optimizing options are to be applied, specify 0 as the integer.
- If the same integer is specified more than once, it will be treated as a single instance of the integer. However, multiple specifications of the same integer should be avoided.
- Relationship to the system definition
- If no SQL extension optimizing option values are specified, the values specified in the pd_additional_optimize_level operand of the system definition are assumed as the default. For details of the pd_additional_optimize_level operand, see the manual HiRDB Version 8 System Definition.
- Relationship to the client environment definition
- The specification of PDADDITIONALOPTLVL has no applicability to CREATE PROCEDURE.
- Relationship with SQL
- If SQL optimization is specified in an SQL statement, the SQL optimization specification takes precedence over SQL optimization options. For SQL optimization specifications, see 2.24 SQL optimization specification.
- SQL extension optimizing option specification values
- Table 3-24 shows the SQL extension optimizing option specification values. For details about optimization methods, see the HiRDB Version 8 UAP Development Guide.
Table 3-24 SQL extension optimizing option specification values (CREATE PROCEDURE)
No. | Optimizing option | Specification values |
---|
Identifier | Unsigned integer |
---|
1 | Application of optimizing mode 2 based on cost | "COST_BASE_2" | 1 |
2 | Hash join, subquery hash execution | "APPLY_HASH_JOIN" | 2 |
3 | Suppressing the execution of a foreign server in SQL statements that contain joins | "DETER_JOIN_SQL" | 67108864 |
4 | Forcing the execution of SQL statements including a direct product on a foreign server | "FORCE_CROSS_JOIN_SQL" | 134217728 |
5 | Suppressing the derivation of rapid search that can be generated unconditionally and executed on a foreign server | "DETER_FSVR_DERIVATIVE_COND" | 1073741824 |
- Note 1
- Items 2-5 take effect when Application of optimizing mode 2 based on cost is specified.
- Note 2
- Optimization items 3-5 take effect when a foreign table is retrieved; in other cases, these items have no effect.
- [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 PROCEDURE. 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 in the procedure. For details of SQL procedure statements, see the General rules section in 7. Routine Control SQL.
- 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 procedure 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.
The OUT and INOUT parameters for Java procedures in SQL data type definitions are of the Java data type for the SQL data type; these parameters are passed as Java method parameters, as an array of one element. After the Java method has terminated, any array written by the Java method is treated as output parameters of the Java procedure.