SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η...

126
SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Transcript of SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η...

Page 1: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SQL Quick SyntaxGuide

Version 7.2April 1996Part No. 000-7880A

Page 2: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ii SQL Quick Syntax Gu

Published by INFORMIX® Press Informix Software, Inc.4100 Bohannon DriveMenlo Park, CA 94025

The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in theUnited States of America as indicated by an “®,” and in numerous other countries worldwide:

INFORMIX®; INFORMIX®-OnLine Dynamic Server; C-ISAM®

The following are worldwide trademarks of the indicated owners or their subsidiaries, registered in theUnited States of America as indicated by an “®,” and in numerous other countries worldwide:

X/Open Company Ltd.: UNIX®; X/Open®

Adobe Systems Incorporated: PostScript®

Some of the products or services mentioned in this document are provided by companies other than Informix.These products or services are identified by the trademark or servicemark of the appropriate companies. If youhave a question about one of these products or services, please contact the company in question directly.

Documentation Team: Diana Chase, Geeta Karmarkar, Tom Noronha

Copyright © 1981-1996 by Informix Software, Inc. All Rights Reserved.

No part of this work covered by the copyright hereon may be reproduced or used in any form or by anymeans—graphic, electronic, or mechanical, including photocopying, recording, taping, or information storageand retrieval systems—without permission of the publisher.

RESTRICTED RIGHTS LEGEND

Software and accompanying materials acquired with United States Federal Government funds or intended foruse within or for any United States federal agency are provided with “Restricted Rights” as defined in DFARS252.227-7013(c)(1)(ii) or FAR 52.227-19.

ide

Page 3: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Introduction

The following statements and segments for Version 7.2 are presented in thisguide:

■ SQL statements

■ SQL segments

■ Stored Procedure Language (SPL) statements

■ INFORMIX-OnLine/Optical statements

This guide presents the syntax diagrams for statements and segments butdoes not provide any explanatory information. For complete descriptions ofSQL and SPL statements and segments, see the Informix Guide to SQL: Syntax.For complete descriptions of INFORMIX-OnLine/Optical statements, see theINFORMIX-OnLine/Optical User Manual. These manuals provide the follow-ing information for each statement:

■ A brief introduction that explains the purpose of the statement

■ A syntax diagram that is identical to the syntax diagram in theSQL Quick Syntax Guide

■ A syntax table that provides the purpose, restrictions, and syntax forall italicized elements in a syntax diagram

■ Rules and examples of usage

■ References to related statements

Introduction 3

Page 4: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Syntax Conventions

Syntax ConventionsThis section describes conventions for syntax diagrams. Each diagramdisplays the sequences of required and optional keywords, terms, andsymbols that are valid in a given statement, command line, or otherspecification, as in the following diagram of the SET EXPLAIN statement.

Each syntax diagram begins at the upper left corner and ends at the upperright corner with a vertical terminator. Between these points, any path thatdoes not stop or reverse direction describes a possible form of the statement.(For a few diagrams, notes in the text identify path segments that aremutually exclusive.)

Syntax elements in a path represent terms, keywords, symbols, and segmentsthat can appear in your statement. Except for separators in loops, which thepath approaches counterclockwise from the right, the path alwaysapproaches elements from the left and continues to the right. Unlessotherwise noted, at least one blank character separates syntax elements.

SET EXPLAIN

OFF

+ ON

4 INFORMIX

Page 5: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Elements That Can Appear on the Path

Elements That Can Appear on the PathYou might encounter one or more of the following elements on a path.

Element Description

KEYWORD A word in UPPERCASE characters is a keyword. Youmust spell the word exactly as shown; however, youcan use either uppercase or lowercase characters.

( . , ; @ + * - / ) Punctuation and other non-alphanumeric charactersare literal symbols that you must enter exactly asshown.

' ' Single quotes are literal symbols that you must enteras shown.

variable A word in italics represents a value that you mustsupply. A table immediately following the diagramexplains the value.

A term shown in a rectangle represents a subdiagramon the same page (if no page number is supplied) or aspecified page, as if the subdiagram were spliced intothe diagram at this point. The same subdiagram can berepresented by rectangles of different shapes, as inthese symbols for the ADD Clause subdiagram.

This reference represents a statement described in theINFORMIX-OnLine/Optical User Manual. Imaginethat the statement is spliced into the diagram at thispoint.

An icon is a warning that this path is valid only forsome products, or only under certain conditions.Characters on the icons indicate what products orconditions support the path.

These icons might appear in a syntax diagram:

This path is valid only forINFORMIX-OnLine Dynamic Server.

This path is valid only for INFORMIX-SE.

(1 of 3)

ADDClausep. 1-14

ADD Clause

CREATE OPTICALCLUSTERStatement

see INFORMIX-OnLine/OpticalUser Manual

OL

OL

SE

Introduction 5

Page 6: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Elements That Can Appear on the Path

This path is valid only for DB-Access.

This path is valid only for SQL state-ments in INFORMIX-ESQL/C andINFORMIX-ESQL/COBOL.

This path is valid only forINFORMIX-ESQL/C.

This path is valid only forINFORMIX-ESQL/COBOL.

This path is valid only for only if you areusing Informix Stored ProcedureLanguage (SPL).

This path is valid only forINFORMIX-OnLine/Optical.

This path is an Informix extension toANSI SQL-92 entry-level standard SQL. Ifyou initiate Informix extension checkingand include this syntax branch, youreceive a warning. If you have set theDBANSIWARN environment variable atcompile time, or have used the -ansicompile flag, you receive warnings atcompile time. If you have DBANSIWARNset at runtime, or if you compiled withthe -ansi flag, warning flags are set in thesqlwarn structure.

This path is valid only only if yourdatabase or application is GLS compliant.

A shaded option is the default. If you do not specifyany of the available options, this option is in effect bydefault.

Syntax that is enclosed between a pair of arrows isa subdiagram.

Element Description

(2 of 3)

D/B

ESQL

E/C

E/CO

SPL

OP

+

GLS

ALL

6 INFORMIX

Page 7: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Elements That Can Appear on the Path

The vertical line is a terminator. This symbol onlyappears at the right, indicating that the syntaxdiagram is complete.

A branch below the main path indicates an optionalpath. (Any term on the main path is required, unlessa branch can circumvent it.)

A set of multiple branches indicates that a choiceamong more than two different paths is available.

A loop indicates a path that you can repeat.Punctuation along the top of the loop indicates theseparator symbol for list items, as in this example. If nosymbol appears, a blank space is the separator,

A gate ( ) on a path indicates that you can only usethat path the indicated number of times, even if it ispart of a larger loop. Here you can specify size no morethan three times within this statement segment.

Element Description

(3 of 3)

NOT

NULLIS

ERROR

WARNING

NOT FOUND

statement

variable

,

3 size

, 3

Introduction 7

Page 8: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

How to Read a Syntax Diagram

How to Read a Syntax DiagramFigure 1 shows a syntax diagram that uses most of the path elements that arelisted in the previous table.

To use this diagram to construct a statement, start at the top left with thekeywords CREATE DATABASE. Then follow the diagram to the right,proceeding through the options that you want.

Figure 1Elements of a Syntax Diagram

OL Log Clause

CREATE DATABASE database name

IN dbspace

LOG IN 'pathname'

MODE ANSI

BUFFERED

LOG

LOG MODE ANSI

OL Log Clause

WITH

WITH

SE Log Clause

OL

SE SE Log Clause

OL

Main diagram

Subdiagram

8 INFORMIX

Page 9: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Sample-Code Conventions

To read the example syntax diagram

1. You must type the words CREATE DATABASE.

2. You must supply a database name.

