How to read the syntax diagrams The -+4 symbol indicates the end of a syntax diagram Syntax fragments start with the h symbol and end with the symbol Required items appear on the horizontal line(the main path) p一 required item- Optional items appear below the main path 一 required it If an optional item appears above the main path, that item has no effect on execution, and is used only for readability optional it p一 required it If you can choose from two or more items, they appear in a stack If you must choose one of the items, one item of the stack appears on the main pati rEquired it required choiceI- If choosing one of the items is optional, the entire stack appears below the main path If one of the items is the default, it will appear above the main path, and the remaining choices will be shown below p一 required it IX
The ── symbol indicates the end of a syntax diagram. Syntax fragments start with the ├─── symbol and end with the ───┤ symbol. Required items appear on the horizontal line (the main path). required_item Optional items appear below the main path. required_item optional_item If an optional item appears above the main path, that item has no effect on execution, and is used only for readability. required_item optional_item If you can choose from two or more items, they appear in a stack. If you must choose one of the items, one item of the stack appears on the main path. required_item required_choice1 required_choice2 If choosing one of the items is optional, the entire stack appears below the main path. required_item optional_choice1 optional_choice2 If one of the items is the default, it will appear above the main path, and the remaining choices will be shown below. required_item default_choice optional_choice optional_choice How to read the syntax diagrams About this book ix
How to read the syntax diagrams An arrow returning to the left, above the main line, indicates an item that can be repeated. In this case, repeated items must be separated by one or more bP-required item-repeatable it If the repeat arrow contains a comma, you must separate repeated items with a comma A repeat arrow above a stack indicates that you can make more than one choice from the stacked items or repeat a single choice. Keywords appear in uppercase(for example, FROM). They must be spelled exactly as shown. Variables appear in lowercase(for example, col umn-name) They represent user-supplied names or values in the syntax If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax. Sometimes a single variable represents a larger fragment of the syntax. For example, in the following diagram, the variable parameter-block represents he whole syntax fragment that is labeled parameter-block -required item- parameter-block parameter-block Adjacent segments occurring between"large bullets"(O)may be specified in required item-ttem1-●ttem2-●ttem3-●tem4 The above diagram shows that item2 and item3 may be specified in either order. Both of the following are valid
An arrow returning to the left, above the main line, indicates an item that can be repeated. In this case, repeated items must be separated by one or more blanks. required_item repeatable_item If the repeat arrow contains a comma, you must separate repeated items with a comma. required_item , repeatable_item A repeat arrow above a stack indicates that you can make more than one choice from the stacked items or repeat a single choice. Keywords appear in uppercase (for example, FROM). They must be spelled exactly as shown. Variables appear in lowercase (for example, column-name). They represent user-supplied names or values in the syntax. If punctuation marks, parentheses, arithmetic operators, or other such symbols are shown, you must enter them as part of the syntax. Sometimes a single variable represents a larger fragment of the syntax. For example, in the following diagram, the variable parameter-block represents the whole syntax fragment that is labeled parameter-block: required_item parameter-block parameter-block: parameter1 parameter2 parameter3 parameter4 Adjacent segments occurring between “large bullets” (*) may be specified in any sequence. required_item item1 * item2 * item3 * item4 The above diagram shows that item2 and item3 may be specified in either order. Both of the following are valid: How to read the syntax diagrams x SQL Reference, Volume 2
How to read the syntax diagrams required item iteml item2 item3 item4 required item iteml item3 item2 item4 Common syntax elements The following sections describe a number of syntax fragments that are used in syntax diagrams. The fragments are referenced as follows Function designator A function designator uniquely identifies a single function. Function typically appear in DDL statements for functions(such as DROP or altel function-designator: UNCT ION-function-n (-data-type-) - SPECIFIC FUNCTION-specific-name- Description FUNCTION function-name Identifies a particular function, and is valid only if there is exactly one function instance with the name function-name in the schema. The identified function can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no function by this name exists in the named or implied schema, an error(SQLSTATE 42704)is raised. If there is more than one instance of the function in the named or implied schema, an error(SQLSTATE 42725) is raised FUNCTiON function-name( data-type, Provides the function signature, which uniquely identifies the function The function resolution algorithm is not used nction-mame Specifies the name dynamic SQL statements, CURRENT SCHEMA special register is used as a qualifier for an
required_item item1 item2 item3item4 required_item item1 item3item2 item4 Common syntax elements The following sections describe a number of syntax fragments that are used in syntax diagrams. The fragments are referenced as follows: fragment Function designator A function designator uniquely identifies a single function. Function designators typically appear in DDL statements for functions (such as DROP or ALTER). Syntax: function-designator: FUNCTION function-name ( ) , ( data-type ) SPECIFIC FUNCTION specific-name Description: FUNCTION function-name Identifies a particular function, and is valid only if there is exactly one function instance with the name function-name in the schema. The identified function can have any number of parameters defined for it. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. If no function by this name exists in the named or implied schema, an error (SQLSTATE 42704) is raised. If there is more than one instance of the function in the named or implied schema, an error (SQLSTATE 42725) is raised. FUNCTION function-name (data-type,...) Provides the function signature, which uniquely identifies the function. The function resolution algorithm is not used. function-name Specifies the name of the function. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an How to read the syntax diagrams About this book xi
Function designator unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for ( data-type…) Values must match the data types that were specified(in the corresponding position)on the CREATE FUNCTION statement. The number of data types, and the logical concatenation of the data types If a data type is unqualified, the type name is resolved by searching the schemas on the SQL Path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOATO cannot be used (SQLSTATE 42601), because the parameter value indicates different data types(real or dOUBLE) If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. A type of FLOAT(n) does not need to match the defined value for n, because 0< n<25 means REAL, and 24< n< 54 means double. Matching occurs on the basis of whether the type is REAL or DOUBLE If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883)is raised SPECIFIC FUNCTION specific-namme Identifies a particular user-defined function, using the name that is specified or defaulted to at function creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific function instance in the named or implied schema; otherwise, an error(SQLSTATE 42704)is raised Method designator A method designator uniquely identifies a single method Method designators typically appear in DDL statements for methods(such as DROP or ALTER) Xll SQL Reference, Volume 2
unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE FUNCTION statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific function instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised. SPECIFIC FUNCTION specific-name Identifies a particular user-defined function, using the name that is specified or defaulted to at function creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific function instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised. Method designator A method designator uniquely identifies a single method. Method designators typically appear in DDL statements for methods (such as DROP or ALTER). Syntax: Function designator xii SQL Reference, Volume 2
Method designator method-designator: -SPECIFIC METHOD--specific-name- Description: METHOD method-name Identifies a particular method, and is valid only if there is exactly one method instance with the name method-name for the type type-name. The identified method can have any number of parameters defined for it. If no method by this name exists for the type, an error(SQLSTATE 42704)is raised. If there is more than one instance of the method for the type, an error(SQLSTATE 42725)is raised METHOD method-name(data-type,) Provides the method signature, which uniquely identifies the method. The method resolution algorithm is not used Specifies the name of the method for the type type Values must match the data types that were specified (in the corresponding position) on the CREATE TYPE statement. The number of data types, and the logical concatenation of the data types, is used If a data type is unqualified, the type name is resolved by searching the schemas on the SQL Path. This also applies to data type names specified for a REFERENCE type It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOATO cannot be used(SQLSTATE 42601 ), because the parameter value indicates different data types(real or DOUbLE) If length, precision, or scale is coded, the value must exactly match that specified in the CREATE TYPE statement A type of FLOAT(n) does not need to match the defined value for n, because 0< n< 25 means REAL, and 24< n< 54 means DOUBle. Matching occurs on the basis of whether the type is REAL or DOUBLE about this book xIll
method-designator: METHOD method-name FOR type-name ( ) , ( data-type ) SPECIFIC METHOD specific-name Description: METHOD method-name Identifies a particular method, and is valid only if there is exactly one method instance with the name method-name for the type type-name. The identified method can have any number of parameters defined for it. If no method by this name exists for the type, an error (SQLSTATE 42704) is raised. If there is more than one instance of the method for the type, an error (SQLSTATE 42725) is raised. METHOD method-name (data-type,...) Provides the method signature, which uniquely identifies the method. The method resolution algorithm is not used. method-name Specifies the name of the method for the type type-name. (data-type,...) Values must match the data types that were specified (in the corresponding position) on the CREATE TYPE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific method instance. If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type. It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match. FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE). If length, precision, or scale is coded, the value must exactly match that specified in the CREATE TYPE statement. A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE. Method designator About this book xiii