CNDBA社区
1 Oracle SQLcl 工具概述
Oracle SQL Developer 命令行 (SQLcl) 是一个免费的 Oracle 数据库命令行接口。它允许您通过交互方式或批处理方式执行 SQL 和 PL/SQL。SQLcL 提供内联编辑、语句完成和命令回调等功能丰富的体验，同时还支持以前编写的 SQL*Plus 脚本。
工具官网首页：
https://www.oracle.com/cn/database/technologies/appdev/sqlcl.html
SQLcl 工具主要特性如下：
| 内嵌编辑器 | 在 SQLcl 提示符下以交互方式编辑多行语句和脚本 |
|---|---|
| 命令历史记录 | 循环浏览之前的 100 个脚本/命令 |
| 完成洞察 | 使用 Tab 键自动完成对象名称或关键字 |
| 新命令 | CTAS、DLL、Repeat、ALIAS、SCRIPT、FORMAT 等 |
| 客户端脚本 | 执行 javascript 来操纵查询结果，构建动态命令，与会话交互等等 |
| SQL*Plus 支持 | SQL*Plus 环境设置、命令和行为 |
SQLcl详细使用手册：
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/20.4/sqcug/working-sqlcl.html
2 部署SQLcl工具
直接从官网下载压缩包：
https://download.oracle.com/otn/java/sqldeveloper/sqlcl-20.4.1.351.1718.zip
SQLcl 工具只有一个安装包，安装Java 后，只需要将sqlcl/bin 目录添加到Windows 或 Linux 的环境变量就可以使用了。
在~/.bash_profile 的PATH 文件中添加sqlcl的路径，然后source 生效：
[dave@www.cndba.cn ~]$ vim ~/.bash_profile
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/oracle/product/19c/dbhome_1/bin:/u01/sqlcl/bin
[dave@www.cndba.cn ~]$ source ~/.bash_profile
调用命令windows 是sql.exe, Linux 是sql，随意没有扩展名，但本质就是一个shell 脚本，可以直接查看里面的代码。
[root@dave u01]# ll /u01/
total 0
drwxrwxr-x. 4 oracle oinstall 40 Mar 9 2020 app
drwxrwxr-x. 3 oracle oinstall 158 Jan 22 17:26 software
drwxr-xr-x 4 oracle oinstall 68 Jan 24 14:48 sqlcl
[root@dave u01]#
[root@dave bin]# pwd
/u01/sqlcl/bin
[root@dave bin]# ll
total 160
-rw-r--r-- 1 oracle oinstall 962 Jan 24 14:48 dependencies.txt
-rw-r--r-- 1 oracle oinstall 419 Jan 24 14:48 README.md
-rw-r--r-- 1 oracle oinstall 20031 Jan 24 14:48 sql
-rw-r--r-- 1 oracle oinstall 129536 Jan 24 14:48 sql.exe
-rw-r--r-- 1 oracle oinstall 119 Jan 24 14:48 version.txt
[root@dave bin]#
[dave@www.cndba.cn bin]$ more sql
#!/bin/bash
########################################################################
# (@)sql.sh
#
# Copyright 2014 by Oracle Corporation,
# 500 Oracle Parkway, Redwood Shores, California, 94065, U.S.A.
# All rights reserved.
#
# This software is the confidential and proprietary information
# of Oracle Corporation.
#
# NAME sql
#
# DESC This script starts SQL CL.
#
# AUTHOR bamcgill
#
# MODIFIED
# bamcgill 21/03/2014 Created
# bamcgill 18/07/2014 Simplified classpaths and args
# bamcgill 11/12/2014 Renamed script and contents
# bamcgill 16/01/2015 Renamed script and contents
# bamcgill 05/02/2015 Added STD_ARGS for headless and other args
# cdivilly 12/02/2015 Locate home folder via symlinks
# bamcgill 10/06/2015 Quote jarfiles for dirs with spaces
# bamcgill 02/10/2015 Adding specific JAVA_HOME for ADE dev users
# totierne 02/10/2015 use -cp instead of JARFILE to add ojdbc6
# bamcgill 14/10/2015 switch Cygwin settings so Cygwin Term will work
# totierne 16/10/2015 add classpath to allow times ten jars
# bamcgill 17/10/2015 Cleaning up bootstrap to call single java
# implementation with pruned args.
# totierne 12/05/2016 added $OH/lib to $LD_LIBRARY_PATH when $OH
# bamcgill 12/05/2016 adding -cleanup to args
# bamcgill 29/06/2016 Added more checks around JAVA_HOME settings
# bamcgill 04/07/2016 Using the ADE RDBMS JDK if it exists.
# jmcginni 23/08/2016 Grab Proxy info on Mac, KDE, Gnome
# bamcgill 04/11/2016 Added all jars to classpath and pointed cobertura
# ser file for running.
# bamcgill 17/11/2016 Added $OH/jdk as java location if JAVA_HOME not set
# bamcgill 16/10/2017 Enumerated the libraries for sqlcl to avoid unwanted
# class loads
# bamcgill 11/03/2017 Added classpaths for jars in different locations
# embedded in Oracle SQLDeveloper
# bamcgill 11/06/2017 Added classpaths for drivers and exts.
# bamcgill 10/05/2019 Hardened support for MINGW console on windows
# bamcgill 21/05/2019 Adding silence for nashorn warning after jdk11
# bamcgill 19/06/2019 Adding slf4j as its used with lb support and
# the new ssh implementation:wq
# bamcgill 14/11/2019 Removing funny comments from file.
# skutz 29/01/2020 Added utility function to get java version and
# used in in ADE setup function
# bamcgill 12/03/2020 Adding LANG and LC_ALL variables for forcing
# sqlcl into a particular language
# bamcgill 03/06/2020 Changing the DEBUG Flag to be explicitly called
# SQLCL_DEBUG which will allow debugging of sqlcl java.
# bamcgill 23/07/2020 In docker alpine images, LANG defaults to C or posix
# in this case we default to en_US.UTF8
########################################################################
……
3 SQLcl 使用示例
在之前提到，SQLcl 工具对sqlplus 是完全兼容的，只不过调用命令从sqlplus 变成了sql，并且扩展了很多非常适用的功能。
3.1 连接数据库
windows:
PS C:/Users/Dave> sql sys/oracle@192.168.74.202:1521:cndba as sysdba
SQLcl: 发行版 20.4 Production, 发行日期 星期日 一月 24 15:19:18 2021
版权所有 (c) 1982, 2021, Oracle。保留所有权利。
已连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 DAVE READ WRITE NO
4 USTC READ WRITE NO
SQL>
Linux：
[dave@www.cndba.cn ~]$ sql / as sysdba
SQLcl: Release 19.1 Production on Sun Jan 24 14:52:46 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
3.2 查看帮助
SQL> help
For help on a topic type help <topic>
List of Help topics available:
……
ARCHIVE_LOG
BREAK
BRIDGE*
BTITLE
CD*
CHANGE
CLEAR
……
3.3 使用Tab键进行代码补齐
在输入语句的时候，可以通过按Tab键来补全语句关键字（比如select）、table的名字，column的名字等。注意一点：自动补全的部分会使用大写来显示。
SQL> select * from DBA_SEGMENTS;
3.4 多行编辑功能：键盘左键
在编写比较长的SQL语句的时候，经常使用换行，但在SQL*Plus中，如果有一行写错了，必须重新将整个语句重写，非常不方便。
所以之前的解决方案是将SQL语句写在一个文件当中，然后来执行这个文件。
如果在在SQLcl中，则可以直接通过按方向键左键来移动光标进行修改。 注意这里不是按方向键上下键，因为上下键是用来查找我们之前执行的语句历史。
3.5 使用HISTORY命令查看执行历史：键盘上下键
在Windows环境中执行SQL*Plus时是可以使用方向键上下键来查找之前执行过的语句，Linux 环境则不支持。而在SQLcl当中，无论是在Linux还是Windows环境下，都支持使用方向键上下键对之前执行过的语句进行滚动查找并执行。
此外，还可以使用SQLcl的history命令来查看之前执行过的语句和命令。history命令有如下几个参数：full、usage（执行次数）、time（执行时间），clear（清除历史）以及使用具体的语句序号。
SQL> history
History:
1 select * from DBA_SEGMENTS;
2 alter session set container=USTC;
SQL> history time
1 select * from DBA_SEGMENTS;
2 (00.049) alter session set container=USTC;
SQL> history full
1 select * from DBA_SEGMENTS;
2 alter session set container=USTC;
SQL>
3.6 使用CD命令直接在SQLcl当中更换目录
在执行本地脚本时，如果启动SQL*Plus不是在脚本所在的路径，则要使用全路径名来执行脚本，而在SQLcl中可以像在命令行中一样，使用cd命令进行路径的切换。
SQL> pwd
/u01/software
SQL> cd /u01/app/oracle
SQL> pwd
/u01/app/oracle
SQL>
3.7 使用SQLFORMAT设定输出格式
正常情况下，我们想将数据表中的数据，以JSON或者CSV的格式输出，需要通过第三方的语言来实现，比如Java或者Python。而在SQLcl当中，只要通过SQLFORMAT设定一下输出格式就可以了。
SQL> help set sqlformat
SET SQLFORMAT
SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}
SQL>
SQL> set sqlformat json
SQL> select * from all_users where rownum <3;
{"results":[{"columns":[{"name":"USERNAME","type":"VARCHAR2"},{"name":"USER_ID","type":"NUMBER"},{"name":"CREATED","type":"DATE"},{"name":"COMMON","type":"VARCHAR2"},{"name":"ORACLE_MAINTAINED","type":"VARCHAR2"},{"name":"INHERITED","type":"VARCHAR2"},{"name":"DEFAULT_COLLATION","type":"VARCHAR2"},{"name":"IMPLICIT","type":"VARCHAR2"},{"name":"ALL_SHARD","type":"VARCHAR2"}],"items":
[
{"username":"SYS","user_id":0,"created":"17-APR-19","common":"YES","oracle_maintained":"Y","inherited":"YES","default_collation":"USING_NLS_COMP","implicit":"NO","all_shard":"NO"}
,{"username":"AUDSYS","user_id":8,"created":"17-APR-19","common":"YES","oracle_maintained":"Y","inherited":"YES","default_collation":"USING_NLS_COMP","implicit":"NO","all_shard":"NO"}
]}]}
SQL>
3.8 使用INFORMATION获取更多资讯
在SQL*Plus当中，需要使用describe命令来获取table的信息，在SQLcl当中，可以使用information或者info来获取更多的讯息。可以显示基本的column信息、index信息以及主外键信息。
SQL> info all_users
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
USERNAME VARCHAR2(128 BYTE) No Name of the user
USER_ID NUMBER No ID number of the user
CREATED DATE No User creation date
COMMON VARCHAR2(3 BYTE) Yes Indicates whether this user is Common
ORACLE_MAINTAINED VARCHAR2(1 BYTE) Yes Denotes whether the user was created, and is
maintained, by Oracle-supplied scripts. A user for
which this has the value Y must not be changed in
any way except by running an Oracle-supplied
script.
INHERITED VARCHAR2(3 BYTE) Yes Was user definition inherited from another
container
DEFAULT_COLLATION VARCHAR2(100 BYTE) Yes User default collation
IMPLICIT VARCHAR2(3 BYTE) Yes Is this user a common user created by an implicit
application
ALL_SHARD VARCHAR2(3 BYTE) Yes Is this user an all-shard user
SQL>
3.9 使用DDL命令获取数据对象ddl信息
有时候我们想获取一个表或者其他数据对象的DDL信息，以前我们都是使用程序包来实现，调用起来相对比较麻烦，在SQLcl当中我们可以使用DDL命令方便地获取这些数据对象的DDL信息。
SQL> create table cndba as select * from all_users;
Table created.
SQL> ddl cndba
CREATE TABLE "SYS"."CNDBA"
( "USERNAME" VARCHAR2(128) NOT NULL ENABLE,
"USER_ID" NUMBER NOT NULL ENABLE,
"CREATED" DATE NOT NULL ENABLE,
"COMMON" VARCHAR2(3),
"ORACLE_MAINTAINED" VARCHAR2(1),
"INHERITED" VARCHAR2(3),
"DEFAULT_COLLATION" VARCHAR2(100),
"IMPLICIT" VARCHAR2(3),
"ALL_SHARD" VARCHAR2(3)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
将生成的DDL信息保存到文件当中：
SQL> ddl cndba save cndba.sql
SQL> !cat cndba.sql
cat: cndba.sql: No such file or directory
注意这里默认保存的路径：
[dave@www.cndba.cn bin]$ find /u01 -name cndba.sql
/u01/app/oracle/product/19.3.0/dbhome_1/sqldeveloper/sqldeveloper/bin/cndba.sql
[dave@www.cndba.cn bin]$
我们加上绝对路径就可以了：
SQL> ddl cndba save /tmp/cndba.sql
SQL> !cat /tmp/cndba.sql
CREATE TABLE "SYS"."CNDBA"
( "USERNAME" VARCHAR2(128) NOT NULL ENABLE,
"USER_ID" NUMBER NOT NULL ENABLE,
"CREATED" DATE NOT NULL ENABLE,
"COMMON" VARCHAR2(3),
"ORACLE_MAINTAINED" VARCHAR2(1),
"INHERITED" VARCHAR2(3),
"DEFAULT_COLLATION" VARCHAR2(100),
"IMPLICIT" VARCHAR2(3),
"ALL_SHARD" VARCHAR2(3)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
版权声明：本文为博主原创文章，未经博主允许不得转载。
