oracle sqlplus命令详解（官方示例）

以为内容选自Oracle官方文档，只讲command-line：

规范：<变量名> , {举例} , a | b 枚举可选值，（XX）描述

----------------------------------------------------------------------------------------------------

1、glogin.sql常用脚本（配置sqlplus command line）：

-- To change the SQL*Plus prompt to display the current user, -- connection identifier and current time. -- First set the database date format to show the time. ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH:MI:SS'; -- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER -- and _DATE variables. SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> " -- To set the number of lines to display in a report page to 24. SET PAGESIZE 24 -- To set the number of characters to display on each report line to 78. SET LINESIZE 78 -- To set the number format used in a report to $99,999. --SET NUMFORMAT $99,999

--SET SERVEROUTPUT ON

2、常用命令：

--sqlplus xxx@xxx 登陆sqlplus

--exit 退出

--CTRL+C 强制中断会话

--help index 调出sqlplus帮助索引界面

{

}

--? | help 查看指定命令说明

--@ 运行指定脚本

--@@ 同@，运行nest script时使用

--/ 执行最近脚本

--accept 接受用户输入行存储到替代变量中

{

ACCEPT pswd CHAR PROMPT 'Password: ' HIDE

}

--append 向当前SQL行追加文本

{

HR@orcl> list

1 select employee_id, last_name from employees

2*

HR@orcl> append where salary > 12000

2* where salary > 12000

HR@orcl> list

1 select employee_id, last_name from employees

2* where salary > 12000

}

--achive log 查看和管理归档信息

--attribute 设置object类型column属性

{

ATTRIBUTE EMPLOYEE_TYPE.LAST_NAME FORMAT A20

}

--break 分开重复列

{

HR@orcl> BREAK ON DEPARTMENT_ID;

HR@orcl> SELECT DEPARTMENT_ID, LAST_NAME, SALARY

2 FROM EMP_DETAILS_VIEW

3 WHERE SALARY > 12000

4 ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME SALARY

------------- ------------------------- ----------

20 Hartstein 13000

80 Russell 14000

Partners 13500

90 King 24000

Kochhar 17000

De Haan 17000

100 Greenberg 12008

110 Higgins 12008

HR@orcl> CLEAR BREAKS

breaks 已清除

}

--COLUMN 命令集 改变列格式

{

COLUMN SALARY FORMAT $99,999 --设置数字显示格式 COLUMN LAST_NAME FORMAT A10 --设置LAST_NAME列每行显示10个字符 COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS --LIKE通知SQLPLUS COPY之前定义好的列SALARY属性 COLUMN column_name CLEAR --清楚已设置列属性 CLEAR COLUMNS --重设所有列属性

} --compute 运算查询结果集

{

COLUMN DUMMY NOPRINT;

COMPUTE SUM OF SALARY ON DUMMY;

BREAK ON DUMMY SKIP 1;

SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY

FROM EMP_DETAILS_VIEW

WHERE SALARY>12000

ORDER BY DEPARTMENT_ID;

COLUMN DUMMY NOPRINT;

COMPUTE SUM OF SALARY ON DUMMY;

BREAK ON DUMMY;

SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY

FROM EMP_DETAILS_VIEW

WHERE SALARY>12000

ORDER BY DEPARTMENT_ID;

CLEAR COMPUTES

}

--change err/correct 修改错误代码

{

SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY

*

第 1 行出现错误:

ORA-00904: "JO_ID": 标识符无效

HR@orcl> list 1

1* SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY

HR@orcl> change /jo_id/job_id

1* SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY

HR@orcl> l

1 SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY

2 FROM EMPLOYEES

3* WHERE SALARY>12000

}

--connect | conn 会话中切换连接命令，如conn hr/hr@orcl

--disconnect 断开连接

--clear buffer 清空sql buffer

--clear screen 清空screen

--copy

--define 定义变量（ variables）

{

HR@orcl> define l_name = "SMITH"

HR@orcl> define --列出所有替代变量

DEFINE _DATE = "2014-02-13 03:47:00" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)

DEFINE _USER = "HR" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)

DEFINE _EDITOR = "Notepad" (CHAR)

DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.

1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (

CHAR)

DEFINE _O_RELEASE = "1102000100" (CHAR)

DEFINE _RC = "0" (CHAR)

DEFINE L_NAME = "SMITH" (CHAR)

HR@orcl> undefine l_name

}

--undefine 取消替代变量

--describle/desc 描述表、视图的列以及函数、程序的说明。

--del 删除SQL BUFFER指定行（不知道n 则倒序删除行）

{

HR@orcl> del 3

HR@orcl> list

1 SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY

2* FROM EMPLOYEES

HR@orcl> del

HR@orcl> list

1* SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY

HR@orcl> del

HR@orcl> list

SP2-0223: SQL 缓冲区中不存在行。

}

--edit | ed 创建或编辑sql 脚本

--execute 执行语句

--get 将sql脚本从文件load into sql buffer

{

HR@orcl> get c:\myfile

1* select sysdate from dual;

HR@orcl> list

1* select sysdate from dual;

HR@orcl>

}

--save 将sql buffer内容保存至文件

--host sql会话模式利用host命令调用cmd命令

