[Oracle]分享：我的oracle9i学习笔记（9）

文章作者 100test 发表时间 2007:03:14 13:54:35
来源 100Test.Com百考试题网

############## Create Views #####################

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[,alias]...)]

AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint_name]]

[WITH READ ONLY [CONSTRAINT constraint_name]]. ------创建视图的语法

example: Create or replace view testview as 0select col1,col2,col3 from table_name. ------创建视图

/*使用别名*/

Create or replace view testview as 0select col1,sum(col2) col2_alias from table_name.

/*创建复杂视图*/

Create view view_name (alias1,alias2,alias3,alias4) as 0select d.col1,min(e.col1),max(e.col1),avg(e.col1) from table_name1 e,table_name2 d where e.col2=d.col2 group by d.col1.

/*当用0update修改数据时，必须满足视图的col1>.10的条件，不满足则不能被改变.*/

Create or replace view view_name as 0select * from table_name where col1>.10 with check option.

/*改变视图的值.对于简单视图可以用0update语法修改表数据，但复杂视图则不一定能改。如使用了函数，group by ,distinct等的列*/

0update view_name set col1=value1.

/*TOP-N分析*/

0select [column_list],rownum from (0select [column_list] from table_name order by Top-N_column) where rownum<=N.

/*找出某列三条最大值的记录*/

example: 0select rownum as rank ,col1 ,col2 from (0select col1 ,col2 from table_name order by col2 desc) where rownum<=3.

############# Other database Object ###############

CREATE SEQUENCE sequence_name [INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCEL | NOCYCLE}]

[{CACHE n | NOCACHE}]. -----创建SEQUENCE

example:

CREATE SEQUENCE sequence_name INCREMENT BY 10

START WITH 120

MAXVALUE 9999

NOCACHE

NOCYCLE.

0select * from user_sequences .---当前用户下记录sequence的视图

0select sequence_name.nextval,sequence_name.currval from dual.-----sequence的引用

alter sequence sequence_name INCREMENT BY 20