FETCH语句

语法：

fetch_statement ::=

fetch_statement::=
    FETCH cursor {into_clause | bulk_collect_into_clause};

bulk_collect_into_clause ::=

bulk_collect_into_clause::= BULK COLLECT INTO {collection[,...n]}

示例1：将数据绑定到集合中

我们可以从游标中批量取得数据并绑定到一个或多个集合中

--  清理环境
DROP TABLE tab;

CREATE TABLE tab 
(
    ID INTEGER,
    NUM INTEGER,
    NAME VARCHAR2(15)
);
INSERT INTO tab VALUES(1, 11,'1A');
INSERT INTO tab VALUES(2, 22,'2B');
INSERT INTO tab VALUES(3, 33,'3C');
INSERT INTO tab VALUES(4, 44,'4D');
INSERT INTO tab VALUES(5, 55,'5E');

DECLARE
    TYPE idtab IS TABLE OF tab.ID%TYPE;
    TYPE nametab IS TABLE OF tab.NAME%TYPE;
    CURSOR c1 IS
        SELECT ID, NAME FROM tab WHERE id > 2;
    idt idtab;
    namet nametab;
BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT INTO idt, namet;   
END;
/

--删除
DROP TABLE tab;

示例2：将数据绑定到记录类型中

可以批量取得数据并绑定到记录类型的集合中

--  清理环境
DROP TABLE tab CASCADE;

CREATE TABLE tab 
(
    ID INTEGER,
    NUM INTEGER,
    NAME VARCHAR2(2)
);
INSERT INTO tab VALUES(1, 11,'1A');
INSERT INTO tab VALUES(2, 22,'2B');
INSERT INTO tab VALUES(3, 33,'3C');
INSERT INTO tab VALUES(4, 44,'4D');
INSERT INTO tab VALUES(5, 55,'5E');

DECLARE
    TYPE ttab IS TABLE OF tab%ROWTYPE;
    mytab ttab;
    CURSOR c1 IS
        SELECT ID, NUM, NAME FROM tab
            WHERE ID > 2;
BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT INTO mytab;
END;
/
--删除
DROP TABLE tab CASCADE;