创建视图的存储过程
好的，所以我知道这个问题已经被问过。我读到的所有内容都是“好吧..你可以使用动态 DSL 来做到这一点，但不要这样做”我的问题是为什么。我对此还是新手，所以我正在学习，所以请耐心等待，但这就是我正在做的事情。 我想使用存储过程创建动态视图（但不是临时表），该视图有两个日期，用于建立开始日期和结束日期。它看起来像这样：
create or replace view MyView as
SELECT
A.COLUMN_A
FUNCTION1(to_date('2/10/2011','MM/DD/YYYY') TOTAL1,
FUNCTION2(to_date('2/15/2011','MM/DD/YYYY') TOTAL2
FROM TABLE_A A;
然后，该视图用于生成 Crystal 报告所需的数据。 问题是我们即将开始以另一种语言使用这些相同的 sql 语句。 （我们目前正在使用delphi，但要使用另一种语言（但我不知道另一种语言是什么））我想在存储过程中创建视图的原因是，因为a）视图是动态的，并且基于b) 用户选择的日期范围，而不必放入一些相当大的多种语言视图（由于日期范围选择的变化，必须动态创建），只需单行函数和参数需要通过。我读到的很多内容都说使用动态 SQL 创建视图是不好的，但是知道它已经是专门为用户动态创建的动态视图，有人认为这有问题吗？我之所以这么问，是因为我不想让自己陷入困境，如果不拔掉自己的头发，我就无法摆脱困境。
Ok, So i know this questions has been asked. and everything i read has been "well.. you can do it using dynamic DSL, but dont do it" my question is why. I'm still new at this so I'm learning so bear with me but here is what i'm doing.
I want to use a stored procedure to create a dynamic view (but not a temporary table) the view has two dates that it uses to establish a beginning and ending date. it looks something like this:
create or replace view MyView as
SELECT
A.COLUMN_A
FUNCTION1(to_date('2/10/2011','MM/DD/YYYY') TOTAL1,
FUNCTION2(to_date('2/15/2011','MM/DD/YYYY') TOTAL2
FROM TABLE_A A;
This view is then used to generate the data needed for a report in crystal.
The problem is that we are about to start using these same sql statements in another language. (we are currently using delphi but to about working on another language (but i dont know what the other language is)) the reason I want to create the view in a stored procedure is, because a) the view is dynamic, and based off the date range selected by the user and b) instead of having to put in some rather large views in multiple languages (that have to be created on the fly due to the changing date range selection) onlt the single line for the function and the parameters would need to be passed. Alot of what i read has said that using Dynamic SQL to create a view is bad, but knowing that its already a dynamic view created specifically for the user on the fly, does anyone see an issue with that? I'm asking because I dont want to get myself into something down the road that I wouldnt be able to get myself out of with out wanting to yank all my hair out.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我做 Delphi 前端和 SQL Server 后端。为什么要使用视图？我总是只用一个简单的 SELECT 创建一个 SP，它可以很好地完成工作。如果经常使用 Crystal Report（通常是这种情况），我只需创建一个永久表，每次运行 SP 时都会清除并重新填充该表。简短、甜蜜、简单。
I do Delphi front-ends and SQL Server backends. Why use a view ? I always just create a SP with a simple SELECT which does the job very nicely. If the Crystal Report is used frequently which is usually the case) I simply create a permanent table which is cleared and repopulated each time the SP is run. Short, sweet, and simple.
只有当它确实是代码生成并且通常相当持久时，生成视图才是一个好主意。因为这是一个架构更改，所以它应该被视为一种高级技术，并且我总是尝试按复杂性的顺序使用数据库技术。
即对于某些表/视图设计，如果可以用视图来完成，请尝试一下。如果确实需要索引视图来提高性能，请尝试这样做。或者，可能是计算列或持久计算列。也许是内联表值函数，或多语句表值函数，或者可能是存储过程。所以我只在必要时尝试升级。
就您而言，该“视图”也可以是 SQL Server（至少是 DB2）中的内联表值函数，这样的生物就像参数化视图一样。您还可以直接从大多数报告工具（当然还有大多数语言/数据库库）使用参数化存储过程。
Generating a view is only a good idea if it's really code-generation and generally fairly permanent. Because it's a schema change, it should be viewed as an advanced technique, and I always try to use database techniques in order of complexity.
i.e. For certain table/view designs, if it can be done with a view, try that. If an indexed view is really needed for performance, try that. Alternatively, maybe a computed column or a persisted computed column. Perhaps an inline table-valued function, or a multi-statement table-valued function, or potentially a stored procedure. So I try to escalate only when necessary.
In your case, that "view" can also be an inline table-valued function in SQL Server (and DB2 at least) and such a creature is just like a parametrized view. You can also use a parametrized stored procedure directly from most reporting tools (and of course from most language/db libraries).
动态创建视图（或任何数据库对象）就像在运行时操作代码一样。在好的方面和坏的方面都很强大。
您可能遇到的风险和问题：
99.9% 的人不需要动态创建对象，如果不是 0.1%，则不应使用它们。
鉴于您对手头任务的描述：为什么不直接使用带有变量部分的 sql 语句作为绑定变量并使用它呢？我认为这里没有必要观看。
Creating a view (or any database object) dynamically is like manipulating your code at runtime. Powerfull in good and bad ways.
The riscs and problems you might run into:
In 99.9% one doesn't need dynamic creation of objects and if you aren't in the 0.1 percent you shouldn't use them.
Given your descripton of the task at hand: Why don't you just use the sql statement with the variable parts as bind variables and use that? I don't see the need for a view here.