The Oracle Database
In this Post we know about following things:
1. Relational databases.
2. The Structured Query Language (SQL), which is used to access a database.
3. SQL*Plus, Oracle’s interactive text-based tool for running SQL statements.
4. SQL Developer, which is a graphical tool for database development.
5. PL/SQL, Oracle’s procedural programming language. PL/SQL allows you to
develop programs that are stored in the database.
1.What Is a Relational Database?
The concept of a relational database was originally developed back in 1970 by Dr. E.F. Codd. He
laid down the theory of relational databases in his seminal paper entitled “A Relational Model of
Data for Large Shared Data Banks,” published in Communications of the ACM (Association for
Computing Machinery), Vol. 13, No. 6, June 1970.
The basic concepts of a relational database are fairly easy to understand. A relational database
is a collection of related information that has been organized into tables. Each table stores data in
rows; the data is arranged into columns. The tables are stored in database schemas, which are
areas where users may store their own tables. A user may grant permissions to other users so they
can access their tables.
Most of us are familiar with data being stored in tables—stock prices and train timetables
are sometimes organized into tables. One example table used in this book records customer
information for an imaginary store; the table stores the customer first names, last names, dates
of birth (dobs), and phone numbers:
first_name. last_name. dob. phone
---------- --------- ----------- ------------
John. Brown. 01-JAN-1965. 800-555-1211
Cynthia. Green. 05-FEB-1968. 800-555-1212
Steve. White. 16-MAR-1971. 800-555-1213
Gail. Black. 800-555-1214
Doreen. Blue. 20-MAY-1970
This table could be stored in a variety of forms:
* A card in a box
* An HTML file on a web page
* A table in a database
An important point to remember is that the information that makes up a database is different
from the system used to access that information. The software used to access a database is known
as a database management system. The Oracle database is one such piece of software; other
examples include SQL Server, DB2, and MySQL. Of course, every database must have some way to get data in and out of it, preferably using
a common language understood by all databases. Database management systems implement a
standard language known as Structured Query Language, or SQL. Among other things, SQL allows
you to retrieve, add, modify, and delete information in a database.
Introducing the Structured Query Language (SQL)
Structured Query Language (SQL) is the standard language designed to access relational
databases. SQL should be pronounced as the letters “S-Q-L.”
NOTE
“S-Q-L” is the correct way to pronounce SQL according to the
American National Standards Institute. However, the single word
“sequel” is frequently used instead.
SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of
SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as
System R, and SQL was born from that project. Later, in 1979, a company then known as Relational
Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL.
SQL is now fully standardized and recognized by the American National Standards Institute.
SQL uses a simple syntax that is easy to learn and use. You’ll see some simple examples of its
use in this Post. There are five types of SQL statements, outlined in the following list:
I. Query statements retrieve rows stored in database tables. You write a query using the
SQL SELECT statement.
II. Data Manipulation Language (DML) statements modify the contents of tables. There are
three DML statements:
* INSERT adds rows to a table.
* UPDATE changes rows.
* DELETE removes rows.
III. Data Definition Language (DDL) statements define the data structures, such as tables,
that make up a database. There are five basic types of DDL statements:
CREATE creates a database structure. For example, CREATE TABLE is used to create
a table; another example is CREATE USER, which is used to create a database user.
ALTER modifies a database structure. For example, ALTER TABLE is used to modify
a table.
DROP removes a database structure. For example, DROP TABLE is used to remove a
table.
RENAME changes the name of a table.
TRUNCATE deletes all the rows from a table
IV. Transaction Control (TC) statements either permanently record any changes made to
rows, or undo those changes. There are three TC statements:
COMMIT permanently records changes made to rows.
ROLLBACK undoes changes made to rows.
SAVEPOINT sets a “save point” to which you can roll back changes.
V. Data Control Language (DCL) statements change the permissions on database structures.
There are two DCL statements:
GRANT gives another user access to your database structures.
REVOKE prevents another user from accessing your database structures.
There are many ways to run SQL statements and get results back from the database, some of
which include programs written using Oracle Forms and Reports. SQL statements may also be
embedded within programs written in other languages, such as Oracle’s Pro*C++, which allows
you to add SQL statements to a C++ program. You can also add SQL statements to a Java program
using JDBC; for more details, see my book Oracle9i JDBC Programming (Oracle Press, 2002).
Oracle also has a tool called SQL*Plus that allows you to enter SQL statements using the
keyboard or to run a script containing SQL statements. SQL*Plus enables you to conduct a
“conversation” with the database; you enter SQL statements and view the results returned by
the database. You’ll be introduced to SQL*Plus next.
Using SQL*Plus
If you’re at all familiar with the Oracle database, chances are that you’re already familiar with
SQL*Plus. If you’re not, don’t worry: you’ll learn how to use SQL*Plus in this book.
In the following sections, you’ll learn how to start SQL*Plus and run a query.
Starting SQL*Plus
If you’re using Windows XP Professional Edition and Oracle Database 11g, you can start SQL*Plus
by clicking start and selecting All Programs | Oracle | Application Development | SQL Plus.
Figure 1-1 shows SQL*Plus running on Windows XP. SQL*Plus asks you for a username.
Figure 1-1 shows the scott user connecting to the database (scott is an example user that is
contained in many Oracle databases; scott has a default password of tiger). The host string
after the @ character tells SQL*Plus where the database is running. If you are running the
database on your own computer, you’ll typically omit the host string (that is, you enter scott/
tiger)—doing this causes SQL*Plus to attempt to connect to a database on the same machine
on which SQL*Plus is running. If the database isn’t running on your machine, you should speak
with your database administrator (DBA) to get the host string. If the scott user doesn’t exist or
is locked, ask your DBA for an alternative user and password (for the examples in the first part
of this Post, you can use any user; you don’t absolutely have to use the scott user).
Starting SQL*Plus from the Command Line
You can also start SQL*Plus from the command line. To do this, you use the sqlplus command.
The full syntax for the sqlplus command is
sqlplus [user_name[/password[@host_string]]]
I. user_name is the name of the database user.
II. password is the password for the database user.
III. host_string is the database you want to connect To.
The following examples show sqlplus commands:
sqlplus scott/tiger
sqlplus scott/tiger@orcl
If you’re using SQL*Plus with a Windows operating system, the Oracle installer automatically
adds the directory for SQL*Plus to your path. If you’re using a non-Windows operating system (for
example, Unix or Linux), either you must be in the same directory as the SQL*Plus program to run
it or, better still, you should add the directory to your path. If you need help with that, talk to your
system administrator.
For security, you can hide the password when connecting to the database. For example, you
can enter
sqlplus scott@orcl
SQL*Plus then prompts you to enter the password. As you type in the password, it is hidden from
prying eyes. This also works when starting SQL*Plus in Windows.
You can also just enter
sqlplus
SQL*Plus then prompts you for the user name and password. You can specify the host string by
adding it to the user name (for example, scott@orcl).
Performing a SELECT Statement Using SQL*Plus
Once you’re logged onto the database using SQL*Plus, go ahead and run the following SELECT
statement (it returns the current date):
SELECT SYSDATE FROM dual;
SYSDATE is a built-in database function that returns the current date, and the dual table is a
table that contains a single row. The dual table is useful when you need the database to evaluate
an expression (e.g., 2 * 15 / 5), or when you want to get the current date.
This illustration shows the results of this SELECT statement in SQL*Plus running on
Windows. As you can see, the query displays the current date from the database. You can edit your last SQL statement inSQL*Plus by entering EDIT. Doing this is usefulwhen you make a mistake or you want to make a change to your SQL statement. On Windows,when you enter EDIT you are taken to theNotepad application; you then use Notepad toedit your SQL statement. When you exit Notepadand save your statement, the new statement ispassed back to SQL*Plus, where you can re-execute it by entering a forward slash (/). On Linux orUnix, the default editor is typically set to vi or emacs.
SQL Developer
You can also enter SQL statements using SQL Developer. SQL Developer uses a very nice
graphical user interface through which you can enter SQL statements, examine database tables,
run scripts, edit and debug PL/SQL code, and much more. SQL Developer can connect to any
Oracle Database, version 9.2.0.1 and higher, and runs on Windows, Linux, and Mac OSX. The
following illustration shows SQL Developer running.
You need to have Java installed on your computer before you can run SQL Developer.
If you’re using Windows XP Professional Edition and Oracle Database 11g, you start SQL
Developer by clicking Start and selecting All Programs | Oracle | Application Development |
SQL Developer. SQL Developer will prompt you to select the Java executable. You then browse
to the location where you have installed it and select the executable. Next, you need to create
a connection by right-clicking Connections and selecting New Connection. Once you’ve created a connection and tested it, you can use it to connect to the database and
run queries, examine database tables, and so on.
Creating the Store Schema
The imaginary store sells items such as books, videos, DVDs, and CDs. The database for the store
will hold information about the customers, employees, products, and sales. The SQL*Plus script
to create the database is named store_schema.sql, which is located in the SQL directory
where you extracted the Zip file for this book. The store_schema.sql script contains the DDL
and DML statements used to create the store schema. You’ll now learn how to run the store_
schema.sql script.
Running the SQL*Plus Script to Create the Store Schema
You perform the following steps to create the store schema:
1. Start SQL*Plus.
2. Log into the database as a user with privileges to create new users, tables, and PL/SQL
packages. I run scripts in my database using the system user; this user has all the
required privileges. You may need to speak with your database administrator about
setting up a user for you with the required privileges (they might also run the store_
schema.sql script for you).
3. Run the store_schema.sql script from within SQL*Plus using the @ command.
The @ command has the following syntax:
@ directory\store_schema.sql
where directory is the directory where your store_schema.sql script is located.
For example, if the script is stored in E:\sql_book\SQL, then you enter
@ E:\sql_book\SQL\store_schema.sql
If you have placed the store_schema.sql script in a directory that contains spaces, then
you must place the directory and script in quotes after the @ command. For example:
@ "E:\Oracle SQL book\sql_book\SQL\store_schema.sql"
If you’re using Unix or Linux and you saved the script in a directory named SQL in the tmp
file system, then you enter
@ /tmp/SQL/store_schema.sql
The first executable line in the store_schema.sql script attempts to drop the store user,
generating an error because the user doesn’t exist yet. Don’t worry about the error: the line is there
so you don’t have to manually drop the store user when recreating the schema.
When the store_schema.sql script has finished running, you’ll be connected as the
store user. If you want to, open the store_schema.sql script using a text editor like Windows
Notepad and examine the statements contained in it. Don’t worry about the details of the statements
contained in the script.
To know more about Oracle Database in next post.I hope you will like it.
In this Post we know about following things:
1. Relational databases.
2. The Structured Query Language (SQL), which is used to access a database.
3. SQL*Plus, Oracle’s interactive text-based tool for running SQL statements.
4. SQL Developer, which is a graphical tool for database development.
5. PL/SQL, Oracle’s procedural programming language. PL/SQL allows you to
develop programs that are stored in the database.
1.What Is a Relational Database?
The concept of a relational database was originally developed back in 1970 by Dr. E.F. Codd. He
laid down the theory of relational databases in his seminal paper entitled “A Relational Model of
Data for Large Shared Data Banks,” published in Communications of the ACM (Association for
Computing Machinery), Vol. 13, No. 6, June 1970.
The basic concepts of a relational database are fairly easy to understand. A relational database
is a collection of related information that has been organized into tables. Each table stores data in
rows; the data is arranged into columns. The tables are stored in database schemas, which are
areas where users may store their own tables. A user may grant permissions to other users so they
can access their tables.
Most of us are familiar with data being stored in tables—stock prices and train timetables
are sometimes organized into tables. One example table used in this book records customer
information for an imaginary store; the table stores the customer first names, last names, dates
of birth (dobs), and phone numbers:
first_name. last_name. dob. phone
---------- --------- ----------- ------------
John. Brown. 01-JAN-1965. 800-555-1211
Cynthia. Green. 05-FEB-1968. 800-555-1212
Steve. White. 16-MAR-1971. 800-555-1213
Gail. Black. 800-555-1214
Doreen. Blue. 20-MAY-1970
This table could be stored in a variety of forms:
* A card in a box
* An HTML file on a web page
* A table in a database
An important point to remember is that the information that makes up a database is different
from the system used to access that information. The software used to access a database is known
as a database management system. The Oracle database is one such piece of software; other
examples include SQL Server, DB2, and MySQL. Of course, every database must have some way to get data in and out of it, preferably using
a common language understood by all databases. Database management systems implement a
standard language known as Structured Query Language, or SQL. Among other things, SQL allows
you to retrieve, add, modify, and delete information in a database.
Introducing the Structured Query Language (SQL)
Structured Query Language (SQL) is the standard language designed to access relational
databases. SQL should be pronounced as the letters “S-Q-L.”
NOTE
“S-Q-L” is the correct way to pronounce SQL according to the
American National Standards Institute. However, the single word
“sequel” is frequently used instead.
SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of
SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as
System R, and SQL was born from that project. Later, in 1979, a company then known as Relational
Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL.
SQL is now fully standardized and recognized by the American National Standards Institute.
SQL uses a simple syntax that is easy to learn and use. You’ll see some simple examples of its
use in this Post. There are five types of SQL statements, outlined in the following list:
I. Query statements retrieve rows stored in database tables. You write a query using the
SQL SELECT statement.
II. Data Manipulation Language (DML) statements modify the contents of tables. There are
three DML statements:
* INSERT adds rows to a table.
* UPDATE changes rows.
* DELETE removes rows.
III. Data Definition Language (DDL) statements define the data structures, such as tables,
that make up a database. There are five basic types of DDL statements:
CREATE creates a database structure. For example, CREATE TABLE is used to create
a table; another example is CREATE USER, which is used to create a database user.
ALTER modifies a database structure. For example, ALTER TABLE is used to modify
a table.
DROP removes a database structure. For example, DROP TABLE is used to remove a
table.
RENAME changes the name of a table.
TRUNCATE deletes all the rows from a table
IV. Transaction Control (TC) statements either permanently record any changes made to
rows, or undo those changes. There are three TC statements:
COMMIT permanently records changes made to rows.
ROLLBACK undoes changes made to rows.
SAVEPOINT sets a “save point” to which you can roll back changes.
V. Data Control Language (DCL) statements change the permissions on database structures.
There are two DCL statements:
GRANT gives another user access to your database structures.
REVOKE prevents another user from accessing your database structures.
There are many ways to run SQL statements and get results back from the database, some of
which include programs written using Oracle Forms and Reports. SQL statements may also be
embedded within programs written in other languages, such as Oracle’s Pro*C++, which allows
you to add SQL statements to a C++ program. You can also add SQL statements to a Java program
using JDBC; for more details, see my book Oracle9i JDBC Programming (Oracle Press, 2002).
Oracle also has a tool called SQL*Plus that allows you to enter SQL statements using the
keyboard or to run a script containing SQL statements. SQL*Plus enables you to conduct a
“conversation” with the database; you enter SQL statements and view the results returned by
the database. You’ll be introduced to SQL*Plus next.
Using SQL*Plus
If you’re at all familiar with the Oracle database, chances are that you’re already familiar with
SQL*Plus. If you’re not, don’t worry: you’ll learn how to use SQL*Plus in this book.
In the following sections, you’ll learn how to start SQL*Plus and run a query.
Starting SQL*Plus
If you’re using Windows XP Professional Edition and Oracle Database 11g, you can start SQL*Plus
by clicking start and selecting All Programs | Oracle | Application Development | SQL Plus.
Figure 1-1 shows SQL*Plus running on Windows XP. SQL*Plus asks you for a username.
Figure 1-1 shows the scott user connecting to the database (scott is an example user that is
contained in many Oracle databases; scott has a default password of tiger). The host string
after the @ character tells SQL*Plus where the database is running. If you are running the
database on your own computer, you’ll typically omit the host string (that is, you enter scott/
tiger)—doing this causes SQL*Plus to attempt to connect to a database on the same machine
on which SQL*Plus is running. If the database isn’t running on your machine, you should speak
with your database administrator (DBA) to get the host string. If the scott user doesn’t exist or
is locked, ask your DBA for an alternative user and password (for the examples in the first part
of this Post, you can use any user; you don’t absolutely have to use the scott user).
Starting SQL*Plus from the Command Line
You can also start SQL*Plus from the command line. To do this, you use the sqlplus command.
The full syntax for the sqlplus command is
sqlplus [user_name[/password[@host_string]]]
I. user_name is the name of the database user.
II. password is the password for the database user.
III. host_string is the database you want to connect To.
The following examples show sqlplus commands:
sqlplus scott/tiger
sqlplus scott/tiger@orcl
If you’re using SQL*Plus with a Windows operating system, the Oracle installer automatically
adds the directory for SQL*Plus to your path. If you’re using a non-Windows operating system (for
example, Unix or Linux), either you must be in the same directory as the SQL*Plus program to run
it or, better still, you should add the directory to your path. If you need help with that, talk to your
system administrator.
For security, you can hide the password when connecting to the database. For example, you
can enter
sqlplus scott@orcl
SQL*Plus then prompts you to enter the password. As you type in the password, it is hidden from
prying eyes. This also works when starting SQL*Plus in Windows.
You can also just enter
sqlplus
SQL*Plus then prompts you for the user name and password. You can specify the host string by
adding it to the user name (for example, scott@orcl).
Performing a SELECT Statement Using SQL*Plus
Once you’re logged onto the database using SQL*Plus, go ahead and run the following SELECT
statement (it returns the current date):
SELECT SYSDATE FROM dual;
SYSDATE is a built-in database function that returns the current date, and the dual table is a
table that contains a single row. The dual table is useful when you need the database to evaluate
an expression (e.g., 2 * 15 / 5), or when you want to get the current date.
This illustration shows the results of this SELECT statement in SQL*Plus running on
Windows. As you can see, the query displays the current date from the database. You can edit your last SQL statement inSQL*Plus by entering EDIT. Doing this is usefulwhen you make a mistake or you want to make a change to your SQL statement. On Windows,when you enter EDIT you are taken to theNotepad application; you then use Notepad toedit your SQL statement. When you exit Notepadand save your statement, the new statement ispassed back to SQL*Plus, where you can re-execute it by entering a forward slash (/). On Linux orUnix, the default editor is typically set to vi or emacs.
SQL Developer
You can also enter SQL statements using SQL Developer. SQL Developer uses a very nice
graphical user interface through which you can enter SQL statements, examine database tables,
run scripts, edit and debug PL/SQL code, and much more. SQL Developer can connect to any
Oracle Database, version 9.2.0.1 and higher, and runs on Windows, Linux, and Mac OSX. The
following illustration shows SQL Developer running.
You need to have Java installed on your computer before you can run SQL Developer.
If you’re using Windows XP Professional Edition and Oracle Database 11g, you start SQL
Developer by clicking Start and selecting All Programs | Oracle | Application Development |
SQL Developer. SQL Developer will prompt you to select the Java executable. You then browse
to the location where you have installed it and select the executable. Next, you need to create
a connection by right-clicking Connections and selecting New Connection. Once you’ve created a connection and tested it, you can use it to connect to the database and
run queries, examine database tables, and so on.
Creating the Store Schema
The imaginary store sells items such as books, videos, DVDs, and CDs. The database for the store
will hold information about the customers, employees, products, and sales. The SQL*Plus script
to create the database is named store_schema.sql, which is located in the SQL directory
where you extracted the Zip file for this book. The store_schema.sql script contains the DDL
and DML statements used to create the store schema. You’ll now learn how to run the store_
schema.sql script.
Running the SQL*Plus Script to Create the Store Schema
You perform the following steps to create the store schema:
1. Start SQL*Plus.
2. Log into the database as a user with privileges to create new users, tables, and PL/SQL
packages. I run scripts in my database using the system user; this user has all the
required privileges. You may need to speak with your database administrator about
setting up a user for you with the required privileges (they might also run the store_
schema.sql script for you).
3. Run the store_schema.sql script from within SQL*Plus using the @ command.
The @ command has the following syntax:
@ directory\store_schema.sql
where directory is the directory where your store_schema.sql script is located.
For example, if the script is stored in E:\sql_book\SQL, then you enter
@ E:\sql_book\SQL\store_schema.sql
If you have placed the store_schema.sql script in a directory that contains spaces, then
you must place the directory and script in quotes after the @ command. For example:
@ "E:\Oracle SQL book\sql_book\SQL\store_schema.sql"
If you’re using Unix or Linux and you saved the script in a directory named SQL in the tmp
file system, then you enter
@ /tmp/SQL/store_schema.sql
The first executable line in the store_schema.sql script attempts to drop the store user,
generating an error because the user doesn’t exist yet. Don’t worry about the error: the line is there
so you don’t have to manually drop the store user when recreating the schema.
When the store_schema.sql script has finished running, you’ll be connected as the
store user. If you want to, open the store_schema.sql script using a text editor like Windows
Notepad and examine the statements contained in it. Don’t worry about the details of the statements
contained in the script.
To know more about Oracle Database in next post.I hope you will like it.
Tags:
Oracle Database