Oracle SQL脚本中基于条件的假脱机生成 [英] Condition based spool generation in Oracle SQL script

问题描述

我有一种情况，我们根据条件生成假脱机.

I have a scenario where we generate spool based on condition.

仅当用户键入y时，才应生成假脱机.

The spool should generate only if user types y.

column col noprint new_value elcm_script
select decode(lower('&gen'),'y','C:\ELCM.SQL','n', 'null') col from dual;

SPOOL c:\ELCM_DETAILS.SPL 
@&elcm_script
spool off

在上面，即使用户键入n，例如错误读取文件，它也会生成假脱机文件.如果n.如何停止生成假脱机，或者如何删除已生成的假脱机/

In the above it will generate spool file even if user type n like error reading file.. How to stop generating the spool if n.or how to remove spool which is generated/

推荐答案

如果您可以将此控制部分放到自己的脚本中，例如elcm_ctl.sql，您可以做到:

If you can put this controlling section into its own script, e.g. elcm_ctl.sql, you could make that:

accept run_it char format a1 prompt "Run the script?"

set termout off

whenever sqlerror exit success rollback
exec if upper(nvl('&run_it', 'N')) != 'Y' then raise no_data_needed; end if;
whenever sqlerror continue

spool c:\elcm.sql
select 'select sysdate from dual;' from dual;
spool off

set termout on

spool c:\elcm_details.spl
@c:\elcm.sql
spool off

accept命令有点在我看来，它比依赖替代提示更整洁，并且有些自我记录.这将提示用户输入指定的字符串，而不只是'gen'. (在SQL * Plus中，您可以对此进行扩展，并强制用户输入单个字符，如果用户输入的时间更长，则再次提示；如果用户只按return键而不输入任何内容，则默认为'N'.但是SQL Developer仅支持功能的一部分).

The accept command is a bit neater than relying on substitution prompts, in my opinion, and is somewhat self-documenting. This prompts the user with the specified string, rather than just 'gen'. (In SQL*Plus you can extend this and force the user to put in a single character, re-prompting if they put in anything longer; and default to 'N' if they just press return without entering anything. But SQL Developer only supports a subset of the functionality).

然后，如果输入的变量值不是'y'或'Y'，则一个小的匿名块将引发异常-哪一个都不重要.在执行此操作时，我 set termout off ，所以您看不到实际的异常.而且我使用了 whenever sqlerror 引发该异常时脚本退出，因此以后运行的内容均不会运行.这就是控制脚本中的所有内容，不仅是下一个查询，而且如果需要更灵活的话，可以有几个子脚本.

Then a small anonymous block throws an exception - doesn't really matter which one - if the entered variable value is not 'y' or 'Y'. While it's doing that I've set termout off so you don't see the actual exception. And I've used whenever sqlerror to make the script exit when that exception is raised, so whatever comes later is not run. That is everything else in the control script, not just the next query, but you could have several sub-scripts if you needed to be more flexible.

但是在SQL Developer中，termout仅在通过@运行时按预期方式工作.如果直接从SQL工作表中运行elcm_ctl.sql的内容，您将看到引发的异常，这有点难看.因此，请保存该控制脚本并将其保存在空白工作表中，只需执行以下操作:

But in SQL Developer, termout only works as expected when you run via @. If you run the contents of elcm_ctl.sql directly from the SQL Worksheet you'll see the exception being raised, which is a bit ugly. So instead save that control script and in an empty worksheet just do:

@c:\elcm_ctl.sql

将该工作表作为脚本执行，它将提示您；如果输入'Y'，您将在脚本输出窗口中看到脚本的输出(除非您将set termout off留在控制脚本中)，它将创建假脱机文件.如果输入其他任何内容，它将不会运行elcm.sql文件，不会在脚本输出窗口中显示任何内容，也不会创建假脱机文件.

Execute that worksheet as a script and it will prompt you; if you enter 'Y' you'll see the output of the script in the script output window (unless you leave set termout off in the control script), and it will create the spool file. If you enter anything else then it won't run the elcm.sql file, won't show anything in the script output window, and won't create a spool file.

这篇关于Oracle SQL脚本中基于条件的假脱机生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！