关于SQL语句的一些怪异想法，请指教！！！

[sexfreebird]

我想询问一下在DataView视图中能不能用SQL语句实现对一部分视图进行排序，如果能，SQL语句怎么写？？？

[black_snail]

DATAVIEW is ?

please clear your meaning?

[shizhenghai]

是不是数据字典里的视图呀?

[luyuen]

晕

[beckhambobo]

view是否能排序

[skystar99047]

意思不是很清楚，
不过可以创建一个视图使其排序。
create view DateView as
select * from mytable order by column1;

[beckhambobo]

sorry,
view是不能出order by

[cyberflying]

连接到:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> select *
2 from test;

COL_ID COL_CHAR COL_VAR
---------- -------------------- --------------------
1 a a
2 a b

SQL> create view test_1
2 as
3 select *
4 from test
5 order by col_id desc;

视图已建立。

SQL> select *
2 from test_1;

COL_ID COL_CHAR COL_VAR
---------- -------------------- --------------------
2 a b
1 a a

SQL>


btw: dataview是什么?

[cyberflying]

SQL> create view test_2
2 as
3 select *
4 from test;

视图已建立。
SQL> select *
2 from test_2;

COL_ID COL_CHAR COL_VAR
---------- -------------------- --------------------
1 a a
2 a b

SQL> update test_2
2 set col_var = 'test'
3 where col_id = 1;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select *
2 from test_2;

COL_ID COL_CHAR COL_VAR
---------- -------------------- --------------------
1 a test
2 a b

SQL> select *
2 from test;

COL_ID COL_CHAR COL_VAR
---------- -------------------- --------------------
1 a test
2 a b

SQL> update test_1
2 set col_var = 'test'
3 where col_id = 2;
update test_1
*
ERROR 位于第 1 行:
ORA-01732: data manipulation operation not legal on this view


SQL>

[cyberflying]

Restrictions on the Defining Subquery of a View
The view subquery cannot select the CURRVAL or NEXTVAL pseudocolumns.
If the view subquery selects the ROWID, ROWNUM, or LEVEL pseudocolumns, those columns must have aliases in the view subquery.
If the view subquery uses an asterisk (*) to select all columns of a table, and you later add new columns to the table, the view will not contain those columns until you re-create the view by issuing a CREATE OR REPLACE VIEW statement.
For object views, the number of elements in the view subquery select list must be the same as the number of top-level attributes for the object type. The datatype of each of the selecting elements must be the same as the corresponding top-level attribute.
You cannot specify the SAMPLE clause.
The preceding restrictions apply to materialized views as well.

Notes on Creating Updatable Views
An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. (The information displayed by this view is meaningful only for inherently updatable views.)

If you want the view to be inherently updatable, it must not contain any of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
Joins (with some exceptions as described in the paragraphs that follow).
In addition, if an inherently updatable view contains pseudocolumns or expressions, you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
If you want a join view to be updatable, all of the following conditions must be true:
The DML statement must affect only one table underlying the join.
For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table in one for which every primary key or unique key value in the base table is also unique in the join view.
For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
For a DELETE statement, if the join results in more than one key-preserved table, then Oracle deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.