
Scalable Database Server
HiRDB Version 8
SQL Reference
3020-6-357(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 Literals
- 1.3.1 Predefined character string representation of date data
- 1.3.2 Predefined character string representation of time data
- 1.3.3 Predefined character string representation of time stamp data
- 1.3.4 Decimal representation of date interval data
- 1.3.5 Decimal representation of time interval data
- 1.3.6 Decimal representation of datetime interval data
- 1.4 USER, CURRENT_DATE value function, CURRENT_TIME value function, and CURRENT_TIMESTAMP value function
- 1.4.1 USER
- 1.4.2 CURRENT_DATE value function
- 1.4.3 CURRENT_TIME value function
- 1.4.4 CURRENT_TIMESTAMP value function
- 1.5 Embedded variables, indicator variables, ? parameters, SQL parameters, and SQL variables
- 1.5.1 Embedded variables and indicator variables
- 1.5.2 ? parameters
- 1.5.3 SQL parameters and SQL variables
- 1.5.4 Specifiable locations
- 1.5.5 Setting a value for an indicator variable
- 1.5.6 Setting a null default value in an embedded variable
- 1.5.7 Assignment rules
- 1.6 Null value
- 1.7 Component specification
- 1.8 Routines
- 1.8.1 Procedures
- 1.8.2 Functions
- 1.8.3 Results-set return facility
- 1.9 Java routines
- 1.9.1 Specification of external routines
- 1.9.2 Type mapping
- 1.9.3 SQL executability using a non-POSIX library version of HiRDB (UNIX version only)
- 1.10 Specifying a datetime format
- 1.11 Relationship to HiRDB External Data Access
- 1.12 Restrictions on the use of the inner replica facility
- 1.13 Locator
- 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.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
- 3. Definition SQL
- General rules
- ALTER PROCEDURE (Recreate SQL object of procedure)
- ALTER ROUTINE (Recreate SQL objects for functions, procedures, and triggers)
- ALTER TABLE (Alter table definition)
- ALTER TRIGGER (Recreate a trigger SQL object)
- COMMENT (Comment)
- CREATE ALIAS (Define alias)
- CREATE AUDIT (Define the target audit event)
- CREATE CONNECTION SECURITY (Define the connection security facility)
- CREATE FOREIGN INDEX (Define a foreign index)
- CREATE FOREIGN TABLE (Define a foreign table)
- CREATE FUNCTION (Define function)
- CREATE INDEX Format 1 (Define index)
- CREATE INDEX Format 2 (Define index)
- CREATE PROCEDURE (Define procedure)
- CREATE SCHEMA (Define schema)
- CREATE SERVER (Define a foreign server)
- CREATE TABLE (Define table)
- CREATE TRIGGER (Define a trigger)
- CREATE TYPE (Define type)
- CREATE USER MAPPING (Define user mapping)
- CREATE VIEW (Define view)
- DROP ALIAS (Delete alias)
- DROP AUDIT (Delete an audit target event)
- DROP CONNECTION SECURITY (Delete the connection security facility)
- DROP DATA TYPE (Delete user-defined data type)
- DROP FOREIGN INDEX (Delete a foreign index)
- DROP FOREIGN TABLE (Delete a foreign table)
- DROP FUNCTION (Delete function)
- DROP INDEX (Delete index)
- DROP PROCEDURE (Delete procedure)
- DROP SCHEMA (Delete schema)
- DROP SERVER (Delete a foreign server)
- DROP TABLE (Delete table)
- DROP TRIGGER (Delete a trigger)
- DROP USER MAPPING (Delete user mapping)
- DROP VIEW (Delete 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
- 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)
- CONNECT statement with RD-node specification (Connect to distributed RD-node)
- DISCONNECT statement with RD-node specification (Disconnect from distributed RD-node)
- ROLLBACK statement (Cancel transaction)
- SET CONNECTION statement (Set current RD-node)
- 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)
- 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. Correspondence Between Data Types When a Foreign Table is Used
- C.1 Foreign DB: HiRDB
- C.2 Foreign DB: XDM/RD E2
- C.3 Foreign DB: ORACLE
- C.4 Foreign DB: DB2
- D. Restrictions on Using a Foreign Table
- E. Example Database
- Index
All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.