ALTER DOMAIN domain-name
... ADD constraint-definition
... DROP CONSTRAINT constraint-name { RESTRICT | CASCADE }
... DROP DEFAULT { RESTRICT | CASCADE }
... SET default-clause
ALTER SCHEMA schema-name
... DEFAULT CHARACTER SET character-set-name
ALTER SEQUENCE sequence-name
... AS data-type
... { CYCLE | NO CYCLE }
... INCREMENT BY n
... MAXVALUE n
... MINVALUE n
... RESTART WITH n
ALTER TABLE table-name
... ADD [COLUMN] column-name column-definition
... ADD constraint-definition
... ALTER [COLUMN] column-name SET
... ALTER [COLUMN] column-name DROP
... ALTER [COLUMN] column-name RESTART
... DROP COLUMN column-name
... DROP CONSTRAINT constraint-name
... DROP FOREIGN KEY foreign-key-name
... DROP PRIMARY KEY primary-key-name

BEGIN

Start a compound statement. Example: BEGIN INSERT INTO t VALUES (5); END;

BEGIN
... [ATOMIC] any series of SQL/PSM statements

CALL

Call an SQL-invoked routine. Example: CALL ROUTINE_X;

CALL
... routine-name

CLOSE

Close a cursor. This is only legal in a compound statement. Example: BEGIN ... OPEN ... CLOSE ... END

CLOSE
... cursor-name

COMMIT

Make permanent any changes to the database since the last commit. Example: COMMIT

COMMIT [WORK]

CONNECT

Tell the DBMS what cluster you want to access, who you are, and what name you'd like your new connection to have. Example: CONNECT TO 'OCELOT' AS 'CONNECTION_1' USER 'OCELOT';

CONNECT TO dsn [AS connection name] [USER user name]

CONNECT STRINGS. The string used for dsn or connection name or user name may contain additional or alternative information, with the form 'tag=value'. The possible tags are: 'dsn=' 'uid=' 'pwd=' 'net=' 'well_known_port=' 'encrypted=' 'remoteipaddress=' 'homeipaddress=' 'disable_autodial=' 'autocommit=' 'max_rows=' 'login_timeout=' 'txn_isolation=' 'quirks=' 'query_timeout=' 'current_catalog=' 'current_schema=' 'server=' 'database=' 'use_big_heaps=' 'max_dbcs=' 'max_stmts=' 'max_stmt_heaps=' 'resources_base_address='. Example: CONNECT TO 'ocelot;uid=peter;WELL_KNOWN_PORT=9999'

CREATE

CREATE. Make an object. For short list of common objects click here. Example: CREATE TABLE TABLE_1 (COLUMN_1 CHARACTER VARYING (20000));

CREATE ASSERTION assertion-name
CREATE CHARACTER SET character-set-name
... [AS] GET source [COLLATE name] [COLLATION FROM 'language']
CREATE COLLATION collation-name
... FOR character-set-spec FROM source [NO PAD | PAD SPACE ]
CREATE DOMAIN domain-name
... [AS] data type
CREATE FUNCTION function-name
... function definition
CREATE PROCEDURE procedure-name
... procedure definition
CREATE SCHEMA schema-name
... AUTHORIZATION auth-id
... DEFAULT CHARACTER SET character-set-name
... any series of CREATE or GRANT statements
CREATE SEQUENCE sequence-name
... AS data-type
... { CYCLE | NO CYCLE }
... INCREMENT BY n
... MAXVALUE n
... MINVALUE n
... RESTART WITH n
CREATE TABLE table-name
... (column definition, ...)
CREATE {LOCAL|GLOBAL} TEMPORARY TABLE table-name
... {column definition, ...} [ON COMMIT {PRESERVE|DELETE} ROWS]
CREATE ROLE role-name
... role definition
CREATE TRANSLATION translation-name
... FOR character-set TO character-set
CREATE TRIGGER trigger-name
... [BEFORE|AFTER] INSERT|UPDATE|DELETE ON table [FOR EACH ROW] routine body
CREATE TYPE type-name
... type definition
CREATE VIEW view-name
... view definition

DECLARE

Name and allocate space for a variable. Legal only in SQL/PSM. Example: BEGIN DECLARE v INT; SET v = 5; INSERT INTO t VALUES (v); END;

DECLARE variable-name data-type

DELETE

Get rid of rows in a table. Example: DELETE FROM TABLE_1 WHERE COLUMN_1 = 'X';

DELETE
... FROM table-name [WHERE clause]

DISCONNECT

The opposite of CONNECT. Like logging off. Example: DISCONNECT ALL;

DISCONNECT
... ALL
... connection name

DROP

The opposite of CREATE. Get rid of the definition. Example: DROP COLLATION C RESTRICT;

DROP ASSERTION assertion-name
DROP CHARACTER SET character-set-name
DROP COLLATION collation-name
DROP DOMAIN domain-name
DROP ROLE role-name
DROP SCHEMA schema-name
DROP SEQUENCE sequence-name
DROP TABLE table-name
DROP TRIGGER trigger-name
DROP VIEW view-name

