Oracle 数组问题

Question

在包中声明两个数组:
TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
TYPE INT_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;在insert 语句里取数组的元素添加. 数据都是通过文件导入进来的,C#里使用string[]传递.有一个问题:字段的类型是varchar2,如果我的文件数据里对应该字段有一个数据是数字类型,那么其它非数字类型的数据就无法插入.好像是被默认转为了INT_ARRAY 类型数组.比如:有一组数据     张三   aaaa     bbbb
                             李四   aaaa     bbbb
                              666   aaaa     bbbb
那么导入到数据库就只有666 这一组数据.而张三,李四两行却为空.  求原因,解决方案

Answer 1

简单说:某个列是varchar2类型的,如"张三",但如果我导入的数据有一个是数字,如:"123",那么其它字符类型的数据就无法添加.也就是"123"可以写进去,而"张三"无法写进去.

Answer 2

create or replace package importDealer
as
TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE INT_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE ADDDEALER
(
         dCID VARCHAR2,
          arrDEALERNAME IN VARCHAR2_ARRAY,
          arrDEALERTYPE IN INT_ARRAY,
          arrDEALERID IN VARCHAR2_ARRAY,
          arrDEALERPROID IN VARCHAR2_ARRAY,
          arrDEALERCITY IN VARCHAR2_ARRAY,
          arrDEALERCOUNTY IN VARCHAR2_ARRAY,
          arrDEALERLINKMAN IN VARCHAR2_ARRAY,
          arrDEALERLINKTEL IN VARCHAR2_ARRAY,
          arrDEALERPHONE IN VARCHAR2_ARRAY,
          arrDEALERFAX IN VARCHAR2_ARRAY,
          arrDEALERADDRESS IN VARCHAR2_ARRAY,
          arrDEALERNOTE IN VARCHAR2_ARRAY, 
          SESSNUM OUT NUMBER,
          FALSENUM OUT NUMBER,
          REPEATNUM OUT NUMBER,
          REPEATIDNUM OUT NUMBER,
          REPEATID OUT VARCHAR2
);END importDealer;CREATE OR REPLACE PACKAGE BODY importDealer
AS
PROCEDURE ADDDEALER
(
          dCID VARCHAR2,
          arrDEALERNAME IN VARCHAR2_ARRAY,
          arrDEALERTYPE IN INT_ARRAY,
          arrDEALERID IN VARCHAR2_ARRAY,
          arrDEALERPROID IN VARCHAR2_ARRAY,
          arrDEALERCITY IN VARCHAR2_ARRAY,
          arrDEALERCOUNTY IN VARCHAR2_ARRAY,
          arrDEALERLINKMAN IN VARCHAR2_ARRAY,
          arrDEALERLINKTEL IN VARCHAR2_ARRAY,
          arrDEALERPHONE IN VARCHAR2_ARRAY,
          arrDEALERFAX IN VARCHAR2_ARRAY,
          arrDEALERADDRESS IN VARCHAR2_ARRAY,
          arrDEALERNOTE IN VARCHAR2_ARRAY, 
          SESSNUM OUT NUMBER,
          FALSENUM OUT NUMBER,
          REPEATNUM OUT NUMBER,
          REPEATIDNUM OUT NUMBER,
          REPEATID OUT VARCHAR2
          
)
AS
ISREPEAT NUMBER;
ISEXISTS NUMBER;
STRSQL VARCHAR2(2000);
D_SESSNUM NUMBER:=0;
D_FALSENUM NUMBER:=0;
D_REPEATNUM NUMBER:=0;
D_REPEATIDNUM NUMBER:=0;
D_REPEATID VARCHAR2(2000);
BEGIN
          FOR I IN 1..arrDEALERNAME.COUNT LOOP
            BEGIN
            SELECT COUNT(1) INTO ISREPEAT FROM DRUG_DEALER WHERE  CID = dCID AND DEALERID = arrDEALERID(I);
              IF(ISREPEAT=0) THEN
                     SELECT COUNT(1) INTO ISEXISTS  FROM DRUG_DEALER WHERE CID = dCID AND DEALERNAME= arrDEALERNAME(I);
                     IF(ISEXISTS=0) THEN
                     STRSQL:='INSERT INTO DRUG_DEALER (CID,DEALERID,DEALERNAME,DEALERTYPE,PROVINCEID,CITYID,COUNTYID,LINKMAN,LINKTEL,MOBILETEL,ADDRESS,FAX,NOTE)
                     VALUES(:CID,:DEALERID,:DEALERNAME,:DEALERTYPE,:PROVINCEID,:CITYID,:COUNTYID,:LINKMAN,:LINKTEL,:MOBILETEL,:ADDRESS,:FAX,:NOTE)';
                     EXECUTE IMMEDIATE STRSQL USING
                     dCID,arrDEALERID(I),arrDEALERNAME(I),arrDEALERTYPE(I),arrDEALERPROID(I),arrDEALERCITY(I),arrDEALERCOUNTY(I),arrDEALERLINKMAN(I),
                     arrDEALERLINKTEL(I),arrDEALERPHONE(I),arrDEALERADDRESS(I),arrDEALERFAX(I),arrDEALERNOTE(I);                  
                     D_SESSNUM:=D_SESSNUM+1;
                     COMMIT;
                     ELSE
                     STRSQL := 'UPDATE DRUG_DEALER SET DEALERNAME=:DEALERNAME,DEALERTYPE=:DEALERTYPE,PROVINCEID=:PROVINCEID,CITYID=:CITYID,COUNTYID=:COUNTYID,LINKMAN=:LINKMAN,
                     LINKTEL=:LINKTEL,MOBILETEL=:MOBILETEL,ADDRESS=:ADDRESS,FAX=:FAX,NOTE=:NOTE WHERE CID=:CID AND DEALERNAME=:DEALERNAME ';
                     EXECUTE IMMEDIATE STRSQL USING
                     arrDEALERID(I),arrDEALERTYPE(I),arrDEALERPROID(I),arrDEALERCITY(I),arrDEALERCOUNTY(I),arrDEALERLINKMAN(I),
                     arrDEALERLINKTEL(I),arrDEALERPHONE(I),arrDEALERADDRESS(I),arrDEALERFAX(I),arrDEALERNOTE(I),dCID,arrDEALERNAME(I);
                    
                     D_REPEATNUM :=D_REPEATNUM+1;
                     COMMIT;
                     END IF;
              ELSE
                     D_REPEATIDNUM :=D_REPEATIDNUM+1;
                     D_REPEATID := D_REPEATID+arrDEALERID(I);
              END IF;
              EXCEPTION WHEN OTHERS THEN
              D_FALSENUM := D_FALSENUM+1;
            END;            
          END LOOP;
          SESSNUM:=D_SESSNUM;
          FALSENUM:=D_FALSENUM;
          REPEATNUM:=D_REPEATNUM;
          REPEATIDNUM:=D_REPEATIDNUM;
          REPEATID:=D_REPEATID;
