HITACHI Inspire The Next

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