CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure)

Function

CREATE PROCEDURE defines a procedure.

CREATE PROCEDURE can also be used to define public procedures, which are available to all users without them having to qualify the routine identifier with an authorization identifier.

(1) CREATE PROCEDURE (Define procedure)

Privileges

Users who own a schema

These users can define their own procedures.

Format

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|C}
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-Java-routine-name|external-C-stored-routine-name}
                            PARAMETER STYLE parameter-style
parameter-style ::= {JAVA|RDSQL}

Operands

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.
{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.
The following data types cannot be specified:
  • Abstract data type when JAVA or C is specified in the LANGUAGE clause
  • BINARY or BLOB type when C is specified in the LANGUAGE clause
For details about the data types that can be specified, see 1.10.1(2) Type mapping or CREATE [PUBLIC] FUNCTION (Define function, define public function) in this chapter.

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.

However, if C is specified in the LANGUAGE clause, the value specified in this operand is ignored.

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.
C
Specifies that the processing part of the procedure is specified as an external routine and the procedure is to be implemented as a C module. When C is specified, the maximum number of SQL parameters is limited to 128.

Whether other operands need to be specified depends on what is specified for this operand. The following table indicates whether other operands need to be specified depending on the LANGUAGE clause specification.

Table 3-24 Whether other operands need to be specified depending on the LANGUAGE clause specified in CREATE PROCEDURE

Other operandLANGUAGE clause specification
SQLJAVAC
EXTERNAL NAMENYY
PARAMETER STYLENJAVARDSQL
SQL-procedure-statementYNN
Legend:
Y: Must be specified.
N: Cannot be specified.
JAVA: Specify JAVA.
RDSQL: Specify RDSQL.

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 9 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
  1. At least one identifier must be specified.
  2. When multiple identifiers are specified, separate them with the comma (,).
  3. For details about the contents that can be specified in an identifier (optimization methods), see Table 3-25 SQL optimization option specification values (CREATE PROCEDURE).
  4. 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.
  5. The identifiers are case-sensitive.
  6. 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
  1. When HiRDB is upgraded from a version earlier than Version 06-00 to a Version 06-00 or later, the total value specification in the earlier 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.
  2. At least one integer must be specified.
  3. When multiple integers are specified, separate them with the comma (,).
  4. For details about the contents that can be specified in an unsigned integer (optimization methods), see Table 3-25 SQL optimization option specification values (CREATE PROCEDURE).
  5. 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.
  6. 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
  7. 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.
  8. 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
  1. 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 9 System Definition.
  2. 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.
  3. 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
The following table lists the SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-25 SQL optimization option specification values (CREATE PROCEDURE)

No.Optimization optionSpecification values
IdentifierUnsigned integer
1Forced nest-loop-joinFORCE_NEXT_JOIN4
2Making multiple SQL objectsSELECT_APSL10
3Increasing the target floatable servers (back-end servers for fetching data)#1, #2FLTS_INC_DATA_BES16
4Prioritized nest-loop-joinPRIOR_NEST_JOIN32
5Increasing the number of floatable server candidates#2FLTS_MAX_NUMBER64
6Priority of OR multiple index usePRIOR_OR_INDEXES128
7Group processing, ORDER BY processing, and DISTINCT set function processing at the local back-end server#2SORT_DATA_BES256
8Suppressing use of AND multiple indexesDETER_AND-INDEXES512
9Rapid grouping facilityRAPID_GROUPING1024
10Limiting the target floatable servers (back-end servers for fetching data)#1, #2FLTS_ONLY_DATA_BES2048
11Separating data collecting servers#1,#2FLTS_SEPARATE_COLLECT_SVR2064
12Suppressing index use (forced table scan)FORCE_TABLE_SCAN4096
13Forcing use of multiple indexesFORCE_PLURAL_INDEXES32768
14Suppressing creation of update-SQL work tablesDETER_WORK_TABLE_FOR_UPDATE131072
15Deriving rapid search conditions"DERIVATIVE_COND"262144
16Applying key conditions, including scalar operations"APPLY_ENHANCED_KEY_COND"524288
17Facility for batch acquisition from functions provided by plug-ins"PICKUP_MULTIPLE_ROWS_PLUGIN"1048576​
18Facility for moving search conditions into derived table"MOVE_UP_DERIVED_COND"2097152​
#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
  1. At least one identifier must be specified.
  2. When multiple identifiers are specified, separate them with the comma (,).
  3. For details about the contents that can be specified in an identifier (optimization methods), see Table 3-26 SQL extension optimizing option specification values (CREATE PROCEDURE).
  4. If no extension optimizing options are to be applied, specify "NONE" as the identifier.
  5. The identifiers are case-sensitive.
  6. 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
  1. At least one integer must be specified.
  2. When multiple integers are specified, separate them with the comma (,).
  3. For details about the contents that can be specified in an unsigned integer (optimization methods), see Table 3-26 SQL extension optimizing option specification values (CREATE PROCEDURE).
  4. If no extension optimizing options are to be applied, specify 0 as the integer.
  5. 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 9 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
The following table lists the SQL optimization option specification values. For details about optimization methods, see the HiRDB Version 9 UAP Development Guide.

Table 3-26 SQL extension optimizing option specification values (CREATE PROCEDURE)

No.Optimizing optionSpecification values
IdentifierUnsigned integer
1Application of optimizing mode 2 based on cost"COST_BASE_2"1
2Hash join, subquery hash execution"APPLY_HASH_JOIN"2
3Facility for applying join conditions including value expression"APPLY_JOIN_COND_FOR_VALUE_EXP"32
Note
Items 2 and 3 take effect when Application of optimizing mode 2 based on cost is specified.
[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 for the UNIX edition); it affects the length of the result of the SUBSTR scalar function. For details about SUBSTR, see 2.16.1(20) 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 9 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 9 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 about SQL procedure statements, see General rules in 7. Routine Control SQL.

external-Java-routine-name
Specifies the external routine, written in Java, that constitutes a Java method. For details about how to specify an external Java routine name, see 1.10.1(1) External Java routine names.
external-C-stored-routine-name
Specifies a Java method written in the C language as an external routine. For details about how to specify an external Java routine name, see 1.10.2(1) External C stored routine names.

Specifies items to be passed as parameters when an external routine is called.

JAVA parameter-style

Parameters to be passed to an external Java procedure, defined using SQL data types, are passed to the Java method using the Java data type that corresponds to the SQL data type.

The OUT and INOUT parameters of an external Java procedure defined which SQL data types correspond which Java data types. Parameters are passed to a Java method is an array of one element. An array written by the Java method after its completion is treated as the output parameter of the external Java procedure.

RDSQL parameter-style
If the number of SQL parameters is n, the SQL parameter passed to the external C function is as described in the following table.

Table 3-27 Contents of the parameter passed to the C function

Parameter no.
(n is number of SQL parameters)
ContentsDescription of contentsData type
1 to nData part of SQL parameterParameters corresponding to the data part of the SQL parameter. The input/output mode of each parameter is the same as that of the corresponding SQL parameter.Data type corresponding to data type of SQL parameter#1
n + 1 to 2nIndicator part of SQL parameterParameters corresponding to the indicator part of the SQL parameter. The input/output mode of each parameter is the same as that of the corresponding SQL parameter.
If it is an input parameter and the data is a null value, a negative value is set and passed to the C function.
If it is an output parameter, it is set to the indicator part in the C function that implemented the external C stored routine, according to the following description.
  • If the output value is a null value
    Setting: -1
  • If the output value is not a null value
    Setting: 0
short*
2n + 1SQLSTATEOutput parameter used by the C function that implemented the external C stored routine to set the SQLSTATE value. The area is 6 bytes in length. The SQLSTATE value is saved in the first 5 bytes. The SQLSTATE value is set in the C function that implemented the external C stored routine according to the following description.
  • If the C function terminates normally
    Setting: '00000'
    SQL execution result: Terminate normally
  • If the C function terminates abnormally
    Setting: Value in the format '38XYY'
    X and Y are values in the following range.
    X: I to Z
    Y: 0 to 9 or A to Z
    Examples: '38I01', '38ZCD'
    SQL execution result: SQL error occurs.
  • If the C function ends in an indeterminate state
    Setting: Value other than '00000' and not in the format '38XYY'
    SQL execution result: SQL error occurs.
char*
2n + 2Routine nameInput parameter indicating the routine namestruct{
short variable-name-1;
char variable-name-2[30];
}*#2
2n + 3ID nameInput parameter indicating the ID name used to identify the procedurestruct{
short variable-name-1;
char variable-name-2[30];
}*#2
2n + 4Message textOutput parameter for setting the detailed reason for an error if an error occurs in the C function that implemented an external C stored routine.
If the value of class 38 is set to SQLSTATE when an external C stored routine finishes execution, an error message with embedded message text is output.
The maximum length of the message text that can be set is 80 bytes.
struct{
short variable-name-1;
char variable-name-2[80];
}*#2
#1
The correspondence between the data type of an SQL parameter that is specified when defining an external C procedure and the data type of the parameter passed to the C function that implements an external C stored routine is the same as the correspondence when defining an external C function. For details, see CREATE [PUBLIC] FUNCTION (Define function, define public function) in this chapter.
#2
Sets the character string length (in bytes) in variable-name-1 and the character string indicating the contents in variable-name-2.

Common rules

  1. The same identifier used in the following procedures cannot be specified for the routine identifier:
    • Procedure of an owner
    • Procedure of a public procedure
  2. An input parameter cannot contain an I/O parameter for a routine that is subject to the CALL statement and defined with the OUT or INOUT option; also, it cannot be specified in a FETCH statement, in the INTO clause of a single-row SELECT statement, or in the target of an assignment statement.
  3. Output parameters cannot be specified in any location with the exception of the following: input/output parameters for the arguments for the routines, subject to the CALL statement, defined in either OUT or INOUT; the INTO clause of the FETCH statement; the INTO clause of the single-row SELECT statement; the target of an assignment statement; or a value expression in the WRITE LINE statement.
  4. The number of procedure parameters must not exceed 30,000 (128 if C is specified in the LANGUAGE clause). However, if Java is specified in the LANGUAGE clause, an error may result at execution time due to specification limitations depending on the JavaVM in use even though the number of specified procedure parameters does not exceed 30,000.
  5. The designated name for designating a procedure is the same as [authorization-identifier.]routine-identifier.
  6. The BOOLEAN data type cannot be specified in an input or output parameter.
  7. If the name of a CALL statement specified in the procedure body is qualified with authorization-identifier, you cannot specify a procedure that has the same authorization identifier and routine identifier as the procedure that you are currently defining.
    If the name of a CALL statement specified in the procedure body is not qualified with authorization-identifier, you cannot specify a procedure that has the same routine identifier as the procedure that you are currently defining.
  8. When the SQL compile option is specified in an ALTER PROCEDURE or ALTER ROUTINE statement, the length of the SQL statement that is created by incorporating the SQL compile option into the source CREATE PROCEDURE statement of the procedure being re-created must not exceed the maximum permissible length for SQL statements.
  9. A WRITE specification cannot be specified in a query specification in an SQL procedure statement.
  10. If C is specified in the LANGUAGE clause, no result set can be returned.

Notes

  1. The CREATE PROCEDURE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. The SQL parameter can assume the null value.
  3. When executing multiple SQL statements in a procedure, routine control SQL, such as compound statements must be used.
  4. When a procedure is defined, an SQL object is created that codes an access procedure for executing the procedure. When an external Java procedure is defined, no SQL object is created that codes an access procedure for executing the procedure.
  5. The data type data guarantee level of the SQL statement in the procedure, the SQL optimization option, the SQL extension optimizing option, and the maximum character length are determined by what is specified when the procedure is being defined or modified, and are not affected by the system definition or client environment variable definition that is in effect when the procedure is called.
  6. Stacks in the operating system may overflow if routine calls are repeated extensively or infinitely in a routine.
  7. The following SQL statements cannot be executed from an external Java procedure:
    • Control SQL statements other than the COMMIT, LOCK, and ROLLBACK statements
    • Routine control SQL statements
  8. A results set cannot be received in an SQL procedure.
  9. A result set (ResultSet) returned by a method of the DatabaseMetaData class acquired within the external Java procedure cannot be returned as a dynamic result set. Use the metadata from the call source connection to the external Java procedure to acquire the information.

(2) CREATE PUBLIC PROCEDURE (Define public procedure)

Privileges

Users who own a schema
These users can define their own public procedures.

Format

CREATE PUBLIC procedure-body

Operands

Specifies the procedure that is to be defined as a public procedure.

When a procedure becomes a public procedure, it can be used by multiple users simply by specifying the routine identifier without each user having to define a procedure with the same contents.

For descriptions of operands other than [authorization-identifier.]routine-identifier, see (1) CREATE PROCEDURE (Define procedure) under CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure) in this chapter.

