Oracle_071_lesson_p14

就是给查询语句取一个名称（别名）
类型：
1、简单视图 simple view
2、复杂视图 complex view

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[,alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

简单视图 simple view
示例：
create view empv80
as
select salary
from employees
where department=80;

desc empv80;
select * from empv80;

CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER,last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

复杂视图 complex view

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;

CREATE OR REPLACE 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 JOIN departments d
USING (department_id)
GROUP BY d.department_name;
此select 语句复杂些，可包含函数等

DESCRIBE user_views;

SELECT view_name FROM user_views;

SELECT text FROM user_views
WHERE view_name = ‘EMP_DETAILS_VIEW‘;

set long 9999;

select 子句有表达式或者数字时，要加别名。不能删除视图，比如有：1、group by2、distinct3、rownum4、分组函数You can usually perform DML operations on simple views.You cannot remove a row if the view contains the following:1、Group functions2、A GROUP BY clause3、The DISTINCT keyword4、The pseudocolumn ROWNUM keyword

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!