ANBOB™

像desc 一样，用sql 查询表字段类型及长度，不多说很简单

   anbob@ORCL> CREATE TABLE TEST_DATATYPE (ID INT,ID2 NUMBER(20),ID3 CHAR(2),ID4 DATE,ID5 VARCHAR2(30));
 
Table created.
 
anbob@ORCL> select table_name,column_name,data_type,data_length
  2  FROM cols where table_name='TEST_DATATYPE';
 
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            DATA_LENGTH
------------------------------ ------------------------------ -------------------- -----------
TEST_DATATYPE                  ID                             NUMBER                        22
TEST_DATATYPE                  ID2                            NUMBER                        22
TEST_DATATYPE                  ID3                            CHAR                           2
TEST_DATATYPE                  ID4                            DATE                           7
TEST_DATATYPE                  ID5                            VARCHAR2                      30
 

COLS是USER_TAB_COLUMNS.的同义词，USER_TAB_COLUMNS视图是又是根据USER_TAB_COLS创建.USER_TAB_COLS是来建立在基表上且类型名称也是写在sql代码decode中的，因篇幅原因不再描述

下面创建个procedure返回

create or replace procedure gettype(tname varchar2,cname varchar2)
/**
by anbob.com
discribe:get columns type fo table
param: tname is table name
param: cname is column
**/
is
v_ctype varchar2(30);
v_len number;
begin
select data_type,data_length into v_ctype,v_len from cols where table_name=upper(tname) and column_name =upper(cname);
dbms_output.put_line('info: this column  ['||cname||']of table ['||tname||'] type is >'||v_ctype||' ,lenth>'||v_len); 
end;

anbob@ORCL> exec gettype('test_datatype','id');
info: this column  [id]of table [test_datatype] type is >NUMBER ,lenth>22
 
PL/SQL procedure successfully completed.
 
anbob@ORCL> exec gettype('test_datatype','id4');
info: this column  [id4]of table [test_datatype] type is >DATE ,lenth>7
 
PL/SQL procedure successfully completed.

打赏

微信扫一扫，打赏作者吧～