Implementation of Built-In Data type

cuiyongzhou6314

于 2009-02-06 16:37:36 发布

阅读量73

点赞数

data dictionary 存储表中列的数据类型等一系列信息

SQL> desc linyu.test
Name Null? Type
----------------------------------------- -------- ----------------------------
X NUMBER

SQL> select object_id from dba_objects t where t.object_name='TEST';

OBJECT_ID
----------
52492

SQL> select obj#,length,type# from col$ t1 where t1.obj#=52492;

OBJ# LENGTH TYPE#
---------- ---------- ----------
52492 22 2

SQL> desc scott.emp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

SQL> select object_id from dba_objects t where t.object_name='EMP';

OBJECT_ID
----------
51148
52611
SQL> select obj#,length,type# from col$ t1 where t1.obj#=51148;

      OBJ#     LENGTH      TYPE#
---------- ---------- ----------
     51148         22          2
     51148         10          1
     51148          9          1
     51148         22          2
     51148          7         12
     51148         22          2
     51148         22          2
     51148         22          2

CHAR:Fixed length character string up to 2,000 characters

internal code:96

varchar2:variable length character string up to 4,000 characters

internal code:1

SQL> select dump(ename,16) from scott.emp;

DUMP(ENAME,16)
--------------------------------------------------------------------------------
Typ=1 Len=5: 53,4d,49,54,48
Typ=1 Len=5: 41,4c,4c,45,4e
Typ=1 Len=4: 57,41,52,44
Typ=1 Len=5: 4a,4f,4e,45,53
Typ=1 Len=6: 4d,41,52,54,49,4e
Typ=1 Len=5: 42,4c,41,4b,45
Typ=1 Len=5: 43,4c,41,52,4b
Typ=1 Len=5: 53,43,4f,54,54
Typ=1 Len=4: 4b,49,4e,47
Typ=1 Len=6: 54,55,52,4e,45,52
Typ=1 Len=5: 41,44,41,4d,53

DUMP(ENAME,16)
--------------------------------------------------------------------------------
Typ=1 Len=5: 4a,41,4d,45,53
Typ=1 Len=4: 46,4f,52,44
Typ=1 Len=6: 4d,49,4c,4c,45,52

SQL> select dump(ename,17) from scott.emp;

DUMP(ENAME,17)
--------------------------------------------------------------------------------
Typ=1 Len=5: S,M,I,T,H
Typ=1 Len=5: A,L,L,E,N
Typ=1 Len=4: W,A,R,D
Typ=1 Len=5: J,O,N,E,S
Typ=1 Len=6: M,A,R,T,I,N
Typ=1 Len=5: B,L,A,K,E
Typ=1 Len=5: C,L,A,R,K
Typ=1 Len=5: S,C,O,T,T
Typ=1 Len=4: K,I,N,G
Typ=1 Len=6: T,U,R,N,E,R
Typ=1 Len=5: A,D,A,M,S

DUMP(ENAME,17)
--------------------------------------------------------------------------------
Typ=1 Len=5: J,A,M,E,S
Typ=1 Len=4: F,O,R,D
Typ=1 Len=6: M,I,L,L,E,R

14 rows selected.

2.NCHAR:Fixed length character string up to 2,000 bytes

Internal code:96

NVARCHAR2:Variable length character string up to 4,000 bytes

Internal code:1

数值转换

You can use dbms_stats.convert_raw_value, among many other ways.

SQL> select dump(1,16) from dual;

DUMP(1,16)
----------------------------------
Typ=2 Len=2: c1,2

SQL> set serverout on
SQL>
SQL> declare n number;
2 begin
3 dbms_stats.convert_raw_value('C102',n);
4 dbms_output.put_line(n);
5 end;
6 /

Date:Fixed length seven byte.

internal code:12

SQL> select dump(hiredate),hiredate from scott.emp;

DUMP(HIREDATE)
--------------------------------------------------------------------------------
HIREDATE
---------
Typ=12 Len=7: 119,180,12,17,1,1,1
17-DEC-80
century:119-100=19

year:180-100=80

month:12

dat:17

hours:1-1=0

minutes:1-1=0

seconds:1-1=0

timestamp :Fixed length 11-byte arrary;

the first seven bytes are the same as in date

the last four bytes are fractions for seconds

internal code:180

timestamp with time zone:fixed length 13-byte array;

the first seven bytes are the same as in date

the next four bytes are fractions for seconds

the last two bytes are the time zone hour and the time zone minute respectively

internal code:181

TIMESTAMP WITH LOCAL TIME ZONE:Fixed length 11-byte array:

the first seven bytes are the same as in date

the last four bytes are fractions for seconds

internal code:231

sysdate: Fixed length eight-byte format:

century,year

month,day

hour,minute,second

unused

internal code:13

SQL> select sysdate,dump(sysdate) from dual;

SYSDATE
---------
DUMP(SYSDATE)
--------------------------------------------------------------------------------
07-FEB-09
Typ=13 Len=8: 217,7,2,7,18,38,52,0
当>128时7-128

7*256+217=2009

2月

7日

18:38:52

0没有使用

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7301064/viewspace-545458/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7301064/viewspace-545458/