HP 3000 Manuals HP 3000 Manuals
Appendix A SQL Syntax Summary
ADD DBEFILE
ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName
ADD TO GROUP
{DBEUserID}
ADD {GroupName} [,...] TO GROUP TargetGroupName
{ClassName}
ALTER DBEFILE
{TABLE}
ALTER DBEFILE DBEFileName SET TYPE = {INDEX}
{MIXED}
ALTER TABLE
{AddColumnSpecification }
ALTER TABLE [Owner.]TableName {AddConstraintSpecification }
{DropConstraintSpecification}
AddColumnSpecification.
ADD{(ColumnDefinition [,...] )}
{Column Definition }
AddConstraintSpecification.
{ {UniqueConstraint [CONSTRAINT ConstraintID] } }
{({ReferentialConstraint [CONSTRAINT ConstraintID]} [,...] )}
ADD CONSTRAINT{ {CheckConstraint [CONSTRAINT ConstraintID] } }
{{UniqueConstraint [CONSTRAINT ConstraintID] } }
{{ReferentialConstraint [CONSTRAINT ConstraintID]} }
{{CheckConstraint [CONSTRAINT ConstraintID] } }
DropConstraintSpecification.
DROP CONSTRAINT{(ConstraintID [,...] )}
{ConstraintID }
Assignment (=)
{:LocalVariable } = Expression;
{:ProcedureParameter}
BEGIN
BEGIN [Statement;] [...] END;
BEGIN ARCHIVE
BEGIN ARCHIVE
BEGIN DECLARE SECTION
BEGIN DECLARE SECTION
BEGIN WORK
[RR]
BEGIN WORK [Priority] [CS]
[RC]
[RU]
CHECKPOINT
CHECKPOINT
CLOSE
CLOSE CursorName
COMMIT ARCHIVE
COMMIT ARCHIVE
COMMIT WORK
COMMIT WORK [RELEASE]
CONNECT
CONNECT TO {'DBEnvironmentName'} [AS {'ConnectionName'}]
{:HostVariable1 } [ {:HostVariable2 }]
[USER {'UserID' } [USING :HostVariable4]]
[ {:HostVariable3} ]
CREATE DBEFILE
CREATE DBEFILE DBEFilename WITH PAGES = DBEFileSize, NAME = 'SystemFileName'
[, INCREMENT = DBEFileIncrSize[, MAXPAGES = DBEFileMaxSize]]
[ {TABLE}]
[,TYPE = {INDEX}]
[ {MIXED}]
CREATE DBEFILESET
CREATE DBEFILESET DBEFileSetName
CREATE GROUP
CREATE GROUP [Owner.]GroupName
CREATE INDEX
CREATE [UNIQUE] [CLUSTERING] INDEX [Owner.]IndexName ON
[Owner.]TableName ( {ColumnName [ASC ]} [,...] )
{ [DESC]}
CREATE PROCEDURE
CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName]
[(ParameterDeclaration [, ParameterDeclaration] [...])] AS BEGIN
ProcedureStatement; [...] END;
ParameterDeclaration.
ParameterName ParameterType [LANG = ParameterLanguage]
[DEFAULT DefaultValue] [NOT NULL] [OUTPUT]
CREATE RULE
{ON }
CREATE RULE [Owner.]RuleName AFTER StatementType [,...] {OF }
{FROM}
{INTO}
[Owner].TableName [REFERENCING {OLD AS OldCorrelationName}[...]]
[ {NEW AS NewCorrelationName} ]
[WHERE FiringCondition] EXECUTE PROCEDURE [OwnerName.]ProcedureName
[(ParameterValue [,...])]
CREATE SCHEMA
[TableDefinition ]
[ViewDefinition ]
[IndexDefinition ]
CREATE SCHEMA AUTHORIZATION AuthorizationName [ProcedureDefinition] [...]
[RuleDefinition ]
[CreateGroup ]
[AddToGroup ]
[GrantStatement ]
CREATE TABLE
[PUBLIC ]
CREATE [PUBLICREAD] TABLE [Owner.]TableName [LANG = TableLanguageName]
[PRIVATE ]
{ColumnDefinition }
( {[{UniqueConstraint } ]} [,...])
{[{ReferentialConstraint} [CONSTRAINT ConstraintID]]}
{[{CheckConstraint } ]}
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]
[HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages ]
[CLUSTERING ON CONSTRAINT [ConstraintID] ]
[IN DBEFileSetName]
ColumnName {ColumnDataType }
{LongColumnType [IN DBEFileSet]}
[ {Constant }]
[LANG = ColumnLanguageName] [DEFAULT {NULL }]
[ {CurrentFunction}]
[NOT NULL [{UNIQUE } [CONSTRAINT ConstraintID]] ]
[ [{PRIMARY KEY} ] ]
[ ][...]
[REFERENCES RefTableName [(RefColumnName)] [CONSTRAINT ConstraintID][...]]
[ ]
[CHECK (SearchCondition) [CONSTRAINT ConstraintID] ]
CREATE TEMPSPACE
CREATE TEMPSPACE TempSpaceName WITH [MAXFILEPAGES = MaxTempFileSize,]
LOCATION ='PhysicalLocation'
CREATE VIEW
CREATE VIEW [Owner.]ViewName [( ColumnName [,...])] AS QueryExpression
[WITH CHECK OPTION [CONSTRAINT ConstraintID]]
DECLARE CURSOR
DECLARE CursorName [IN DBEFileSetName] CURSOR FOR {QueryExpression}
{StatementName }
[FOR UPDATE OF {ColumnName} [,...]]
DECLARE Variable
DECLARE {LocalVariable} [,...] VariableType [LANG = VariableLangName]
[ {Constant }]
[DEFAULT {NULL }] [NOT NULL]
[ {CurrentFunction}]
DELETE
DELETE FROM {[Owner.]TableName} [WHERE SearchCondition]
{[Owner.]ViewName }
DELETE WHERE CURRENT
DELETE FROM {[Owner.]TableName} WHERE CURRENT OF CursorName
{[Owner.]ViewName }
DESCRIBE
DESCRIBE [OUTPUT] StatementName {INTO } [[SQL] DESCRIPTOR] {SQLDA }
[INPUT ] {USING} {AreaName}
DISABLE RULES
DISABLE RULES
DISCONNECT
{'ConnectionName' }
{'DBEnvironmentName'}
DISCONNECT {:HostVariable }
{ALL }
{CURRENT }
DROP DBEFILE
DROP DBEFILE DBEFileName
DROP DBEFILESET
DROP DBEFILESET DBEFileSetName
DROP GROUP
DROP GROUP GroupName
DROP INDEX
DROP INDEX [Owner.]IndexName [FROM [Owner.]TableName]
DROP MODULE
DROP MODULE [Owner.]ModuleName [PRESERVE]
DROP PROCEDURE
DROP PROCEDURE [Owner.]ProcedureName [PRESERVE]
DROP RULE
DROP RULE [Owner.]RuleName [FROM TABLE [Owner.]TableName]
DROP TABLE
DROP TABLE [Owner.]TableName
DROP TEMPSPACE
DROP TEMPSPACE TempSpaceName
DROP VIEW
DROP VIEW [Owner.]ViewName
ENABLE RULES
ENABLE RULES
END DECLARE SECTION
END DECLARE SECTION
EXECUTE
EXECUTE {StatementName }
{[Owner.]ModuleName [(SectionNumber)]}
[ {[SQL] DESCRIPTOR{SQLDA } }]
[ { {AreaName} }]
[USING { }]
[ {:HostVariableName[[INDICATOR] :IndicatorVariable] [,...]}]
[ {:Buffer [,:StartIndex [, :NumberOfRows]] }]
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE {'String' }
{:HostVariable}
EXECUTE PROCEDURE
EXECUTE PROCEDURE [:ReturnStatusVariable =] [Owner.]ProcedureName
[( [ParameterValue [OUTPUT]] [, [ParameterValue [OUTPUT]]][...])]
FETCH
{INTO HostVariableSpecification }
{ {[SQL]DESCRIPTOR {SQLDA }}}
[BULK] FETCH CursorName {USING { {AreaName}}}
{ { }}
{ {HostVariableSpecification }}
BULK HostVariableSpecification.
:Buffer [,:StartIndex [,:NumberOfRows]]
Non-BULK HostVariableSpecification.
{:HostVariable [[INDICATOR] :Indicator]} [,...]
GENPLAN
GENPLAN [WITH (VariableName DataType [,..])] FOR SQLStatement
GOTO
{GOTO } {Label }
{GO TO} {Integer}
GRANT
Grant Authority for a Table or View.
{ALL [PRIVILEGES] }
{{SELECT } }
{{INSERT } }
GRANT {{DELETE } } ON {[Owner.]TableName}
{{ALTER } |,...|} {[Owner.]ViewName }
{{INDEX } }
{{UPDATE [(ColumnName [,...])] } }
{{REFERENCES [(ColumnName [,...])]} }
{DBEUserID}
TO {GroupName} [,...] [WITH GRANT OPTION] [BY {DBEUserID}]
{ClassName} [ {ClassName}]
{PUBLIC }
Grant RUN or EXECUTE Authority.
{{DBEUserID} }
GRANT {RUN ON [Owner.]ModuleName } TO {{GroupName} [,...]}
{EXECUTE ON PROCEDURE [Owner.]ProcedureName} {{ClassName} }
{PUBLIC }
Grant CONNECT, DBA, or RESOURCE Authority.
{CONNECT } {DBEUserID}
GRANT{DBA }TO {GroupName}[,...]
{RESOURCE} {ClassName}
IF
IF Condition THEN [Statement;[...]]
[ELSEIF Condition THEN [Statement; [...]]] [ELSE [Statement; [...]]]
ENDIF;
INCLUDE
INCLUDE {SQLCA}
{SQLDA}
INSERT - I
[BULK] INSERT INTO {[Owner.]TableName} [({ColumnName} [,...])] VALUES
{[Owner.]ViewName }
{SingleRowValues}
({BulkValues })
{? }
Single Row Values.
{NULL }
{USER }
{:HostVariable [[INDICATOR] :IndicatorVariable]}
{? }
{:LocalVariable }
{:ProcedureParameter }
{::Built-inVariable }
{ConversionFunction }
{CurrentFunction }
{ {Integer} }
{[+] {Float } }
{[-] {Decimal} }
{ }
{'CharacterString' }
{0xHexadecimalString }
{'LongColumnIOString' }
{[,...] }
LongColumnIOString.
[{> } {FileName[.Group[.Account]]}]
[{>>} {CharString$ }]
< {FileName[.Group[.Account]]} [{>!} {CharString$CharString }]
{%HeapAddress:LengthofHeap } [ ]
[>%$ ]
Bulk Values.
:Buffer [,:StartIndex [, :NumberOfRows]]
Dynamic Parameter Substitution.
(? [,...] )
INSERT - II
INSERT INTO {[Owner.]TableName} [(ColumnName [,...])] QueryExpression
{[Owner.]ViewName }
Labeled Statement
Label: Statement
LOCK TABLE
LOCK TABLE [Owner.]TableName IN {SHARE [UPDATE]} MODE
{EXCLUSIVE }
OPEN
OPEN CursorName [KEEP CURSOR [WITH LOCKS ]] USING
[ [WITH NOLOCKS]]
[[SQL] DESCRIPTOR{SQLDA } ]
[ {AreaName} ]
[ ]
[HostVariableName[[INDICATOR] :IndicatorVariable] [,...]]
PREPARE
PREPARE {StatementName } [IN DBEFileSetName] FROM
{[Owner.]ModuleName [(SectionNumber)]}
{'String' }
{:HostVariable}
PRINT
{'Constant' }
PRINT {:LocalVariable };
{:Parameter }
{::Built-inVariable}
RAISE ERROR
RAISE ERROR [ErrorNumber] [MESSAGE ErrorText]
REFETCH
REFETCH CursorName INTO {:HostVariable [[INDICATOR] :Indicator]} [,...]
RELEASE
RELEASE
REMOVE DBEFILE
REMOVE DBEFILE DBEFileName FROM DBEFILESET DBEFileSetName
REMOVE FROM GROUP
{DBEUserID}
REMOVE {GroupName} [,...] FROM GROUP [Owner.]TargetGroupName
{ClassName}
RESET
{SYSTEM.ACCOUNT [FOR USER {* }]}
RESET { [ {DBEUserID}]}
{ }
{SYSTEM.COUNTER }
RETURN
RETURN [ReturnStatus];
REVOKE
Revoke Authority on a Table or View.
{ALL [PRIVILEGES] }
{[SELECT ] }
{[INSERT ] }
REVOKE {[DELETE ] } ON
{[ALTER ] |,...|}
{[INDEX ] }
{[UPDATE [({ColumnName} [,...])] ] }
{[REFERENCES [({ColumnName} [,...])]] }
{DBEUserID}
{[Owner.]TableName} FROM {GroupName} [,...] [CASCADE]
{[Owner.]ViewName } {ClassName}
{PUBLIC }
Revoke RUN or EXECUTE Authority.
REVOKE [RUN ON [Owner.]ModuleName ] FROM
[EXECUTE ON PROCEDURE [Owner.]ProcedureName]
{{DBEUserID} }
{{GroupName} [,...]}
{{ClassName} }
{PUBLIC }
Revoke CONNECT, DBA, or RESOURCE Authority.
{CONNECT } {DBEUserID}
REVOKE {DBA } FROM {GroupName} [,...]
{RESOURCE} {ClassName}
ROLLBACK WORK
[ {SavePointNumber }]
[TO {:HostVariable }]
ROLLBACK WORK [ {:LocalVariable }]
[ {:ProcedureParameter}]
[ ]
[RELEASE ]
SAVEPOINT
[:HostVariable ]
SAVEPOINT [:LocalVariable ]
[:ProcedureParameter]
SELECT
Select Statement Level.
[BULK] QueryExpression [ORDER BY {ColumnID [ASC ]} [,...]]
[ { [DESC]} ]
Subquery Level.
(QueryExpression)
Query Expression Level.
{QueryBlock } [UNION [ALL] {QueryBlock }] [...]
{(QueryExpression)} [ {(QueryExpression)}]
Query Block Level.
SELECT [ALL ] SelectList [INTO HostVariableSpecification] FROM
[DISTINCT]
FromSpec [,...] [WHERE SearchCondition1] [GROUP BY GroupColumnList]
[HAVING SearchCondition2]
SelectList.
{* }
{[Owner.]Table.* }
{CorrelationName.* } [,...]
{Expression }
{[[Owner.]Table.]ColumnName}
{CorrelationName.ColumnName}
HostVariableSpecification--Without BULK Option.
{:HostVariable [[INDICATOR] :Indicator]} [,...]
FromSpec.
{TableSpec }
{(FromSpec) }
{ [INNER ] }
{FromSpec NATURAL [LEFT [OUTER] ] JOIN {TableSpec } }
{ [RIGHT [OUTER]] {(FromSpec)} }
{ }
{ [INNER ] }
{FromSpec [LEFT [OUTER] ] JOIN {TableSpec } {ON SearchCondition3}}
{ [RIGHT [OUTER]] {(FromSpec)} {USING (ColumnList) }}
TableSpec.
[Owner.]TableName [CorrelationName]
SET CONNECTION
SET CONNECTION {'ConnectionName'}
{:HostVariable }
SET CONSTRAINTS
SET ConstraintType [,...] CONSTRAINTS {DEFERRED }
{IMMEDIATE}
SET DML ATOMICITY
SET DML ATOMICITY AT {ROW } LEVEL
{STATEMENT}
SET MULTITRANSACTION
SET MULTITRANSACTION {ON }
{OFF}
SET PRINTRULES
SET PRINTRULES [ON ]
[OFF]
SET USER TIMEOUT
{{TimeoutValue } [SECONDS]}
{{:HostVariable} [MINUTES]}
SET USER TIMEOUT [TO] { }
{DEFAULT }
{MAXIMUM }
SQLEXPLAIN
SQLEXPLAIN :HostVariable
START DBE
START DBE 'DBEnvironmentName' [AS ConnectionName'] [MULTI]
[BUFFER = (DataBufferPages, LogBufferPages)]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]}]
[MAXIMUM TIMEOUT = { [MINUTES]}]
[ { }]
[ {NONE }]
[ ] |,...|
[ {TimeoutValue [SECONDS]}]
[DEFAULT TIMEOUT = { [MINUTES]}]
[ { }]
[ {MAXIMUM }]
[ ]
[RUN BLOCK = ControlBlockPages ]
START DBE NEW
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEW
[DUAL LOG ]
[BUFFER = (DataBufferPages, LogBufferPages)]
[LANG = LanguageName ]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]}]
[MAXIMUM TIMEOUT = { [MINUTES]}]
[ { }]
[ {NONE }]
[ ] |,...|
[ {TimeoutValue [SECONDS]}]
[DEFAULT TIMEOUT = { [MINUTES]}]
[ { }]
[ {MAXIMUM }]
[ ]
[RUN BLOCK = ControlBlockPages ]
[DBEFile0Definition ]
[DBELogDefinition ]
START DBE NEWLOG
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEWLOG
[{ARCHIVE} |...| LOG ]
[{DUAL } ]
[ ]
[BUFFER = (DataBufferPages, LogBufferPages)]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]}]
[MAXIMUM TIMEOUT = { [MINUTES]}]
[ { }] |,...| NewLogDefinition
[ {NONE }]
[ ]
[ {TimeoutValue [SECONDS]}]
[DEFAULT TIMEOUT = { [MINUTES]}]
[ { }]
[ {MAXIMUM }]
[ ]
[RUN BLOCK = ControlBlockPages ]
NewLogDefinition.
LOG DBEFILE DBELog1ID [AND DBELog2ID] WITH PAGES = DBELogSize, NAME =
'SystemFileName1' [AND ]
['SystemFileName2']
STOP DBE
STOP DBE
TERMINATE USER
TERMINATE USER {DBEUserID}
{SessionID}
TRANSFER OWNERSHIP
{[TABLE] [Owner.]TableName }
TRANSFER OWNERSHIP OF {[VIEW] [Owner.]ViewName } TO NewOwnerName
{PROCEDURE [Owner.]ProcedureName}
{GROUP GroupName }
UPDATE
{ {Expression }}
UPDATE {[Owner.]TableName} SET {ColumnName = {'LongColumnIOString'}}
{[Owner.]ViewName } { {NULL }}
[,...] [WHERE SearchCondition]
LongColumnIOString.
{[< {FileName[.Group[.Account]]}] }
{[ {%HeapAddress:LengthofHeap }] }
{ }
{[{> } {FileName[.Group[.Account]]}]} |...|
{[{>>} {CharString$ }]}
{[{>!} {CharString$CharString }]}
{[ ]}
{[>%$ ]}
UPDATE STATISTICS
UPDATE STATISTICS FOR TABLE {[Owner.]TableName }
{SYSTEM.SystemViewName}
UPDATE WHERE CURRENT
{ {Expression }}
UPDATE {[Owner.]TableName} SET {ColumnName = {'LongColumnIOString'}}
{[Owner.]ViewName } { {NULL }}
[,...] WHERE CURRENT OF CursorName
LongColumnIOString.
{[< {FileName[.Group[.Account]]}] }
{[ {%HeapAddress:LengthofHeap }] }
{ }
{[{> } {FileName[.Group[.Account]]}]} |...|
{[{>>} {CharString$ }]}
{[{>!} {CharString$CharString }]}
{[ ]}
{[>%$ ]}
VALIDATE
{MODULE {[Owner.]ModuleName}[,...] }
{ }
VALIDATE {PROCEDURE {[Owner.]ProcedureName}[,...]}
{ }
{ALL {MODULES } }
{ {PROCEDURES} }
WHENEVER
{SQLERROR } {STOP }
WHENEVER {SQLWARNING} {CONTINUE}
{NOT FOUND } {Label }
{Label }
WHILE
WHILE Condition DO [Statement; [...]] ENDWHILE;