问题描述:

This question already has an answer here:

  • creating parameterized views in oracle11g

    3 answers

网友答案:

A view is a canned query that cannot be changed on the fly and does not accept parameters. You can "SELECT FROM myView WHERE..." to filter results, but that doesn't dynamically re-configure what is defined inside a column. So if the view can't be dynamic - it has to reference something else that can. Like, say, the contents of a table.... or package.

For example, have a package that holds the parameter with a public functions to set it and get it. Your view then includes a call to the package getter function to retrieve this value and use it in the query. Because packages instantiate once per user session each user can have a different value in their instance of the package, and so also into their own instance of the view.

Altenately, you could have a global temporary table referenced by the view that you pop the parameter into so the view can see it at runtime. Again, this allows each user to set their value within their session.

Of course, it means you have to go through certain steps for the view to work, and you have to have the view properly handle the cases where the user didn't set a value at all - or in the global temporary table accidentally put in TWO rows.

But it CAN be done.

相关阅读:
Top