1.4 SQL Statements

The SQL language is primarily used to create tables and manipulate table data; however, the language includes statements that perform other functions. SQL statements are used to create database accounts, size and tune for performance, and perform administration tasks . An Oracle database first comes into existence with a SQL statement, the CREATE DATABASE statement. The following describes the categories of SQL statements.

1.4.1 Data Definition Language (DDL)

CREATE TABLE is a DDL statement. DDL statements define our database objects and result in updates to the Oracle data dictionary. They create, modify, and delete objects such as a tables, views, stored procedures, database triggers, database links, and dozens of other objects in the database.

DDL statements can alter existing database objects including tables. Columns and constraints can be added with the ALTER TABLE command. Should we choose to add a column for a student's age we can execute the following SQL statement, which alters the definition of the table in the database data dictionary.

 ALTER TABLE students ADD (age NUMBER(3)); 

1.4.2 Data Manipulation Language (DML)

DML manipulates data with the following statements:

• INSERT

• UPDATE

• DELETE

• SELECT

SELECT statements do not actually manipulate data, but the SELECT statement is often included within the conversational context of "DML."

An Oracle database provides table changes through INSERT, UPDATE, and DELETE to cohabitate with SELECT statements. The concept supported in an Oracle environment is called consistent read image. A user making uncommitted changes to the database through INSERT, UPDATE, and DELETE statements does not block a user who issues a SELECT during that same time frame. Transactions such as an INSERT that are not committed to the database do not interfere with concurrent SELECT statements against the same tables. A SELECT statement is guaranteed a consistent image. The results returned to the user from a SELECT statement are guaranteed to be from the most recent consistent image, which will be the rows as of the most recent COMMIT. A COMMIT statement establishes a new checkpoint by which table data is updated to a consistent point in time.

1.4.3 Transaction Control

Transaction control statements allow you to bundle a group of DML statements under an all-or-nothing domain. That is, "all statements complete successfully," or if one statement fails, then all statements fail as one group . Examples of transaction control statements are: SET TRANSACTION, COMMIT, and ROLLBACK.

1.4.4 Session Control

Session Control statements are temporary and persist for the duration of the user's database connection. A useful SQL tuning practice is to turn SQL trace on for the session. This records session information to a user dump directory for SQL statement analysis using the Oracle tool, TKPROF. The following will turn tracing on/off for the session.

 ALTER SESSION SET SQLTRACE [TRUEFALSE] 

Once SQL Trace is on, the user runs the application. During execution Oracle writes data to a trace file. The trace file can be analyzed using the Oracle utility, TKPROF. The following shows the host command string to parse the trace file. The result after running TKPROF is an output ASCII file showing each SQL statement run with detail tuning information.

 tkprof oracle_trace_file.trc oracle_output_trace_file.prf explain=SCOTT/TIGER@ORA sys=no 

The following is a common alter session command. It alters the default display format for columns with a DATE type to include: day, month, year, hours, minutes, and seconds.

 ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'; 

1.4.5 System Control

System Control statements are largely database administrative commands. The SQL statements are used to open the database, shut down the database, and perform administrative commands such as resize datafiles or switch log files. The following is a system control statement used to set the number of Oracle job queue processes.

 ALTER SYSTEM SET job_queue_processes=4 SCOPE=BOTH;