3. You can stop, taking the direct route to the terminator, or you cantake one or more of the optional paths.

4. If desired, you can designate a dbspace by typing the word IN and adbspace name.

5. If desired, you can specify logging. Here, you are constrained by thedatabase server with which you are working:

❑ If you are using INFORMIX-OnLine Dynamic Server, go to thesubdiagram named OL Log Clause. Follow the subdiagram bytyping the keyword WITH, then choosing and typing either LOG,BUFFERED LOG, or LOG MODE ANSI. Then follow the arrow backto the main diagram.

❑ If you are using INFORMIX-SE, go to the subdiagram named SELog Clause. Follow the subdiagram by typing the keywordsWITH LOG IN, typing a quote, supplying a pathname, and clos-ing the quotes. You can then choose the MODE ANSI optionbelow the line or continue to follow the line across.

6. Once you are back at the main diagram, you come to the terminator.Your CREATE DATABASE statement is complete.

Sample-Code ConventionsExamples of SQL code occur throughout this manual. Except where noted,the code is not specific to any single Informix application development tool.If only SQL statements are listed in the example, they are not delimited bysemicolons. To use this SQL code for a specific product, you must apply thesyntax rules for that product. For example, if you are using the Query-language option of DB-Access, you must delimit multiple statements withsemicolons. If you are using an SQL API, you must use EXEC SQL and asemicolon (or other appropriate delimiters) at the start and end of eachstatement, respectively.

Introduction 9

Page 10: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A
Page 11: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ion

SQL Statements

Sect

Page 12: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A
Page 13: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALLOCATE DESCRIPTOR

ALLOCATE DESCRIPTOR

ALTER FRAGMENT

WITH MAX occurrences

occurrencesvariable

descriptorvariable

' descriptor 'ALLOCATEDESCRIPTORESQL

+

ALTER FRAGMENT ON

ADDClausep. 16

DROPClausep. 16

DETACHClausep. 14

MODIFYClausep. 16

INITClausep. 14

ATTACHClausep. 14

TABLE

INDEX surviving index

surviving table+

OL

SQL Statements 13

Page 14: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER FRAGMENT

ATTACH Clause

DETACH Clause

INIT Clause

ATTACHClause

,

dbspace

ATTACH

BEFORE

AFTER

consumed table

surviving table

AS frag-expression

1

AS REMAINDER

,

AS frag-expression

DETACHClause

DETACH dbspace-name new table

INITClause

INIT FRAGMENTBY

Clausefor TablesWITH ROWIDS

IN dbspace

FRAGMENTBY

Clausefor Indexes

14 SQL Quick Syntax Guide

Page 15: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER FRAGMENT

FRAGMENT BY Clause for Tables

FRAGMENT BY Clause for Indexes

EXPRESSION

,

FRAGMENTBY

Clausefor Tables

,

dbspaceROUND ROBIN INFRAGMENT BY

frag-expressionIN dbspace

REMAINDER INremainder dbspace,

dbspace ,

frag-expressionIN dbspace

,

FRAGMENTBY

Clausefor Indexes

FRAGMENT BY

REMAINDER INremainder dbspace,

EXPRESSION

,

frag-expressionIN dbspace

frag-expressionIN dbspace

,

SQL Statements 15

Page 16: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER FRAGMENT

ADD Clause

DROP Clause

MODIFY Clause

ADDClause

ADD new dbspace

REMAINDER IN new dbspace

AFTER

BEFORE existing dbspace

frag-expression IN new dbspace

DROPClause

DROP dbspace-name

MODIFYClause

mod-dbspace TOMODIFY

REMAINDER IN new-dbspace

frag-expression IN new-dbspace

1

,

16 SQL Quick Syntax Guide

Page 17: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER INDEX

ALTER INDEX

ALTER TABLE

+

NOT

Index Namep. 104 TOALTER INDEX CLUSTER

SynonymNamep. 108

OL

Table Namep. 108

ALTER TABLE

DROP Clause p. 21

MODIFY Clause p. 21

LOCK MODEClause p. 23

MODIFY NEXT SIZEClause p. 23

DROP CONSTRAINTClause p. 23

ADD CONSTRAINTClause p. 22

ADD Clausep. 18

1

,

1

1 ADD ROWIDS

DROP ROWIDS

+

SQL Statements 17

Page 18: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER TABLE

ADD Clause

Add Column Clause

,

Add ColumnClause

Add ColumnClause

( )

ADD

ADD Clause

Add ColumnClause

columnnameBEFORE

newcolumnname

Data Typep. 94

DEFAULTClausep. 19 New Column

ConstraintDefinition

p. 19

18 SQL Quick Syntax Guide

Page 19: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER TABLE

DEFAULT Clause

New Column Constraint Definition

DEFAULTClause

DEFAULT literal

NULL

CURRENTp. 97

DATETIMEField Qualifier

p. 95

USERp. 97

TODAYp. 97

SITENAMEp. 97

DBSERVERNAMEp. 97

Constraint-Mode

Definitionsp. 20

UNIQUE

DISTINCT

PRIMARYKEY

REFERENCESClausep. 20

CHECKClausep. 20

Constraint-Mode

Definitionsp. 20

+

New ColumnConstraintDefinition

+NOTNULL

+

SQL Statements 19

Page 20: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER TABLE

Constraint-Mode Definitions

REFERENCES Clause

CHECK Clause

CONSTRAINT

FILTERING

DISABLED

WITHERROR

Constraint-ModeDefinitions

ConstraintNamep. 93

ENABLED

WITHOUTERROR

REFERENCESClause

( )

,

columnname

REFERENCES

ON DELETE CASCADE

+

table name

OL

Condition p. 89

CHECK ( )

CHECKClause

20 SQL Quick Syntax Guide

Page 21: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER TABLE

DROP Clause

MODIFY Clause

Modify Column Clause

,

column name

column nameDROP

( )

DROPClause

,

Modify ColumnClause

( )

MODIFY

Modify ColumnClause

MODIFYClause

,

Modify ColumnClause

columnname Data Type

p. 94 DEFAULTClausep. 19 New Column

ConstraintDefinition

p. 19

SQL Statements 21

Page 22: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER TABLE

ADD CONSTRAINT Clause

Table-Level Constraint Definition

,

Table-LevelConstraintDefinition

( )

ADD CONSTRAINT

ADD CONSTRAINTClause

Table-LevelConstraintDefinition

UNIQUE

FOREIGN KEYREFERENCES

Clausep. 20

CHECKClausep. 20

