oracle 使用sqlplus

发布时间：2020-12-12 16:19:38 所属栏目：百科来源：网络整理

导读：AUsing SQL Command Line This section provides an introduction to SQL Command Line (SQL*Plus),an interactive and batch command-line query tool that is installed with Oracle Database Express Edition. This section contains the following topic

AUsing SQL Command Line

This section provides an introduction to SQL Command Line (SQL*Plus),an interactive and batch command-line query tool that is installed with Oracle Database Express Edition.

This section contains the following topics:

Overview of SQL Command Line
Using SQL Command Line

For information about running SQL language statements,seeChapter 3,"Using SQL".

Overview of SQL Command Line

SQL Command Line (SQL*Plus) is a command-line tool for accessing Oracle Database XE. It enables you to enter and run SQL,PL/SQL,and SQL*Plus commands and statements to:

Query,insert,and update data
Execute PL/SQL procedures
Examine table and object definitions
Develop and run batch scripts
Perform database administration

You can use SQL Command Line to generate reports interactively,to generate reports as batch processes,and to write the results to a text file,to a screen,or to an HTML file for browsing on the Internet.

Using SQL Command Line

This section describes SQL Command Line (SQL*Plus),a command-line utility to run SQL and PL/SQL.

This contains the following topics:

Starting and Exiting SQL Command Line
Displaying Help With SQL Command Line
Entering and Executing SQL Statements and Commands
SQL Command Line DESCRIBE Command
SQL Command Line SET Commands
Running Scripts From SQL Command Line
Spooling From SQL Command Line
Using Variables With SQL Command Line

Note:

Before starting SQL Command Line,make sure that the necessary environmental variables have been set up properly. SeeOracle Database Express Edition 2 Day DBAfor information about setting environmental variables for SQL Command Line.

Starting and Exiting SQL Command Line

To start SQL Command Line 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 in the local database. For example,enterHRfor the username andmy_hr_passwordfor the password when prompted.

You can also include the username and password when you start SQL Command Line. For example:

sqlplus hr/my_hr_password

If you want to connect to a database running on a remote system,you need to include a connect string when starting SQL Command Line. For example:

sqlplus hr/my_hr_password@host_computer_name

After you have started SQL Command Line,theSQL>prompt displays as follows:

SQL>

At theSQL>prompt,you can enter SQL statements.

When you want to exit SQL Command Line,enterEXITat the SQL prompt,as follows:

SQL> EXIT

Displaying Help With SQL Command Line

To display a list of Help topics for SQL Command Line,enterHELPINDEXat the SQL prompt as follows:

SQL> HELP INDEX

From the list of SQL Command Line Help topics,you can display Help about an individual topic by enteringHELPwith a topic name. For example,the following displays Help about the SQL Command LineCOLUMNcommand,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,enter 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,enter the first line and press the Enter key. Continue entering lines,and terminate the last line with a semi-colon (;). For example:

SQL>SELECT employee_id,first_name,last_name
2FROM employees
3WHERE employee_id >= 105 AND employee_id <= 110;

The output from the previousSELECTstatement is similar to:

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -----------------------
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
6 rows selected.

Note that a terminating semi-colon (;) is optional with SQL Command Line commands,such asDESCRIBEo rSET,but required with SQL statements.

SQL Command Line DESCRIBE Command 描述表字段情况

SQL Command Line provides theDESCRIBEcommand to display a description of a database object. For example,the following displays the structure of theemployeestable. This description is useful when constructing SQL statements that manipulate theemployeestable.

SQL> DESCRIBE employees
NameNull? Type
---------------------------------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL Command Line SET Commands 数据多的情况修改样式等。

The SQL Command LineSETcommands can be used to specify various SQL Command Line settings,such as the format of the output from SQLSELECTstatements. For example,the followingSETcommands specify the number of lines for each page and the number of characters for each line in the output:

