INTEGER SERIAL[(n0)] CHAR(n) DATE
SMALLINT DECIMAL(m,n) DATETIME qualif1 TO qualif2
REAL MONEY(m,n) INTERVAL qualif1 TO qualif2
FLOAT RECORD ARRAY [i,j,k] OF datatype
qualif \in {YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,FRACTION(n)}
: ** * / mod + - ( )
, CHAR, DECIMAL
: -7 mod 3 = -1
: mod ** SELECT
4GL (. Dh4fu E "
4GL") (. Dh4gcfu E "
4GL")
string1,string2
string [m,n]
string CLIPPED
string USING ""
string WORDWRAP
time + interval = time
time - time = interval
=, != <>, <=, >=, <, >
NOT , OR, AND
IS [NOT] NULL
"\"
string [NOT] LIKE "" [ESCAPE "escape-char"]
D% E D_ E !
string [NOT] MATCHES "" [ESCAPE "esc-char"]
D* E D? E D[ EabH D] E D[^ Ed D- Ez D] E
" D E", " D E", " D E", " D E"
. ,
WHERE
( 4GL)
Dstatus E { 0 | NOTFOUND | <0 }
Dquit_flag E ( 0 QUIT )
Dint_flag E ( 0 ^C )
define SQLCA record #
SQLCODE integer, =status
SQLERRM char(71), -
SQLERRP char(8), -
SQLERRD array[8] of int,.
SQLAWARN char(8) warning
end record
SQLERRD[1]
SQLERRD[2] serial ISAM error cod
SQLERRD[3]
SQLERRD[4] CPU cost
SQLERRD[5] offset of error into SQL-st
SQLERRD[6] ROWID of last row
SQLERRD[7]
SQLERRD[8]
MAIN ( )
. . .
END MAIN
CALL function-name ([ ]) [RETURNING . ]
FUNCTION function-name ([ ])
. . .
[RETURN expr-list]
. . .
END FUNCTION
REPORT report-name(variable-list) . Dformat E
[DEFINE-statement]
. . .
[OUTPUT
output-statement
. . .]
[ORDER [EXTERNAL] BY sort-list
FORMAT
format-statement
. . .
4gl-statement
. . .
END REPORT
START REPORT report-name
[TO {file-name | PRINTER | PIPE program}]
OUTPUT TO REPORT report-name (, [, ...])
FINISH REPORT report-name
DEFINE { type | LIKE table.column
| RECORD {LIKE table.* | [,..]
END RECORD} } [,...]
type :
INTEGER CHAR(n) DATE
SMALLINT DECIMAL(m,n) DATETIME qualif1 TO qualif2
REAL MONEY(m,n) INTERVAL qualif1 TO qualif2
FLOAT RECORD ARRAY [i,j,k] OF datatype
qualif {YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,FRACTION(n)}
GLOBALS { " GLOBALS " |
DEFINE-st
. . . ,
END GLOBALS }
INITIALIZE {LIKE column-list | TO NULL}
NULL DEFAULT
LET =
WHENEVER { ERROR | WARNING | NOT FOUND }
{ GOTO [:]label | CALL function-name | CONTINUE | STOP }
!!! function-name () !!!
DEFER INTERRUPT ^C
DEFER QUIT QUIT
QUIT => quit_flag!=0, ^C => int_flag!=0
CALL function([ ]) [RETURNING ]
! ! !
CASE CASE ()
WHEN .. WHEN 1
. . . . . .
[EXIT CASE] [EXIT CASE]
. . . . . .
WHEN .. WHEN 2
. . . . . .
[OTHERWISE] [OTHERWISE]
. . . . . .
END CASE END CASE
IF THEN
. . .
[ELSE
. . . ]
END IF IF !!!
FOR I= i1 TO i2 [STEP i3]
statement
. . .
[CONTINUE FOR]
. . .
[EXIT FOR]
. . .
END FOR
CONTINUE { FOR | FOREACH | MENU | WHILE }
EXIT { CASE | WHILE | FOR | FOREACH | MENU | INPUT | DISPLAY
| PROGRAM[(status code for UNIX)] }
WHILE
. . .
. . .
[CONTINUE WHILE]
. . .
[EXIT WHILE]
. . .
END WHILE
GOTO [:] ':' ANSI
LABEL :
RUN {" UNIX"|char-variable} [RETURNING int-variable
| WITHOUT WAITING]
SLEEP -. n
MENU " "
COMMAND { KEY (key-list) |
[KEY (key-list)] "k "
[" help"] [HELP help-number] }
key, , .
statement
. . .
[CONTINUE MENU]
. . .
[EXIT MENU]
. . .
[NEXT OPTION "k " #
[COMMAND . . . ]
. . .
END MENU
OPTIONS { :
PROMPT LINE p | FIRST
MESSAGE LINE m | FIRST + 1
FORM LINE f | FIRST + 2
COMMENT LINE c | LAST [-1]
ERROR LINE e | LAST
INPUT { WRAP | NO WRAP } | NO WRAP
INSERT KEY key-name | F1 !! :
DELETE KEY key-name | . F2 CONTROL-A,D,H,L,
NEXT KEY key-name | F3 CONTROL-Q,R,X,
PREVIOUS KEY key-name | F4 CONTROL-C,S,Q,Z
ACCEPT KEY key-name | ESC
HELP FILE "help-file" |
HELP KEY key-name | CONTROL-W mkmessage
INPUT ATTRIBUTE( ) |
DISPLAY ATTRIBUTE( )
} [,...] :
NORMAL DREVERSE E FORM
BOLD UNDERLINE WINDOW
INVISIBLE BLINK
OPEN WINDOW window-name AT row, column
WITH { integer ROWS, integer COLUMNS | FORM "form-file" }
[ATTRIBUTE( )]
: BORDER :
BOLD, DIM, INVISIBLE , NORMAL NORMAL
DREVERSE E, UNDERLINE, B L I N K
PROMPT LINE n FIRST
MESSAGE LINE m FIRST + 1
FORM LINE m FIRST + 2
COMMENT LINE m LAST
CURRENT WINDOW IS { window name | SCREEN }
CLEAR {SCREEN | WINDOW window-name | FORM | }
CLOSE WINDOW window-name
OPEN FORM form-name FROM "form-file" .frm
DISPLAY FORM form-name [ATTRIBUTE( )]
CLOSE FORM form-name
MESSAGE , [ATTRIBUTE( )]
ERROR , [ATTRIBUTE( )]
REVERSE
PROMPT
[ATTRIBUTE( )] FOR [CHAR] variable
[HELP help-number] # variable
[ATTRIBUTE( )] # FOR CHAR -
[ON KEY (key-list)
statement : NORMAL DREVERSE E
. . . BOLD UNDERLINE
. . . DIM BLINK
END PROMPT] INVISIBLE
ON KEY
PROMPT, INPUT. .
DISPLAY { BY NAME |
TO { |screen-record[ D[ En D] E].*}[,..] |
AT row, column }
[ATTRIBUTE( )]
[ ]
INPUT { BY NAME [WITHOUT DEFAULTS] |
[WITHOUT DEFAULTS] FROM
{ | screen-record[ D[ En D] E].*}[,...]}
[ATTRIBUTE( )]
[HELP help-number]
[ { BEFORE FIELD ESC
| AFTER { FIELD | INPUT }
| ON KEY (key-list) }
statement . . .
[NEXT FIELD field-name]
[EXIT INPUT]
statement . . .
. . .
END INPUT ]
WHERE QUERY BY EXAMPLE
CONSTRUCT {BY NAME char-variable ON column-list |
char-variable ON column-list FROM
{ | screen-record[ D[ En D] E].*}[,...]}
[ATTRIBUTE( )]
:
: : :
* *X
? X?
| abc|cdef
>,<, >=,<=,<> >X
: X:YW
.. Date..Date
call set_count(- )
DISPLAY ARRAY record-array TO screen-array.*
[ATTRIBUTE( )]
[ ON KEY (key-list)
. . .
[EXIT DISPLAY]
. . .
END DISPLAY ] | [END DISPLAY]
SCROLL {field-list | screen-record.*} [,...}
{UP | DOWN} [BY int]
call set_count(- )
INPUT ARRAY record-array [WITHOUT DEFAULTS]
FROM screen-array.* [HELP help-number] [ATTRIBUTE(.)]
[{BEFORE {ROW | INSERT | DELETE | FIELD }[,...]
| AFTER {ROW|INSERT|DELETE|FIELD |INPUT}[,...]
| ON KEY (key-list) }
statement ...
[NEXT FIELD field-name]
statement...
[EXIT INPUT]
. . .
. . .
END INPUT ]
DISPLAY ARRAY :
arr_curr() .
arr_count() .
scr_line() .
CALL showhelp(helpnumber) - help
PREPARE statement-id FROM {char-variable | "SQL- [] "}
D SQL E -statement
, ?
D E :
DECLARE PREPARE LOAD
OPEN EXECUTE UNLOAD
CLOSE FETCH SELECT INTO variables
EXECUTE statment-id [USING input-list]
, ? input-list
FREE { statment-id | cursor-name }
DECLARE cursor-name [SCROLL] CURSOR [WITH HOLD] FOR
{ SELECT-st [FOR UPDATE [OF column-list]] |
INSERT-st | statment-id }
SCROLL - , .
statment-id - PREPARE
HOLD -
: SCROLL cursor FOR UPDATE,
-SCROLL cursora
: DECLARE cursor-name
.
OPEN cursor-name [USING ]
CLOSE cursor-name
SELECT-:
FOREACH cursor-name [INTO ]
. . .
[CONTINUE FOREACH]
. . .
[EXIT FOREACH]
. . .
END FOREACH
FETCH { NEXT | PREVIOUS | FIRST | LAST | CURRRENT |
RELATIVE m | ABSOLUTE n ] cursor-name
[INTO ]
cursor not SCROLL NEXT
, status=NOTFOUND
INSERT-:
PUT cursor-name [FROM ] ,
[ ? DECLAREd INSERT-st ]
FLUSH cursor-name
D ^^ E DSQL E D^^ E
CREATE, DROP, DATABASE, ALTER, RENAME
DELETE INSERT UPDATE LOAD UNLOAD
SELECT SELECT
GRANT/REVOKE, LOCK/UNLOCK TABLE, SET LOCK MODE
BEGIN WORK, COMMIT WORK, ROLLBACK WORK, START DATABASE, ...
D E !
CREATE DATABASE db-name [WITH LOG IN "pathname" [MODE ANSI]]
ansi ,
DROP DATABASE { database-name | char-variable }
DATABASE database-name [EXCLUSIVE]
CLOSE DATABASE
CREATE [TEMP] TABLE table-name( column-name datatype [NOT NULL]
[UNIQUE [CONSTRAINT constr-name]] [,...] )
[UNIQUE(uniq-col-list) [CONSTRAINT constr-name] ] [,..]
[WITH NO LOG]
[IN "pathname-directory"]
datatype :
INTEGER SERIAL[(n0)] DATE
SMALLINT DECIMAL(m,n) DATETIME qualif1 TO qualif2
REAL MONEY(m,n) INTERVAL qualif1 TO qualif2
FLOAT CHAR(n)
qualifier \in {YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,FRACTION(n)}
ALTER TABLE table-name #
{ ADD ( new-column-name datatype [NOT NULL]
[UNIQUE [CONSTRAINT constr-name]][,...] )
[BEFORE old-column-name]
|
DROP (old-column-name[,...])
|
MODIFY (old-column-name new-datatype [NOT NULL][,...])
|
ADD CONSTRAINT UNIQUE (old-column-name[,...])
[CONSTRAINT constr-name]
|
DROP CONSTRAINT (constr-name[,...])
} [,...]
RENAME TABLE old-table-name TO new-table-name
RENAME COLUMN table.old-column-name TO new-column-name
CREATE VIEW view-name [(column-list)]
AS SELECT-statement [WITH CHECK OPTION]
CREATE [UNIQUE|DISTINCT] [CLUSTER] INDEX index-name
ON table-name (column-name [DESC], ...)
ALTER INDEX index-name TO [NOT] CLUSTER
CREATE SYNONYM synonym-name FOR table-name
DROP VIEW view-name
DROP TABLE table-name
DROP INDEX index-name
DROP SYNONYM synonym-name
UPDATE STATISTICS [FOR TABLE table-name]
SET EXPLAIN {ON | OFF}
sqlexplain.out
DELETE FROM table-name [WHERE {condition | CURRENT OF cursor-name}]
!* 4GL *!
INSERT INTO table-name [(column-list)]
{ VALUES (value-list) | SELECT-statement }
UPDATE table-name SET {column-name ={ expression | (SELECT-st) } [,...]
| {(col-list) | [table.]*} =
{ ({ expr-list | (SELECT-st) } [,...]) | record-name.* }
[WHERE {condition | CURRENT OF cursor-name}]
!* 4GL *!
LOAD FROM "file-name" [DELIMITER "?"] { INSERT INTO table
[(col-list)] | char-variable with INSERT-st }
UNLOAD TO "file" [DELIMITER "?"] SELECT-statement
:
1|2| ... ||n|
...
|| ... |
OUTPUT TO {FILENAME | PIPE program} [WITHOUT HEADINGS] SELECT-st
INFORMIX-SQL
SELECT [ALL | UNIQUE] column-expr [col-lable] [,...]
[INTO ] !* 4GL *!
FROM { [OUTER] table-name [tab-alias] |
OUTER (table-expr) } [,...] -
(
[WHERE condition] )
[GROUP BY column-list [HAVING condition] ]
[ORDER BY column-name [DESC],...]
[INTO TEMP table-name]
WHERE conditions:
OR, AND, NOT
1 D E 1
D E = , > , < , >= , <= , <> , !=
column-name IS [NOT] NULL
[NOT] BETWEEN 1 AND 2
[NOT] IN (1 , ... [, ...] )
"\"
[NOT] LIKE "" [ESCAPE "escape-char"]
D% E D_ E "" ""
[NOT] MATCHES "" [ESCAPE "esc-char"]
D* E D? E " D E" " D E"
D[ EabH D] E D[^ Ed D- Ez D] E " D E" " D E"
D E {ALL | [ANY | SOME]} (SELECT-)
[NOT] IN (SELECT-) !* *!
[NOT] EXISTS (SELECT-) !* SQL *!
{DBpriv {PUBLIC
GRANT | TO | [WITH GRANT OPTION] [AS grantor]
TBpriv [,..] ON table} user-list} grantor
{DBpriv
REVOKE | FROM { PUBLIC | user-list }
TBpriv [,..] ON table}
TABLE PRIVILEGES :
DATABASE PRIVILEGES: ALTER DELETE
INDEX INSERT
CONNECT SELECT[(cols)]
RESOURCE UPDATE [(cols)]
DBA ALL [PRIVILEGES]
SET LOCK MODE TO [NOT] WAIT [ ]
.
LOCK TABLE table-name IN {SHARE | EXCLUSIVE} MODE
{ | }
UNLOCK TABLE table-name
CREATE DATABASE db-name WITH LOG IN "/pathname" [MODE ANSI]]
START DATABASE db-name WITH LOG IN "/pathname" [MODE ANSI]
(log-)
DATABASE database-name [EXCLUSIVE]
ROLLFORWARD DATABASE database-name
CLOSE DATABASE
BEGIN WORK !!
. . . !!
COMMIT WORK K
ROLLBACK WORK COMMIT
CREATE AUDIT FOR table-name IN "pathname"
. . .
RECOVER TABLE table-name
DROP AUDIT FOR table-name
VALIDATE LIKE column-list
(syscolval)?
status<0
:
4GL, INFORMIX-SQL
INFO { TABLES | { COLUMNS | INDEXES | ACCES | PRIVILEGES |
STATUS } FOR table-name }
CHECK TABLE owner.table-name
REPAIR TABLE table-name
Last-modified: Wed, 09 Sep 1998 09:09:21 GMT