END ADDDEALER;
END importDealer;

Answer 3

还得麻烦贴下DRUG_DEALER的建表语句

Answer 4

好,我都建成varchar2的,应该没问题,呵呵

Answer 5

不知道我这个方法能不能提供解决办法：
1.找一个中间变量接收你的数据库来的值
2.判断。。是string还是int
3.如果是int用转换.ToString()，在存入你的string[]
不知道这样能不能解决你的问题。以上我只是觉得可能可以，所以写出来了，新手，大家误笑

Answer 6

前面也有类似的操作,就这个出问题.是不是数据源的问题啊?这个数据源是读的Excel, 以前的是xml

Answer 7

是不是这个判断的问题
SELECT COUNT(1) INTO ISREPEAT FROM DRUG_DEALER WHERE  CID = dCID AND DEALERID = arrDEALERID(I);

Answer 8

arrDEALERID对应你的数据的哪列?

Answer 9

DEALERID   这一列啊

Answer 10

用存储过程导入了下,没问题,前台调用应该也差不多吧.
SQL> create or replace package importDealer
  2  as
  3  TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
  4  TYPE INT_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  5  END ;
  6  /
 
Package created
 
SQL> 
SQL> CREATE OR REPLACE PROCEDURE ADDDEALER1
  2  (
  3            dCID VARCHAR2,
  4            arrDEALERNAME IN importdealer.VARCHAR2_ARRAY,
  5            arrDEALERTYPE IN importdealer.INT_ARRAY,
  6            arrDEALERID IN importdealer.VARCHAR2_ARRAY,
  7            arrDEALERPROID IN importdealer.VARCHAR2_ARRAY,
  8            arrDEALERCITY IN importdealer.VARCHAR2_ARRAY,
  9            arrDEALERCOUNTY IN importdealer.VARCHAR2_ARRAY,
 10            arrDEALERLINKMAN IN importdealer.VARCHAR2_ARRAY,
 11            arrDEALERLINKTEL IN importdealer.VARCHAR2_ARRAY,
 12            arrDEALERPHONE IN importdealer.VARCHAR2_ARRAY,
 13            arrDEALERFAX IN importdealer.VARCHAR2_ARRAY,
 14            arrDEALERADDRESS IN importdealer.VARCHAR2_ARRAY,
 15            arrDEALERNOTE IN importdealer.VARCHAR2_ARRAY,
 16            SESSNUM OUT NUMBER,
 17            FALSENUM OUT NUMBER,
 18            REPEATNUM OUT NUMBER,
 19            REPEATIDNUM OUT NUMBER,
 20            REPEATID OUT VARCHAR2
 21  
 22  )
 23  AS
 24  ISREPEAT NUMBER;
 25  ISEXISTS NUMBER;
 26  STRSQL VARCHAR2(2000);
 27  D_SESSNUM NUMBER:=0;
 28  D_FALSENUM NUMBER:=0;
 29  D_REPEATNUM NUMBER:=0;
 30  D_REPEATIDNUM NUMBER:=0;
 31  D_REPEATID VARCHAR2(2000);
 32  v_num number(20);
 33  BEGIN
 34            v_num:=arrDEALERNAME.COUNT;
 35            FOR I IN 1..arrDEALERNAME.COUNT LOOP
 36              BEGIN
 37              SELECT COUNT(1) INTO ISREPEAT FROM DRUG_DEALER WHERE  CID = dCID AND DEALERID = arrDEALERID(I);
 38                IF(ISREPEAT=0) THEN
 39                       SELECT COUNT(1) INTO ISEXISTS  FROM DRUG_DEALER WHERE CID = dCID AND DEALERNAME= arrDEALERNAME(I);
 40                       IF(ISEXISTS=0) THEN
 41                       STRSQL:='INSERT INTO DRUG_DEALER (CID,DEALERID,DEALERNAME,DEALERTYPE,PROVINCEID,CITYID,COUNTYID,LINKMAN,LINKTEL,MOBILETEL,ADDRESS,FAX,NOTE)
 42                       VALUES(:CID,:DEALERID,:DEALERNAME,:DEALERTYPE,:PROVINCEID,:CITYID,:COUNTYID,:LINKMAN,:LINKTEL,:MOBILETEL,:ADDRESS,:FAX,:NOTE)';
 43                       EXECUTE IMMEDIATE STRSQL USING
 44                       dCID,arrDEALERID(I),arrDEALERNAME(I),arrDEALERTYPE(I),arrDEALERPROID(I),arrDEALERCITY(I),arrDEALERCOUNTY(I),arrDEALERLINKMAN(I),
 45                       arrDEALERLINKTEL(I),arrDEALERPHONE(I),arrDEALERADDRESS(I),arrDEALERFAX(I),arrDEALERNOTE(I);
 46                       D_SESSNUM:=D_SESSNUM+1;
 47                       COMMIT;
 48                       ELSE
 49                       STRSQL := 'UPDATE DRUG_DEALER SET DEALERNAME=:DEALERNAME,DEALERTYPE=:DEALERTYPE,PROVINCEID=:PROVINCEID,CITYID=:CITYID,COUNTYID=:COUNTYID,LINKMAN=:LINKMAN,
 50                       LINKTEL=:LINKTEL,MOBILETEL=:MOBILETEL,ADDRESS=:ADDRESS,FAX=:FAX,NOTE=:NOTE WHERE CID=:CID AND DEALERNAME=:DEALERNAME ';
 51                       EXECUTE IMMEDIATE STRSQL USING
 52                       arrDEALERID(I),arrDEALERTYPE(I),arrDEALERPROID(I),arrDEALERCITY(I),arrDEALERCOUNTY(I),arrDEALERLINKMAN(I),
 53                       arrDEALERLINKTEL(I),arrDEALERPHONE(I),arrDEALERADDRESS(I),arrDEALERFAX(I),arrDEALERNOTE(I),dCID,arrDEALERNAME(I);
 54  
 55                       D_REPEATNUM :=D_REPEATNUM+1;
 56                       COMMIT;
 57                       END IF;
 58                ELSE
 59                       D_REPEATIDNUM :=D_REPEATIDNUM+1;
 60                       D_REPEATID := D_REPEATID+arrDEALERID(I);
 61                END IF;
 62                EXCEPTION WHEN OTHERS THEN
 63                D_FALSENUM := D_FALSENUM+1;
 64              END;
 65            END LOOP;
 66            SESSNUM:=D_SESSNUM;
 67            FALSENUM:=D_FALSENUM;
 68            REPEATNUM:=D_REPEATNUM;
 69            REPEATIDNUM:=D_REPEATIDNUM;
 70            REPEATID:=D_REPEATID;
 71  END ;
 72  /
 
