调试经验——使用Toad Quest Script Runner (QSR)工具批量执行SQL语句 (run SQL statements in batch with Toad QSR )

问题：

有一个报表，增删改查操作很多（上百条statement，上千行代码）。之前，据说有人是按条执行的，执行完一条CREATE TABLE语句后，还时不时来一句select count(*)查一下数据进表了没有。可以想象，这种工作模式下，出一份报表是很不容易的。难怪之前一份报表分给好几个人做，你做一部分数据整合，我做一部分数据准备，再交给第三个人集成。这样的效率肯定是不高的。

解决方法：

1. 根据报表的业务要求（即，最终的报表中所包含的字段、特殊处理逻辑、呈现方式（pivot table, filter）等），重新梳理之前的脚本：

1）移除select *, select count(*), desc table等多余语句

2）优化执行顺序（对同一张数据表的操作，尽量放在一块儿）

2. 按照业务逻辑分组SQL语句，然后形成几个相对独立的script，在同一script内部，可以全选后直接使用Toad Quest Script Runner进行批量运行 （这一步是至关重要的一步）

然后，就会发现，本来要手动点击几十下甚至几百下（考虑到不断的选取代码块，点击执行的操作），简化到只要点击十来次就够了。仅仅这一点，估计可以节省时间1小时。

看来这个QSR还真是个好工具啊！

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Execute Scripts with Quest Script Runner

Quest Script Runner (QSR) looks and operates the same way as the Toad Editor, but it only includes a subset of the Editor's features. Quest Script Runner is a small script execution utility that can run in the background or from the command line. Quest Script Runner can be helpful when you need to run long scripts and want to perform other tasks in Toad. In addition, several instances of Quest Script Runner can run at one time because of its small size.

The Quest Script Runner window is divided into the following regions:

• Editor (top)—Displays the script for you to review and edit. You can use the toolbar to save the script, open a different one, search, manage your connection, and other options.
• Script output (bottom)—Displays the script output and variable settings. See Script Output Tabs for more information.

Notes:

• Quest Script Runner is not completely SQL*Plus compatible; however, most DDL and DML scripts should be supported. See SQL*Plus Commands for more information.

• If you change data in the script session, the changes will not reflect in Toad until you commit the changes in the script session. Also, any session control statements executed in the script session (such as ALTER SESSION) are not visible to the Toad session.

• This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To execute scripts from Toad in Quest Script Runner

1. Open the script in the Toad Editor.
2. Select Editor | Execute SQL via QSR. Quest Script Runner opens using your current connection and executes the script.

   Note: You can also click the drop-down beside the icon and select Execute in QSR.

To execute scripts within QSR

1. Open the script in the Quest Script Runner Editor.
2. Click on the Quest Script Runner toolbar.