oracle 存储过程报错

Question

其中strSendNo为变量值由create or replace procedure P_CREATE_CARPLAN
(    v_strSendNo     varchar2)其v_strSendNo为传入参数值,传入值SB001,SB002将strSendNo转换为'SB001','SB002'给变量v_strTrueSendNo
select sendwkno into vs_strSEndWkNo from send where sendno in (v_strTrueSendNo) and rownum=1;程序执行到上一步报错请教各位如何解决这个问题!

Answer 1

create or replace procedure P_CREATE_CARPLAN 
(     v_strSendNo    varchar2 ,
    vs_strSEndWkNo out varchar2) 
as
temp_sql varchar2(200);begin 
   temp_sql:='select sendwkno from send where sendno in ('||v_strTrueSendNo||') and rownum=1';
   execute immediate temp_sql into  vs_strSEndWkNo;
end ;

Answer 2

老大,这样确实可以!
但是若是游标怎么解决呢?  SendNo in (v_strTrueSendNo ) CURSOR CUR_CARPALN (strSendNo varchar2,strCustNo varchar2)
    IS
select cast(sum(A.Volume)/1000000 as numeric(15,2)) as Volume ,cast(sum(A.Weight)/1000 as numeric(15,2))as Weight ,
ceil(sum(A.Qbox))as Qbox from (
select a.Mcustno,a.Custno,a.sendno,a.Artno,a.packing, sum(a.Itemqty) as qty,(1.0*sum(a.Itemqty)/a.packing) as Qbox,
 sum(a.Itemqty)*b.volumn as Volume,sum(a.Itemqty)*b.unitwgt as Weight
 from sendlist a inner join v_defartpack b on  a.Mcustno=b.Mcustno and a.artno=b.artno and  a.Packing=b.QPacking
 WHERE SENDIDTYPE='P' and SendNo in (v_strTrueSendNo ) AND LOCNO=vs_strLocNo and Custno=strCustNo
  Group by a.Mcustno,a.Custno,a.sendno,a.Artno,a.packing,b.volumn,b.unitwgt)  A
   Group by A.Mcustno,A.Custno,A.sendno Order by A.sendno ;

Answer 3

遇到这种情况我会用个sql把字符串分拆
SELECT     TO_NUMBER
                                          (DECODE
                                              (LENGTH (in_promo_id),
                                               6, in_promo_id,
                                               SUBSTR
                                                  (in_promo_id,
                                                   DECODE
                                                         (ROWNUM,
                                                          1, 1,
                                                            INSTR
                                                                 (in_promo_id,
                                                                  ',',
                                                                  1,
                                                                  ROWNUM - 1
                                                                 )
                                                          + 1
                                                         ),
                                                   6
                                                  )
                                              )
                                          ) promo_id
                                  FROM DUAL
                            CONNECT BY ROWNUM <=
                                            LENGTH (in_promo_id)
                                          - LENGTH (REPLACE (in_promo_id,
                                                             ',',
                                                             ''
                                                            )
                                                   )
                                          + 1这是我的一个分拆语句
假设输入的是
100000,100001,100002,100003
用这个语句后，生成
100000
100001
100002
100003
这样一个结果集
然后再把需要查找的数据和此数据集关联

Answer 4

假设你的输入是固定5位长度的比如
SB001,SB002
把我的SQL略改一下
select 
                                          DECODE
                                              (LENGTH (v_strSendNo),
                                               5, v_strSendNo,
                                               SUBSTR
                                                  (v_strSendNo,
                                                   DECODE
                                                         (ROWNUM,
                                                          1, 1,
                                                            INSTR
                                                                 (v_strSendNo,
                                                                  ',',
                                                                  1,
                                                                  ROWNUM - 1
                                                                 )
                                                          + 1
                                                         ),
                                                   5
                                                  )
                                              )
                                           strSendNo 
                                  FROM DUAL
                            CONNECT BY ROWNUM <=
                                            LENGTH (v_strSendNo )
                                          - LENGTH (REPLACE (v_strSendNo ,
                                                             ',',
                                                             ''
                                                            )
                                                   )
                                          + 1
这样就能输出
SB001
SB002CURSOR CUR_CARPALN (strSendNo varchar2,strCustNo varchar2) 
    IS 
SELECT   CAST (SUM (a.volume) / 1000000 AS NUMERIC (15, 2)) AS volume,
         CAST (SUM (a.weight) / 1000 AS NUMERIC (15, 2)) AS weight,
         CEIL (SUM (a.qbox)) AS qbox
    FROM (SELECT   a.mcustno, a.custno, a.sendno, a.artno, a.packing,
                   SUM (a.itemqty) AS qty,
                   (1.0 * SUM (a.itemqty) / a.packing) AS qbox,
                   SUM (a.itemqty) * b.volumn AS volume,
                   SUM (a.itemqty) * b.unitwgt AS weight
              FROM sendlist a,
                   v_defartpack b,
                   (SELECT     DECODE
                                  (LENGTH (v_strsendno),
                                   5, v_strsendno,
                                   SUBSTR (v_strsendno,
                                           DECODE (ROWNUM,
                                                   1, 1,
                                                     INSTR (v_strsendno,
                                                            ',',
                                                            1,
                                                            ROWNUM - 1
                                                           )
                                                   + 1
                                                  ),
                                           5
                                          )
                                  ) strsendno
                          FROM DUAL
                    CONNECT BY ROWNUM <=
                                    LENGTH (v_strsendno)
                                  - LENGTH (REPLACE (v_strsendno, ',', ''))
                                  + 1) c
             WHERE a.mcustno = b.mcustno
               AND a.artno = b.artno
               AND a.packing = b.qpacking
               AND sendidtype = 'P'
               AND sendno = c.strsendno
               AND locno = vs_strlocno
               AND custno = strcustno
          GROUP BY a.mcustno,
                   a.custno,
                   a.sendno,
                   a.artno,
                   a.packing,
                   b.volumn,
                   b.unitwgt) a
GROUP BY a.mcustno, a.custno, a.sendno
ORDER BY a.sendno;

Answer 5

先定义一个cursor的ref变量 
然后open cur for str。 
事先这个str整理好就行了。 
如何处理v_strTrueSendNo呢？ 
直接替换,-〉',' 
方法'''' ¦ ¦ replace(v_strTrueSendNo,',',''',''') ¦ ¦ '''' 
给个例子 
CREATE OR REPLACE PROCEDURE CURNAME 
IS 
aaa sys_refcursor; 
str varchar2(4000); 
vp varchar2(4000); 
BEGIN 
  vp :='12345,67890'; 
  vp :='''' ¦ ¦ replace(vp,',',''',''') ¦ ¦ ''''; 
  str := 'select to_char(sysdate,''YYYYMMDD'') from dual where ''67890'' in(' ¦ ¦ vp ¦ ¦ ')'; 
  open aaa for str; 
  fetch aaa into vp; 
  close aaa; 
  dbms_output.put_line(vp); 
END;