Release 8.0
A58227-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Concepts Release 8.0 A58227-01 |
|
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:
|
Additional Information:
For complete information on PL/SQL, see the PL/SQL User's Guide and Reference. |
SQL is a very simple, yet powerful, database access language. SQL is a nonprocedural 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, Oracle Enterprise Manager, 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.
|
Additional Information:
See the Oracle8 SQL Reference for detailed information about SQL commands and other parts of SQL (such as operators, functions, and format models). See the Oracle Enterprise Manager Administrator's Guide for information about Oracle Enterprise Manager and Server Manager commands, including their distinction from SQL commands. |
This section includes the following topics:
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 consists partially 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.
A SQL statement can be thought of as a very simple, but powerful, computer program or instruction. 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
Oracle SQL statements are divided into the following categories:
|
Note: Oracle also supports the use of SQL statements in PL/SQL program units; see Chapter 17, "Procedures and Packages" and Chapter 18, "Database Triggers" for more information about this feature. |
DML statements query or manipulate data in existing schema objects. They enable you to
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');
DDL statements define, alter the structure of, and drop schema objects. DDL statements enable you to
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 25, "Controlling Database Access", Chapter 26, "Privileges and Roles", and Chapter 27, "Auditing".
Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to
Session control statements manage the properties of a particular user's session. For example, they enable you to
System control statements change the properties of the Oracle server instance.
The only system control command is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), to kill a session, and to perform other tasks.
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
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.
|
Additional Information:
For information on how to use the FIPS flagger, see the Pro*C/C++ Precompiler Programmer's Guide, Pro*COBOL Precompiler Programmer's Guide, or SQL*Module for Ada Programmer's Guide. |
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.
A cursor is a handle or name for a private SQL area - an area in memory in which a parsed statement and other information for processing the statement are kept.
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.
Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, the database administrator can alter the OPEN_CURSORS initialization parameter.
Some statements (primarily DDL statements) require Oracle to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor may execute several recursive calls. These recursive cursors also use shared SQL areas.
Oracle automatically notices when applications send identical SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared - that is, used for processing subsequent occurrences of that same statement. Therefore, 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.
|
Additional Information:
See the Oracle8 Application Developer's Guide for more information on shared SQL. |
Parsing is one stage 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
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 generates the parsed representation of the statement, and 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.
Both parse calls and parsing can be expensive relative to execution, so it is desirable to perform them as seldom as possible.
This discussion applies also to the parsing of PL/SQL blocks and allocation of PL/SQL areas. (See "PL/SQL" on page 14-16.) 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.
This section introduces the basics of SQL processing. Topics include:
Figure 14-1 outlines the stages commonly used to process and execute a SQL statement. In some cases, Oracle might execute these stages in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how you wrote your code.
For many Oracle tools, several of the stages are performed automatically. Most users need not be concerned with or aware of this level of detail. However, you might find this information useful when writing Oracle applications.
This section provides a simple example of what happens during the execution of a SQL statement, in each stage of DML statement processing.
Assume that you are using a Pro*C program to increase the salary for all employees in a department. Also assume that the program you are using has connected to Oracle and that you are connected to the proper schema to update the EMP table. You might embed the following SQL statement in your program:
EXEC SQL UPDATE emp SET sal = 1.10 * sal WHERE deptno = :dept_number;
DEPT_NUMBER is a program variable containing a value for department number. When the SQL statement is executed, the value of DEPT_NUMBER is used, as provided by the application program.
The following stages are necessary for each type of statement processing:
Optionally, you can include another stage:
Queries (SELECTs) require several additional stages, as shown in Figure 14-1:
See "Query Processing" on page 14-11 for more information.
A program interface call creates a cursor. The cursor is created independent of any SQL statement; it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can either occur implicitly or be explicitly declared.
During parsing, the SQL statement is passed from the user process to Oracle, and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this stage of statement processing.
Parsing is the process of:
Oracle parses a SQL statement only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated and the statement is parsed. (See "Shared SQL" on page 14-7.)
The parse stage includes processing requirements that need to be done only once no matter how many times the statement is executed. Oracle translates each SQL statement only once, reexecuting that parsed statement during subsequent references to the statement.
Although the parsing of a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, some errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can be encountered and reported only during the execution stage.
Queries are different from other types of SQL statements because, if successful, they return data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.
Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries (subqueries) in other SQL statements. For example, each of the following statements requires a query as a part of its execution:
INSERT INTO table SELECT... UPDATE table SET x = y WHERE... DELETE FROM table WHERE... CREATE table AS SELECT...
In particular, queries:
The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user.
In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.
In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.
At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to execute the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for DEPT_NUMBER. The process of obtaining these values is called binding variables.
A program must specify the location (memory address) where the value can be found. End users of applications might be unaware that they are specifying bind variables, because the Oracle utility might simply prompt them for a new value.
Because you specify the location (binding by reference), you need not rebind the variable before re-execution. You can change its value and Oracle looks up the value on each execution, using the memory address.
You must also specify a datatype and length for each value (unless they are implied or defaulted) if Oracle needs to perform datatype conversion.
|
Additional Information:
For more information about specifying a datatype and length for a value, refer to the following publications:
|
Oracle can parallelize queries (SELECTs), INSERTs, UPDATEs, DELETEs, and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so that it can complete faster.
See Chapter 22, "Parallel Execution", for more information about parallel SQL.
At this point, Oracle has all necessary information and resources, so the statement is executed. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction. This ensures data integrity.
For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.
In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.
The final stage of processing a SQL statement is closing the cursor.
The execution of DDL statements differs from the execution of DML statements and queries because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.
Transaction management, session management, and system management SQL statements are processed using the parse and execute stages. To reexecute them, simply perform another execute.
In general, only application designers using the programming interfaces to Oracle are concerned with the types of actions that should be grouped together as one transaction. Transactions must be defined properly so that work is accomplished in logical units and data is kept consistent. A transaction should consist of all of the necessary parts for one logical unit of work, no more and no less.
For example, a transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other nonrelated actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.
In addition to determining which types of actions form a transaction, when you design an application you must also determine when it is useful to use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions. See "Discrete Transaction Management" on page 15-8 for more information.
PL/SQL is Oracle's procedural language extension to SQL. PL/SQL enables you to mix SQL statements with procedural constructs. With PL/SQL, you can 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 17, "Procedures and Packages", and Chapter 18, "Database Triggers".
The PL/SQL engine, which processes PL/SQL program units, is a special component of many Oracle products, including the Oracle server.
Figure 14-2 illustrates the PL/SQL engine contained in 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:
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:
PL/SQL blocks can include the following PL/SQL language constructs:
This section gives a general description of each construct.
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.
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.
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 division 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.
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:
You can also call a stored procedure from another PL/SQL block, either an anonymous block or another stored procedure. See Chapter 17, "Procedures and Packages" for more information.
|
Additional Information:
For information on how to call stored procedures from each type of application, see the documentation for the specific application tool, such as the Pro*C/C++ Precompiler Programmer's Guide or Pro*COBOL Precompiler Programmer's Guide. |
You can write stored procedures and anonymous PL/SQL blocks that include dynamic SQL by using the DBMS_SQL package. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are entered into, or built by, the program at runtime.
This enables 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 DDL 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.
|
Additional Information:
For more information about dynamic SQL, see the Oracle8 Application Developer's Guide. |
A PL/SQL procedure executing on an Oracle server can call an external procedure or function that is written in the C programming language and stored in a shared library. The C routine executes in a separate address space from that of the Oracle server.
|
Additional Information:
See the PL/SQL User's Guide and Reference for detailed information about external procedures. |
