RAM Table in Oracle(oracle内存表)

来源：互联网发布：辽宁金税三期软件下载编辑：程序博客网时间：2024/06/07 23:28

oracle-内存表
一、    内存表概念
1、PL/SQL表类似于C语言中的数组。如果要声明一个PL/SQL表，要先定义该表类型，然后在声明属于该类型的变量。
2、理论上，数据库数据空间有多大，我们的内存表就可以存储多大的数据，就是说他和我们的物理表是相同的，我们可以把物理表的数据完全拷贝到内存表中。
3、PL/SQL表的元素没有必要按照特定的次序排列，因为他们不是象数组那样连续存储在内存中，元素可以按照任意键值进行插入。
4、PL/SQL表的键值（KEY）没有必要是顺序的。表所占用的内存并不依赖于键所使用的数值。
二、    声明内存表
DECLARE
-- 定义表类型
TYPE T_USRID_ARRAY IS TABLE OF TF_F_USRARCH_MAIN.USRID%TYPE INDEX BY BINARY_INTEGER;
G_USERID T_USRID_ARRAY;
BEGIN
NULL;
END;
我们也可以定义表类型为一个表的所有字段，例如：
DECLARE
-- 主表结构
TYPE T_USRREC IS RECORD(usrid              tf_f_usrarch_main.usrid%TYPE,
                          mphonecode         tf_f_usrarch_main.mphonecode%TYPE,
                          citycode           tf_f_usrarch_main.citycode%TYPE,
                          servicecode        tf_f_usrarch_main.servicecode%TYPE,
                          opendate           tf_f_usrarch_main.opendate%TYPE,
                          firststoptime      tf_f_usrarch_main.firststoptime%TYPE,
                          usrstatecodeset    tf_f_usrarch_main.usrstatecodeset%TYPE,
                          utag3              tf_f_usrarch_main.utag3%TYPE,
                          callrankcode       tf_f_usrarch_main.callrankcode%TYPE,
                          roamrankcode       tf_f_usrarch_main.roamrankcode%TYPE,
                          advancepay         tf_f_usrarch_main.advancepay%TYPE,
                          ureservvalue       tf_f_usrarch_main.ureservvalue%TYPE,
                          creditfactor5      tf_f_usrarch_main.creditfactor5%TYPE);
-- 定义表类型
TYPE T_USRREC_ARRAY IS TABLE OF T_USRREC INDEX BY BINARY_INTEGER;
G_USERID T_USRREC_ARRAY;
BEGIN
NULL;
END;
三、    对表类型的引用
DECLARE
-- 定义表类型
TYPE T_USRID_ARRAY IS TABLE OF TF_F_USRARCH_MAIN.USRID%TYPE INDEX BY BINARY_INTEGER;
G_USERID T_USRID_ARRAY;
V_USRID TF_F_USRARCH_MAIN.USRID%TYPE;
BEGIN
SELECT USRID INTO V_USRID FROM TF_F_USRARCH_MAIN WHERE MPHONECODE = ‘13351785505’;
G_USERID(1) := V_USRID;
G_USERID(2) := V_USRID;
G_USERID(10) := V_USRID;
G_USERID(-2) := V_USRID;
DBMS_OUTPUT.PUT_LINE(G_USERID(1));
DBMS_OUTPUT.PUT_LINE(G_USERID(2));
DBMS_OUTPUT.PUT_LINE(G_USERID(10));
DBMS_OUTPUT.PUT_LINE(G_USERID(-2));
END;
执行时设置：set serveroutput on
DECLARE
-- 主表结构
TYPE T_USRREC IS RECORD(usrid              tf_f_usrarch_main.usrid%TYPE,
                          mphonecode         tf_f_usrarch_main.mphonecode%TYPE,
                          citycode           tf_f_usrarch_main.citycode%TYPE,
                          servicecode        tf_f_usrarch_main.servicecode%TYPE,
                          opendate           tf_f_usrarch_main.opendate%TYPE,
                          firststoptime      tf_f_usrarch_main.firststoptime%TYPE,
                          usrstatecodeset    tf_f_usrarch_main.usrstatecodeset%TYPE,
                          utag3              tf_f_usrarch_main.utag3%TYPE,
                          callrankcode       tf_f_usrarch_main.callrankcode%TYPE,
                          roamrankcode       tf_f_usrarch_main.roamrankcode%TYPE,
                          advancepay         tf_f_usrarch_main.advancepay%TYPE,
                          ureservvalue       tf_f_usrarch_main.ureservvalue%TYPE,
                          creditfactor5      tf_f_usrarch_main.creditfactor5%TYPE);
