各位先進:
程式傳入一個變數:A1
A1 = "'1','2','3','4'"然後在store procedure寫
select a,b,c
from ta
where c in (A1);這樣的語法錯誤嗎?會找不到資料,如果將他改成
select a,b,c
from ta
where c in ('1','2','3','4');
就找得到資料
有誰知到原因嗎?
程式傳入一個變數:A1
A1 = "'1','2','3','4'"然後在store procedure寫
select a,b,c
from ta
where c in (A1);這樣的語法錯誤嗎?會找不到資料,如果將他改成
select a,b,c
from ta
where c in ('1','2','3','4');
就找得到資料
有誰知到原因嗎?
execute immediate 'insert into dept values (:1, :2, :3)'
这样方式来取得结果
可是卻select不出資料,
不懂archwuke1的意思…
還是不行,
大家都沒有事過傳一個變數到store procedure中,
供 in條件參考嗎?
請大家多幫忙…
l_sql varchar2(2000);
begin
l_sql:='select c1
from t
where c in'||' (''1'',''2'',''3'',''4'')';
--dbms_output.put_line(l_sql);
execute immediate l_sql;end;
都遇见过,都没蠢到用这种做法。A1 = "'1','2','3','4'"然後在store procedure寫
select a,b,c
from ta
where c in (A1);这么做,只有当c = '1','2','3','4' 时才能查出来,理解了吗?a b c
1 1 1
1 1 2
1 1 3
1 1 '1','2','3','4' --只有这条是数据库认为满足条件的
那有沒有比較不蠢的方法,
讓c='1' or c= '2' or c='3' or c='4'時,都能滿足條件啊!
(sele_gtype in varchar2 )
is
cursor prd_pos_curs
is select a,b,c
from ta
where c in (sele_gtype);prd_pos_rec prd_pos_curs%rowtype ;begin open prd_pos_curs;
loop
begin
fetch prd_pos_curs into prd_pos_rec;
exit when prd_pos_curs%NOTFOUND;
end ;
end loop;
close prd_pos_curs ;
end ;請大家幫忙,謝謝!
1 动态 (sele_gtype in varchar2 )
is
prd_pos_curs sys_refcursor;
aa ta.a%type;
bb ta.b%type;
cc ta.c%type;
begin open prd_pos_curs for 'select a,b,c from ta where c in ('||sele_gtype||')';
loop
begin
fetch prd_pos_curs into aa,bb,cc;
exit when prd_pos_curs%NOTFOUND;
end ;
end loop;
close prd_pos_curs ;
end ;
2 拆分(sele_gtype in varchar2 )
isv_c number := length(sele_gtype) - length(replace(sele_gtype,','))+1;
cursor prd_pos_curs
is select a,b,c
from ta
where c in (select regexp_substr(sele_gtype,'[^,]+',1,level)
from dual connect by level <= v_c);prd_pos_rec prd_pos_curs%rowtype ;begin open prd_pos_curs;
loop
begin
fetch prd_pos_curs into prd_pos_rec;
exit when prd_pos_curs%NOTFOUND;
end ;
end loop;
close prd_pos_curs ;
end ;
我的oracle版本為8i
不知道是不是版本的關係,
方法一會出現:
[Error] PLS-00201 (8: 14): PLS-00201: 識別字 'SYS_REFCURSOR' 必須被宣告
的錯誤;
方法二會出現:regexp_substr未被定義的錯誤,麻煩了!謝謝!
1 先执行 create package pack_mycur 方法二select regexp_substr(sele_gtype,'[^,]+',1,level)
from dual connect by level <= v_c
改为select substr(chr(44)||sele_gtype||chr(44),
instr(chr(44)||sele_gtype||chr(44),chr(44),1,level)+1,
instr(chr(44)||sele_gtype||chr(44),chr(44),1,level+1)-instr(chr(44)||sele_gtype||chr(44),chr(44),1,level)-1
) from t connect by level <= v_c
create package pack_mycur is
type mycur is ref cursor;
end pack_mycur;然后过程里prd_pos_curs sys_refcursor;改成prd_pos_curs mycur ;