columnname

)(

Table-LevelConstraint Definition

PRIMARYKEY ,

+

Constraint-ModeDefinitions

p. 20

+

DISTINCT

columnname

)(

,

22 SQL Quick Syntax Guide

Page 23: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ALTER TABLE

DROP CONSTRAINT Clause

MODIFY NEXT SIZE Clause

LOCK MODE Clause

,

( )

DROP CONSTRAINT

ConstraintNamep. 93

ConstraintNamep. 93

DROP CONSTRAINTClause

MODIFY NEXT SIZEClause

kbytesMODIFY NEXT SIZE

LOCK MODEClause

LOCK MODE PAGE

ROW

( )

SQL Statements 23

Page 24: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

BEGIN WORK

BEGIN WORK

CHECK TABLE

CLOSE

CLOSE DATABASE

COMMIT WORK

BEGIN WORK+

SE

DB+

CHECK TABLETableNamep. 108

ESQL CLOSE cursor id

+ CLOSE DATABASE

COMMIT WORK

24 SQL Quick Syntax Guide

Page 25: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CONNECT

CONNECT

Database Environment

connectionname

AS

conn_nmvariableAS

WITH CONCURRENT TRANSACTION

DatabaseEnvironment

CONNECT TO

DEFAULT

USERClausep. 26

+

''

ESQLESQL

ESQL

'dbname'

db_env variable

DatabaseEnvironment

'dbname@dbservername'

'pathname/dbname'

'@dbservername'

'pathname/dbname@dbservername'

SE

ESQL

SQL Statements 25

Page 26: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE AUDIT

USER Clause

CREATE AUDIT

CREATE DATABASE

SE LOG Clause

USERClause

user identifierUSER

user_idvariable

USING auth variable' '

SE+

' pathname 'CREATE AUDIT FOR IN

SynonymNamep. 108

TableNamep. 108

OL Log Clause

SE Log ClauseSEOL

OL

IN dbspace

+ CREATEDATABASE

DatabaseNamep. 93

SE Log Clause

WITH LOG IN ' pathname '

MODE ANSI

26 SQL Quick Syntax Guide

Page 27: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE INDEX

OL LOG Clause

CREATE INDEX

OL Log Clause

BUFFERED

LOG

LOG MODE ANSI

WITH

INDEXCREATE IndexDefinition

p. 28

IN dbspace

FRAGMENTBY

EXPRESSIONClausep. 28

CLUSTER

+

OLOL

FILLFACTOR percent

UNIQUE

DISTINCT

Object Modesfor Unique

Indexesp. 28

Object Modesfor Duplicate

Indexesp. 29

SQL Statements 27

Page 28: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE INDEX

Index Definition

FRAGMENT BY EXPRESSION Clause

Object Modes for Unique Indexes

SynonymNamep. 108

IndexDefinition

ON

,

column name

DESC

( )IndexNamep. 104

ASC

table name

FRAGMENT BYEXPRESSION

Clause

FRAGMENT BYEXPRESSION REMAINDER IN

remainderdbspace,

frag-expressionIN dbspace

,

frag-expressionIN dbspace

,

Object Modes forUnique Indexes

ENABLED

DISABLED

WITHOUTERROR

WITHERROR

FILTERING

28 SQL Quick Syntax Guide

Page 29: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE PROCEDURE

Object Modes for Duplicate Indexes

CREATE PROCEDURE

Parameter Option

Object Modes forDuplicate Indexes

ENABLED

DISABLED

ProcedureNamep. 107

StatementBlockp. 31

CREATE PROCEDURE

ENDPROCEDURE

( )

DBA,

ParameterRETURNING

Clausep. 30

,

Quoted Stringp. 107

DOCUMENTWITH

LISTING IN ' pathname '

;

+

Parameter

.LIKE

variablename

SQL Data Type(Subset)

p. 94

tablename

columnname

defaultvalue

REFERENCES BYTE

TEXT DEFAULTNULL

DEFAULT

SQL Statements 29

Page 30: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE PROCEDURE

RETURNING Clause

,

SQL Data Type(Subset)

p. 94

;

BYTE

RETURNING

TEXT

REFERENCES

RETURNINGClause

30 SQL Quick Syntax Guide

Page 31: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE PROCEDURE

Statement Block

Statement Block BEGIN END

CONTINUEStatement

p. 113

IF Statementp. 117

EXIT Statementp. 115

FOR Statementp. 116

FOREACH Statementp. 117

LET Statementp. 119

RAISE EXCEPTIONStatement

p. 119

RETURN Statementp. 120

TRACE Statementp. 120

WHILE Statementp. 120

SYSTEM Statementp. 120

EXECUTE PROCEDUREStatement

p. 50

CALL Statementp. 113ON

EXCEPTIONStatement

p. 119

DEFINEStatement

p. 114

Statement Block

Subset of SQLStatementsee SQLS

;

SQL Statements 31

Page 32: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE PROCEDURE FROM

CREATE PROCEDURE FROM

CREATE ROLE

CREATE SCHEMA

ESQL ' filename '

variablename

CREATE PROCEDURE FROM+

CREATE ROLE role name+

OL

+CREATE INDEX

Statementp. 27

CREATE VIEWStatement

p. 44

CREATE SYNONYMStatement

p. 33

GRANTStatement

p. 55

+

user name

CREATE SCHEMAAUTHORIZATION

CREATE TABLEStatement

p. 33DB

CREATE TRIGGERStatement

p. 40

CREATE OPTICALCLUSTERStatement

p. 123

OP

32 SQL Quick Syntax Guide

Page 33: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE SYNONYM

CREATE SYNONYM

CREATE TABLE

Column-Definition Option

View Namep. 109

+ CREATE SYNONYM FOR

PUBLIC

PRIVATE

SynonymNamep. 108

Table Namep. 108

,

TABLECREATE

StorageOptionp. 39

TableNamep. 108

,

,Column

Definition( )

Table-LevelConstraintDefinition

p. 36TEMPTABLEClausep. 37

+WITH

ROWIDS

OL

columnname

DEFAULTClausep. 34

Data Typep. 94

ColumnDefinition

Column-LevelConstraintDefinition

p. 35

SQL Statements 33

Page 34: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TABLE

DEFAULT Clause

DEFAULTClause

literalDEFAULT

DATETIMEField Qualifier

p. 95

USERp. 97

SITENAMEp. 97

DBSERVERNAMEp. 97

TODAYp. 97

NULL

CURRENTp. 97

+

+

+

+

OL

34 SQL Quick Syntax Guide

Page 35: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TABLE

Column-Level Constraint-Definition Option

Constraint-Mode Definitions

UNIQUE

DISTINCT

PRIMARYKEY

REFERENCESClausep. 36

CHECKClausep. 36

Constraint-Mode

Definitions

+

Column-LevelConstraintDefinition

+

Constraint-Mode

Definitions

+

NOTNULL

CONSTRAINT

FILTERING

DISABLED

WITHERROR

Constraint-ModeDefinitions

ConstraintNamep. 93

ENABLED

WITHOUTERROR

SQL Statements 35

Page 36: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TABLE

REFERENCES Clause

CHECK Clause

Table-Level Constraint-Definition Option

REFERENCESClause

( )

,

columnname

REFERENCES

ON DELETE CASCADE

+OL

tablename

CHECKClause

( )Conditionp. 89

CHECK

UNIQUE

FOREIGN KEYREFERENCES

Clause

CHECKClause

columnname

)(

Table-LevelConstraint Definition

PRIMARYKEY ,

+

Constraint-ModeDefinitions

p. 35

+

DISTINCT

columnname

)(

,

36 SQL Quick Syntax Guide

Page 37: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TABLE

TEMP TABLE Clause

Temporary Table Column Definition

( )

Temp TableTable-LevelConstraint

p. 38

,

TEMPTABLE

temptablename ,

,

TemporaryTable

ColumnDefinition

TEMP TABLEClause

WITH NO LOG

columnname

NOT NULLDEFAULTClausep. 34

Data Typep. 94

Temporary Table ColumnDefinition

Temp TableColumn

ConstraintDefinition

p. 38

SQL Statements 37

Page 38: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TABLE

Temp Table Column Constraint Definition

Temp Table Table-Level Constraint

PRIMARYKEY

CHECKClausep. 36

Temp Table ColumnConstraint Definition

DISTINCT

UNIQUE

+

UNIQUE columnname

,

)(

CHECKClausep. 36

Temp Table Table-LevelConstraint

DISTINCT

PRIMARYKEY

+

38 SQL Quick Syntax Guide

Page 39: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TABLE

Storage Option

FRAGMENT BY Clause

Extent Option

StorageOption

LOCK MODEClause p. 40

Extent OptionIN dbspace

FRAGMENTBY

Clause

SE IN 'pathname'

OL

,

dbspaceROUND ROBIN INFRAGMENT BY

EXPRESSION

,

frag-expressionIN dbspace

FRAGMENTBY

Clause

,REMAINDER IN

remainderdbspace

dbspace ,

frag-expressionIN dbspace

,

Extent Option

NEXT SIZEnext

kbytesEXTENT SIZEfirst

kbytes

SQL Statements 39

Page 40: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TRIGGER

LOCK MODE Clause

CREATE TRIGGER

LOCK MODE Clause

LOCK MODE

ROW

PAGE

TriggerNamep. 41

CREATE TRIGGER

DB

DELETE

UPDATEClausep. 41

ESQL

ON

ON

InsertREFERENCING

Clausep. 42

ActionClausep. 41

TableNamep. 108

DeleteREFERENCING

Clausep. 42

ActionClausep. 41

TableNamep. 108

UpdateREFERENCING

Clausep. 42

ActionClausep. 41

TableNamep. 108

INSERT ON

ActionClause

Referencingp. 43

ActionClause

Referencingp. 43

ActionClause

Referencingp. 43

+Trigger Object

Modesp. 43

40 SQL Quick Syntax Guide

Page 41: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TRIGGER

Trigger Name

UPDATE Clause

Action Clause

TriggerName

Identifierp. 104

owner.

,

column name

UPDATE

UPDATEClause

OF

FOR EACHROW

BEFORETriggeredAction List

p. 43

ActionClause

TriggeredAction List

p. 43AFTER

TriggeredAction List

p. 43

FOR EACHROW

TriggeredAction List

p. 43

AFTERTriggeredAction List

p. 43

AFTERTriggeredAction List

p. 43

SQL Statements 41

Page 42: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TRIGGER

Insert REFERENCING Clause

Delete REFERENCING Clause

Update REFERENCING Clause

REFERENCING NEW correlation name

InsertREFERENCING

Clause

AS

DeleteREFERENCING

Clause

REFERENCING OLD correlation name

AS

UpdateREFERENCING

Clause

1

REFERENCING

AS

OLD correlation name

AS

NEW correlation name

1

42 SQL Quick Syntax Guide

Page 43: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE TRIGGER

Action Clause Referencing

Triggered Action List

Trigger Object Modes

TriggeredAction ListBEFORE

FOR EACHROW

TriggeredAction List

AFTERTriggeredAction List

ActionClause

Referencing

,

Conditionp. 89WHEN

INSERTStatement

p. 59

UPDATEStatement

p. 84

DELETEStatement

p. 45

EXECUTEPROCEDURE

p. 50

TriggeredAction List

,

( )

( )

Trigger ObjectModes

DISABLED

ENABLED

SQL Statements 43

Page 44: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CREATE VIEW

CREATE VIEW

DATABASE

DEALLOCATE DESCRIPTOR

WITH CHECKOPTION

columnname

CREATE VIEW

)(

, ASView

Namep. 109

SELECTStatement(Subset)

p. 69

DATABASE

EXCLUSIVE

+ DatabaseNamep. 93

descriptorvariable

DEALLOCATE DESCRIPTOR 'descriptor '+

ESQL

44 SQL Quick Syntax Guide

Page 45: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

DECLARE

DECLARE

DELETE

SELECTStatement

p. 69

statement id

WITHHOLD

columnname

OF

WITHHOLD

SCROLLCURSOR

+

+

cursorvariable

EXECUTEPROCEDURE

Statementp. 50

DECLARE cursorid CURSOR

+

FORINSERT

Statement(Subset)

p. 59

ESQL

,

statement idvariable

+

+

FOR

FOR READ ONLY

+

FORUPDATE

SELECTStatement(Subset)

p. 69

ViewNamep. 109 ESQL

Conditionp. 89

SynonymNamep. 108

DELETE FROM

WHERE

CURRENT OFcursorname

TableNamep. 108

SQL Statements 45

Page 46: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

DESCRIBE

DESCRIBE

DISCONNECT

DROP AUDIT

DROP DATABASE

INTO sqlda pointer

+

statementid variable

ESQLDESCRIBE statement id

USINGSQL DESCRIPTOR

E/C

' descriptor '

descriptorvariable

' connection name '

DISCONNECT CURRENT

conn_nm variable

ALL

DEFAULT

+

ESQL

DROP AUDIT FOR Table Namep. 108

SynonymNamep. 108

SE+

+ DROP DATABASE DatabaseNamep. 93

46 SQL Quick Syntax Guide

Page 47: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

DROP INDEX

DROP INDEX

DROP PROCEDURE

DROP ROLE

DROP SYNONYM

DROP TABLE

+ DROP INDEX Index Namep. 104

ProcedureNamep. 107

+ DROP PROCEDURE

DROP ROLE role name+

OL

+ DROP SYNONYMSynonym

Namep. 108

SynonymNamep. 108

DROP TABLETableNamep. 108

CASCADE

RESTRICT

+

SQL Statements 47

Page 48: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

DROP TRIGGER

DROP TRIGGER

DROP VIEW

EXECUTE

DROP TRIGGERTriggerNamep. 41

DBESQL

+

SynonymNamep. 108

DROP VIEWViewNamep. 109

CASCADE

RESTRICT

+

EXECUTE statement id

statementid variable INTO

Clausep. 49

USINGClausep. 49

ESQL

48 SQL Quick Syntax Guide

Page 49: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

EXECUTE

USING Clause

INTO Clause

DESCRIPTOR storage sqlda pointer

storage descriptor variable

,

E/C

SQL DESCRIPTOR

storagevariablename

'storage descriptor '

+

INDICATOR

E/C

USING

USINGClause

storageindicatorvariable

storageindicatorvariable

:

DESCRIPTOR output sqlda pointer

output descriptor variable

,

E/C

SQL DESCRIPTOR

outputvariablename

' output descriptor '

: outputindicatorvariable

+

outputindicatorvariable

INDICATOR

E/C

INTO

INTOClause

SQL Statements 49

Page 50: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE

EXECUTE PROCEDURE

Argument

statement variable name

EXECUTE IMMEDIATEQuotedStringp. 107

+

ESQL

,

ProcedureNamep. 107

( )+ EXECUTEPROCEDURE

,Argument

INTO

ESQL

SPLhost

variable

Argument

parameter name =

Expressionp. 96

SELECTStatement(Singleton)

p. 69

50 SQL Quick Syntax Guide

Page 51: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

FETCH

FETCH

FLUSH

+

+

NEXT

cursorvariable

USING 'descriptor'SQLDESCRIPTOR

descriptorvariable

DESCRIPTOR sqldapointer

E/C

,

+

INDICATOR

+

PRIOR

rowposition

RELATIVE

ABSOLUTE

rowposition

-

PREVIOUS

FIRST

LAST

CURRENT

data structure

indicatorvariable

indicatorvariable

FETCHESQL cursor id INTOdata

variable

cursorvariable

FLUSH cursorid

ESQL+

SQL Statements 51

Page 52: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

FREE

FREE

GET DESCRIPTOR

cursorvariable

FREE cursor id

statementid variable

statementid

+ESQL

,

itemnumber

VALUE

descriptor variable

itemnumbervariable

DescribedItem Info

p. 53

ESQL GET DESCRIPTOR 'descriptor ' host variable = COUNT+

52 SQL Quick Syntax Guide

Page 53: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

GET DIAGNOSTICS

Described Item Info

GET DIAGNOSTICS

Statement Clause

DescribedItem Info

field hostvariable

= TYPE

LENGTH

PRECISION

SCALE

NULLABLE

INDICATOR

NAME

DATA

ITYPE

IDATA

ILENGTH

GET DIAGNOSTICS StatementClause

EXCEPTIONClausep. 54

+

ESQL

MORE

NUMBER

st_var =

,

ROW_COUNT

StatementClause

SQL Statements 53

Page 54: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

GET DIAGNOSTICS

EXCEPTION Clause

CLASS_ORIGIN

RETURNED_SQLSTATE

SERVER_NAME

en_var

except_num ex_var =

,EXCEPTION

SUBCLASS_ORIGIN

MESSAGE_TEXT

CONNECTION_ALIAS

MESSAGE_LENGTH

EXCEPTIONClause

54 SQL Quick Syntax Guide

Page 55: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

GRANT

GRANT

Database-Level Privileges

Table Privilegesfor PUBLICand Users

p. 56

,

user

PUBLICDatabase-Level

PrivilegesGRANT TO+

+

,

user

PUBLICTO+

role name

' user '

rolename

' rolename'

WITHGRANTOPTION

OL' user '

EXECUTE ONProcedure

Namep. 107

Table Privilegesfor Roles

p. 56

+

OL

Database-LevelPrivileges

CONNECT

RESOURCE

DBA

SQL Statements 55

Page 56: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

GRANT

Table Privileges for PUBLIC and Users

Table Privileges for Roles

+

ViewNamep. 109

Table-Level

Privilegesp. 57

SynonymNamep. 108

,

' user '

user

TO PUBLICONTableNamep. 108

WITH GRANT OPTION AS grantor

+

Table Privileges forPUBLIC and Users

ViewNamep. 109

Table-Level

Privilegesp. 57

SynonymNamep. 108

' role name'

TO role nameONTableNamep. 108

AS grantor

Table Privilegesfor Roles

56 SQL Quick Syntax Guide

Page 57: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

GRANT

Table-Level Privileges

,

columnname

ALL

PRIVILEGES

DELETE

SELECT

UPDATE

REFERENCES

INDEX

ALTER

( )

+

+

INSERT

,

Table-Level Privileges

,

columnname

( )

,

columnname

( )

+

SQL Statements 57

Page 58: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

GRANT FRAGMENT

GRANT FRAGMENT

Fragment-Level Privileges

ONGRANT

FRAGMENTFragment-Level

Privileges+

OLdbspace( )tablename

,

user

,

'user '

TO

WITH GRANTOPTION

AS grantor

ALL

INSERT

UPDATE

,

DELETE

Fragment-LevelPrivileges

58 SQL Quick Syntax Guide

Page 59: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

INFO

INFO

INSERT

INFO TABLES

COLUMNS

INDEXES

ACCESS

PRIVILEGES

FORTableNamep. 108

STATUS

REFERENCES

FRAGMENTS

OL

DB

+

,

SynonymNamep. 108

,

columnname

( )

INSERT INTOTableName p. 108

ViewName p. 109

EXECUTE PROCEDUREProcedure

Namep. 107

Argumentp. 60

)(

VALUES Clause p. 61

SELECTStatement(Subset)

p. 69

+

SQL Statements 59

Page 60: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

INSERT

Argument

parametername

=SELECT

Statement(Singleton)

p. 69

Expressionp. 96

Argument

60 SQL Quick Syntax Guide

Page 61: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

INSERT

VALUES Clause

Quoted Stringp. 107

USERp. 97

SITENAME p. 97

Literal Number p. 107

Literal DATETIME p. 105

Literal INTERVAL p. 106

OL

NULL

VALUES Clause

+

DBSERVERNAMEp. 97

variablenameESQLVALUES

TODAY p. 97

CURRENT p. 97

: indicator variable

$ indicator variable

)(

,

+

SQL Statements 61

Page 62: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

LOAD

LOAD

LOCK TABLE

,

INSERT INTO

DELIMITER ' delimiter '

columnname

TableNamep. 108

LOAD FROM ' filename 'DB+

))