--input 追加新行enter a new line

{

HR@orcl> LIST

1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY

2 FROM EMPLOYEES

3* WHERE JOB_ID='SA_MAN'

HR@orcl> input

4 ORDER BY SALARY

5

HR@orcl> l

1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY

2 FROM EMPLOYEES

3 WHERE JOB_ID='SA_MAN'

4* ORDER BY SALARY

}

--list | L 列出SQL BUFFER中sql语句

{

HR@orcl> list

1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY

2 FROM EMPLOYEES

3* WHERE SALARY>12000

HR@orcl> list 2

2* FROM EMPLOYEES

}

--password 修改current user 密码

--print 打印变量

{

HR@orcl> print n

N

----------

2

}

--prompt 向screen发送消息

--quit 用法同exit

--recover 恢复数据库等操作

--repheader 放置report头（见ttitle）

--repfooter 放置report角

{

REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT'

TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO

SELECT LAST_NAME, SALARY

FROM EMP_DETAILS_VIEW

WHERE SALARY > 12000;

}

--remark | rem 单行注释

{

REMARK Commission Report;

REMARK to be run monthly.;

COLUMN LAST_NAME HEADING 'LAST_NAME';

COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999;

COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90;

REMARK Includes only salesmen;

SELECT LAST_NAME, SALARY, COMMISSION_PCT

FROM EMP_DETAILS_VIEW

WHERE JOB_ID='SA_MAN';

}

--run 列出sql buffer内容同时执行该内容

--show 显示变量或参数值

--shutdown 关闭数据库（DBA用户执行）

--startup 启动数据库（DBA用户执行）

--SET 命令集：

--SET PAGESIZE 60 设置一页显示行数

--set autocommit on 设置事务自动提交

--set verify off 关闭替代变量校验

{

SQL> SET VERIFY ON

SQL> SELECT &name FROM DUAL;

输入 name 的值: 1

原值 1: SELECT &name FROM DUAL

新值 1: SELECT 1 FROM DUAL

1

----------

1

SQL> SET VERIFY OFF

SQL> SELECT &name FROM DUAL;

输入 name 的值: 1

1

----------

1

}

--SET SERVEROUTPUT ON 打开屏幕输出dbms_output

--set underline 设置标题与内容分隔符号

{

HR@orcl> select * from dual;

D

-

X

HR@orcl> set underline =

HR@orcl> /

D

=

X

}

--set termout off | on 关闭打开sqlplus输出显示

--SET AUTOTRACE TRACEONLY 查看执行计划

--set feedback off 关闭”PL/SQL 过程已成功完成。“

--store and start 存储set 变量值和恢复set 变量值

{

SCOTT@orcl> store set c:\plusenv.sql --存储当前session set 变量值

已创建 file c:\plusenv.sql

SCOTT@orcl> show pagesize

pagesize 24

SCOTT@orcl> set pagesize 60

SCOTT@orcl> show pagesize

pagesize 60

SCOTT@orcl> start c:\plusenv.sql --恢复set 变量值

SCOTT@orcl> show pagesize

pagesize 24

SCOTT@orcl> host del /f c:\plusenv.sql

}

--ttitle 设置report抬头

--btitle 设置report尾

{

HR@orcl> TTITLE CENTER -

> "ACME SALES DEPARTMENT PERSONNEL REPORT"

HR@orcl> BTITLE CENTER "COMPANY CONFIDENTIAL"

HR@orcl> SELECT DEPARTMENT_ID, LAST_NAME, SALARY

2 FROM EMP_DETAILS_VIEW

3 WHERE DEPARTMENT_ID = 30

4 ORDER BY DEPARTMENT_ID, SALARY;

ACME SALES DEPARTMENT PERSONNEL REPORT

DEPARTMENT_ID LAST_NAME SALARY

------------- ------------------------- ----------

30 Colmenares 2500

30 Himuro 2600

30 Tobias 2800

30 Baida 2900

30 Khoo 3100

30 Raphaely 11000

COMPANY CONFIDENTIAL

已选择6行。

}

--spool 将屏幕显示保存到文件

--timing 计时器

{

HR@orcl> timing start sql_timer

HR@orcl> timing show

计时: sql_timer

已用时间: 00: 00: 05.25

HR@orcl> timing stop

计时: sql_timer

已用时间: 00: 00: 17.29

}

--variable 声明绑定变量供PLSQL块引用

{

HR@orcl> variable ret_val number

HR@orcl> begin

2 :ret_val := 4;

3 end;

4 /

PL/SQL 过程已成功完成。

HR@orcl> print ret_val

RET_VAL

--------

$4

HR@orcl> VARIABLE employee_info REFCURSOR

HR@orcl> BEGIN

2 OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY

3 FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ;

4 END;

5 /

PL/SQL 过程已成功完成。

HR@orcl> PRINT employee_info

}

--whenever oserror exit 执行命令中遇到系统异常自动退出

--whenever sqlerror exit sql.sqlcode 执行命令中遇到SQL异常自动退出

--XQUERY 运行xquery语句

{

set long 160

set linesize 160

xquery for $i in ora:view("EMP_DETAILS_VIEW") return $i

/

}

To be continue...

---------------------------

Dylan Presents.