oracle put_line长度,oracle cols 查询表字段类型及长度 function

像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.

打赏

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