ViewNamep. 109

SynonymNamep. 108

IN SHARE

EXCLUSIVE

MODELOCK TABLETableNamep. 108

SynonymNamep. 108

+

62 SQL Quick Syntax Guide

Page 63: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

OPEN

OPEN

OUTPUT

PREPARE

,

variable name

E/C

SQL DESCRIPTOR

descriptorvariable

cursorid

USING

OPEN

'descriptor'

sqldapointer

DESCRIPTOR

ESQL

WITH REOPTIMIZATION

E/C

+cursorvariable

OUTPUT TO

WITHOUTHEADINGS

SELECTStatement

p. 69

DB+ filename

PIPE program

statementvariablename

PREPARE statementid FROM+

ESQL QuotedString p. 107

statementid variable

SQL Statements 63

Page 64: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

PUT

PUT

RECOVER TABLE

RENAME COLUMN

SQL DESCRIPTOR

descriptorvariable

USING ' descriptor '

cursorvariable

sqldapointerDESCRIPTOR

FROM

E/C

,variablename

: indicatorvariable

+

indicatorvariable

INDICATOR

cursoridPUT

ESQL+

$ indicatorvariable

TableNamep. 108

RECOVER TABLESE+

.old column name TOTableNamep. 108

RENAME COLUMN+ new column name

