我想填充我的物件表，我嘗試不同的方式，但我不能。使用此代碼，我有一個廣泛的錯誤：“在期望以下之一時遇到符號“L_T_O_TYPE”：.(*@_- /”，這是一個很大的訊息
CREATE OR REPLACE TYPE O_Type AS OBJECT (
depar_id NUMBER,
depar_name VARCHAR2(20),
man_id NUMBER,
loca_id NUMBER,
CONSTRUCTOR FUNCTION O_Type(
depar_id NUMBER,
depar_name VARCHAR2,
man_id NUMBER,
loca_id NUMBER)
RETURN SELF AS RESULT
);
CREATE OR REPLACE TYPE BODY O_Type IS
CONSTRUCTOR FUNCTION O_Type(
depar_id NUMBER,
depar_name VARCHAR2,
man_id NUMBER,
loca_id NUMBER,
)RETURN AS SELF AS RESULT IS
BEGIN
self.depar_id := depar_id;
self.depar_name := depar_name;
self.man_id := man_id;
self.loca_id := loca_id;
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE T_o_type AS TABLE OF O_Type;
/
DECLARE
CURSOR C_depar IS SELECT *
FROM departments;
TYPE T_C_DEPAR IS TABLE OF C_depar%ROWTYPE;
L_TABLE T_C_DEPAR;
l_T_o_type T_o_type;
BEGIN
l_T_o_type := T_o_type();
OPEN C_depar;
LOOP
FETCH C_depar BULK COLLECT INTO L_TABLE;
EXIT WHEN C_depar%NOTFOUND;
FORALL i IN 1..L_TABLE.count
l_T_o_type(i) : = T_o_type(L_TABLE.DEPARTMENT_ID, L_TABLE.DEPARTMENT_NAME, L_TABLE.MANAGER_ID , L_TABLE.LOCATION_ID)
END LOOP;
CLOSE C_depar;
END;
/
有人能告訴我填充物件表的最佳方法嗎？
uj5u.com熱心網友回復：
“最好”是主觀的。
如果您想要單個集合中的所有行，請使用SELECT ... BULK COLLECT INTO：
DECLARE
l_depts T_o_type;
BEGIN
SELECT O_Type(id, name, man_id, loc_id)
BULK COLLECT INTO l_depts
FROM departments;
FOR i IN 1 .. l_depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
l_depts(i).depar_id
|| ' ' || l_depts(i).depar_name
|| ' ' || l_depts(i).man_id
|| ' ' || l_depts(i).loca_id
);
END LOOP;
END;
/
如果要批量處理行，則可以使用：
DECLARE
CURSOR C_depar IS
SELECT O_Type(id, name, man_id, loc_id)
FROM departments;
l_depts T_o_type;
BEGIN
OPEN C_depar;
LOOP
FETCH C_depar BULK COLLECT INTO L_depts LIMIT 10;
FOR i IN 1 .. l_depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
l_depts(i).depar_id
|| ' ' || l_depts(i).depar_name
|| ' ' || l_depts(i).man_id
|| ' ' || l_depts(i).loca_id
);
END LOOP;
EXIT WHEN C_depar%NOTFOUND;
END LOOP;
CLOSE C_depar;
END;
/
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448722.html
上一篇：我怎樣才能加入4張桌子