谁能帮我找找这个存储过程哪里错了。我第一次写ORACLE的存储过程
谢谢
PROCEDURE add_vendor(
p_VendorName IN VARCHAR2,
o_ErrorCode OUT NUMBER,
o_Message OUT VARCHAR2
) IS
v_VendorID NUMBER; CURSOR cIsVendorExist IS SELECT 1 FROM ECT_Vendor_lookup WHERE vendor_Name = p_VendorName;
v_VendorExist NUMBER := 0;
BEGIN
o_Message := 'STARTED';
o_ErrorCode := -1; OPEN cIsVendorExist;
FETCH cIsVendorExist INTO v_VendorExist;
CLOSE cIsVendorExist; o_ErrorCode := -2;
IF (v_VendorExist = 1) THEN
o_ErrorCode := -3;
o_Message := 'Vendor already exists!';
ELSE
SELECT ect_global_seq.NEXTVAL INTO v_VendorID FROM dual;
o_ErrorCode := -4;
INSERT INTO ECT_vendor_lookup(vendor_id,vendor_name) values (v_VendorID,p_VendorName);
o_ErrorCode := 1;
o_Message := 'SUCCESS';
END IF;
EXCEPTION
WHEN OTHERS THEN
o_Message := 'Flag ' || o_ErrorCode || '; Message: ' || o_Message || '; SQLERRM: ' || SQLERRM;
o_ErrorCode := SQLCODE;
END;
谢谢
PROCEDURE add_vendor(
p_VendorName IN VARCHAR2,
o_ErrorCode OUT NUMBER,
o_Message OUT VARCHAR2
) IS
v_VendorID NUMBER; CURSOR cIsVendorExist IS SELECT 1 FROM ECT_Vendor_lookup WHERE vendor_Name = p_VendorName;
v_VendorExist NUMBER := 0;
BEGIN
o_Message := 'STARTED';
o_ErrorCode := -1; OPEN cIsVendorExist;
FETCH cIsVendorExist INTO v_VendorExist;
CLOSE cIsVendorExist; o_ErrorCode := -2;
IF (v_VendorExist = 1) THEN
o_ErrorCode := -3;
o_Message := 'Vendor already exists!';
ELSE
SELECT ect_global_seq.NEXTVAL INTO v_VendorID FROM dual;
o_ErrorCode := -4;
INSERT INTO ECT_vendor_lookup(vendor_id,vendor_name) values (v_VendorID,p_VendorName);
o_ErrorCode := 1;
o_Message := 'SUCCESS';
END IF;
EXCEPTION
WHEN OTHERS THEN
o_Message := 'Flag ' || o_ErrorCode || '; Message: ' || o_Message || '; SQLERRM: ' || SQLERRM;
o_ErrorCode := SQLCODE;
END;
from dual
where exists (select 1
from ECT_Vendor_lookup
WHERE vendor_Name = p_VendorName
)
;去掉定义的游标及相关部分
CREATE OR REPLACE PROCEDURE.... 很有可能
p_VendorName IN VARCHAR2,
o_ErrorCode OUT NUMBER,
o_Message OUT VARCHAR2
) IS
v_VendorID NUMBER; CURSOR cIsVendorExist IS SELECT 1 FROM ECT_Vendor_lookup WHERE vendor_Name = p_VendorName;
v_VendorExist NUMBER := 0;
BEGIN
o_Message := 'STARTED';
o_ErrorCode := -1; OPEN cIsVendorExist;
FETCH cIsVendorExist INTO v_VendorExist;
CLOSE cIsVendorExist; o_ErrorCode := -2;
IF (v_VendorExist = 1) THEN
o_ErrorCode := -3;
o_Message := 'Vendor already exists!';
ELSE
SELECT ect_global_seq.NEXTVAL INTO v_VendorID FROM dual;
o_ErrorCode := -4;
INSERT INTO ECT_vendor_lookup(vendor_id,vendor_name) values (v_VendorID,p_VendorName);
o_ErrorCode := 1;
o_Message := 'SUCCESS';
END IF;
EXCEPTION
WHEN OTHERS THEN
o_Message := 'Flag ' || o_ErrorCode || '; Message: ' || o_Message || '; SQLERRM: ' || SQLERRM;
o_ErrorCode := SQLCODE;
END;