64 SQL Quick Syntax Guide

Page 65: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

RENAME DATABASE

RENAME DATABASE

RENAME TABLE

REPAIR TABLE

TORENAME DATABASE new database nameOL

+ old database name

TOTableNamep. 108

RENAME TABLE+ new tablename

SE

+

TableNamep. 108

REPAIR TABLEDB

SQL Statements 65

Page 66: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

REVOKE

REVOKE

Procedure Namep. 107

,

user

'user '

REVOKE FROM PUBLIC

EXECUTE ON

+

Database-Level

Privilegesp. 67

,

user

'user '

ON FROM PUBLICTable-LevelPrivileges

p. 67

CASCADE

RESTRICT

OL

rolename

'

role name

OL

rolename'

OL

table name

view name

synonymname

,

user

'user '

FROM PUBLIC

ONTable-LevelPrivileges

p. 67table name

view name

synonymname

66 SQL Quick Syntax Guide

Page 67: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

REVOKE

Table-Level Privileges

Database-Level Privileges

ALL

PRIVILEGES

SELECT

UPDATE

INSERT

DELETE

INDEX

ALTER

,

REFERENCES

Table-LevelPrivileges

CONNECT

RESOURCE

DBA

Database-LevelPrivileges

SQL Statements 67

Page 68: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

REVOKE FRAGMENT

REVOKE FRAGMENT

Fragment-Level Privileges

ROLLBACK WORK

ONREVOKE

FRAGMENTFragment-Level

Privileges+

OL

FROM

dbspace( )

tablename

,user

,

'user '

ALL

INSERT

UPDATE

,

DELETE

Fragment-LevelPrivileges

ROLLBACK

WORK

68 SQL Quick Syntax Guide

Page 69: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ROLLFORWARD DATABASE

ROLLFORWARD DATABASE

SELECT

+ ROLLFORWARD DATABASESE Database

Namep. 93

WHEREClausep. 72

ORDER BYClause p. 74 INTO TEMP

Clause p. 74

+

INTOClausep. 71

HAVINGClausep. 74

UNION ALL

UNION

SELECTFROMClausep. 71

SelectClausep. 70

GROUP BYClause p. 73

ESQL

SPL

+

FOR READ ONLY

FOR UPDATE

OF

,columnname

SQL Statements 69

Page 70: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SELECT

SELECT Clause

Select List

DISTINCT

UNIQUE

SelectList

ALL

,

+

SelectClause

SelectList

SynonymNamep. 108

ViewNamep. 109

TableNamep. 108

Expressionp. 96

AS

.

displaylabel

.

.

*

