script_71_old.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) NOT NULL UNIQUE,
    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
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;
--没变。

--结论：  




---------- sm_view.txt ----------
/*
 * 范例名称：同义词
 * 文件名称：sm_view.txt
 */

--sm_saleorderview:用于保存售货信息.sm_saleorderlist + 对应销售人员的信息


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
CREATE SYNONYM sm_order FOR sm_saleorderlist;
SELECT * FORM sm_order;

--with synonym
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: 权限不足

--GRANT 建立公有同义词：public synonym
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/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



---------- sm_dblink.txt ----------
/*
 * 范例名称：数据库链
 * 文件名称：sm_dblink.txt
 */

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

--必须首先以 system/manger使用如下语句
show parameter global;
alter system set global_names=false;

--连接本地oracle.建立service  :ora8
--启动lsnrctl,测试ora8

drop database link sm_link;

CREATE DATABASE LINK sm_link
CONNECT TO scott  IDENTIFIED BY tiger
USING 'ORA8';

CREATE DATABASE LINK sm_link
CONNECT TO scott  IDENTIFIED BY tiger
USING 'ORA8';

--测试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;

--连接远oracle.建立service  :test80
--启动lsnrctl,测试test80

drop database link sm_80;
CREATE DATABASE LINK sm_80
CONNECT TO scott  IDENTIFIED BY  tiger
USING 'test80';
SELECT * FROM emp@sm_80;


--CREATE DATABASE LINK ora8
--CONNECT TO scott  IDENTIFIED BY tiger
--USING 'ORA8';

-----
drop database link test80;

CREATE DATABASE LINK test80
CONNECT TO scott  IDENTIFIED BY tiger
USING 'test80';
SELECT * FROM emp@test80;


SELECT * FROM emp@sm_80;

--在同伴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');
COMMIT;
SELECT * FROM sm_emp@sm_80;


CREATE DATABASE LINK ora8.us.oracle.com
CONNECT TO scott  IDENTIFIED BY tiger
USING 'ora8.us.oracle.com';
SELECT * FROM [e-mail];

SELECT * FROM [e-mail];


drop database link sm_link;

CREATE DATABASE LINK sm_link
CONNECT TO scott  IDENTIFIED BY tiger
USING 'local';


CREATE DATABASE LINK sm_80
CONNECT TO scott  IDENTIFIED BY  tiger
USING 'test140';