我的过程如下: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'就能查处结果,到底是怎么回事呢????
(
.....
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'就能查处结果,到底是怎么回事呢????
---------------------------------------------------------------------------------
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;
改为:
令值为 '''B00014'',''B00015'''试试看
改为:
令值为 '''B00014'',''B00015'''
---------------------------------应该也不行的,还是用动态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;
(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,010301060305012cur_out 结果是
{193AC18E-6254-4720-90B6-66B9D6B42CCA}
{B0EC6835-DA83-47C5-984F-1D876E1ACF41}
{B0EC6835-DA83-47C5-984F-1D876E1ACF41}
pck_getpaged.type_cur也是在包里定义的游标变量吧?
Strin SQL
BEGIN
SQL:='SELECT ... WHERE ..IN (:A,:B)';
OEPN CUR C FOR SQL USING PA,PB;
END;
2、将in的条件改为instr( ','||in_branch_codes||',',','||a.branch_code||',')>0试试,传入的变量in_branch_codes='B00015,B00014'