ORACLE 逸出字元 之 單引號篇

錯誤語句：

預存程序名稱：ADD_MENU_PLine 74  - 79V_SQL := 'insert into sy_menu(menu_code,depth,menu_url,create_date,created_by,activity,menu_no,menu_parent_no)values(V_MENU_code,v_depth,v_menu_url,sysdate,'wangchao',1,v_menu_no,V_PARENT_MENU_NO)';  --執行刪除語句  EXECUTE IMMEDIATE V_SQL;

 

錯誤編號：

Compilation errors for PROCEDURE INDONESIAPROD2.ADD_MENU_PError: PLS-00103: 出現符號 "WANGCHAO"在需要下列之一時：        * & = - +          ; < / > at in is mod remainder not rem <an exponent (**)>          <> or != or ~= >= <= <> and or like like2 like4 likec between          || multiset member submultiset       符號 "*在 "WANGCHAO" 繼續之前已插入。Line: 77Text: (V_MENU_code,v_depth,v_menu_url,sysdate,'wangchao',1,v_menu_no,V_PARENT_MENU_NO)';Error: Hint: Parameter 'IN_MENU_URL' is declared but never used in 'ADD_MENU_P'Line: 2Text: ( IN_MENU_URL varchar2,Error: Hint: Value assigned to 'v_menu_url' never used in 'ADD_MENU_P'Line: 72Text: v_menu_url:=v_menu_url||  IN_MENU_NAME;

 

錯誤原因：字串常量中的單引號要用兩個單引號來表示.

錯誤解決：改成如下SQL 即可

  V_SQL := 'insert into sy_menu(menu_code,depth,menu_url,create_date,created_by,activity,menu_no,menu_parent_no)values(V_MENU_code,v_depth,v_menu_url,sysdate,''wangchao'',1,v_menu_no,V_PARENT_MENU_NO)';  --執行刪除語句  EXECUTE IMMEDIATE V_SQL;