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