Structured query Language (SQL)

SQL is a an extremely simple, yet powerful, database access language. SQL is a non-procedural language; users describe in SQL what they desire done, and also the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task. IBM study developed and defined SQL, and also ANSI/ISO has actually refined SQL together the typical language for relational database administration systems. The SQL imposed by Corporation because that is 100% compliant in ~ the entry Level v the ANSI/ISO 1992 typical SQL data language. SQL contains many expansions to the ANSI/ISO traditional SQL language, and also tools and also applications provide extr commands. The tools SQL*Plus and also Server Manager permit you come execute any type of ANSI/ISO traditional SQL statement against an database, as well as additional commands or attributes that are obtainable for those tools.Although part tools and also applications leveling or mask the usage of SQL, all database operations room performed using SQL. Any type of other data access technique would circumvent the security constructed into and potentially weaken data security and integrity. Check out the ptcouncil.net7 Server SQL referral for much more information about SQL commands and other components of SQL (for example, functions) and the Server Manager User"s guide for much more information about Server Manager commands, including their distinction from SQL commands. This section includes the adhering to topics:SQL statementIdentifying Non-Standard SQLRecursive SQLCursorsShared SQLParsing

SQL Statements

All operations carry out on the info in an database space executed utilizing SQL statements. A SQL explain is a specific instance of a valid SQL command. A declare partially is composed of SQL scheduled words, which have special meaning in SQL and cannot be used for any type of other purpose. Because that example, SELECT and UPDATE are booked words and cannot be provided as table names. The statement must be the identical of a SQL "sentence," together inSELECT ename, deptno native emp;Only a SQL statement have the right to be executed, conversely, a "sentence fragment" such as the following generates one error describe that much more text is required before a SQL statement can execute: pick ename A SQL statement have the right to be assumed of as a really simple, but powerful, computer system program or instruction. SQL statements are split into the adhering to categories: Data Manipulation Language declaration (DML) Data meaning Language declaration (DDL) Transaction control statements Session manage statements System control statements installed SQL explanation Each classification of SQL declare is briefly defined below. Note: also supports the use of SQL explanation in PL/SQL routine units; watch Chapter 14, "Procedures and Packages," and Chapter 15, "Database Triggers," for an ext information about this feature.Data Manipulation declaration (DML)DML statements questions or manipulate data in present schema objects. They allow you to carry out the following: remove rows native tables or views (DELETE). Check out the execution plan for a SQL declare (EXPLAIN PLAN). Add brand-new rows the data right into a table or watch (INSERT). Lock a table or view, temporary limiting various other users" access to that (LOCK TABLE). Retrieve data from one or much more tables and also views (SELECT). Adjust column worths in present rows of a table or view (UPDATE). DML statements space the most generally used SQL statements. Some instances of DML statements follow: choose ename, mgr, comm + sal from emp; INSERT into emp values (1234, "DAVIS", "SALESMAN", 7698, "14-FEB-1988", 1600, 500, 30); DELETE indigenous emp whereby ename IN ("WARD","JONES"); Transaction manage StatementsTransaction control statements manage the alters made through DML statements and group DML statements right into transactions. They permit you to perform the following: do a transaction"s changes permanent (COMMIT). Undo the transforms in a transaction, either since the transaction began or due to the fact that a savepoint (ROLLBACK). Collection a allude to i beg your pardon you deserve to roll back (SAVEPOINT). Develop properties because that a transaction (SET TRANSACTION). Data an interpretation Statements (DDL)DDL explanation define, alter the structure of, and drop schema objects. DDL statements allow you to do the following: Create, alter, and also drop schema objects and also other database structures, including the database itself and also database individuals (CREATE, ALTER, DROP). Readjust the name of schema objects (RENAME). Delete all the data in schema objects without removing the objects" structure (TRUNCATE). Conference statistics around schema objects, validate thing structure, and list chained rows in ~ objects (ANALYZE). Grant and revoke privileges and roles (GRANT, REVOKE). Turn auditing options on and off (AUDIT, NOAUDIT). Include a comment to the data thesaurus (COMMENT). DDL statements implicitly commit the preceding and also start a brand-new transaction. Some examples of DDL declaration follow: develop TABLE plants (COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); drop TABLE plants; GRANT choose ON emp come scott; REVOKE DELETE top top emp from scott; For particular information ~ above DDL statements that correspond to database and also data access, watch Chapter 17, "Database Access", chapter 18, "Privileges and also Roles", and Chapter 19, "Auditing".Session regulate StatementsSession control commands manage the properties of a details user"s session. For example, they enable you to execute the following: transform the present session by performing a committed function, together as allowing and disabling the SQL trace facility (ALTER SESSION). Permit and disable functions (groups the privileges) because that the existing session (SET ROLE). System control StatementsSystem regulate commands readjust the nature of the Server instance. The only system regulate command is alter SYSTEM. It permits you to change such setups as the minimum variety of shared servers, to kill a session, and also to perform other tasks. Embedded SQL StatementsEmbedded SQL statements incorporate DDL, DML, and transaction manage statements in ~ a procedural language program. Castle are provided with the Precompilers. Installed SQL statements allow you to execute the following: Define, allocate, and also release cursors (DECLARE CURSOR, OPEN, CLOSE). Declare a database name and also connect to (DECLARE DATABASE, CONNECT). Assign variable names, initialize descriptors, and specify exactly how error and warning conditions are handled (DECLARE STATEMENT, DESCRIBE, WHENEVER). Parse and also execute SQL statements, and also retrieve data indigenous the database (PREPARE, EXECUTE, EXECUTE IMMEDIATE, FETCH).

