Introduction
Structured Query Language (SQL), pronounced "sequel," is the set of commands that all programs and users must use to access data within the Oracle7 database. Application programs and Oracle7 tools often allow users to access the database without directly using SQL, but these applications in turn must use SQL when executing the user's request. This chapter provides background information on SQL used by most relational database systems. Topics include:
History of SQL
The paper, "A Relational Model of Data for large Shared Data Banks," by Dr. E. F. Codd, was published in June 1970 in the Association of Computer Machinery (ACM) journal, Communications of the ACM. Codd's model is now accepted as the definitive model for relational database management systems (RDBMS). The language, Structured English Query Language(SEQUEL) was developed by IBM Corporation, Inc. to use Codd's model. SEQUEL later became SQL. In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.
SQL Standards
Oracle7 SQL complies with industry accepted standards. Oracle Corporation ensures future compliance with evolving SQL standards by actively involving key personnel in SQL standards committees. Industry accepted committees are the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC), both of which have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of the standards conform to conventions used by the organization, but the technical details are exactly the same.
The latest SQL standard published by ANSI and ISO is often called SQL-92 (and sometimes SQL2). The formal names of the new standard are:
- ANSI X3.135-1992, "Database Language SQL"
- ISO/IEC 9075:1992, "Database Language SQL"
SQL-92 defines three levels of compliance, Entry, Intermediate, and Full. Oracle7, Release 7.2 conforms to Entry level compliance, and has many features that conform to Intermediate or Full level compliance.
Release 7.2 conformance to Entry Level SQL-92 was tested by the National Institute for Standards and Technology (NIST) using the Federal Information Processing Standard (FIPS), FIPS PUB 127-2.
How SQL Works
This section describes many of the reasons for SQL's widespread acceptance by relational database vendors as well as end users. The strengths of SQL benefit all ranges of users including application programmers, database administrators, management, and end users.
Technically speaking, SQL is a data sublanguage. That is to say, the purpose of SQL is to interface to a relational database such as Oracle7, and all SQL statements are instructions to the database. In this it differs from general purposes programming languages like C and Basic. Among the features of SQL are the following:
- it processes sets of data as groups rather than as individual units
- it uses statements that are complex and powerful individually, and that therefore stand alone. The flow-control statements of most programming languages are absent in SQL, although they are provided in Oracle's extension to standard SQL called PL/SQL.
Essentially, SQL lets you work with data at the logical level, only being concerned with the implementation details when you want to manipulate them. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user , to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle7 that determines the fastest means of accessing the specified data. Oracle7 also provides techniques you can use to make the optimizer perform its job better.
SQL provides commands for a variety of tasks including:
- inserting, updating, and deleting rows in a table
- creating, replacing, altering, and dropping objects
- controlling access to the database and its objects
- guaranteeing database consistency and integrity
SQL unifies all of the above tasks in one consistent language.
Common Language for All Relational Databases
Because all major relational database management systems support SQL, you can transfer all skills you have gained with SQL from one database to another. In addition, since all programs written in SQL are portable, they can often be moved from one database to another with very little modification.
Embedded SQL
Embedded SQL refers to the use of standard SQL commands embedded within a procedural programming language. Embedded SQL is a collection of these commands:
- all SQL commands, such as SELECT and INSERT, available with SQL with interactive tools
- flow control commands, such as PREPARE and OPEN, which integrate the standard SQL commands with a procedural programming language
Embedded SQL also includes extensions to some standard SQL commands. Chapter 4, "Commands," presents these commands in both standard form and embedded SQL form.
Embedded SQL is supported by the Oracle precompilers. The Oracle precompilers interpret embedded SQL statements and translate them into statements that can be understood by procedural language compilers.
Each of these Oracle precompilers translates embedded SQL programs into a different procedural language:
- the Pro*C/C++ precompiler
- the Pro*COBOL precompiler
- the Pro*FORTRAN precompiler
- the Pro*Pascal precompiler
For a definition of the Oracle precompilers, see Programmer's Guide to the Oracle Precompilers.
Embedded SQL Terms
The following embedded SQL terms are used throughout this manual:
:host_variable is a language variable declared according to the rules of the procedural language and used in a SQL statement. A host variable can be a predefined type or a user-defined array and can include an associated indicator variable.
You can only use host variables in place of numeric or character expressions. You must precede each host variable by a colon (:) to distinguish it from a schema object name. You cannot use host variables in place of SQL keywords or schema object names.
This manual also uses terms for host variables with specific datatypes, such as :host_integer and :host_string.
cursor
is an identifier for a cursor.
db_name
is an identifier for a non-default database.
db_string
is the database identification string for a SQL*Net connection. For more information about connect strings, see the SQL*Net documentation for your operating system.
statement_name block_name
designates an identifier for a SQL statement or PL/SQL block.
Lexical Conventions
The following lexical conventions for issuing SQL statements apply specifically to Oracle's implementation of SQL, but are generally acceptable in all other SQL implementations.
When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the command. Thus, Oracle7 evaluates the following two statements in the same manner:
SELECT ENAME,SAL*12,MONTHS_BETWEEN(HIREDATE,SYSDATE) FROM EMP
SELECT ENAME,
SAL * 12,
MONTHS_BETWEEN( HIREDATE, SYSDATE )
FROM
EMP
Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names. See the syntax description of 'text' ![[*]](jump.gif)
.
Tools Support
Most Oracle7 tools support all features of Oracle's SQL. However, not all tools support all features. This manual describes the complete functionality of SQL. If the Oracle7 tool that you are using does not support this complete functionality, you can find a discussion of the restrictions in the manual describing the tool, such as PL/SQL User's Guide and Reference.
