SQL*Plus常用命令

Starting and Exiting SQL*Plus

To start SQL*Plus from the operating system command prompt, enter the following:

sqlplus

When prompted, enter the username and password of the user account (schema) that you want to access. For example, enter HR for the username and your_hr_password when prompted.

After you have started SQL*Plus, the SQL> prompt displays for you to type in SQL

statements, as follows:

SQL>

When you want to quit or exit SQL*Plus, type EXIT or QUIT at the SQL prompt, as

follows:

SQL> EXIT

Displaying Help With SQL*Plus

To display a list of help topics for SQL*Plus commands enter HELP INDEX at the SQL prompt as follows:

SQL> HELP INDEX

From the list of SQL*Plus help topics, you can display help on an individual topic by entering HELP with a topic name. For example the following displays help on the SQL*Plus COLUMN command, which enables you to format column output:

SQL> HELP COLUMN

Entering and Executing SQL Statements and Commands

To enter and execute SQL statements or commands, type in the statement or command at the SQL prompt. At the end of a SQL statement, put a semi-colon (;) and then press the Enter key to execute the statement. For example:

SQL> SELECT * FROM employees;

If the statement does not fit on one line, type in the first line and press the Enter key.

Continue entering lines, terminating the last line with a semi-colon (;). For example:

SQL> SELECT employee_id, first_name, last_name

2 FROM employees

SQL*Plus DESCRIBE Command

SQL*Plus provides the DESCRIBE to display a description of a database object. For example, the following displays the structure of the employees table. This description is useful when constructing SQL statements that manipulate the employees table.

SQL> DESCRIBE employees

SQL*Plus SET Commands

The SQL*Plus SET commands can be used to specify various SQL*Plus settings, such

as the format of the output from SQL*Plus statements. For example, the following SET

commands specify the number of lines per page display and the number of characters

per line in the output:

SQL> SET PAGESIZE 200

SQL> SET LINESIZE 140

To enable output from PL/SQL blocks with DBMS_OUTPUT

To view all the settings, enter the following at the SQL prompt:

SQL> SHOW ALL

Running Scripts From SQL*Plus

You can use a text editor to create SQL*Plus script files containing SQL*Plus, SQL, and PL/SQL statements. For consistency, use the .sql extension for the script file name. A SQL script file can be executed with a START or @ command as follows:

SQL> @c:\my_scripts\my_sql_script.sql

You can use SET ECHO ON to cause a script to echo each statement that is executed. You

can use SET TERMOUT OFF to prevent the script output from displaying on the screen.

Spooling From SQL*Plus

The SPOOL command can be used to direct the output from SQL*Plus to a disk file, which enables you to save the output for future review.

To start spooling the output to an operating system file, you can enter:

SQL> SPOOL my_log_file.log

If you want to append the output to an existing file:

SQL> SPOOL my_log_file.log APPEND

To stop spooling and close a file, enter the following:

SQL> SPOOL OFF

Using Variables With SQL*Plus

You can write queries that use variables to make your SELECT statements more

flexible. You can define the variable prior to running a SQL statement or you can

prompt for a variable value at the time that the SQL statement is run.

When using a variable in a SQL statement, the variable name must be begin with an

ampersand (&).

This section includes the following topics:

■ Prompting for a Variable on page A-4

■ Defining a Variable Value for a Query on page A-4

Prompting for a Variable

In Example A–1, including the variable &emp_id causes the SQL statement to prompt

for a value when the statement is executed. You can then enter the employee_id that

you want to display, such as employee Id 125.

Example A–1 Defining a Variable

-- prompt for employee_id in a query, such as 125

SELECT employee_id, last_name, job_id FROM employees WHERE employee_id = &emp_id;

Defining a Variable Value for a Query

In Example A–2, the variable &jobid is defined prior to running the SQL statement

and the defined value is substituted for the variable when the statement is executed.

Example A–2 Prompting for a Variable

-- define a variable value for a query as follows

DEFINE jobid = "ST_CLERK"

-- run a query using the defined value

SELECT employee_id, last_name FROM employees WHERE job_id = '&jobid';