
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
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.