END

Mark the termination of a compound statement that started with BEGIN. Example: BEGIN INSERT INTO T VALUES (5); END

FETCH

FETCH [NEXT] FROM cursor-name
... INTO variable,...

GRANT

Give privileges to users so they can access or change things. Example: GRANT USAGE ON TRANSLATION TRANSLATION_1 TO SAM;

GRANT USAGE ON CHARACTER SET character-set-name TO user-list
GRANT USAGE ON COLLATION collation-name TO user-list
GRANT EXECUTE ON SPECIFIC ROUTINE routine-name TO user-list
GRANT USAGE ON TRANSLATION translation-name TO user-list
GRANT DELETE INSERT|ALL PRIVILEGES|SELECT USAGE ON table-name TO user-list
GRANT DELETE UPDATE ON table-name [column list] TO user-list

INSERT

Put rows in a table. This is what you need to add new data. Example:INSERT INTO TABLE_1 VALUES ('X',55.5,1E+7);

INSERT INTO table-name
... VALUES (list of values)
... SELECT statement

IF

Conditional execution. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=9; ww: WHILE v1=9 DO IF v1=10 THEN ITERATE ww; ELSE LEAVE ww; END IF; END WHILE ww; END;

IF condition THEN statement ELSE statement END IF

ITERATE

Go back to loop start. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=9; ww: WHILE v1=9 DO IF v1=10 THEN ITERATE ww; ELSE LEAVE ww; END IF; END WHILE ww; END;

ITERATE label

LEAVE

Break out of a loop. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=9; ww: WHILE v1=9 DO IF v1=10 THEN ITERATE ww; ELSE LEAVE ww; END IF; END WHILE ww; END;

LEAVE label

LOOP

Loop start. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=5; ww: LOOP INSERT INTO tt VALUES (v1); SET v1=v1+1; IF v1 > 5 THEN LEAVE ww; END IF; END LOOP ww; END;

LOOP statements END LOOP

OPEN

Open a cursor. This is only legal with SQL/PSM. Example: BEGIN ... OPEN ... FETCH ... CLOSE ... END;

OPEN cursor-name

REPEAT

Loop start. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=7; REPEAT INSERT INTO tt VALUES (v1); SET v1=v1+1 UNTIL v1=8; END REPEAT; END;

REPEAT statements UNTIL condition END REPEAT

REVOKE

The opposite of GRANT. Take privileges away from users. Example: REVOKE USAGE ON TRANSLATION_1 FROM SAM;

REVOKE USAGE ON CHARACTER SET character-set-name FROM user-list
REVOKE USAGE ON COLLATION collation-name FROM user-list
REVOKE EXECUTE ON SPECIFIC ROUTINE routine-name FROM user-list
REVOKE USAGE ON TRANSLATION translation-name FROM user-list
REVOKE DELETE INSERT|ALL PRIVILEGES|SELECT USAGE FROM table-name TO user-list
REVOKE DELETE UPDATE ON table-name [column list] FROM user-list

RETURN

Return from a function|procedure. This is only legal with SQL/PSM. Example: CREATE FUNCTION f1 () RETURNS INT RETURN 7;

RETURN
RETURN value

ROLLBACK

Cancel the effects of all activity since the last COMMIT. So if you altered a table, now you can un-alter it. Example: ROLLBACK;

ROLLBACK [WORK]

SAVEPOINT

Establish a savepoint for partial rollbacks. Example: SAVEPOINT AFTER_UPDATE;

SAVEPOINT label

SELECT

Find data. Typically: search for rows that meet certain criteria in certain tables, so that the results can be displayed. Example: SELECT COLUMN_1 FROM TABLE_1 WHERE COLUMN_1 = 'X';

SELECT select-list FROM tables [WHERE conditions] [GROUP BY columns] [HAVING conditions] [ORDER BY columns]

SET

Change connection parameters, such as the user name or the time zone or the default schema. Or -- SQL/PSM only -- set value for variable. Example: SET CONSTRAINTS ALL IMMEDIATE;

SET CATALOG name
SET CONNECTION name
SET CONSTRAINTS { name | ALL } { DEFERRED | IMMEDIATE }
SET FLAGGER { ON | OFF }
SET NAMES name
SET ROLE name
SET SCHEMA name
SET SESSION AUTHORIZATION name
SET TIME ZONE interval
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
SET variable = expression

SIGNAL

Make a diagnostic. This is only legal within SQL/PSM. Example: SIGNAL SQLSTATE = '55555';

SIGNAL SQLSTATE = value

UPDATE

Change information stored in existing rows. Example: UPDATE TABLE_1 SET COLUMN_1 = 'Y' WHERE COLUMN_1 = 'X';

UPDATE table-name SET set-clause [WHERE conditions]

Return to Ocelot home page