Oracle synonyms sequence view

摘抄：oracle---synonym, sequence,view

 

/*==============================================================*/
/* Synonym: PRODUCT_DEFINE                                      */
/*==============================================================*/
CREATE SYNONYM PRODUCT_DEFINE FOR PC_PROD_MAIN 

introduce the synonym, sequence and view in oracle

同義字 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 --指定序列之間的間隔，正負整數；預設１，正為升序，負為降序。
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 <view_name> as <query>

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