Oracle SqlPlus常用命令简介

参考资料

1. 【SQL*Plus】SETシステム変数の設定前後の具体例

一. 执行系命令

1.1 执行系统命令

⏹进入SqlPlus之后，通过host或者!命令，可执行系统命令。
👉在linux系统中，执行的是bash命令
👉在windows中执行的是cmd命令

• host

SQL> host ls -l ~/work/test.sh
-rw-rw-r-- 1 apluser apluser 39 Oct  5 19:11 /home/apluser/work/test.sh

• !

SQL> ! ls -l ~/work/test.sh
-rw-rw-r-- 1 apluser apluser 39 Oct  5 19:11 /home/apluser/work/test.sh

1.2 执行sql脚本文件

1.2.1 在数据库中执行sql脚本

⏹语法：@/绝对路径/文件名.sql

• 通过host命令执行系统命令的时候，所用到的路径可以是相对路径。
• 但是通过@来执行sql文件的时候，路径只能为绝对路径，不能是相对路径。

SQL> host ls -l ~/work/select.sql
-rw-rw-r-- 1 apluser apluser 47 Dec 29 20:56 /home/apluser/work/select.sql

SQL>
SQL> host cat ~/work/select.sql
set markup csv on;
select * from cst_customer;

SQL>
SQL> @/home/apluser/work/select.sql

"CUST_ID","CUST_NAME","CUST_SOURCE","CUST_INDUSTRY","CUST_LEVEL","CUST_ADDRESS","CUST_PHONE"
2,"李四","线下门店","制造业","普通会员","上海市浦东新区","13900139001"
3,"王五","朋友推荐","金融业","黄金会员","广州市天河区","13700137002"
4,"赵六","搜索引擎","教育培训","普通会员","深圳市南山区","13600136003"
5,"孙七","自媒体","电子商务","钻石会员","杭州市西湖区","13500135004"

1.2.2 通过sqlplus执行sql脚本

⏹语法：sqlplus -s 用户名/密码@数据库地址 @/脚本文件路径

• -s：静默模式，输出更简洁，不显示多余的欢迎信息。

apluser@ubuntu24-01:~$ cat /home/apluser/work/select.sql
-- 格式化设置
set markup csv on;

-- 查询语句
select * from cst_customer;

-- 退出数据库
exit;
apluser@ubuntu24-01:~$
apluser@ubuntu24-01:~$ sqlplus -s system/oracle@192.168.118.137 @/home/apluser/work/select.sql

"CUST_ID","CUST_NAME","CUST_SOURCE","CUST_INDUSTRY","CUST_LEVEL","CUST_ADDRESS","CUST_PHONE"
2,"李四","线下门店","制造业","普通会员","上海市浦东新区","13900139001"
3,"王五","朋友推荐","金融业","黄金会员","广州市天河区","13700137002"
4,"赵六","搜索引擎","教育培训","普通会员","深圳市南山区","13600136003"
5,"孙七","自媒体","电子商务","钻石会员","杭州市西湖区","13500135004"

二. show命令

2.1 显示SqlPlus中的全部环境变量

SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
...省略...

2.2 显示指定环境变量的设置

SQL> show heading
heading ON
SQL>
SQL> show PAGESIZE
pagesize 14

三. 时间显示

3.1 set time on 提示符显示时间

SQL> set time on
20:45:28 SQL>
20:45:29 SQL>
20:45:30 SQL>
20:45:30 SQL>
20:45:30 SQL>

3.2 set timing on 显示SQL的耗时

SQL> set timing on
SQL> select * from cst_customer;

"CUST_ID","CUST_NAME","CUST_SOURCE","CUST_INDUSTRY","CUST_LEVEL","CUST_ADDRESS","CUST_PHONE"
2,"李四","线下门店","制造业","普通会员","上海市浦东新区","13900139001"
3,"王五","朋友推荐","金融业","黄金会员","广州市天河区","13700137002"
4,"赵六","搜索引擎","教育培训","普通会员","深圳市南山区","13600136003"
5,"孙七","自媒体","电子商务","钻石会员","杭州市西湖区","13500135004"

