Nonstop Database

HiRDB Version 9

SQL Reference

3020-6-457-10(E)


Contents

Notices
Preface

1. Basics
1.1 SQL coding format
1.1.1 Order of specifying operands
1.1.2 Keyword specification
1.1.3 Specifying a numeric value
1.1.4 Insertion of delimiters
1.1.5 SQL character set
1.1.6 Maximum length of an SQL statement
1.1.7 Specification of names
1.1.8 Qualifying a name
1.1.9 Schema path
1.2 Data types
1.2.1 Data types
1.2.2 Data types that can be converted (assigned or compared)
1.2.3 Notes on using character data, national character data, and mixed character data
1.2.4 Notes on using the decimal type
1.2.5 Notes on using large-object data
1.2.6 Notes on using the BINARY type
1.2.7 Notes on using logical data
1.2.8 Notes on using an abstract data type
1.3 Character sets
1.4 Literals
1.4.1 Predefined character string representation of date data
1.4.2 Predefined character string representation of time data
1.4.3 Predefined character string representation of time stamp data
1.4.4 Decimal representation of date interval data
1.4.5 Decimal representation of time interval data
1.4.6 Decimal representation of datetime interval data
1.5 USER, CURRENT_DATE value function, CURRENT_TIME value function, and CURRENT_TIMESTAMP value function
1.5.1 USER
1.5.2 CURRENT_DATE value function
1.5.3 CURRENT_TIME value function
1.5.4 CURRENT_TIMESTAMP value function
1.6 Embedded variables, indicator variables, ? parameters, SQL parameters, and SQL variables
1.6.1 Embedded variables and indicator variables
1.6.2 ? parameters
1.6.3 SQL parameters and SQL variables
1.6.4 Specifiable locations
1.6.5 Setting a value for an indicator variable
1.6.6 Setting a null default value in an embedded variable
1.6.7 Assignment rules
1.7 Null value
1.8 Component specification
1.9 Routines
1.9.1 Procedures
1.9.2 Functions
1.9.3 Results-set return facility
1.10 External routines
1.10.1 External Java routines
1.10.2 External C stored routines
1.11 Specifying a datetime format
1.12 Restrictions on the use of the inner replica facility
1.13 Locator
1.14 XML type
1.14.1 XML type description format
1.14.2 XML constructor function
1.14.3 SQL/XML scalar functions
1.14.4 SQL/XML predicates
1.14.5 SQL/XML set functions
1.14.6 Definition SQL for the XML type
1.15 XQuery
1.15.1 XQuery data model
1.15.2 Basic items
1.15.3 Specifying an XQuery
1.15.4 XQuery description format
1.15.5 XQuery declaration
1.15.6 XQuery query body
1.15.7 XQuery comment
1.15.8 XQuery functions

2. Details of Constituent Elements
2.1 Cursor specification
2.1.1 Cursor specification: Format 1
2.1.2 Cursor specification: Format 2
2.2 Query expressions
2.2.1 Query expression format 1 (general-query-expression)
2.2.2 Query expression format 2 (unnesting query expression for repetition columns)
2.3 Query specification
2.4 Subqueries
2.5 Table expressions
2.6 Table reference
2.7 Search conditions
2.7.1 Function
2.7.2 Logical operations
2.7.3 Results of a predicate
2.7.4 Rules common to predicates
2.7.5 Predicates
2.8 Row value constructors
2.9 Value expressions, value specifications, and item specifications
2.10 Arithmetic operations
2.11 Date operations
2.12 Time operations
2.13 Concatenation operation
2.14 Set functions
2.15 Window function
2.16 Scalar functions
2.16.1 System built-in scalar functions
2.16.2 System-defined scalar functions
2.16.3 Plug-in definition scalar functions
2.17 CASE expressions
2.18 Operational results with overflow error suppression specified
2.18.1 Example of overflow in a search condition
2.18.2 Example of overflow in an update value
2.19 Lock option
2.20 Function calls
2.21 Inner derived tables
2.21.1 Conditions for an inner derived table
2.22 WRITE specification
2.23 GET_JAVA_STORED_ROUTINE_SOURCE specification
2.24 SQL optimization specification
2.24.1 SQL optimization specification for a used index
2.24.2 Join method SQL optimization specification
2.24.3 Subquery execution method SQL optimization specification
2.24.4 Examples of SQL optimization specification
2.25 CAST specification
2.26 Extended statement name
2.27 Extended cursor name
2.28 NEXT VALUE expression

