1.2 SQL
| SQL means something very specific, whereas the term database has broad meaning and interpretation in everyday conversation. This section elaborates on the SQL language as a standard. Topics include SQL implementation, embedded SQL, Direct SQL, and SQL*Plus as an implementation of Direct SQL. 1.2.1 ANSI StandardSQL is a language currently defined in an ANSI standard (the previous section discussed the history of SQL as it passed from IBM to ANSI). If you are writing an Oracle application that uses SQL, then you are coding SQL statements whose syntax complies with ANSI-X3.135-1999, "Database Language SQL." This ANSI standard is available to the public through the ANSI Web store. You can visit the site, http://webstore.ansi.org/ansidocstore/ , which has a search form field. Using the search field, you can find SQL standards with a search on "ANSI X3.135." You can obtain copies of this document in PDF format; the cost, as of this writing, is $18 and it contains about 600 pages. The standard, as with most standards, is defined using the Backus Naur Form syntax ” illustrated next . The following extract, from ANSI-X3.135-1999, "Database Language SQL," is shown here to illustrate the preciseness with which the standard defines the syntax of a CREATE TABLE statement. <table definition> ::= CREATE [ { GLOBAL ) LOCAL } TEMPORARY ] TABLE <table name> <table element list> [ ON COMMIT { DELETE I PRESERVE } ROWS ] <table element list> ::= <left paren> <table element> [ { <comma> <table element> }... ] <right paren> <table element> ::= <column definition> <table constraint definition> So, if you are a database vendor and you sell a SQL database product, then you are telling your customers that your database product is capable of parsing and executing ANSI SQL statements, such as the aforementioned CREATE TABLE statement. There is always an advantage to working with a standard. It means that your knowledge is transferable. An understanding of SQL means that your knowledge of relational database technology can transfer between products such as Oracle and SQL Server. Even within a standard there are differences. SQL is no exception. SQL differs across vendors for two reasons. First, there are "implementation defined" features in the standard; that is, for some SQL features, the specific nature of how a feature is implemented is "implementation defined" ”the standard explicitly states this with the language "implementation defined." This means the vendor has discretion in how the feature is implemented. One example is how the vendor implements the catalog or data dictionary. The standard cannot impose how a vendor stores database metadata. We'll see later in this chapter how Oracle's data dictionary is a set of relational tables from which we can select tremendous amounts of information, such as information describing the tables we create. A second reason why there are differences across databases is due to the fact that vendors do enhance their specific SQL engines with additional capabilities that they feel benefit the application developer. One example is the DECODE SQL function that you will find in Oracle. This is a powerful function that will not necessarily appear in other database products simply because DECODE is not part of the ANSI standard. Another example is the database column type CURRENCY that you find in SQL Server ”not all database products provide a CURRENCY type. These differences are minor compared to the many consistencies across SQL products that are ensured by the ANSI standard. In summary, you will find variations in some SQL statements across database engines ”this is really not a big deal. For example, if you build your expertise in SQL and you build this knowledge in Oracle, then your knowledge will include an understanding of inner and outer joins. If you are ever confronted with the syntax of outer joins from a SQL Server or DB2 application, where this syntax varies, then you can easily make the knowledge transition. The key issue is to recognize that there are differences, because you are likely to become exposed to these differences at some point in your career. When this happens, just remember that products will have subtle differences even under the umbrella of an ANSI standard. 1.2.2 SQL DatabaseThe term database is used often in everyday language, but it is important to remember that not all "databases" are created equal. Nor can they all be called relational databases. The term relational database refers to one based on the SQL standard that is capable of parsing and executing SQL statements. We can be very general in how we use the term database, but the term relational database does convey a database that is based on the SQL standard ”something very specific. In contrast to relational databases, there are nonrelational databases, such as DBMS. DBMS is a database product licensed by Oracle and is not a relational database ”it is a network database. Access to DBMS is through a language similar to SQL, but it is not SQL per ANSI standard. Some databases, such as IDMS, are not relational databases, yet they provide a front end that allows an application to access the data using SQL. Having a SQL front-end capability does not make IDMS a relational database. A relational database is relational when it conforms to the minimum requirements of the ANSI SQL standard. Oracle is a fully SQL-compliant database. An Oracle database consists of many files that are preferably distributed across several disk partitions ”distributing files minimizes IO contention . 1.2.3 SQL ImplementationGiven that Oracle is a relational database and we access Oracle data using SQL, how does one create and manipulate that data? You could write a Visual Basic, C, Perl, or Java program. You could write just about any program in any language you want and embed SQL statements in your program. These statements will insert data, change it, and delete it. Our application programs can potentially execute any valid SQL statement against our relational database. SQL, originally called SEQUEL, designed by IBM, and based on Codd's relational theory, provided data access methods for the System/R project. The primary purpose of SQL today is that we use it in a manner similar to which it was used by System/R; that is, to be embedded within a programming language and to provide data access methods against database tables for a production system. One core difference, from a day-to-day coding perspective, between programs of today and those of System/R is that we are more likely to use a more current programming language, such as Java or C#. Secondly, programs we code today utilize enhancements to the SQL standards ”this includes the key components of this text:
Suppose you just want to do something simple and quick. Let's say you want to create a table with one column and insert one row into that table. For such a simple task, the effort to code and compile a program seems excessive ”and it is. For a simple SQL interface we use tools that address the "direct invocation of SQL." The SQL standard addresses embedded SQL ”and this is what has been discussed up to this point, embedding SQL statements within compiler language programs ”but the SQL standard also addresses another method referred to as Direct SQL. This is where we do the simple and quick stuff. The SQL Standard specifies three approaches to SQL: embedded SQL, modular SQL, and Direct SQL. Embedded and modular SQL are principal techniques for applications development using a compiler language ”they are great for applications development but impractical for simple tasks and for learning the SQL language. If we did not have an implementation of Direct SQL and wanted to see all the rows of a table, we would have to write an application program, embed a SQL cursor, fetch each row into local variables and print those variables out to some default device. A tool that implements Direct SQL allows us to display the same data in about 5 seconds ”mostly limited to how fast we can type or drag-and-drop. Rather than a compiled program, Direct SQL allows us to type a SQL SELECT statement whereby all rows are flushed to our screen for viewing. The ANSI standard description of direct SQL is stated in the following:
Notice that Direct SQL is "implementation defined," hence vendors have wide discretion in how they implement Direct SQL. SQL*Plus is an Oracle implementation of Direct SQL, as is SQL Server's Query Analyzer. These tools differ in user interface; however, the same SQL query statement, executed across different tools, will produce the same result set; that is, the same data and number of rows will be the same. There exists a wide variety of SQL tools that implement direct SQL. These tools allow us to type basic SQL statements and execute them without the additional complexity of a compiler or scripting language. Some are GUI and some provide a basic command line interface. Examples of such tools are SQL*Plus, which is bundled with the Oracle database software; SQL Navigator , which is licensed by Quest Software, SQL Worksheet which is bundled with the Oracle Enterprise Manager (OEM) software; and TOAD, which is also a Quest Software product. There are many other tools as well. There is a conceptual difference between accessing Oracle, or any relational database, through a compiler language such as Java and using a direct query tool like TOAD or SQL*Plus. All tools that implement Direct SQL operate within an interactive framework so you need to keep the following two points in mind. First, when you execute a query with SQL*Plus, or with any direct SQL tool, the results are dumped to your screen. Secondly, when executing the same query within an application, you must develop code to capture the results of your query. This is illustrated with the following two SQL query statements. The first SQL statement is a query that you would execute in SQL*Plus. Upon execution of the statement; the student name is flushed to the screen. 1. SELECT student_name FROM registered_students WHERE student_id = 'A101'; The aforementioned SELECT statement is a valid SQL statement for Direct SQL only. It will not compile as an embedded SQL statement within a compiled program. The SELECT statement embedded in an application program will be slightly different ”the difference is the additional INTO clause, shown next. The INTO clause will reference a program variable into which the student name is to be copied . [View full width]
There are variations on the aforementioned query that use the INTO clause ”we have explicit cursors , cursor loops , and other options ”these topics are covered in Chapter 11. It is helpful to keep in mind that SQL*Plus, like Toad and other direct SQL tools, is primarily an interactive tool ”you do not worry about capturing result data. On the other hand, application programs do capture query result data and when you develop code to "capture data" you have design issues: multiple rows returned exception conditions ”these topics are addressed throughout the text. 1.2.4 SQL*PlusIn addition to being an implementation of Direct SQL, SQL*Plus is a trademarked name for an Oracle executable program that runs as a command line interactive program. As mentioned in Section 1.2.3, there are many SQL tools one can use to execute SQL against a database. We've mentioned SQL*Plus, Toad, SQL Navigator, and SQL Worksheet. In fact, you can use SQL Server SQL Query Analyzer to run your queries against your Oracle database. You have many choices. This text makes use of SQL*Plus, but you'll have no difficulty following along should you use another tool for interacting with Oracle. SQL*Plus primarily serves an end user in a client/server environment. Figure 1-1 illustrates a common configuration that includes SQL*Plus. In this figure, the communication protocol across this network is the Oracle Net8 protocol. The Oracle software that runs on your client sends the SQL statement across the network to the database and it is Oracle software, on the back end, that forwards the data back to the client. If you run SQL*Plus and type the following SQL statement: SELECT student_name FROM registered_students WHERE student_id = 'A101'; Figure 1-1. SQL*Plus in a Client/Server Mode. the SQL statement, exactly as you type it, including the same case, is sent across in a network packet to the Oracle server. The processed result, that being a list of student names (in this case probably just the one name of the student with that particular student ID), is returned to the SQL*Plus client where it is flushed to your screen. The sending and receiving of data is Oracle Net8, which is Oracle communication software and runs over TCP/IP, or other network protocols. SQL*Plus is written to utilize Net8, which utilizes TCP/IP over the network. SELECT, INSERT, UPDATE, and DELETE are the most common SQL statements ”they are the core means of manipulating data. In addition to these core data manipulation statements, the SQL standard includes the specification for statements like: CREATE DATABASE, ALTER DATABASE, CREATE TABLE, CREATE VIEW, CREATE TRIGGER, and many other SQL commands. Any valid SQL statement can be executed from SQL*Plus, including the ALTER DATABASE statement. So, all SQL statements, not just the common INSERT, UPDATE, DELETE, SELECT can be executed from SQL*Plus. There are many advantages to learning SQL*Plus. A few are summarized here.
|
= 'A101'; 
EAN: 2147483647
Pages: 111