调用存储过程出错现象：在存储过程中使用not in ()方式引用传入字符型值(字符型值之间使用“，”分隔)时总是出错。对于传值应注意什么事

Question

试一下连接语句
procedure p_grant(vRCode in varchar2,vFCode in varchar2)
asstr varchar2（2000）；begin
str：='DELETE FROM TEST WHERE RCODE='||vRCode||' AND FCODE NOT IN ('||vFCode||')';
execute immediate str;
end;
中间的中文字符我没转换，自己换一下吧

Answer 1

armyyd(不会游泳的猫) ：
你的方法可能行得通，但我的需求中不只一个SQL语句，其中还有根据条件打开CURSOR等，都是根据传入的参数进行NOT IN处理，不知还有没有其他好的方法。

Answer 2

一楼的方法应该就可以了
不知道楼主的具体需求
不使用动态sql很难解决的

Answer 3

bzszp(www.bzszp.533.net):
我的需求是一个进行角色授权，其中模块表结构如下：
FUNCTION（
FCODE varchar2(10),//模块代码
...
)
部分数据如下：
FCODE
10
1001
1002
1003
1004
1005
20
2001
2002
...角色权限表结构如下：
ROLEAUTH(
RCODE varchar2(10),//角色代码
FCODE varchar2(10),//模块代码
...
)
其中数据如下：
RCODE  FCODE
10     10
10     1001
10     1003
20     10
20     2002
.....
也就是角色10具备10，1001，1003模块的权限，角色20具备10，2002模块的权限。
在操作界面上进行授权，比如选择了角色10进行授权，当用户打开界面时，先将10，1001，1003这些已经授权的模块显示给用户，这时若用户删除了1001权限，并且增加了1004权限并进行保存时，我的做法是将角色10和模块10，1003，1004传入存储过程中，在存储过程中先删除ROLEAUTH表中角色10的模块号1001，也就是说ROLEAUTH表中不在10，1003，1004中的任何一项即可删除，同时在当前ROLEAUTH表中增加不在10，1003，1004中的任何一项，即1004项。所以我写的存储过程如下：procedure p_grantAuthByRole (vRoleCode in varchar2,vFuncCode in varchar2)
as
  sFuncCode varchar2(10);
begin
  --删除角色权限；
  DELETE FROM TEST WHERE ROLECODE=vRoleCode AND FUNCCODE NOT IN (vFuncCode);
  --开始角色授权；
  declare cursor cur_auth is
    SELECT A.FUNCCODE FROM FUNCTION A WHERE A.FUNCCODE IN (vFuncCode)
    MINUS
    SELECT A.FUNCCODE from ROLEAUTH A WHERE A.ROLECODE=vRoleCode;
  begin
    open cur_auth;
    loop fetch cur_auth into sFuncCode;
      exit when cur_auth%notfound;
        insert into ROLEAUTH(ROLECODE,FUNCCODE) VALUES(vRoleCode,sFuncCode);
    end loop;
    close cur_auth;
    end;
  end;
在调用该存储过程时将变量值为10，和'10','1003','1004'的字符型变量作为参数传入存储过程时，总是得不到我所需要的结果，又不知如何调试，认为可能是NOT IN传入的参数值不对。
需求就是这样的，不知大家有什么好的建议？

Answer 4

CodeMagic(写错了吧) ：
其实我的ROLEAUTH表中还有一个字段为授权时间，记录每个角色授予什么功能的时间，如果本次用户修改了权限模块并没有删除以前所授权的功能时，授权时间并不作变化。
如果按照你的方法，则将未改变授权的时间也进行了修改。

Answer 5

你在你的过程中把传进去的参数弹出来看看procedure p_grant(vRCode in varchar2,vFCode in varchar2)
as
begin
  raise error_my;
  DELETE FROM TEST WHERE RCODE=vRCode AND FCODE NOT IN (vFCode);
exception
  when error_my then
  raise_application_error('-20008',vFCode);
end;

Answer 6

用pl/sql table类型来解决吧。create package pkg_security as
   type typfcode is table of varchar2 (30)
      index by binary_integer;   procedure p_grant (vrcode in varchar2, vfcode in typfcode);
end pkg_security;
/create package body pkg_security as
   procedure p_grant (vrcode in varchar2, vfcode in typfcode) as
      w_cnt   integer;
   begin
      for i in vfcode.first .. vfcode.last loop
         delete from test
               where rcode = vrcode and fcode not in (vfcode (i));
      end loop;
   end p_grant;
end pkg_security;
/

Answer 7

KingSunSha(弱水三千):
你的方法我现在不知道怎么在Java程序中测试啊。怎么传参数啊！

Answer 8

或者传字符串下来
再用StrToArray 方法转化成数组，再处理。
另外用动态sql 应该也可以
DECLARE
    v_src           VARCHAR2(1000);
    v_dst           VARCHAR2(1000);    FUNCTION IfContain
    (
        i_src       IN  VARCHAR2 ,
        i_dst       IN  VARCHAR2
    )
    RETURN BOOLEAN
    IS
        v_ret       BOOLEAN;        TYPE t_array    IS VARRAY(100) OF VARCHAR2(100);        v_arr_src       t_array := t_array('');
        v_arr_dst       t_array := t_array('');        
v_found BOOLEAN;

FUNCTION StrToArray(i_str IN VARCHAR2) RETURN t_array

IS 
     v_lst_pos       NUMBER;
        v_cur_pos       NUMBER;
v_cnt NUMBER;
       v_arr        t_array := t_array('');
BEGIN
v_cur_pos  := 1;
v_lst_pos :=1 ;
v_cnt := 1;


        LOOP

v_cur_pos := INSTR(i_str,',' , 1,v_cnt ) ;

DBMS_OUTPUT.PUT_LINE ( 'v_cur_pos=' || v_cur_pos  );

IF  v_cur_pos <= 0 THEN

v_cur_pos := LENGTH(i_str);

v_arr(v_cnt ) := SUBSTR( i_str , v_lst_pos  , v_cur_pos - v_lst_pos  + 1 );
EXIT;

ELSE

            v_arr.EXTEND;
            v_arr(v_cnt ) := SUBSTR( i_str , v_lst_pos  , v_cur_pos - v_lst_pos  );
            v_lst_pos   := v_cur_pos + 1 ;

END IF;

v_cnt  := v_cnt  + 1;

        END LOOP;

RETURN v_arr;

EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  RETURN v_arr;
END StrToArray;

    BEGIN       v_arr_src := StrToArray(i_src);
   v_arr_dst := StrToArray(i_dst);
   
   v_ret  := TRUE;
   
   <<loop2>>    
   FOR i IN 1..v_arr_src.COUNT LOOP
       v_found := FALSE;
   
   <<loop1>>
       FOR j IN 1..v_arr_dst.COUNT LOOP
       IF v_arr_src(i) = v_arr_dst(j) THEN
      v_found := TRUE;
  EXIT  loop1 ;
 ELSE
   END IF;  
   END LOOP;
   
      IF NOT v_found THEN
      v_ret := FALSE;
  EXIT loop2;
   END IF;
   
   END LOOP;
   

RETURN v_ret;

        EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('error=*' || SQLERRM || '*');
            RETURN v_ret    ;
    END IfContain;BEGIN    v_src := 'a';
    v_dst := 'a';    IF IfContain(v_src,v_dst) THEN
        DBMS_OUTPUT.PUT_LINE('YES');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO');
    END IF;
END;