Procedure created
 
SQL> 
SQL> create or replace procedure proc_import
  2  as
  3  v_name  importdealer.VARCHAR2_ARRAY;
  4  v_type importdealer.int_array;
  5  v_id   importdealer.VARCHAR2_ARRAY;
  6  v_SESSNUM  NUMBER;
  7  v_FALSENUM  NUMBER;
  8  v_REPEATNUM  NUMBER;
  9  v_REPEATIDNUM  NUMBER;
 10  v_REPEATID  VARCHAR2(4000);
 11  begin
 12   v_name(1):='张三';v_name(2):='李四';v_name(3):='666';
 13   v_type(1):=1;v_type(2):=2;v_type(3):=3;
 14   v_id(1):='aaa';v_id(2):='bbb';v_id(3):='ccc';
 15  
 16   ADDDEALER1('123',v_name,v_type,v_id,v_id,v_id,v_id,v_id,v_id,v_id,v_id,v_id,v_id,v_SESSNUM,v_FALSENUM,v_REPEATNUM,v_REPEATIDNUM,v_REPEATID);
 17  end;
 18  /
 
Procedure created
 
SQL> truncate table DRUG_DEALER;
 
Table truncated
 
SQL> exec proc_import
 
PL/SQL procedure successfully completed
 
SQL> select * from DRUG_DEALER;
 
CID                                                                              DEALERID                                                                         DEALERNAME                                                                       DEALERTYPE                                                                       PROVINCEID                                                                       CITYID                                                                           COUNTYID                                                                         LINKMAN                                                                          LINKTEL                                                                          MOBILETEL                                                                        ADDRESS                                                                          FAX                                                                              NOTE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
123                                                                              aaa                                                                              张三                                                                             1                                                                                aaa                                                                              aaa                                                                              aaa                                                                              aaa                                                                              aaa                                                                              aaa                                                                              aaa                                                                              aaa                                                                              aaa
123                                                                              bbb                                                                              李四                                                                             2                                                                                bbb                                                                              bbb                                                                              bbb                                                                              bbb                                                                              bbb                                                                              bbb                                                                              bbb                                                                              bbb                                                                              bbb
123                                                                              ccc                                                                              666                                                                              3                                                                                ccc                                                                              ccc                                                                              ccc                                                                              ccc                                                                              ccc                                                                              ccc                                                                              ccc                                                                              ccc                                                                              ccc
 
SQL>

Answer 11

是Excel 的问题.我调试发现有数字的那一列,汉字就读不出来.  而其它列没问题

Answer 12

我还要先创建一个DataTable及各列不成?   默认变成int类型列了?