通过批处理调用SQL*Plus执行批量脚本

文章目录

• set echo off

• set feedback off

• spool

• 已经登录

• 没有登录

• 方式1：登录进入SQL命令行

• 方式２：先进入SQL命令行，再登录

• 方式３： 使用用户名/密码连接指定机器

• 切换用户或服务器

• 在批处理文件中调用SQL*Plus命令执行批量脚本的场景

• Windows命令终端连接数据库

• 批量脚本执行

• 批量脚本的内容

• 完整示例

• 常见设置与语句汇总

关于SQL*Plus的介绍和安装参考： Oracle SQL*Plus的安装与使用

在批处理文件中调用SQL*Plus命令执行批量脚本的场景

SQLPlus是Oracle提供的命令行工具，可以在命令行中输入命令执行查询、插入和建表和执行脚本SQL等操作。
对于应用开发人员，一般数据库相关操作使用界面工具sqldeveloper就可以了，但是如果是CI（持续集成），需要命令行执行一些脚本文件就需要使用到SQLPlus。
在CI中，可以使用Jenkins等工具串接部署前确认、代码获取、代码构建、代码部署、数据导入以及部署后通知等流程节点。而在数据导入这一步，如果使用的是Oracle数据库，则可以在Jenkins中配置批处理文件（本地、远程皆可）进行导入。也就是：

1. Jenkins触发批处理

2. 批处理调用SQL*PLus命令

3. 通过SQL*PLus执行批量脚本，插入数据到数据库表。

接下来从登录、执行批量脚本以及日志输出逐步介绍如何实现整体功能。

Windows命令终端连接数据库

在命令行窗口登录数据库服务器的方式有多种：

方式1：登录进入SQL命令行

已经配置了连接机器，也可以是服务器本地

1. 命令行窗口输入 sqlplus

2. 输入用户名/密码

方式２：先进入SQL命令行，再登录

sqlplus /NOLOGconn user/pass@localhost:1521/orcl

• 1

• 2

• 1

• 2

• conn也可以全写 connect

方式３： 使用用户名/密码连接指定机器

sqlplus 用户名/密码@连接标识符
比如
sqlplus user/pass@localhost:1521/orcl

这里使用方式３即可。

切换用户或服务器

在登录某台服务器后也可以使用connect 命令切换到其他数据库服务器。
connect user/pass@localhost:1521/orcl

批量脚本执行

已经登录

如果在命令行已经登录了服务器，则直接使用@符号后面加批处量脚本文件名既可以执行这个脚本文件，比如：
@my.sql

没有登录

登录和执行批量脚本也可以在一个命令中完成，例如：
sqlpus username/password@host @my.sql

批量脚本的内容

批量脚本中一般会放数据操作的语句，比如新建表、插入数据、更新数据等语句，但是在CI中，是希望输出批量执行的一些有用的日志信息，以便查看导入的进度和错误的提示。这样就会使用到如下的设置命令：

set echo off

set echo off是关闭命令行本身的输出，因为批量导入的数据一般比较多，对于每一条语句本身不需要关注，所以也就不需要在控制台输出。

set feedback off

set feedback off 用于关闭执行结果的显示，比如插入、删除影响的行数，比如往某张表中插入一行数据，如果没有关闭执行结果显示的话，则在控制台会输出：

已创建 1 行。11

spool

spool将命令执行输出的结果复制到指定文件中，比如：

spool my.loginsert into my_user values('2','pass','1');commit;spool offquit

• 1

• 2

• 3

• 4

• 5

• 1

• 2

• 3

• 4

• 5

spool的完整命令语法
spool [file_name [create|replace|append] off|out]

• file_name 文件路径和文件名

• create，创建文件；replace文件存在，则替换；append内容附加到已经存在文件

• off 停止输出结果复制到文件，并关闭文件。 out是启动该功能。

完整示例

这里以批处理文件my.bat和批量脚本my.sql为例演示实际使用：

1. my.bat

@echo offecho  ===================Begin Batch========sqlplus oscar999/oscar999@host:1521/orcl @my.sql echo  ===================End Batch========12341234

• @echo off， 关闭命令本身的回显，即命令本身不显示在控制台中

• echo 在命令行终端输入后面的内容

1. my.sql

set feedback offspool my.logselect 'Loading my_user ... ' from dual;insert into my_user values('2','pass','1');commit;spool offdisconnectquit

• 1

• 2

• 3

• 4

• 5

• 6

• 7

• 8

• 9

• 1

• 2

• 3

• 4

• 5

• 6

• 7

• 8

• 9

• select 'Loading my_user … ’ from dual; 用于显示当前在处理那张表，纯粹是为了日志显示

• set feedback off关闭执行结果的显示，比如插入、删除影响的行数，但是Select语句的结果还是会输出。

常见设置与语句汇总

• set echo on 显示命名本身， 默认为off

• set echo off关闭命令行本身的输出

• set feedback off 显示命令处理的记录条数，默认为on

• SET TERMOUT OFF 显示脚本命令执行结果，默认为on

• set trimout on：去除标准输出每行的拖尾空格，缺省为off

• SET LINESIZE 800 设置每行显示的长度

• SET PAGESIZE 50设置每次显示的行数

• disconnect 断开连接 

• exit 退出sqlplus命令窗口