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.

Executing Unrecognized SQL Statements

Under "Running SQL Queries," I stated that SQL*Plus may be used to execute any SQL statement. That's sort of true. You do need to have a version of SQL*Plus recent enough to "know" about whatever SQL statement you are attempting to execute. For example, SQL*Plus 8.1.7 does not recognize MERGE:

SQL>

MERGE INTO project dest

SP2-0734: unknown command beginning "merge into..." - rest of line ignored.
 

SQL*Plus looks only at the first word of the statement before returning the error shown in this example. MERGE was introduced in Oracle9 i Database, and no previously existing statements began with that keyword. New ALTER statements don't cause the same problem because ALTER something has been around forever.

If you find that you must execute an unrecognized SQL statement from an older version of SQL*Plus, and if that statement does not return any kind of result set that SQL*Plus would otherwise try to display, you likely can execute the statement from a PL/SQL block. For example:

BEGIN

 MERGE INTO project dest

 USING (SELECT * FROM project) source

 ON (dest.project_id = source.project_id)

 WHEN MATCHED THEN UPDATE

 SET dest.project_name = source.project_name;

END;

/
 

See the Section 2.5 for information on executing PL/SQL from SQL*Plus.


     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net