Oracle存储过程inout参数问题-解决方案-错字 [英] Oracle Stored Procedure inout parameter issue - Got the solution - Typo error

问题描述

我已经成功创建了以下存储过程:

I have successfully created the below stored procedure:

CREATE OR REPLACE PROCEDURE FIRSTPROC (ID1 IN VARHAR2, ID2 IN NUMBER )
AS 
CURSOR FIRST_CUR AS
 SELECT id2val
 FROM 
 WHERE id1val = ID1;

BEGIN
  DBMS_OUTPUT.PUT_LINE(ID1);
  OPEN FIRST_CUR;
  FETCH FIRST_CUR INTO ID2;
  IF FIRST_CUR%NOTFOUND THEN
     ID2 := 0;   
  END IF;

END

在执行时，我在ID1变量中将值作为23传递，并且该值显示在DBMS_OUTPUT.PUT_LINE语句中.并且该值存在于表中，但我没有得到结果

While executing, I am passing value as 23 in ID1 variable, and the value is displayed in the DBMS_OUTPUT.PUT_LINE statement. And the value is present in the table, but I am not getting the result

但是，如果我在存储过程中对23进行硬编码，我将获得该值.感到困惑.我在上面缺少了一些东西.

But if I hardcode the 23 in the stored procedure I am getting the value. Got confused. Something I am missing above.

CREATE OR REPLACE PROCEDURE FIRSTPROC (ID1 IN VARHAR2, ID2 IN NUMBER )
AS 
CURSOR FIRST_CUR AS
 SELECT id2val
 FROM 
 WHERE id1val = 23;

BEGIN
  DBMS_OUTPUT.PUT_LINE(ID1);
  OPEN FIRST_CUR;
  FETCH FIRST_CUR INTO ID2;
  IF FIRST_CUR%NOTFOUND THEN
     ID2 := 0;   
  END IF;

END

推荐答案

某些问题:

1. VARHAR2是我们的错字，应为VARCHAR2.
   • 您可以改为使用table_name.column_name%TYPE指定数据类型以引用相应的表列.

1. VARHAR2 us a typo and should be VARCHAR2.
   • You could instead specify the datatypes to reference the corresponding table columns using table_name.column_name%TYPE.

类似这样的东西:

CREATE OR REPLACE PROCEDURE FIRSTPROC (
  ID1 IN  table_name.id1val%TYPE,
  ID2 OUT table_name.id2val%TYPE
)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE(ID1);
  SELECT id2val
  INTO   ID2
  FROM   table_name
  WHERE  id1val = ID1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ID2 := 0; -- Or you could use NULL
  WHEN TOO_MANY_ROWS THEN
    ID2 := 0; -- Or you could use NULL
END;
/

这篇关于Oracle存储过程inout参数问题-解决方案-错字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！