【IT168 技术文档】

　　同义词 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 --指定序列之间的间隔，正负整数;默认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;

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

　　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