oracle 从嵌套表中获取数据到游标中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5975227/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fetching data from a nested table into cursor
提问by gaurav
Requirement
要求
The table XYZcontains the column p_typewhich contains the column name of PQR table . Now here I am fetching a record from the XYZtable and sum(amount)on the basis of the group by p_typewhich is fetched from the record from table XYZ. I have used partition by because I also want t_idand not in group by. Now I take the records fetched from PQRtable and compare with a record of the XYZtable, and for each record fetched from PQRtable, sum(amount)is compared with a record of XYZ table.
该表XYZ包含p_type包含 PQR 表的列名的列。现在，我从XYZtable 中获取记录，并sum(amount)基于p_type从 table 中的记录中获取的组XYZ。我使用了 partition by 是因为我也想要t_id而不是 group by。现在我把从PQR表中提取的记录与表的记录进行比较XYZ，对于从PQR表中提取的每条记录，sum(amount)都与XYZ表的记录进行比较。
Question
题
I have fetched the xyz_idand t_idin the nested table, but I want to take this in cursor, IS there any way to do this so that I can select these record in one go.
我已经获取了嵌套表中的xyz_id和t_id，但我想将其放入游标中，有什么方法可以做到这一点，以便我可以一次性选择这些记录。
The probable solution I found is to take a temporary table, but if I dont want to use a temp table, then what will be the solution.
我找到的可能的解决方案是采用临时表，但如果我不想使用临时表，那么解决方案是什么。
DECLARE
type queryCursor is ref cursor;
v_ref queryCursor;
v_query queryCursor;
v_t_id pqr.t_id%TYPE;
v_total NUMBER (38);
CURSOR varcursor
IS
SELECT abc_id,p_type,p_amount
FROM xyz;
v_temp VARCHAR2 (4000);
v_temp1 VARCHAR2 (200);
v_temp2 VARCHAR2 (200);
TYPE XYZ_table IS TABLE OF XYZ%ROWTYPE;
v_XYZ XYZ_table :=XYZ_table();
TYPE PQR_table IS TABLE OF PQR%ROWTYPE;
v_PQR PQR_table :=XYZ_table();
BEGIN
FOR varcursor_rec IN varcursor
LOOP
v_temp1 := varcursor_rec.p_type; --p_type contains column name of the pqr table
v_temp :=
'SELECT T_ID,SUM(AMOUNT) OVER (PARTITION BY '
|| v_temp1
|| ' ORDER BY '
|| v_temp1
|| ') total from pqr
||';
OPEN v_ref FOR v_temp;
LOOP
FETCH V_REF into v_t_id,v_total;
exit when V_REF%notfound;
IF varcursor_rec.p_amount <v_total
THEN
counter:=counter+1;
v_XYZ.EXTEND;
v_PQR.EXTEND;
v_XYZ(counter).xyz_id:=varcursor_rec.xyz_id;
v_PQR(counter).t_id:=varcursor_rec.t_id;
--HOW CAN I FETCH THE
END IF;
END LOOP;
END LOOP;
END;
/
XYZ Table:
XYZ 表：
xyz_id p_type p_amount
============================================================
p_1 p_id 100000
p_2 p_id,t_status 100
PQR Table:
PQR表：
T_id p_id amount t_status
=================================================
1 E1 100 open
2 E2 200 open
3 E1 200 close
4 E2 300 open
5 E1 100 close
采纳答案by Michael Rickman
You can do this by using declared object types and tables of declared object types.
您可以通过使用声明的对象类型和声明的对象类型表来做到这一点。
Let's start by creating the tables and data:
让我们从创建表和数据开始：
CREATE TABLE xyz
( xyz_id VARCHAR2(3)
, p_type VARCHAR2(20)
, p_amount NUMBER
);
INSERT INTO xyz VALUES ('p_1','p_id','100000');
INSERT INTO xyz VALUES ('p_2','p_id,t_status','100');
CREATE TABLE pqr
( t_id NUMBER
, p_id VARCHAR2(2)
, amount NUMBER
, t_status VARCHAR2(5)
);
INSERT INTO pqr VALUES (1,'E1',100,'open');
INSERT INTO pqr VALUES (2,'E2',200,'open');
INSERT INTO pqr VALUES (3,'E1',200,'close');
INSERT INTO pqr VALUES (4,'E2',300,'open');
INSERT INTO pqr VALUES (5,'E1',100,'close');
Next, create the declared object types:
接下来，创建声明的对象类型：
CREATE TYPE pqr_tid_sumamt_rec IS OBJECT
( t_id NUMBER
, sum_amount NUMBER
);
CREATE TYPE pqr_tid_sumamt_tab IS TABLE OF pqr_tid_sumamt_rec;
CREATE TYPE xyztab_ptstab_rec IS OBJECT
( xyz_id VARCHAR2(3)
, p_type VARCHAR2(20)
, p_amount NUMBER
, p_pqr_tid_sumamt_tab pqr_tid_sumamt_tab
);
CREATE TYPE xyztab_ptstab_tab AS TABLE OF xyztab_ptstab_rec;
Now, we'll create the function that will return a sys_refcursor with a nested table:
现在，我们将创建将返回带有嵌套表的 sys_refcursor 的函数：
CREATE OR REPLACE
FUNCTION xyz_pqr_refcursor
RETURN sys_refcursor
IS
TYPE querycursor IS REF CURSOR;
v_ref querycursor;
v_query querycursor;
v_t_id pqr.t_id%TYPE;
v_total NUMBER (38);
CURSOR varcursor IS
SELECT xyz_id,p_type,p_amount
FROM xyz;
v_temp VARCHAR2 (4000);
v_temp1 VARCHAR2 (200);
tab_pqr_tid_sumamt pqr_tid_sumamt_tab := pqr_tid_sumamt_tab();
tab_xyztab_ptstab xyztab_ptstab_tab := xyztab_ptstab_tab();
ret_cur sys_refcursor;
BEGIN
FOR varcursor_rec IN varcursor
loop
v_temp1 := varcursor_rec.p_type; --p_type contains column name of the pqr TABLE
v_temp :=
'SELECT T_ID,SUM(AMOUNT) OVER (PARTITION BY '
|| v_temp1
|| ' ORDER BY '
|| v_temp1
|| ') total from pqr';
-- dbms_output.put_line('v_temp: '||v_temp);
OPEN v_ref FOR v_temp;
loop
fetch v_ref INTO v_t_id,v_total;
exit WHEN v_ref%notfound;
IF varcursor_rec.p_amount < v_total THEN
tab_pqr_tid_sumamt.EXTEND;
-- cast the cursor values into the record type to be added to the table type
tab_pqr_tid_sumamt( tab_pqr_tid_sumamt.count ) := pqr_tid_sumamt_rec(v_t_id, v_total);
END IF;
END loop;
tab_xyztab_ptstab.EXTEND;
-- cast the cursor values and nested table type to the record type and add to the table type
tab_xyztab_ptstab(tab_xyztab_ptstab.count) := xyztab_ptstab_rec (varcursor_rec.xyz_id, varcursor_rec.p_type, varcursor_rec.p_amount, tab_pqr_tid_sumamt);
END loop;
--open the return refcursor
OPEN ret_cur FOR
SELECT xyztab_ptstab_rec(xyz_id, p_type, p_amount, p_pqr_tid_sumamt_tab) FROM TABLE( tab_xyztab_ptstab );
-- return the refcursor
RETURN ret_cur;
END xyz_pqr_refcursor;
Now, let's test the function:
现在，让我们测试一下函数：
DECLARE
test_cur sys_refcursor;
test_rec xyztab_ptstab_rec;
test_pqrtab pqr_tid_sumamt_tab;
counter int := 1;
BEGIN
test_cur := xyz_pqr_refcursor();
loop
fetch test_cur INTO test_rec;
exit WHEN test_cur%notfound;
dbms_output.put_line('test_rec('||counter||') -- xyz_id: '||test_rec.xyz_id||', p_type: '||test_rec.p_type||', p_amount: '||test_rec.p_amount);
test_pqrtab := test_rec.p_pqr_tid_sumamt_tab;
FOR j IN 1 .. test_pqrtab.count
loop
dbms_output.put_line('.... test_pqrtab('||j||') - t_id: '||test_pqrtab(j).t_id||', sumamt: '||test_pqrtab(j).sum_amount);
END loop;
counter := counter + 1;
END loop;
EXCEPTION
WHEN others THEN dbms_output.put_line('whoops: '||sqlerrm);
END;
Test results:
检测结果：
test_rec(1) -- xyz_id: p_1, p_type: p_id, p_amount: 100000
test_rec(2) -- xyz_id: p_2, p_type: p_id,t_status, p_amount: 100
.... test_pqrtab(1) - t_id: 3, sumamt: 300
.... test_pqrtab(2) - t_id: 5, sumamt: 300
.... test_pqrtab(3) - t_id: 4, sumamt: 500
.... test_pqrtab(4) - t_id: 2, sumamt: 500
Alternatively, you could just return the declared table type itself instead of a sys_refcursor.
或者，您可以只返回声明的表类型本身而不是 sys_refcursor。
I hope this helps.
我希望这有帮助。
