oracle 返回多条记录,用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;