iT邦幫忙

0

ORACLE 變數 execute immediate 使用

sql postgresql oracle oracle sql procedure
dork300916 2021-01-07 20:58:18 ‧ 5341 瀏覽

  • 分享至 

  • twitterImage

各位大大好
問題情境如下

目前有一個固定的Execute immediate 寫法(如最下圖)，但原本sql_text 都只會固定帶入一個參數去做簡單的語法查詢
例如select count(1) from TABLE where ID = :參數

但目前需要帶入多筆參數，在不影響原有的寫法之下，於是將多筆參數都合併寫成一個
像是下方由Execute immediate帶入參數值SN = parameter1/parameter2 (兩筆參數)
裡面再包一個Execute immediate，來實現將SN拆成兩個參數

例如以下錯誤的SQL

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := parameter1/parameter2;
BEGIN

   sql_text := '
   DECLARE
   parameters VARCHAR2(50);
   parameter1 VARCHAR2(50);
   parameter2 VARCHAR2(50);
   sql_txt VARCHAR2(1000);
   BEGIN
      parameters := :SN;
      parameter1 := substr(parameters,0,10);
      parameter2 := substr(parameters,11,10);
      
      sql_txt = ''select count(1) from productDetails  where ID = :parameter1
      AND NUMBER = :parameter2''
      EXECUTE IMMEDIATE sql_txt USING parameter1 RETURNING INTO MSG;
   END;'; 
   
    EXECUTE IMMEDIATE sql_text USING SN RETURNING INTO MSG;
    DBMS_OUTPUT.put_line(MSG);
END;

再請各位大大幫忙看看上方的SQL哪裡錯了

感恩

尼克 iT邦大師 1 級 ‧ 2021-01-08 12:36:31 檢舉
看不懂你的問題？
dork300916 iT邦新手 5 級 ‧ 2021-01-08 20:49:05 檢舉
尼克大大，已更新問題
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

1
pojen
iT邦研究生 5 級 ‧ 2021-01-08 06:25:49
最佳解答

不是很清楚你的問題, 如果是想把一串文字轉成表格然後選取的話可以 google: csv to arrary

範例:
我有一個表格 id 從 1 到 10
我有一個 csv 字串, '1,2,3'
我想用字串來過濾我的表格

drop table pojen;
create table pojen as select level eid,'A' ename from dual connect by level<10;

select * from pojen;

select * from pojen where eid in (
SELECT *
FROM (
    select trim(regexp_substr(str,'[^,]+', 1, level)) as str1
    from ( 
       SELECT '1,2,3' as Str 
       FROM dual 
    )
    connect by regexp_substr(str, '[^,]+', 1, level) is not null
) 
);

https://ithelp.ithome.com.tw/upload/images/20210108/20033353NY68TxKDKM.png

根據你的版本還有可否自加輔助 PL/SQL. 還有很多方法. 字串不長的話用 regexp 應該是最快的.

看更多先前的回應...收起先前的回應...
dork300916 iT邦新手 5 級 ‧ 2021-01-08 20:49:35 檢舉

感謝回答! 問題敘述比較難懂不好意思，已重新編輯問題

pojen iT邦研究生 5 級 ‧ 2021-01-09 05:08:12 檢舉

首先我不建議 execute immediate 在迴圈使用. 已提供的範例來說, 你可以測試以下方案:

drop table productDetails;
create table productDetails(c1 VARCHAR2(50),c2 VARCHAR2(50));
insert into productDetails values ('parameter1','parameter22');
insert into productDetails values ('parameter1','parameter2');
insert into productDetails values ('parameter1','/parameter');
commit;

DECLARE
    sql_txt VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) ;
   parameter1 VARCHAR2(50);
   parameter2 VARCHAR2(50);
   BEGIN

SN  := 'parameter1/parameter2';


      parameter1 := substr(SN,0,10);
      parameter2 := substr(SN,11,10);
      DBMS_OUTPUT.put_line(parameter1);
      DBMS_OUTPUT.put_line(parameter2);
      sql_txt := 'select count(1)*108 from productDetails  where c1 = :b1    AND c2 = :b2';

   
    EXECUTE IMMEDIATE sql_txt INTO MSG USING parameter1,parameter2 ;
    DBMS_OUTPUT.put_line(MSG);
END;
/

https://ithelp.ithome.com.tw/upload/images/20210109/20033353KVwRgs04UH.png

如果你有一組數據要用來測試, 請愛用 TYPE


drop type typ_po;
create type  typ_po as object (p1 varchar2(50),p2 varchar2(50));

declare
l_par typ_po;
l_exists int;
begin
l_par := typ_po('parameter1','parameter2');
dbms_output.put_line(l_par.p1);
dbms_output.put_line(l_par.p2);
select count(*)*809 into l_exists from productDetails where c1=l_par.p1 and c2=l_par.p2;
dbms_output.put_line(l_exists);
end;
/

https://ithelp.ithome.com.tw/upload/images/20210109/200333539yhKS03zVK.png

dork300916 iT邦新手 5 級 ‧ 2021-01-09 10:47:49 檢舉

感謝回答
目前的情境會比較像是，下方的EXECUTE IMMEDIATE是固定寫法

EXECUTE IMMEDIATE sql_text USING SN RETURNING INTO MSG;

只會USING一個參數進去

能變動的是裡面的sql_text內容，也就是上方看到的，裡面還包了一個

sql_text := '
   DECLARE
   parameters VARCHAR2(50);
   parameter1 VARCHAR2(50);
   parameter2 VARCHAR2(50);
   sql_txt VARCHAR2(1000);
   BEGIN
      parameters := :SN;
      parameter1 := substr(parameters,0,10);
      parameter2 := substr(parameters,11,10);
      
      sql_txt = ''select count(1) from productDetails  where ID = :parameter1
      AND NUMBER = :parameter2''
      EXECUTE IMMEDIATE sql_txt USING parameter1 RETURNING INTO MSG;
   END;';

