sql - PLS-00201：必须声明标识符“ISDATE”

Question

以下是我的程序：

CREATE OR REPLACE PROCEDURE proc_InsertNewCustomer( IN_CustName IN VARCHAR,
                                                    IN_CustContact IN VARCHAR,
                                                    IN_DateOfBirth IN VARCHAR,
                                                    IN_CustAddress IN VARCHAR,
                                                    IN_PostalCode IN VARCHAR) AS
v_newCustID customer.customerID%TYPE;
v_newAddressID customeraddress.addressid%TYPE;
v_location number(5);
e_invalidDate exception;
e_invalidPostalCode exception;

BEGIN

   IF (ISDATE(IN_DateOfBirth) = false) THEN
       raise e_invalidDate;
   ELSE
       IF NOT EXISTS(SELECT locationid FROM location WHERE locationpostcode = IN_PostalCode) THEN
             raise e_invalidPostalCode;
       ELSE
             v_location := locationid;   
       END IF;
   END IF;

   v_newCustID := 'C'||customerID.nextval;
   v_newAddressID := addressid.nextval;

   INSERT INTO customer values(v_newCustID, IN_CustName, IN_CustContact, TO_DATE(IN_DateOfBirth,'dd/mm/yyyy'));
   INSERT INTO customeraddress values(TO_NUMBER(v_newAddressID, '999'), IN_CustAddress, v_location);
   INSERT INTO addresslist values(TO_NUMBER(v_newAddressID, '999'), v_newCustID, 1);

   DBMS_OUTPUT.PUT_LINE(chr(10));
   DBMS_OUTPUT.PUT_LINE('New staff added successfully.');
   DBMS_OUTPUT.PUT_LINE(chr(10));
   DBMS_OUTPUT.PUT_LINE('Customer ID: '||v_newCustID);
   DBMS_OUTPUT.PUT_LINE('Customer Name: '||IN_CustName);
   DBMS_OUTPUT.PUT_LINE('Customer Contact: '||IN_CustContact);
   DBMS_OUTPUT.PUT_LINE('Customer Birthday: '||IN_DateOfBirth);
   DBMS_OUTPUT.PUT_LINE('Customer Address ID: '||v_newAddressID);
   DBMS_OUTPUT.PUT_LINE('Customer Address: '||IN_CustAddress);
   DBMS_OUTPUT.PUT_LINE('Postal Code: '||v_location);

EXCEPTION
     WHEN e_invalidDate THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Input: Wrong Date of Birth input.');
     WHEN e_invalidPostalCode THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Input: PostalCode does not exist.');
        rollback;

END;
/

我得到了一些错误

14/4     PL/SQL: Statement ignored
14/8     PLS-00201: identifier 'ISDATE' must be declared
24/4     PL/SQL: Statement ignored
24/24    PLS-00201: identifier 'CUSTOMERID.NEXTVAL' must be declared
25/4     PL/SQL: Statement ignored
25/22    PLS-00201: identifier 'ADDRESSID.NEXTVAL' must be declared

我的客户 ID 将是：

C00001 C00002 C00003 ......

我的地址 ID 将是：

001
002
003
...
501
502

我想用 .nextval 和地址 id 增加 customerid，但我不知道该怎么做。寻求帮助。

标签： sqloraclesqlplus

Answer 1

Oracle 没有ISDATE检查字符串类型的功能。但是您可以编写自己的函数并使用它。例如：

function my_is_date( p_str in varchar2 ) return number
is
    v_date date;
begin
    v_date := to_date(p_str, 'dd.mm.yyyy');
    return 1;
exception
    when others then
        return 0;
end;