Oracle查询以获取列名 [英] Oracle query to fetch column names
问题描述
我有一个mySQL查询来从像这样的表中获取列:
I have a mySQL query to get columns from a table like this:
String sqlStr="select column_name
from information_schema.COLUMNS
where table_name='users'
and table_schema='"+_db+"'
and column_name not in ('password','version','id')"
如何在Oracle 11g数据库中更改以上查询?我需要获取列名作为表'users'的结果集,不包括某些列,并指定一个模式.现在我所有的表都在新表空间中,因此我可以指定表空间名称代替模式名称吗?
How do I change the above query in Oracle 11g database? I need to get columns names as a resultset for table 'users' excluding certain columns, specifying a schema. Right now I have all tables in my new tablespace, so do I specify tablespace name in place of schema name?
还为此提供通用的HQL吗?在我的新Oracle数据库(我是Oracle新手)中,我只有表空间名称,所以它等效于架构名称(在逻辑上?)
Also is there a generic HQL for this? In my new Oracle database (I am new to Oracle), I only have tablespace name, so is that equivalent to schema name (logically?)
推荐答案
对于当前用户拥有的表,information_schema.COLUMNS
的Oracle等效值为USER_TAB_COLS
,对于所有用户拥有的表,ALL_TAB_COLS
或DBA_TAB_COLS
等效.
The Oracle equivalent for information_schema.COLUMNS
is USER_TAB_COLS
for tables owned by the current user, ALL_TAB_COLS
or DBA_TAB_COLS
for tables owned by all users.
表空间不等同于架构,也不必提供表空间名称.
Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.
如果要查询ALL_TAB_COLS
或DBA_TAB_COLS
特定用户拥有的OF列的列,则提供架构/用户名将很有用.在您的情况下,我想查询将类似于:
Providing the schema/username would be of use if you want to query ALL_TAB_COLS
or DBA_TAB_COLS
for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:
String sqlStr= "
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'USERS'
AND owner = '" +_db+ "'
AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"
请注意,使用这种方法会冒着SQL注入的风险.
Note that with this approach, you risk SQL injection.
编辑:表名和列名大写,因为它们在Oracle中通常是大写的.如果使用双引号将它们引起来,则它们仅是小写或混合大小写.
Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.
这篇关于Oracle查询以获取列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!