 |
|
ss
Oracle Tips by Burleson |
SQL and SQL*Plus
So, we started our database and loaded the PUBS
schema. Now, let’s dive into SQL (pronounced sequel). Relational
databases and SQL were developed in the early 1970s at IBM. SQL
stands for Structured Query Language
. The idea was to define a common method of storing data that would
allow you to retrieve specific information without detailed knowledge
of the underlying database engine. In 1979, Oracle released the first
commercial relational database that used SQL. In 1986, the American
National Standards Institute
(ANSI) began publishing SQL standards. SQL is currently the
standard query method of all major database management systems. In
Oracle, SQL is divided into two basic groups: data definition language
(DDL) or data manipulation language
(DML). DDL is used to define objects within the database
just as creating tables or indexes. DML is used to insert, update and
delete data in the database. Finally, there is data retrieval, which
is the SELECT statement.
The examples in this book are created using
SQL*Plus. SQL*Plus is the command line interface to the Oracle
Database. The first step is to start SQL*Plus and connect to the
database. In Windows, open a terminal window. In Linux/Unix go to
the command line and ensure that the database environment is set. To
start SQL*Plus just enter “sqlplus”. If the program is not found,
make sure the ORACLE_HOME is set in the path.
[oracle@appsvr oracle]$ sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Thu
Jan 20 20:23:44 2005
Copyright (c) 1982, 2004, Oracle. All rights
reserved.
Enter user-name:
My database is called DEVDB. I can start
SQL*Plus and log on in one command like below.
[oracle@appsvr oracle]$ sqlplus pubs/pubs@devdb
SQL*Plus: Release 10.1.0.2.0 - Production on Thu
Jan 20 20:28:11 2005
Copyright (c) 1982, 2004, Oracle. All rights
reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining
options
SQL>
The log on format is:
username/password@database_service_name.
The database service name is the name of the
entry in the TNSNAMES.ora file located in the ORACLE_HOME/
network/admin/tnsnames.ora. You may need to get with your DBA to
setup the TNSNAME.ORA file. If you are running SQL*Plus on the
computer that you installed Oracle on, the installation program
created a TNSNAMES entry that matches the database name, in my case
DEVDB.
If there was someone watching me log on and I
didn’t want them to see my password, I could not include it and
SQL*Plus will ask for it and not echo the password to the screen.
[oracle@appsvr oracle]$ sqlplus pubs@devdb
SQL*Plus: Release 10.1.0.2.0 - Production on Thu
Jan 20 20:29:54 2005
Copyright (c) 1982, 2004, Oracle. All rights
reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining
options
SQL>
Now that we are connected, lets get to the SQL.
I would recommend that you print a copy of the pubs.ppt slide in the
Code Depot for easy reference. Look at the AUTHOR table. You can
also see what makes up the AUTHOR table by describing it.
SQL> desc author
Name
Null? Type
-----------------------------------------
-------- ------------ AUTHOR_KEY
VARCHAR2(11)
AUTHOR_LAST_NAME
VARCHAR2(40)
AUTHOR_FIRST_NAME
VARCHAR2(20)
AUTHOR_PHONE
VARCHAR2(12)
AUTHOR_STREET
VARCHAR2(40)
AUTHOR_CITY
VARCHAR2(20)
AUTHOR_STATE
VARCHAR2(2)
AUTHOR_ZIP
VARCHAR2(5)
AUTHOR_CONTRACT_NBR NUMBER(5)
This command lists the columns and their
definitions.
SQL*Plus
places each command into a
buffer. You can edit the SQL*Plus buffer, but it is more efficient to
create and run scripts (unless you are a vi cowboy) (if you don’t know
what that is don’t worry, you are not one). When you enter a command,
SQL*Plus will continue to place it into the buffer until it encounters
a semicolon. This tells SQL*Plus to execute the command. You can
re-execute the previous command by entering a forward slash (/) or by
entering RUN. To list the current buffer enter “L”. When you enter a
carriage return without a semicolon, SQL*Plus assumes you are still
entering a command and will provide another line. The Windows version
of SQL*Plus also has a command history that you can cycle through
using the Up/Down arrows.
As your queries become more complicated, you will
want to be able to edit your queries. The easiest way to do this is
to use the host command. Enter host notepad <filename> at the SQL
prompt to open a text file in notepad. Write/edit the query, save and
close the file, then execute it with the @<filename>
command. To re-edit the file, hit the up arrow to bring the host
command back, enter and edit the query. In this way, you can quickly
go from editing to execution and back to editing.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
