|
|
ALLBASE/SQL Database Administration Guide: HP 3000 MPE/iX Computer Systems > Appendix C SQL Syntax Summary |
|
ALTER TABLE [Owner.] TableName { AddColumnSpecification AddConstraintSpecification DropConstraintSpecification SetTypeSpecification SetPartitionSpecification } ADD { ColumnDefinition [,...] ) Column Definition } [ CLUSTERING ON CONSTRAINT [ConstraintID] ] ADD CONSTRAINT ( { UniqueConstraint ReferentialConstraint CheckConstraint [,...] ) } [ CLUSTERING ON CONSTRAINT [ConstraintID1] ] SET TYPE { PRIVATE PUBLICREAD PUBLIC PUBLICROW } [ RESET AUTHORITY PRESERVE AUTHORITY ] BEGIN WORK [Priority] [ RR CS RC RU ] [ LABEL { 'LabelString' :HostVariable} }] [[ PARALLEL NO ] FILL ] CLOSE CursorName [ USING {[SQL] DESCRIPTOR { SQLDA AreaName } :HostVariable [ [INDICATOR] :Indicator ] [ , ... ] }] CONNECT TO { 'DBEnvironmentName' :HostVariable1 } [ AS { 'ConnectionName' :HostVariable2 }] [ USER { 'UserID' :HostVariable3 } [USING :HostVariable4]] CREATE DBEFILE DBEFilename WITH PAGES = DBEFileSize, NAME = 'SystemFileName' [ , INCREMENT = DBEFileIncrSize [, MAXPAGES = DBEFileMaxSize]] [ ,TYPE = { TABLE INDEX MIXED }] [,DEVICE = volume;] CREATE [UNIQUE] [CLUSTERING] INDEX [Owner.] IndexNameON [Owner.] TableName( { ColumnName [ ASC DESC ]} [ , ... ]) CREATE PROCEDURE [Owner.] ProcedureName [ LANG = ProcLangName] [ ( ParameterDeclaration [, ParameterDeclaration] [ ... ]) ] [ WITH RESULT ResultDeclaration [, ResultDeclaration] [...]] AS BEGIN [ProcedureStatement] [ ... ] END [IN DBEFileSetName] ParameterNameParameterType [LANG = ParameterLanguage] [DEFAULT DefaultValue] [NOT NULL] [ [OUTPUT] [ONLY] ] CREATE RULE [Owner.] RuleName AFTER StatementType [,...] { ON OF FROM INTO } [Owner] .TableName [ REFERENCING { OLD AS OldCorrelationName NEW AS NewCorrelationName } [...]] [WHERE FiringCondition] EXECUTE PROCEDURE [OwnerName.] ProcedureName [ (ParameterValue [ ,...]) ] [IN DBEFileSetName] CREATE SCHEMA AUTHORIZATION AuthorizationName [ TableDefinition ViewDefinition IndexDefinition ProcedureDefinition RuleDefinition CreateGroup AddToGroup GrantStatement ] [...] CREATE [ PRIVATE PUBLICREAD PUBLIC PUBLICROW ] TABLE [Owner.] TableName [LANG = TableLanguageName] ( { ColumnDefinition UniqueConstraint ReferentialConstraint CheckConstraint } [ ,... ]) [ UNIQUE HASH ON (HashColumnName[ ,... ]) PAGES = PrimaryPages HASH ON CONSTRAINT [ConstraintID] PAGES = PrimaryPages CLUSTERING ON CONSTRAINT [ConstraintID]] [ IN PARTITION { PartitionName DEFAULT NONE }] [IN DBEFileSetName1] ColumnName { ColumnDataType LongColumnType [IN DBEFileSetName2]} [ LANG = ColumnLanguageName] [ [NOT] CASE SENSITIVE ] [ DEFAULT { Constant USER NULL CurrentFunction }] [ NOT NULL [{ UNIQUE PRIMARY KEY } [CONSTRAINT ConstraintID]] REFERENCES RefTableName [ (RefColumnName) [CONSTRAINT ConstraintID]] [...] CHECK (SearchCondition) [CONSTRAINT ConstraintID] [IN DBEFileSetName3]] [...] { UNIQUE PRIMARY KEY } (ColumnName [,...] ) [CONSTRAINT ConstraintID] FOREIGN KEY (FKColumnName [ ,... ]) REFERENCES RefTableName [ (RefColumnName [ ,... ]) ] [CONSTRAINT ConstraintID] CREATE TEMPSPACE TempSpaceName WITH [MAXFILEPAGES = MaxTempFileSize,] LOCATION ='PhysicalLocation' CREATE VIEW [Owner.] ViewName [ (ColumnName [ ,... ]) ] AS QueryExpression [IN DBEFileSetName] [ WITH CHECK OPTION [CONSTRAINT ConstraintID] ] DECLARE CursorName [IN DBEFileSetName] CURSOR FOR {{ QueryExpression SelectStatementName } [ FOR UPDATE OF {ColumnName} [,...] FOR READ ONLY ] ExecuteProcedureStatement ExecuteStatementName } DECLARE {LocalVariable} [,...] VariableType [LANG = VariableLangName] [ DEFAULT { Constant NULL CurrentFunction }] [NOT NULL] DESCRIBE [ OUTPUT INPUT RESULT ] StatementName { INTO [ [SQL] DESCRIPTOR ] USING [SQL] DESCRIPTOR } { SQLDA AreaName } EXECUTE { StatementName [Owner.] ModuleName [(SectionNumber)]} [ USING {[SQL] DESCRIPTOR {[INPUT] { SQLDA AreaName1 } [ AND OUTPUT { SQLDA AreaName2 }] OUTPUT { SQLDA AreaName } [INPUT] HostVariableSpecification1 [AND OUTPUT HostVariableSpecification2] OUTPUT HostVariableSpecification :Buffer [ ,:StartIndex [, :NumberOfRows]]}}] EXECUTE PROCEDURE [:ReturnStatusVariable= ] [Owner.] ProcedureName [ ( [ActualParameter]] [ , [ActualParameter] ] [ [ ... ]) ] [BULK]FETCH CursorName { INTO HostVariableSpecification USING {[SQL] DESCRIPTOR { SQLDA AreaName } HostVariableSpecification }} GENPLAN [WITH (HostVariableDefinition) FOR] { SQLStatement MODULE SECTION [Owner.] ModuleName(SectionName) PROCEDURE SECTION [Owner.] ProcedureName(SectionName) } GRANT { ALL [PRIVILEGES] { SELECT INSERT DELETE ALTER INDEX UPDATE [ ( {ColumnName} [ ,... ]) ] REFERENCES [ ( {ColumnName} [ ,... ]) ]} |,...| } ON {[Owner.] TableName [Owner.] ViewName } TO { DBEUserID GroupName ClassName PUBLIC } [,...] [WITH GRANT OPTION] [ BY { DBEUserID ClassName }] GRANT { RUN ON [Owner.] ModuleName EXECUTE ON PROCEDURE [Owner.] ProcedureName } { TO {{ DBEUserID GroupName ClassName } [,...] PUBLIC }} GRANT { CONNECT DBA INSTALL [AS OwnerID] MONITOR RESOURCE TO { DBEUserID GroupName ClassName } [,...]} IF Condition THEN [ Statement;[ ... ] ] [ ELSEIF Condition THEN [ Statement; [ ... ] ] ] [ ELSE [ Statement;[ ... ] ] ] ENDIF; [BULK] INSERT INTO {[Owner.] TableName [Owner.] ViewName } [ ( {ColumnName} [ ,... ]) ] VALUES ( { SingleRowValues BulkValues ? } ) { NULL USER :HostVariable [ [INDICATOR] :IndicatorVariable ] ? :LocalVariable :ProcedureParameter ::Built-inVariable ConversionFunction CurrentFunction [ + - ] { Integer Float Decimal } 'CharacterString' 0xHexadecimalString 'LongColumnIOString' } [,...] < { FileName [ .Group [.Account] ] %HeapAddress:LengthofHeap } [{ > >> >! } { FileName [ .Group [.Account] ] CharString$ CharString$CharString } >%$ ] INSERT INTO {[Owner.] TableName [Owner.] ViewName } [ (ColumnName [,...] ) ] QueryExpression OPEN CursorName [ KEEP CURSOR [ WITH LOCKS WITH NOLOCKS ]] [ USING {[SQL] DESCRIPTOR { SQLDA AreaName } HostVariableName [INDICATOR] :IndicatorVariable[ ,... ] }] PREPARE [REPEAT] { StatementName [Owner.] ModuleName [(SectionNumber] ) } [IN DBEFileSetName] FROM { 'String' :HostVariable } REMOVE { DBEUserID GroupName ClassName } [,...] FROM GROUP [Owner.] TargetGroupName REVOKE { ALL [PRIVILEGES] [ SELECT INSERT DELETE ALTER INDEX UPDATE [ ( {ColumnName} [ ,... ]) ] REFERENCES [ ( { ColumnName [,...]) } [,...] ] ON {[Owner.] TableName [Owner.] ViewName } FROM { DBEUserID GroupName ClassName PUBLIC } [,...] [CASCADE]]} REVOKE [RUN ON] [Owner.] ModuleName EXECUTE ON PROCEDURE [Owner.] ProcedureName FROM {{ DBEUserID GroupName ClassName } [,...] PUBLIC } REVOKE { CONNECT DBA INSTALL [AS OwnerID] MONITOR RESOURCE } FROM { DBEUserID GroupName ClassName } [,...] ROLLBACK WORK [ TO { SavePointNumber :HostVariable :LocalVariable :ProcedureParameter } RELEASE ] { QueryBlock (QueryExpression) } [ UNION [ALL] { QueryBlock QueryExpression) }] [...] SELECT [ ALL DISTINCT ] SelectList [INTO HostVariableSpecification] FROM FromSpec[ ,... ] [WHERE SearchCondition1] [GROUP BY GroupColumnList] [HAVING SearchCondition2] { * [Owner.] Table.* CorrelationName.* Expression [[Owner.] Table. ] ColumnName CorrelationName.ColumnName } [,...] { TableSpec (FromSpec) FromSpec NATURAL [ INNER LEFT [OUTER] RIGHT [OUTER] ] JOIN { TableSpec (FromSpec) } FromSpec [ INNER LEFT [OUTER] RIGHT [OUTER] ] JOIN { TableSpec (FromSpec) } { ON SearchCondition3 USING (ColumnList) }} SET DEFAULT { SECTIONSPACE TABLESPACE } TO DBEFILESET DBEFileSetName FOR PUBLIC SETOPT { CLEAR GENERAL { ScanAccess JoinAlgorithm } [,...] BEGIN { GENERAL { ScanAccess JoinAlgorithm }} [;...] END } SET SESSION { ISOLATION LEVEL { RR CS RC RU REPEATABLE READ SERIALIZABLE CURSOR STABILITY READ COMMITTED READ UNCOMMITTED :HostVariable1 } PRIORITY { Priority :HostVariable2 } LABEL { 'LabelString' :HostVariable3 } ConstraintType [,...] CONSTRAINTS { DEFERRED IMMEDIATE } DML ATOMICITY AT { STATEMENT ROW } LEVEL ON { TIMEOUT DEADLOCK } ROLLBACK { QUERY TRANSACTION } USER TIMEOUT [TO] { DEFAULT MAXIMUM TimeoutValue [ { SECONDS MINUTES } ] :HostVariable4 [ { SECONDS MINUTES } ]} TERMINATION AT { SESSION TRANSACTION QUERY RESTRICTED } LEVEL [ { PARALLEL NO } ] FILL } [,...] SET TRANSACTION { ISOLATION LEVEL { RR CS RC RU REPEATABLE READ SERIALIZABLE CURSOR STABILITY READ COMMITTED READ UNCOMMITTED :HostVariable1 } PRIORITY { Priority :HostVariable2 } LABEL { 'LabelString' :HostVariable3 } ConstraintType [,...] CONSTRAINTS { DEFERRED IMMEDIATE } DML ATOMICITY AT { STATEMENT ROW } LEVEL ON { TIMEOUT DEADLOCK } ROLLBACK { QUERY TRANSACTION } USER TIMEOUT [TO] { DEFAULT MAXIMUM TimeoutValue [ { SECONDS MINUTES } ] :HostVariable4 [ { SECONDS MINUTES } ]} TERMINATION AT { SESSION TRANSACTION QUERY RESTRICTED } LEVEL } [,...] SET USER TIMEOUT [TO] {{ TimeoutValue :HostVariable } [ SECONDS MINUTES DEFAULT MAXIMUM ]} START DBE 'DBEnvironmentName' [AS ConnectionName'] [MULTI] [BUFFER = (DataBufferPages,] LogBufferPages) TRANSACTION = MaxTransactions MAXIMUM TIMEOUT = { TimeoutValue [ SECONDS MINUTES NONE ]} DEFAULT TIMEOUT = { TimeoutValue [ SECONDS MINUTES MAXIMUM ]} RUN BLOCK = ControlBlockPages |,...| START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [MULTI] NEW [{ DUAL AUDIT } |...| LOG BUFFER = (DataBufferPages, LogBufferPages) LANG = LanguageName TRANSACTION = MaxTransactions MAXIMUM TIMEOUT = { TimeoutValue [ SECONDS MINUTES NONE ]} DEFAULT TIMEOUT = { TimeoutValue [ SECONDS MINUTES MAXIMUM ]} RUN BLOCK = ControlBlockPages DEFAULT PARTITION = { DefaultPartitionNumber NONE } COMMENT PARTITION = { CommentPartitionNumber DEFAULT NONE } MAXPARTITIONS = MaximumNumberOfPartitions AUDIT NAME = 'AuditName' { COMMENT DATA DEFINITION STORAGE AUTHORIZATION SECTION ALL } |...| AUDIT ELEMENTS DBEFile0Definition DBELogDefinition |,...| ] START DBE 'DBEnvironmentName' [AS 'ConnectionName'] [ MULTI NEWLOG [ { ARCHIVE DUAL AUDIT } |...| LOG BUFFER = (DataBufferPages, LogBufferPages) TRANSACTION = MaxTransactions MAXIMUM TIMEOUT = { TimeoutValue [ SECONDS MINUTES NONE ]} DEFAULT TIMEOUT = { TimeoutValue [ SECONDS MINUTES MAXIMUM ]} ] RUN BLOCK = ControlBlockPages DEFAULT PARTITION = { DefaultPartitionNumber NONE } COMMENT PARTITION = { CommentPartitionNumber DEFAULT NONE } MAXPARTITIONS = MaximumNumberOfPartitions AUDIT NAME = 'AuditName' { COMMENT DATA DEFINITION STORAGE AUTHORIZATION SECTION ALL } |...| AUDIT ELEMENTS |,...| NewLogDefinition ] TRANSFER OWNERSHIP OF {[TABLE] [Owner.TableName] [VIEW] [Owner.] ViewName PROCEDURE [Owner.] ProcedureName GROUP GroupName } TO NewOwnerName UPDATE {[Owner.] TableName [ Owner. ViewName ]} SET { ColumnName = { Expression 'LongColumnIOString' NULL }} [,...] [ WHERE SearchCondition] UPDATE { [Owner.] TableName [Owner.] ViewName } SET { ColumnName = { Expression 'LongColumnIOString' NULL }} [,...] WHERE CURRENT OF CursorName VALIDATE [ FORCE DROP SETOPTINFO ] { MODULE {{[Owner.] ModuleName } [,...] { SECTION [Owner.] ModuleName(Section Number) } [,...]} PROCEDURE {{[Owner.] ProcedureName } [,...] { SECTION [Owner.] ProcedureName(Section Number) } [,...]} ALL { MODULES PROCEDURES } [WITH AUTOCOMMIT]} |
|