如何实现SQLPLUS调用SQL文件时可选参数的默认值配置

我通过CMD脚本调用SQL文件并传递参数，传入参数或手动回车时代码能正常运行；但不传参的话，SQLPlus会卡住等待用户输入，希望这种场景下自动用默认值继续执行。

原代码示例 #

CMD脚本 #

REM 传参时正常执行
sqlplus !dbuser! @!some_dir!\some_sql_file.sql test_text >> !log!

REM 不传参时会卡住等待输入
sqlplus !dbuser! @!some_dir!\some_sql_file.sql >> !log!

SQL文件 #

set serveroutput on

SET LINESIZE 10000

declare
    l_some_text varchar2(1000);
begin
     select nvl('&&3','no_text_given') into l_some_text from dual;

    dbms_output.enable;
    dbms_lock.sleep(1);
    dbms_output.put_line('SQL uses: ' || l_some_text );
end ;
/

解决方案 #

方案1：在SQL文件中定义参数默认值 #

问题根源是&&3会在未传参时触发SQLPlus的输入提示，通过提前用define命令设置默认值，可以避免等待输入：

set serveroutput on
SET LINESIZE 10000

-- 先设置默认值，未传参时使用该值
define input_text = 'no_text_given'
-- 如果传入了参数，覆盖默认值（&1对应CMD传入的第一个参数）
define input_text = '&1'

declare
    l_some_text varchar2(1000);
begin
    l_some_text := '&input_text';
    dbms_output.enable;
    dbms_lock.sleep(1);
    dbms_output.put_line('SQL uses: ' || l_some_text );
end ;
/

说明：将原SQL中的&&3改为&1，对应CMD传入的第一个参数（原代码参数位置不匹配，需修正）。通过define先设定默认值，即使没有传参，&1也不会触发输入提示，直接使用之前定义的默认值。

方案2：在CMD脚本中处理参数判断 #

在调用SQLPlus前，判断是否传入参数，无参时自动传递默认值：

REM 判断是否有传入参数，无参则传递默认值
if "%~1"=="" (
    sqlplus !dbuser! @!some_dir!\some_sql_file.sql "no_text_given" >> !log!
) else (
    sqlplus !dbuser! @!some_dir!\some_sql_file.sql %1 >> !log!
)

说明：通过CMD的条件判断，确保始终向SQL文件传递一个参数，从根源上避免SQLPlus等待输入的情况。

内容的提问来源于stack exchange，提问作者Peter Frey