如何在bash脚本中执行SQL？

I have some SQL scripts that I'm trying to automate. In the past I have used SQL*Plus, and called the sqlplus binary manually, from a bash script.

我有一些SQL脚本，我正在尝试自动化。在过去，我使用过SQL * Plus，并从bash脚本手动调用sqlplus二进制文件。

However, I'm trying to figure out if there's a way to connect to the DB, and call the script from inside of the bash script... so that I can insert date and make the queries run relative to a certain number of days in the past.

但是，我试图弄清楚是否有一种连接到数据库的方法，并从bash脚本内部调用脚本...这样我就可以插入日期并使查询相对于特定天数运行以往。

5 个解决方案

#1

I'm slightly confused. You should be able to call sqlplus from within the bash script. This may be what you were doing with your first statement

我有点困惑。您应该能够在bash脚本中调用sqlplus。这可能是你在第一次发表声明时所做的

Try Executing the following within your bash script:

尝试在bash脚本中执行以下命令：

#!/bin/bash          
echo Start Executing SQL commands
sqlplus <user>/<password> @file-with-sql-1.sql
sqlplus <user>/<password> @file-with-sql-2.sql

If you want to be able to pass data into your scripts you can do it via SQLPlus by passing arguments into the script:

如果您希望能够将数据传递到脚本中，可以通过将参数传递到脚本中来通过SQLPlus执行此操作：

Contents of file-with-sql-1.sql

file-with-sql-1.sql的内容

 select * from users where username='&1';

Then change the bash script to call sqlplus passing in the value

然后更改bash脚本以调用sqlplus传入值

#!/bin/bash

MY_USER=bob
sqlplus <user>/<password> @file-with-sql-1.sql $MY_USER

#2

You can also use a "here document" to do the same thing:

您还可以使用“此处文档”执行相同的操作：

VARIABLE=SOMEVALUE

sqlplus connectioninfo << HERE
start file1.sql
start file2.sql $VARIABLE
quit
HERE

#3

Maybe you can pipe SQL query to sqlplus. It works for mysql:

也许你可以将SQL查询传递给sqlplus。它适用于mysql：

echo "SELECT * FROM table" | mysql --user=username database

#4

-3

Here is a simple way of running MySQL queries in the bash shell

这是在bash shell中运行MySQL查询的一种简单方法

mysql -u [database_username] -p [database_password] -D [database_name] -e "SELECT * FROM [table_name]"

#5

-4

As Bash doesn't have built in sql database connectivity... you will need to use some sort of third party tool.

由于Bash没有内置的sql数据库连接......你需要使用某种第三方工具。

#1