不一致的数据类型:在包函数中从游标返回表时 - ORACLE [英] inconsistent datatypes: when returning table from cursor in a package function - ORACLE
本文介绍了不一致的数据类型:在包函数中从游标返回表时 - ORACLE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下的软件包。我想从游标中填充函数内的记录并返回记录。我不确定如何将光标中的行分配到记录变量中。
I have the following package. I am trying to fill the records inside the function from a cursor and return the record. I am unsure of how to assign the rows from the cursor into the record variable. I need to return the record so that I could create a materialized view from it.
CREATE OR REPLACE PACKAGE pkg_contrator_of_consultant AS
TYPE cst_record IS RECORD(
consultant_id NUMBER(10));
TYPE cst_id_type IS TABLE OF cst_record;
FUNCTION fnc_get_contractor_id(cst_username IN VARCHAR2)
RETURN cst_id_type
PIPELINED;
END;
CREATE OR REPLACE PACKAGE BODY pkg_contrator_of_consultant AS
FUNCTION fnc_get_contractor_id(cst_username IN VARCHAR2 )
RETURN cst_id_type
PIPELINED IS
V_cursor_contracotr_id cst_id_type;
CURSOR cursor_contract_name IS
SELECT plc.FK2_CONTRACTOR_ID
FROM lds_consultant cons
INNER JOIN lds_account acc on cons.consultant_id = acc.fk1_consultant_id
INNER JOIN lds_placement plc on acc.account_id = plc.FK1_ACCOUNT_ID
WHERE UPPER(cons.USERNAME) = UPPER(cst_username)
AND UPPER(plc.PLT_TO_PERMANENT) = UPPER('Y');
V_contracotr_id cst_id_type;
BEGIN
FOR rec IN cursor_contract_name LOOP
V_contracotr_id := rec.fk2_contractor_id;
SELECT V_contracotr_id INTO V_cursor_contracotr_id FROM DUAL;
dbms_output.put_line('cst_username : '||cst_username||' V_contracotr_id :'||V_contracotr_id);
END LOOP;
PIPE ROW (V_cursor_contracotr_id);
RETURN;
END fnc_get_contractor_id;
END;
/
在行
V_contracotr_id := rec.fk2_contractor_id;
当光标选择的列是不一致的数据类型:期望的UDT有NUMBER
It gives the error "inconsistent datatypes: expected UDT got NUMBER" when the column selected by the cursor is of NUMBER type.
FK2_CONTRACTOR_ID NUMBER
推荐答案
试试这个:
Try this:
out_rec cst_record;
CURSOR C1 IS
SELECT ...;
BEGIN
open c1;
LOOP
FETCH c1 INTO out_rec;
exit when c1%notfound;
PIPE ROW(out_rec);
END LOOP;
close c1;
RETURN;
END fnc_get_contractor_id;
更新代码:
UPDATED CODE:
CREATE OR REPLACE PACKAGE pkg_contrator_of_consultant AS
TYPE cst_record IS RECORD(
consultant_id NUMBER(10));
TYPE cst_id_type IS TABLE OF cst_record;
FUNCTION fnc_get_contractor_id(cst_username IN VARCHAR2)
RETURN cst_id_type
PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_contrator_of_consultant AS
FUNCTION fnc_get_contractor_id(cst_username IN VARCHAR2 )
RETURN cst_id_type
PIPELINED IS
CURSOR c1 IS
SELECT plc.FK2_CONTRACTOR_ID
FROM lds_consultant cons
INNER JOIN lds_account acc on cons.consultant_id = acc.fk1_consultant_id
INNER JOIN lds_placement plc on acc.account_id = plc.FK1_ACCOUNT_ID
WHERE UPPER(cons.USERNAME) = UPPER(cst_username)
AND UPPER(plc.PLT_TO_PERMANENT) = UPPER('Y');
out_rec cst_record;
BEGIN
open c1;
LOOP
FETCH c1 INTO out_rec;
exit when c1%notfound;
PIPE ROW(out_rec);
END LOOP;
close c1;
RETURN;
END fnc_get_contractor_id;
END;
/
这篇关于不一致的数据类型:在包函数中从游标返回表时 - ORACLE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文