我可以创建一个自动检查新月表的 Oracle 视图吗？
我想知道是否可以创建一个视图来自动检查是否有每月创建的新表以及是否包含该表？
我们每个月都会创建一个新表，每个表都以月份数字结尾，例如
table for January: table_1
table for February: table_2
etc...
是否可以创建一个视图，从所有这些表中获取数据，并查找何时创建新表？
I'm wondering if its possible to create a view that automatically checks if there is a new monthly created table and if there is include that one?
We have a new table created each month and each one ends with the number of the month, like
table for January: table_1
table for February: table_2
etc...
Is it possible to create a view that takes data from all those tables and also finds when there is a new one created?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不，视图的定义是静态的。您必须每月用包含新表的新副本替换视图；您可以编写一个动态 PL/SQL 程序来执行此操作。或者您现在可以创建所有空表并将它们全部包含在视图定义中；如果有必要，您可以推迟授予对未来表的任何 INSERT 访问权限，直到它们变为“活动”状态。
但实际上，这个模型是有缺陷的 - 请参阅 Michael Pakhantsov 的答案以获得更好的替代方案 - 或者只有一个带有 MONTH 列的简单表。
No, a view's definition is static. You would have to replace the view each month with a new copy that included the new table; you could write a dynamic PL/SQL program to do this. Or you could create all the empty tables now and include them all in the view definition; if necessary you could postpone granting any INSERT access to the future tables until they become "live".
But really, this model is flawed - see Michael Pakhantsov's answer for a better alternative - or just have one simple table with a MONTH column.
如果您不是每月创建新表而是为现有表创建新分区，则这是可能的。
更新：
如果您的 Oracle SE 没有分区选项，您可以创建两个表：LiveTable 和 ArchiveTable。然后每个月您都需要将行从 Live 移动到 ArchiveTable 并清理 live 表。在这种情况下，您需要仅从两个表创建视图。
Will be possible if you instead of creating new table each month will create new partition for existing table.
UPDATE:
If you have oracle SE without partitioning option you can create two tables: LiveTable and ArchiveTable. Then each month you need move rows from Live to ArchiveTable and clean live table. In this case you need create view just from two tables.
另一种选择是在另一个架构中创建表，并授予相关用户权限并为其创建公共同义词。
当在本地模式中创建每月表时，它们将“优先于”公共同义词，并且视图将选择它们。它仍然会失效并需要重新编译，但实际的视图文本应该需要更改，从代码控制的角度来看这可能更简单。
Another option is to create the tables in another schema with grants to the relevant user and create public synonyms to them.
As the monthly tables get created in the local schema, they'll "out-precedence" the public synonyms and the view will pick them up. It will still get invalidated and need recompiling, but the actual view text should need changing, which may be simpler from a code-control point of view.
您可以编写一个过程或函数来查看
USER_TABLES或ALL_TABLES以确定表是否存在，生成动态sql，并返回包含数据的引用游标。使用流水线函数也可以完成同样的操作。You can write a procedure or function that looks at
USER_TABLESorALL_TABLESto determine if a table exists, generate dynamic sql, and return a ref cursor with the data. The same can be done with a pipelined function.