script_72.txt

orale培训教材包括了所有的sql说明和实例

---------- sm_emp_sale_data.txt ----------
/*
 * 范例名称：数据准备
 * 文件名称：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;结果会是什么呢？
--再输入SELECT sm_sequence.nextval FROM DUAL;结果会是什么呢？


--以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;
--没变。

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

--用固定数插入
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: 权限不足

--以system登录，为scott用户GRANT 建立公有同义词权限：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: 权限不足
--注意：


--以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;

--授权后，scott才可以drop public synonym
练习：
用system/manager建立一个表（如sm_emp）
为其指定public同义词
使用system/manager访问此同义词
尝试用scott/tiger登录sqlplus访问此同义词,看看结果如何



---------- sm_dblink.txt ----------
/*
 * 范例名称：数据库链
 * 文件名称：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;

--系统的global_names参数必须为false
alter system set global_names=false;

--连接本地oracle.建立service  :ora8
--启动监听器 lsnrctl start,测试服务名ora8
--建立指向本机的数据库链，并使用
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 ----------
/*
 * 范例名称：数据库链
 * 文件名称：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;
--远程结果
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');

--远程结果
select * from remote_emp;

--本地结果
select * from sm_emp;

rollback;

--远程结果
select * from remote_emp;

--本地结果
select * from sm_emp;

--结论：commit,rollback对本地dml，远程dml相同

/*
结论的理论含义：
各Oracle数据库有各自本地服务器控制，
但他们能共同维护整个分布式数据库的数据。
*/