Oracle SQL*Plus
大约 13 分钟
Oracle SQL*Plus
SQL*Plus是一个命令行工具，可以运行在交互模式也可以运行在批处理模式。
可以使用SQL*Plus：
执行SQL， PL/SQL，SQL*Plus和操作系统命令
输入，编辑，运行，存储，查询和保存SQL命令及PL/SQL块
格式化，计算，存储和打印查询结果
查看表和对象定义
执行数据库管理
语法：
SQLPLUS [ [Options] [Logon|/NOLOG] [Start] ]
Options语法：
-H[ELP]|-V[ERSION]
|[[-C[OMPATIBILITY] {x.y[.z]] [-L[OGON]] [-M[ARKUP] markup_option]
[-R[ESTRICT] {1|2|3}] [-S[ILENT]] ]
markup_option语法：
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}]
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
Logon语法：
{username[/password][@connect_identifier]| / }
[AS {SYSOPER|SYSDBA|SYSASM}][edition=value]
Start语法：
@{url|file_name[.ext]} [arg ...]
启动SQL*Plus：
- 打开一个终端，进入oracle用户
- 输入
sqlplus <username>/<password>或者sqlplus /nolog - 如果使用/nolog选项，则需要使用
connect <username>/<password>进行连接
例子：启动SQL*Plus并查看帮助
[oracle@test ~]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 3 14:33:11 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
SQL> help connect
CONNECT
-------
Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.
CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA|SYSASM}] [edition=value]]
where logon has the following syntax:
username[/password][@connect_identifier]
where proxy has the syntax:
proxyuser[username][/password][@connect_identifier]
NOTE: Brackets around username in proxy are required syntax
这里使用HELP INDEX显示SQL*Plus的命令列表。
例子：使用exit退出SQL*Plus
SQL> exit
执行命令
命令分类：
| Category | Purpose |
|---|---|
| Environment | Affect the general behavior of SQL statements for the session. |
| Format | Format query results. |
| File manipulation | Save, load, and run script files. |
| Execution | Send SQL statements from the SQL buffer to the Oracle server. |
| Edit | Modify SQL statements in the buffer. |
| Interaction | Create and pass variables to SQL statements, print variable values, and print messages to the screen. |
| Miscellaneous | Connect to the database, manipulate the SQL*Plus environment, and display column definitions. |
在SQL*Plus命令行中，输入分号并按回车键执行命令。
例子：查看表或者视图定义
SQL> desc EMP_DETAILS_VIEW
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
JOB_ID NOT NULL VARCHAR2(10)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
LOCATION_ID NUMBER(4)
COUNTRY_ID CHAR(2)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
JOB_TITLE NOT NULL VARCHAR2(35)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_NAME VARCHAR2(40)
REGION_NAME VARCHAR2(25)
例子：查看函数或者存储过程定义
SQL> create or replace function afunc (f1 varchar2, f2 number) return number as
2 begin
3 if (length(f1) > f2) then
4 return 1;
5 else
6 return 0;
7 end if;
8 end;
9 /
Function created.
SQL> desc afunc
FUNCTION afunc RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
F1 VARCHAR2 IN
F2 NUMBER IN
执行SQL命令
在SQL*Plus执行SQL命令可以操作数据库中的数据。
回车进行换行，结束 SQL命令有三种方式：
- 分号（;）,使用分号加回车执行SQL
- 斜线（/）位于单独一行，回车执行SQL
- 空行，表示不执行
例子：执行SQL
SQL> SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
2 FROM EMP_DETAILS_VIEW WHERE SALARY > 12000;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
201 Hartstein MK_MAN 13000
205 Higgins AC_MGR 12008
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
108 Greenberg FI_MGR 12008
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
8 rows selected.
执行PL/SQL块
斜线（/）位于单独一行，回车执行PL/SQL块。
例子：执行PL/SQL块
--serveroutput环境变量控制是否显示存储过程或者PL/SQL块的输出
SQL> set serveroutput on
SQL> declare
2 x int := 20;
3 y int := 10;
4 z number;
5 begin
6 z := (x - y) / (x + y);
7 dbms_output.put_line(z);
8 exception
9 when zero_divide then
10 dbms_output.put_line('The divisor must not be zero!');
11 end;
12 /
.3333333333333333333333333333333333333333
PL/SQL procedure successfully completed.
执行SQL*Plus命令
SQL*Plus命令不需要以分号结束。
例子：执行SQL*Plus命令格式化输出
SQL> COLUMN SALARY FORMAT $99,999 HEADING 'MONTHLY SALARY'
SQL> SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
2 FROM EMP_DETAILS_VIEW WHERE SALARY > 12000;
EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY
----------- ------------------------- ---------- --------------
201 Hartstein MK_MAN $13,000
205 Higgins AC_MGR $12,008
100 King AD_PRES $24,000
101 Kochhar AD_VP $17,000
102 De Haan AD_VP $17,000
108 Greenberg FI_MGR $12,008
145 Russell SA_MAN $14,000
146 Partners SA_MAN $13,500
8 rows selected.
例子：显示命令执行的时间
SQL> set timing on
例子：显示SQL执行计划信息
SQL> set autotrace on
SQL> select count(*) from employees;
COUNT(*)
----------
107
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
执行操作系统命令
使用host关键字可以在SQL*Plus内执行操作系统命令。
例子：执行操作系统命令
SQL> host ls
database
使用脚本
编辑脚本
SQL*Plus将最近的一条SQL语句或者PL/SQL块存储到SQL buffer中，可以对SQL buffer中的SQL语句或者PL/SQL块进行编辑。
编辑命令有：
| Command | Abbreviation | Purpose |
|---|---|---|
APPEND text | A text | adds text at the end of the current line |
CHANGE/old/new | C/old/new | changes old to new in the current line |
CHANGE/text | C/text | deletes text from the current line |
CLEAR BUFFER | CL BUFF | deletes all lines |
DEL | (none) | deletes the current line |
DEL n | (none) | deletes line n |
DEL * | (none) | deletes the current line |
DEL n * | (none) | deletes line n through the current line |
DEL LAST | (none) | deletes the last line |
DEL m n | (none) | deletes a range of lines (m to n) |
DEL * n | (none) | deletes the current line through line n |
INPUT | I | adds one or more lines |
INPUT text | I text | adds a line consisting of text |
LIST | ; or L | lists all lines in the SQL buffer |
LIST n | L n or n | lists line n |
LIST * | L * | lists the current line |
LIST n * | L n * | lists line n through the current line |
LIST LAST | L LAST | lists the last line |
LIST m n | L m n | lists a range of lines (m to n) |
LIST * n | L * n | lists the current line through line n |
例子：使用SQL*Plus的编辑命令
SQL> select department_id
2 from departments where department_id=100;
DEPARTMENT_ID
-------------
100
使用list列出刚才执行过的命令
SQL> list
1 select department_id
2* from departments where department_id=100
使用1（数字1）列出刚才执行语句的第一行
SQL> 1
1* select department_id
使用a（append），在刚才这一行语句的最后增加后面的内容
SQL> a ,department_name
1* select department_id,department_name
SQL> list
1 select department_id,department_name
2* from departments where department_id=100
使用r（run）运行修改后的内容
SQL> r
1 select department_id,department_name
2* from departments where department_id=100
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
100 Finance
使用l（list）列出刚才执行过的命令
SQL> l
1 select department_id,department_name
2* from departments where department_id=100
使用1（数字1）列出刚才执行语句的第一行
SQL> 1
1* select department_id,department_name
使用c（change）进行替换
SQL> c/department_name/manager_id
1* select department_id,manager_id
使用l（list）列出刚才执行过的命令
SQL> l
1 select department_id,manager_id
2* from departments where department_id=100
使用r（run）运行替换后的内容
SQL> r
1 select department_id,manager_id
2* from departments where department_id=100
DEPARTMENT_ID MANAGER_ID
------------- ----------
100 108
增加注释
增加注释有以下三种方式：
- 使用SQL*Plus的
REMARK命令进行单行注释 - 使用SQL注释分隔符
/*... */进行单行或者多行注释 - 使用ANSI/ISO注释
- -进行单行注释
例子：使用REMARK命令进行注释，不能跨行
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';
例子：使用/*...*/进行注释，可以跨行，/*后面需要有一个空格，不能嵌套
/* Commission Report
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
/* Include only salesmen.*/
WHERE JOB_ID='SA_MAN';
例子：使用- -进行注释，不能跨行，可以跟在SQL和PL/SQ行后面，不能跟在SQL*Plus命令行后面
-- Commissions report to be run monthly
DECLARE --block for reporting monthly sales
-- set maximum width for LONG to 777
SET LONG 777
运行脚本
使用START命令运行脚本，默认脚本的扩展名为.sql。
语法：
START file_name
例子：将执行的SQL保存为脚本，再运行脚本
SQL> select department_id,manager_id from departments where department_id=100;
DEPARTMENT_ID MANAGER_ID
------------- ----------
100 108
--使用save保存刚才执行的语句到sql脚本文件
SQL> save my_query
Created file my_query.sql
--使用start命令执行sql脚本文件
SQL> start my_query
DEPARTMENT_ID MANAGER_ID
------------- ----------
100 108
还可以使用@或者@@命令运行脚本
SQL> @my_query
DEPARTMENT_ID MANAGER_ID
------------- ----------
100 108
SQL> @@my_query
DEPARTMENT_ID MANAGER_ID
------------- ----------
100 108
--其中?表示$ORACLE_HOME
SQL> @?/rdbms/admin/awrrpt.sql
还可以在启动SQL*Plus的时候运行脚本
[oracle@dbrac10 ~]$ sqlplus hr/hr @my_query
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 3 21:43:10 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
DEPARTMENT_ID MANAGER_ID
------------- ----------
100 108
替换变量
通过使用替换变量，可以用1条SQL语句执行不同的查询，比如我刚开始需要查询人员编号为100的信息，后来又需要查询人员编号为200的信息，如果使用替换变量，就可以沿用前面的的语句。替换变量有2个符号，一个是单&符号，一个是双&&符号，两者区别在于作用范围不一样，&引用的替换变量只在当前SQL有效，而&&引用的替换变量则在当前会话有效。
例子：通过提示输入人员编号查询不同人员的信息
SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;
Enter value for employee_num: 100
old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num
new 1: select employee_id,last_name,salary,department_id from employees where employee_id=100
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
如果变量的值是字符或者日期，则最好在语句中使用单引号将变量引起来，就不需要在输入变量的时候输入单引号了。
例子：通过提示输入工作编号查询不同人员的信息
SQL> select last_name,department_id,salary*12 from employees where job_id='&job_title';
Enter value for job_title: IT_PROG
old 1: select last_name,department_id,salary*12 from employees where job_id='&job_title'
new 1: select last_name,department_id,salary*12 from employees where job_id='IT_PROG'
LAST_NAME DEPARTMENT_ID SALARY*12
------------------------- ------------- ----------
Hunold 60 108000
替换变量除了用在条件比较，还可以用于select子句中的字段，where子句中的整个条件，order by子句的排序字段，甚至select关键字后面的所有内容。
例子：通过提示输入需要的字段信息来进行查找、限制和排序
SQL> select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column;
Enter value for column_name: salary
Enter value for condition: salary>15000
Enter value for order_column: last_name
old 1: select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column
new 1: select employee_id,last_name,job_id,salary from employees where salary>15000 order by last_name
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
102 De Haan AD_VP 17000
如果语句里面的变量需要重复使用，可以使用&&符号。
例子：通过提示输入需要的字段进行选择和排序
SQL> select employee_id,last_name,job_id,&&column_name from employees order by &column_name;
Enter value for column_name: department_id
old 1: select employee_id,last_name,job_id,&&column_name from employees order by &column_name
new 1: select employee_id,last_name,job_id,department_id from employees order by department_id
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen AD_ASST 10
如果将上面的语句再次执行，会发现不需要输入变量的值了，这是由于刚才输入变量的值时就已经在系统中定义了该变量的值为department_id，如果要更换为其他的值，就需要使用undefine关键字删除该变量，当然也可以使用define预先定义变量的值。
SQL> define column_name
DEFINE COLUMN_NAME = "department_id" (CHAR)
例子：先定义一个变量并赋值，再在select语句中使用，最后删除变量
SQL> define employee_num=200
SQL> define employee_num
DEFINE EMPLOYEE_NUM = "200" (CHAR)
SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;
old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num
new 1: select employee_id,last_name,salary,department_id from employees where employee_id=200
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
SQL> undefine employee_num;
SQL> define employee_num
SP2-0135: symbol employee_num is UNDEFINED
前面进行变量替换的时候，会显示替换前和替换后的语句，可以设置sqlplus的verify环境变量进行设置是否显示。
例子：设置进行变量替换的时候不显示替换前和替换后的语句
SQL> set verify off
SQL> select employee_id,last_name,salary from employees where employee_id=&employee_num;
Enter value for employee_num: 200
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
200 Whalen 4400
SQL> show verify
verify OFF
sqlplus里面环境变量还有很多，可以所有show all进行显示，如果要设置，就使用set。
例子：设置环境变量后只输出查询结果
SQL> set heading off feedback off pagesize 0 verify off echo off
SQL> select last_name from employees where employee_id=100;
King
与替换变量相关的环境变量如下：
| System Variable | Affect on Substitution Variables |
|---|---|
| SET CONCAT | Defines the character that separates the name of a substitution variable or parameter from characters that immediately follow the variable or parameter—by default the period (.). |
| SET DEFINE | Defines the substitution character (by default the ampersand "&") and turns substitution on and off. |
| SET ESCAPE | Defines an escape character you can use before the substitution character. The escape character instructs SQL*Plus to treat the substitution character as an ordinary character rather than as a request for variable substitution. The default escape character is a backslash (\). |
| SET NUMFORMAT | Sets the default format for displaying numbers, including numeric substitution variables. |
| SET NUMWIDTH | Sets the default width for displaying numbers, including numeric substitution variables. |
| SET VERIFY ON | Lists each line of the script before and after substitution. |
在脚本中使用&加数字做为替换变量，以便在运行脚本的时候传递参数。
例子：在脚本中使用替换变量，运行脚本的时候传递参数
SQL> SELECT EMPLOYEE_ID,LAST_NAME FROM EMP_DETAILS_VIEW
2 WHERE JOB_ID='&1'
3 AND SALARY='&2';
Enter value for 1: PU_CLERK
old 2: WHERE JOB_ID='&1'
new 2: WHERE JOB_ID='PU_CLERK'
Enter value for 2: 3100
old 3: AND SALARY='&2'
new 3: AND SALARY='3100'
EMPLOYEE_ID LAST_NAME
----------- -------------------------
115 Khoo
SQL> SAVE MYFILE
Created file MYFILE.sql
SQL> START MYFILE PU_CLERK 3100
old 2: WHERE JOB_ID='&1'
new 2: WHERE JOB_ID='PU_CLERK'
old 3: AND SALARY='&2'
new 3: AND SALARY='3100'
EMPLOYEE_ID LAST_NAME
----------- -------------------------
115 Khoo
格式化
格式化列
使用COLUMN命令格式化列。
例子：使用COLUMN命令的HEADING子句修改列头
SQL> COLUMN LAST_NAME HEADING 'LAST NAME'
SQL> COLUMN SALARY HEADING 'MONTHLY SALARY'
SQL> COLUMN COMMISSION_PCT HEADING COMMISSION
SQL> SELECT LAST_NAME, SALARY, COMMISSION_PCT
2 FROM EMP_DETAILS_VIEW
3 WHERE JOB_ID='SA_MAN';
LAST NAME MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
例子：使用|对列头换行
SQL> COLUMN SALARY HEADING 'MONTHLY|SALARY'
SQL> COLUMN LAST_NAME HEADING 'LAST|NAME'
SQL> /
LAST MONTHLY
NAME SALARY COMMISSION
------------------------- ---------- ----------
Russell 14000 .4
Partners 13500 .3
Errazuriz 12000 .3
Cambrault 11000 .3
Zlotkey 10500 .2
例子：格式化数字列
SQL> COLUMN SALARY FORMAT $99,990
SQL> /
LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------- ----------
Russell $14,000 .4
Partners $13,500 .3
Errazuriz $12,000 .3
Cambrault $11,000 .3
Zlotkey $10,500 .2
例子：格式化字符列
SQL> COLUMN LAST_NAME FORMAT A10
SQL> /
LAST MONTHLY
NAME SALARY COMMISSION
---------- -------- ----------
Russell $14,000 .4
Partners $13,500 .3
Errazuriz $12,000 .3
Cambrault $11,000 .3
Zlotkey $10,500 .2
例子：格式化
格式化输出
使用BREAK和COMPUTE命令格式化输出。
例子：在有ORDER BY子句的查询中使用BREAK命令进行列去重
SQL> BREAK ON DEPARTMENT_ID;
SQL> SELECT DEPARTMENT_ID, LAST_NAME, SALARY
2 FROM EMP_DETAILS_VIEW
3 WHERE SALARY > 12000
4 ORDER BY DEPARTMENT_ID;
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------- ---------- --------
20 Hartstein $13,000
80 Russell $14,000
Partners $13,500
90 De Haan $17,000
King $24,000
Kochhar $17,000
100 Greenberg $12,008
110 Higgins $12,008
8 rows selected.
--添加分割行
SQL> BREAK ON DEPARTMENT_ID SKIP 1
SQL> /
LAST MONTHLY
DEPARTMENT_ID NAME SALARY
------------- ---------- --------
20 Hartstein $13,000
80 Russell $14,000
Partners $13,500
90 De Haan $17,000
King $24,000
Kochhar $17,000
100 Greenberg $12,008
110 Higgins $12,008
8 rows selected.
保存输出
SQL*Plus使用SPOOL命令将输出结果保存到操作系统。
语法：
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
例子：将查询结果输出到文件
SQL> spool sql.txt
SQL> select * from departments where department_id=10;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
SQL> spool off
查看输出的文件
[oracle@test ~]$ cat sql.txt
SQL> select * from departments where department_id=10;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
SQL> spool off