Oracle7 Server Concepts

Contents Index Home Previous Next

Structured Query Language (SQL)

SQL is a very simple, yet powerful, database access language. SQL is a non-procedural language; users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.

IBM Research developed and defined SQL, and ANSI/ISO has refined SQL as the standard language for relational database management systems. The SQL implemented by Oracle Corporation for Oracle is 100% compliant at the Entry Level with the ANSI/ISO 1992 standard SQL data language.

Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional commands. The Oracle tools SQL*Plus and Server Manager allow you to execute any ANSI/ISO standard SQL statement against an Oracle database, as well as additional commands or functions that are available for those tools.

Although some Oracle tools and applications simplify or mask the use of SQL, all database operations are performed using SQL. Any other data access method would circumvent the security built into Oracle and potentially compromise data security and integrity.

See the Oracle7 Server SQL Reference for more information about SQL commands and other parts of SQL (for example, functions) and the Oracle Server Manager User's Guide for more information about Server Manager commands, including their distinction from SQL commands.

This section includes the following topics:

SQL Statements

All operations performed on the information in an Oracle database are executed using SQL statements. A SQL statement is a specific instance of a valid SQL command. A statement partially consists of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

The statement must be the equivalent of a SQL "sentence," as in

SELECT ename, deptno FROM emp;

Only a SQL statement can be executed, whereas a "sentence fragment" such as the following generates an error indicating that more text is required before a SQL statement can execute: 

SELECT ename

A SQL statement can be thought of as a very simple, but powerful, computer program or instruction.

Oracle SQL statements are divided into the following categories:

Each category of SQL statement is briefly described below.

Note: Oracle also supports the use of SQL statements in PL/SQL program units; see Chapter 14, "Procedures and Packages," and Chapter 15, "Database Triggers," for more information about this feature.

Data Manipulation Statements (DML)

DML statements query or manipulate data in existing schema objects. They allow you to do the following:

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

SELECT ename, mgr, comm + sal FROM emp; 
INSERT INTO emp VALUES 	
	(1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); 
DELETE FROM emp WHERE ename IN ('WARD','JONES');

Transaction Control Statements

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

Data Definition Statements (DDL)

DDL statements define, alter the structure of, and drop schema objects. DDL statements allow you to do the following:

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

CREATE TABLE plants 
	(COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); 
DROP TABLE plants; 
GRANT SELECT ON emp TO scott; 
REVOKE DELETE ON emp FROM scott;

For specific information on DDL statements that correspond to database and data access, see Chapter 17, "Database Access", Chapter 18, "Privileges and Roles", and Chapter 19, "Auditing".

Session Control Statements

Session control commands manage the properties of a particular user's session. For example, they allow you to do the following:

System Control Statements

System control commands change the properties of the Oracle Server instance. The only system control command is ALTER SYSTEM. It allows you to change such settings as the minimum number of shared servers, to kill a session, and to 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 allow you to do the following:

Identifying Non-Standard SQL

Oracle provides features beyond the standard SQL "Database Language with Integrity Enhancement". The Federal Information Processing Standard for SQL (FIPS 127-2) requires a method for identifying SQL statements that use vendor-supplied 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. For information on how to use the FIPS flagger, see the Oracle7 Server SQL Reference, the Programmer's Guide to the Oracle Precompilers, or the SQL*Module User's Guide and Reference.

Recursive SQL

When a DDL statement is issued, Oracle implicitly issues recursive SQL statements that modify data dictionary information. Users need not be concerned with the recursive SQL internally performed by Oracle.

Cursors

A cursor is a handle or name for an area in memory in which a parsed statement and other information for processing the statement are kept; such an area is also called a private SQL area. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically for the parsing of SQL statements embedded within the application.

Shared SQL

Oracle automatically notices when applications send identical SQL statements to the database. If two identical statements are issued, the SQL area used to process the first instance of the statement is shared, or used for processing subsequent instances of that same statement.

Therefore, instead of having multiple shared SQL areas for identical SQL statements, only one shared SQL area exists for a unique statement. Since shared SQL areas are shared memory areas, any Oracle process can use a shared SQL area. The sharing of SQL areas reduces memory usage on the database server, thereby increasing system throughput.

In evaluating whether statements are identical, Oracle considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.

For more information on shared SQL, see the Oracle7 Server Application Developer's Guide.

What Is Parsing?

Parsing is one step in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle performs these tasks:

Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and executes the statement immediately. If not, Oracle parses the statement, performing these tasks:

Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement:

Since both parse calls and parsing can be expensive relative to execution, it is desirable to perform them as seldom as possible.

This discussion applies also to the parsing of PL/SQL blocks and the allocation of PL/SQL areas. (See the description of PL/SQL in the next section.) Stored procedures, functions, and packages and triggers are assigned PL/SQL areas. Oracle also assigns each SQL statement within a PL/SQL block a shared and a private SQL area.

Contents Index Home Previous Next