CREATE OR REPLACE PROCEDURE ADD_COMPLEX_SALE_TO_DB(pcustid NUMBER, pprodid NUMBER, pqty NUMBER, pdate VARCHAR2) AS
ERR_INVALID_STATUS EXCEPTION;
ERR_QTY_OUT_RANGE EXCEPTION;
ERR_INVALID_DATE EXCEPTION;
ERR_CUST_NOTFOUND EXCEPTION;
ERR_PROD_NOTFOUND EXCEPTION;
vcustid NUMBER(10);
vstatus VARCHAR2(5);
vamt NUMBER(10);
vSellPrice NUMBER(10);
PRAGMA EXCEPTION_INIT(ERR_PROD_NOTFOUND, 100);
BEGIN
IF pqty < 1 OR pqty > 999 THEN
RAISE ERR_QTY_OUT_RANGE;
END IF;
vStatus := 'OK';
IF vStatus = 'OK' THEN
UPDATE customer SET status = vStatus WHERE custid = pcustid;
IF SQL%NOTFOUND THEN
RAISE ERR_CUST_NOTFOUND;
END IF;
ELSE
RAISE ERR_INVALID_STATUS;
END IF;
IF pdate = TO_DATE(pdate, 'YYYYMMDD') THEN
INSERT INTO sale(saleid, custid, prodid, qty, price, saledate) VALUES(SALE_SEQ.nextVal, pcustid, pprodid, pqty, vamt, TO_DATE(pdate));
ELSE
RAISE ERR_INVALID_DATE;
END IF;
SELECT selling_price INTO vSellPrice FROM product WHERE prodid = pprodid;
IF SQL%NOTFOUND THEN
RAISE ERR_PROD_NOTFOUND;
END IF;
vamt := pqty * vSellPrice;
UPD_CUST_SALESYTD_IN_DB(pcustid, vamt);
UPD_PROD_SALESYTD_IN_DB(pprodid, vamt);
EXCEPTION
WHEN ERR_QTY_OUT_RANGE THEN
RAISE_APPLICATION_ERROR(-20091, 'Sale Quantity outside valid range');
WHEN ERR_INVALID_STATUS THEN
RAISE_APPLICATION_ERROR(-20092, 'Customer status not OK');
WHEN ERR_INVALID_DATE THEN
RAISE_APPLICATION_ERROR(-20093, 'Date not valid');
WHEN ERR_CUST_NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20094, 'Customer ID not found');
WHEN ERR_PROD_NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20095, 'Product ID not found');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, sqlerrm);
END;
执行此代码时,我收到“ORA-01861:文字与格式字符串不匹配”错误 . 我在这个代码中哪里出错了?我也附上了问题片段 . 实际表中插入值的'saledate'变量是数据类型日期,但我应该通过过程输入日期作为varchar2 dataype . 我使用to_date()函数转换日期但仍然出现相同的错误
1 回答
您试图以错误的方式处理无效的日期格式
尝试在
pdate
变量周围使用单独的try catch块