RAM Table in Oracle(oracle内存表)

骑猪看日落 2022-08-24 01:59 214阅读 0赞

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);