用record类型和table类型存储查询语句返回的多条记录 的例子

DECLARE

--定義一個record type
TYPE TEST_EMP IS RECORD
(
C1 gobo_sign.org_id%TYPE,
C2 gobo_sign.proc_name%TYPE
);

[@more@]

--定義table type

type t_type is table of TEST_EMP;
v_type t_type;

BEGIN

--將select result寫入 定義的table
SELECT org_id,proc_name BULK COLLECT INTO v_type
FROM gobo_sign
WHERE rownum <= 6;

for v_index in v_type.first .. v_type.last loop
dbms_output.put_line(v_type(v_index).C1 || ' ' || v_type(v_index).C2);
end loop;
END;

結果:

0 OMR004
0 OMR110
1 BOMR002
1 BOMR003
1 OMR004
1 OMR110

另:

一个关于tape的procedure ，LZ可以看看

CREATE OR REPLACE PROCEDURE Zxt_type_Study Is
/*
type 的使用
*/
Testrow1 Test%ROWTYPE;
TYPE Aa IS TABLE OF Test%Rowtype;
Testrow Aa := Aa();
TYPE Bb IS TABLE OF Test.ac%Type;
Cc Bb;
Testrow2 Test%ROWTYPE;
t Test.Ac%TYPE;
BEGIN
Testrow2.Ac := 'a3';
Testrow2.Breanch := 'zxtss';
Testrow2.Amt := 5000;
--取update的结果
UPDATE Test SET ROW = Testrow2 WHERE Ac = Testrow2.Ac RETURNING Ac, Breanch, Amt INTO Testrow1;
--给type付值
FOR i IN (SELECT * FROM Test) LOOP
Testrow.EXTEND;
Testrow(Testrow.LAST) := i;
Testrow2 :=i;
Dbms_Output.Put_Line('ac1=' || Testrow2.Ac || ';breanch1=' || Testrow2.Breanch || ';amt1=' || Testrow2.Amt);
END LOOP;
Dbms_Output.Put_Line('ac=' || Testrow1.Ac || ';breanch=' || Testrow1.Breanch || ';amt=' || Testrow1.Amt);

--给type付值1
SELECT Test.Ac BULK COLLECT INTO Cc FROM Test;
--forall的使用
FORALL j IN Cc.FIRST .. Cc.LAST
UPDATE Test SET Test.Amt = 1000 WHERE Test.Ac = Cc(j);
--取type中的一个值
FOR y IN Cc.FIRST .. Cc.LAST LOOP
Dbms_Output.Put_Line('cc(y)' || Cc(y));
END LOOP;
Dbms_Output.Put_Line('t=' || t);
--打印执行前边的sql结果集的条数
Dbms_Output.Put_Line('sql=' || SQL%ROWCOUNT);
END Zxt_type_Study;

引自: http://www.itpub.net/717088.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92289/viewspace-896996/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/92289/viewspace-896996/