Informix Guide to SQL
®
February 1998
Part No. 000-4367
Syntax
Informix Dynamic Server, Version 7.3
Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options, Version 8.2
Informix Dynamic Server, Developer Edition, Version 7.3
Informix Dynamic Server, Workgroup Edition, Version 7.3
ii Informix Guide to SQL: Syntax
Published by INFORMIX
Press Informix Software, Inc.
4100 Bohannon Drive
Menlo Park, CA 94025-1032
Copyright
1981-1998 by Informix Software, Inc. or its subsidiaries, provided that portions may be
copyrighted by third parties, as set forth in documentation. All rights reserved.
The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in the
United States of America as indicated by “
,” and in numerous other countries worldwide:
Answers OnLine
; INFORMIX
; Informix
; Illustra
; C-ISAM
; DataBlade
;Dynamic Server
; Gateway
;
NewEra
All other names or marks may be registered trademarks or trademarks of their respective owners.
Documentation Team: Evelyn Eldridge-Diaz, Geeta Karmarkar, Barbara Nomiyama, Tom Noronha,
Kathy Schaefer, Kami Shahi, Judith Sherwood
RESTRICTED RIGHTS/SPECIAL LICENSE RIGHTS
Software and documentation acquired with US Government funds are provided with rights as follows: (1) if
for civilian agency use, with Restricted Rights as defined in FAR 52.227-19; (2) if for Dept. of Defense use, with
rights as restricted by vendor's standard license, unless superseded by negotiated vendor license as prescribed
in DFAR 227.7202. Any whole or partial reproduction of software or documentation marked with this legend
must reproduce the legend.
Table of Contents
Table of
Contents
Introduction
About This Manual .................. 3
Types of Users .................. 3
Software Dependencies ............... 4
Assumptions About Your Locale............ 4
Demonstration Databases .............. 5
New Features .................... 5
New Features in Version 7.3 ............. 5
New Features in Version 8.2 ............. 7
Documentation Conventions .............. 7
Typographical Conventions ............. 8
Icon Conventions ................. 9
Syntax Conventions ................ 11
Sample-Code Conventions.............. 15
Additional Documentation ............... 16
On-Line Manuals ................. 16
Printed Manuals ................. 17
Error Message Files ................ 17
Documentation Notes, Release Notes, Machine Notes .... 18
Related Reading ................. 19
Compliance with Industry Standards ........... 20
Informix Welcomes Your Comments ............ 20
Chapter 1 Overview of SQL Syntax
How to Enter SQL Statements .............. 1-3
How to Enter SQL Comments .............. 1-6
Categories of SQL Statements .............. 1-9
Stored Procedure Statements .............1-11
ANSI Compliance and Extensions ............1-12
iv Informix Guide to SQL: Syntax
Chapter 2 SQL Statements
ALLOCATE DESCRIPTOR ............. 2-6
ALTER FRAGMENT ............... 2-8
ALTER INDEX ................. 2-34
ALTER TABLE ................. 2-37
BEGIN WORK.................. 2-67
CLOSE .................... 2-70
CLOSE DATABASE ................ 2-73
COMMIT WORK................. 2-75
CONNECT ................... 2-77
CREATE DATABASE ............... 2-89
CREATE EXTERNAL TABLE ............ 2-92
CREATE INDEX .................2-106
CREATE PROCEDURE ..............2-134
CREATE PROCEDURE FROM ............2-144
CREATE ROLE .................2-146
CREATE SCHEMA ................2-148
CREATE SYNONYM ...............2-151
CREATE TABLE .................2-155
CREATE TRIGGER ................2-198
CREATE VIEW .................2-230
DATABASE...................2-236
DEALLOCATE DESCRIPTOR ............2-239
DECLARE ...................2-241
DELETE ....................2-258
DESCRIBE ...................2-262
DISCONNECT .................2-267
DROP DATABASE ................2-271
DROP INDEX ..................2-273
DROP PROCEDURE ...............2-275
DROP ROLE ..................2-276
DROP SYNONYM ................2-277
DROP TABLE ..................2-279
DROP TRIGGER .................2-282
DROP VIEW ..................2-283
EXECUTE ...................2-285
EXECUTE IMMEDIATE ..............2-294
EXECUTE PROCEDURE ..............2-297
FETCH ....................2-301
FLUSH ....................2-312
FREE .....................2-315
GET DESCRIPTOR ................2-318
GET DIAGNOSTICS ...............2-325
GRANT ....................2-343
GRANT FRAGMENT ...............2-360
INFO .....................2-369
INSERT ....................2-373
LOAD ....................2-384
Table of Contents v
LOCK TABLE ..................2-391
OPEN .....................2-394
OUTPUT ....................2-402
PREPARE ....................2-404
PUT......................2-418
RENAME COLUMN ................2-426
RENAME DATABASE ...............2-428
RENAME TABLE .................2-429
REVOKE ....................2-432
REVOKE FRAGMENT ...............2-444
ROLLBACK WORK ................2-449
SELECT ....................2-451
SET AUTOFREE .................2-503
SET CONNECTION ................2-506
SET Database Object Mode ..............2-513
SET DATASKIP..................2-535
SET DEBUG FILE TO................2-538
SET DEFERRED_PREPARE .............2-541
SET DESCRIPTOR.................2-545
SET EXPLAIN ..................2-553
SET ISOLATION .................2-556
SET LOCK MODE .................2-561
SET LOG ....................2-564
SET OPTIMIZATION................2-566
SET PDQPRIORITY ................2-570
SET PLOAD FILE .................2-574
SET Residency ..................2-576
SET ROLE ...................2-579
SET SCHEDULE LEVEL...............2-581
SET SESSION AUTHORIZATION ...........2-582
SET TRANSACTION ................2-585
SET Transaction Mode ...............2-591
START VIOLATIONS TABLE .............2-595
STOP VIOLATIONS TABLE .............2-614
UNLOAD....................2-616
UNLOCK TABLE .................2-621
UPDATE ....................2-623
UPDATE STATISTICS ...............2-635
WHENEVER...................2-646
Chapter 3 SPL Statements
CALL ..................... 3-4
CONTINUE ................... 3-7
DEFINE .................... 3-8
EXIT .....................3-16
FOR......................3-18
FOREACH ...................3-23
IF.......................3-27
