oracle 存储过程错误

[J*mes]

谁能帮我找找这个存储过程哪里错了。我第一次写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;

[yhuib]

什么错误啊？编译不通过还是结果不对？

[duzif]

CREATE OR REPLACE PROCEDURE....

[yhuib]

判断表中是否存在某条记录不需要用游标，建议改成

select count(0) into v_VendorExist
  from dual
 where  exists (select 1
                  from ECT_Vendor_lookup 
                 WHERE vendor_Name = p_VendorName
               )
;

去掉定义的游标及相关部分

[yhuib]

[Quote=引用 2 楼 duzif 的回复:]
CREATE OR REPLACE PROCEDURE....
[/Quote]
有可能

[Richard345265669]

引用 2 楼 duzif 的回复:
CREATE OR REPLACE PROCEDURE....

很有可能

[anxing]

CREATE OR REPLACE 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;

[J*mes]

谢谢各位的回复，我找到错误了，是我外面的参数名写错了。。。

[cab1225]

[Quote=引用 3 楼 yhuib 的回复:]
判断表中是否存在某条记录不需要用游标，建议改成

SQL code
select count(0) into v_VendorExist
from dual
where exists (select 1
from ECT_Vendor_lookup
WHERE vendor_Name = p_VendorName
)
;



去掉定义的游标及相关部分
[/Quote]

见意不错。！！楼主，你可以试试哦！

[superhsj]

lz把报的错误码贴出来看看