谁能解决这个问题给分200,高手请进,存储过程问题!!!!!!!!!!

[lyhang801]

我的过程如下:

PROCEDURE p_query_other_self_branch_fh
(
.....
in_branch_codes IN VARCHAR2,
.........
) IS
v_date_str td_calendar.date_str%TYPE;
BEGIN
..
OPEN cur_r FOR
SELECT a.date_str,
......
FROM t_rpt_acct_all_detail a,
td_organ b
WHERE a.date_str = v_date_str
.....
AND a.branch_code in ('B00014','B00015')
--AND a.branch_code in (in_branch_codes);
RETURN;
END;

我想出入动态的字符串in_branch_codes代替'B00014','B00015',但是测试时候将in_branch_codes令值为'B00014','B00015',却查不出来,而直接用'B00014','B00015'就能查处结果,到底是怎么回事呢????

[子陌红尘]

try：
---------------------------------------------------------------------------------
PROCEDURE p_query_other_self_branch_fh
(
.....
in_branch_codes IN VARCHAR2, --参数格式如 'B00014,B00015'
.........
) IS
v_date_str td_calendar.date_str%TYPE;
BEGIN
..
OPEN cur_r FOR
SELECT a.date_str,
......
FROM
t_rpt_acct_all_detail a,
td_organ b
WHERE a.date_str = v_date_str
.....
--AND a.branch_code in ('B00014','B00015')
AND ','||in_branch_codes||',' like '%,'||a.branch_code||',%';

RETURN;
END;

[lyhang801]

谢谢,还是不对

[qiaozhiwei]

1、用动态sql可以试试（要注意'的问题）
2、将in的条件改为instr( ','||in_branch_codes||',',','||a.branch_code||',')>0试试，传入的变量in_branch_codes='B00015,B00014'

[boydgmx]

---令值为'B00014','B00015'
改为：
令值为 '''B00014'',''B00015'''

试试看

[沝林]

---令值为'B00014','B00015'
改为：
令值为 '''B00014'',''B00015'''
---------------------------------

应该也不行的，还是用动态sql吧

[laofang]

很明显这个问题用游标变量和本地动态sql就可解决呀
...PROCEDURE p_query_other_self_branch_fh
(
.....
in_branch_codes IN VARCHAR2,
.........
)
IS
v_date_str td_calendar.date_str%TYPE;

--游标变量
type ref_cur is ref cursor;
cur_r ref_cur;
vs_sql varchar2(1024);

BEGIN
...
--把用输入参数组合出动态sql放到字串中
vs_sql := 'SELECT a.date_str, ...... ' ||
' FROM t_rpt_acct_all_detail a, td_organ b'||
' WHERE a.date_str = v_date_str..... ' ||
--AND a.branch_code in ('B00014','B00015')
' AND a.in_branch_codes in(' || in_branch_codes || ')';

--打开游标变量
OPEN cur_r FOR vs_sql;

--接下来就可像普通游标一样操作了
...

RETURN;
END;

[wvivw]

create or replace procedure test_invchar2
(in_char in varchar2,
cur_out out pck_getpaged.type_cur) is
begin
Open cur_out for
select guid from testtable
where in_char like '%'||customercode||'%';

end test_invchar2;


begin
-- Call the procedure
test_invchar2(in_char => :in_char,
cur_out => :cur_out);
end;
:in_char 值 010301060305013,010301060305012

cur_out 结果是
{193AC18E-6254-4720-90B6-66B9D6B42CCA}
{B0EC6835-DA83-47C5-984F-1D876E1ACF41}
{B0EC6835-DA83-47C5-984F-1D876E1ACF41}

[laofang]

wvivw(疾风) ：
pck_getpaged.type_cur也是在包里定义的游标变量吧？

[多壮志]

DECLAER
Strin SQL
BEGIN
SQL:='SELECT ... WHERE ..IN (:A,:B)';
OEPN CUR C FOR SQL USING PA,PB;
END;