Data Manipulation Language Statements

Data Definition Language Statements

Transaction Control Statements

Session Control Statements

System Control Statements

Embedded SQL Statements

Data Manipulation Language Statements

Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:

• Retrieve data from one or more tables or views (SELECT); fetches can be scrollable (see "Scrollable Cursors")
• Add new rows of data into a table or view (INSERT)
• Change column values in existing rows of a table or view (UPDATE)
• Update or insert rows conditionally into a table or view (MERGE)
• Remove rows from tables or views (DELETE)
• See the execution plan for a SQL statement (EXPLAIN PLAN)
• Lock a table or view, temporarily limiting other users' access (LOCK TABLE)

DML statements are the most frequently used SQL statements. Some examples of DML statements are:

SELECT last_name, manager_id, commission_pct + salary FROM employees; 

INSERT INTO employees VALUES 
    (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); 

DELETE FROM employees WHERE last_name IN ('WARD','JONES'); 

Data Definition Language Statements

Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to:

• Create, alter, and drop schema objects and other database structures, including the database itself and database users (CREATE, ALTER, DROP)
• Change the names of schema objects (RENAME)
• Delete all the data in schema objects without removing the objects' structure (TRUNCATE)
• Grant and revoke privileges and roles (GRANT, REVOKE)
• Turn auditing options on and off (AUDIT, NOAUDIT)
• Add a comment to the data dictionary (COMMENT)

DDL statements implicitly commit the preceding and start a new transaction. Some examples of DDL statements are:

CREATE TABLE plants  
    (COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); 

DROP TABLE plants; 

GRANT SELECT ON employees TO scott; 

REVOKE DELETE ON employees FROM scott; 

Transaction Control Statements

Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to:

• Make a transaction's changes permanent (COMMIT)
• Undo the changes in a transaction, either since the transaction started or since a savepoint (ROLLBACK)
• Set a point to which you can roll back (SAVEPOINT)
• Establish properties for a transaction (SET TRANSACTION)

Session Control Statements

Session control statements manage the properties of a particular user's session. For example, they enable you to:

• Alter the current session by performing a specialized function, such as enabling and disabling the SQL trace facility (ALTER SESSION)
• Enable and disable roles (groups of privileges) for the current session (SET ROLE)

System Control Statements

System control statements change the properties of the Oracle server instance. The only system control statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), kill a session, and perform other tasks.

Embedded SQL Statements

Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statements enable you to:

• Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE)
• Specify a database and connect to Oracle (DECLARE DATABASE, CONNECT)
• Assign variable names (DECLARE STATEMENT)
• Initialize descriptors (DESCRIBE)
• Specify how error and warning conditions are handled (WHENEVER)
• Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE)
• Retrieve data from the database (FETCH)

Identification of Nonstandard SQL

Oracle provides extensions to the standard SQL database language with integrity enhancement. The Federal Information Processing Standard for SQL (FIPS 127-2) requires vendors to supply a method for identifying SQL statements that use such extensions. You can identify or flag Oracle extensions in interactive SQL, the Oracle precompilers, or SQL*Module by using the FIPS flagger.

If you are concerned with the portability of your applications to other implementations of SQL, use the FIPS flagger.