70 SQL Quick Syntax Guide

Page 71: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SELECT

INTO Clause

FROM Clause

INTOClause ,

data variable

ESQL

indicator variableINDICATOR

INTO

data structure

: indicatorvariable

+

$ indicatorvariable

FROMTableNamep. 108

FROMClause

, AdditionalTablesp. 72

alias

AS

+ViewNamep. 109

SynonymNamep. 108

SQL Statements 71

Page 72: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SELECT

Additional Tables

WHERE Clause

+

AdditionalTables

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

AS

,

( )

alias

OUTER

OUTER

alias

AS

+

AdditionalTables

,

AS

alias

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

WHERE

AND

Conditionp. 89

Joinp. 73

WHEREClause

72 SQL Quick Syntax Guide

Page 73: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SELECT

Join Option

GROUP BY Clause

columnname

columnname

.

Join

RelationalOperator

p. 108

alias

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

alias

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

.

.

.

.

.

.

.

GROUP BY

,

selectnumber

TableNamep. 108

ViewNamep. 109

.

SynonymNamep. 108

.

+

GROUP BYClause

.alias

columnname

SQL Statements 73

Page 74: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SELECT

HAVING Clause

ORDER BY Clause

INTO TEMP Clause

HAVING Conditionp. 89

HAVINGClause

ORDER BY columnname

selectnumber

displaylabel

ASC

DESC

TableNamep. 108

ViewNamep. 109

.

.

SynonymNamep. 108

.

ORDER BYClause

.alias

ROWID

,

+

[ first, last ]

+

WITH NO LOG

INTO TEMP temp table name

INTO TEMPClause

74 SQL Quick Syntax Guide

Page 75: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET

SET

Table-Mode Format

SET+ Table-ModeFormatp. 75

List-ModeFormatp. 77

Transaction-Mode Format

p. 77

OL

FOR

,

INDEXES

CONSTRAINTS

TRIGGERS

table nameObject Modes forConstraints andUnique Indexes

p. 76

Object Modes forTriggers and

Duplicate Indexesp. 76

Table-Mode Format

SQL Statements 75

Page 76: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET

Object Modes for Constraints and Unique Indexes

Object Modes for Triggers and Duplicate Indexes

Object Modes for Constraintsand Unique Indexes

DISABLED

WITHOUTERROR

WITHERROR

FILTERING

ENABLED

DISABLED

ENABLED

Object Modes for Triggersand Duplicate Indexes

76 SQL Quick Syntax Guide

Page 77: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET

List-Mode Format

Transaction-Mode Format

CONSTRAINTS

,constraint

name

Object Modes forTriggers and

Duplicate Indexesp. 76

INDEXES

,

index nameObject Modes forConstraints andUnique Indexes

p. 76

Object Modes forConstraints andUnique Indexes

p. 76

,

List-Mode Format

TRIGGERS trigger nameObject Modes for

Triggers andDuplicate Indexes

p. 76

DEFERRED

IMMEDIATECONSTRAINTS

,constraint

name

Transaction-Mode Format

ALL

SQL Statements 77

Page 78: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET CONNECTION

SET CONNECTION

SET DATASKIP

SET DEBUG FILE TO

'connection name'SET CONNECTIONESQL

conn_nm variable

+

DEFAULT

DatabaseEnvironment

p. 25

CURRENT

DORMANT

+

+

+E/C

E/C

SET DATASKIP ON

OFF

DEFAULT

OL

,

dbspace

+

filename'+ SET DEBUG FILE TO '

WITH APPENDvariable name

characterexpression

78 SQL Quick Syntax Guide

Page 79: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET DESCRIPTOR

SET DESCRIPTOR

Item Descriptor Information

valueESQL

' descriptor '

itemnumber

SET DESCRIPTOR

descriptorvariable ,

itemnumbervariable

VALUE

COUNT =

ItemDescriptorInformation

countvariable

+

ItemDescriptorInformation

literal integer

=

TYPE

LENGTH

PRECISION

SCALE

NULLABLE

INDICATOR

ITYPE

IDATA

ILENGTH

NAME

=

Literal Numberp. 107

Literal INTERVALp. 106

Literal DATETIMEp. 105

Quoted Stringp. 107

data variable

integer-hostvariable

DATA

SQL Statements 79

Page 80: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET EXPLAIN

SET EXPLAIN

SET ISOLATION

SET LOCK MODE TO

SET LOG

SET EXPLAIN

OFF

+ ON

SET ISOLATION TO DIRTY READ

COMMITTED READ

CURSOR STABILITY

REPEATABLE READ

OL+

SET LOCK MODE TO WAIT

seconds

+

OL

NOT WAIT

SET LOG

BUFFERED

OL+

80 SQL Quick Syntax Guide

Page 81: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET OPTIMIZATION

SET OPTIMIZATION

SET PDQPRIORITY

SET ROLE

SET SESSION AUTHORIZATION

SET OPTIMIZATION

LOW

+ HIGH

SET PDQPRIORITY DEFAULT

OFF

HIGH

percent-of-resources

OL+

LOW

SET ROLE role name

NULL

NONE

+

OL

SET SESSION AUTHORIZATION TO 'user 'ESQL

OL

SQL Statements 81

Page 82: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

SET TRANSACTION

SET TRANSACTION

START DATABASE

1

,

SET TRANSACTION READ WRITE

READ ONLY

READ COMMITTEDISOLATION LEVEL

REPEATABLE READ

,

READ UNCOMMITTEDISOLATION LEVEL

READ ONLY1

1

1

SERIALIZABLE

OL

OL

START DATABASESE Database

Namep. 93

WITH LOG IN ' pathname '

WITH NO LOG

MODE ANSI

+

82 SQL Quick Syntax Guide

Page 83: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

START VIOLATIONS TABLE

START VIOLATIONS TABLE

STOP VIOLATIONS TABLE

UNLOAD

UNLOCK TABLE

START VIOLATIONS TABLE FOR tablename

numrowsMAX ROWSUSING violations , diagnostics

+

STOP VIOLATIONS TABLE FOR tablename+

DELIMITER 'delimiter'

UNLOAD TO 'filename'DB SELECT

Statementp. 69+

TableNamep. 108

SynonymNamep. 108

UNLOCK TABLE+

SQL Statements 83

Page 84: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

UPDATE

UPDATE

SET Clause

UPDATE SET

WHERE

CURRENT OFcursor

id

ESQL

TableNamep. 108

ViewNamep. 109

Conditionp. 89

SET Clause

SynonymNamep. 108

SPL

( )

columnname

columnname Expression

(Subset)p. 96

Expression(Subset)

p. 96

SELECTStatement(Subset)

p. 69

SETClause

( )

( )SELECT

Statement(Subset)

p. 69

,

( )

NULL

NULL

+

,

,

=

=

*

84 SQL Quick Syntax Guide

Page 85: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

UPDATE STATISTICS

UPDATE STATISTICS

FOR PROCEDURE

ProcedureNamep. 107

DROPDISTRIBUTIONS

MEDIUM

RESOLUTION percent

HIGH

LOW

RESOLUTION percent

UPDATE STATISTICS+

SynonymNamep. 108

TableNamep. 108

,columnname( )

FORTABLE

SynonymNamep. 108

TableNamep. 108

,

columnname

( )

FORTABLE

SynonymNamep. 108

TableNamep. 108

,columnname( )

FORTABLE

DISTRIBUTIONS ONLYconf

DISTRIBUTIONS ONLY

OL

OL

SQL Statements 85

Page 86: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

WHENEVER

WHENEVER

WHENEVER

+

ESQL SQLERROR

NOT FOUND

CONTINUE

CALL

