SQL-92 具体内容（READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE）

http://www.ocelot.ca/commands.htm

ALTER

Change domain, schema, sequence, or table definition.
Example: ALTER TABLE TABLE_1 DROP COLUMN COLUMN_1 RESTRICT;

• 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

• 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]