-- 定义游标类型
TYPE T_USRREC_CUR IS REF CURSOR;
RETURN T_USRREC;
-- 定义表类型
TYPE T_USRREC_ARRAY IS TABLE OF T_USRREC INDEX BY BINARY_INTEGER;

-- 定义游标
CUR_TF_F_USRARCH_MAIN T_USRREC_CUR;
-- 定义内存表
G_USER T_USRREC_ARRAY;
PersonKind T_USRREC;
v_cur BINARY_INTEGER;
BEGIN
v_cur := 0;
OPEN cur_tf_f_usrarch_main
        FOR SELECT usrid,
                     mphonecode,
                     citycode,
                     servicecode,
                     opendate,
                     firststoptime,
                     SUBSTR(usrstatecodeset,-1,1),
                     utag3,
                     callrankcode,
                     roamrankcode,
                     advancepay,
                     ureservvalue,
                     creditfactor5
                FROM tf_f_usrarch_main
               WHERE removetag = '0'
                 AND substr(usrid, -2, 2) = '00'
                 AND rownum<100;
         LOOP
            v_cur := v_cur+1;
            FETCH cur_tf_f_usrarch_main INTO PersonKind;
            EXIT WHEN cur_tf_f_usrarch_main%NOTFOUND;

            G_USER(v_cur).usrid           := PersonKind.usrid;
            G_USER(v_cur).mphonecode      := PersonKind.mphonecode;
            G_USER(v_cur).citycode        := PersonKind.citycode;
            G_USER(v_cur).servicecode     := PersonKind.servicecode;
            G_USER(v_cur).opendate        := PersonKind.opendate;
            G_USER(v_cur).firststoptime   := PersonKind.firststoptime;
            G_USER(v_cur).usrstatecodeset := PersonKind.usrstatecodeset;
            G_USER(v_cur).utag3           := PersonKind.utag3;
            G_USER(v_cur).callrankcode    := PersonKind.callrankcode;
            G_USER(v_cur).roamrankcode    := PersonKind.roamrankcode;
            G_USER(v_cur).advancepay      := PersonKind.advancepay;
            G_USER(v_cur).ureservvalue    := PersonKind.ureservvalue;
            G_USER(v_cur).creditfactor5   := PersonKind.creditfactor5;
         END LOOP;

         CLOSE cur_tf_f_usrarch_main;
         DBMS_OUTPUT.PUT_LINE(G_USER(1).mphonecode||’ ’||G_USER(1).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(2).mphonecode||’ ’||G_USER(2).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(3).mphonecode||’ ’||G_USER(3).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(10).mphonecode||’ ’||G_USER(10).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(21).mphonecode||’ ’||G_USER(21).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(34).mphonecode||’ ’||G_USER(34).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(46).mphonecode||’ ’||G_USER(46).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(67).mphonecode||’ ’||G_USER(67).advancepay);
         DBMS_OUTPUT.PUT_LINE(G_USER(89).mphonecode||’ ’||G_USER(89).advancepay);
END;
四、    表的属性
count：返回PL/SQL表中行的当前数目。
delete：删除表中的行。
exists：如果指定的表项在表中存在那么返回ture。
first：返回表中第一行的索引。
last：返回表中最后一行的索引。
next：返回表中指定行的下一行的索引。
prior：返回表中指定行的上一行的索引。
例如：
DECLARE
-- 定义表类型
TYPE T_USRID_ARRAY IS TABLE OF TF_F_USRARCH_MAIN.USRID%TYPE INDEX BY BINARY_INTEGER;
G_USERID T_USRID_ARRAY;
V_USRID TF_F_USRARCH_MAIN.USRID%TYPE;
BEGIN
SELECT USRID INTO V_USRID FROM TF_F_USRARCH_MAIN WHERE MPHONECODE = ‘13351785505’;
G_USERID(1) := V_USRID;
G_USERID(2) := V_USRID;
G_USERID(10) := V_USRID;
G_USERID(-2) := V_USRID;
DBMS_OUTPUT.PUT_LINE(G_USERID(1));
DBMS_OUTPUT.PUT_LINE(G_USERID(2));
DBMS_OUTPUT.PUT_LINE(G_USERID(10));
DBMS_OUTPUT.PUT_LINE(G_USERID(-2));
DBMS_OUTPUT.PUT_LINE(‘row number sum: ’|| G_USERID.COUNT);
END;
DELETE 属性会删除PL/SQL表中的行，TABLE.DELETE会删除该表中的所有行，TABLE.DELETE(i)，从表中删除由索引i所标记的行，TABLE.DELETE(i,j)，从表中删除位于索引i和j 之间的所有行。
DECLARE
-- 定义表类型
TYPE T_USRID_ARRAY IS TABLE OF TF_F_USRARCH_MAIN.USRID%TYPE INDEX BY BINARY_INTEGER;
G_USERID T_USRID_ARRAY;
V_USRID TF_F_USRARCH_MAIN.USRID%TYPE;
BEGIN
SELECT USRID INTO V_USRID FROM TF_F_USRARCH_MAIN WHERE MPHONECODE = ‘13351785505’;
G_USERID(1) := V_USRID;
G_USERID(2) := V_USRID;
G_USERID(10) := V_USRID;
G_USERID(-2) := V_USRID;
DBMS_OUTPUT.PUT_LINE(G_USERID(1));
DBMS_OUTPUT.PUT_LINE(G_USERID(2));
DBMS_OUTPUT.PUT_LINE(G_USERID(10));
DBMS_OUTPUT.PUT_LINE(G_USERID(-2));
IF G_USERID.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE(‘row number(1) exists’);
END IF;
IF G_USERID.EXISTS(9) THEN
DBMS_OUTPUT.PUT_LINE(‘row number(9) exists’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘row number(9) not exists’);
END IF;
END;

