文档章节

oracle 使用sqlplus

Oscarfff
 Oscarfff
发布于 2016/07/05 10:23
字数 1725
阅读 119
收藏 0

A Using 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:

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

See Also:

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:

Note:

Before starting SQL Command Line, make sure that the necessary environmental variables have been set up properly. See Oracle Database Express Edition 2 Day DBA for 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, enter HR for the username andmy_hr_password for 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, the SQL> prompt displays as follows:

SQL>

At the SQL> prompt, you can enter SQL statements.

When you want to exit SQL Command Line, enter EXIT at the SQL prompt, as follows:

SQL> EXIT

 

Displaying Help With SQL Command Line

To display a list of Help topics for SQL Command Line, enter HELP INDEX at 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 entering HELP with a topic name. For example, the following displays Help about the SQL Command Line 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, 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
  2  FROM employees
  3  WHERE employee_id >= 105 AND employee_id <= 110;

The output from the previous SELECT statement 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 as DESCRIBE o r SET, but required with SQL statements.

 

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

SQL Command Line provides the DESCRIBE command 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
Name                                        Null? 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 Line SET commands can be used to specify various SQL Command Line settings, such as the format of the output from SQL SELECT statements. For example, the following SET commands 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 with DBMS_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 Line SERVEROUTPUT setting to display output from a PL/SQL program, see "Inputting and Outputting Data with PL/SQL".

See Also:

SQL*Plus User's Guide and Reference for information about setting up the SQL Command Line environment with a login file

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 .sql extension for the script file name.

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

SQL> @c :\my_scripts\my_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 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.

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 the SQLPATH environment variable.

See Also:

 

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

The SPOOL command 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 the SPOOL command followed by a file name. For example:

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 Command Line

You can create queries that use variables to make SELECT statements 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:

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. In Example A-1, including the &employee_id variable causes the SQL statement to prompt for a value when the statement is executed. You can then enter a value for the employee_id that 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 previous SELECT statement, 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. In Example 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, last_name, &&column_name FROM employees
  ORDER BY &&column_name;

 

Defining a Variable Value for a Query

In Example A-3, the &job_id variable is defined before running the SQL statement with the DEFINE command, 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';

本文转载自:https://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/xedev_sqlplus.htm

共有 人打赏支持
Oscarfff
粉丝 74
博文 816
码字总数 97116
作品 0
崇明
后端工程师
私信 提问
加载中

评论(3)

6组源声
6组源声
嗯哼
6组源声
6组源声
Magic_yuan
Magic_yuan
fffffff
设置sqlplus访问远程oracle数据库的方法

  如果要连接远程数据库,传统的一定可行的方法是在本地装一个oracle,然后使用“Network Configuration Assistant”配置,之后用PL/SQL Dev连接oracle官网上提供了最小访问包,就是如下3...

弱弱小男子
2013/08/30
0
0
Linux中SQLplus上下键不能显示历史命令的问题

在Linux的sqlplus中运行SQL语句之后,想用上下键把历史命令找出来,发现不支持。 按照下面的步骤可以设置 1,从http://download.csdn.net/user/kkdelta下载 rlwrap 2,安装rlwrap 3,使用rlw...

晨曦之光
2012/04/25
134
0
linux重启oracle 各种方法

在linux下重启oracle数据库及监听器: 方法1: 用root以ssh登录到linux,打开终端输入以下命令: cd $ORACLE_HOME #进入到oracle的安装目录 dbstart #重启服务器 lsnrctl start #重启监听器 ...

mrliuze
2015/06/25
0
0
Linux下安装Oracle 12c数据库

(1)Linux下安装Oracle 安装前注意:1、确保/etc/hosts中有系统主机名的记录 2、Linux系统安装图形化界面 3、OFA(最佳灵活体系结构):/字母数字/标准名字/用户名,如/u01/app/oracle 步骤...

冰原2010
2018/07/02
0
0
Oracle 11g 安装札记

Oracle数据库一直没有弄清楚过,今天整理了一些安装过程,记录下来。 1.安装操作系统,可以使用CentOS或者Oracle Linux. 2.依赖包解决。 3.执行runInstaller。安装过程中我选择的是只安装数据...

eatapple
2014/02/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

类加载机制过程

1.加载。 将代码转换成字节流加载进内存。加载完之后创建一个Class对象,这个对象是访问数据的入口。 2.验证。 JVM规范验证和代码逻辑验证。 3.准备 内存分配和初始化。对static修饰的类变量...

无精疯
22分钟前
1
0
next.js 提示 chunk styles [mini-css-extract-plugin]

会出现css 导入警告 导入两个插件 并在next.config.js 配置 yarn add webpack-filter-warnings-pluginyarn add mini-css-extract-plugin const FilterWarningsPlugin = require('webpack-......

一箭落旄头
29分钟前
1
0
AWS的自动部署codeploy 应用程序规范文件

codedeploy应用程序的规范文件 ECS平台上的应用规范文件: AppSpec file也可以是 YAML 或 JSON 格式的,可以直接写入控制台内的编辑器内。 AppSpec file用于指定: 用于将流量定向到新任务集...

守护-创造
37分钟前
1
0
Confluence 6 超过当前许可证期限进行升级

这个页面将会对你在进行 Confluence 升级的时候超过了当前许可证的期限进行升级的情况。 许可证警告 在升级的过程中,你将会在 Confluence 的应用程序日志(log file)中看到类似下面的错误提...

honeymoose
43分钟前
2
0
JS 调用Angularjs 的方法

// 1. 获取 Controllerlet appElement = document.querySelector('[data-ng-controller=MessagesCtrl]');let scope = angular.element(appElement).scope();// 2. 调用方法scope.l......

Moks角木
58分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部