PLS

2024-03-27 14:04| 来源: 网络整理| 查看: 265

以下是我的操作步骤：

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来增加客户id，但我不知道该怎么做。寻求帮助。

【本文地址】

公司简介

联系我们