Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

1.1 What Is SQL*Plus?

SQL*Plus is essentially an interactive query tool with some scripting
capabilities. You can enter a SQL statement, such as a SELECT query,
and view the results. You can execute data
definition
language (DDL) statements to
create tables and other objects. DBAs can use SQL*Plus to start up,
shut down, and otherwise administer a database. You can even enter
and execute PL/SQL code.

SQL*Plus is primarily a command-line application, but, despite its
lack of "flash," it is a workhorse
tool used daily by database administrators, developers, and yes, even
end users. As a DBA, it is my tool of choice for managing the
databases under my care. I use it to peek under the hoodto
explore the physical implementation of my database, and to create and
manage users, tables, and tablespaces. In my role as a developer,
SQL*Plus is the first tool that I fire up when I need to develop a
query. In spite of all the fancy, GUI-based SQL generators contained
in products such as PowerBuilder, Clear Access, and Crystal Reports,
I still find it quicker and easier to build up and test a complex
query in SQL*Plus before transferring it to whatever development tool
I am using.

1.1.1 Uses for SQL*Plus

Originally developed simply as a way to
enter queries and see results, SQL*Plus has been enhanced with
scripting and formatting capabilities and can be used for many
different purposes. The basic functionality is simple. With SQL*Plus,
you can do the following:

Issue a SELECT query and view the results.

Insert, update, and delete data from database tables.

Submit PL/SQL blocks to the Oracle server for execution.

Issue DDL statements, such as those used to create, alter, or drop
database objects (e.g., tables, indexes, and users), as well as any
other types of SQL statements that Oracle supports.

Execute SQL*Plus script files.

Write output to a file.

Execute procedures and functions that are stored in a database.

While these operations may not seem significant, they are the
building blocks you can use to perform various useful functions.

Consider the ability to enter a SELECT statement and view the results.
Example 1-1 shows how to do this using SQL*Plus.

Example 1-1. Executing a query in SQL*Plus

SQL> SELECT employee_id, employee_name, employee_billing_rate
2 FROM employee;
EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_BILLING_RATE
----------- ---------------------------------------- ---------------------
101 Marusia Churai 169
102 Mykhailo Hrushevsky 135
104 Pavlo Virsky 99
105 Mykola Leontovych 121
107 Lesia Ukrainka 45
108 Pavlo Chubynsky 220
110 Ivan Mazepa 84
111 Taras Shevchenko 100
112 Igor Sikorsky 70
113 Mykhailo Verbytsky 300 Combine this capability with SQL*Plus''''s formatting
abilities and you can turn these results into a credible-looking
report, such as that shown in Example 1-2, complete
with page titles, page numbers, column titles, and nicely formatted
output.

Example 1-2. A SQL*Plus formatted report

Employee Listing Page 1
Billing
Emp ID Name Rate
---------- ------------------- --------
101 Marusia Churai $169.00
102 Mykhailo Hrushevsky $135.00
104 Pavlo Virsky $99.00
105 Mykola Leontovych $121.00
107 Lesia Ukrainka $45.00
108 Pavlo Chubynsky $220.00
110 Ivan Mazepa $84.00
111 Taras Shevchenko $100.00
112 Igor Sikorsky $70.00
113 Mykhailo Verbytsky $300.00 Another twist on the same theme is to format the output as a list of
comma-separated values, such as that shown in Example 1-3.

Example 1-3. Comma-separated values from SQL*Plus

101,"Marusia Churai",169
102,"Mykhailo Hrushevsky",135
104,"Pavlo Virsky",99
105,"Mykola Leontovych",121
107,"Lesia Ukrainka",45
108,"Pavlo Chubynsky",220
110,"Ivan Mazepa",84
111,"Taras Shevchenko",100
112,"Igor Sikorsky",70
113,"Mykhailo Verbytsky",300 Using the SQL*Plus SPOOL
command, which you''''ll read more about in Chapter 5, you can write this output to a
.csv file easily readable by most, if not all,
spreadsheet programs. In fact, if you are running Microsoft Windows
with Microsoft Office installed, simply double-clicking on a
.csv file will open that file in Microsoft
Excel, where you can further manipulate the data.

Beginning with SQL*Plus in Oracle8 i Database,
you can use the SET MARKUP
HTML command to generate HMTL output, such
as that shown in Example 1-4.

Example 1-4. A SQL*Plus report formatted in HTML

