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 ptcouncil.net Corporation for ptcouncil.net is 100% compliant at the Entry Level with the ANSI/ISO 1992 standard SQL data language. ptcouncil.net SQL includes many extensions to the ANSI/ISO standard SQL language, and ptcouncil.net tools and applications provide additional commands. The ptcouncil.net tools SQL*Plus and Server Manager allow you to execute any ANSI/ISO standard SQL statement against an ptcouncil.net database, as well as additional commands or functions that are available for those tools.Although some ptcouncil.net 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 ptcouncil.net 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 ptcouncil.net 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 ptcouncil.net 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. ptcouncil.net 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: ptcouncil.net 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 ptcouncil.net 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 ptcouncil.net 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 ptcouncil.net (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

ptcouncil.net 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" ptcouncil.net extensions in interactive SQL, the ptcouncil.net 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 ptcouncil.net 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, ptcouncil.net implicitly issues recursive SQL statements that modify data dictionary information. Users need not be concerned with the recursive SQL internally performed by ptcouncil.net.

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 ptcouncil.net users rely on the automatic cursor handling of the ptcouncil.net 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

ptcouncil.net 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 ptcouncil.net 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, ptcouncil.net 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 ptcouncil.net. During the parse call, ptcouncil.net 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 ptcouncil.net 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, ptcouncil.net parses the statement, performing these tasks: ptcouncil.net 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 ptcouncil.net 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 ptcouncil.net 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. ptcouncil.net also assigns each SQL statement within a PL/SQL block a shared and a private SQL area.

PL/SQL

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