原文鏈接：http://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2013/08/21/using-notepad-to-execute-oracle-sql.aspx
Notepad++是一個很實用的文本編輯工具，用它來執行Oracle SQL也很有意思，雖然不太實用，或許在某些場合會有用吧。
在此復制原文只作記錄，原文見如上鏈接。
原文如下：
Toad for Oracle is the single most robust and powerful IDE (Integrated Development Environment) and DBA tool for all your Oracle database needs. It’s a veritable nuclear powered, Swiss army knife capable of grand accomplishments. However there may well be times where you need a smaller, focused and thus simpler tool for modest tasks – such as opening and running a straightforward SQL script, where you might make minor changes such as setting a different script variable value at the top. Yet you still expect important features such as syntax highlighting for readability. For those specific, lesser needs I often use the popular freeware editor Notepad++. However it requires some manual tweaking in order to have Notepad++ execute SQL via Oracle’s SQL*Plus. In this blog I’ll show you how.
Note: The astute reader might ask “Why not just use Toad’s free QSR utility (i.e. Quest Script Runner -- it ships with every copy of Toad for Oracle) which looks and operates the same way as the Toad Editor, but it only includes a subset of the Editor's features?” – shown below in Figure 1. The answer is simple – people like choices and many people know and prefer Notepad++ as their standard Windows Notepad replacement and editor of choice for most simple needs.
Figure 1: Quest Script Runner Example
So let’s see how to enable Notepad++ to work much like QSR – where Notepad++ is the basic editor and spawns SQL*Plus to run the editor contents. It may not be as well integrated or flexible as QSR, but once again it’s what many people know and prefer for basic editing. Figure 2 shows the same script execution results as Figure 1.
Figure 2: Notepad++ Example
Here are the steps:
Figure 3: Notepad++ Plugin Manager – Enable NppExec
Figure 4: Save your SQL*Plus execute macro
在ORACLE的PL/SQL裡：
EXECUTE IMMEDIATE 代替了以前Oracle8i中DBMS_SQL package包.
它解析並馬上執行動態的SQL語句或非運行時創建的PL/SQL塊.動態創建和執行SQL語句性能超前，EXECUTE IMMEDIATE的目標在於減小企業費用並獲得較高的性能，較之以前它相當容易編碼.盡管DBMS_SQL仍然可用，但是推薦使用EXECUTE IMMEDIATE,因為它獲的收益在包之上。
-- 使用技巧
1. EXECUTE IMMEDIATE將不會提交一個DML事務執行，應該顯式提交
如果通過EXECUTE IMMEDIATE處理DML命令，
那麼在完成以前需要顯式提交或者作為EXECUTE IMMEDIATE自己的一部分.
如果通過EXECUTE IMMEDIATE處理DDL命令,它提交所有以前改變的數據
2. 不支持返回多行的查詢,這種交互將用臨時表來存儲記錄(參照例子如下)或者用REF cursors.
3. 當執行SQL語句時，不要用分號，當執行PL/SQL塊時，在其尾部用分號.
4. 在Oracle手冊中，未詳細覆蓋這些功能。
下面的例子展示了所有用到Execute immediate的可能方面.希望能給你帶來方便.
5. 對於Forms開發者,當在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能.
EXECUTE IMMEDIATE -- 用法例子
1. 在PL/SQL運行DDL語句
begin
execute immediate 'set role all';
end;
2. 給動態語句傳值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
3. 從動態語句檢索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
4. 動態調用例程.例程中用到的綁定變量參數必須指定參數類型.
黓認為IN類型,其它類型必須顯式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in o......余下全文>>
呵呵，經鑒定，1失敗的原因是from 後面沒有空格，你就'||拼接了表名最後執行的sql就是
'select count(*) frombook',所以會報找不到from關鍵字。歡迎追問。