<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=US-ASCII">
<meta name="generator" content="SQL*Plus 10.1.0">
<style type=''''text/css''''> body {font:10pt Arial,Helvetica,
sans-serif; color:black; background:White;}
...
<tr>
<td align="right">
101
</td>
<td>
Marusia Churai
</td>
<td align="right">
$169.00
</td>
</tr>
<tr>
<td align="right">
102
</td>
<td>
Mykhailo Hrushevsky
</td>
...

By writing such HTML output to a file, you can easily generate ad hoc
reports for users to view from a corporate intranet. One DBA whom I
spoke with regularly refreshes the phone list on his departmental
intranet using this mechanism. Figure 1-1 shows the
output in Example 1-4 as you would see it rendered
in a browser.

It''''s a small leap from executing only queries to
executing any other SQL statement. In fact, SQL*Plus will let you
execute any valid SQL statement and
is frequently used during database maintenance tasks. For example,
you can create a new user with the following statement:

CREATE USER sql_dude IDENTIFIED BY some_password;

Of course, it''''s rare that you would issue such a
simple statement, or just one statement, when you add a new user.
Usually, you also want to assign a default tablespace and often a
quota on that tablespace. You may also want to grant the privilege
needed to connect to the database. Whenever you have a task that
requires a sequence of statements to be executed, you can simplify
things by taking advantage of SQL*Plus''''s scripting
capabilities. The statements in Example 1-5, when
placed in a script file, allow you to add a new user with just one
command.

Example 1-5. Script to create a new database user

CREATE USER &&1 IDENTIFIED BY &&2
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA &&3.M ON users;
GRANT CONNECT TO &&1;

The &&1, &&2,
and &&3 in Example 1-5
are SQL*Plus user variables marking the locations at which to insert
parameters that you pass to the script. Assuming that you give the
name create_user.s ql to the
file shown in Example 1-5, and assuming that you are
the DBA, you can issue the following command from SQL*Plus whenever
you need to add a user to your database:

@create_user username password quota Example 1-6 shows how this works, by creating a user
named sql_dude with a password of
yooper and a quota of 10 megabytes.

Example 1-6. Running a script to create a new database user

SQL> @ex1-5 sql_dude yooper 10
old 1: CREATE USER &&1 IDENTIFIED BY &&2
new 1: CREATE USER sql_dude IDENTIFIED BY yooper
old 4: QUOTA &&3.M ON users
new 4: QUOTA 10M ON users
User created.
old 1: GRANT CONNECT TO &&1
new 1: GRANT CONNECT TO sql_dude
Grant succeeded.

The output you see is SQL*Plus showing you the before and after
version of each line containing a SQL*Plus user variable. You will
read more about user variables and the subject of scripting in Chapter 8.

To write complicated scripts, you can take advantage of
Oracle''''s built-in procedural language,
PL/SQL.
Example 1-7 shows a
simple
PL/SQL
block being executed from SQL*Plus.

Example 1-7. "Hello World" written as a PL/SQL block and executed from SQL*Plus

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(''''Hello World!'''');
3 END;
4 /
Hello World!

You''''ve just seen several examples of what can be
done using SQL*Plus to generate simple text reports, perform database
administration tasks, extract data, generate HTML reports, run
automated scripts, and otherwise make your life easier. Subsequent
chapters delve deeply into each of these areas and more.

1.1.2 SQL*Plus''''s Relation to SQL, PL/SQL, and the Oracle Database

SQL*Plus is often used in conjunction with two other products, both
of which have the letters "SQL" in
their names. The first is SQL itself. Without a doubt, the most
common use of SQL*Plus is to submit SQL statements to the database
for execution. The second product is
Oracle''''s
PL/SQL procedural language. Table 1-1 provides a
short summary of each of these three products.

Because these three products all have
"SQL" as part of their names,
people occasionally get confused about the relationships among them
and about which statements get executed where.
SQL*Plus does have its own set
of commands that it recognizes and executes (for example,

SET SERVEROUTPUT ON from Example 1-7), but any SQL statements and PL/SQL blocks are
sent to the database server for execution. Figure 1-2 illustrates this relationship.

Think of SQL*Plus as kind of a middleman, standing between you and
Oracle and helping you to communicate with your database. You type in
a SQL query, SQL*Plus takes it and sends it to the database, the
database returns the results to SQL*Plus, and SQL*Plus displays those
results in a format you can
understand.