Download SQL Quick Syntax Guide
Transcript
SQL Quick Syntax Guide Version 7.2 April 1996 Part No. 000-7880A Published by INFORMIX® Press Informix Software, Inc. 4100 Bohannon Drive Menlo Park, CA 94025 The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in the United 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 the United 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 you have 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 any means—graphic, electronic, or mechanical, including photocopying, recording, taping, or information storage and retrieval systems—without permission of the publisher. RESTRICTED RIGHTS LEGEND Software and accompanying materials acquired with United States Federal Government funds or intended for use within or for any United States federal agency are provided with “Restricted Rights” as defined in DFARS 252.227-7013(c)(1)(ii) or FAR 52.227-19. ii SQL Quick Syntax Guide Introduction The following statements and segments for Version 7.2 are presented in this guide: ■ SQL statements ■ SQL segments ■ Stored Procedure Language (SPL) statements ■ INFORMIX-OnLine/Optical statements This guide presents the syntax diagrams for statements and segments but does not provide any explanatory information. For complete descriptions of SQL and SPL statements and segments, see the Informix Guide to SQL: Syntax. For complete descriptions of INFORMIX-OnLine/Optical statements, see the INFORMIX-OnLine/Optical User Manual. These manuals provide the following 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 the SQL Quick Syntax Guide ■ A syntax table that provides the purpose, restrictions, and syntax for all italicized elements in a syntax diagram ■ Rules and examples of usage ■ References to related statements Introduction 3 Syntax Conventions Syntax Conventions This section describes conventions for syntax diagrams. Each diagram displays the sequences of required and optional keywords, terms, and symbols that are valid in a given statement, command line, or other specification, as in the following diagram of the SET EXPLAIN statement. + SET EXPLAIN ON OFF Each syntax diagram begins at the upper left corner and ends at the upper right corner with a vertical terminator. Between these points, any path that does not stop or reverse direction describes a possible form of the statement. (For a few diagrams, notes in the text identify path segments that are mutually exclusive.) Syntax elements in a path represent terms, keywords, symbols, and segments that can appear in your statement. Except for separators in loops, which the path approaches counterclockwise from the right, the path always approaches elements from the left and continues to the right. Unless otherwise noted, at least one blank character separates syntax elements. 4 INFORMIX Elements That Can Appear on the Path Elements That Can Appear on the Path You might encounter one or more of the following elements on a path. Element Description KEYWORD A word in UPPERCASE characters is a keyword. You must spell the word exactly as shown; however, you can use either uppercase or lowercase characters. (.,;@+*-/) Punctuation and other non-alphanumeric characters are literal symbols that you must enter exactly as shown. ' ' Single quotes are literal symbols that you must enter as shown. variable A word in italics represents a value that you must supply. A table immediately following the diagram explains the value. ADD Clause p. 1-14 ADD Clause CREATE OPTICAL CLUSTER Statement see INFORMIXOnLine/Optical User Manual OL A term shown in a rectangle represents a subdiagram on the same page (if no page number is supplied) or a specified page, as if the subdiagram were spliced into the diagram at this point. The same subdiagram can be represented by rectangles of different shapes, as in these symbols for the ADD Clause subdiagram. This reference represents a statement described in the INFORMIX-OnLine/Optical User Manual. Imagine that the statement is spliced into the diagram at this point. An icon is a warning that this path is valid only for some products, or only under certain conditions. Characters on the icons indicate what products or conditions support the path. These icons might appear in a syntax diagram: OL This path is valid only for INFORMIX-OnLine Dynamic Server. SE This path is valid only for INFORMIX-SE. (1 of 3) Introduction 5 Elements That Can Appear on the Path Element Description D/B This path is valid only for DB-Access. ESQL This path is valid only for SQL statements in INFORMIX-ESQL/C and INFORMIX-ESQL/COBOL. E/C ALL This path is valid only for INFORMIX-ESQL/C. E/CO This path is valid only for INFORMIX-ESQL/COBOL. SPL This path is valid only for only if you are using Informix Stored Procedure Language (SPL). OP This path is valid only for INFORMIX-OnLine/Optical. + This path is an Informix extension to ANSI SQL-92 entry-level standard SQL. If you initiate Informix extension checking and include this syntax branch, you receive a warning. If you have set the DBANSIWARN environment variable at compile time, or have used the -ansi compile flag, you receive warnings at compile time. If you have DBANSIWARN set at runtime, or if you compiled with the -ansi flag, warning flags are set in the sqlwarn structure. GLS This path is valid only only if your database or application is GLS compliant. A shaded option is the default. If you do not specify any of the available options, this option is in effect by default. Syntax that is enclosed between a pair of arrows is a subdiagram. (2 of 3) 6 INFORMIX Elements That Can Appear on the Path Element Description The vertical line is a terminator. This symbol only appears at the right, indicating that the syntax diagram is complete. IS NULL NOT NOT FOUND ERROR A branch below the main path indicates an optional path. (Any term on the main path is required, unless a branch can circumvent it.) A set of multiple branches indicates that a choice among more than two different paths is available. WARNING , variable A loop indicates a path that you can repeat. Punctuation along the top of the loop indicates the separator symbol for list items, as in this example. If no symbol appears, a blank space is the separator, statement , 3 size A gate ( 3 ) on a path indicates that you can only use that path the indicated number of times, even if it is part of a larger loop. Here you can specify size no more than three times within this statement segment. (3 of 3) Introduction 7 How to Read a Syntax Diagram How to Read a Syntax Diagram Figure 1 shows a syntax diagram that uses most of the path elements that are listed in the previous table. Figure 1 Elements of a Syntax Diagram Main diagram CREATE DATABASE database name OL IN dbspace Subdiagram OL OL Log Clause SE SE Log Clause OL Log Clause WITH LOG BUFFERED LOG MODE ANSI SE Log Clause WITH LOG IN 'pathname' MODE ANSI To use this diagram to construct a statement, start at the top left with the keywords CREATE DATABASE. Then follow the diagram to the right, proceeding through the options that you want. 8 INFORMIX 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 can take one or more of the optional paths. 4. If desired, you can designate a dbspace by typing the word IN and a dbspace name. 5. If desired, you can specify logging. Here, you are constrained by the database server with which you are working: 6. ❑ If you are using INFORMIX-OnLine Dynamic Server, go to the subdiagram named OL Log Clause. Follow the subdiagram by typing the keyword WITH, then choosing and typing either LOG, BUFFERED LOG, or LOG MODE ANSI. Then follow the arrow back to the main diagram. ❑ If you are using INFORMIX-SE, go to the subdiagram named SE Log Clause. Follow the subdiagram by typing the keywords WITH LOG IN, typing a quote, supplying a pathname, and closing the quotes. You can then choose the MODE ANSI option below the line or continue to follow the line across. Once you are back at the main diagram, you come to the terminator. Your CREATE DATABASE statement is complete. Sample-Code Conventions Examples 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 by semicolons. To use this SQL code for a specific product, you must apply the syntax rules for that product. For example, if you are using the Querylanguage option of DB-Access, you must delimit multiple statements with semicolons. If you are using an SQL API, you must use EXEC SQL and a semicolon (or other appropriate delimiters) at the start and end of each statement, respectively. Introduction 9 Section SQL Statements ALLOCATE DESCRIPTOR ALLOCATE DESCRIPTOR + ESQL ALLOCATE DESCRIPTOR ' descriptor ' descriptor variable WITH MAX occurrences occurrences variable ALTER FRAGMENT OL + ALTER FRAGMENT ON TABLE INDEX surviving table surviving index ATTACH Clause p. 14 DETACH Clause p. 14 INIT Clause p. 14 ADD Clause p. 16 DROP Clause p. 16 MODIFY Clause p. 16 SQL Statements 13 ALTER FRAGMENT ATTACH Clause ATTACH Clause , ATTACH consumed table , AS frag-expression BEFORE surviving table AFTER AS frag-expression DETACH Clause dbspace-name new table INIT Clause INIT Clause INIT WITH ROWIDS FRAGMENT BY Clause for Tables FRAGMENT BY Clause for Indexes IN dbspace 14 SQL Quick Syntax Guide 1 AS REMAINDER DETACH Clause DETACH dbspace ALTER FRAGMENT FRAGMENT BY Clause for Tables FRAGMENT BY Clause for Tables , FRAGMENT BY ROUND ROBIN IN dbspace , dbspace , EXPRESSION frag-expression IN dbspace , frag-expression IN dbspace REMAINDER IN remainder dbspace , FRAGMENT BY Clause for Indexes FRAGMENT BY Clause for Indexes FRAGMENT BY , EXPRESSION frag-expression IN dbspace , frag-expression IN dbspace , REMAINDER IN remainder dbspace SQL Statements 15 ALTER FRAGMENT ADD Clause ADD Clause new dbspace ADD frag-expression IN new dbspace BEFORE existing dbspace AFTER REMAINDER IN new dbspace DROP Clause DROP Clause dbspace-name DROP MODIFY Clause MODIFY Clause MODIFY , mod-dbspace TO frag-expression IN new-dbspace 1 16 SQL Quick Syntax Guide REMAINDER IN new-dbspace ALTER INDEX ALTER INDEX + Index Name p. 104 ALTER INDEX TO CLUSTER NOT ALTER TABLE , + ALTER TABLE Table Name p. 108 ADD Clause p. 18 Synonym Name p. 108 DROP Clause p. 21 MODIFY Clause p. 21 ADD CONSTRAINT Clause p. 22 DROP CONSTRAINT Clause p. 23 OL 1 MODIFY NEXT SIZE Clause p. 23 1 LOCK MODE Clause p. 23 1 ADD ROWIDS DROP ROWIDS SQL Statements 17 ALTER TABLE ADD Clause ADD Clause Add Column Clause ADD , ( Add Column Clause ) Add Column Clause Add Column Clause new column name 18 Data Type p. 94 SQL Quick Syntax Guide DEFAULT Clause p. 19 New Column Constraint Definition p. 19 BEFORE column name ALTER TABLE DEFAULT Clause DEFAULT Clause DEFAULT literal NULL CURRENT p. 97 DATETIME Field Qualifier p. 95 USER p. 97 TODAY p. 97 SITENAME p. 97 DBSERVERNAME p. 97 New Column Constraint Definition New Column Constraint Definition UNIQUE + NOT NULL + DISTINCT + ConstraintMode Definitions p. 20 PRIMARY KEY ConstraintMode Definitions p. 20 REFERENCES Clause p. 20 CHECK Clause p. 20 SQL Statements 19 ALTER TABLE Constraint-Mode Definitions Constraint-Mode Definitions CONSTRAINT Constraint Name p. 93 DISABLED ENABLED FILTERING WITHOUT ERROR WITH ERROR REFERENCES Clause REFERENCES Clause table name REFERENCES OL + , ( column name ) ON DELETE CASCADE CHECK Clause CHECK Clause CHECK 20 SQL Quick Syntax Guide ( Condition p. 89 ) ALTER TABLE DROP Clause DROP Clause DROP column name , ( column name ) MODIFY Clause MODIFY Clause Modify Column Clause MODIFY , ( Modify Column Clause ) Modify Column Clause Modify Column Clause column name Data Type p. 94 DEFAULT Clause p. 19 , New Column Constraint Definition p. 19 SQL Statements 21 ALTER TABLE ADD CONSTRAINT Clause ADD CONSTRAINT Clause Table-Level Constraint Definition ADD CONSTRAINT , ( ) Table-Level Constraint Definition Table-Level Constraint Definition Table-Level Constraint Definition , ( UNIQUE + column name ) Constraint-Mode Definitions p. 20 DISTINCT PRIMARY KEY FOREIGN KEY , ( column name CHECK Clause p. 20 22 SQL Quick Syntax Guide + ) REFERENCES Clause p. 20 ALTER TABLE DROP CONSTRAINT Clause DROP CONSTRAINT Clause Constraint Name p. 93 DROP CONSTRAINT , ( Constraint Name p. 93 ) MODIFY NEXT SIZE Clause MODIFY NEXT SIZE Clause MODIFY NEXT SIZE kbytes LOCK MODE Clause LOCK MODE Clause LOCK MODE ( PAGE ) ROW SQL Statements 23 BEGIN WORK BEGIN WORK + BEGIN WORK CHECK TABLE SE DB + CHECK TABLE CLOSE ESQL cursor id CLOSE CLOSE DATABASE + CLOSE DATABASE COMMIT WORK COMMIT WORK 24 SQL Quick Syntax Guide Table Name p. 108 CONNECT CONNECT + Database Environment CONNECT TO ESQL ESQL AS ' connection name ' AS conn_nm variable USER Clause p. 26 DEFAULT ESQL WITH CONCURRENT TRANSACTION Database Environment Database Environment 'dbname' '@dbservername' 'dbname@dbservername' ESQL db_env variable SE 'pathname/dbname' 'pathname/dbname@dbservername' SQL Statements 25 CREATE AUDIT USER Clause USER Clause USER ' user identifier ' auth variable USING user_id variable CREATE AUDIT SE + Table Name p. 108 CREATE AUDIT FOR IN ' pathname ' Synonym Name p. 108 CREATE DATABASE + CREATE DATABASE Database Name p. 93 OL IN dbspace SE LOG Clause SE Log Clause WITH LOG IN ' pathname ' MODE ANSI 26 SQL Quick Syntax Guide SE SE Log Clause OL OL Log Clause CREATE INDEX OL LOG Clause OL Log Clause WITH LOG BUFFERED LOG MODE ANSI CREATE INDEX + CREATE INDEX UNIQUE CLUSTER Index Definition p. 28 DISTINCT OL OL FILLFACTOR percent IN dbspace FRAGMENT BY EXPRESSION Clause p. 28 Object Modes for Unique Indexes p. 28 Object Modes for Duplicate Indexes p. 29 SQL Statements 27 CREATE INDEX Index Definition Index Definition , Index Name p. 104 ON ( table name ) column name ASC Synonym Name p. 108 DESC FRAGMENT BY EXPRESSION Clause FRAGMENT BY EXPRESSION Clause , FRAGMENT BY EXPRESSION frag-expression IN dbspace , frag-expression IN dbspace , Object Modes for Unique Indexes Object Modes for Unique Indexes DISABLED ENABLED FILTERING WITHOUT ERROR WITH ERROR 28 SQL Quick Syntax Guide REMAINDER IN remainder dbspace CREATE PROCEDURE Object Modes for Duplicate Indexes Object Modes for Duplicate Indexes DISABLED ENABLED CREATE PROCEDURE + CREATE PROCEDURE Procedure Name p. 107 ( ) , DBA RETURNING Clause p. 30 Parameter Statement Block p. 31 END PROCEDURE ; , DOCUMENT Quoted String p. 107 WITH LISTING IN ' pathname ' Parameter Option Parameter SQL Data Type (Subset) p. 94 variable name DEFAULT LIKE table name . REFERENCES column name default value BYTE TEXT DEFAULT NULL SQL Statements 29 CREATE PROCEDURE RETURNING Clause RETURNING Clause RETURNING , ; SQL Data Type (Subset) p. 94 REFERENCES BYTE TEXT 30 SQL Quick Syntax Guide CREATE PROCEDURE Statement Block Statement Block DEFINE Statement p. 114 CALL Statement p. 113 ON EXCEPTION Statement p. 119 CONTINUE Statement p. 113 EXECUTE PROCEDURE Statement p. 50 EXIT Statement p. 115 FOR Statement p. 116 FOREACH Statement p. 117 IF Statement p. 117 LET Statement p. 119 RAISE EXCEPTION Statement p. 119 RETURN Statement p. 120 SYSTEM Statement p. 120 TRACE Statement p. 120 WHILE Statement p. 120 Subset of SQL Statement see SQLS BEGIN Statement Block ; END SQL Statements 31 CREATE PROCEDURE FROM CREATE PROCEDURE FROM ESQL + ' filename ' CREATE PROCEDURE FROM variable name CREATE ROLE + CREATE ROLE role name OL CREATE SCHEMA DB CREATE SCHEMA AUTHORIZATION CREATE TABLE Statement p. 33 user name + CREATE INDEX Statement p. 27 CREATE VIEW Statement p. 44 + CREATE SYNONYM Statement p. 33 GRANT Statement p. 55 CREATE TRIGGER Statement p. 40 OP 32 SQL Quick Syntax Guide CREATE OPTICAL CLUSTER Statement p. 123 CREATE SYNONYM CREATE SYNONYM + CREATE SYNONYM PUBLIC Synonym Name p. 108 Table Name p. 108 FOR View Name p. 109 PRIVATE CREATE TABLE , CREATE TABLE Table Name p. 108 ( Column Definition , , + TEMP TABLE Clause p. 37 Table-Level Constraint Definition p. 36 ) OL WITH ROWIDS Storage Option p. 39 Column-Definition Option Column Definition column name Data Type p. 94 DEFAULT Clause p. 34 Column-Level Constraint Definition p. 35 SQL Statements 33 CREATE TABLE DEFAULT Clause DEFAULT Clause literal DEFAULT NULL + CURRENT p. 97 + DATETIME Field Qualifier p. 95 USER p. 97 TODAY p. 97 OL + + SITENAME p. 97 DBSERVERNAME p. 97 34 SQL Quick Syntax Guide CREATE TABLE Column-Level Constraint-Definition Option Column-Level Constraint Definition UNIQUE + NOT NULL + DISTINCT + ConstraintMode Definitions PRIMARY KEY ConstraintMode Definitions REFERENCES Clause p. 36 CHECK Clause p. 36 Constraint-Mode Definitions Constraint-Mode Definitions DISABLED CONSTRAINT Constraint Name p. 93 ENABLED FILTERING WITHOUT ERROR WITH ERROR SQL Statements 35 CREATE TABLE REFERENCES Clause REFERENCES Clause table name REFERENCES OL + , ( column name ) ON DELETE CASCADE CHECK Clause CHECK Clause ( CHECK Condition p. 89 ) Table-Level Constraint-Definition Option Table-Level Constraint Definition , ( UNIQUE + column name ) Constraint-Mode Definitions p. 35 DISTINCT PRIMARY KEY FOREIGN KEY , ( column name CHECK Clause 36 SQL Quick Syntax Guide + ) REFERENCES Clause CREATE TABLE TEMP TABLE Clause TEMP TABLE Clause , TEMP TABLE temp table name ( Temporary Table Column Definition ) , , WITH NO LOG Temp Table Table-Level Constraint p. 38 Temporary Table Column Definition Temporary Table Column Definition column name Data Type p. 94 DEFAULT Clause p. 34 NOT NULL Temp Table Column Constraint Definition p. 38 SQL Statements 37 CREATE TABLE Temp Table Column Constraint Definition Temp Table Column Constraint Definition UNIQUE + DISTINCT PRIMARY KEY CHECK Clause p. 36 Temp Table Table-Level Constraint Temp Table Table-Level Constraint UNIQUE , ( + DISTINCT PRIMARY KEY CHECK Clause p. 36 38 SQL Quick Syntax Guide column name ) CREATE TABLE Storage Option Storage Option OL Extent Option IN dbspace LOCK MODE Clause p. 40 FRAGMENT BY Clause IN 'pathname' SE FRAGMENT BY Clause FRAGMENT BY Clause , FRAGMENT BY ROUND ROBIN IN , dbspace dbspace , EXPRESSION frag-expression IN dbspace , frag-expression IN dbspace , REMAINDER IN remainder dbspace Extent Option Extent Option EXTENT SIZE first kbytes NEXT SIZE next kbytes SQL Statements 39 CREATE TRIGGER LOCK MODE Clause LOCK MODE Clause LOCK MODE PAGE ROW CREATE TRIGGER DB ESQL + CREATE TRIGGER Trigger Name p. 41 Trigger Object Modes p. 43 INSERT ON Table Name p. 108 Action Clause p. 41 Insert REFERENCING Clause p. 42 DELETE ON Table Name p. 108 Action Clause p. 41 Delete REFERENCING Clause p. 42 UPDATE Clause p. 41 ON Table Name p. 108 SQL Quick Syntax Guide Action Clause Referencing p. 43 Action Clause p. 41 Update REFERENCING Clause p. 42 40 Action Clause Referencing p. 43 Action Clause Referencing p. 43 CREATE TRIGGER Trigger Name Trigger Name Identifier p. 104 owner. UPDATE Clause UPDATE Clause UPDATE , OF column name Action Clause Action Clause BEFORE Triggered Action List p. 43 FOR EACH ROW FOR EACH ROW Triggered Action List p. 43 Triggered Action List p. 43 AFTER AFTER AFTER Triggered Action List p. 43 Triggered Action List p. 43 Triggered Action List p. 43 SQL Statements 41 CREATE TRIGGER Insert REFERENCING Clause Insert REFERENCING Clause REFERENCING correlation name NEW AS Delete REFERENCING Clause Delete REFERENCING Clause REFERENCING correlation name OLD AS Update REFERENCING Clause Update REFERENCING Clause REFERENCING 1 correlation name OLD AS 1 correlation name NEW AS 42 SQL Quick Syntax Guide CREATE TRIGGER Action Clause Referencing Action Clause Referencing BEFORE Triggered Action List Triggered Action List FOR EACH ROW AFTER Triggered Action List Triggered Action List Triggered Action List , , ( WHEN ( Condition p. 89 ) INSERT Statement p. 59 ) DELETE Statement p. 45 UPDATE Statement p. 84 EXECUTE PROCEDURE p. 50 Trigger Object Modes Trigger Object Modes DISABLED ENABLED SQL Statements 43 CREATE VIEW CREATE VIEW CREATE VIEW View Name p. 109 AS , ( column name SELECT Statement (Subset) p. 69 ) DATABASE + DATABASE Database Name p. 93 EXCLUSIVE DEALLOCATE DESCRIPTOR + ESQL DEALLOCATE DESCRIPTOR 'descriptor ' descriptor variable 44 SQL Quick Syntax Guide WITH CHECK OPTION DECLARE DECLARE ESQL cursor id DECLARE INSERT Statement (Subset) p. 59 + FOR CURSOR + + cursor variable WITH HOLD + + SELECT Statement (Subset) p. 69 FOR READ ONLY FOR UPDATE , column name OF SCROLL CURSOR FOR WITH HOLD SELECT Statement p. 69 statement id + statement id variable EXECUTE PROCEDURE Statement p. 50 DELETE DELETE FROM Table Name p. 108 View Name p. 109 Synonym Name p. 108 WHERE Condition p. 89 ESQL CURRENT OF cursor name SQL Statements 45 DESCRIBE DESCRIBE ESQL + DESCRIBE USING SQL DESCRIPTOR statement id statement id variable E/C descriptor variable INTO DISCONNECT + DISCONNECT CURRENT ESQL DEFAULT ALL ' connection name ' conn_nm variable DROP AUDIT SE + Table Name p. 108 DROP AUDIT FOR Synonym Name p. 108 DROP DATABASE + 46 SQL Quick Syntax Guide DROP DATABASE ' descriptor ' Database Name p. 93 sqlda pointer DROP INDEX DROP INDEX + Index Name p. 104 DROP INDEX DROP PROCEDURE + Procedure Name p. 107 DROP PROCEDURE DROP ROLE + DROP ROLE OL role name DROP SYNONYM + Synonym Name p. 108 DROP SYNONYM DROP TABLE + DROP TABLE Table Name p. 108 Synonym Name p. 108 CASCADE RESTRICT SQL Statements 47 DROP TRIGGER DROP TRIGGER DB ESQL + Trigger Name p. 41 DROP TRIGGER DROP VIEW + DROP VIEW View Name p. 109 CASCADE Synonym Name p. 108 RESTRICT EXECUTE ESQL EXECUTE statement id statement id variable 48 SQL Quick Syntax Guide INTO Clause p. 49 USING Clause p. 49 EXECUTE USING Clause USING Clause , storage variable name USING E/C : + INDICATOR storage indicator variable storage indicator variable 'storage descriptor ' SQL DESCRIPTOR E/C storage descriptor variable storage sqlda pointer DESCRIPTOR INTO Clause INTO Clause , output variable name INTO E/C : output indicator variable + INDICATOR SQL DESCRIPTOR E/C output indicator variable ' output descriptor ' output descriptor variable DESCRIPTOR output sqlda pointer SQL Statements 49 EXECUTE IMMEDIATE EXECUTE IMMEDIATE + ESQL Quoted String p. 107 EXECUTE IMMEDIATE statement variable name EXECUTE PROCEDURE + EXECUTE PROCEDURE Procedure Name p. 107 ( ) , Argument ESQL SPL INTO Argument Argument Expression p. 96 parameter name = SELECT Statement (Singleton) p. 69 50 SQL Quick Syntax Guide , host variable FETCH FETCH , ESQL FETCH INTO cursor id + data variable cursor variable + NEXT PREVIOUS indicator INDICATOR variable data structure + PRIOR FIRST LAST CURRENT USING row position RELATIVE indicator variable SQL DESCRIPTOR descriptor variable E/C + DESCRIPTOR - 'descriptor' sqlda pointer row position ABSOLUTE FLUSH ESQL + FLUSH cursor id cursor variable SQL Statements 51 FREE FREE ESQL + cursor id FREE cursor variable statement id statement id variable GET DESCRIPTOR + ESQL GET DESCRIPTOR 'descriptor ' host variable = COUNT , descriptor variable VALUE item number item number variable 52 SQL Quick Syntax Guide Described Item Info p. 53 GET DIAGNOSTICS Described Item Info Described Item Info field host variable = TYPE LENGTH PRECISION SCALE NULLABLE INDICATOR NAME DATA ITYPE IDATA ILENGTH GET DIAGNOSTICS + ESQL GET DIAGNOSTICS Statement Clause EXCEPTION Clause p. 54 Statement Clause , Statement Clause st_var = MORE NUMBER ROW_COUNT SQL Statements 53 GET DIAGNOSTICS EXCEPTION Clause EXCEPTION Clause , EXCEPTION except_num en_var ex_var = CLASS_ORIGIN CONNECTION_ALIAS MESSAGE_LENGTH MESSAGE_TEXT RETURNED_SQLSTATE SERVER_NAME SUBCLASS_ORIGIN 54 SQL Quick Syntax Guide GRANT GRANT GRANT Database-Level Privileges + TO PUBLIC , user ' user ' OL + TO role name PUBLIC , user WITH GRANT OPTION ' user ' role name ' role name' + EXECUTE ON Procedure Name p. 107 Table Privileges for PUBLIC and Users p. 56 OL Table Privileges for Roles p. 56 + Database-Level Privileges Database-Level Privileges CONNECT RESOURCE DBA SQL Statements 55 GRANT Table Privileges for PUBLIC and Users Table Privileges for PUBLIC and Users TableLevel Privileges p. 57 ON Table Name p. 108 TO View Name p. 109 PUBLIC , user ' user ' Synonym Name p. 108 + + AS grantor WITH GRANT OPTION Table Privileges for Roles Table Privileges for Roles TableLevel Privileges p. 57 ON Table Name p. 108 View Name p. 109 TO role name ' role name' Synonym Name p. 108 AS grantor 56 SQL Quick Syntax Guide GRANT Table-Level Privileges Table-Level Privileges ALL PRIVILEGES , INSERT DELETE SELECT , + ( UPDATE , + ( , ( INDEX ) column name REFERENCES + ) column name column name ) ALTER SQL Statements 57 GRANT FRAGMENT GRANT FRAGMENT , OL + GRANT FRAGMENT Fragment-Level Privileges ON tablename ( dbspace , user TO WITH GRANT OPTION 'user ' Fragment-Level Privileges Fragment-Level Privileges ALL , INSERT DELETE UPDATE 58 SQL Quick Syntax Guide AS grantor ) INFO INFO DB INFO TABLES + COLUMNS Table Name p. 108 FOR INDEXES ACCESS PRIVILEGES REFERENCES STATUS OL FRAGMENTS INSERT INSERT INTO Table Name p. 108 View Name p. 109 VALUES Clause p. 61 , ( column name Synonym Name p. 108 EXECUTE PROCEDURE ) SELECT Statement (Subset) p. 69 + Procedure Name p. 107 ( , ) Argument p. 60 SQL Statements 59 INSERT Argument Argument Expression p. 96 parameter name 60 SQL Quick Syntax Guide = SELECT Statement (Singleton) p. 69 INSERT VALUES Clause VALUES Clause VALUES , ESQL ( variable name ) : indicator variable + $ indicator variable NULL Literal Number p. 107 Quoted String p. 107 + USER p. 97 Literal DATETIME p. 105 Literal INTERVAL p. 106 TODAY p. 97 CURRENT p. 97 OL SITENAME p. 97 DBSERVERNAME p. 97 SQL Statements 61 LOAD LOAD DB + LOAD FROM ' filename ' DELIMITER INSERT INTO ' delimiter ' Table Name p. 108 , Synonym Name p. 108 column name ) ) View Name p. 109 LOCK TABLE + LOCK TABLE Table Name p. 108 Synonym Name p. 108 62 SQL Quick Syntax Guide IN SHARE EXCLUSIVE MODE OPEN OPEN ESQL OPEN cursor id E/C + cursor variable WITH REOPTIMIZATION , variable name USING 'descriptor' SQL DESCRIPTOR descriptor variable E/C DESCRIPTOR sqlda pointer OUTPUT DB + OUTPUT TO filename WITHOUT HEADINGS PIPE program SELECT Statement p. 69 PREPARE ESQL + PREPARE statement id statement id variable FROM Quoted String p. 107 statement variable name SQL Statements 63 PUT PUT ESQL + PUT cursor id , cursor variable FROM variable name + : indicator variable $ indicator variable INDICATOR USING indicator variable ' descriptor ' SQL DESCRIPTOR descriptor variable E/C DESCRIPTOR sqlda pointer RECOVER TABLE SE + RECOVER TABLE Table Name p. 108 RENAME COLUMN + 64 RENAME COLUMN SQL Quick Syntax Guide Table Name p. 108 .old column name TO new column name RENAME DATABASE RENAME DATABASE OL RENAME DATABASE + old database name TO new database name RENAME TABLE + RENAME TABLE Table Name p. 108 TO new table name REPAIR TABLE SE DB + REPAIR TABLE Table Name p. 108 SQL Statements 65 REVOKE REVOKE + REVOKE Table-Level Privileges p. 67 ON table name FROM PUBLIC , view name user synonym name EXECUTE ON OL 'user ' Procedure Name p. 107 OL role name role name ' role ' name Table-Level Privileges p. 67 ON table name FROM PUBLIC , view name user synonym name 'user ' OL CASCADE RESTRICT DatabaseLevel Privileges p. 67 FROM PUBLIC , user 'user ' 66 SQL Quick Syntax Guide REVOKE Table-Level Privileges Table-Level Privileges ALL PRIVILEGES , INSERT DELETE SELECT UPDATE INDEX ALTER REFERENCES Database-Level Privileges Database-Level Privileges CONNECT RESOURCE DBA SQL Statements 67 REVOKE FRAGMENT REVOKE FRAGMENT OL + REVOKE FRAGMENT Fragment-Level Privileges ON tablename , FROM , ( 'user ' ) dbspace Fragment-Level Privileges Fragment-Level Privileges ALL , INSERT DELETE UPDATE ROLLBACK WORK ROLLBACK WORK 68 SQL Quick Syntax Guide user ROLLFORWARD DATABASE ROLLFORWARD DATABASE SE + ROLLFORWARD DATABASE Database Name p. 93 SELECT UNION UNION ALL SELECT Select Clause p. 70 FROM Clause p. 71 ESQL SPL INTO Clause p. 71 WHERE Clause p. 72 ORDER BY Clause p. 74 HAVING Clause p. 74 GROUP BY Clause p. 73 + + INTO TEMP Clause p. 74 FOR READ ONLY FOR UPDATE , OF column name SQL Statements 69 SELECT SELECT Clause , Select Clause Select List ALL DISTINCT + UNIQUE Select List Select List Expression p. 96 display label AS * 70 SQL Quick Syntax Guide Table Name p. 108 . View Name p. 109 . Synonym Name p. 108 . SELECT INTO Clause INTO Clause , INTO data variable ESQL + : indicator variable $ indicator variable INDICATOR indicator variable data structure FROM Clause FROM Clause FROM Table Name p. 108 View Name p. 109 Synonym Name p. 108 alias + , Additional Tables p. 72 AS SQL Statements 71 SELECT Additional Tables Additional Tables , Table Name p. 108 alias View Name p. 109 + + AS Synonym Name p. 108 OUTER Table Name p. 108 alias View Name p. 109 AS Synonym Name p. 108 OUTER ( Table Name p. 108 View Name p. 109 ) alias AS Synonym Name p. 108 WHERE Clause WHERE Clause AND WHERE Condition p. 89 Join p. 73 72 SQL Quick Syntax Guide , Additional Tables SELECT Join Option Join column name Relational Operator p. 108 column name Table Name p. 108 . Table Name p. 108 . alias . alias . View Name p. 109 . View Name p. 109 . Synonym Name p. 108 Synonym Name p. 108 . . GROUP BY Clause , GROUP BY Clause column name GROUP BY Table Name p. 108 View Name p. 109 . Synonym Name p. 108 . alias . + select number SQL Statements 73 SELECT HAVING Clause HAVING Clause Condition p. 89 HAVING ORDER BY Clause ORDER BY Clause , column name ORDER BY Table Name p. 108 . View Name p. 109 . Synonym Name p. 108 . alias . + ASC [ first, last ] select number + display label ROWID INTO TEMP Clause INTO TEMP Clause INTO TEMP temp table name WITH NO LOG 74 SQL Quick Syntax Guide DESC SET SET + Table-Mode Format p. 75 SET OL List-Mode Format p. 77 TransactionMode Format p. 77 Table-Mode Format Table-Mode Format , CONSTRAINTS INDEXES TRIGGERS FOR table name Object Modes for Constraints and Unique Indexes p. 76 Object Modes for Triggers and Duplicate Indexes p. 76 SQL Statements 75 SET Object Modes for Constraints and Unique Indexes Object Modes for Constraints and Unique Indexes DISABLED ENABLED FILTERING WITHOUT ERROR WITH ERROR Object Modes for Triggers and Duplicate Indexes Object Modes for Triggers and Duplicate Indexes DISABLED ENABLED 76 SQL Quick Syntax Guide SET List-Mode Format List-Mode Format , CONSTRAINTS constraint name Object Modes for Constraints and Unique Indexes p. 76 , INDEXES index name Object Modes for Constraints and Unique Indexes p. 76 Object Modes for Triggers and Duplicate Indexes p. 76 , TRIGGERS trigger name Object Modes for Triggers and Duplicate Indexes p. 76 Transaction-Mode Format Transaction-Mode Format CONSTRAINTS , constraint name IMMEDIATE ALL DEFERRED SQL Statements 77 SET CONNECTION SET CONNECTION ESQL 'connection name' SET CONNECTION E/C + + DORMANT conn_nm variable Database Environment p. 25 + DEFAULT E/C + CURRENT SET DATASKIP OL + SET DATASKIP ON , dbspace OFF DEFAULT SET DEBUG FILE TO + SET DEBUG FILE TO ' filename ' variable name character expression 78 SQL Quick Syntax Guide WITH APPEND SET DESCRIPTOR SET DESCRIPTOR + ESQL SET DESCRIPTOR ' descriptor ' COUNT = value count variable descriptor variable VALUE item number , Item Descriptor Information item number variable Item Descriptor Information Item Descriptor Information = TYPE literal integer integer-host variable LENGTH PRECISION SCALE NULLABLE INDICATOR ITYPE ILENGTH DATA IDATA NAME = Literal Number p. 107 Literal DATETIME p. 105 Literal INTERVAL p. 106 Quoted String p. 107 data variable SQL Statements 79 SET EXPLAIN SET EXPLAIN + ON SET EXPLAIN OFF SET ISOLATION OL + SET ISOLATION TO DIRTY READ COMMITTED READ CURSOR STABILITY REPEATABLE READ SET LOCK MODE TO + SET LOCK MODE TO WAIT OL seconds NOT WAIT SET LOG OL + SET LOG BUFFERED 80 SQL Quick Syntax Guide SET OPTIMIZATION SET OPTIMIZATION + HIGH SET OPTIMIZATION LOW SET PDQPRIORITY OL + SET PDQPRIORITY DEFAULT LOW OFF HIGH percent-of-resources SET ROLE + SET ROLE role name OL NULL NONE SET SESSION AUTHORIZATION ESQL OL SET SESSION AUTHORIZATION TO 'user ' SQL Statements 81 SET TRANSACTION SET TRANSACTION , SET TRANSACTION READ WRITE 1 READ ONLY OL 1 ISOLATION LEVEL READ COMMITTED REPEATABLE READ SERIALIZABLE , READ ONLY 1 OL 1 ISOLATION LEVEL READ UNCOMMITTED START DATABASE SE + START DATABASE Database Name p. 93 WITH LOG IN ' pathname ' MODE ANSI WITH NO LOG 82 SQL Quick Syntax Guide START VIOLATIONS TABLE START VIOLATIONS TABLE + START VIOLATIONS TABLE FOR tablename , MAX ROWS violations USING diagnostics numrows STOP VIOLATIONS TABLE STOP VIOLATIONS TABLE FOR + tablename UNLOAD DB + UNLOAD TO SELECT Statement p. 69 'filename' DELIMITER 'delimiter' UNLOCK TABLE + UNLOCK TABLE Table Name p. 108 Synonym Name p. 108 SQL Statements 83 UPDATE UPDATE Table Name p. 108 UPDATE SET Clause SET Condition p. 89 WHERE View Name p. 109 ESQL SPL Synonym Name p. 108 CURRENT OF cursor id SET Clause SET Clause , column name = Expression (Subset) p. 96 ( + SELECT Statement (Subset) p. 69 ) NULL , ( column name , ) = ( * ( SELECT Statement (Subset) p. 69 NULL 84 SQL Quick Syntax Guide ) Expression (Subset) p. 96 ) UPDATE STATISTICS UPDATE STATISTICS UPDATE STATISTICS + FOR PROCEDURE Procedure Name p. 107 LOW Table Name p. 108 FOR TABLE DROP DISTRIBUTIONS , Synonym Name p. 108 column name ( ) MEDIUM FOR TABLE Table Name p. 108 RESOLUTION percent , Synonym Name p. 108 ( column name ) OL conf DISTRIBUTIONS ONLY HIGH FOR TABLE RESOLUTION Table Name p. 108 Synonym Name p. 108 , ( column name percent OL ) DISTRIBUTIONS ONLY SQL Statements 85 WHENEVER WHENEVER ESQL WHENEVER SQLERROR CONTINUE + GO TO NOT FOUND : label GOTO + STOP SQLWARNING E/CO CALL ERROR label E/CO function name + PERFORM 86 SQL Quick Syntax Guide paragraph name Section SQL Segments Condition Condition AND OR Comparison Condition p. 90 NOT Condition with Subquery p. 91 SQL Segments 89 Condition Comparison Conditions (Boolean Expressions) Comparison Condition Expression p. 96 Relational Operator p. 108 Expression p. 96 Expression p. 96 Expression p. 96 BETWEEN NOT Expression p. 96 , Expression p. 96 + AND Literal Number p. 107 ( IN NOT ) Literal DATETIME p. 105 Literal INTERVAL p. 106 Quoted String p. 107 90 TODAY Table Name p. 108 . View Name p. 109 . Synonym Name p. 108 . alias . Table Name p. 108 . View Name p. 109 . Synonym Name p. 108 . alias . SQL Quick Syntax Guide USER CURRENT DATETIME Field Qualifier p. 95 OL SITENAME DBSERVERNAME column name IS NULL NOT LIKE NOT + MATCHES column name Quoted String p. 107 ESCAPE 'char' column name Condition Condition with a Subquery Condition with Subquery IN Subquery p. 92 EXISTS Subquery p. 92 ALL/ANY/SOME Subquery p. 92 SQL Segments 91 Condition IN Subquery IN Subquery Expression p. 96 IN ( NOT Condition with Subquery p. 91 ) EXISTS Subquery EXISTS Subquery EXISTS ( NOT Condition with Subquery p. 91 ) ALL/ANY/SOME Subquery ALL/ANY/SOME Subquery Expression p. 96 Relational Operator p. 108 ( ALL ANY SOME 92 SQL Quick Syntax Guide Condition with Subquery p. 91 ) Constraint Name Constraint Name Identifier p. 104 owner. OL : database 'owner'. @dbservername Database Name dbname OL OL @ dbservername ' //dbservername/dbname ' ESQL variable name SE ' //dbservername/pathname/dbname ' ' /pathname/dbname@dbservername ' SQL Segments 93 Data Type Data Type CHAR ( CHARACTER ) size (1) GLS NCHAR + + DATE + DATETIME DATETIME Field Qualifier p. 95 DECIMAL ( DEC ) precision NUMERIC , scale 16 FLOAT ( float precision ) DOUBLE PRECISION INTEGER INT + INTERVAL + MONEY INTERVAL Field Qualifier p. 105 ( ) precision 16 ,2 , scale SERIAL + ( + (1) start ) SMALLFLOAT REAL SMALLINT OL 94 SQL Quick Syntax Guide INFORMIX-OnLine Dynamic Server-Specific Data Types p. 95 DATETIME Field Qualifier INFORMIX-OnLine Dynamic Server-Specific Data Types INFORMIX-OnLine Dynamic Server-Specific Data Types + TEXT + BYTE IN TABLE blobspace OP family name + VARCHAR ( ) max , GLS + ,0 NVARCHAR CHARACTER VARYING reserve ( ) max , reserve DATETIME Field Qualifier YEAR MONTH TO YEAR DAY TO MONTH HOUR MINUTE TO DAY TO HOUR SECOND TO MINUTE FRACTION TO SECOND TO FRACTION (3) (digit) SQL Segments 95 Expression Expression + - * / || Column Expressions p. 97 + Constant Expressions p. 97 Function Expressions p. 98 Aggregate Expressions p. 103 Procedure Call Expressions p. 103 variable name SPL procedure variable name ( 96 SQL Quick Syntax Guide Expression ) Expression Column Expressions column name Table Name p. 108 . alias . View Name p. 109 . Synonym Name p. 108 . + + [first, last ] ROWID Constant Expressions Quoted String p. 107 USER OL + SITENAME DBSERVERNAME + Literal Number p. 107 TODAY CURRENT Literal DATETIME p. 105 DATETIME Field Qualifier p. 95 Literal INTERVAL p. 106 n UNITS datetime unit SQL Segments 97 Expression Function Expressions + Algebraic Functions p. 99 DBINFO Function p. 100 Exponential and Logarithmic Functions p. 100 HEX Function p. 100 LENGTH Function p. 101 Time Functions p. 101 Trigonometric Functions p. 102 TRIM Function p. 102 98 SQL Quick Syntax Guide Expression Algebraic Functions Algebraic Functions ) ABS ( MOD ( dividend, divisor ) POW ( base, exponent ) ROOT ( radicand num_expression ) , index ,2 ROUND ( ) Expression p. 96 , rounding factor ,0 SQRT ( TRUNC ( sqrt_radicand ) ) Expression p. 96 , truncating factor ,0 SQL Segments 99 Expression DBINFO Function DBINFO Function DBINFO ( 'DBSPACE' OL ) tblspace num , expression 'sqlca.sqlerrd1' 'sqlca.sqlerrd2' OL + 'sessionid' Exponential and Logarithmic Functions Exponential and Logarithmic Functions float expression LOGN ( ( float expression ) ) LOG10 ( float expression ) EXP HEX Function HEX Function HEX 100 SQL Quick Syntax Guide ( integer expression ) Expression LENGTH Function LENGTH Functions + Quoted String p. 107 ( LENGTH CHAR_LENGTH SPL ESQL CHARACTER_LENGTH ) variable name column name OCTET_LENGTH Table Name p. 108 . Time Functions Time Functions + DATE DAY ( non-date expression ) ( date/ datetime expression ) MONTH WEEKDAY YEAR ( EXTEND MDY ( date/ datetime expression month integer expression , ) , day integer expression first TO last , year integer expression ) SQL Segments 101 Expression Trigonometric Functions Trigonometric Functions COS ( radian expression ) ( numeric expression ) SIN TAN ASIN ACOS ATAN ATAN2 ( y, x ) TRIM Function TRIM Function TRIM source character value expression ( LEADING TRAILING FROM trim character value expression BOTH trim character value expression 102 SQL Quick Syntax Guide FROM ) Expression Aggregate Expressions COUNT (*) ( AVG MAX MIN SUM COUNT DISTINCT UNIQUE ( DISTINCT + UNIQUE ( COUNT Table Name p. 108 . Synonym Name p. 108 . View Name p. 109 . column name ) ALL ( AVG MAX MIN SUM ALL ) Expression (Subset) p. 96 RANGE STDEV VARIANCE Procedure Call Expressions , Procedure Name p. 107 ( Expression p. 96 called variable ) = SQL Segments 103 Identifier Identifier letter underscore letter digit underscore Delimited Identifier Delimited Identifiers Delimited Identifier double quote letter double quote digit underscore nonalphanumeric character Index Name Identifier owner. OL : database @ dbservername 104 SQL Quick Syntax Guide 'owner'. INTERVAL Field Qualifier INTERVAL Field Qualifier YEAR TO YEAR (y-precision) (4) MONTH TO MONTH (precision) (2) DAY TO DAY (precision) (2) HOUR TO HOUR (precision) (2) MINUTE TO MINUTE (precision) (2) SECOND (precision) TO SECOND (2) TO FRACTION FRACTION (f-precision) (3) Literal DATETIME DATETIME ( Numeric Date p. 106 ) DATETIME Field Qualifier p. 95 SQL Segments 105 Literal Interval Numeric Date Numeric Date yyyy mo dd space hh : mi : ss . f Literal Interval INTERVAL 106 SQL Quick Syntax Guide ( Numeric Date ) INTERVAL Field Qualifier p. 105 Literal Number Literal Number digit + - . digit . digit E digit + - Procedure Name Identifier p. 104 owner. OL + 'owner' : database @ dbservername Quoted String ' ' character + " '' " character "" SQL Segments 107 Relational Operator Relational Operator < <= > = >= <> + != Synonym Name owner. OL + 'owner'. : database Identifier p. 104 @ dbservername Table Name Identifier p. 104 owner. OL + 'owner'. : database @ dbservername 108 SQL Quick Syntax Guide View Name View Name Identifier p. 104 owner. OL + : database 'owner'. @ dbservername SQL Segments 109 Section III Stored Procedure Language Statements CALL CALL CALL Procedure Name p. 107 ( ) ; , , Argument RETURNING procedure variable Argument Argument Expression p. 96 parameter name = SELECT Statement (Subset) p. 69 CONTINUE CONTINUE FOR ; WHILE FOREACH SPL Statements 113 DEFINE DEFINE , DEFINE SQL Data Type (Subset) p. 94 variable name GLOBAL OL REFERENCES DEFAULT Default Value p. 115 DEFAULT NULL BYTE TEXT , SQL Data Type (Subset) p. 94 variable name OL REFERENCES BYTE TEXT LIKE Table Name p. 108 Synonym Name p. 108 View Name p. 109 PROCEDURE 114 SQL Quick Syntax Guide . column name ; EXIT Default Value Clause Default Value Literal Number p. 107 Quoted String p. 107 Literal Interval p. 106 Literal Datetime p. 105 CURRENT p. 90 DATETIME Field Qualifier p. 95 USER TODAY NULL OL DBSERVERNAME SITENAME EXIT EXIT FOR ; WHILE FOREACH SPL Statements 115 FOR FOR , FOR variable name IN ( left right TO expression expression ) Statement Block p. 31 END FOR ; STEP increment expression , expression = left expression TO STEP 116 SQL Quick Syntax Guide right expression increment expression FOREACH FOREACH SELECT...INTO Statement p. 71 FOREACH cursor name Statement Block p. 31 END FOREACH ; WITH HOLD FOR WITH HOLD EXECUTE PROCEDURE Procedure Name p. 107 ( ) , variable name INTO , parameter name = Expression (Subset) p. 96 IF IF Condition p. 89 THEN END IF ; IF Statement List p. 118 ELIF Condition p. 89 THEN IF Statement List p. 118 ELSE IF Statement List p. 118 SPL Statements 117 IF IF Statement List IF Statement List BEGIN Statement Block p. 31 END CALL Statement p. 113 CONTINUE Statement p. 113 EXIT Statement p. 115 FOR Statement p. 116 FOREACH Statement p. 117 IF Statement p. 117 LET Statement p. 119 RAISE EXCEPTION Statement p. 119 RETURN Statement p. 120 SYSTEM Statement p. 120 TRACE Statement p. 120 WHILE Statement p. 120 SQL Statement 118 SQL Quick Syntax Guide LET LET , , , variable name LET Procedure Name p. 107 = Expression p. 96 ( called variable ; ) = , Expression p. 96 ON EXCEPTION Statement Block p. 31 ON EXCEPTION , IN SET SQL error variable ( END EXCEPTION ; error number ) WITH RESUME , ISAM error variable , error data variable RAISE EXCEPTION RAISE EXCEPTION SQL error ; , ISAM error , error text variable SPL Statements 119 RETURN RETURN ; RETURN , Expression p. 96 WITH RESUME SYSTEM ; expression SYSTEM character variable TRACE ; ON TRACE OFF PROCEDURE Expression p. 96 WHILE WHILE Condition p. 89 Statement Block p. 31 END WHILE ; 120 SQL Quick Syntax Guide Section IV INFORMIX-OnLine Optical Statements ALTER OPTICAL CLUSTER ALTER OPTICAL CLUSTER + DB ALTER OPTICAL CLUSTER cluster name ESQL CLUSTERSIZE Clause owner. CLUSTERSIZE Clause CLUSTERSIZE Clause CLUSTERSIZE 500 clustersize CREATE OPTICAL CLUSTER + DB ESQL CREATE OPTICAL CLUSTER cluster name FOR Clause ON Clause p. 124 owner. CLUSTERSIZE Clause FOR Clause , FOR Clause FOR Table Name p. 108 ( blob column name ) INFORMIX-OnLine/Optical Statements 123 ON Clause ON Clause , ON Clause cluster-key column name ( ON ) DROP OPTICAL CLUSTER + DROP OPTICAL CLUSTER DB ESQL cluster name owner. RELEASE + DB ESQL 'family name' RELEASE ESQL family-name variable SPL procedure variable name 124 SQL Quick Syntax Guide volume number RESERVE RESERVE + DB ESQL 'family name' RESERVE volume number ESQL family-name variable SPL procedure variable name SET MOUNTING TIMEOUT + DB ESQL SET MOUNTING TIMEOUT TO WAIT NOT WAIT seconds Function Expressions Function Expressions p. 98 + DB ESQL DESCR (blob column name) FAMILY (blob column name) VOLUME (blob column name) INFORMIX-OnLine/Optical Statements 125