ITPub博客

首页 > 数据库 > Oracle > oracle---synonym, sequence,view

oracle---synonym, sequence,view

原创 Oracle 作者:mrnavy 时间:2007-09-30 12:30:05 0 删除 编辑
introduce the synonym, sequence and view in oracle[@more@]

同义词 synonym

create 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";服务名称,即在本地的net manager中配置的连接远程数据库的服务名) connect
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 --指定序列之间的间隔,正负整数;默认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 view as

create view mytable
as
select first_name||’,’||last_name
from hr.employees;

:如何使用视图作为安全机制
1. desc考察hr.employees,看作一个公司的员工信息数据库表,简单说明
2. 目标:实现每个员工都可以访问公司中所有雇员的name, email, phone_number,方便通讯
3. 方案:
a) 赋予所有员工访问hr.employees表的权限?salary
b) 建立一个只包含合适字段的视图,然后赋予所有员工访问这个视图的权限,而不是表的权限。
4. 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

验证视图有效性:
基本表的一些改变可能会导致视图无效:
1) 改变出现在视图中列的名称,或删掉列
2) 删除构建视图的基本表或视图

[试验]使视图无效,并重新编译并使其有效:
1) 基本表:create table base(id number,data varchar2(200));
insert into base values(1,’abc’); commit;
2) view: create view view_b as
select id view_id, data view_data from t;

select * from view_b;
3) 更新基本表: alter table base modify(id number,data varchar2(255));
alter table base add(data2 varchar2(100));

4) 视图无效:select object_name, status from dba_objects where object_name=upper(‘view_b’)
5) 使视图有效:只需要从视图中选取即可, oracle会自动对视图编译
select * from view_b;
6) 手动编译: alter view view_b compile;

force 选项:
强制oracle接受无效的视图定义:
1) 比如开发过程中a负责建立基本表,b负责建立视图。这样b不必依赖于a的工作进度就可以将视图建立并编译进数据库。
2) 或者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 约束:
作用:阻止更新不能通过视图访问的数据。

试验:
1) 建立视图,只能看到department_id=10的雇员
create view department_10 as
select * from hr.employees where department_id=10
with check option
2) 选择:select employee_id,first_name,last_name from department_10;
3) 查看可更新列:
select * from dba_updatable_columns
where table_name=upper(‘department_10’)
4) 尝试将此人移动到部门20
update department_10
set department_id=20
where employee_id=200
报错!!
这个视图限制我们只能访问department=10的数据,我们要通过视图修改department=20的数据,被禁止。

[试验]关于前5名
1) 谁是公司前5名的雇员
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;
结果不正确,先取了前5条数据,再排序
3)select last_name,hire_date
from (select last_name,hire_date
from hr.employees
order by hire_date)
where rownum<6

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/119395/viewspace-974121/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Java基础之关键字
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2007-12-14

  • 博文量
    7
  • 访问量
    32852