oracle SQL 创建表在列中使用 %type
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14646561/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL create table use %type at column
提问by Vloxxity
I'm trying to create a backuptable of one of my tables (in a function),
我正在尝试为我的一个表创建一个备份表（在一个函数中），
CREATE TABLE TBTestBackup
(
colum1 user.TBTest.colum1%type,
colum2 user.TBTest.colum2%type,
colum3 user.TBTest.colum3%type
colum31 user.TBTest.colum3%type, --new column with same type as colum3
colum4 user.TBTest2.column15type, --column from other table
colum4 CHAR (12 BYTE), --new column with fixed type
) TABLESPACE user_DATA
But I red this won't work, now my question is how could I make this as much dynamic as possible so I don't have to update the datatypes at the backup script every time I change a datatype e.g. from:
但是我认为这是行不通的，现在我的问题是如何使它尽可能地动态化，这样我就不必在每次更改数据类型时更新备份脚本中的数据类型，例如：
VARCHAR2(24 CHAR)to VARCHAR2(50 CHAR)
VARCHAR2(24 CHAR)到 VARCHAR2(50 CHAR)
(the table-columns are fixed they wont change) this doesn't happen often but we had to do it some times because the field wasn't great enough for a specific value and then nobody updated the backup-table and id gave some errors.
（表列是固定的，它们不会改变）这并不经常发生，但我们不得不这样做几次，因为该字段对于特定值不够大，然后没有人更新备份表，id 给出了一些错误.
EDIT: I forgot something necessary:
编辑：我忘记了一些必要的东西：
- I have to add 2 columns that aren't in the original table, but should have the same datatype as one of the already existing tables. could I use select as so it will have the same type but another name? if yes how do I do that?
- and some fields that are from a different table (so I have to use joins)
- 我必须添加 2 列不在原始表中，但应与现有表之一具有相同的数据类型。我可以使用 select as 以便它具有相同的类型但另一个名称吗？如果是，我该怎么做？
- 和一些来自不同表的字段（所以我必须使用连接）
SUM:
和：
- Multiple columns with type from multiple tables
- New Column with fixed Type
- New Column with variable type like colum XY from table ABC
- 来自多个表的具有类型的多个列
- 固定类型的新列
- 具有变量类型的新列，如表 ABC 中的 XY 列
采纳答案by Alex Poole
Based on your updated requirements, to create a table based on the types in these two tables:
根据您更新的要求，根据这两个表中的类型创建一个表：
create table t1 (col1 number, col2 varchar2(2), col3 date);
create table t2 (col1 varchar2(10 char));
You can just join them together, with a filter that always evaluates to false as Orangecrush suggested:
您可以将它们连接在一起，并使用一个始终评估为 false 的过滤器，正如 Orangecrush 建议的那样：
create table tb tablespace users as
select t1.col1 as col1, t1.col2 as col2, t1.col3 as col3,
t1.col3 as col4, t2.col1 as col5, cast(null as varchar2(12 byte)) as col6
from t1
cross join t2
where null is not null;
Normally a cross joinwould be unwelcome, but the optimizer is clever enough to realise that the filter means it doesn't need to actually hit the tables at all. You can use a normal inner join if there are fields you can join on though, of course.
通常 across join是不受欢迎的，但优化器足够聪明，可以意识到过滤器意味着它根本不需要实际命中表。当然，如果有可以加入的字段，则可以使用普通的内部联接。
desc tb
Name Null Type
---- ---- -----------------
COL1 NUMBER
COL2 VARCHAR2(2)
COL3 DATE
COL4 DATE -- new column with same type as t1.col3
COL5 VARCHAR2(10 CHAR) -- column from other table
COL6 VARCHAR2(12) -- new column with fixed type
回答by mavroprovato
You could create the table with:
您可以使用以下方法创建表：
CREATE TABLE TBTestBackup AS
SELECT colum1, column2, column2
FROM user.TBTest
Based on your edit: You can change the select statement to anything you like. Join the two tables and select 3 columns from one table and 2 from the other one
根据您的编辑：您可以将选择语句更改为您喜欢的任何内容。连接两个表并从一个表中选择 3 列，从另一个表中选择 2 列
回答by Orangecrush
You can use the statement to create the backup.
您可以使用该语句来创建备份。
CREATE TABLE TBTestBackup AS SELECT * FROM ORIGINAL_TABLE_NAME WHERE 1=2;
This is assuming that you do not want the data in the backup table. If you do want the data as well, just remove the WHEREcondition from the above statement.
这是假设您不希望备份表中的数据。如果您也需要数据，只需WHERE从上述语句中删除条件即可。
回答by Lupuss
The syntax is
语法是
CREATE TABLE TBTestBackup as (SELECT * FROM TBTest)
Not sure how you could automate changes to the datatypes, but anyway it's not something that should change often (if it does you need to work on that).
不确定如何自动更改数据类型，但无论如何它不应该经常更改（如果您需要处理它）。
回答by tbone
Sounds like a materialized viewwould work for what you want.
听起来像物化视图可以满足您的需求。
create materialized view my_backup
tablespace whatever
nologging
build immediate
refresh complete on demand
as
select t.*
from some_table t;
When you want to refresh your backup, do a full refresh on the mat view:
当您要刷新备份时，请在 mat 视图上进行完全刷新：
exec dbms_mview.refresh('MY_BACKUP', 'C', atomic_refresh=>false);
Also note that this is no substitute for RMANor other tools used by DBAs to do backups, but fits what you're asking to do.
另请注意，这不能替代RMAN或 DBA 用来进行备份的其他工具，但符合您的要求。
Also note that you can easily add extra columns to the query as needed.
另请注意，您可以根据需要轻松地向查询添加额外的列。
