script_71_bak.txt

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

---------- sm_emp_sale_data.txt ----------
/*
 * 范例名称：数据准备
 * 文件名称：sm_emp_sale_data.txt
 */

--insert data need in chap11 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));

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');
--007的telno与别人不同
INSERT INTO sm_emp VALUES('0000000007','007',100000,'62634546');
COMMIT;
--data sm_emp

  
  
---------- sm_view1.txt ----------
/*
 * 范例名称：视图的定义
 * 文件名称：sm_view1.txt
 */

--首先运行sm_emp_sale_data生成数据

CREATE OR  REPLACE VIEW sm_emp_telno AS
(SELECT * FROM sm_emp WHERE telno='62613546');


DESC  sm_emp_telno;
SELECT * FROM sm_emp_telno;

--view自动和基表数据一致。
SELECT * FROM sm_emp_telno;
--基表insert
INSERT INTO sm_emp VALUES('0000000009','009',100,'62613546');
SELECT * FROM sm_emp_telno;





--------------------------------------------------------------------



--对视图使用where子句
SELECT empid,name FROM sm_emp_telno  WHERE salary>100;

--VIEW BASE ON VIEW:基于view的view
CREATE  OR  REPLACE VIEW sm_emp_telno2 AS
(SELECT * FROM sm_emp_telno  WHERE salary>100);

DESC sm_emp_telno2;
SELECT * FROM  sm_emp_telno2;

--DROP
DROP VIEW sm_emp_telno2;
SELECT * FROM  sm_emp_telno2;


--ALTER VIEW (DROP ,THEN CREATE):直接使用CREATE  OR  REPLACE重建
CREATE  OR  REPLACE VIEW sm_emp_telno2 AS
(SELECT * FROM sm_emp_telno  WHERE salary>50);
 SELECT * FROM sm_emp_telno2;


--INSERT INTO VIEW:从view插入
INSERT INTO sm_emp_telno VALUES('0000000010','FROMVIEW',10,'62613546');
--看看基表
SELECT * FROM sm_emp;
--delete
Delete  from sm_emp_telno ;
--SELECT * FROM sm_emp;
--剩下view :sm_emp_telno以外的纪录




---------- sm_view2.txt ----------
/*
 * 范例名称：DML 与	VIEW
 * 文件名称：sm_view2.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;

--DML AND VIEW
INSERT INTO sm_saleorderview VALUES
(999,100,'0000000999','999','62613546','09_9月_02');
--ERROR 位于第 1 行:
--ORA-01776: 无法通过连接视图修改多个基表
 
INSERT INTO sm_saleorderview 
(TransactionID,TotalPrice ,EmployID ,SaleTime )
VALUES
(999,100,'0000000999','09_9月_02');

SELECT * FROM sm_saleorderlist;

SELECT * FROM sm_saleorderview ;



--view 中仍显示不出。因为sm_emp中没有相应记录，
--关联查询WHERE sm_saleorderlist.EmployID=sm_emp.empid未满足。

INSERT INTO sm_saleorderview 
(Name,telno )
VALUES
('999','62613546');
--ERROR 位于第 2 行:
--ORA-01779: 无法修改与非键值保存表对应的列
--证明结论2

update sm_saleorderview set name='aa';
--出乎意外的delete
delete from sm_saleorderview;

select * from sm_saleorderview;

select * from sm_saleorderlist;

--只delete了sm_saleorderlist的一部分
select * from sm_emp;


---------- sm_view_dd.txt ----------
/*
 * 范例名称：DML 与	VIEW
 * 文件名称：sm_view_dd.txt
 */

DESC USER_UPDATABLE_COLUMNS;

SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME='SM_SALEORDERVIEW';



---------- sm_view_readonly.txt ----------
/*
 * 范例名称：只读VIEW
 * 文件名称：sm_view_readonly.txt
 */
  
CREATE OR  REPLACE VIEW sm_emp_telno_read AS
(SELECT * FROM sm_emp WHERE telno='62613546')
WITH READ ONLY;



---------- sm_rownum.txt ----------
/*
 * 范例名称：TOP N分析:ROWNUM伪列
 * 文件名称：sm_rownum.txt
 */

SELECT *  FROM sm_emp
WHERE ROWNUM<=2;


--练习

--通过两个view 实现 一个视图：包括雇员姓名，雇员编号,salary,及雇员总销售额。

--view1:销售交易单分组求出雇员总销售额，EmployID组成total视图
--sum_price 是sum(totalprice)的别名，作为view 的'字段'

create view total as
select sum(totalprice) sum_price,EmployID from sm_saleorderlist
group by EmployID;

--view2:total视图通过EmployID与sm_emp建立联接join,建立emp_tatal视图
create view emp_tatal as
select name,empid,salary,sum_price
from sm_emp,total
where empid=EmployID;


--扩展：
--只用一个view :ename,etotal,salary,telno为查询语句的别名
--使用多表查询同时用分组
create or replace view emp_totalprice as 
select max(name) ename,sum(totalprice) etotal,max(salary) salary,max(telno) telno
from sm_emp,sm_saleorderlist
WHERE sm_saleorderlist.EmployID=sm_emp.empid
group by sm_saleorderlist.EmployID;


--

CREATE table base 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;

create view sale_emp as select * from base;



--去掉TransactionID，
CREATE OR REPLACE VIEW sm_saleorderview AS 
SELECT 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;

--看DD,不变
SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME='SM_SALEORDERVIEW';
--插入
INSERT INTO sm_saleorderview 
(TotalPrice ,EmployID ,SaleTime )
VALUES
(100,'0000000999','09_9月_02');
--ERR,因为TransactionID  NOT NULL ,此时以NULL补。则ERR

UPDATE sm_saleorderview  SET TotalPrice=100;
--OK