签到成功

知道了

CNDBA社区CNDBA社区

Oracle SQLcl 工具 说明

2021-01-24 16:09 338 0 原创 Oracle 19c
作者: Dave

1 Oracle SQLcl 工具概述

Oracle SQL Developer 命令行 (SQLcl) 是一个免费的 Oracle 数据库命令行接口。它允许您通过交互方式或批处理方式执行 SQL 和 PL/SQL。SQLcL 提供内联编辑、语句完成和命令回调等功能丰富的体验,同时还支持以前编写的 SQL*Plus 脚本。

工具官网首页:

https://www.oracle.com/cn/database/technologies/appdev/sqlcl.htmlhttps://www.cndba.cn/dave/article/4365

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://www.cndba.cn/dave/article/4365

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 生效:https://www.cndba.cn/dave/article/4365

[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,并且扩展了很多非常适用的功能。https://www.cndba.cn/dave/article/4365

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:https://www.cndba.cn/dave/article/4365https://www.cndba.cn/dave/article/4365

[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语句写在一个文件当中,然后来执行这个文件。

https://www.cndba.cn/dave/article/4365

如果在在SQLcl中,则可以直接通过按方向键左键来移动光标进行修改。 注意这里不是按方向键上下键,因为上下键是用来查找我们之前执行的语句历史。

3.5 使用HISTORY命令查看执行历史:键盘上下键

在Windows环境中执行SQL*Plus时是可以使用方向键上下键来查找之前执行过的语句,Linux 环境则不支持。而在SQLcl当中,无论是在Linux还是Windows环境下,都支持使用方向键上下键对之前执行过的语句进行滚动查找并执行。https://www.cndba.cn/dave/article/4365

此外,还可以使用SQLcl的history命令来查看之前执行过的语句和命令。history命令有如下几个参数:full、usage(执行次数)、time(执行时间),clear(清除历史)以及使用具体的语句序号。https://www.cndba.cn/dave/article/4365

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命令进行路径的切换。https://www.cndba.cn/dave/article/4365

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" ;

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1832
    原创
  • 2
    翻译
  • 417
    转载
  • 119
    评论
  • 访问:3164684次
  • 积分:2888
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群