ORA-06504:PL/SQL:结果集变量或查询的返回类型不匹配

Question

我正在尝试使用Oracle PL/SQL存储过程在服务器端实现分页。

create or replace PROCEDURE SEARCH_CUST_RECIPIENTS(v_cust_id IN t_recipient.r_cust_id%TYPE,
v_recipient_id IN t_recipient.recipient_id%TYPE,
v_current_page IN NUMBER,
v_records_per_page IN NUMBER,
v_result_set OUT SYS_REFCURSOR,
v_total_records_found OUT NUMBER,
v_total_pages_no OUT NUMBER)
AS
TYPE lv_result_set_row_type IS RECORD (lrecipient_id t_recipient.recipient_id%TYPE,
lr_cust_id t_recipient.r_cust_id%TYPE,
lrecipient_name t_recipient.recipient_name%TYPE,
lrecipient_country t_recipient.recipient_country%TYPE,
lrecipient_state t_recipient.recipient_state%TYPE,
lrecipient_pincode t_recipient.recipient_pincode%TYPE,
lr_acc_number t_recipient.r_acc_number%TYPE,
lr_bank_name t_recipient.r_bank_name%TYPE);
lv_result_set_row lv_result_set_row_type;   
BEGIN
OPEN v_result_set FOR
SELECT * FROM
(
SELECT t_temp_table.*, ROWNUM rnum
FROM        
(
SELECT t_recipient.recipient_id,
t_recipient.r_cust_id,
t_recipient.recipient_name,
t_recipient.recipient_country,
t_recipient.recipient_state,
t_recipient.recipient_pincode,
t_recipient.r_acc_number,
t_recipient.r_bank_name
FROM t_recipient 
WHERE r_cust_id=v_cust_id AND recipient_id LIKE v_recipient_id||'%' ORDER BY t_recipient.recipient_id
) t_temp_table
WHERE ROWNUM <= (v_current_page)*v_records_per_page
)
WHERE rnum > (v_current_page-1)*v_records_per_page;
IF v_result_set%ISOPEN THEN
LOOP 
FETCH v_result_set INTO lv_result_set_row;
v_total_records_found := v_result_set%ROWCOUNT;
v_total_pages_no := ROUND(v_total_records_found/v_records_per_page);
END LOOP;
END IF;
END;

我不知道结果集的返回类型和定义的记录类型出了什么问题，但当我试图将游标v_result_set提取到lv_result_set_row中时，会出现以下错误。

Connecting to the database ORACLR_CONNECTION_DATA_CMS.
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at "C##SCOTT.SEARCH_CUST_RECIPIENTS", line 42
ORA-06512: at line 15
Process exited.

Answer 1

除非我错了，否则这个声明是不正确的：

TYPE lv_result_set_row_type IS RECORD 

为什么？因为你在做这个：

FETCH v_result_set INTO lv_result_set_row;

而v_result_set包含以下内容：

t_temp_table.*, ROWNUM rnum
^^^^^^^

并且CCD_ 2不包含任何将接受CCD_ 3值的内容。

因此，如果您将其修改为例如

TYPE lv_result_set_row_type IS RECORD (lrecipient_id t_recipient.recipient_id%TYPE,
lr_cust_id t_recipient.r_cust_id%TYPE,
lrecipient_name t_recipient.recipient_name%TYPE,
lrecipient_country t_recipient.recipient_country%TYPE,
lrecipient_state t_recipient.recipient_state%TYPE,
lrecipient_pincode t_recipient.recipient_pincode%TYPE,
lr_acc_number t_recipient.r_acc_number%TYPE,
lr_bank_name t_recipient.r_bank_name%TYPE,
rnum number                                         --> add this
);

应该没问题。

---

但不会太久！一些反对意见：

• 您永远不会退出循环；添加EXIT WHEN v_result_set%NOTFOUND;
• 对于每个循环迭代，v_total_records_found总是包含v_result_set中的行数。这不是一个"；错误"；，但是，它的目的是什么