Elapsed: 00:00:00.01

四. SQL查询格式化

4.1 set markup csv on 查询结果输出为csv格式

SQL> set markup csv on
SQL> select * from cst_customer;

"CUST_ID","CUST_NAME","CUST_SOURCE","CUST_INDUSTRY","CUST_LEVEL","CUST_ADDRESS","CUST_PHONE"
2,"李四","线下门店","制造业","普通会员","上海市浦东新区","13900139001"
3,"王五","朋友推荐","金融业","黄金会员","广州市天河区","13700137002"
4,"赵六","搜索引擎","教育培训","普通会员","深圳市南山区","13600136003"
5,"孙七","自媒体","电子商务","钻石会员","杭州市西湖区","13500135004"

4.2 其他设置

⏹set linesize 1000

• 设置输出行的最大长度
• 如果不设置或者设置的值过小，在命令行窗口进行查询的时候可能导致一行内容被截断，无法更好的在页面上显示。

⏹set heading on

• 显示字段名称

⏹set colsep ','

• 设置分隔符

⏹SET PAGESIZE 0

• 数据显示不分页，都在一页显示

⏹set define off

• 用于关闭替代变量的替换功能
• 在 SQLPlus 中，默认情况下，替代变量以 & 开头，并且在执行命令时会被替换为用户输入的值。
• 执行 SET DEFINE OFF;后，SQL*Plus 将不再替换以 & 开头的内容，而会将&视为普通的文本。
• 当你需要输入包含 & 字符的文本而不想让 SQL*Plus 将其作为替代变量处理时常用。例如插入的数据为aaa&bbb。

⏹set sqlnumber off

• 当sql有换行的时候，默认情况下，换行处会显示数字，用来显示sql的行数。
• 通过上述设置后，可不显示sql的行数。

五. 内容输出

5.1 spool 命令将内容输出到指定文件

• spool /path/：将SqlPlus中的操作内容保存到指定的文件中。
• spool off：关闭输出。

SQL> host ls /home/apluser/work/sql_op.log
ls: cannot access '/home/apluser/work/sql_op.log': No such file or directory

SQL> spool /home/apluser/work/sql_op.log
SQL>
SQL> set markup csv on
SQL>
SQL> select * from cst_customer;

"CUST_ID","CUST_NAME","CUST_SOURCE","CUST_INDUSTRY","CUST_LEVEL","CUST_ADDRESS","CUST_PHONE"
2,"李四","线下门店","制造业","普通会员","上海市浦东新区","13900139001"
3,"王五","朋友推荐","金融业","黄金会员","广州市天河区","13700137002"
4,"赵六","搜索引擎","教育培训","普通会员","深圳市南山区","13600136003"
5,"孙七","自媒体","电子商务","钻石会员","杭州市西湖区","13500135004"

SQL>
SQL> spool off
SQL>

apluser@ubuntu24-01:~$
apluser@ubuntu24-01:~$ cat /home/apluser/work/sql_op.log
SQL>
SQL> set markup csv on
SQL>
SQL> select * from cst_customer;

"CUST_ID","CUST_NAME","CUST_SOURCE","CUST_INDUSTRY","CUST_LEVEL","CUST_ADDRESS","CUST_PHONE"
2,"李四","线下门店","制造业","普通会员","上海市浦东新区","13900139001"
3,"王五","朋友推荐","金融业","黄金会员","广州市天河区","13700137002"
4,"赵六","搜索引擎","教育培训","普通会员","深圳市南山区","13600136003"
5,"孙七","自媒体","电子商务","钻石会员","杭州市西湖区","13500135004"

SQL>
SQL> spool off
apluser@ubuntu24-01:~$