DECLARE
-- 定义表类型
TYPE T_USRID_ARRAY IS TABLE OF TF_F_USRARCH_MAIN.USRID%TYPE INDEX BY BINARY_INTEGER;
G_USERID T_USRID_ARRAY;
V_USRID TF_F_USRARCH_MAIN.USRID%TYPE;
V_INDEX BINARY_INTEGER;
BEGIN
SELECT USRID INTO V_USRID FROM TF_F_USRARCH_MAIN WHERE MPHONECODE = ‘13351785505’;
G_USERID(1) := V_USRID;
G_USERID(2) := V_USRID;
G_USERID(10) := V_USRID;
G_USERID(-2) := V_USRID;
V_INDEX := G_USERID.FIRST;
LOOP
DBMS_OUTPUT.PUT_LINE(V_INDEX||’ ‘||G_USERID(V_INDEX));
V_INDEX := G_USERID.NEXT(V_INDEX);
EXIT WHEN V_INDEX = G_USERID.LAST;
END LOOP;
DBMS_OUTPUT.PUT_LINE(G_USERID.LAST||’ ‘|| G_USERID(G_USERID.LAST));
END;
五、    BULK COLLECT 关键字的引用
BULK COLLECT是一个PL/SQL语句，而不是SQL语言的一部分。因此，如果想用SQL执行一个BULK COLLECT操作，我必须在一个PL/SQL块内进行操作。例如：
DECLARE
   TYPE UsrId_Array IS TABLE OF NUMBER;
   TYPE MphoneCode_Array IS TABLE OF NUMBER;
   vusrid UsrId_Array;
   vmphonecode MphoneCode_Array;

   type test_type is table of tf_f_usrarch_main%rowtype;

   CURSOR cur_tf_f_usrarch_main IS
   SELECT usrid, mphonecode
     FROM tf_f_usrarch_main
    WHERE rownum<10;

   CURSOR cur_tf_f_usrarch_main1 IS
   SELECT *
     FROM tf_f_usrarch_main
    WHERE rownum<10;

   temp NUMBER;

   temp1 test_type := test_type();
BEGIN
   OPEN cur_tf_f_usrarch_main;
   FETCH cur_tf_f_usrarch_main BULK COLLECT INTO vusrid, vmphonecode;

   dbms_output.put_line(to_char(vusrid.count));
   FOR temp IN 1..vusrid.count
   LOOP
     dbms_output.put_line(vusrid(temp));
   END LOOP;
END;
//============================================================================

Another Document referenced

----------------------------------------------

PL/SQL表---table()函数用法

PL/SQL表---table()函数用法：
利用table()函数，我们可以将PL/SQL返回的结果集代替table。
oracle内存表在查询和报表的时候用的比较多，它的速度相对物理表要快几十倍。
simple example：
1、table()结合数组：
*/

create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);

create or replace type t_test_table as table of t_test;

create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
end loop;
return v_test;
end f_test_array;
/

select * from table(f_test_array(10));

select * from the(select f_test_array(10) from dual);

/*
2、table()结合PIPELINED函数：
*/

create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
for i in 1 .. nvl(n,100) loop
pipe row(t_test(i,sysdate,'mc'||i));
end loop;
return;
end f_test_pipe;
/

select * from table(f_test_pipe(20));

select * from the(select f_test_pipe(20) from dual);

/*
3、table()结合系统包：
*/

create table test (id varchar2(20));
insert into test values('1');
commit;
explain plan for select * from test;
select * from table(dbms_xplan.display);