CREATE OR REPLACE PROCEDURE test(flag1 IN varchar2,flag2 out varchar2) AS vsvar varchar2(2000); vswhere varchar2(2000); vsstr counter integer;BEGINselect instr(flag1,',') into counter from dual;WHILE counter > 0 LOOP select substr(flag1,1,counter) into vsvar from dual; select replace(flag1,vsvar,'') into flag1 from dual; select replace(vsvar,',','') into vsvar from dual; vsvar := ''''||vsvar||''','; vswhere := vswhere || vsvar; counter := 0; select instr(flag1,',') into counter from dual; if counter < 0 then counter := 0 ; end if; END LOOP ;vswhere :=' in ('||vswhere||')' ;flag2 :=vswhere;END;红字这一句有什么问题吗?老是执行不过去
select replace(flag1,vsvar,'') into flag1 from dual; 你的声明: CREATE OR REPLACE PROCEDURE test(flag1 IN varchar2,flag2 out varchar2) AS 你要修改flag1入参的值,必须声明为in out CREATE OR REPLACE PROCEDURE test(flag1 IN OUT varchar2,flag2 out varchar2) AS
vsvar varchar2(2000);
vswhere varchar2(2000);
vsstr
counter integer;BEGINselect instr(flag1,',') into counter from dual;WHILE counter > 0 LOOP select substr(flag1,1,counter) into vsvar from dual;
select replace(flag1,vsvar,'') into flag1 from dual;
select replace(vsvar,',','') into vsvar from dual;
vsvar := ''''||vsvar||''',';
vswhere := vswhere || vsvar;
counter := 0;
select instr(flag1,',') into counter from dual;
if counter < 0 then
counter := 0 ;
end if;
END LOOP ;vswhere :=' in ('||vswhere||')' ;flag2 :=vswhere;END;红字这一句有什么问题吗?老是执行不过去
2、字符串不支持,
字符串可以vswhere := vswhere || vsvar;
或者使用函数vswhere := concat(vswhere,vsvar);
你的声明:
CREATE OR REPLACE PROCEDURE test(flag1 IN varchar2,flag2 out varchar2) AS
你要修改flag1入参的值,必须声明为in out
CREATE OR REPLACE PROCEDURE test(flag1 IN OUT varchar2,flag2 out varchar2) AS
vsvar :=''''||vsvar||''','; --'' 为 '
vswhere := vswhere||vsvar; --字符连接符是||
test(flag1 IN varchar2,flag2 out varchar2)
flag1 改成可赋值可被赋值的类型
test(flag1 IN out varchar2,flag2 out varchar2)