oracle symonym_Oracle SYNONYM

同义词 synonym

CREATE [PUBLIC]SYNONYM synonym For schema.object

隐藏对象的名称和所有者:

select count(*) from hr.employees;

create synonym emp for hr.employees;    --默认属于donny用户，是donny的私有对象private

select count(*) from emp;

为分布式数据库的远程对象提供了位置透明性:

访问其他数据库时，要首先建立数据库连结：

CREATE DATABASE LINK test_link CONNECT TO username IDENTIFIED BY pass USING 'orabase';

Select count(*) from hr.employees@test_link;

create synonym link_emp for hr.employees@test_link;

select count(*) from link_emp;

提供对象的公共访问:

create public synonym pub_emp for hr.employees;

pub_emp属于public用户，数据库所有用户都可以访问。

同义词类型

–私有 emp    实际上donny.emp

–公用 pub_emp   所有用户都可以直接访问

当公有对象和私有对象同名时(因为数据不同的用户，所以可以)，以私有对象优先。(类似于局部变量)

desc   dba_synonyms/ user_synonyms/ all_synonyms 数据字典，复数

tab公有同义词

建立私有的tab表，查看效果。

删除同义词：

drop synonym donny.emp;

drop public synonym pub_emp;

序列sequence：

CREATE SEQUENCE donny.seq　　--也是属于某个用户的，以下参数均可省略，使用默认值。

INCREMENT BY 1 --指定序列之间的间隔，正负整数；默认１，正为升序，负为降序。

START WITH 1 --第一个序列号，默认=MINVALUE

NOMAXVALUE --设置最大值，此处表示默认10的27次幂。MAXVALUE 10

NOMINVALUE --设置最小值，此处表示默认-10的26次幂。MINVALUE 1

NOCYCLE   --或者CYCLE;表示序列达到最大或者最小(降序)后，要不要从头开始

CACHE 10;   --默认CACHE 20,　事先分配多少序列号放在内存中，提高速度。

访问序列：

oracle为序列提供了两个伪列，可以看作其属性。

Nextval: 根据increment by得到的一个新的序列值。每次执行都会得到一个新值。

Currval: current value,　当前值，已经被取得的值。

Select seq.nextval from dual;

Select seq.currval from dual;

使用序列：

insert into t values(seq.nextval);

修改序列：

alter sequence seq …..重新指定各个参数

不能修改start with；除非删除重建

删除序列：

drop sequence seq;

数据字典：

desc dba_sequences / user_…/ all….

视图view:

CREATE [OR REPLACE][FORCE/ NOFORCE] VIEW AS

Create view mytable

As

Select first_name||’,’||last_name

from hr.employees;

[试验]：如何使用视图作为安全机制

１． desc考察hr.employees，看作一个公司的员工信息数据库表，简单说明

２． 目标：实现每个员工都可以访问公司中所有雇员的name, email, phone_number，方便通讯

３． 方案：

a) 赋予所有员工访问hr.employees表的权限？salary

b) 建立一个只包含合适字段的视图，然后赋予所有员工访问这个视图的权限，而不是表的权限。

４． Alter user hr account unlock;

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’)

with read only

验证视图有效性：

基本表的一些改变可能会导致视图无效：

１) 改变出现在视图中列的名称，或删掉列

２) 删除构建视图的基本表或视图

［试验］使视图无效，并重新编译并使其有效：

１) 基本表：create table base(id number,data varchar2(200));

insert into base values(1,’abc’); commit;

２) view:     create view view_b as

select id view_id, data view_data from t;

select * from view_b;

３) 更新基本表： alter table base modify(id number,data varchar2(255));

alter table base add(data2 varchar2(100));

４) 视图无效：select object_name, status from dba_objects where object_name=upper(‘view_b’)

５) 使视图有效：只需要从视图中选取即可,　oracle会自动对视图编译

select * from view_b;

６) 手动编译：　alter view view_b compile;

FORCE 选项：

强制ORACLE接受无效的视图定义：

１) 比如开发过程中A负责建立基本表，B负责建立视图。这样B不必依赖于A的工作进度就可以将视图建立并编译进数据库。

２) 或者B需要建立在A用户表上视图，但是还暂时没有对A用户表select 的权限，可以先建立，等待授权后再使用。

Create view invalid_view as

Select * from table_not_exist;

Create force view invalid_view as

Select * from table_not_exist;

通过视图进行更新和删除：

类似于company_phone_book是可以跟新的。

可以通过dba_updatable_columns查看那些列可以做那些更新；

desc hr.company_phone_book

select * from dba_updatable_columns where table_name=upper(‘company_phone_book’)

尝试更新email和name

update hr.company_phone_book

set name=’Chen, Donny’

where emp_id=100

1. 使用instead of 触发器更新视图：

create trigger update_name_company_phone_book

INSTEAD OF

Update on hr.company_phone_book

Begin

Update hr.employees

Set employee_id=:new.emp_id,

First_name=substr(:new.name, instr(:new.name,’,’)+2),

last_name= substr(:new.name,1,instr(:new.name,’,’)-1),

phone_number=:new.phone_number,

email=:new.email

where employee_id=:old.emp_id;

end;

2. With check option 约束：

作用：阻止更新不能通过视图访问的数据。

试验：

１) 建立视图，只能看到department_id=10的雇员

create view department_10 as

select * from hr.employees where department_id=10

With check option

２) 选择：select employee_id,first_name,last_name from department_10;

３) 查看可更新列：

select * from dba_updatable_columns

where table_name=upper(‘department_10’)

4) 尝试将此人移动到部门２０

update department_10

set department_id=20

where employee_id=200

报错！！

这个视图限制我们只能访问department=10的数据，我们要通过视图修改department=20的数据，被禁止。

［试验］关于前５名

１) 谁是公司前５名的雇员

select last_name,hire_date

from hr.employees

order by hire_date;

2) 只想取回前五名数据呢？

select last_name,hire_date

from hr.employees

where rownum<6

order by hire_date;

结果不正确，先取了前５条数据，再排序

３)select last_name,hire_date

from (select last_name,hire_date

from hr.employees

order by hire_date)

where rownum<6