使用Shell脚本运行SQL查询并以CSV格式返回输出。

huangapple go评论78阅读模式
英文:

Running SQL Queries using Shell Script and get back the output in CSV Format

问题

我想使用Shell脚本自动化执行一个包含多个SQL查询的SQL文件,并且同时将它们的输出保存到一个.csv格式的文件中。

还有一个注意事项!这些查询将会有一些可变部分,即它们将会根据用户输入而改变。

例如:

SELECT NAME, CITY, ADDRESS FROM DATA WHERE COUNTRY = 'USA';

在这里,我不希望它始终是USA。那么,有没有办法在Shell脚本中将COUNTRY作为用户输入提供?

英文:

I want to automate a SQL file which contains multiple SQL queries using shell script and in the same time get their output in a .csv format file.

Also there's a catch! These queries will have some variable part i.e they will change and will depend on user input

For Eg:

SELECT NAME, CITY, ADDRESS FROM DATA WHERE COUNTRY = 'USA'

Here i don't want it always to be USA.So is there a way i can provide COUNTRY as a user input in Shell Script?

答案1

得分: 0

你可以尝试使用Oracle SQL*Plus替代变量。

SQL脚本 "demo.sql" 如下:

SPOOL some.csv APPEND
set colsep ','
SELECT NAME, CITY, ADDRESS FROM DATA WHERE COUNTRY = '&Country';
SPOOL OFF

然后运行如下脚本:

sqlplus -S username/pass @demo.sql

会提示您输入 "Country":

输入 "Country" 值:

然后输入一些内容并按回车键。
SQL将被执行,并会生成一个以逗号分隔的 some.csv 文件。

英文:

You could try Oracle SQL*Plus Substitution Variables.

SQL script "demo.sql" like that

SPOOL some.csv APPEND
set colsep ','
SELECT NAME, CITY, ADDRESS FROM DATA WHERE COUNTRY = '&Country';
SPOOL OFF

then run script like that

sqlplus -S username/pass @demo.sql

you will be prompted to input "Country"

Enter value for Country:

then type something and press Enter.
SQL was executed and will get some.csv file separated by Comma.

huangapple
  • 本文由 发表于 2020年1月6日 18:04:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/59610078.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定