Introduction to SQL PL/SQL and Oracle Database History

واضح آرشیو وب فارسی:سایت ریسک: Vahed24-10-2007, 01:04 PMWhat is PL/SQL? It is the procedural (and sometimes object-oriented) programming extension to SQL, provided by Oracle, exclusively for Oracle. If you are familiar with another programming language called Ada, you will find striking similarities in PL/SQL. The reason they are so similar is that PL/SQL grew from Ada, borrowing many of its concepts from it. The PL in PL/SQL stands for procedural language.PL/SQL is a proprietary language not available outside the Oracle Database. It is a third-generation language (3GL) that provides programming constructs similar to other 3GL languages, including variable declarations, loops, error handling, etc. Historically, PL/SQL was procedural only. As discussed in the preceding section, however, PL/SQL can now be considered part of the object-oriented category of languages. Should we change the name to PL/OO/SQL? Structured Query Language (SQL) The SQL in PL/SQL stands for structured query language.We use SQL to SELECT, INSERT, UTE, or DELETE data. We use it to create and maintain objects and users, and to control access rights to our instances. SQL (pronounced as sequelor by its letter abbreviation) is the entrance, or window, to the database. It is a fourth-generation language (4GL) that is intended to be easy to use and quick to learn. The basic SQL syntax is not the creation of Oracle. It actually grew out of the work done by Dr. E.F. Codd and IBM in the early 1970s. The American National Standards Institute (ANSI) recognizes SQL and publishes standards for the language. Oracle supports ANSI-standard SQL but also adds its own twist in its SQL*Plus utility. Through SQL*Plus, Oracle supports additional commands and capabilities that are not part of the standard. SQL*Plus is a utility available in multiple forms: ¦ Command line From the Unix prompt or DOS prompt ¦ GUI SQL*Plus Client, SQL Worksheet, Enterprise Manager ¦ Web Page iSQL*Plus, Enterprise Manager in 10 g With just a client installed, we can configure a network connection to remote databases. Oracle 10 gmakes configuration even easier with a browser-based Enterprise Manager and iSQL*Plus, both configured at install time. Relational Database Overview SQL is the window to the database, but what is the database? A databasein general terms is anything that stores data. Electronic databases can be as simple as a spreadsheet or word processing document. As you might imagine, storing large amounts of data in a spreadsheet or word processing document can become overwhelming very quickly. These onedimensional databases have no efficient way of filtering redundant data, ensuring consistent data entry, or handling information retrieval. Oracle is a relational database management system, or RDBMS. Relational databases store data in tables. Tablesare made up of columns that define the type of data that can be stored in them (character, number, etc.). A table has a minimum of one column.When data is placed in the table, it is stored in rows.This holds true for all relational database vendors (see Figure 1-1). In Oracle, tables are owned by a user, or schema. The schema is a collection of objects, like tables, that the database user owns. It is possible to have two tables in one database that have the same name as long as they are owned by different users. Other vendors do not necessarily follow this approach. SQL Server, for example, applies different terminology. The SQL Server database is more like an Oracle schema, and the SQL Server servermore resembles the Oracle database. The result is the same, however. Objects, such as tables, always have an owner. It is possible to store all of our data in a single table, just like the spreadsheet, but that does not take advantage of Oracle’s relational features. For example, a table containing data about Oracle Press books is incomplete without author information. It is possible that an author has written multiple titles. In a flat-file, or single-table, model, the author is listed multiple times. This redundancy can be avoided by splitting the data into two tables with a column that links related data together. Since we have the AUTHORS table, we don’t have to repeat author information over and over for every title each person writes. We add a single AUTHOR1 column in the BOOKS table and insert the appropriate ID value from the AUTHORS table for each title in the BOOKS table. Using a FOREIGN KEY on the BOOKS.AUTHOR1 column, we can relate the two tables together using SQL. Let’s take a look at an example: CREATE TABLE authors ( id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50) ); CREATE TABLE books ( isbn CHAR(10) PRIMARY KEY, category VARCHAR2(20), title VARCHAR2(100), num_pages NUMBER, price NUMBER, copyright NUMBER(4), author1 NUMBER CONSTRAINT books_author1 REFERENCES authors(id) ); After inserting a few records into the tables, we can perform a SELECT, joining the tables according to their relationship. SELECT b.title, a.first_name, a.last_name FROM authors a, books b WHERE b.author1 = a.id; This joins the two tables together and retrieves data just as you would have seen it had it been stored in a flat file. The differences are less redundancy, fewer opportunities for error, and greater flexibility. To add publisher information, all I would need to do is create a table called PUBLISHER that contains an ID, then add a column to the BOOKS table with a FOREIGN KEY pointing back to the PUBLISHER.ID column. IT Articles 2007 سایت ما را در گوگل محبوب کنید با کلیک روی دکمه ای که در سمت چپ این منو با عنوان +1 قرار داده شده شما به این سایت مهر تأیید میزنید و به دوستانتان در صفحه جستجوی گوگل دیدن این سایت را پیشنهاد میکنید که این امر خود باعث افزایش رتبه سایت در گوگل میشود

این صفحه را در گوگل محبوب کنید

[ارسال شده از: سایت ریسک]

[مشاهده در: www.ri3k.eu]

[تعداد بازديد از اين مطلب: 338]