如何通过命令行将参数传递给sql脚本 [英] How to pass parameters to sql scripts via command line

问题描述

有一个要求，我试图在我的项目中自动化一个流程，其中需要运行 sql 以进行日常报告.

There a requirement where I am trying to automate a process in my project where in a sql need to be run for daily reporting.

sql 如下所示:(这是最简单的形式，但我的 sql 有 400 行，以下只是获取结果的示例).

sql looks like below: (This is simplest form but my sql is of 400 lines,below is just an example to get the results).

test.sql

select * from table
where create_date between &date1 and &date2;

我想创建一个调用 sqlplus 并通过命令提示符传递日期的 bat 文件.日期将自动计算并在命令提示符本身中传递.

I wanted to create a bat file that calls the sqlplus and passes the dates via command prompt.Date will be calculated automatically and will be passed in the commmmand prompt itself.

我试过下面的命令行:

sqlplus userid/password@db_name @test.sql DATE1 DATE2

但这仍然提示我输入日期 1 和日期 2 的日期，我希望它们自动从参数中提取.

But that still prompts me to enter the dates for date 1 and date 2 which I want that to be picked up from arguments automatically.

你能帮我实现上述目标吗?

Could you please help me in achieving the above ?

推荐答案

从命令行传递的参数在 SQLPLUS 中作为 &1 和 &2 可用.

The parameters that are being passed from the command line are available in SQLPLUS as &1 and &2.

select * from table
where create_date between &1 and &2;

为了防止出现日期格式问题，您可能需要考虑将其更改为

To prevent problems with date formatting you may want to consider changing that to

select * from table
where create_date between to_date('&1','DD-MM-YYYY') and to_date('&2','DD-MM-YYYY');

或者你想使用的任何日期格式.

Or whatever date format you want to use.

这篇关于如何通过命令行将参数传递给sql脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！