急!请大虾示范where子句带in参数的存储过程!

Question

这个试穿入字符串的。
16:29:21 SQL> create or replace procedure pro_in(v_in varchar2) as
16:29:36   2  v_tb tb%rowtype;
16:29:36   3  begin
16:29:36   4  execute immediate 'select * from tb where col2 in ('''||v_in||''') and rownum=1'
16:29:36   5  into v_tb;
16:29:36   6  dbms_output.put_line(v_tb.col1||','||v_tb.col2);
16:29:41   7  end pro_in;
16:29:47   8  /过程已创建。已用时间:  00: 00: 00.94
16:29:48 SQL> select * from tb;COL1       COL2                           COLNEW
---------- ------------------------------ --------------------
1          aaa                            aaa
1          aaa                            aaa
1          aaa                            aaa
1          bba                            bba
1          bbb                            bbb
1          bbc                            bbc
1          bbd                            bbd
1          ccc                            ccc
1          TEMP                           TEMP
1          ddd                            ddd
1          eee                            eeeCOL1       COL2                           COLNEW
---------- ------------------------------ --------------------
1          2                              3
1          2                              3
1          2                              3
1          3                              4
1          TEMP
1          TEMP
c          TEMP
a          aa                             aaa已选择19行。已用时间:  00: 00: 00.94
16:29:56 SQL> exec pro_in('abc'',''ccc');
1,cccPL/SQL 过程已成功完成。已用时间:  00: 00: 00.46
16:30:33 SQL>

Answer 1

CREATE OR REPLACE procedure rbtsdp007(UserMobile   in varchar2,
                                      callerFlag out number
                                      )
----------------------用户是否注册---------------------------------
as
     TYPE crcursor is REF CURSOR;
 MobileNo varchar2(21);  ----主叫号码
 TempMobile varchar2(21);----取出表中主叫号码
 String   varchar2(10);  ----取主叫号码前两位
 crcursor1 crcursor;     
Begin
 callerFlag:=0;
 MobileNo:=UserMobile;
select SUBSTR(MobileNo,1,2) into string from dual;
       if  String='86' then
       MobileNo:=SUBSTR(MobileNo,3,11);
           String:='';
   end if;
open crcursor1 for
select dcusermobile  from rbtsdt001 where dcusermobile=MobileNo;
   fetch crcursor1 into TempMobile;
close crcursor1;
       if TempMobile is null then 
       callerFlag:=0;
   return;
else
   callerFlag:=1;
   return;
   end if;    
           
End;
/