PERFORM

functionname

E/CO

ERROR

GO TO

STOP

paragraphname

label

SQLWARNING

: label

+

GOTO

E/CO

+

86 SQL Quick Syntax Guide

Page 87: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ion

SQL Segments

Sect

Page 88: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A
Page 89: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Condition

Condition

ComparisonCondition

p. 90

Condition withSubquery

p. 91

NOT

OR

AND

SQL Segments 89

Page 90: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Condition

Comparison Conditions (Boolean Expressions)

RelationalOperator

p. 108

Expressionp. 96

Expressionp. 96

Expressionp. 96

Expressionp. 96

Expressionp. 96

DATETIMEField

Qualifierp. 95

IS

Expressionp. 96

NOT

NOT

columnname

NOT

,

BETWEEN AND

)(

MATCHES

IN

USER

LiteralNumberp. 107

LiteralDATETIME

p. 105

LiteralINTERVAL

p. 106

QuotedStringp. 107

SITENAME

NULL

CURRENT

DBSERVERNAME

NOT

OL

+

+

.

ComparisonCondition

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

alias

TODAY

.

TableNamep. 108

ViewNamep. 109

SynonymNamep. 108

alias

columnname

LIKEQuotedStringp. 107

ESCAPE 'char'

.

.

.

.

.

.

.

.

column name

90 SQL Quick Syntax Guide

Page 91: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Condition

Condition with a Subquery

INSubquery

p. 92

Conditionwith

Subquery

ALL/ANY/SOMESubquery

p. 92

EXISTSSubquery

p. 92

SQL Segments 91

Page 92: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Condition

IN Subquery

EXISTS Subquery

ALL/ANY/SOME Subquery

Expressionp. 96

Conditionwith

Subqueryp. 91

INSubquery

)(

NOT

IN

Conditionwith

Subqueryp. 91

EXISTSSubquery

EXISTS

NOT

)(

Expressionp. 96

RelationalOperator

p. 108ALL

ANY

SOME

Conditionwith

Subqueryp. 91

ALL/ANY/SOMESubquery

( )

92 SQL Quick Syntax Guide

Page 93: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Constraint Name

Constraint Name

Database Name

Identifierp. 104

@dbservername

:

owner.

'owner'.

OL

database

@ dbservername

OL

' //dbservername/dbname '

variable name

' //dbservername/pathname/dbname 'SE

ESQL

OL

dbname

' /pathname/dbname@dbservername '

SQL Segments 93

Page 94: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Data Type

Data Type

INTERVAL Field Qualifier p. 105

DATETIME Field Qualifier p. 95

16

SERIAL

(1)start

, 2

, scale

NCHAR

SMALLINT

CHAR

DATE

DATETIME

INTERVAL

SMALLFLOAT

DECIMAL

size

floatprecision

, scale

precision

CHARACTER

DEC

NUMERIC

FLOAT

INTEGER

INT

MONEY

REAL

( )

)(

( )

(1)

( )

16

precision )(

INFORMIX-OnLine Dynamic Server-Specific Data Types p. 95

GLS

+

+

+

+

+

+

+

OL

DOUBLE PRECISION

94 SQL Quick Syntax Guide

Page 95: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

DATETIME Field Qualifier

INFORMIX-OnLine Dynamic Server-Specific Data Types

DATETIME Field Qualifier

max

IN TABLE

NVARCHAR

TEXT

INFORMIX-OnLine DynamicServer-Specific

Data Types

family name

)VARCHAR

reserve

blobspace

BYTE

+

+

+

+

GLS,0

OP

,(

max(CHARACTER VARYING )

, reserve

(3)

YEAR

TO MONTH

TO DAY

TO HOUR

TO MINUTE

TO SECOND

TO FRACTION

FRACTION

SECOND

MINUTE

HOUR

DAY

MONTH TO YEAR

(digit)

SQL Segments 95

Page 96: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

Expression

Column Expressions

p. 97

ConstantExpressions

p. 97

FunctionExpressions

p. 98

AggregateExpressions

p. 103

Expression

-

+

*/

| |

-+

variable name

)(

Procedure CallExpressions

p. 103

procedure variable nameSPL

96 SQL Quick Syntax Guide

Page 97: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

Column Expressions

Constant Expressions

[first, last ]

TableNamep. 108

ViewNamep. 109

+

SynonymNamep. 108

.

.

.

alias ROWID

columnname

.+

QuotedStringp. 107

LiteralNumberp. 107+

OL+ SITENAME

DBSERVERNAME

TODAY

CURRENT

LiteralDATETIME

p. 105

LiteralINTERVAL

p. 106

USER

UNITS datetimeunit

n

DATETIMEField

Qualifierp. 95

SQL Segments 97

Page 98: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

Function Expressions

+ AlgebraicFunctions

p. 99

DBINFOFunctionp. 100

TRIMFunctionp. 102

TimeFunctions

p. 101

LENGTHFunctionp. 101

HEXFunctionp. 100

Exponential andLogarithmicFunctions

p. 100

TrigonometricFunctions

p. 102

98 SQL Quick Syntax Guide

Page 99: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

Algebraic Functions

Expressionp. 96

ABS

base, exponent

sqrt_radicand

num_expression

AlgebraicFunctions

ROUND

, rounding factor

dividend, divisor

POW

MOD

ROOT radicand

Expressionp. 96

TRUNC

, truncating factor

SQRT

, index

)(

)

)

(

(

( )

)(

( )

)(

, 2

, 0

, 0

SQL Segments 99

Page 100: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

DBINFO Function

Exponential and Logarithmic Functions

HEX Function

DBINFO Function

DBINFO 'DBSPACE' ,

'sqlca.sqlerrd1'

'sqlca.sqlerrd2'

OL tblspace num( )

expression

'sessionid'

OL

+

LOG10

float expressionLOGN

Exponential and Logarithmic Functions

EXP float expression

float expression

)

)

)

