CREATE OR REPLACE TYPE O_T_emplo AS OBJECT (
L_o_first_name VARCHAR2(30),
L_o_last_name VARCHAR2(30),
L_o_depar NUMBER,
CONSTRUCTOR FUNCTION O_T_emplo(
L_o_first_name VARCHAR2(30),
L_o_last_name VARCHAR2(30),
L_o_depar NUMBER)
RETURN SELF AS RESULT
);
/
CREATE OR REPLACE TYPE BODY O_T_emplo IS
CONSTRUCTOR FUNCTION O_T_emplo(
L_o_first_name VARCHAR2(30),
L_o_last_name VARCHAR2(30),
L_o_depar NUMBER
) RETURN SELF AS RESULT IS
BEGIN
SELF.L_o_first_name := L_o_first_name;
SELF.L_o_last_name := L_o_last_name;
self.L_o_depar := L_o_depar;
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE tab_obj AS TABLE OF O_T_emplo;
/
使用它plsql：
DECLARE
tab_emps tab_obj;
info_emps O_T_emplo;
CURSOR C_infos IS
SELECT e.first_name, e.last_name, e.department_id
FROM employees e;
infos C_infos%ROWTYPE;
BEGIN
tab_emps := tab_obj();
OPEN C_infos;
LOOP
FETCH C_infos INTO infos;
info_emps := O_T_emplo(infos.first_name, infos.last_name, infos.department_id);
tab_emps.extend();
tab_emps(tab_emps.LAST) := info_emps;
EXIT WHEN C_infos%NOTFOUND;
END LOOP;
CLOSE C_infos;
END;
/
誰能幫我？
uj5u.com熱心網友回復：
- 在型別中，從
VARCHAR2宣告和正文中的建構式簽名中的資料型別中洗掉長度。 - 在 PL/SQL 塊中，更改回圈中的順序，以便
EXIT緊跟在FETCH;之后 如果你反過來做，那么游標的最后一行將被插入兩次。 - 或者，您可以洗掉
info_emps中間變數。
CREATE OR REPLACE TYPE O_T_emplo AS OBJECT (
L_o_first_name VARCHAR2(30),
L_o_last_name VARCHAR2(30),
L_o_depar NUMBER,
CONSTRUCTOR FUNCTION O_T_emplo(
L_o_first_name VARCHAR2,
L_o_last_name VARCHAR2,
L_o_depar NUMBER
) RETURN SELF AS RESULT
)
/
CREATE OR REPLACE TYPE BODY O_T_emplo IS
CONSTRUCTOR FUNCTION O_T_emplo(
L_o_first_name VARCHAR2,
L_o_last_name VARCHAR2,
L_o_depar NUMBER
) RETURN SELF AS RESULT
IS
BEGIN
SELF.L_o_first_name := L_o_first_name;
SELF.L_o_last_name := L_o_last_name;
self.L_o_depar := L_o_depar;
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE tab_obj AS TABLE OF O_T_emplo;
/
然后：
DECLARE
tab_emps tab_obj;
CURSOR C_infos IS SELECT e.first_name, e.last_name, e.department_id FROM employees e;
infos C_infos%ROWTYPE;
BEGIN
tab_emps := tab_obj();
OPEN C_infos;
LOOP
FETCH C_infos INTO infos;
EXIT WHEN C_infos%NOTFOUND;
tab_emps.extend();
tab_emps(tab_emps.LAST) := O_T_emplo(
infos.first_name,
infos.last_name,
infos.department_id
);
END LOOP;
CLOSE C_infos;
FOR i IN 1 .. tab_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
tab_emps(i).l_o_first_name || ', ' ||
tab_emps(i).l_o_last_name || ', ' ||
tab_emps(i).l_o_depar
);
END LOOP;
END;
/
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448733.html
上一篇：函式重調SysRefcursor