createing view

1Why Use Views
  to restrict data access
  to make complex query easy
  to provide data independence
  to provide defferent view of the same data
2 Creating a View
  1)create [or replace] [force|noforce] view view
  as subquery
  force : create view wether the referenced object existed or not
 
  desc view_name;
 2)create a view by using column aliases in the subquery
  create view salv50
  as select employee_idIO_NUMBER,last_name NAME,slaary*12 ANN_SALARY
  from employees
  where department_id=50;
3 Modigy a View
  1) Modigy the emplvu80 view by using create or replace view clause.Add an alias for each

column name;
   create or replace view empvu80
   (id_number,name,sal,department_id)
   as select employee_id,first_name||" "||last_name,salary.department_id
   from employees
   where department_id=80;
   column aliases in the create view clause are listed in the same order as the columns in

the subquery
   note : alter view_name is not a valid command.
4 Create a Complex View
  Create a complex view that contains group functions to display values from two tables
  create view dept_sum_vu
   (name,minsal,maxsal,avgsal)
  as
   select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
   from employees e,departments d
   where e.department_id=d.department_id
   group by d.department_name;
5 Rules for performs DML operaton on a view
  1) You can perform DML operation on simple views
  2) You can not romove a row if the view contains the following:
    --group functions
    --a group by clause
    --the distince keyword
    -- rownum keyword
    -- column defined by expressions
6 Using the with check option Clause
  1) you can ensure that dml operatons performed on the view stay within the domain of the

view by using the with check option clause.
  creaate view test1
  as
  select * from emp where qty>10;
  with check option;
  update testview1 set qty=10
  where ster_id=6830;
  --when you doing the following update operation
  update testview1 set qty=5 where id=10;
  -- an error will report
  --you violate the where clause
  2)Any attempt to change the department number for any row in the view fails because it

violates the with check option constraint
   create or replace view empvu20
   as
   select * where department_id=20
   with check option constriant empvu20_ck;
7 Denying DML Operations
  1 You can ensure that no dml operations occur by adding the with read only option to your

view definition.
  2)Any attempt to a DML on any row in the view resuls in an oralce server error.
8 remove veiw
  drop view_name
9 inline view
  1) an inline view is a subquery with an alias that you can use within a sql statement.
  2) a named subquery in the from clause of the main query is an exqmple of an inline view
  3) an inline view is not a schema object.
10 Top-N Analysis
 1)Top_N querise ask for the n largest or smallest values of a column.
 2)Both largest values and smallest values sets considered Top-N queries
  select * from (select ster_id,qty from sales);
 example
  To display the top three earner names and salaries from the employees
  select rownum as rank,last_name,salary
  from (select last_anme,slary from employee
        order by slary desc)
  where rownum<=3;
 

posted on 2006-09-27 18:30 康文 阅读(264) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