3. Definition SQL
General rules
ALTER INDEX (Alter index definition)
ALTER PROCEDURE (Re-create SQL object of procedure)
ALTER ROUTINE (Re-create SQL objects for functions, procedures, and triggers)
ALTER TABLE (Alter table definition)
ALTER TRIGGER (Re-create a trigger SQL object)
COMMENT (Comment)
CREATE AUDIT (Define the target audit event)
CREATE CONNECTION SECURITY (Define the connection security facility)
CREATE [PUBLIC] FUNCTION (Define function, define public function)
CREATE INDEX Format 1 (Define index)
CREATE INDEX Format 2 (Define index)
CREATE INDEX Format 3 (Define substructure index)
CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure)
CREATE SCHEMA (Define schema)
CREATE SEQUENCE (Define sequence generator)
CREATE TABLE (Define table)
CREATE TRIGGER (Define a trigger)
CREATE TYPE (Define type)
CREATE [PUBLIC] VIEW (Define view, define public view)
DROP AUDIT (Delete an audit target event)
DROP CONNECTION SECURITY (Delete the connection security facility)
DROP DATA TYPE (Delete user-defined data type)
DROP [PUBLIC] FUNCTION (Delete function, delete public function)
DROP INDEX (Delete index)
DROP [PUBLIC] PROCEDURE (Delete procedure, delete public procedure)
DROP SCHEMA (Delete schema)
DROP SEQUENCE (Delete sequence generator)
DROP TABLE (Delete table)
DROP TRIGGER (Delete a trigger)
DROP [PUBLIC] VIEW (Delete view table, delete public view table)
GRANT Format 1 (Grant privileges)
GRANT Format 2 (Change auditor's password)
REVOKE (Revoke privileges)

4. Data Manipulation SQL
General rules
ALLOCATE CURSOR statement Format 1 (Allocate a statement cursor)
ALLOCATE CURSOR statement Format 2 (Allocate a result set cursor)
ASSIGN LIST statement Format 1 (Create list)
ASSIGN LIST statement Format 2 (Create list)
CALL statement (Call procedure)
CLOSE statement (Close cursor)
DEALLOCATE PREPARE statement (Nullify the preprocessing of SQL)
DECLARE CURSOR Format 1 (Declare cursor)
DECLARE CURSOR Format 2 (Declare cursor)
DELETE statement Format 1 (Delete rows)
DELETE statement Format 2 (Delete row using an array)
Preparable dynamic DELETE statement: locating (Delete row using a preprocessable cursor)
DESCRIBE statement Format 1 (Receive retrieval information and I/O information)
DESCRIBE statement Format 2 (Receive retrieval information and I/O information)
DESCRIBE CURSOR statement (Receive cursor retrieval information)
DESCRIBE TYPE statement (Receive definition information on user-defined data type)
DROP LIST statement (Delete list)
EXECUTE statement Format 1 (Execute SQL)
EXECUTE statement Format 2 (Execute an SQL statement using an array)
EXECUTE IMMEDIATE statement (Preprocess and execute SQL)
FETCH statement Format 1 (Fetch data)
FETCH statement Format 2 (Fetch data)
FETCH statement Format 3 (Fetch data)
FREE LOCATOR statement (Invalidate locator)
INSERT statement Format 1 (Insert row)
INSERT statement Format 2 (Insert row)
INSERT statement Format 3, Format 4 (Insert row using an array)
OPEN statement Format 1 (Open cursor)
OPEN statement Format 2 (Open cursor)
PREPARE statement (Preprocess SQL)
PURGE TABLE statement (Delete all rows)
Single-row SELECT statement (Retrieve one row)
Dynamic SELECT statement Format 1 (Retrieve dynamically)
Dynamic SELECT statement Format 2 (Retrieve dynamically)
UPDATE statement Format 1 (Update data)
UPDATE statement Format 2 (Update data)
UPDATE statement Format 3, Format 4 (Update row using an array)
Preparable dynamic UPDATE statement: locating Format 1 (Update data using a preprocessable cursor)
Preparable dynamic UPDATE statement: locating Format 2 (Update data using a preprocessable cursor)
Assignment statement Format 1 (Assign a value to an SQL variable or SQL parameter)
Assignment statement Format 2 (Assign a value to an embedded variable or a ? parameter)

5. Control SQL
General rules
CALL COMMAND statement (Execute command or utility)
COMMIT statement (Terminate transaction normally)
CONNECT statement (Connect a UAP to HiRDB)
DISCONNECT statement (Disconnect a UAP from HiRDB)
LOCK statement (Lock control on tables)
ROLLBACK statement (Cancel transaction)
SET SESSION AUTHORIZATION statement (Change connected user)

6. Embedded Language Syntax
General rules
BEGIN DECLARE SECTION (Declare beginning of embedded SQL)
END DECLARE SECTION (Declare end of embedded SQL)
ALLOCATE CONNECTION HANDLE (Allocate connection handle)
FREE CONNECTION HANDLE (Release connection handle)
DECLARE CONNECTION HANDLE SET (Declare connection handle to be used)
DECLARE CONNECTION HANDLE UNSET (Reset all connection handles being used)
GET CONNECTION HANDLE (Get connection handle)
COPY (Include library text)
GET DIAGNOSTICS (Retrieve diagnostic information)
COMMAND EXECUTE (Execute commands from a UAP)
SQL prefix
SQL terminator
WHENEVER (Declare embedded exception)
SQLCODE variable
SQLSTATE variable
PDCNCTHDL type variable declaration
INSTALL JAR (Register JAR file)
REPLACE JAR (Re-register JAR file)
REMOVE JAR (Remove JAR file)
INSTALL CLIB (Install external C library file)
REPLACE CLIB (Replace external C library file)
REMOVE CLIB (Remove external C library file)
DECLARE AUDIT INFO SET (Set user connection information)

7. Routine Control SQL
General rules
Compound statement (Execute multiple statements)
IF Statement (Execute by conditional branching)
LEAVE statement (Exit statement)
RETURN statement (Return function return value)
WHILE statement (Repeat statements)
FOR statement (Repeat a statement on rows)
WRITE LINE statement (Character string output to a file)
SIGNAL statement (Signal error)
RESIGNAL statement (Resignal error)

Appendixes
A. Reserved Words
A.1 SQL reserved words
A.2 HiRDB reserved words
A.3 Reserved words that can be deleted using the SQL reserved word deletion facility
B. List of SQLs
C. Example Database

Index