oracle中多条数据添加的问题
因为主键的原因，在里面写了个循环，写是写好了，可是传的参数，他不能识别，就是我传的这个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;
Oracle select
[解决办法]
上面那个过程如果传入字符就错了，应该用：
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;