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

2.4 Running SQL Queries

Using SQL*Plus , you can execute any SQL
query or
statement that you desire. This includes data manipulation language
(DML) statements such as INSERT, UPDATE, DELETE, MERGE, and SELECT.
This also includes data definition language (DDL) statements such as
CREATE TABLE, CREATE INDEX, CREATE USER, and so on. Essentially, you
can execute any statement listed in the Oracle SQL
Reference manual.

Example 2-1 shows a simple SELECT statement against
the project table.

Example 2-1. A simple SELECT statement

SQL> SELECT * /* All columns */
2 FROM project;
PROJECT_ID PROJECT_NAME PROJECT_BUDGET
---------- ---------------------------------------- --------------
1001 Corporate Web Site 1912000
1002 Enterprise Resource Planning System 9999999
1003 Accounting System Implementation 897000
1004 Data Warehouse Maintenance 294000
1005 VPN Implementation 415000 Look again at the query shown in the example. Notice that the
statement spans more than one line. Notice that it contains an
embedded comment and that it ends with a semicolon. All of these
things are important because they illustrate the following rules for
entering SQL statements:

SQL statements may span multiple lines.

Line breaks may occur anywhere SQL allows whitespace, but blank lines
are not normally allowed.

Comments, delimited by /*...*/, may be embedded anywhere whitespace
is allowed. A comment entered this way may span multiple lines.

Comments denoted by a leading dash (-) may be
added anywhere in a line and mark the remainder of that line as a
comment.

SQL statements must be terminated in one of three ways:

The statement may end with a trailing semicolon.

The statement may end with a forward slash character, but the forward
slash must be on a line by itself and it must be in column one of
that line.

The statement may end with a blank line, in which case it will be
stored in the SQL buffer rather than be executed
immediately.

Pay close attention to the three ways to terminate an SQL statement.
You have to worry about this because statements can span multiple
lines, and when you press ENTER for a new line, SQL*Plus needs some
way of knowing whether you are done with the statement or whether you
just want to continue it on another line. Until you enter a
semicolon, a forward slash, or a blank line, SQL*Plus assumes that
you are continuing your statement from one line to the next.

From Oracle8 i Database onward, you can issue the command SET SQLBLANK-LINES ON to allow blank lines within SELECT statements. This is useful when running scripts originally designed to be run by the now obsolete Server Manager utility.

I usually recommend terminating SQL statements
with semicolons, because I think
that's the simplest and cleanest-looking method. The
SELECT statement in Example 2-1 shows a semicolon at
the end of the line, but if you forget and hit ENTER too quickly, you
can also put it on the next line by itself, as shown in Example 2-2.

Example 2-2. The terminating semicolon on a line by itself

SQL> INSERT INTO project
2 /* All columns */
3 (project_id, project_name, project_budget)
4 VALUES (1006,'Mainframe Upgrade',456789)
5 ;
1 row created.

You can also use the forward slash (/) character to terminate a SQL
statement, but it must be on a line by itself and must be the first
and only character on that line. Example 2-3
demonstrates this usage.

Example 2-3. A statement terminated by a forward slash

SQL> UPDATE project
2 SET project_budget = 1000000
3 WHERE project_id = 1006
4 /
1 row updated.

You will read more about the forward slash character later in this
chapter because it's used to execute the SQL
statement, or PL/SQL block, currently in the SQL buffer.

The final option for terminating an SQL statement is to simply press
Enter on a blank line. This is shown in Example 2-4.
There is a catch to this method, though.

Example 2-4. A statement terminated by a blank line

SQL> DELETE
2 FROM project
3 WHERE project_id = 1006
4
SQL>

Look carefully at Example 2-4. Nothing happened. You typed in the DELETE
statement, pressed Enter on a blank line, got back another
SQL> prompt, but SQL*Plus said nothing about
deleting the row that you just asked to delete. Why not? Because when
you terminate an SQL query with a blank line, SQL*Plus stores that
statement in an internal buffer but does not execute it.
You'll read more about this later in this chapter in
the section Section 2.6. For now, though, if you
haven't entered any other statements after the
DELETE statement shown above, just type a forward slash on a line by
itself and press Enter:

SQL> /
1 row deleted.

The DELETE has been executed and the row deleted. The forward slash
tells SQL*Plus to execute the SQL statement most recently entered.

If you have been following along with SQL*Plus, and entering the
examples while reading this section, you've probably
noticed a couple of things. First, it's a pain when
you make a mistake. Second, it's even worse when
that mistake is on a previous line. If you were using the GUI version
of SQL*Plus, you may have even tried to arrow up to correct a
mistyped line. Well, don't waste your time because
you can't do that. However, SQL*Plus does have some
built-in line-editing capabilities, and it can call the text editor
of your choice. You'll read about these options in
just a bit, after the next section on executing PL/SQL blocks.