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 .

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.