---------- sm_emp_sale_data.txt ----------
/*
 * 范例名稱：數(shù)據(jù)準(zhǔn)備
 * 文件名稱：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','關(guān)羽',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生成數(shù)據(jù)

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自動和基表數(shù)據(jù)一致。
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;
--INSERT INTO sm_emp VALUES
('0000000019','009',110,'62613546');
--不在sm_emp_telno2

INSERT INTO sm_emp VALUES
('0000000029','009',101,'62613546');
--在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',51,'62613546');
INSERT INTO sm_emp_telno VALUES
('0000000010','FROMVIEW',50,'62613546');
--看看基表
SELECT * FROM sm_emp;
--delete
Delete  from sm_emp_telno ;
--SELECT * FROM sm_emp;
--剩下view :sm_emp_telno以外的紀(jì)錄


INSERT INTO sm_emp_telno VALUES
('2000000010','FROMVIEW',50,'911');
INSERT INTO sm_emp_telno VALUES
('3000000010','FROMVIEW',50,'911');





---------- sm_view_dd.txt ----------
/*
 * 范例名稱：DML 與	VIEW
 * 文件名稱：sm_view_dd.txt
 */

DESC USER_UPDATABLE_COLUMNS;
SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME='TOTAL';

SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME='SM_EMP';

-- user_views find the sql of the view
select * from user_views where 
view_name='SM_EMP_TELNO';

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

insert into sm_emp_telno_read VALUES('0000000001','張飛',100000,'62613546');
--ERROR 位于第 1 行:
--ORA-01733: 此處不允許虛擬列

CREATE OR  REPLACE VIEW sm_emp_telno_write AS
(SELECT * FROM sm_emp WHERE telno='62613546');
insert into sm_emp_telno_read VALUES('0000000001','張飛',100000,'62613546');
--ok

SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME='sm_emp_telno_read';



---------- view的作用.txt ----------
/*
 * 范例名稱：如果應(yīng)用建立在視圖上，
 當(dāng)數(shù)據(jù)庫表發(fā)生變化時，
 * 可以在表上修改視圖，通過視圖屏蔽表的變化，
 從而應(yīng)用程序可以不動。
 * 
 * 文件名稱：view的作用.txt
 */
--------view的作用.txt-----------------------------
drop table test2;
create table test2(
name1 varchar(10));

create or replace view view_test2 as 
select name1 name  from test2; 

--基于view的應(yīng)用
select name from view_test2;

--基于table的應(yīng)用
select name1 from test2;

drop table test2;

--改變字段name1為name2
create table test2(
name2 varchar(10));
--只改view
create or replace view view_test2 
as select name2 name  from test2; 

--select 可以不變。注意：select 通常是寫在java中的更改極難！
select name from view_test2;

select name1 from test2;


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

SELECT *  FROM sm_emp
WHERE ROWNUM<=2;