SQL Statement Classes

The SQL Standard groups the Core SQL statements into seven classes, according to their function. These classes are as follows:

1. SQL-Schema statements -- create, alter, and drop Schemas and Schema Objects, so they may have a persistent effect on Schemas. The SQL-Schema statements are: CREATE SCHEMA, DROP SCHEMA, CREATE DOMAIN, ALTER DOMAIN, DROP DOMAIN, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE VIEW, DROP VIEW, CREATE ASSERTION, DROP ASSERTION, CREATE CHARACTER SET, DROP CHARACTER SET, CREATE COLLATION, DROP COLLATION, CREATE TRANSLATION, DROP TRANSLATION, CREATE TRIGGER, DROP TRIGGER, CREATE TYPE, DROP TYPE, CREATE ORDERING, DROP ORDERING, CREATE TRANSFORM, DROP TRANSFORM, CREATE PROCEDURE, CREATE FUNCTION, CREATE METHOD, DROP SPECIFIC ROUTINE, DROP SPECIFIC FUNCTION, DROP SPECIFIC PROCEDURE, CREATE ROLE, GRANT, REVOKE, and DROP ROLE.
2. SQL-data statements -- perform queries, insert, update, and delete operations, so they may have a persistent effect on SQL-data. The SQL-data statements are: DECLARE TABLE, DECLARE CURSOR, OPEN, CLOSE, FETCH, SELECT, FREE LOCATOR, HOLD LOCATOR, and the SQL-data change statements INSERT, UPDATE, and DELETE.
3. SQL-transaction statements -- set parameters for transactions, as well as starting and ending transactions, so (except for the COMMIT statement) they have no effect that lasts after the SQL-session ends. The SQL-transaction statements are: START TRANSACTION, SET TRANSACTION, SET CONSTRAINTS, COMMIT, ROLLBACK, SAVEPOINT, and RELEASE SAVEPOINT.
4. SQL-control statements -- control the execution of a set of SQL statements and have no effect that lasts after the SQL-session ends. The SQL-control statements are: CAL, and RETURN.
5. SQL-Connection statements -- start and end Connections, and allow an SQL-client to switch from a session with one SQL-server to a session with another, so they have no effect that lasts after the SQL-session ends. The SQL-Connection statements are: CONNECT, SET CONNECTION, and DISCONNECT.
6. SQL-session statements -- set certain default values and other parameters for an SQL-session, so they have no effect that lasts after the SQL-session ends. The SQL-session statements are: SET TIME ZONE, SET ROLE, SET SESSION AUTHORIZATION, and SET SESSION CHARACTERISTICS.
7. SQL-diagnostics statements -- get diagnostics from the diagnostics area and signal exceptions in SQL routines, so they have no effect that lasts after the SQL-session ends. The SQL-diagnostics statement is: GET DIAGNOSTICS.

Transaction-initiating SQL Statements

If there is no current transaction, these SQL statements will begin one: (a) any SQL-Schema statement, (b) the SQL-transaction statements COMMIT and ROLLBACK (if they specify AND CHAIN), (c) the SQL-data statements OPEN, CLOSE, FETCH, SELECT, INSERT, UPDATE, DELETE, FREE LOCATOR, and HOLD LOCATOR or (d) START TRANSACTION. The SQL-control statement RETURN will also begin a transaction if it causes the evaluation of a <subquery> when there is no current transaction. No other SQL statement will begin a transaction.

Which SQL statements Can You Use?

Even if it conforms to the SQL Standard, your DBMS may not support all of the SQL statements described in this book because different SQL statements may be prepared and executed in different ways. Thus, the set of SQL statements supported by a DBMS depends on the binding style it supports. The options are as follows.

• SQL Module Language binding style -- where both static and dynamic SQL statements are prepared when the Module is created and executed when the procedure that contains them is called.
• Embedded SQL Syntax binding style -- where both static and dynamic SQL statements are prepared when the host language program is precompiled and executed when the host language program is run.
• Direct SQL Invocation binding style -- where static SQL statements are effectively prepared immediately prior to execution.

Note:

Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.