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 Corporation for is 100% compliant at the Entry Level with the ANSI/ISO 1992 standard SQL data language. SQL includes many extensions to the ANSI/ISO standard SQL language, and tools and applications provide additional commands. The tools SQL*Plus and Server Manager allow you to execute any ANSI/ISO standard SQL statement against an database, as well as additional commands or functions that are available for those tools.Although some 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 and potentially compromise data security and integrity. See the ptcouncil.net7 Server SQL Reference for more information about SQL commands and other parts of SQL (for example, functions) and the 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 statementIdentifying Non-Standard SQLRecursive SQLCursorsShared SQLParsing

SQL Statements

All operations performed on the information in an 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 inSELECT 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. 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 Embedded SQL statements Each category of SQL statement is briefly described below. Note: 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 StatementsTransaction 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 StatementsSession 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 StatementsSystem control commands change the properties of the 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 StatementsEmbedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the 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 (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 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" extensions in interactive SQL, the 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 ptcouncil.net7 Server SQL Reference, the Programmer"s Guide to the Precompilers, or the SQL*Module User"s Guide and Reference.

You are watching: A query language is a procedural language.

Recursive SQL

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


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 users rely on the automatic cursor handling of the 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 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 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, 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 ptcouncil.net7 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 During the parse call, 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 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, parses the statement, performing these tasks: 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 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 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. also assigns each SQL statement within a PL/SQL block a shared and a private SQL area.


PL/SQL is"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 stores in the database and can be called by name from an application. When you create a stored procedure, parses the procedure and stores its parsed representation in the database. 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 products, including the Server, that processes PL/SQL.

See more: How Are Algae Different From Plants ? How Are Algae Different From Plants

Figure 11 - 1 illustrates the PL/SQL engine contained in Server.