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 数据库中更改上述查询?我需要获取列名称作为表用户"的结果集，不包括某些列，指定模式.现在我的新表空间中有所有表，那么我是否指定表空间名称代替模式名称?

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屋！