 |
|
ss
Oracle Tips by Burleson |
PL/SQL
and SQL*Plus
At this point you should have your Oracle database
started and loaded with the PUBS schema and it is now time to move to
using PL/SQL. In Oracle, SQL is divided into three basic groups of
commands including queries, Data Definition Language
(DDL) and Data Manipulation Language
(DML).
–
We use the SELECT operator to perform data extractions
– Data
Definition Language is used to
define objects within the database such as creating tables or
indexes.
– Data
Manipulation Language is used to
insert, update and delete data in the database.
Remember, PL/SQL
is a programming language that is
tightly integrated with SQL. It has the ability to retrieve and
manipulate data, but also to execute DDL and other PL/SQL
code.
PL/SQL
runs on the database server, not
on the local client machine. Unlike external programs, PL/SQL takes
advantage of the power and safety of the Oracle database, its security
and protection. By executing on the database server, PL/SQL also
avoids the most time consuming part of interacting with the database,
the passing of result-sets back-and-forth across the network.
The examples in this book are created using SQL*Plus, the
command line interface to the Oracle Database. You may chose to use
one of the PL/SQL integrated development environments such as TOAD, PL/SQL
Developer or Oracle’s new Raptor
. For a new developer we recommend
using SQL*Plus because it is simple and will keep you from confusing
PL/SQL errors and the tool errors. You will find that the first thing
a developer needs to do is understand the Oracle and PL/SQL error
messages
. Remember, a development tool can
make error messages harder to understand.
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
the “sqlplus” command. 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:
The example database is called DEVDB. You 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 tnsnames.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 SID, in my case DEVDB.
If there was someone watching you log on and you
didn’t want them to see the password, do not included 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>
We recommend that you print a copy of the pubs.ppt
slide in the code depot for easy reference. Look at the author
table on the slide. You can also see what makes up the
author table by describing it from the SQL*Plus prompt.
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 statement including an anonymous PL/SQL
block 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, the wildly popular UNIX editor) When you enter a SQL
statement, SQL*Plus will continue to place it into the buffer until it
encounters a semicolon. This tells SQL*Plus to execute the command.
In PL/SQL, each line is terminated in a semicolon.
SQL*Plus waits until you enter a forward slash to send the
buffer to the database for execution. You can re-execute the previous
statement or block 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.
PL/SQL
deals with blocks of code or
scripts. You must use a text editor to create the script and then use
SQL*Plus to execute it. The easiest way to do this is to use the
host command. On Windows, enter “host notepad <filename>” at
the SQL*Plus prompt to open a text file in notepad. Write/edit the
query or PL/SQL block, save and close the file, then execute it with
the “@<filename>” command. On Windows 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. Sadly, Linux/Unix will not scroll through previous
commands with the arrow keys without using a separate utility.
To get a list of SQL*Plus command
type “help index”.
SQL> help
index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER
For more detail use “help <command>”
SQL> help
column
COLUMN
------
Specifies
display attributes for a given column, such as:
- text for the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column or all
columns.
COL[UMN]
[{column | expr} [option ...] ]
where option represents one of the following
clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
It bears repeating that PL/SQL is tightly
coupled to SQL. If you are not comfortable with SQL you will find
PL/SQL very difficult. This book uses Oracle’s SQL functions
throughout the examples. These SQL functions are covered in detail in
the companion book Easy Oracle SQL.
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Get Started
Fast with Working PL/SQL Code Examples
ISBN 0-9759135-7-3
John Garmany
http://www.rampant-books.com/book_2005_1_easy_plsql.htm
|