---------- sm_emp_sale_data.txt ----------
/*
 * 范例名稱：數(shù)據(jù)準備
 * 文件名稱：sm_emp_sale_data.txt
 */

--insert data need in chap12 sm_saleorderlist,sm_emp

DROP TABLE sm_saleorderlist CASCADE CONSTRAINT;
CREATE TABLE sm_saleorderlist(
    TransactionID NUMBER(10) ,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));

--data sm_saleorderlist
INSERT INTO sm_saleorderlist VALUES(1,100.10,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(2,222.20,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(3,300.10,'0000000002','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(4,100.10,'0000000003','09_9月_02');
COMMIT;

--if sm_emp exists,
DROP TABLE sm_emp;

CREATE table sm_emp
(EmpID CHAR(10) PRIMARY KEY,
Name   VARCHAR2(10),
salary NUMBER(8,2),
TelNo  CHAR(8));

INSERT INTO sm_emp VALUES('0000000001','張飛',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000002','關羽',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000003','劉備',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000007','007',100000,'62634546');
COMMIT;
--data sm_emp



---------- sm_sequence.txt ----------
/*
 * 范例名稱：序列的定義
 * 文件名稱：sm_sequence.txt
 */

--sm_emp_sale_data.txt must run first.

CREATE   SEQUENCE  sm_sequence;

SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;

--
INSERT INTO sm_saleorderlist VALUES
(sm_sequence.nextval,100.10,'0000000003','09_9月_02');

INSERT INTO sm_saleorderlist VALUES
(sm_sequence.nextval,600.10,'0000000006','09_9月_02');

SELECT * FROM sm_saleorderlist;

SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
--9
SELECT sm_sequence.nextval FROM DUAL;

--更改序列: 改變SEQUENCE的最大值----------------------
ALTER SEQUENCE  sm_sequence
MAXVALUE 9;
--RA-04009: MAXVALUE 不能小于當前值
ALTER SEQUENCE  sm_sequence
MAXVALUE 11;
--ok可以再次使用sm_sequence.nextval
ALTER SEQUENCE  sm_sequence
MAXVALUE 3;
--err! MAXVALUE 不能小于當前值

ALTER SEQUENCE  sm_sequence increment by -1;


ALTER SEQUENCE  sm_sequence
MAXVALUE 12;
SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
--超過MANVALUE
SELECT sm_sequence.nextval FROM DUAL;
ORA-08004: 序列SM_SEQUENCE.NEXTVAL exceeds MAXVALUE 無法實例化
  
 
---------- sm_sequence_2session.txt ----------
/*
 * 范例名稱：序列在不同sesion中的使用
 * 文件名稱：sm_sequence_2session.txt
 */

 
--以scott/tiger登錄一個sqlplus（1）
connect;
scott/tiger;

DROP SEQUENCE sm_sequence ;
CREATE   SEQUENCE  sm_sequence;

SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.nextval FROM DUAL;
SELECT sm_sequence.currval FROM DUAL;
--再輸入SELECT sm_sequence.currval FROM DUAL;結(jié)果會是什么呢？
--再輸入SELECT sm_sequence.nextval FROM DUAL;結(jié)果會是什么呢？


--以scott/tiger登錄另一個sqlplus（2）
SELECT sm_sequence.currval FROM DUAL;
--ERROR!說明了什么？
SELECT sm_sequence.nextval FROM DUAL;

SELECT sm_sequence.currval FROM DUAL;

--返回sqlplus（1）
SELECT sm_sequence.currval FROM DUAL;
--沒變。
SELECT sm_sequence.nextval FROM DUAL;
--多跳了一個！

--返回sqlplus（2）
SELECT sm_sequence.currval FROM DUAL;
--沒變。

--結(jié)論：  
INSERT INTO sm_saleorderlist VALUES
(sm_sequence.nextval,100.10,'0000000003','09_9月_02');

--用固定數(shù)插入
INSERT INTO sm_saleorderlist VALUES
(101,100.10,'0000000003','09_9月_02');

INSERT INTO sm_saleorderlist VALUES
(sm_sequence.nextval,600.10,'0000000006','09_9月_02');



---------- sm_view.txt ----------
/*
 * 范例名稱：同義詞
 * 文件名稱：sm_view.txt
 */

--sm_saleorderview:用于保存售貨信息.sm_saleorderlist + 對應銷售人員的信息
--參見sm_emp_saleorder圖.ppt


CREATE OR REPLACE VIEW sm_saleorderview AS 
SELECT sm_saleorderlist.TransactionID,sm_saleorderlist.TotalPrice ,sm_saleorderlist.EmployID ,
sm_emp.Name,sm_emp.TelNo,
sm_saleorderlist.SaleTime
FROM sm_saleorderlist,sm_emp
WHERE sm_saleorderlist.EmployID=sm_emp.empid;

select * from sm_saleorderview;
DESC  sm_saleorderview;

--定義synonym，準備在建立view時使用

CREATE SYNONYM sm_order FOR sm_saleorderlist;
SELECT * FORM sm_order;

--可以很簡單的命名
CREATE SYNONYM s FOR sm_saleorderview;
SELECT * FORM s;

--with synonym，建立view時使用同義詞，簡化sql語句
CREATE OR REPLACE VIEW sm_saleorderview AS 
SELECT sm_order.TransactionID,sm_order.TotalPrice ,sm_order.EmployID ,
sm_emp.Name,sm_emp.TelNo,
sm_order.SaleTime
FROM sm_order,sm_emp
WHERE sm_order.EmployID=sm_emp.empid;



---------- public_synonym.txt ----------
/*
 * 范例名稱：公用同義詞
 * 文件名稱：public_synonym.txt
 */

--synonym
SCOTT/TIGER
CREATE PUBLIC SYNONYM sm_orderpub FOR sm_saleorderlist;
--ERROR 位于第 1 行:
--ORA-01031: 權(quán)限不足

--以system登錄，為scott用戶GRANT 建立公有同義詞權(quán)限：public synonym

connect SYSTEM/MANAGER
GRANT CREATE PUBLIC SYNONYM TO SCOTT;

--
SCOTT/TIGER
CREATE PUBLIC SYNONYM sm_orderpub FOR sm_saleorderlist;
-- drop synonym sm_orderpub;
--err!drop public synonym是單獨的語句。不可用drop synonym刪除public synonym
drop public synonym sm_orderpub;
--ERROR 位于第 1 行:
--ORA-01031: 權(quán)限不足
--注意：


--以system登錄，
connect SYSTEM/MANAGER
SELECT * FROM sm_orderpub;

DROP PUBLIC SYNONYM sm_orderpub;
--ok.system 可以刪除 public SYNONYM。但不能刪除 普通scott的 SYNONYM.
grant drop public synonym to scott;

--授權(quán)后，scott才可以drop public synonym
練習：
用system/manager建立一個表（如sm_emp）
為其指定public同義詞
使用system/manager訪問此同義詞
嘗試用scott/tiger登錄sqlplus訪問此同義詞,看看結(jié)果如何



---------- sm_dblink.txt ----------
/*
 * 范例名稱：數(shù)據(jù)庫鏈
 * 文件名稱：sm_dblink.txt
 */

--LINK ANOTHER DATABASE.要在另一臺pc system上建立 sm_sep.

--必須首先以 system/manger使用如下語句
show parameter global;
--每次win重起動，將global_names=true,
--所以必須alter system set global_names=false;

--系統(tǒng)的global_names參數(shù)必須為false
alter system set global_names=false;

--連接本地oracle.建立service  :ora8
--啟動監(jiān)聽器 lsnrctl start,測試服務名ora8
--建立指向本機的數(shù)據(jù)庫鏈，并使用
drop database link sm_link;

CREATE DATABASE LINK sm_link
CONNECT TO scott  IDENTIFIED BY tiger
USING 'ORA8';
select * from emp@sm_link;

--測試sm_link,select 
SELECT * FROM sm_emp@sm_link;

--測試sm_link,insert
INSERT INTO sm_emp@sm_link 
VALUES('0000009999','099',90000,'62634546');
COMMIT;
SELECT * FROM sm_emp@sm_link;


---------- remote_dblink.txt ----------
/*
 * 范例名稱：數(shù)據(jù)庫鏈
 * 文件名稱：remote_dblink.txt
 */
--連接遠oracle.建立service  :test80
--啟動lsnrctl,測試test80
--驗證遠端oracle 的global_names
show parameter global;
--每次win重起動，將global_names=true,所以必須alter system set global_names=false;
alter system set global_names=false;

drop database link sm_80;
CREATE DATABASE LINK sm_80
CONNECT TO scott  IDENTIFIED BY  tiger
USING 'test80';
SELECT * FROM emp@sm_80;
--注意：USING 'test80'，因為建立service  :test80




SELECT * FROM emp@sm_80;
SELECT * FROM emp;

--在同伴pc建立一個table,用link 插入記錄
SELECT * FROM emp@sm_80;
SELECT * FROM sm_emp@sm_80;
INSERT INTO sm_emp@sm_80 
VALUES('0000009999','099',90000,'62634546');
INSERT INTO sm_emp@sm_80 
VALUES('9999');

--INSERT INTO sm_emp VALUES('0000009999','099',90000,'62634546');
COMMIT;
--這一個commit向兩個db commit!等會在rollback我們將看一下分布式事務管理的效果。
SELECT * FROM sm_emp@sm_80;


-----------------使用同義詞驗證  遠程dml同于普通表-------------------------------
create synonym remote_emp for sm_emp@sm_80;


select * from remote_emp;

--驗證同義詞
select * from remote_emp;

--通過同義詞對remote表訪問



--dml同于普通表
insert into remote_emp 
(empid,name,salary,telno) values(999,'張飛',100,'999');



select * from remote_emp;

update remote_emp set name='張飛2',salary=111.1
where empid='999';

commit;
--遠程結(jié)果
select * from remote_emp;

--分布式commit,rollback
--------------------------------分析commit,rollback對本地dml，遠程dml相同嗎？-----------------------

delete from remote_emp where empid='0000009999';

--此時插入一個本地dml,分析commit,rollback對本地dml，遠程dml相同嗎？
insert into sm_emp 
(empid,name,salary,telno) values('999','張飛99',100,'999');

--遠程結(jié)果
select * from remote_emp;

--本地結(jié)果
select * from sm_emp;

rollback;

--遠程結(jié)果
select * from remote_emp;

--本地結(jié)果
select * from sm_emp;

--結(jié)論：commit,rollback對本地dml，遠程dml相同

/*
結(jié)論的理論含義：
各Oracle數(shù)據(jù)庫有各自本地服務器控制，
但他們能共同維護整個分布式數(shù)據(jù)庫的數(shù)據(jù)。
*/