oracle-内存表

Innovation

UID: 36029
帖子: 194
积分: 446
在线时间: 2 天 3 小时

1^# Innovation 发表于 2006-10-18 22:05

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;
  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));
  V_INDEX := G_USERID.FIRST;
  DBMS_OUTPUT.PUT_LINE(‘FIRST ROW INDEX’||’  ’||V_INDEX);
  V_INDEX := G_USERID.LAST;
  DBMS_OUTPUT.PUT_LINE(‘LAST ROW INDEX’||’  ’||V_INDEX);
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;
  TYPE T_USRID_ARRAY IS TABLE OF TF_F_USRARCH_MAIN.USRID%TYPE INDEX BY BINARY_INTEGER;
  G_USERID T_USRID_ARRAY;
BEGIN