oracle管理之 view synonym sequence(server.102 b14231)

--view sequence synonyms
view也是可以update的，只要你有update的許可權，不過有很多約束
例如：view不能含有 set distinct group by，not null列沒有default值，decode等等都不能update delete
create table AAATTT
(
ID CHAR(5) not null,
NAME CHAR(10),
NOTE CHAR(30),
XINGMING VARCHAR2(10),
XINGM VARCHAR2(10),
NUM NUMBER(5,3),
NUM2 NUMBER(4),
NUM3 NUMBER(*,3),
DAT DATE,
TIMLZ TIMESTAMP(6) WITH LOCAL TIME ZONE,
TIMZ TIMESTAMP(6) WITH TIME ZONE
);

create view aaat as select * from aaattt;
--view定義存放在資料字典中如下：
select "ID","NAME","NOTE","XINGMING","XINGM","NUM","NUM2","NUM3","DAT","TIMLZ","TIMZ" from aaattt

insert into aaat(id,name) values ('999','hexiaoling');
commit;

--create or replace view比 drop view再create view更好，不丟失授權和依賴關係

--join view有多個base table，聯合檢視可更新的條件
1、只能更新主鍵保留表，如果一個表的primary key也是檢視的primary key，那麼這個表可以透過檢視來更新
2、可以透過instead of trigger來更新base table

--sequence
CREATE SEQUENCE emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
--cache 10,如果遇到instance失敗，cache內的資料丟失，sequence則跳過cache內的數值
--可以遞增也可以遞減，可以迴圈
NEXTVAL
CURRVAL
DROP SEQUENCE order_seq;

--Synonyms Managing
CREATE PUBLIC SYNONYM public_emp FOR jward.emp
DROP PUBLIC SYNONYM public_emp;
--相關檢視
DBA_VIEWS
ALL_VIEWS
USER_VIEWS
DBA_SYNONYMS
ALL_SYNONYMS
USER_SYNONYMS
DBA_SEQUENCES
ALL_SEQUENCES
USER_SEQUENCES
DBA_UPDATABLE_COLUMNS
ALL_UPDATABLE_COLUMNS
USER_UPDATABLE_COLUMNS[@more@]