(

(

(

HEX Function

HEX ( integerexpression )

100 SQL Quick Syntax Guide

Page 101: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

LENGTH Function

Time Functions

LENGTHFunctions

variablename

columnname

SPLESQL

+QuotedStringp. 107

LENGTH )(

CHAR_LENGTH

CHARACTER_LENGTH

OCTET_LENGTH

TableNamep. 108

.

+

Time Functions

DATE

DAYdate/

datetimeexpression

MONTH

WEEKDAY

YEAR

EXTEND

MDY

non-dateexpression

date/datetime

expressionfirst TO last,

,,monthinteger

expression

dayinteger

expression

yearinteger

expression

)(

)(

( )

)(

SQL Segments 101

Page 102: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

Trigonometric Functions

TRIM Function

TAN

SIN

TrigonometricFunctions

COS

ATAN2 y, x

numericexpression

radianexpression

)(

)

)

(

(

ACOS

ASIN

ATAN

TRIM Function

TRIM )(source

charactervalue

expression

LEADING

TRAILING

BOTH

FROM

trimcharacter

valueexpression

FROM

trimcharacter

valueexpression

102 SQL Quick Syntax Guide

Page 103: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Expression

Aggregate Expressions

Procedure Call Expressions

TableNamep. 108

( )columnname

AVG

MAX

MIN

SUM

COUNT

DISTINCT

AVG

MAX

MIN

SUM

(

ALL

Expression(Subset)

p. 96

)

.

COUNT (*)

SynonymNamep. 108

.

ViewNamep. 109

.

(

UNIQUE

DISTINCT

UNIQUE

STDEV

VARIANCE

RANGE

COUNT (

ALL

+

ProcedureNamep. 107

calledvariable

=

Expressionp. 96

,

( )

SQL Segments 103

Page 104: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Identifier

Identifier

Delimited Identifiers

Index Name

letter

underscore

letter

digit

underscore

DelimitedIdentifier

DelimitedIdentifier

letter

digit

underscore

nonalphanumeric character

double quotedouble quote

@ dbservername

database

OL

Identifier

: 'owner'.

owner.

104 SQL Quick Syntax Guide

Page 105: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

INTERVAL Field Qualifier

INTERVAL Field Qualifier

Literal DATETIME

YEAR

MONTH

DAY

MINUTE

SECOND

FRACTION

TO YEAR

TO MONTH

TO DAY

TO HOUR

TO MINUTE

TO SECOND

TO FRACTION

HOUR

(precision)

(precision)

(precision)

(precision)

(y-precision)

(precision)

(f-precision)

(2)

(4)

(2)

(2)

(2)

(2)

(3)

DATETIMEDATETIME

Field Qualifierp. 95

NumericDate

p. 106)(

SQL Segments 105

Page 106: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Literal Interval

Numeric Date

Literal Interval

yyyy

mo

dd

space

hh

mi

ss

f

Numeric Date

-

:

:

.

-

INTERVALField Qualifier

p. 105Numeric Date )(INTERVAL

106 SQL Quick Syntax Guide

Page 107: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Literal Number

Literal Number

Procedure Name

Quoted String

digit

digitEdigit

digit

+-

.

.+-

@ dbservername

owner.

database

+

Identifierp. 104

:'owner'

OL

character

character

' '

"' '

" "

"

+

SQL Segments 107

Page 108: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

Relational Operator

Relational Operator

Synonym Name

Table Name

+

<

<=>

<>

=>=

!=

@ dbservername

owner.

database

OL

:

+'owner'.

Identifierp. 104

@ dbservername

owner.

database :

+

Identifierp. 104

OL

'owner'.

108 SQL Quick Syntax Guide

Page 109: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

View Name

View Name

@ dbservername

owner.

database

OL+

Identifierp. 104

'owner'.:

SQL Segments 109

Page 110: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A
Page 111: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

III

Stored Procedure Language

Statements

Sect

ion

Page 112: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A
Page 113: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

CALL

CALL

Argument

CONTINUE

CALLProcedure

Namep. 107

;( ),

Argument RETURNINGprocedurevariable

,

=

Expressionp. 96

SELECTStatement(Subset)

p. 69

parametername

Argument

CONTINUE

FOREACH

WHILE

FOR ;

SPL Statements 113

Page 114: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

DEFINE

DEFINE

DEFINE GLOBALSQL Data Type

(Subset)p. 94

DefaultValuep. 115DEFAULT

,

variablename

DEFAULTNULLREFERENCES

REFERENCES

SQL Data Type(Subset)

p. 94

BYTE

TEXT

PROCEDURE

Table Namep. 108 column name.LIKE

BYTE

TEXT

;

,variablename

OL

OL

SynonymNamep. 108

View Namep. 109

114 SQL Quick Syntax Guide

Page 115: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

EXIT

Default Value Clause

EXIT

Literal Numberp. 107

Quoted Stringp. 107

Literal Intervalp. 106

Literal Datetimep. 105

CURRENTp. 90

DefaultValue

TODAY

USER

DATETIMEField

Qualifierp. 95

SITENAME

NULL

DBSERVERNAME

OL

;EXIT

FOREACH

WHILE

FOR

SPL Statements 115

Page 116: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

FOR

FOR

)

,

FORvariablename

leftexpression

TO rightexpressionIN (

StatementBlockp. 31

ENDFOR

,

expression

;

incrementexpression

leftexpression

TO rightexpression

=

incrementexpression

STEP

STEP

116 SQL Quick Syntax Guide

Page 117: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

FOREACH

FOREACH

IF

,

FOREACHSELECT...INTO

Statementp. 71

StatementBlockp. 31

ENDFOREACH

;cursorname FOR

WITH HOLD

WITH HOLD

EXECUTEPROCEDURE

ProcedureNamep. 107

( )

variablenameINTO

Expression(Subset)

p. 96

,

parametername =

;

IF Conditionp. 89 THEN

ELIF Conditionp. 89 THEN

IF StatementList

p. 118

IF StatementList

p. 118

END IF

IF StatementList

p. 118

ELSE

SPL Statements 117

Page 118: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

IF

IF Statement List

StatementBlockp. 31

BEGIN END

CONTINUEStatement

p. 113

IFStatement

p. 117

EXITStatement

p. 115

FORStatement

p. 116

FOREACHStatement

p. 117

LETStatement

p. 119

RAISE EXCEPTIONStatement

p. 119

RETURNStatement

p. 120

TRACEStatement

p. 120

WHILEStatement

p. 120

SYSTEMStatement

p. 120

CALLStatement

p. 113

SQL Statement

IF StatementList

118 SQL Quick Syntax Guide

Page 119: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

LET

LET

ON EXCEPTION

RAISE EXCEPTION

,

,variablenameLET =

ProcedureNamep. 107

,

( Expressionp. 96 ;)

Expressionp. 96

calledvariable =

,

,ON EXCEPTION

StatementBlockp. 31

END EXCEPTION

errornumberIN )(

SETSQLerror

variable

;

errordata

variable

WITH RESUME

,

ISAMerror

variable,

RAISE EXCEPTION ;SQLerror

ISAMerror

,error textvariable,

SPL Statements 119

Page 120: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

RETURN

RETURN

SYSTEM

TRACE

WHILE

,RETURN

Expressionp. 96

WITH RESUME

;

expressionSYSTEM ;

character variable

TRACE ON

OFF

PROCEDURE

Expressionp. 96

;

WHILE Conditionp. 89

StatementBlockp. 31

END WHILE

;

120 SQL Quick Syntax Guide

Page 121: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

IV

INFORMIX-OnLine OpticalStatements

Sect

ion

Page 122: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A
Page 123: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

LTER OPTICAL CLUSTER

A

ALTER OPTICAL CLUSTER

CLUSTERSIZE Clause

CREATE OPTICAL CLUSTER

FOR Clause

owner.

ALTEROPTICALCLUSTER

CLUSTERSIZEClause

clustername

+

DB

ESQL

CLUSTERSIZEClause

CLUSTERSIZE

500

clustersize

owner.

FORClause

CREATEOPTICALCLUSTER

CLUSTERSIZEClause

ONClausep. 124

clustername

+

DB

ESQL

FORClause

(FOR )blob column name

,

Table Namep. 108

INFORMIX-OnLine/Optical Statements 123

Page 124: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

ON Clause

ON Clause

DROP OPTICAL CLUSTER

RELEASE

ONClause

(ON )cluster-keycolumn name

,

owner.

DROPOPTICALCLUSTER

clustername

+

DB

ESQL

RELEASE volume number

procedurevariablename

family-namevariable

+

DB

ESQLESQL

SPL

'family name'

124 SQL Quick Syntax Guide

Page 125: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A

RESERVE

RESERVE

SET MOUNTING TIMEOUT

Function Expressions

RESERVE volume number

procedurevariablename

family-namevariable

+

DB

ESQLESQL

SPL

'family name'

SET MOUNTING TIMEOUT TO WAIT

NOT WAIT

seconds

+

DB

ESQL

FunctionExpressions

p. 98

DESCR (blob column name)

FAMILY (blob column name)

VOLUME (blob column name)

+

DB

ESQL

INFORMIX-OnLine/Optical Statements 125

Page 126: SQL Quick Syntax Guide - ΤΜΗΜΑ ΜΗΧΑΝΙΚΩΝ Η ...pitoura/informix-doc/SQL_Quick_Syntax_Guide.pdf · SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A