ODP.NET CommandText 跑多行指令
2 |
對 ODP.NET 如何跑 Oracle 多行指令,我始終一知半解。何時可以加分號「;」?何時該用 begin end?為何冒出 Encountered the symbol "xxx" when expecting one of the following: ... 錯誤?我通常是亂試一通,能跑就當沒事,沒想過認真把它弄清楚。前陣子查資料順便做了功課,終於有點心得。
問題一,CommandText 能不能有多行指令?需不需要加 ;?
CommandText 可一次跑多行指令的印象主要來自 MSSQL,而 SQL Server 行末的分號可加可不加;Oracle GUI 工具如 Oracle SQL Developer,允許寫兩行 INSERT 以分號結尾(跟 SQL 不同是分號不可省略),全選執行可以一次插入兩筆,於是我有了「使用分號分隔,Oracle 可一次跑多行指令」的錯覺:
但是把同樣的程式搬到 ODP.NET,滿心期望可用 OracleCommand 一次跑完,卻以命令結束有問題出錯收場 - ORA-00933: SQL 命令的結束有問題 / SQL command not properly ended
:
先釐清一點,SQLPlus、Oracle SQL Developer 這些工具有一種命令檔(Script)模式,當輸入多行指令(Statement)並以分號結尾,要以命令檔模式才會全部執行。以 Oracle SQL Developer 為例,UI 的執行有兩顆鈕,左邊 1 是 Execute Statement (執行述描句)、2 所指的是 Run Script (執行命令檔)。前者只會執行游標所在位置的指令,如果有兩行 INSERT 而游標停在第一行,只會執行第一行;按 Run Script 或全選再按 Execute Statement 才會全部執行,而分號就是觸發執行該 Statement 的標記。
CommandText 只能執行一個 Statement,無法容納多個 Statement,不管有沒有加分號。要突破這個限制有一個做法是用 begin 與 end; 將多行指令夾起來,這段指令將被包成一個 PL/SQL Block,其中可容納多個 Statement,但每個 Statement 要以分號結尾才符合規範,不像 MSSQL 可加可不加:
問題二,為什麼有時會冒出 Encountered the symbol "xxx" when expecting one of the following: ( begin case declare end exception ... 錯誤?
學會了 bein end; 大絕,我想把 Oracle SQL Developer 建資料表動作搬到 ODP.NET 裡跑,例如:先建 Table 再加欄位註釋:
結果得到 ORA-06550 PLS-00103 Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with ... :
bein end; 包含的區塊是所謂 PL/SQL Anonymous Block,而 PL/SQL Block 的一項重要原則是不可以包含 DDL (Data Definition Language) 指令,舉凡 CREATE、ALTER、DROP、GRANT、REVOKE、COMMENT... 都在禁止之列。如果一定要用,須改以 execute immediate 間接執行,但因涉及組裝 SQL 指令,請務必謹慎不要留下 SQL Injection 漏洞。
begin
execute immediate '
CREATE TABLE TBL (
PK INT,
N VARCHAR(16)
)';
execute immediate 'COMMENT ON COLUMN TBL.PK IS ''Primary Key''';
execute immediate 'COMMENT ON COLUMN TBL.N IS ''Name''';
end;
問題三,換行符號 "\r" 到底要不要移掉?
Windows 平台的換行符號是"\r\n",而 UNIX 世界是"\n",在過去 CommandText 的換行符號常會導致 PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for function goto if loop ... 錯誤,有很多次經驗需用 Replace("\r", string.Empty) 消毒,但隨著 Oracle 版本更新,似乎已能自動修正,例如用 Managed ODP.NET 連 XE 18c,故意送出 \r 也不會有問題:
小結
整理一下本篇提到的幾個要點:
- CommandText 裡只能有一個執行動作(SELECT、INSERT、UPDATE、DELETE...),若要包含多個一次執行,可用 begin end; 包成 PL/SQL Block
- PL/SQL Block 不能混入 DDL 資料庫異動指令(CREATE、ALTER...),除非以 EXECUTE IMMEDIATE '...' 方式間接觸發
- 過去 CommandText 中不能摻雜 \r 換行符號,但當代 Oracle Client 似已無此限制
Tips of how to run multiple statements in one CommandText in ODP.NET.
Comments
# by 偉倫
您好, 請教一下,是否有嘗試過轉換過以下情境為oracle語法? 1. 原mssql語法中使用declare後再執行select的情境 2. 原mssql語法中使用建立暫存表(select into)後,後續執行select時會再使用該暫存表
# by Jeffrey
to 偉倫,好問題,沒試過。關於這個議題,改天我再專門寫一篇文跟各位講解... (老高上身) 記得訂閱分享按下小鈴噹哦。