我只想知道如何将游标提取到另一个游标中。我有以下套餐:
create or replace
PACKAGE Matching AS
type Cursor_Return is ref Cursor;
Procedure Get_Data
(Cus_ID in Varchar2,
Cursor_back OUT Cursor_Return,
Cursor_back2 OUT Cursor_Return);
END BARCODEMATCHING;
create or replace
PACKAGE BODY Matching AS
Procedure Matching_Proc
(Cus_ID in Varchar2,
Cursor_back OUT Cursor_Return,
Cursor_back2 OUT Cursor_Return
) AS
BEGIN
Open Cursor_back for 'Select * from cus.customerHead where CustomerID = ' || cus_Id;
Open Cursor_back2 for 'Select Cus_Location, Cus_zipcode from cus.customerBody where = CustomerID = ' || cus_ID;
Fetch Cursor_back2 into Cursor_back;
END Matching_Proc;
END Matching;
这是我到目前为止的代码。我只需要返回光标:"Cursor_back"。当我尝试运行此代码时,出现错误:ORA-06512:缺少表达式。有没有办法解决这个问题?我可以宣布我的两个哥伦,我想以另一种方式交给Cursor_back2吗?我只想返回带有两列(最多四列)的Cursor_back,所以我有一个输出,如下所示:
cus.customerbody.cus_location | cus.customerbody.cus_zipcode | cus.customerhead.cus_id | cus.customerhead.cus_Name | and so on
提前谢谢。
您收到"ORA-06512:缺少表达式"错误,因为此行中有一个额外的=
符号:
Open Cursor_back2 for 'Select Cus_Location, Cus_zipcode from cus.customerBody where = CustomerID = ' || cus_ID;
它应该是where = CustomerID =
的,而不是where = CustomerID =
的。不过,cursor 语句不需要是动态的,您可以使用:
Open Cursor_back for
Select * from cus.customerHead where CustomerID = cus_Id;
Open Cursor_back2 for
Select Cus_Location, Cus_zipcode from cus.customerBody where CustomerID = cus_ID;
如果您坚持使用动态版本,其中查询在运行时之前无法进行语法检查,那么由于您cus_ID
作为字符串传递,您可能需要将其括在转义的单引号中作为动态 SQL 语句的一部分。但是不要使用动态SQL,除非你真的必须这样做。
并不真正想要两个游标,因为您正在尝试组合来自两个相关查询的值。您需要表之间的联接,以及单个 out 参数游标,如下所示:
Open Cursor_back for
Select cb.cus_location, cb.cus_zipcode, ch.cus_id, ch.cus_Name
from cus.customerHead ch
join cus.customerBody cb
on cb.customerID = ch.customerID
where ch.CustomerID = cus_Id;