sql_text的內容是用來切換搜尋語法的
例如也有這種只帶入一個參數的

sql_txt = ''select count(1) from productDetails  where ID = :parameter1''

原本都只是帶入一個參數執行搜尋，這次想帶入多個參數去執行搜尋，又不要影響到原本只帶入一個參數的語法運作

所以才會看到會有下方，這種一個參數再猜開來的作法

parameters := :SN;
parameter1 := substr(parameters,0,10);
parameter2 := substr(parameters,11,10);

這樣會比較建議怎麼做

感恩

pojen iT邦研究生 5 級 ‧ 2021-01-09 13:13:20 檢舉

我放棄了... 沒想到能把結果傳出來的方法. 上面那層是原先改好的程序. 下面那個是一般如何把結果拿出 pl/sql 的方法. 但沒想出來如何把兩個東西黏在一起. 或許你可以嘗試用 cursor 的方式在打開 cursor 時把東西傳出來.

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := 'parameter1/parameter2';
BEGIN

   sql_text := '   DECLARE   
   xx  number ; 
   parameters VARCHAR2(50);  
   parameter1 VARCHAR2(50);
   parameter2 VARCHAR2(50);
   sql_txt VARCHAR2(1000);
   BEGIN 
  -- /*
   parameters := :SN;      
   parameter1 := substr(parameters,0,10);      
   parameter2 := substr(parameters,11,10); 
   sql_txt := ''select count(1) from productDetails  where c1 = :parameter1 AND c2 = :parameter2'';      
   EXECUTE IMMEDIATE sql_txt  into xx USING parameter1,parameter2; 
  -- */
   /*
   dbms_output.put_line(88);
   :yy := 77;
   */
   END;'; 
   
    EXECUTE IMMEDIATE sql_text USING in out SN;
    DBMS_OUTPUT.put_line(SN);
END;
/
dork300916 iT邦新手 5 級 ‧ 2021-01-09 16:07:55 檢舉

感謝說明
cursor就第一次聽到了~再研究看看/images/emoticon/emoticon30.gif

dork300916 iT邦新手 5 級 ‧ 2021-01-12 21:30:13 檢舉

問題已解決了~感恩
解法是
內側使用

 EXECUTE IMMEDIATE sql_txt INTO :msg USING IN parameter1, IN parameter2;

外側使用

 EXECUTE IMMEDIATE sql_text USING IN SN, OUT MSG;
登入發表回應
1
尼克
iT邦大師 1 級 ‧ 2021-01-10 21:08:54 
CREATE OR REPLACE PROCEDURE ERP.sp_getemployees (
   e_recordset OUT SYS_REFCURSOR)
IS
BEGIN
   OPEN e_recordset FOR SELECT * FROM personnel;
END sp_getemployees;
/

微軟報表產生器呼叫ORACLE 上的PROCEDURE 操作手冊

pojen iT邦研究生 5 級 ‧ 2021-01-11 05:54:06 檢舉

不是的, 我的意思是這個.

create table  productDetails(c1 varchar2(50),c2 varchar2(50));
insert into productDetails values ('PO','JEN');
insert into productDetails values ('parameter1','/parameter');
commit;

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := 'parameter1/parameter2';
BEGIN

   sql_text := '   DECLARE   
   cursor my_cur is select count(*)*997 echeck from productDetails where c1=substr(:yy,0,10) and c2=substr(:yy,11,10);
   BEGIN 
   for l_cur in my_cur 
   loop
   :yy := l_cur.echeck;
   end loop;
   END;'; 
   
    EXECUTE IMMEDIATE sql_text USING in out SN;
    DBMS_OUTPUT.put_line(SN);
END;
/ DBMS_OUTPUT.put_line(SN);
END;
/

https://ithelp.ithome.com.tw/upload/images/20210111/20033353mDsffWh18U.png

請注意, 這是為了符合甲方提出必須使用 execute immeidate + 一個變數的要求. 如果正常使用, 強烈推薦我用 type 的範例.

dork300916 iT邦新手 5 級 ‧ 2021-01-12 21:30:52 檢舉

問題已解決了~感恩
解法是
內側使用

EXECUTE IMMEDIATE sql_txt INTO :msg USING IN parameter1, IN parameter2;
外側使用

EXECUTE IMMEDIATE sql_text USING IN SN, OUT MSG;

登入發表回應
1
喵凹咿唉思嗯
iT邦研究生 5 級 ‧ 2021-01-12 20:54:33

試看看with ? 但這樣用斜線切不會出事嗎XD

create table productDetails ( a varchar2(30), b varchar2(30) );
insert into productDetails values('a','x');
insert into productDetails values('b','x');
insert into productDetails values('c','y');

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := 'a/x';
BEGIN
    sql_text := 'with param as (select :1 input from dual ) 
    select count(1) from productDetails,param where a=substr(input,0,instr(input,''/'')-1) and b = substr(input,instr(input,''/'')+1)';
    EXECUTE IMMEDIATE sql_text  INTO MSG USING SN;
    DBMS_OUTPUT.put_line(MSG);
END;
dork300916 iT邦新手 5 級 ‧ 2021-01-12 21:30:39 檢舉

問題已解決了~感恩
解法是
內側使用

EXECUTE IMMEDIATE sql_txt INTO :msg USING IN parameter1, IN parameter2;
外側使用

EXECUTE IMMEDIATE sql_text USING IN SN, OUT MSG;

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