Oracle概念：同义词、序列与视图

核心提示： Conn hr/hrCreate view company_phone_book asSelect first_name||’, ’||last_name name, email, phone_numberFrom employees;Grant select on

Conn hr/hr

Create view company_phone_book as

Select first_name||’, ’||last_name name, email, phone_number

From employees;

Grant select on company_phone_book to public;

Desc company_phone_book 对比列的长度

Select * from company_phone_book;

name隐藏数据的复杂性

数据字典：

dba_views

text字段，long

select text from dba_views where view_name=upper(’company_phone_book’)

改变视图定义：

新需求：想要在现有视图上增加员工的ID号(employee_id)

Create view company_phone_book as

Select employee_id emp_id,

first_name||’,’||last_name name, email, phone_number

From employees;

报错;

如果删掉重建，会有什么缺点?会把关联的授权全部删掉。Create or replace view保留原有授权。

Create or replace view company_phone_book as

Select employee_id emp_id,

first_name||’,’||last_name name, email, phone_number

From employees;

Desc company_phone_book

Drop view company_phone_book

视图中增加约束：

create view yearly_hire_totals as

select to_char(hire_date,’YYYY’) year,

count(*) total

from hr.employees

group by to_char(hire_date,’YYYY’)

order by to_char(hire_date,’YYYY’);

联接视图：

desc hr.emp_details_view

set long 5000

select text from dba_views where view_name=upper(‘emp_details_view’)