oracle连接符问题

Question

大家好，初学数据库。下边问题请各位帮忙下
                               select a.serialno serialno,
                                      a.dealid dealid,
                                      b.MOBILEPHONE MOBILEPHONE,
                                      b.EMAILADDRESS EMAILADDRESS,
                                      b.STAFFNAME STAFFNAME,
                                      b.SKILLNAME SKILLNAME,
                                      max(dealid) over(partition by serialno) rn ,
                                      a.distillstaff distillstaff,
                                      a.handleskillid handleskillid,
                                      a.lasthandlehourlimit lasthandlehourlimit,
                                      a.musthandledate musthandledate,
                                      (a.musthandledate - sysdate)*24 as leftLimitTime
                                 from t_wf_process a,t_dz_contact b
                                where a.dealid in('||i_dealids||') 
                                  and a.handleskillid = b.SKILLID
                                  and a.distillstaff = b.STAFFNO
其中红色部分存在问题，在有in关键字的时候如何用连接符将语句连接起来？

Answer 1

'string1' || 'string2'你肯定有哪写错了

Answer 2

SQL = 'select a.serialno serialno,
  a.dealid dealid,
  b.MOBILEPHONE MOBILEPHONE,
  b.EMAILADDRESS EMAILADDRESS,
  b.STAFFNAME STAFFNAME,
  b.SKILLNAME SKILLNAME,
  max(dealid) over(partition by serialno) rn ,
  a.distillstaff distillstaff,
  a.handleskillid handleskillid,
  a.lasthandlehourlimit lasthandlehourlimit,
  a.musthandledate musthandledate,
  (a.musthandledate - sysdate)*24 as leftLimitTime
  from t_wf_process a,t_dz_contact b
  where a.dealid in(' || i_dealids || ') 
  and a.handleskillid = b.SKILLID
  and a.distillstaff = b.STAFFNO';EXECUTE IMMEDIATE SQL;

Answer 3

修正一下：字符串赋值SQL := 'select a.serialno serialno,
  a.dealid dealid,
  b.MOBILEPHONE MOBILEPHONE,
  b.EMAILADDRESS EMAILADDRESS,
  b.STAFFNAME STAFFNAME,
  b.SKILLNAME SKILLNAME,
  max(dealid) over(partition by serialno) rn ,
  a.distillstaff distillstaff,
  a.handleskillid handleskillid,
  a.lasthandlehourlimit lasthandlehourlimit,
  a.musthandledate musthandledate,
  (a.musthandledate - sysdate)*24 as leftLimitTime
  from t_wf_process a,t_dz_contact b
  where a.dealid in(' || i_dealids || ')  
  and a.handleskillid = b.SKILLID
  and a.distillstaff = b.STAFFNO';EXECUTE IMMEDIATE SQL;

Answer 4

对的，这种方式是可以，但有个问题。这是我的整个过程create or replace procedure p_dz_handlehourlimit
(
  i_dealids     in    varchar2,
  i_sendTime    in    date,
  i_content     in   varchar2,
  o_ret          OUT varchar2
)
as
  v_sql                  varchar2(2000);
  v_content              varchar2(2000);
  v_serialno             varchar2(20);
  v_mobilePhone          varchar2(20);
  v_email                varchar2(20);
  v_handlehourLimit      varchar2(20);
  v_musthandleDate       date;
  v_logid                varchar2(20);
  v_dealids              varchar2(20);
  v_sendTime             date;
  v_Cursor               pack_service.t_RetDataSet;     -- 查询结果集
BEGIN
      
            
        if i_sendTime is null then 
           v_sendTime := sysdate;
        else
           v_sendTime := i_sendTime;
        end if;
        
      /* insert into test2 values('00000',i_dealids);
       commit;
        */
      --  v_dealids := i_dealids;
        
        for tmpSet in( select serialno,
                              distillstaff,
                              handleskillid,
                              lasthandlehourlimit,
                              musthandledate,
                              leftLimitTime,
                              MOBILEPHONE,
                              EMAILADDRESS,
                              STAFFNAME,
                              SKILLNAME
                         from (select a.serialno serialno,
                                      a.dealid dealid,
                                      b.MOBILEPHONE MOBILEPHONE,
                                      b.EMAILADDRESS EMAILADDRESS,
                                      b.STAFFNAME STAFFNAME,
                                      b.SKILLNAME SKILLNAME,
                                      max(dealid) over(partition by serialno) rn ,
                                      a.distillstaff distillstaff,
                                      a.handleskillid handleskillid,
                                      a.lasthandlehourlimit lasthandlehourlimit,
                                      a.musthandledate musthandledate,
                                      (a.musthandledate - sysdate)*24 as leftLimitTime
                                 from t_wf_process a,t_dz_contact b
                                where a.dealid = i_dealids
                                  and a.handleskillid = b.SKILLID
                                  and a.distillstaff = b.STAFFNO)
                         where rn = dealid) loop
                         
                  v_serialno :=  tmpSet.Serialno;
                  v_mobilePhone := tmpSet.Mobilephone;
                  v_email := tmpSet.Emailaddress;      
                  v_handlehourLimit := tmpset.lasthandlehourlimit;
                  v_musthandleDate := tmpset.musthandledate;
                  v_sendTime := v_sendTime;    
                  v_content := '你好！你有工单需要处理，工单流水号为：'||v_serialno||'';
                  v_content := v_content || ',必须处理时间为：'||v_musthandleDate||'';
                  v_content := v_content || ',剩余处理时间为：'||v_handlehourLimit||'时';
                  
                  p_dz_process2ms_tmp(v_content,
                                      v_mobilePhone,
                                      v_email,
                                      v_serialno,
                                      v_handlehourLimit,
                                      v_sendTime,
                                      v_musthandleDate
                  ); 
                         
        
        end loop;
    
      commit;
      o_ret := 0;
  
  EXCEPTION
    WHEN OTHERS THEN
    -- 未找到数据（工单没有附件）
    o_Ret := 1;
    ROLLBACK;    --错误日志记录END p_dz_handlehourlimit;