[20111219]关于anadata.txt

[20111219]关于anadata.txt

想研究一下anadata,由于这个链接:
http://space.itpub.net/267265/viewspace-713215

google
发现如下：
http://www.orafaq.com/node/1853

1.建立表：
create table t1 (a number not null,b varchar2(20),c sys.anydata);

insert into t1 values (1,'number',sys.anydata.convertnumber(1));
insert into t1 values (2,'date',sys.anydata.convertdate(sysdate));
insert into t1 values (3,'varchar2',sys.anydata.convertvarchar2('a'));
insert into t1 values (4,'timedtamp',sys.anydata.converttimestamp(systimestamp));
commit;

2.
SQL> col c format a20 trunc
SQL> select * from t1;

         A B                    C()
---------- -------------------- --------------------
         1 number               ANYDATA()
         2 date                 ANYDATA()
         3 varchar2             ANYDATA()
         4 timedtamp            ANYDATA()

SQL> col typename format a20 trunc
SQL> select t1.*,sys.anydata.gettypename(temp1.b) typename from t1;

SQL> select t1.*,sys.anydata.gettypename(t1.c) typename from t1;

         A B                    C()                  TYPENAME
---------- -------------------- -------------------- --------------------
         1 number               ANYDATA()            SYS.NUMBER
         2 date                 ANYDATA()            SYS.DATE
         3 varchar2             ANYDATA()            SYS.VARCHAR2
         4 timedtamp            ANYDATA()            SYS.TIMESTAMP


3.建立包:

create or replace
package pkg_anydata
as
   function getnumber (anydata_p in sys.anydata) return number;
   function getdate (anydata_p in sys.anydata) return date;
   function getvarchar2 (anydata_p in sys.anydata) return varchar2;
   function gettimestamp (anydata_p in sys.anydata) return timestamp;
end;
/
show errors

create or replace
package body pkg_anydata
as
   function getnumber (anydata_p in sys.anydata) return number is
      x number;
      thenumber_v number;
   begin
      x := anydata_p.getnumber(thenumber_v);
      return (thenumber_v);
   end;

   function getdate (anydata_p in sys.anydata) return date is
      x number;
      thedate_v date;
   begin
      x := anydata_p.getdate(thedate_v);
      return (thedate_v);
   end;

   function getvarchar2 (anydata_p in sys.anydata) return varchar2 is
      x number;
      thevarchar2_v varchar2(4000);
   begin
      x := anydata_p.getvarchar2(thevarchar2_v);
      return (thevarchar2_v);
   end;

   function gettimestamp (anydata_p in sys.anydata) return timestamp is
      x number;
      thetimestamp_v timestamp;
   begin
      x := anydata_p.gettimestamp(thetimestamp_v);
      return (thetimestamp_v);
   end;

end;
/
show errors

With this package in place we can now see our data.

col thevalue format a20 trunc
SELECT t1.*, SYS.ANYDATA.gettypename (t1.c) typename,
       CASE
          WHEN SYS.ANYDATA.gettypename (t1.c) = 'SYS.NUMBER'
             THEN TO_CHAR (pkg_anydata.getnumber (t1.c))
          WHEN SYS.ANYDATA.gettypename (t1.c) = 'SYS.DATE'
             THEN TO_CHAR (pkg_anydata.getdate (t1.c), 'YYYY-MM-DD HH24:MI:SS')
          WHEN SYS.ANYDATA.gettypename (t1.c) = 'SYS.VARCHAR2'
             THEN pkg_anydata.getvarchar2 (t1.c)
          WHEN SYS.ANYDATA.gettypename (t1.c) = 'SYS.TIMESTAMP'
             THEN TO_CHAR (pkg_anydata.gettimestamp (t1.c), 'YYYY-MM-DD HH24:MI:SS.FF')
       END thevalue
  FROM t1
/