Identifying Non-Standard SQL offers features past the standard SQL "Database Language through Integrity Enhancement". The Federal information Processing standard for SQL (FIPS 127-2) calls for a method for identify SQL declaration that usage vendor-supplied extensions. You can identify or "flag" extensions in interactive SQL, the Precompilers, or SQL*Module by utilizing the FIPS flagger.If girlfriend are came to with the portability of your applications to other implementations that SQL, usage the FIPS flagger. For information on just how to use the FIPS flagger, watch the ptcouncil.net7 Server SQL Reference, the Programmer"s overview 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 thesaurus information. Users need not be concerned with the recursive SQL internally perform by


A cursor is a manage or name for an area in memory in i m sorry a parsed statement and other info for processing the statement room kept; together an area is additionally called a exclusive SQL area. Although many users rely on the automatically cursor taking care of of the utilities, the programmatic interfaces sell application designers an ext control over cursors. In application development, a cursor is a named source available come a program and also can be offered specifically because that the parsing the SQL statements embedded within the application.

Shared SQL instantly notices once applications send the same SQL statements to the database. If two the same statements are issued, the SQL area provided to procedure the very first instance that the explain is shared, or offered for handling subsequent instances that that same statement. Therefore, instead of having actually multiple mutual SQL locations for the same SQL statements, only one mutual SQL area exists for a unique statement. Because shared SQL locations are shared memory areas, any process can use a shared SQL area. The sharing of SQL locations reduces memory intake on the database server, thereby enhancing system throughput. In assessing whether statements are identical, considers SQL declaration issued directly by users and applications and also recursive SQL statements approve internally by a DDL statement. For more information on common SQL, watch the ptcouncil.net7 Server applications Developer"s Guide.

What Is Parsing?

Parsing is one step in the processing of a SQL statement. As soon as an application worries a SQL statement, the application makes a parse speak to to Throughout the parse call, performs this tasks:checks the statement for syntactic and semantic validity determines whether the procedure issuing the statement has privileges come execute the allocates a exclusive SQL area for the statement likewise determines whether there is one existing shared SQL area include the parsed depiction of the statement in the library cache. If so, the user process uses this parsed representation and executes the explain immediately. If not, parses the statement, performing these tasks: generates the parsed representation of the statement. The user procedure allocates a common SQL area because that the explain in the library cache and also stores that is parsed depiction there. Keep in mind the difference between an applications making a parse call for a SQL statement and actually parsing the statement: A parse call by the applications associates a SQL statement through a exclusive SQL area. When a statement has been associated with a exclusive SQL area, it can be executed consistently without your application making a parse call. A parse operation by allocates a shared SQL area for a SQL statement. When a mutual SQL area has actually been allocated because that a statement, it have the right to be executed repetitively without being reparsed. Since both parse calls and also parsing deserve to be expensive relative to execution, that is preferable to execute them as hardly ever as possible. This discussion applies likewise to the parsing the PL/SQL blocks and the allocation that PL/SQL areas. (See the description of PL/SQL in the next section.) save on computer procedures, functions, and also packages and triggers space assigned PL/SQL areas. likewise assigns each SQL statement within a PL/SQL block a shared and also a private SQL area.


PL/SQL is"s procedural language expansion to SQL. PL/SQL enables you come mix SQL statements v procedural constructs. PL/SQL offers the capability to define and execute PL/SQL program units such as procedures, functions, and also packages. PL/SQL program units typically are categorized together anonymous blocks and stored procedures. An anonymous block is a PL/SQL block that shows up within your application and also it is not called or stored in the database. In numerous applications, PL/SQL blocks can appear wherever SQL statements can appear.A save procedure is a PL/SQL block the shop in the database and also can be called by name from an application. As soon as you create a save procedure, parses the procedure and also stores that is parsed representation in the database. also permits you come create and also store functions, i m sorry are comparable to procedures, and packages, i beg your pardon are teams of procedures and also functions. For info on save procedures, functions, packages, and also database triggers, view Chapter 14, "Procedures and also Packages", and Chapter 15, "Database Triggers".

How PL/SQL Executes

The PL/SQL engine is a distinct component of plenty of products, including the Server, that processes PL/SQL.

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

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