SQL> SET PAGESIZE 200
SQL> SET LINESIZE 140

To enable output from PL/SQL blocks withDBMS_OUTPUT.PUT_LINE,use the following:

SQL> SET SERVEROUTPUT ON

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

SQL> SHOW ALL

For information about the SQL Command LineSERVEROUTPUTsetting to display output from a PL/SQL program,see"Inputting and Outputting Data with PL/SQL".

Running Scripts From SQL Command Line 通过命令行批量运行脚本

You can use a text editor to create SQL Command Line script files that contain SQL*Plus,SQL,and PL/SQL statements. For consistency,use the.sqlextension for the script file name.

A SQL script file is executed with aSTARTor@command. For example,in a Windows environment,you can execute a SQL script as follows:

SQL> @c :my_scriptsmy_sql_script.sql

A SQL script file can be executed in a Linux environment as follows:

SQL> START /home/cjones/my_scripts/my_sql_script.sql

You can useSETECHOONto cause a script to echo each statement that is executed. You can useSETTERMOUTOFFto prevent the script output from displaying on the screen.

When running a script,you need to include the full path name unless the script is located in the directory from which SQL Command Line was started,or the script is located in the default script location specified by theSQLPATHenvironment variable.

Spooling From SQL Command Line 将查询结果输入文本里面

TheSPOOLcommand can be used to direct the output from SQL Command Line 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 enter theSPOOLcommand followed by a file name. For example:

SQL> SPOOLmy_log_file.log

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

SQL> SPOOLmy_log_file.logAPPEND

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

SQL> SPOOL OFF

Using Variables With SQL Command Line

You can create queries that use variables to makeSELECTstatements more flexible. You can define the variable before running a SQL statement,or you specify that the statement prompts 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 contains the following topics:

Prompting for a Variable Value in a Query
Reusing a Variable Value in a Query
Defining a Variable Value for a Query

For information about using bind variables in PL/SQL code,see"Using Bind Variables With PL/SQL".

Prompting for a Variable Value in a Query 可以随时多次输入参数查询

You can use&to identify a variable that you want to define dynamically. InExample A-1,including the&employee_idvariable causes the SQL statement to prompt for a value when the statement is executed. You can then enter a value for theemployee_idthat corresponds to the employee information that you want to display,such as employee ID 125. Note that you can use any name for the variable,such as&my_variable.

Example A-1 Prompting for a Variable Value in SQL Command Line

-- prompt for employee_id in a query,you need to enter a valid ID such as 125
SELECT employee_id,last_name,job_id FROM employees 
  WHERE employee_id = &employee_id;

When you run the previousSELECTstatement,the output is similar to:

Enter value for employee_id:125
...
EMPLOYEE_ID LAST_NAME JOB_ID
----------- ------------------------- ----------
125 Nayer ST_CLERK

Reusing a Variable Value in a Query

You can use&&to identify a variable that you want to define dynamically multiple times,but only want to prompt the user once. InExample A-2,including the&&column_namevariable causes the SQL statement to prompt for a value when the statement is executed. The value that is entered is substituted for all remaining occurrences of&&column_namein the SQL statement.

Example A-2 Reusing a Variable Value in SQL Command Line

-- prompt for a column name,such as job_id,which is then substituted in the
-- remaining identical substitution variables prefixed with &&
SELECT employee_id,&&column_name FROM employees
  ORDER BY &&column_name;

Defining a Variable Value for a Query

InExample A-3,the&job_idvariable is defined before running the SQL statement with theDEFINEcommand,and the defined value is substituted for the variable when the statement is executed. Because the variable has already been defined,you are not prompted to enter a value.

Example A-3 Defining a Variable for a Query in SQL Command Line

-- define a variable value for a query as follows
DEFINE job_id = "ST_CLERK"
-- run a query using the defined value for job_id (ST_CLERK)
SELECT employee_id,last_name FROM employees WHERE job_id = '&job_id';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!