HP 3000 Manuals HP 3000 Manuals
SQL Syntax Summary (contd)
INSERT - 1
[BULK] INSERT INTO {[Owner.]TableName} [({ColumnName} [,...])] VALUES
{[Owner.]ViewName }
{SingleRowValues}
({BulkValues })
{? }
SingleRowValues. [REV BEG]
{NULL }
{USER }
{:HostVariable [[INDICATOR] :IndicatorVariable]}
{? }
{:LocalVariable }
{:ProcedureParameter }
{::Built-inVariable }
{ConversionFunction } [,...]
{CurrentFunction }
{ {Integer} }
{[+] {Float } }
{[-] {Decimal} }
{ }
{'CharacterString' }
{0xHexadecimalString }
{'LongColumnIOString' }
[REV END]
LongColumnIOString.
[{> } {FileName[.Group[.Account]]}]
[{>>} {CharString$ }]
< {FileName[.Group[.Account]]} [{>!} {CharString$CharString }]
{%HeapAddress:LengthofHeap } [ ]
[>%$ ]
BulkValues.
:Buffer [,:StartIndex [, :NumberOfRows]]
Dynamic Parameter Substitution.
(? [,...] )
INSERT - 2
INSERT INTO {[Owner.]TableName} [(ColumnName [,...])] QueryExpression
{[Owner.]ViewName }
Labeled Statement
Label: Statement
LOCK TABLE
LOCK TABLE [Owner.]TableName IN {SHARE [UPDATE]} MODE
{EXCLUSIVE }
[REV BEG]
LOG COMMENT
{'String' }
{:HostVariable }
LOG COMMENT {:ProcedureParameter }
{:ProcedureLocalVariable}
{? }
[REV END][REV BEG]
OPEN
OPEN CursorName [KEEP CURSOR [WITH LOCKS ]]
[ [WITH NOLOCKS]]
[ {[SQL] DESCRIPTOR{SQLDA } }]
[USING { {AreaName} }]
[ { }]
[ {HostVariableName[[INDICATOR] :IndicatorVariable] [,...]}]
[REV END][REV BEG]
PREPARE
PREPARE [REPEAT] {StatementName }
{[Owner.]ModuleName [(SectionNumber)]}
[IN DBEFileSetName] FROM {'String' }
{:HostVariable}
[REV END]
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];
[REV BEG]
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 or 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}
SQL Syntax--Revoke DBEFileSet Authority.
REVOKE {SECTIONSPACE} |,...| ON DBEFILESET DBEFileSetName FROM
{TABLESPACE }
{{DBEUserID} }
{{GroupName} [,...]}
{{ClassName} }
{PUBLIC }
[REV END]
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--With BULK Option.
:Buffer [,:StartIndex [,:NumberOfRows]]
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}
[REV BEG]
SET DEFAULT DBEFILESET
SET DEFAULT {SECTIONSPACE} TO DBEFILESET DBEFileSetName FOR PUBLIC
{TABLESPACE }
[REV END]
SET DML ATOMICITY
SET DML ATOMICITY AT {ROW } LEVEL
{STATEMENT}
SET MULTITRANSACTION
SET MULTITRANSACTION {ON }
{OFF}
[REV BEG]
SETOPT
{CLEAR }
{GENERAL {ScanAccess } [,...] }
SETOPT { {JoinAlgorithm} }
{BEGIN {GENERAL {ScanAccess }} [;...] END}
{ { {JoinAlgorithm}} }
[REV END][REV BEG]
Scan Access.
{SERIALSCAN}
[NO] {INDEXSCAN }
{HASHSCAN }
{SORTINDEX }
[REV END][REV BEG]
Join Algorithm.
{NESTEDLOOP}
[NO] {NLJ }
{SORTMERGE }
{SMJ }
[REV END]
SET PRINTRULES
SET PRINTRULES [ON ]
[OFF]
[REV BEG]
SET SESSION
{ {RR } }
{ {CS } }
{ {RC } }
{ {RU } }
{ISOLATION LEVEL {REPEATABLE READ } }
{ {SERIALIZABLE } }
{ {CURSOR STABILITY} }
{ {READ COMMITTED } }
{ {READ UNCOMMITTED} }
{ {:HostVariable1 } }
{ }
{PRIORITY {Priority } }
SET SESSION { {:HostVariable2} } [,...]
{ }
{LABEL {'LabelString' } }
{ {:HostVariable3} }
{ }
{ConstraintType [,...] CONSTRAINTS {DEFERRED }}
{ {IMMEDIATE}}
{ }
{DML ATOMICITY AT {STATEMENT} LEVEL }
{ {ROW } }
{ }
{[{PARALLEL}] FILL }
{[{NO }] }
[REV END][REV BEG]
SET TRANSACTION
{ {RR } }
{ {CS } }
{ {RC } }
{ {RU } }
{ISOLATION LEVEL {REPEATABLE READ } }
{ {SERIALIZABLE } }
{ {CURSOR STABILITY} }
{ {READ COMMITTED } }
{ {READ UNCOMMITTED} }
{ {:HostVariable1 } }
SET TRANSACTION { } [,...]
{PRIORITY {Priority } }
{ {:HostVariable2} }
{ }
{LABEL {'LabelString' } }
{ {:HostVariable3} }
{ }
{ConstraintType [,...] CONSTRAINTS {DEFERRED }}
{ {IMMEDIATE}}
{ }
{DML ATOMICITY AT {STATEMENT} LEVEL }
{ {ROW } }
[REV END]
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 ]
[REV BEG]
START DBE NEW
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEW
[{DUAL } |...| LOG ]
[{AUDIT} ]
[ ]
[BUFFER = (DataBufferPages, LogBufferPages) ]
[LANG = LanguageName ]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]} ]
[MAXIMUM TIMEOUT = { [MINUTES]} ]
[ { } ]
[ {NONE } ]
[ ]
[ {TimeoutValue [SECONDS]} ]
[DEFAULT TIMEOUT = { [MINUTES]} ]
[ { } ]
[ {MAXIMUM } ]
[ ]
[RUN BLOCK = ControlBlockPages ]
[DEFAULT PARTITION = {DefaultPartitionNumber}] |,...|
[ {NONE }]
[ ]
[ {CommentPartitionNumber}]
[COMMENT PARTITION = {DEFAULT }]
[ {NONE }]
[ ]
[MAXPARTITIONS = MaximumNumberOfPartitions ]
[AUDIT NAME = 'AuditName' ]
[{COMMENT } ]
[{DATA } ]
[{DEFINITION } ]
[{STORAGE } |...| AUDIT ELEMENTS ]
[{AUTHORIZATION} ]
[{SECTION } ]
[{ALL } ]
[ ]
[DBEFile0Definition ]
[DBELogDefinition ]
[REV END]
DBEFile0Definition.
DBEFILE0 DBEFILE DBEFile0ID WITH PAGES = DBEFile0Size, NAME =
'SystemFileName1'
DBELogDefinition.
LOG DBEFILE DBELog1ID [AND DBELog2ID] WITH PAGES = DBELogSize, NAME =
'SystemFileName2' [AND 'SystemFileName3']
[REV BEG]
START DBE NEWLOG
START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEWLOG
[{ARCHIVE} ]
[{DUAL } |...| LOG ]
[{AUDIT } ]
[ ]
[BUFFER = (DataBufferPages, LogBufferPages) ]
[TRANSACTION = MaxTransactions ]
[ {TimeoutValue [SECONDS]} ]
[MAXIMUM TIMEOUT = { [MINUTES]} ]
[ { } ]
[ {NONE } ]
[ ]
[ {TimeoutValue [SECONDS]} ]
[DEFAULT TIMEOUT = { [MINUTES]} ]
[ { } ]
[ {MAXIMUM } ]
[ ]
[RUN BLOCK = ControlBlockPages ] |,...| NewLogDefinition
[DEFAULT PARTITION = {DefaultPartitionNumber}]
[ {NONE }]
[ ]
[ {CommentPartitionNumber}]
[COMMENT PARTITION = {DEFAULT }]
[ {NONE }]
[ ]
[MAXPARTITIONS = MaximumNumberOfPartitions ]
[AUDIT NAME = 'AuditName' ]
[{COMMENT } ]
[{DATA } ]
[{DEFINITION } ]
[{STORAGE } |...| AUDIT ELEMENTS ]
[{AUTHORIZATION} ]
[{SECTION } ]
[{ALL } ]
[REV END]
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 }
[REV BEG]
TRUNCATE TABLE
TRUNCATE TABLE [Owner.]TableName
[REV END]
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 }]}
{[ ]}
{[>%$ ]}
[REV BEG]
VALIDATE
{MODULE {[Owner.]ModuleName}[,...] }
{ }
VALIDATE [DROP SETOPTINFO] {PROCEDURE {[Owner.]ProcedureName}[,...]}
{ }
{ALL {MODULES } }
{ {PROCEDURES} }
[REV END]
WHENEVER
{SQLERROR } {STOP }
WHENEVER {SQLWARNING} {CONTINUE }
{NOT FOUND } {GOTO Label }
{GO TO Label}
WHILE
WHILE Condition DO [Statement; [...]] ENDWHILE;