oracle练习

1.创建两个本地管理表空间，表空间名为xm1和xm2,各包含一个数据文件
（文件名为d:\A1.DBF和d:\A2.dbf）,大小都为50M。
前者让系统自动选择所分配的区的大小且自动段空间管理，
后者将每次分配的区大小统一限制为128K。（10分）


create tablespace jiashixin1
datafile 'd:\A1.dbf' size 50M
extent management local autoallocate segment space management auto;


create tablespace jiashixin2
datafile 'd:\A2.dbf' size 50M
extent management local uniform size 128k;


2.为表空间xm1添加一个数据文件（文件名为d:\A3.DBF，大小为10m），
并查询表空间名和数据文件
(用col tablespace_name for a30,col file_name for a30 控制每行输出的列数使输出内容整齐）。（10分）
alter tablespace jiashixin1 add datafile 'D:\A3.dbf' size 10M;


select tablespace_name,file_name from dba_data_files;


3.创建用户“xm”,密码为自己学号，该用户采用数据库口令认证方式，
强制要求首次登陆时必须修改密码，将默认表空间设置为xm1,并在xm1表空间上为其分配10m空间限额。（10分）
create user jiashixin identified by 201410625109 
password expire default
tablespace jiashixin1
quota 10M on jiashixin1;


4.把系统权限resource、创建会话和创建表等的权限授予用户xm。（5分）


grant resource,create session ,create table to jiashixin;


create role role_jiashixin identified by 201410625109;


5. 创建一个角色role_xm,并为角色授予insert\update\delete权限，再把角色授予用户xm。（10分）


grant insert,update,delete on scott.dept to role_jiashixin;


grant role_jiashixin to jiashixin;


6. 连接用户，将SQL*plus的默认提示符从“SQL>”修改为显示当前用户名和连接标识符。（5分）


set sqlprompt "_USER '@'_CONNECT_IDENTIFIER _date"
set time on;


7. 在当前用户下创建两个表BOOKS和ORDERS,存储表空间为xm，表的结构如下：


create table books(
bookId char(6) primary key,
bookName varchar2(20),
author varchar2(20),
bookTime date default sysdate
)
tablespace jiashixin1;




create table orders(
orderId char(4) primary key,
bookId char(6)references books(bookId),
dgsl integer
)
tablespace jiashixin1;
8、用insert into插入记录，两个表的记录如图，只写出插入下列表列出的记录。


insert into books values('101','ORACLE数据库','贾世鑫',to_date('1999-01-01','YYYY-MM-DD'));


insert into books values('102','VF数据库','贾世鑫',to_date('2010-01-01','YYYY-MM-DD'));


insert into books values('104','VB程序设计','贾世鑫',to_date('1999-01-01','YYYY-MM-DD'));


insert into books values('105','VC程序设计','贾世鑫',to_date('2010-01-01','YYYY-MM-DD'));


insert into orders values('01','101','150.00');
insert into orders values('02','101','140.00');
insert into orders values('03','102','400.00');
insert into orders values('04','102','410.00');
insert into orders values('05','102','390.00');
insert into orders values('06','103','600.00');
9、创建范围分区表rang_book_xm（结构和books一样），
根据出版时间（booktime）创建分区，将1999年1月1日前的保存在第一分区中p1中，
表空间为xm1；其他的保存在第一分区中p2中，表空间为xm2；
用insert into ...select ...插入books表的记录（表3），
并查询1999年1月1日前(及p1分区)的记录。（10分）
create table rang_book_jiashixin(
bookId char(6) primary key,
bookname varchar2(20),
author varchar2(20),
booktime date
)
partition by range(booktime)
(
partition p1 values less than(to_date('1999-01-01','YYYY-MM-DD'))tablespace jiashixin1,
partition p2 values less than(maxvalue) tablespace jiashixin2
);


10.创建一个视图xm_view，检索books中的bookid,bookname和orders的dgsl,并查询这个视图，显示视图的结果。（10分）
create view jiashixin_view
as
select b.bookid,b.bookname,ord.dgsl
from books b,orders ord
where b.bookid=ord.bookid;




11.用创建游标（xm_yb）的方法实现显示books表中的全部数据。（10分）
set serveroutput on


declare
 cursor jiashixin_yb is
 select bookId,bookName,author,bookTime
 from books
 order by bookId;
begin
  FOR V_BOOKS IN jiashixin_yb LOOP
DBMS_OUTPUT.PUT_LINE(jiashixin_yb%ROWCOUNT || ':' || 
V_BOOKS.BOOKID  || ' ' || V_BOOKS.BOOKNAME|| ' ' || 
V_BOOKS.AUTHOR  || ' ' || V_BOOKS.BOOKTIME);
 end loop;
 if jiashixin_yb%isopen then close jiashixin_yb;
 end if;
end;
/