oracle中多条数据添加的问题

[penglingling0212]

因为主键的原因，在里面写了个循环，写是写好了，可是传的参数，他不能识别，就是我传的这个P_ITEMID 参数是一个拼接的字符串，如‘12456’，‘54215’，‘54214’....!一次性传50条！为什么不能成功添加呢？求解~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~急用！！！！！！！！

create or replace procedure SP_Insert(
P_ITEMID CHAR,
P_NEWBARGROUPID CHAR,
P_NEWBARID CHAR,
P_STATUS CHAR,
P_CREATEUSERID CHAR,
P_CREATEDATE DATE,
NUM OUT NUMBER) AS
SQLSTRING VARCHAR2(30000);
NO NUMBER;
CURSOR C2 IS
SELECT ITEMID,
COMPANYID,
RETAILID,
ORIGINALBARGROUPID,
ORIGINALBARID
FROM (SELECT A.COMPANYID AS COMPANYID,
A.RETAILID AS RETAILID,
A.ITEMID AS ITEMID,
A.BARGROUPID AS ORIGINALBARGROUPID,
A.BARID AS ORIGINALBARID
FROM A
WHERE A.LQT77_ITEMID IN (P_LQT72_ITEMID)
UNION ALL

SELECT B.ITEMID ASITEMID,
B.COMPANYID ASCOMPANYID,
B.RETAILID ASRETAILID,
B.BARGROUPID ASORIGINALBARGROUPID,
B.BARID ASORIGINALBARID
FROM B
WHERE B.ITEMID IN (P_ITEMID));
REC C2%ROWTYPE;
BEGIN

OPEN C2;
LOOP
--循环开始
FETCH C2
INTO REC.ITEMID,
REC.COMPANYID,
REC.RETAILID,
REC.ORIGINALBARGROUPID,
REC.ORIGINALBARID;
EXIT WHEN C2%NOTFOUND;
IF (REC.ORIGINALBARGROUPID IS NULL) THEN
REC.ORIGINALBARGROUPID := NULL;
END IF;
IF (REC.ORIGINALBARID IS NULL) THEN
REC.ORIGINALBARID := NULL;
END IF;
SELECT COUNT(NO) INTO NO FROM STOREADJUSTHIS;
IF NO1 = 0 THEN
NO1 := 1;
ELSE
SELECT MAX(NO) INTO NO FROM STOREADJUSTHIS;
NO1 := NO1 + 1;
END IF;

INSERT INTO STORE
(NO1,
COMPANYID,
RETAILID,
ITEMID,
ORIGINALBARGROUPID,
ORIGINALBARID,
NEWBARGROUPID,
NEWBARID,
STATUS,
DELETEDFLG,
CREATEUSERID,
CREATEDATE,
UPDATEUSERID,
UPDATEDATE)
VALUES
(NO,
REC.COMPANYID,
REC.RETAILID,
REC.ITEMID,
REC.ORIGINALBARGROUPID,
REC.ORIGINALBARID,
P_NEWBARGROUPID,
P_NEWBARID,
P_STATUS,
'00',
P_CREATEUSERID,
P_CREATEDATE,
P_CREATEUSERID,
P_CREATEDATE
);

COMMIT;
END LOOP;
CLOSE C2;
IF SQL%ROWCOUNT = 0 THEN
SELECT 0 INTO NUM FROM DUAL;
ELSE
SELECT 1 INTO NUM FROM DUAL;
END IF;
END;

[u010412956]

必须要传这种格式：‘12456’，‘54215’，‘54214’？？ 能否 传 12456,54215,54214这样的？

[u010412956]

drop table t_test;
create table t_test(a varchar2(10));
insert into t_test values('a1');
insert into t_test values('a2');
insert into t_test values('a3');
insert into t_test values('a4');
insert into t_test values('1');
insert into t_test values('2');
insert into t_test values('3');
commit;
/
create or replace procedure p_test(str varchar2) is
begin

  for i in (with t1 as
               (select regexp_substr(str, '[^,]+', 1, level) a
                 from dual
               connect by level <= regexp_count(str, ',') + 1)
              select t.* from t_test t, t1 where t.a = trim(t1.a)) loop
  
    dbms_output.put_line(i.a);
  end loop;

end;

传入a1,a2,1 可得到结果。

[u010412956]

上面那个过程如果传入字符就错了，应该用：

create or replace procedure p_test(str varchar2) is
begin

  for i in (with t1 as
               (select regexp_substr(str, '[^,]+', 1, level) a
                 from dual
               connect by level <= regexp_count(str, ',') + 1)
              select t.* from t_test t, t1 where t.a = t1.a) loop
    dbms_output.put_line(i.a);
  end loop;

end;

[penglingling0212]

没明白你想表达的意思

[u010412956]

引用 4 楼 penglingling0212 的回复:

没明白你想表达的意思

我的意思就是如果你传入的是 12456，54215，54214，就将其拆分为 12456 54215 54214 变为3行。。。。 然后去跟你原有的B.ITEMID 进行关联。

[penglingling0212]

还要拆分？没懂，为啥我还要把他拆了？就是应为拼一起查询简单一些才设置传进来是个拼好的字符串

[penglingling0212]

自己解决了，拼接一下sql语句，用EXECUTE IMMEDIATE，出现报错的原因是，传进来的 P_ITEMID 是个字符串类型，无论你是传两个还是三个在in里面，数据库都会把他当做一个整体，所以要用EXECUTE IMMEDIATE拼接一下sql语句就可以了！

[嘛哩嘛哩吽]

学习！取经