[authorization-identifier.]routine-identifier
authorization-identifier
An authorization identifier cannot be specified because this is a public procedure.
routine-identifier
Specifies the name of the public procedure to be defined.

Common rules

  1. You cannot specify the authorization identifier of [authorization-identifier.]routine-identifier in the procedure body.
  2. The same routine identifier cannot be used in more than one procedure in a system.
  3. The name used when identifying a procedure is the same as "PUBLIC".routine-identifier.
  4. If the name of a CALL statement specified in the procedure body is qualified with PUBLIC, you cannot specify a procedure that has the same authorization identifier and routine identifier as the procedure that you are currently defining.
    If the name of a CALL statement specified in the procedure body is not qualified with authorization-identifier, you cannot specify a procedure that has the same routine identifier as the procedure that you are currently defining.
  5. If SQL compile options are specified in ALTER PROCEDURE or ALTER ROUTINE, the length of the SQL statement that is created by incorporating the SQL compile options in the source of the procedure being created by the CREATE PUBLIC PROCEDURE must not exceed the maximum allowable length for an SQL statement.
  6. For details about other rules, see under (1) CREATE PROCEDURE (Define procedure) CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure) in this chapter.

Notes

  1. CREATE PUBLIC PROCEDURE cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. PUBLIC is set in the column that stores the owner of the dictionary table (such as ROUTINE_SCHEMA in the SQL_ROUTINES table). Also, the authorization identifier that was used to define the public procedure is stored in the ROUTINE_CREATOR column of the SQL_ROUTINES table.
  3. Use DROP PUBLIC FUNCTION to delete public functions.
  4. For other notes, see under (1) CREATE PROCEDURE (Define procedure) CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure) in this chapter.