Format operatorov Informix-4GL
Tipy dannyh i vyrazheniya nad peremennymi
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
gde qualif \in {YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,FRACTION(n)}
Operacii chislovye: ** * / mod + - ( )
Vse argumenty, v tom chisle CHAR, preobrazuyutsya k tipu DECIMAL
Vnimanie: -7 mod 3 = -1
Vnimanie: mod i ** nel'zya ispol'zovat' v operatore SELECT
Mozhno pol'zovat'sya vstroennymi funkciyami 4GL (sm. Dh4fu E "Funkcii
4GL") i funkciyami na yazyke Si (sm. Dh4gcfu E "Prisoedinenie Si k
4GL")
string1,string2 scepit'
string [m,n] podstroka
string CLIPPED usech' probely sprava
string USING "format" formatirovat'
string WORDWRAP perenosit' dlinnuyu stroku
time + interval = time
time - time = interval
=, != ili <>, <=, >=, <, >
NOT , OR, AND
vyrazhenie IS [NOT] NULL
po umolchaniyu "\"
string [NOT] LIKE "shablon" [ESCAPE "escape-char"]
specsimvoly shablona D% E D_ E oznachayut ｦ ｧ!
string [NOT] MATCHES "shablon" [ESCAPE "esc-char"]
specsimvoly shablona D* E D? E D[ EabH D] E D[^ Ed D- Ez D] E
oznachayut " Dmnogo E", " Dodin E", " Dlyuboj iz E", " Dni odin iz E"
sm. tak zhe logicheskie vyrazheniya, dopustimye tol'ko v
predlozhenii WHERE
(Ustanavlivayutsya posle lyubogo operatora 4GL)
Dstatus E { 0 | NOTFOUND | <0 } kod zaversheniya operatora
Dquit_flag E ( ne 0 esli bylo nazhato QUIT )
Dint_flag E ( ne 0 esli bylo nazhato ^C )
define SQLCA record # sistemnaya zapis' s kodami zaversheniya
SQLCODE integer, =status
SQLERRM char(71), ｭ-
SQLERRP char(8), ｭ-
SQLERRD array[8] of int,メгm.
SQLAWARN char(8) リ warning ili probel
end record リ
SQLERRD[1] zarezervirovan
SQLERRD[2] serial znachenie ili ISAM error cod
SQLERRD[3] chislo obrabotannyh strok
SQLERRD[4] CPU cost zaprosa
SQLERRD[5] offset of error into SQL-st
SQLERRD[6] ROWID of last row
SQLERRD[7] zarezervirovan
SQLERRD[8] zarezervirovan
Operatory organizacii programmy
MAIN Glavnyj blok (dolzhen byt' rovno odin)
. . .
END MAIN
CALL function-name ([spisok argumentov]) [RETURNING vozvr. znach]
FUNCTION function-name ([spisok argumentov])
. . . Argumenty peredayutsya
[RETURN expr-list] po znacheniyu
. . .
END FUNCTION
REPORT report-name(variable-list) sm. fajl Dformat E
[DEFINE-statement]
. . .
[OUTPUT
output-statement
. . .]
[ORDER [EXTERNAL] BY sort-list
FORMAT
format-statement
. . .
4gl-statement
. . .
END REPORT
Generaciya otchetov ( sm. tak zhe fajl Dformat E )
START REPORT report-name
[TO {file-name | PRINTER | PIPE program}]
OUTPUT TO REPORT report-name (vyrazhenie, vyrazhenie [, ...])
FINISH REPORT report-name
DEFINE spisok peremennyh { type | LIKE table.column
| RECORD {LIKE table.* | spisok peremennyh [,..]
END RECORD} } [,...]
gde type mozhet byt' sleduyushchim:
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
gde qualif ｨ {YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,FRACTION(n)}
GLOBALS { "fajl s GLOBALS obｮyavleniyami" |
DEFINE-st Dolzhen lezhat' vne lyubogo bloka vo vseh
. . . modulyah, gde eti peremennye ispol'zuyutsya
END GLOBALS }
INITIALIZE spisok peremennyh {LIKE column-list | TO NULL}
prisvoit' peremennym NULL ili DEFAULT znacheniya
LET peremennaya = vyrazhenie
WHENEVER { ERROR | WARNING | NOT FOUND }
{ GOTO [:]label | CALL function-name | CONTINUE | STOP }
!!! function-name bez () !!!
DEFER INTERRUPT Zapretit' preryvanie programmy klavishej ^C
DEFER QUIT Zapretit' preryvanie programmy klavishej QUIT
Togda posle nazhatiya QUIT => quit_flag!=0, ^C => int_flag!=0
CALL function([spisok argumentov]) [RETURNING spisok peremennyh]
! ! ! peredacha po znacheniyu
CASE CASE (vyrazhenie)
WHEN logich.vyrazh. WHEN vyrazh1
. . . ili . . .
[EXIT CASE] [EXIT CASE]
. . . . . .
WHEN logich.vyrazh. WHEN vyrazh2
. . . . . .
[OTHERWISE] [OTHERWISE]
. . . . . .
END CASE END CASE
IF logicheskoe vyrazhenie THEN
. . .
[ELSE
. . . ]
END IF ne zabyvajte zakryvat' vse operatory 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 logicheskoe vyrazhenie
operatory . . .
. . .
[CONTINUE WHILE]
. . .
[EXIT WHILE]
. . .
END WHILE
GOTO [:] metka Dvoetochie ':' dlya sovmestimosti s ANSI standartom
LABEL metka: Dejstvuet tol'ko vnutri bloka
RUN {"komandnaya stroka UNIX"|char-variable} [RETURNING int-variable
| WITHOUT WAITING]
SLEEP celoe-vyrazh. Podozhdat' n sekund
MENU "Nazvanie menyu"
COMMAND { KEY (key-list) |
[KEY (key-list)] "komanda menyu"
[" podskazka help"] [HELP help-number] }
Libo key, libo pervaya bukva, obyazany byt' latinskimi.
statement
. . .
[CONTINUE MENU]
. . .
[EXIT MENU]
. . .
[NEXT OPTION "komanda menyu" # Perejti k
[COMMAND . . . ]
. . .
END MENU
OPTIONS { Po umolchaniyu:
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 | Vstavit' F1 !! Ne primenyat':
DELETE KEY key-name | Udal. str F2 CONTROL-A,D,H,L,
NEXT KEY key-name | Stranica F3 CONTROL-Q,R,X,
PREVIOUS KEY key-name | Stranica F4 CONTROL-C,S,Q,Z
ACCEPT KEY key-name | ESC
HELP FILE "help-file" | Predvaritel'no otkompilirovannyj
HELP KEY key-name | CONTROL-W utilitoj mkmessage
INPUT ATTRIBUTE(spisok atributov) |
DISPLAY ATTRIBUTE(spisok atributov)
} [,...] atributy:
NORMAL DREVERSE E FORM ispol'zovat' atributy
BOLD UNDERLINE WINDOW tekushchego okna
INVISIBLE BLINK
OPEN WINDOW window-name AT row, column
WITH { integer ROWS, integer COLUMNS | FORM "form-file" }
[ATTRIBUTE(spisok attributov)]
Atributy: BORDER Po umolchaniyu: net
BOLD, DIM, INVISIBLE , NORMAL NORMAL
DREVERSE E, UNDERLINE, B L I N K net
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 | spisok polej}
CLOSE WINDOW window-name
OPEN FORM form-name FROM "form-file" Bez rasshireniya .frm
DISPLAY FORM form-name [ATTRIBUTE(spisok attributov)]
CLOSE FORM form-name
Prostye operatory vyvoda na ekran
MESSAGE spisok peremennyh, konstant [ATTRIBUTE(spisok atributov)]
ERROR spisok peremennyh, konstant [ATTRIBUTE(spisok atributov)]
po umolchaniyu REVERSE
PROMPT spisok peremennyh i konstatnt
[ATTRIBUTE(attributy vyvoda)] FOR [CHAR] variable
[HELP help-number] # Vvesti znachenie v variable
[ATTRIBUTE(attributy vvoda)] # FOR CHAR - vvesti odin simvol
[ON KEY (key-list)
statement atributy: NORMAL DREVERSE E
. . . BOLD UNDERLINE
. . . DIM BLINK
END PROMPT] INVISIBLE
v ON KEY punkte nel'zya napryamuyu pol'zovat'sya operatorami
PROMPT, INPUT. Dlya ih vyzova primenyajte funkcii.
Vvod vyvod cherez ekrannye formy
Vyvesti v formu
DISPLAY { BY NAME spisok peremennyh |
spisok peremennyh TO {spisok polej|screen-record[ D[ En D] E].*}[,..] |
spisok peremennyh AT row, column }
[ATTRIBUTE(spisok atributov)]
[Ne stirat' znachenij iz formy pered vvodom]
INPUT { BY NAME spisok peremennyh [WITHOUT DEFAULTS] |
spisok peremennyh [WITHOUT DEFAULTS] FROM
{spisok polej | screen-record[ D[ En D] E].*}[,...]}
[ATTRIBUTE(spisok atributov)]
[HELP help-number]
[ { BEFORE FIELD podspisok polej po klavishe ESC
| AFTER { FIELD podspisok polej | INPUT }
| ON KEY (key-list) }
statement . . .
[NEXT FIELD field-name]
[EXIT INPUT]
statement . . .
. . .
END INPUT ]
konstruiruet WHERE uslovie dlya QUERY BY EXAMPLE
CONSTRUCT {BY NAME char-variable ON column-list |
char-variable ON column-list FROM
{spisok polej | screen-record[ D[ En D] E].*}[,...]}
[ATTRIBUTE(spisok atributov)]
v polyah mogut ispol'zovat'sya sluzhebnye simvoly:
simvol: primer: naznachenie:
* リ *X リ proizvol'naya stroka
? リ X? リ proizvol'nyj simvol
| abc|cdef ili
>,<, >=,<=,<> >X
: リ X:YW リ promezhutok
.. リ Date..Date リ promezhutok mezhdu datami
call set_count(kol-vo vyvodimyh strok) v programmnom massive
DISPLAY ARRAY record-array TO screen-array.*
[ATTRIBUTE(spisok atributov)]
[ ON KEY (key-list)
. . .
[EXIT DISPLAY]
. . .
END DISPLAY ] | [END DISPLAY]
SCROLL {field-list | screen-record.*} [,...} Prokrutit' stroki
{UP | DOWN} [BY int] v ekrannom massive
call set_count(kol-vo vyvodimyh strok) v programmnom massive
INPUT ARRAY record-array [WITHOUT DEFAULTS]
FROM screen-array.* [HELP help-number] [ATTRIBUTE(atr.)]
[{BEFORE {ROW | INSERT | DELETE | FIELD podspisok polej}[,...]
| AFTER {ROW|INSERT|DELETE|FIELD podspisok polej |INPUT}[,...]
| ON KEY (key-list) }
statement ...
[NEXT FIELD field-name]
statement...
[EXIT INPUT]
. . .
. . .
END INPUT ]
Vnutri operatora DISPLAY ARRAY mozhno pol'zovat'sya funkciyami:
arr_curr() nomer tekushchej stroki progr. massiva
arr_count() chislo zapolnennyh stroki progr. massiva
scr_line() nomer tekushchej stroki ekr. massiva
CALL showhelp(helpnumber) - vyvesti help
Dinamicheskoe sozdanie operatorov
PREPARE statement-id FROM {char-variable | "SQL-operator [y] "}
Izgotovit' D SQL E -statement iz simvol'noj stroki
Nel'zya vklyuchat' imena peremennyh, nuzhno zamenyat' ih na znak ?
DNel'zya E gotovit' operatory:
DECLARE PREPARE LOAD
OPEN EXECUTE UNLOAD
CLOSE FETCH SELECT INTO variables
EXECUTE statment-id [USING input-list]
Vypolnyaet, zameniv znaki ? na 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 - fakticheski, sozdaetsya vremennaya tablica.
statment-id - prigotovlenogo PREPARE
HOLD - ignorirovat' konec tranzakcii
Vnimanie: SCROLL cursor nel'zya otkryvat' FOR UPDATE, zato dlya
ne-SCROLL cursora mozhno ispol'zovat'
Vnimanie: operator DECLARE cursor-name dolzhen raspolagat'sya v
tekste programmy vyshe lyubogo ispol'zovaniya etogo kursora.
OPEN cursor-name [USING spisok peremennyh]
CLOSE cursor-name
dlya SELECT-kursora:
FOREACH cursor-name [INTO spisok peremennyh]
. . .
[CONTINUE FOREACH]
. . .
[EXIT FOREACH]
. . .
END FOREACH
FETCH { NEXT | PREVIOUS | FIRST | LAST | CURRRENT |
RELATIVE m | ABSOLUTE n ] cursor-name
[INTO spisok peremennyh]
esli cursor not SCROLL to mozhno tol'ko NEXT
esli stroki ne obnaruzheno, to status=NOTFOUND
dlya INSERT-kursora:
PUT cursor-name [FROM spisok peremennyh] vvesti stroku v bufer,
[zameniv znaki ? dlya DECLAREd INSERT-st na spisok peremennyh]
FLUSH cursor-name vytolknut' bufer
D ^^ E DSQL operatory E D^^ E
Opisaniya CREATE, DROP, DATABASE, ALTER, RENAME
Manipulyaciya dannymi DELETE INSERT UPDATE LOAD UNLOAD
Operator SELECT SELECT
Prava dostupa GRANT/REVOKE, LOCK/UNLOCK TABLE, SET LOCK MODE
Operatory tranzakcii i vosstanovleniya BEGIN WORK, COMMIT WORK, ROLLBACK WORK, START DATABASE, ...
Operatory opisaniya dannyh
Operatory oisaniya dannyh Dne otkatyvayutsya E !
CREATE DATABASE db-name [WITH LOG IN "pathname" [MODE ANSI]]
Standart ansi trebuet imya vladel'ca, tranzakciya po umolchaniyu
DROP DATABASE { database-name | char-variable }
DATABASE database-name [EXCLUSIVE] Sdelat' tekushchej
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"]
gde datatype mozhet byt':
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 # Nedopustim dlya vremenyh tablic
{ 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 Uporyadochit' tablicu
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] V sistemnom kataloge
SET EXPLAIN {ON | OFF}
Vyvodit' sistemnye obｮyasneniya v sqlexplain.out
Operatory manipulyacii dannymi
DELETE FROM table-name [WHERE {condition | CURRENT OF cursor-name}]
!* Tol'ko v 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}]
!* Tol'ko v 4GL *!
LOAD FROM "file-name" [DELIMITER "?"] { INSERT INTO table
[(col-list)] | char-variable with INSERT-st }
UNLOAD TO "file" [DELIMITER "?"] SELECT-statement
format fajla po umolchaniyu:
stolbec1|stolbec2| ... ||stolbecn|
...
znachenie|znachenie| ... znachenie|
OUTPUT TO {FILENAME | PIPE program} [WITHOUT HEADINGS] SELECT-st
tol'ko v INFORMIX-SQL
SELECT [ALL | UNIQUE] column-expr [col-lable] [,...]
[INTO spisok peremennyh] !* Tol'ko v 4GL *!
FROM { [OUTER] table-name [tab-alias] |
OUTER (table-expr) } [,...] -proveryat' uslovie
tol'ko dlya etoj (menee
[WHERE condition] nadezhnoj) tablicy
[GROUP BY column-list [HAVING condition] ]
[ORDER BY column-name [DESC],...]
[INTO TEMP table-name]
WHERE conditions:
svyazannye logicheskimi operatorami OR, AND, NOT sravneniya
vyrazh1 Dsravnenie E vyrazh1
gde D sravnenie E = , > , < , >= , <= , <> , !=
column-name IS [NOT] NULL
vyrazh [NOT] BETWEEN vyrazh1 AND vyrazh2
vyrazh [NOT] IN (vyrazh1 , ... [, ...] )
po umolchaniyu "\"
stroka [NOT] LIKE "shablon" [ESCAPE "escape-char"]
specsimvoly shablona D% E D_ E oznachayut "mnogo" "odin"
stroka [NOT] MATCHES "shablon" [ESCAPE "esc-char"]
specsimvoly shablona D* E D? E oznachayut " Dmnogo E" " Dodin E"
D[ EabH D] E D[^ Ed D- Ez D] E " Dodin iz E" " Dni odin iz E"
vyrazh Dsravnenie E {ALL | [ANY | SOME]} (SELECT-operator)
vyrazh [NOT] IN (SELECT-operator) !* Obyknovennyj *!
[NOT] EXISTS (SELECT-operator) !* SQLevskij *!
Operatory zadaniya prav dostupa (ne otkatyvayutsya).
{DBpriv {PUBLIC pravo davat' prava
GRANT | TO | [WITH GRANT OPTION] [AS grantor]
TBpriv [,..] ON table} user-list} ot imeni grantor
{DBpriv
REVOKE | FROM { PUBLIC | user-list }
TBpriv [,..] ON table}
TABLE PRIVILEGES :
DATABASE PRIVILEGES: ALTER DELETE
INDEX INSERT
CONNECT rabotat' SELECT[(cols)]
RESOURCE sozdavat' obｮekty UPDATE [(cols)]
DBA vse ALL [PRIVILEGES]
SET LOCK MODE TO [NOT] WAIT zhdat' [ne zhdat'] osvobozhdeniya
blokir. strok
LOCK TABLE table-name IN {SHARE | EXCLUSIVE} MODE
{Mozhno smotret' | Nichego nel'zya}
UNLOCK TABLE table-name
Operatory tranzakcij, vosstanovleniya dannyh
CREATE DATABASE db-name WITH LOG IN "/pathname" [MODE ANSI]]
START DATABASE db-name WITH LOG IN "/pathname" [MODE ANSI]
startovat' novyj sistemnyj zhurnal (log-fajl)
DATABASE database-name [EXCLUSIVE] Sdelat' tekushchej
ROLLFORWARD DATABASE database-name Nakatit' bazu iz kopii
CLOSE DATABASE vpered po sistemnomu zhurnalu
BEGIN WORK Nachalo tranzakcii Vnimanie!! Vse
. . . izmenennye stroki blokiruyutsya!!
COMMIT WORK Konec tranzakcii
ROLLBACK WORK Otkatit' izmeneniya k predydushchemu COMMIT
CREATE AUDIT FOR table-name IN "pathname"
. . .
RECOVER TABLE table-name Vosstanovit' tablicu
DROP AUDIT FOR table-name
VALIDATE spisok peremennyh LIKE column-list udovletvoryayut li
peremennye dopustimym znacheniyam dlya etih stolbcov (syscolval)?
esli net to status<0
PRIMECHANIE: podcherknutye operatory nel'zya ispol'zovat'
v 4GL, a mozhno tol'ko v INFORMIX-SQL
INFO { TABLES | { COLUMNS | INDEXES | ACCES | PRIVILEGES |
STATUS } FOR table-name }
CHECK TABLE owner.table-name Proverit' indeksy
REPAIR TABLE table-name Remont indeksov
Last-modified: Wed, 09 Sep 1998 09:09:21 GMT