Oracle存储过程inout参数问题-解决方案-错字 [英] Oracle Stored Procedure inout parameter issue - Got the solution - Typo error
本文介绍了Oracle存储过程inout参数问题-解决方案-错字的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我已经成功创建了以下存储过程:
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
推荐答案
某些问题:
-
VARHAR2
是我们的错字,应为VARCHAR2
.- 您可以改为使用
table_name.column_name%TYPE
指定数据类型以引用相应的表列.
- 您可以改为使用
VARHAR2
us a typo and should beVARCHAR2
.- You could instead specify the datatypes to reference the corresponding table columns using
table_name.column_name%TYPE
.
- You could instead specify the datatypes to reference the corresponding table columns using
类似这样的东西:
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屋!
查看全文