用户在sql命令中输入数据时出现的问题 [英] Issues when user input data in sql command

问题描述

我有一个批处理文件，运行时会调用一个SQL文件.这个SQL文件提示用户输入我存储在MYPeriod中的数据(接受日期作为输入).SQL文件的输出是CSV文件. 这是我的SQL文件代码:

I have a batch files which when i run calls a SQL file. This SQL file prompts user to input data which I store in MYPeriod( this accepts date as input ).the output of SQL file is a CSV file. Here is my code for SQL file:

PROMPT Enter a period
ACCEPT MYPeriod PROMPT 'Period: '

SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF

spool E:\abc\Test\File1_&MYPeriod.csv

select Account || ',' || Membername || ',' || GROUP || ',' || FUTURE1  from ACTUAL_V@UAT1_Test where Key=21 and period_name= '&MYPeriod' ;
spool off
exit

我的查询:

1. 运行此命令时，将在文件E:\ abc \ Test中生成一个文件，文件名为File1_12-2012csv.Lst.我想要一个csv文件. 如果我硬编码文件名(通过测试替换& MYPeriod)，File1_Test.csv会完美生成.为什么代码无法使用用户输入的名称来创建文件.?

1. When I run this , a file gets generated in location E:\abc\Test with a name File1_12-2012csv.Lst. I want a csv file . If i hard code the file name(replace &MYPeriod by test) File1_Test.csv gets generated perfectly.Why the code is not able to create file with the name user has input..?

此命令的输出创建一个csv文件并从db检索帐户，但它在顶部多打印了两行.新查询和旧查询.如何重新定义我的代码，以便将其自动删除.

The output of this creates a csv file and retrieves the accounts from db but it prints two extra line at top. The new query and old query. How do I redefine my code, so that it gets remove automatically.

感谢您的帮助人员.

推荐答案

替换变量为

Substitution variables are optionally terminated by a period to separate them from any characters that follow. If you want to use one in the string as well, you have to add that termination explicitly:

spool E:\abc\Test\File1_&MYPeriod..csv

对于另外两行，请添加 ;现在将其设置为on(默认情况下)，以便向您显示您使用的所有替换变量的旧值和新值.

For the two extra lines issue, add set verify off; at the moment it is set to on (by default) so it shows you the old and new value of any substitution variables you use.

我知道将日期输入文件名的唯一方法是先将其放入替换变量:

The only way I know to get the date into the file name is to put it into a substitution variable first:

set termout off
column x_run_dt new_value y_run_dt
select to_char(sysdate, 'YYYYMMDDHH24MISS') as x_run_dt from dual;
set termout on
spool E:\abc\Test\File1_&MYPeriod._&y_run_dt..csv

在new_value子句中，您可以使用查询列x_run_dt中的值创建替换变量&y_run_dt.当作为脚本运行时，将在set termout子句之间生成该值的select包裹起来，将其从常规输出中隐藏起来.

The new_value clause lets you create a substitution variable, &y_run_dt in this case, with a value from a queried column, x_run_dt. Wrapping the select that generates that value between set termout clauses hides it from the normal output, when run as a script.

这篇关于用户在sql命令中输入数据时出现的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！