script_71.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;
--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以外的纪录


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 ----------
/*
 * 范例名称：如果应用建立在视图上，
 当数据库表发生变化时，
 * 可以在表上修改视图，通过视图屏蔽表的变化，
 从而应用程序可以不动。
 * 
 * 文件名称：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的应用
select name from view_test2;

--基于table的应用
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;