oracle视图

创建一个用户
set sqlprompt "_USER > "
create user jsx identified by 123; //创建用户jsx
grant all privileges to jsx; //给用户授权
conn jsx/123;
//创建books表
CREATE TABLE books(
bookid NUMBER(6) PRIMARY KEY,
booknum VARCHAR2(6),
bookname VARCHAR2(60),
author VARCHAR(50),
publish VARCHAR2(50),
bookprice NUMBER(8,2),
category CHAR(10),
booktime DATE DEFAULT SYSDATE
)
STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 20 MAXEXTENTS 15)
TABLESPACE users;


//创建orders表
CREATE TABLE orders(
order_id VARCHAR2(20) CONSTRAINT O_PK PRIMARY KEY,
order_date DATE DEFAULT SYSDATE,
qty INTEGER,
payterms VARCHAR2(12),
book_id NUMBER(6) CONSTRAINT O_FK REFERENCES books(bookid)
);
//插入数据books
INSERT INTO books(booknum,bookname,author,bookprice) 
VALUES('DB1006','ORACLE','admin',30);


INSERT INTO books(booknum,bookname,author,bookprice) 
VALUES('DB1003','JAVA','admin2',37.8);


INSERT INTO books(booknum,bookname,author,bookprice) 
VALUES('DB1002','LINUX','admin3',32.31);




//插入数据orders
INSERT INTO orders(order_id,book_id) 
VALUES(1,10);




创建图书信息视图


CREATE VIEW book_view 
AS
SELECT booknum,bookname,author,bookprice 
FROM books 
WHERE bookprice > 32;


检索视图
SELECT * FROM book_view;




CREATE VIEW book_view2 
AS
SELECT bookname,bookprice 
FROM books 
WHERE bookprice > 32;




检索视图
SELECT * FROM book_view2;


//视图中插入记录
INSERT INTO book_view VALUES(15,'SX2001','高等数学上','李映雪',40.2);
INSERT INTO book_view VALUES(16,'SX2001','高等数学下','李映雪',29);


UPDATE book_view
SET bookprice = 100
WHERE bookname = '高等数学上';


UPDATE book_view
SET bookprice = 20
WHERE bookname = '高等数学下';


SELECT * FROM bookview2;


//创建只读视图


CREATE VIEW bookview2
AS 
SELECT bookid,booknum,bookname,author,bookprice
FROM books
WHERE bookprice > 30
WITH READ ONLY;




创建订单信息视图 使用check option约束
CREATE VIEW orders_view 
AS
SELECT order_id,qty,book_id FROM orders WHERE qty > 100 WITH CHECK OPTION;


基于books和orders两个表创建视图
CREATE VIEW bo_view
AS
SELECT bookid,bookname,QTY FROM books B,orders ord WHERE b.bookid = ord.book_id;


SELECT * FROM bo_view;//查看视图


修改视图


ALTER VIEW bo_view COMPILE;  //可添加删除约束  无法修改结构 若修改结构必须create


CREATE OR REPLACE VIEW book_view 
AS
SELECT booknum,bookname,category,author,bookprice FROM books WHERE bookprice > 32;


查看视图定义
SELECT view_name,text FROM USER_VIEWS WHERE view_name ='BOOK_VIEW';
查看是否支持DML操作
SELECT column_name,insertable,updatable,deletable FROM USER_UPDATABLE_COLUMNS WHERE table_name='BOOK_VIEW';


insert into orders_view values('141622940',90,400300);


删除视图
DROP VIEW book_view;






内嵌视图


SELECT * FROM (
SELECT ROWNUM num,salnum,nest_order1.book_id,b.bookname,b.author,b.bookprice 
FROM(
SELECT SUM(qty) salnum,book_id 
FROM orders GROUP BY book_id) nest_order1,books b 
WHERE nest_order1.book_id=b.book_id ORDER BY salnum DESC) nest_order2
WHERE num<6;