类似如下的过程,目的是将IN_ARRAY=1,2,3,4类似这样的字符作为入参,从表A中查询出记录循环插入表bb中,整了一下午没有搞定,提示类型问题,烦请高手指点如何解决,不胜感激。
create package body "abc" is
procedure a(IN_ARRAY) is
CURSOR ARRAY_CURSOR IS
select *From a where a.id in (IN_ARRAY)
BEGIN
FOR v_array in ARRAY_CURSOR loop
insert into bb(num)
values(v_array.num);
end loop;
end;
end a;
end abc;
create package body "abc" is
procedure a(IN_ARRAY) is
CURSOR ARRAY_CURSOR IS
select *From a where a.id in (IN_ARRAY)
BEGIN
FOR v_array in ARRAY_CURSOR loop
insert into bb(num)
values(v_array.num);
end loop;
end;
end a;
end abc;
v_array.num 改成to_number(v_array.num)试试
IS
v_FrendsMobile_str CLOB;
v_FrendsName_str CLOB;
v_FrendMobile VARCHAR2(20);
v_FrendName VARCHAR2(100);
v_sql VARCHAR2(1000);
v_loop NUMBER(18,0);
BEGIN
v_FrendsMobile_str := ','||v_FrendsMobile||',';
v_FrendsName_str := ','||v_FrendsName||',';
v_sql := 'INSERT INTO MOBILEFRENDS_TMP_PROC(MOBILE,FRENDMOBILE,FRENDNAME,ID) VALUES(:v_Mobile,:v_FrendMobile,:v_FrendName,MOBILEFRENDS_TMP_PROC_SEQ.NEXTVAL)';
v_loop := LENGTH(v_FrendsMobile_str)-LENGTH(REPLACE(v_FrendsMobile_str,',',''));
FOR i IN 2 .. v_loop loop
v_FrendMobile := SUBSTR(v_FrendsMobile_str,INSTR(v_FrendsMobile_str,',',1,i-1)+1,
INSTR(v_FrendsMobile_str,',',1,i)-INSTR(v_FrendsMobile_str,',',1,i-1)-1);
v_FrendName := SUBSTR(v_FrendsName_str,INSTR(v_FrendsName_str,',',1,i-1)+1,
INSTR(v_FrendsName_str,',',1,i)-INSTR(v_FrendsName_str,',',1,i-1)-1);
EXECUTE IMMEDIATE v_sql USING v_Mobile, v_FrendMobile, v_FrendName;
END LOOP; DELETE FROM MOBILEFRENDS_TMP_PROC T1
WHERE NOT EXISTS (SELECT 1 FROM MOBILEFRENDS_TMP_PROC T2 GROUP BY T2.MOBILE, T2.FRENDMOBILE
HAVING MAX(ID)=T1.ID)
OR T1.FRENDMOBILE IS NULL; INSERT INTO MOBILEFRENDS(MOBILE,FRENDMOBILE,FRENDNAME) SELECT T.MOBILE, T.FRENDMOBILE, T.FRENDNAME
FROM MOBILEFRENDS_TMP_PROC T
WHERE NOT EXISTS (SELECT 1 FROM MOBILEFRENDS M WHERE M.MOBILE=T.MOBILE AND M.FRENDMOBILE=T.FRENDMOBILE); COMMIT;
END mobileFrends_in_proc;
--------------------------- 优化版本 --------------------------------------------
-- ALTER TABLE MOBILEFRENDS_TMP_PROC ADD ID NUMBER(18,0);
-- CREATE SEQUENCE MOBILEFRENDS_TMP_PROC_SEQ START WITH 1 NOMAXVALE CACHE 20;CREATE OR REPLACE PACKAGE pkg_mobileFrends_op
AS
TYPE myrctype IS REF CURSOR; --定义游标用以 查询 返回好友手机号
PROCEDURE mobileFrends_in_proc(v_Mobile IN VARCHAR2, v_FrendsMobile IN CLOB, v_FrendsName IN CLOB); --添加 好友手机号
PROCEDURE mobileFrends_de_proc(v_Mobile IN VARCHAR2, v_FrendsMobile IN CLOB); --删除 部分好友手机号
PROCEDURE mobileFrends_deall_proc(v_Mobile IN VARCHAR2); --删除 所有好友手机号
PROCEDURE mobileFrends_se_proc(v_Mobile IN VARCHAR2, p_rc OUT myrctype); --查询 好友手机号
PROCEDURE mobileFrends_seb_proc(v_FromMobile IN VARCHAR2, v_ToMobile IN VARCHAR2, p_rc OUT myrctype); --查询 好友手机号(按 手机号 区间)
PROCEDURE mobileFrends_se2_proc(v_Mobile IN VARCHAR2, p_rc OUT myrctype); --查询 好友手机号,包括其好友状态
PROCEDURE mobileFrends_se2b_proc(v_FromMobile IN VARCHAR2, v_ToMobile IN VARCHAR2, p_rc OUT myrctype); --查询 好友手机号,包括其好友状态(按 手机号 区间)
PROCEDURE mobileFrends_se3_proc(v_FrendMobile IN VARCHAR2, p_rc OUT myrctype); --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)
PROCEDURE mobileFrends_se3b_proc(v_FromFrendMobile IN VARCHAR2, v_ToFrendMobile IN VARCHAR2, p_rc OUT myrctype);
--根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)(按 手机号 区间)
END pkg_mobileFrends_op;
/-------CREATE OR REPLACE PACKAGE BODY pkg_mobileFrends_op
AS
--添加 好友手机号
PROCEDURE mobileFrends_in_proc(v_Mobile IN VARCHAR2, v_FrendsMobile IN CLOB, v_FrendsName IN CLOB)
IS
v_FrendsMobile_str CLOB;
v_FrendsName_str CLOB;
v_FrendMobile VARCHAR2(20);
v_FrendName VARCHAR2(100);
v_sql VARCHAR2(1000);
v_loop NUMBER(18,0);
BEGIN
v_FrendsMobile_str := ','||v_FrendsMobile||',';
v_FrendsName_str := ','||v_FrendsName||',';
v_sql := 'INSERT INTO MOBILEFRENDS_TMP_PROC(MOBILE,FRENDMOBILE,FRENDNAME,ID) VALUES(:v_Mobile,:v_FrendMobile,:v_FrendName,MOBILEFRENDS_TMP_PROC_SEQ.NEXTVAL)';
v_loop := LENGTH(v_FrendsMobile_str)-LENGTH(REPLACE(v_FrendsMobile_str,',',''));
FOR i IN 2 .. v_loop loop
v_FrendMobile := SUBSTR(v_FrendsMobile_str,INSTR(v_FrendsMobile_str,',',1,i-1)+1,
INSTR(v_FrendsMobile_str,',',1,i)-INSTR(v_FrendsMobile_str,',',1,i-1)-1);
v_FrendName := SUBSTR(v_FrendsName_str,INSTR(v_FrendsName_str,',',1,i-1)+1,
INSTR(v_FrendsName_str,',',1,i)-INSTR(v_FrendsName_str,',',1,i-1)-1);
EXECUTE IMMEDIATE v_sql USING v_Mobile, v_FrendMobile, v_FrendName;
END LOOP; DELETE FROM MOBILEFRENDS_TMP_PROC T1
WHERE NOT EXISTS (SELECT 1 FROM MOBILEFRENDS_TMP_PROC T2 GROUP BY T2.MOBILE, T2.FRENDMOBILE
HAVING MAX(ID)=T1.ID)
OR T1.FRENDMOBILE IS NULL; INSERT INTO MOBILEFRENDS(MOBILE,FRENDMOBILE,FRENDNAME) SELECT T.MOBILE, T.FRENDMOBILE, T.FRENDNAME
FROM MOBILEFRENDS_TMP_PROC T
WHERE NOT EXISTS (SELECT 1 FROM MOBILEFRENDS M WHERE M.MOBILE=T.MOBILE AND M.FRENDMOBILE=T.FRENDMOBILE); COMMIT;
END mobileFrends_in_proc; --删除 部分好友手机号
PROCEDURE mobileFrends_de_proc(v_Mobile IN VARCHAR2, v_FrendsMobile IN CLOB)
IS
v_FrendsMobile_str CLOB;
v_FrendMObile VARCHAR2(20);
v_sql VARCHAR2(400);
v_loop NUMBER(18,0);
BEGIN
v_FrendsMobile_str := ','||v_FrendsMobile||',';
v_sql := 'INSERT INTO MOBILEFRENDS_TMP_PROC(MOBILE,FRENDMOBILE) VALUES(:v_Mobile,:v_FrendMobile)';
v_loop := LENGTH(v_FrendsMobile_str)-LENGTH(REPLACE(v_FrendsMobile_str,',',''));
FOR i IN 2 .. v_loop loop
v_FrendMobile := SUBSTR(v_FrendsMobile_str,INSTR(v_FrendsMobile_str,',',1,i-1)+1,
INSTR(v_FrendsMobile_str,',',1,i)-INSTR(v_FrendsMobile_str,',',1,i-1)-1);
EXECUTE IMMEDIATE v_sql USING v_Mobile, v_FrendMobile;
END LOOP; EXECUTE IMMEDIATE 'DELETE FROM MOBILEFRENDS T1 WHERE T1.MOBILE=:v_Mobile
AND EXISTS(SELECT 1 FROM MOBILEFRENDS_TMP_PROC T2 WHERE T2.FRENDMOBILE=T1.FRENDMOBILE)'
USING v_Mobile; COMMIT;
END mobileFrends_de_proc; --删除 所有好友手机号
PROCEDURE mobileFrends_deall_proc(v_Mobile VARCHAR2)
IS
BEGIN
IF v_Mobile IS NOT NULL THEN
EXECUTE IMMEDIATE 'DELETE FROM MOBILEFRENDS WHERE MOBILE=:v_Mobile' USING v_Mobile;
END IF;
COMMIT;
END mobileFrends_deall_proc; --查询 好友手机号
PROCEDURE mobileFrends_se_proc(v_Mobile IN VARCHAR2, p_rc OUT myrctype)
IS
BEGIN
OPEN p_rc FOR 'SELECT FRENDMOBILE FROM MOBILEFRENDS WHERE MOBILE=:v_Mobile' USING v_Mobile;
END mobileFrends_se_proc; --查询 好友手机号2(按 手机号区间)
PROCEDURE mobileFrends_seb_proc(v_FromMobile IN VARCHAR2, v_ToMobile IN VARCHAR2, p_rc OUT myrctype)
IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'SELECT FRENDMOBILE FROM MOBILEFRENDS WHERE MOBILE>=:v_FromMobile AND MOBILE<=:v_ToMobile';
OPEN p_rc FOR v_sql USING v_FromMobile, v_ToMObile;
END mobileFrends_seb_proc; --查询 好友手机号,包括其好友状态
PROCEDURE mobileFrends_se2_proc(v_mobile in varchar2,p_rc out myrctype)
IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'SELECT M.MOBILE,M.FRENDMOBILE,NVL(U.STATE,-1) AS U_STATE,U.SIGN,U.MD5 FROM MOBILEFRENDS M LEFT JOIN U_STATE U ON M.FRENDMOBILE=U.MOBILE WHERE M.MOBILE=:v_Mobile';
OPEN p_rc FOR v_sql USING v_Mobile;
END mobileFrends_se2_proc; --查询 好友手机号,包括其好友状态2(按 手机号区间)
PROCEDURE mobileFrends_se2b_proc(v_FromMobile IN VARCHAR2, v_ToMobile IN VARCHAR2, p_rc OUT myrctype)
IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'SELECT M.MOBILE,M.FRENDMOBILE,NVL(U.STATE,-1) AS U_STATE, U.SIGN, U.MD5, U.CUSTOMIZE_STATUS FROM MOBILEFRENDS M LEFT JOIN U_STATE U ON M.FRENDMOBILE=U.MOBILE WHERE M.MOBILE>=:v_FromMobile AND M.MOBILE<=:v_ToMobile';
OPEN p_rc FOR v_sql USING v_FromMobile, v_ToMobile;
END mobileFrends_se2b_proc; --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)
PROCEDURE mobileFrends_se3_proc(v_FrendMobile IN VARCHAR2,p_rc OUT myrctype)
IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'SELECT M.MOBILE, M.FRENDMOBILE FROM MOBILEFRENDS M WHERE M.FRENDMOBILE=:v_FrendMobile';
OPEN p_rc FOR v_sql USING v_FrendMobile;
END mobileFrends_se3_proc; --根据好友手机号 查询 宿主手机号2(查看有哪些人将这个手机号加为了好友) (按 手机号区间)
PROCEDURE mobileFrends_se3b_proc(v_fromFrendMobile in varchar2, v_toFrendMobile in varchar2, p_rc out myrctype)
IS
v_sql VARCHAR2(400);
BEGIN
v_sql := 'SELECT M.MOBILE, M.FRENDMOBILE FROM MOBILEFRENDS M WHERE EXISTS (SELECT T.MOBILE FROM U_STATE T
WHERE T.MOBILE=M.FRENDMOBILE AND T.MOBILE>=:v_FromFrendMobile AND T.MOBILE<=:v_ToFrendMobile)';
OPEN p_rc FOR v_sql USING v_fromFrendMobile, v_toFrendMobile;
END mobileFrends_se3b_proc;END pkg_mobileFrends_op;
/