SQL值sequence.NEXTVAL

Question

pl/sql相对较新，我正试图在循环中找到一种利用sequence.NEXTVAL的方法。当我运行我目前拥有的，我得到"ORA-01722:无效号码“，有什么时候我错过了吗？

DECLARE 
  CURSOR SALESDATA
  IS
    SELECT vin,
           sale_date,
           SF.plan_ID,
           SP.salepersons_ID,
           COUNT (*) AS Vehicles_Sold,
           SUM (S.gross_sales_price) AS Gross_Sales_Amt
    FROM sales S, sales_financing SF, salepersons SP
    WHERE S.sale_ID = SF.sale_ID AND S.salepersons_ID = SP.salepersons_ID
   GROUP BY vin,
          sale_date,
          SF.plan_ID,
          SP.salepersons_ID
     HAVING COUNT (*) > 0;
  BEGIN
    FOR VEHICLE_REC IN SALESDATA
    LOOP
      INSERT INTO sale_facts (sale_fact_ID,
                              sale_day,
                              vehicle_code,
                              plan_code,
                              dealer_ID,
                              vehicle_sold,
                              gross_sales_amt)
          VALUES (salefactID_seq.NEXTVAL,
                  vehicle_rec.sale_date,
                  vehicle_rec.vin,
                  vehicle_rec.plan_id,
                  vehicle_rec.salepersons_ID,
                  vehicle_rec.vehicles_sold,
                  vehicle_rec.gross_sales_amt);

     COMMIT;
    END LOOP;

    COMMIT;
  END;
 /

这是桌子的结构。

SQL> DESC sales
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_ID                                   NOT NULL NUMBER(10)
 VIN                                                VARCHAR2(17)
 CUST_ID                                            NUMBER(10)
 GROSS_SALES_PRICE                         NOT NULL NUMBER(10)
 MILEAGE                                   NOT NULL VARCHAR2(10 CHAR)
 SALE_DATE                                 NOT NULL DATE
 VEHICLE_STATUS                            NOT NULL VARCHAR2(25 CHAR)
 SALEPERSONS_ID                                     NUMBER(10)

SQL> DESC salepersons
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALEPERSONS_ID                            NOT NULL NUMBER(10)
 TITLE                                     NOT NULL VARCHAR2(25 CHAR)
 NAME                                      NOT NULL VARCHAR2(50 CHAR)
 HIRE_DATE                                 NOT NULL DATE

SQL> DESC sales_financing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_ID                                            NUMBER(10)
 PLAN_ID                                            NUMBER(10)
 DOWN_PAY                                  NOT NULL FLOAT(10)
 LOAN_TERM                                 NOT NULL NUMBER(3)

SQL> DESC sale_facts
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_FACT_ID                              NOT NULL NUMBER(5)
 SALE_DAY                                  NOT NULL DATE
 VEHICLE_CODE                              NOT NULL NUMBER(5)
 PLAN_CODE                                 NOT NULL NUMBER(10)
 DEALER_ID                                 NOT NULL NUMBER(10)
 VEHICLE_SOLD                              NOT NULL NUMBER(10)
 GROSS_SALES_AMT                           NOT NULL NUMBER(15)

Answer 1

'vehicle_code‘在'sale_facts’中是数字(5)，而'VIN‘在'Sales’中是VARCHAR2(17)。您确定VIN的所有值都可以转换为数字(5)吗？也就是说，里面没有字符？