SQL and PL/SQL
High thoughts must have high language.
Aristophanes: Frogs
This chapter provides an overview of SQL, the Structured Query Language, and PL/SQL, Oracle's procedural extension to SQL. The chapter includes:
For complete information on PL/SQL, see the PL/SQL User's Guide and Reference.
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:
- Data Manipulation Language statements (DML)
- Data Definition Language statements (DDL)
- Transaction Control statements
- Session Control statements
- System Control statements
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:
- Remove rows from tables or views (DELETE).
- See the execution plan for a SQL statement (EXPLAIN PLAN).
- Add new rows of data into a table or view (INSERT).
- Lock a table or view, temporarily limiting other users' access to it (LOCK TABLE).
- Retrieve data from one or more tables and views (SELECT).
- Change column values in existing rows of a table or view (UPDATE).
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:
- 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).
Data Definition Statements (DDL)
DDL statements define, alter the structure of, and drop schema objects. DDL statements allow you to do the following:
- 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).
- Gather statistics about schema objects, validate object structure, and list chained rows within objects (ANALYZE).
- 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 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:
- 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 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:
- Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE).
- Declare a database name and connect to Oracle (DECLARE DATABASE, CONNECT).
- Assign variable names, initialize descriptors, and specify how error and warning conditions are handled (DECLARE STATEMENT, DESCRIBE, WHENEVER).
- Parse and execute SQL statements, and retrieve data from the database (PREPARE, EXECUTE, EXECUTE IMMEDIATE, FETCH).
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:
- checks the statement for syntactic and semantic validity
- determines whether the process issuing the statement has privileges to execute it
- allocates a private SQL area for the statement
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:
- Oracle generates the parsed representation of the statement.
- The user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.
Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement:
- A parse call by the application associates a SQL statement with a private SQL area. Once a statement has been associated with a private SQL area, it can be executed repeatedly without your application making a parse call.
- A parse operation by Oracle allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be executed repeatedly without being reparsed.
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.
PL/SQL
PL/SQL is Oracle's procedural language extension to SQL. PL/SQL allows you to mix SQL statements with procedural constructs. PL/SQL provides the capability to define and execute PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks and stored procedures.
An anonymous block is a PL/SQL block that appears within your application and it is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear.
A stored procedure is a PL/SQL block that Oracle stores in the database and can be called by name from an application. When you create a stored procedure, Oracle parses the procedure and stores its parsed representation in the database. Oracle also allows you to create and store functions, which are similar to procedures, and packages, which are groups of procedures and functions. For information on stored procedures, functions, packages, and database triggers, see Chapter 14, "Procedures and Packages", and Chapter 15, "Database Triggers".
How PL/SQL Executes
The PL/SQL engine is a special component of many Oracle products, including the Oracle Server, that processes PL/SQL. Figure 11 - 1 illustrates the PL/SQL engine contained in Oracle Server.
Figure 11 - 1. The PL/SQL Engine and the Oracle Server
The procedure (or package) is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled procedure (or package) into the shared pool in the System Global Area (SGA), and the PL/SQL and SQL statement executors work together to process the statements within the procedure.
The following Oracle products contain a PL/SQL engine:
- SQL*Menu (Version 5 and later)
- Oracle Reports (Version 2 and later)
- Oracle Graphics (Version 2 and later)
You can call a stored procedure from another PL/SQL block, which can be either an anonymous block or another stored procedure. For example, you can call a stored procedure from Oracle Forms (Version 3 or later).
Also, you can pass anonymous blocks to Oracle from applications developed with these tools:
- Oracle Precompilers (including user exits)
- Oracle Call Interfaces (OCIs)
Language Constructs for PL/SQL
PL/SQL blocks can include the following PL/SQL language constructs:
The following sections give a general description of each construct; see the PL/SQL User's Guide and Reference for more information.
Variables and Constants Variables and constants can be declared within a procedure, function, or package. A variable or constant can be used in a SQL or PL/SQL statement to capture or provide a value when one is needed.
Note: Some interactive tools, such as Server Manager, allow you to define variables in your current session. Variables so declared can be used similarly to variables declared within procedures or packages.
Cursors Cursors can be declared explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle data. Cursors also can be declared implicitly (to support other data manipulation actions) by the PL/SQL engine.
Exceptions PL/SQL allows you to explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. Internal exceptions are caused by illegal operations, such as divide-by-zero, or Oracle errors returned to the PL/SQL code. User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application (for example, debiting an account and leaving a negative balance).
When an exception is raised (signaled), the normal execution of the PL/SQL code stops, and a routine called an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.
While many Oracle products have PL/SQL components, this chapter specifically covers the procedures and packages that can be stored in an Oracle database and processed using the PL/SQL engine of Oracle Server. The PL/SQL capabilities of each Oracle tool are described in the appropriate tool user guide.
Oracle also allows you to create and call stored procedures. If your application calls a stored procedure, the parsed representation of the procedure is retrieved from the database and processed by the PL/SQL engine in Oracle. You can call stored procedures from applications developed using these tools:
- Oracle Precompilers (including user exits)
- Oracle Call Interfaces (OCIs)
You can also call a stored procedure from another PL/SQL block, either an anonymous block or another stored procedure. For information on how to call stored procedures from each type of application, see the manual for the specific application tool, such as the Programmer's Guide to the Oracle Precompilers.
Dynamic SQL in PL/SQL
You can write stored procedures and anonymous PL/SQL blocks using dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime.
This permits you to create procedures that are more general purpose. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.
Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the DBMS_SQL package. This helps solve the problem of not being able to parse data definition language statements directly using PL/SQL. For example, you might now choose to issue a DROP TABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package.
