oracle 获取表名称，字段
with vA
as
(
SELECT USER_TAB_COLS.TABLE_NAME
as 表名,USER_TAB_COLS.COLUMN_NAME
as 列名 ,
USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH
as 长度, USER_TAB_COLS.NULLABLE
as 是否为空,
USER_TAB_COLS.COLUMN_ID as 列序号,user_col_comments.comments
as 备注
FROM USER_TAB_COLS
inner join user_col_comments
on user_col_comments.TABLE_NAME
=USER_TAB_COLS.TABLE_NAME
and user_col_comments.COLUMN_NAME
=USER_TAB_COLS.COLUMN_NAME
where USER_TAB_COLS.TABLE_NAME
like ‘T_A_%‘ and USER_TAB_COLS.COLUMN_NAME
like ‘%CHECK_USER%‘
)
select ‘alter table ‘ ||表名
||‘ modify ‘||列名
||‘ VARCHAR2(120) ;‘
from vA
--新增字段
alter table table_name
add(userName
VARCHAR2(
255));
--设置字段不为空, 给出默认值
alert
table table_name
add(userName
varchar2(
255 CHAR)
default ‘这是默认值‘ not null) ;
--修改列字段长度
alter table T_A_DRUG modify DRUG_PRODUCT_CODE
VARCHAR2(
50);
--修改列名称
alter table T_A_DRUG rename
column PRODUCT_NAME
to DRUG_PRODUCT_CODE;
--修改列字段类型
alter table T_FILE_UPLOAD_www modify (upload_date
varchar2(
50));
--删除字段
alter table table_name
drop column userName;
--修改表名称
alter table old_table_name RENAME
TO new_table_name;
rename 旧表名称 to 新表名称;
--删除表中已有的主键约束（有命名）
alter table students
drop constraint yy;
--删除表中已有的主键约束（无命名）
可用
SELECT * from user_cons_columns;
查找表中主键名称得student表中的主键名为SYS_C002715
alter table student
drop constraint SYS_C002715;
--向表中添加主键约束
alter table student
add constraint pk_student
primary key(studentid);
--查看表中约束
--oracle 更改名称：
--查询所有表名：
select t.table_name
from user_tables t;
--查询所有字段名：
select t.column_name
from user_col_comments t;
--查询指定表的所有字段名：
select t.column_name
from user_col_comments t
where t.table_name
= ‘BIZ_DICT_XB‘;
--查询指定表的所有字段名和字段说明：
select t.column_name, t.column_name
from user_col_comments t
where t.table_name
= ‘BIZ_DICT_XB‘;
--查询所有表的表名和表说明：
select t.table_name,f.comments
from user_tables t
inner join user_tab_comments f
on t.table_name
= f.table_name;
--查询模糊表名的表名和表说明：
select t.table_name
from user_tables t
where t.table_name
like ‘BIZ_DICT%‘;
select t.table_name,f.comments
from user_tables t
inner join user_tab_comments f
on t.table_name
= f.table_name
where t.table_name
like ‘BIZ_DICT%‘;
--查询表的数据条数、表名、中文表名
select a.num_rows, a.TABLE_NAME, b.COMMENTS
from user_tables a, user_tab_comments b
WHERE a.TABLE_NAME
= b.TABLE_NAME
order by TABLE_NAME;
一，查看数据库里面所有用户：
select * from dba_users;
前提是你是有dba权限的帐号，如sys,system。
二，查看你能管理的所有用户：
select * from all_users;
三，查看当前用户信息 ：
select * from user_users;
1、创建存储过程
create or replace
procedure sql_test(sex
in nvarchar2, out_return out sys_refcursor)
is
begin
open out_return
for ‘select sex,sex_name from t_a_sex‘;
end;
2、调用带输出列表的存储过程
var type_cur refcursor
var para nvarchar2(
20)
execute :para :
=‘1‘;
exec pro1(:para,:type_cur);
print type_cur;
-- 日期
select to_char(sysdate,
‘yyyy-MM-dd HH24:MI:SS‘)
as vDate
from dual;
--获取表名称、字段、等信息
with vM
as
(
select * from all_col_comments
where OWNER
=‘BYYY‘
and (table_name
like ‘T_FILE%‘ or table_name
like ‘T_RULE%‘ or table_name
like ‘T_JOBLOG%‘
or table_name
like ‘T_REPORT_LOG%‘ or table_name
like ‘T_A%‘ or table_name
like ‘T_E%‘)
and table_name
not like ‘T_AUTH%‘ and table_name
not like ‘T_ERROR%‘
),vA as
(
SELECT USER_TAB_COLS.TABLE_NAME
as 表名,USER_TAB_COLS.COLUMN_NAME
as 列名 ,
USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH
as 长度, USER_TAB_COLS.NULLABLE
as 是否为空,
USER_TAB_COLS.COLUMN_ID as 列序号,user_col_comments.comments
as 备注
FROM all_TAB_COLS USER_TAB_COLS
inner join vM user_col_comments
on user_col_comments.TABLE_NAME
=USER_TAB_COLS.TABLE_NAME
and user_col_comments.COLUMN_NAME
=USER_TAB_COLS.COLUMN_NAME
where USER_TAB_COLS.OWNER
=‘BYYY‘
)
select *
from vA
order by 表名,列名
with vM
as
(
select * from all_col_comments
where OWNER
=‘BASE‘
and (table_name
like ‘T_FILE%‘ or table_name
like ‘T_RULE%‘ or table_name
like ‘T_JOBLOG%‘
or table_name
like ‘T_REPORT_LOG%‘ or table_name
like ‘T_A%‘ or table_name
like ‘T_E%‘)
and table_name
not like ‘T_AUTH%‘ and table_name
not like ‘T_ERROR%‘
),vA as
(
SELECT USER_TAB_COLS.TABLE_NAME
as 表名,USER_TAB_COLS.COLUMN_NAME
as 列名 ,
USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH
as 长度, USER_TAB_COLS.NULLABLE
as 是否为空,
USER_TAB_COLS.COLUMN_ID as 列序号,user_col_comments.comments
as 备注
FROM all_TAB_COLS USER_TAB_COLS
inner join vM user_col_comments
on user_col_comments.TABLE_NAME
=USER_TAB_COLS.TABLE_NAME
and user_col_comments.COLUMN_NAME
=USER_TAB_COLS.COLUMN_NAME
where USER_TAB_COLS.OWNER
=‘BYYY‘
)
select *
from vA
order by 表名,列名
Oracle库基本操作
标签：table drop bsp com rom system 调用 ase mod
