create 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 distinct 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.
          ? create 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-10-11 14:45 康文 閱讀(306) 評論(0)  編輯  收藏 所屬分類: 數(shù)據(jù)庫

          <2006年10月>
          24252627282930
          1234567
          891011121314
          15161718192021
          22232425262728
          2930311234

          導航

          統(tǒng)計

          常用鏈接

          留言簿(1)

          隨筆分類

          隨筆檔案

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 潮安县| 甘泉县| 吉隆县| 遵义县| 舒城县| 山阳县| 内黄县| 唐海县| 衡东县| 蚌埠市| 大宁县| 土默特右旗| 岳普湖县| 尚志市| 灵璧县| 宿松县| 阜平县| 搜索| 台中市| 库伦旗| 武山县| 加查县| 许昌县| 调兵山市| 阿鲁科尔沁旗| 汤原县| 饶阳县| 扶风县| 广饶县| 甘德县| 密山市| 安仁县| 偃师市| 正定县| 泗洪县| 望奎县| 旬邑县| 鸡西市| 礼泉县| 八宿县| 格尔木市|