锐聘学院oracle上机题

–2.1
----1、 在Web版企业管理器中，创建名称为mytablespace的表空间。为表空间增加一个名为datafile01的数据文件，大小为100M。然后编辑该表空间，为其添加一个datafile02的数据文件，大小为50M。
create tablespace mytablespace datafile ‘E:\oracle\datafile01.dbf’ size 100m;
–为表空间增加数据文件
alter tablespace mytablespace add datafile ‘E:\oracle\datafile02.dbf’ size 50m;
–2、 在PLSQL Developer中创建用户myname,密码为123456，将其默认的表空间修改为mytablespace。
create user myname identified by 123456;
–修改默认表空间名
alter database default tablespace mytablespace;
–3、 在sqlplus中创建名称为user_data的表空间，为表空间增加一个名为user_data的数据文件，大小为50M；创建名称为user_temp的临时表空间，并增加一个名为user_temp的数据文件，大小为50M。

create tablespace user_data datafile ‘E:\oracle\user_data.dbf’ size 50m;
create temporary tablespace user_temp tempfile ‘E:\oracle\user_temp1.dbf’ size 50m;

–删除表空间和临时表空间
drop tablespace user_data;
drop tablespace user_temp;

–2.2
1、 在SQL*PLUS下创建用户qst，密码为123456，默认表空间为user_data，临时表空间为user_temp；

修改用户qst密码为123；

锁定和解锁用户qst；

删除用户qst

create user qst default tablespace user_data temporary tablespace user_temp identified by 123456;
alter user qst identified by 123;
alter user qst account lock;
alter user qst account unlock;
drop user qst ;
2、 在PLSQL Developer中创建用户qst1，密码为123456，默认表空间为user_data，临时表空间为user_temp；

修改用户qst1密码为123；

锁定和解锁用户qst1；

删除用户qst1
create user qst1 default tablespace user_data temporary tablespace
user_temp identified by 123456;
alter user qst1 identified by 123;
alter user qst1 account lock;
alter user qst1 account unlocked;
drop user qst1;
–2.3
–1、 在SQLPLUS下为用户qst赋予连接数据库的权限，以及访问scott中emp表的权限；
create user qst identified by 123;
grant connect to qst;–为用户qsr赋予连接数据库的权限
grant select on scott.emp to qst;–访问scott中emp表的权限
–2、 在SQLPLUS下对用户qst回收连接数据库的权限，以及访问scott中emp表的权限；
revoke connect from qst;–收回权限
revoke select on scott.emp from qst;
–3、 在SQLPLUS下为用户qst赋予连接并访问数据库的角色：connect，resource；
–为用户qst赋予连接并访问数据库的角色，注意角色
create role s2;
grant connect,resource to s2;
grant s2 to qst;
–4、 在SQLPLUS下实现权限的传递：scott用户中对emp表的访问权限通过权限传递授予qst，用户qst再把该权限授予qst1；
grant select on scott.emp to qst with grant option;
–5、 在SQL*PLUS下创建角色、删除角色；
–创建角色、删除角色
create role tt;
drop role tt;
–6、 在PLSQL Developer中为用户qst1赋予连接并访问数据库的角色：connect，resource
–为用户qst1赋予连接并访问数据库的角色，注意角色
create role s1;
grant connect,resource to s1;
grant s1 to qst1;
—3.2
drop table s;
drop table c;
create table s(
sno varchar2(10) not null,
sname varchar2(20) null
);
create table c(
cno varchar2(5) not null ,
cname varchar2(20) null,
cteacher varchar2(20) null
);
create table sc(
sno varchar2(10),
cno varchar2(5),
scgrade varchar2(20)
);
alter table s add stel varchar2(20);–添加列
alter table c modify cname varchar2(40);–修改列的数据类型
alter table sc rename column scgrade to allscore;–修改列名
drop table c;
rename s to ss;

–3.3
–1、 结合视频和综合设计题1，完成表的创建，并添加相应的约束。
create sequence ss start with 1000 increment by 1;
create table item(
itemio number(20) primary key,
itemname varchar2(50) not null,
startdate date,
enddate date,
money number(10),
constraint uk unique(itemname),
constraint fk check(money>0)
);
select * from item;
insert into item values(ss.nextval,‘小型监控系统’，‘6-8月-2012’，‘1-1月-2019’，100000);

create table clerk(
no char(5) primary key,
name varchar2(20) not null,
sex varchar2(5) default ‘男’,
birthday date,
address varchar2(10),
salary number(20)
);
alter table clerk add constraint s1 check(sex='男’or sex=‘女’);
alter table clerk add constraint s2 check(salary>0);

–2、 在3.2章节“上机练习”中，为表s的sno，表c的cno添加主键约束；为表sc中的sno和cno添加外键约束，scgrade列添加非空约束；为表s中新添加的的stel列添加唯一约束。
alter table s add constraint t1 primary key(sno);
alter table c add constraint t2 primary key(cno);
alter table sc add constraint t3 foreign key(sno) references s(sno);
alter table sc add constraint t4 foreign key(cno) references c(cno);
alter table sc modify (scgrade not null);–给列添加非空约束！！！
alter table s modify(unique(stel));–给列添加唯一约束！！！
–3、 新创建一张带有约束的雇员表emptest，表结构如下：
create table emptest(
emptno number(3) primary key,
emptname varchar2(20) not null,
sex varchar2(2) default ‘男’,
constrint m1 check(sex='男’or sex=‘女’);
birthday date
);
–3.4
–1
insert into s values(‘10001’,‘王晓明’,‘131123411118’);
insert into s values(‘10002’,‘王涵’,‘13212341118’);
insert into s values(‘10003’,‘李有才’,‘13312341118’);
insert into s values(‘10004’,‘张小小’,‘13412341118’);
insert into s values(‘10005’,‘董强’,‘13512341118’);
–2
create table s1 as select * from s where 1=2;
insert into s1 select *from s where sno=‘10001’;
insert into s1 select * from s where sno=‘10002’;
select *from s1;
–3、 修改s表中的数据，将董强的电话改为13612341118。
update s set stel=‘13612341118’ where sname=‘董强’;
–4、 查询修改后的s表中的全部信息
select *from s;
–5、 删除s1表中的学号列。
alter table s1 drop column sno;–删除表s1的学号列
–3.5
–1、 假设用户student2创建了表s，表s的表结构和数据参见3.3、3.5章节。现要求把查询、修改表s的权限赋予普通用户student1，该如何操作？
grant select on s to student1;
grant update on s to student1;
–2、 收回student1对表s进行修改的权限，该如何操作？
revoke update on s from student1;
–3.6
–1、 向3.2章节“上机练习”中的s表添加两行数据，添加后设置保存点p1，应该如何操作？添加的数据内容如下
insert into s values(‘10010’,‘孙燕姿’,‘18600001111’);
insert into s values(‘10020’,‘周杰伦’,‘18900002222’);
savepoint p1;
–2、 更新学号为10020的电话为18000000000，更新后设置保存点p2，应该如何操作？
update s set stel=‘18000000000’ where sno=‘10020’;
savepoint p2;
–3、 删除学号为10010的学生记录，删除后设置保存点p3，应该如何操作？
delete from s where sno=‘10010’;
savepoint p3;
–4、 分别回滚到p1,p2,p3的状态，查看表s中数据的变化；
rollback to p1;–要加to！！！
rollback to p2;
rollback to p3;
–5、 最后提交操作，查看表s中数据的变化。
select * from s;
commit；
------------4.1----------------

–1、 下面的语句是否可以执行成功？
select ename , job , sal as salary from emp;
–不可以

–2、 下面的语句是否可以执行成功？
select * from emp;
–不可以，应该是
select * from scott.emp;

–3、 找出下面语句中的错误：
select empno , ename,(sal * 12)as annual from scott.emp;

–4、 显示表dept的结构，并查询其中的全部数据。
select * from dept;

–5、 显示出表emp中的不重复的岗位job。
select distinct job from emp;

–6、 选择员工姓名的第三个字母是A的员工姓名。
select ename from emp where ename like ‘__A%’;–模糊查询！！！

–7、 选择姓名中有字母A和E的员工姓名。
select ename from emp where ename like ‘%A%E’;
-----------4.2------------------
–① 在一张表中，显示所有员工的姓名ename，部门号deptno和部门名称dname。
select ename,d.deptno,dname from dept d join emp e on d.deptno=e.deptno;–指明是哪个表的deptno
–② 查询20号部门员工的job和20号部门的loc。
select distinct e.job,d.loc from emp e join dept d on e.deptno=d.deptno and d.deptno=20;
–③ 选择所有有奖金comm的员工的ename , dname , loc。
select ename,dname,loc from dept d join emp e on d.deptno=e.deptno and comm is not null;
–④ 选择在DALLAS工作的员工的ename , job , deptno, dname。
select ename，job,d.deptno,dname from dept d join emp e on loc=‘DALLAS’ and d.deptno=e.deptno;
–⑤ 选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno，结果类似于下面的格式
employees Emp# manager Mgr#
SMITH 7369 FORD 7902
----起别名
select ename as employees,emp.deptno as Emp#,mgr as manager,dept.deptno as Mgr# from dept
join emp on dept.deptno=emp.deptno;
–⑥ 查询各部门员工姓名和他们所在位置，结果类似于下面的格式
Deptno Ename Loc
20 SMITH DALLAS
select dept.deptno ,ename ,loc from dept join emp on dept.deptno=emp.deptno;
------------4.3-----------------
–① 查询公司员工工资的最大值，最小值，平均值，总和
select max(sal),min(sal),avg(sal),sum(sal) from emp;
–② 查询各种job的员工工资的最大值，最小值，平均值，总和
select job,max(sal),min(sal),avg(sal),sum(sal) from emp group by job;
–③ 查询各种job的员工人数(提示:对job进行分组)
select job,count(empno) from emp group by job;
–④ 查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(sal)-min(sal) difference from emp;
–⑤ 查询各个管理者手下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内
select mgr,min(sal) from emp where mgr is not null group by mgr
having min(sal)>800;–注意having
–⑥ 查询所有部门的名字dname，所在位置loc，员工数量和工资平均值
select dname,loc,count(empno),avg(sal) from dept join emp on dept.deptno=emp.deptno
group by dname,loc;–两个都是分组的
–⑦ 查询公司的人数，以及在1980-1987年之间，每年雇用的人数，结果类似下面的格式
select distinct (select count(ename) from emp ) “total”,
(select count(ename)from emp where hiredate>=to_date(‘19800101’,‘yyyymmdd’)and hiredate<to_date(‘19810101’,‘yyyymmdd’)) “1980”,
(select count(ename) from emp where hiredate>=to_date(‘19810101’,‘yyyymmdd’)and hiredate<to_date(‘19820101’,‘yyyymmdd’)) “1981”,
(select count(ename) from emp where hiredate>=to_date(‘19820101’,‘yyyymmdd’)and hiredate<to_date(‘19830101’,‘yyyymmdd’)) “1982”,
(select count(ename) from emp where hiredate>=to_date(‘19870101’,‘yyyymmdd’)and hiredate<to_date(‘19880101’,‘yyyymmdd’)) “1987”
from emp;
select*from emp;
------------4.4------------------------
–1、 查询和scott相同部门的员工姓名ename和雇用日期hiredate
select ename,hiredate from emp where deptno =(
select deptno from emp where ename=‘SCOTT’
);
–2、 查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal；
select empno,ename,sal from emp where sal>(select avg(sal) as avgsal from emp);
–3、 查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名ename；
select deptno from emp where ename like ‘%u%’;

select empno,ename from emp where deptno =(
select deptno from emp where ename like ‘%u%’
);
–4、 查询在部门的loc为NewYork的部门工作的员工的员工姓名ename，部门名称dname和岗位名称job；
select ename,dname,job from dept d,emp e where loc=‘NEWYORK’and d.deptno=e.deptno;
–5、 查询管理者是king的员工姓名ename和工资sal。
select ename,sal from emp where mgr=(select empno from emp where ename=‘KING’);
----------------5.1-----------------------------------
–1、 查询工资大于1600的员工姓名和工资
select ename,sal from emp where sal>1600;
–2、 选择工资不在4000到5000的员工的姓名和工资
select ename,sal from emp where sal not between 4000 and 5000 ;
–3、 选择雇用时间在1981-2-19到1981-12-3之间的员工姓名，JOB和雇用时间HIREDATE
select ename,job,hiredate from emp where hiredate between ‘19-2月-1981’ and ‘3-12月-1981’;
–4、 选择在20和30号部门工作的员工姓名和部门号；
select ename,deptno from emp where deptno in(20,30);
–5、 选择在1987年雇用的员工的姓名和雇用时间
select ename,hiredate from emp where hiredate=1987;
–6、 选择公司中没有管理者的员工姓名及JOB
select ename,job from emp where mgr is null order by job;
–7、 选择公司中有奖金的员工姓名，工资和奖金级别
select ename,sal,comm from emp where comm is not null;
–8、 连接表emp的全部列，各个列之间用逗号连接，列头显示成OUT_PUT(提示:使用连接符||)。
select *from emp;
select empno||’,’||ename||’,’||job||’,’||mgr||’,’||hiredate||’,’||sal||’,’||comm||’,’||deptno
as “OUT_PUT” from emp;
---------------5.2--------------------------------------------
select sysdate,sysdate+1 "日期加1天"from dual;
select sysdate,add_months(sysdate,1) “日期加1个月” from dual;
select sysdate,add_months(sysdate,12) “加1年” from dual;
—加1星期
select sysdate,to_char(sysdate+7,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1天
select sysdate,to_char(sysdate+1,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1小时
select sysdate,to_char(sysdate+1/24,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1分钟,注意是除以60
select sysdate,to_char(sysdate+1/24/60,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–加1秒钟
select sysdate,to_char(sysdate+1/24/60/60,‘yyyy-mm-dd HH24:MI:SS’) from dual;
–相差月数
select months_between(sysdate,to_date(‘2014-5-5’,‘yyyy-mm-dd’))“相差月数” from dual;

select months_between(sysdate,add_months(sysdate,-1))“相差月数” from dual;

—日期所在月份的最后一天
select sysdate,last_day(sysdate) from dual;
—下一个星期一是几号
select sysdate ,next_day(sysdate,2) “下一个星期一是几号” from dual;
select sysdate,next_day(sysdate,‘星期一’)“下一个星期一是几号” from dual;
----下一个星期日是几号
select sysdate,next_day(sysdate,1)“下一个星期日是几号” from dual;
select sysdate,next_day(sysdate,“星期日”)“下一个星期日是几号” from dual;
----下一个星期二是几号
select sysdate,next_day(sysdate,3)“下一个星期二是几号” from dual;

----截取到年（本年的第一天）
select trunc(sysdate,‘year’) from dual;
----截取到季度
select trunc(sysdate,‘q’) from dual;
----截取到月
select trunc(sysdate,‘month’) from dual;
-----分析函数
----给排好序的查询结果中的每一行返回一个唯一的编号，先按薪水排序，再编号
select row_number() over(order by sal desc) as 编号,empno,ename,deptno,sal from emp;
----partition by deptno 按照部门编号分组，组内编号，sal值相同的部门编号也不同
select row_number() over(partition by deptno order by sal desc) as 编号,empno,ename,deptno,sal from emp;
—每组内从1开始编号，组内编号，sal值相同的编号相同，后续编号跳过，如1,2,2,4,5,5,7
select rank() over(order by sal desc)as 编号,empno,ename,deptno,sal from emp;
select rank() over(partition by deptno order by sal desc) as 编号,empno,ename,deptno,sal from emp;
—每组内从1开始编号，组内编号，sal值相同的编号相同，后续编号连续，如1,2,2,3,4,4,5
select dense_rank() over(order by sal desc)as 编号,empno,ename,deptno,sal from emp;
select dense_rank() over(partition by deptno order by sal desc) as 编号,empno,ename,deptno,sal from emp;
----------------------------------第六章-----------------------------------
--------------1、创建范围（区间）分区表----------------
–drop table drawlist;
create table drawlist(
dt_draw date not null
)
partition by range (dt_draw)(
partition p1 values less than(to_date(‘1/1/2013’,‘dd-mm-yyyy’)),
partition p2 values less than(to_date(‘1/1/2014’,‘dd-mm-yyyy’)),
partition p3 values less than(maxvalue)
);
-------------插入测试数据

insert into drawlist values(to_date(‘2012-1-2’,‘yyyy-mm-dd’));
insert into drawlist values(to_date(‘2013-1-2’,‘yyyy-mm-dd’));
insert into drawlist values(to_date(‘2014-1-2’,‘yyyy-mm-dd’));
select * from drawlist;
----查询第一个分区
select from drawlist partition(p1);
select from drawlist partition(p2);
select from drawlist partition(p3);
---------------2、创建散列（Hash）分区表---------每个分区差距不会太大----------
–drop table hash_table;
create table hash_table(
hash_no number(10) not null
)
partition by hash(hash_no)(
partition p1,
partition p2,
partition p3,
partition p4
);
-----使用数据生成器插入500条测试数据
-----查询每个
select count() from hash_table partition(p1);
select count() from hash_table partition(p2);
select count() from hash_table partition(p3);
select count(*) from hash_table partition(p4);
--------------------3、创建列表分区表------------------
–drop table area;
create table area(
code number(10) not null，
name varchar2(10) not null
)
partition by list(code)(
partition p1 values(102200,102202,102203)，
partition p2 values(164300,164302,164303)
);
----使用数据生成器生成
select * from area partition(p1);
select * from area partition(p2);
--------------------4、创建范围散列分区表-------------------
–drop table range_hash1;
create table range_hsah2(
dt_draw date not null,
hash_no number(10) not null
)
partition by range(dt_draw) subpartition by hash(hash_no)(
partition p1 values less than (to_date(‘1/1/2013’,‘dd-mm-yyyy’))(
subpartition p1_hash1,
subpartition p1_hash2
),
partition p2 values less than (to_date(‘1/1/2014’,‘dd-mm-yyyy’))(
subpartition p2_hash1,
subpartition p2_hash2
),
partition p3 values less than (maxvalue)(
subpartition p3_hash1,
subpartition p3_hash2
)
);
------------使用数据生成器产生数据
--------查询每个分区的数据
select *from range_hash2;
select *from range_hash2 partition (p2);
select *from range_hash2 subpartition (p2_hash1);
select *from range_hash2 subpartition (p2_hash2);
--------------------5、创建范围列表分区------------
drop table range_list;
create table range_list(
dt_draw date not null,
code number (10) not null
)
partition by range(dt_draw) subpartition by list(code)(
partition p1 values less than (to_date(‘1/1/2013’,‘dd-mm-yyyy’))(
subpartition p1_list1 values (102200,102202,102203),
subpartition p1_list2 values (164300,164302,164303)
),
partition p2 values less than (to_date(‘1/1/2014’,‘dd-mm-yyyy’))(
subpartition p2_list1 values (102200,102202,102203),
subpartition p2_list2 values (164300,164302,164303)
),
partition p3 values less than (maxvalue)(
subpartition p3_list1 values (102200,102202,102203),
subpartition p3_list2 values (164300,164302,164303)
)
);
-------插入测试数据
insert into range_list values(to_date(‘2012-1-2’,‘yyyy-mm-dd’),102200);
insert into range_list values(to_date(‘2013-5-1’,‘yyyy-mm-dd’),102200);
insert into range_list values(to_date(‘2014-6-2’,‘yyyy-mm-dd’),102200);

insert into range_list values(to_date(‘2012-1-2’,‘yyyy-mm-dd’),164300);
insert into range_list values(to_date(‘2013-5-1’,‘yyyy-mm-dd’),164300);
insert into range_list values(to_date(‘2014-6-2’,‘yyyy-mm-dd’),164300);

insert into range_list values(to_date(‘2012-2-2’,‘yyyy-mm-dd’),102200);
insert into range_list values(to_date(‘2013-4-1’,‘yyyy-mm-dd’),102200);
insert into range_list values(to_date(‘2014-7-2’,‘yyyy-mm-dd’),102200);

insert into range_list values(to_date(‘2012-2-2’,‘yyyy-mm-dd’),164300);
insert into range_list values(to_date(‘2013-4-1’,‘yyyy-mm-dd’),164300);
insert into range_list values(to_date(‘2014-7-2’,‘yyyy-mm-dd’),164300);
------查询
select *from range_list;
select *from range_list partition(p2);
select *from range_list subpartition(p2_list1);
select *from range_list subpartition(p2_list2);
------------------6、维护分区-------------
create table dlist(
dt_date date not null
)
partition by range(dt_date)(
partition p1 values less than(to_date(‘1/1/2013’,‘dd-mm-yyyy’)),
partition p2 values less than(to_date(‘1/1/2014’,‘dd-mm-yyyy’)),
partition p3 values less than(maxvalue)
);
–删除分区
alter table dlist drop partition p3;
–增加分区
alter table dlist add partition p4 values less than (to_date(‘1/1/2015’,‘dd-mm-yyyy’));

insert into dlist values(to_date(‘2012-1-2’,‘yyyy-mm-dd’));
insert into dlist values(to_date(‘2013-1-2’,‘yyyy-mm-dd’));
insert into dlist values(to_date(‘2014-1-2’,‘yyyy-mm-dd’));

insert into dlist values(to_date(‘2012-2-2’,‘yyyy-mm-dd’));
insert into dlist values(to_date(‘2013-2-2’,‘yyyy-mm-dd’));
insert into dlist values(to_date(‘2014-2-2’,‘yyyy-mm-dd’));

select *from dlist partition(p1);
select *from dlist partition(p2);
select *from dlist partition(p4);
—截断分区
alter table dlist truncate partition (p1);
select *from dlist partition (p1);
—合并分区
alter table dlist merge partitions p2,p4 into partition p4;

select *from dlist partition(p2);
select *from dlist partition(p4);
–拆分分区
alter table dlist split partition p4 at(date ‘2014-1-1’) into (partition p2,partition p4);

select *from dlist partition(p2);
select *from dlist partition(p4);
–重命名分区
alter table dlist rename partition p4 to p3;
select *from dlist partition(p3);
select *from dlist partition(p4);

-------------------7、2-------------------------
–数据库对象最基本的是表
–其他数据库对象包括：同义词、序列、视图、索引
命令窗口创建用户，分配权限
conn system/system@orcl as sysdba;
create user test identified by test;
grant connect,create synonym,create public synonym to test;
grant all on scott.emp to test;
---------同义词------------
–是现有对象的一个别名
–1）私有同义词
–2）公有同义词
----创建私有同义词
create synonym staff for scott.emp：
—创建公有同义词,确保先授予test用户create public sysnonym 权限
create public synonym pub_staff for scott.emp;
---------------序列--------------------
drop table t_testseq;
create table t_testseq(
id number,
name varchar2(10) not null
)
segment creation immediate;–取消使用“延迟短”技术
drop sequence seq_value;
create sequence seq_value
start with 1
increment by 1
minvalue 1
maxvalue 10
nocycle
nocache
noorder;
—插入测试数据
truncate table t_testseq;
insert into t_testseq values(seq_value.nextval,‘aaaa’);
insert into t_testseq values(seq_value.nextval,‘bbbb’);
select * from t_testseq;
–返回序列的下一值和当前值
select seq_value.nextval from dual;
select seq_value.currval from dual;
–修改序列
alter sequence seq_value
cycle
maxvalue 20;
–删除一个序列
drop sequence seq_value;
–查询序列信息
select *from user_sequences;
--------------------视图----------------------------
–将查询封装到视图中
grant create view to scott;–登录system:manager给scott授权
create or replace view v_dept_emp
as
select dept.deptno,dname,loc,empno,ename,job,mgr,hiredate
from emp,dept
where emp.deptno=dept.deptno and dname=‘SALES’;

select *from v_dept_emp;

----创建带有 ORDER BY 子句的视图
create or replace view v_dept_emp
as
select dept.deptno,dname,loc,empno,ename,job,mgr,hiredate
from emp,dept
where emp.deptno=dept.deptno order by dept.deptno;
—创建带有 group by 子句的视图
create or replace view v_dept_emp
as
select dept.deptno,dname,loc,empno,ename,job,mgr,hiredate
from emp,dept
where emp.deptno=dept.deptno group by dname;
—修改 视图中的数据
select *from v_dept_emp;
—通过数据字典中的视图，查看自定义视图中可修改的列
select * from user_updatable_columns
where table_name=‘V_DEPT_EMP’;
—with check option 的使用
create or replace view v_emp_clerk
as
select *from emp where job=‘CLERK’
with check option;
-----force可以创建存在错误的视图

-----插入一个不是‘CLERK’的员工数据会报错
insert into v_emp_clerk values(7999,‘aaaa’,‘aaaa’,7934,‘1-1月-2013’，3000，null，10);

insert into v_emp_clerk values(7999,‘aaaa’,‘CLERK’,7934,‘1-1月-2013’，3000，null，10);

---------------------索引---------------------
create index 索引名 on 表名;
drop table depositor;
create table depositor(
actid number(10) not null,
identity number(10) not null,
lastname varchar2(10) not null,
firstname varchar2(10) not null,
address1 varchar2(200) not null,
address2 varchar2(200),
address3 varchar2(200),
account number(10,2) not null
);
–使用数据生成器给上面的数据填充数据–
select count(*)from depositor;
–打开解释计划窗口执行下面语句查看消耗的资源
select *from depositor where identity =5000;
-----------创建普通索引 后再执行上述语句
create index ix_deptor on depositor(identity);
----2、打开解释计划窗口执行下面语句查看消耗的资源-------
select *from depositor where lastname=‘chaykin’ and firstname=‘Jimmy’
--------创建复合索引后再执行上述语句-----------------
create index ix_depositor_last_first on depositor(lastname,firstname);
----3、打开解释计划窗口执行下面语句查看消耗的资源-------
select *from depositor where actid=58765
--------创建反向索引后再执行-------
create index ix_actid on depositor(actid) reverse;
----4、打开解释计划窗口执行下面语句查看消耗的资源-------
select from depositor where lower(firstname)=‘Jimmy’;
------创建基于函数的索引再执行上句--------------
create index ix_lower_fname on depositor(lower(firstname));
----5、打开解释计划窗口执行下面语句查看消耗的资源-------
select count() from depositor where firstname=‘Luke’；
----------创建位图索引后再执行上述语句--------------
-----位图索引主要应用经常使用聚合函数的操作---------
create bitmap index ix_bitmap_fname on depositor(firstname);
-----6、查看数据字典中的索引信息
select *from user_indexes;
select * from user_ind_columns where table_name=‘DEPOSITOR’;

------------------------第八章-------------------
SET SERVEROUTPUT ON;
declare
v_deptno number(2):=10;
v_dname varchar2(14);
begin
select dname into v_dname from dept
where deptno=v_deptno;
dbms_output.put_line(‘部门编号’||v_deptno||‘部门名称：’||v_dname);
EXCEPTION
when others then
dbms_output.put_line(‘出错’);
END;
—属性数据类型：
–%TYPE:列类型
–%rowtype:行类型
–求编号为7369的员工的姓名和职位，由变量输出
declare
xingming scott.emp.ename%type;
zhiwei scott.emp.job%type;
begin
select ename,job into xingming,zhiwei from emp
where empno=7369;
dbms_output.put_line(‘姓名：’||xingming||‘职位:’||zhiwei);
end;
–求编号7369员工的所有信息，由变量输出
declare
rw scott.emp%rowtype;
begin
select *into rw from emp
where empno=7369;
dbms_output.put_line(‘姓名：’||rw.ename);
dbms_output.put_line(‘职位：’||rw.job);
dbms_output.put_line(‘薪水：’||rw.sal);
end;
–加薪原则
declare
v_sal number;
begin
select sal into v_sal from emp where empno=7934;
if v_sal<1000 then
update emp set sal=sal+200 where empno=7934;
else if v_sal>=1000 and v_sal<2000 then
update emp set sal=sal+150 where empno=7934;
else
update emp set sal=sal+100 where empno=7934;
end if;
end;
–CASE语句
–1、
declare
v_sal number;
begin
select sal into v_sal from emp where empno=7934;
case
when v_sal<1000 then
update emp set sal=sal+200 where empno=7934;
when v_sal>=1000 and v_sal<2000 then
update emp set sal=sal+150 where empno=7934;
else
update emp set sal=sal+100 where empno=7934;
end case;
end;
–2、
declare
v_job varchar2(9);
v_empno number(4):=7369;
begin
select job into v_job from emp where empno=v_empno;
case v_job
when ‘CLERK’ then
update emp set sal=sal+200 where empno=v_empno;
when ‘SALESMAN’ then
update emp set sal=sal+200 where empno=v_empno;
when ‘ANALYST’ then
update emp set sal=sal+200 where empno=v_empno;
when ‘MANAGER’ then
update emp set sal=sal+200 where empno=v_empno;
when ‘PRESIDENT’ then
update emp set sal=sal+200 where empno=v_empno;
END CASE;
END;
----------loop循环--------------
create table test_loop
(
id number(5),
name varchar2(10)
);
declare
v_count number:=1;
begin
loop
insert into test_loop
values(v_count,‘name’||v_count);
v_count:=v_count+1;
exit when v_count>100;
end loop;
END;
select *from test_loop;
-----------while循环-----------------
declare
v_count number:=1;
begin
while v_count<=100 loop
insert into test_loop
values(v_count,‘name’||v_count);
v_count:=v_count+1;
end loop;
end;
------------for循环语句----------------
declare
v_count number:=1;
begin
for v_count in 1…100 loop
insert into test_loop
values(v_count,‘name’||v_count);
end loop;
end;
------------系统异常1------------------
declare
v_empno emp.empno%type;
v_emp scott.emp%rowtype;
begin
v_empno:=&员工编号;–要求输入员工编号
select into v_emp from emp where empno=v_empno;
dbms_output.put_line(‘员工编号：’||v_empno||‘姓名为’||v_emp.ename);
EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line(‘员工编号是’||v_empno||‘的员工不存在’);
when others then
dbms_output.put_line(‘错误号是’||SQLCODE);
dbms_output.put_line(‘错误原因是’||SQLERRM);
END;
-----自定义异常
declare
v_empno emp.empno%TYPE;
v_emp emp%rowtype;
empno_no_found EXCEPTION;
v_count PLS_INTEGER;
begin
v_empno:=&员工编号;
select count() into v_count from emp where empno=v_empno;
if v_count<1 then
raise empno_no_found;—注册异常
end if;
select * into v_emp from emp where empno=v_empno;
dbms_output.put_line(‘员工编号：’||v_empno||‘姓名为’||v_emp.ename);
EXCEPTION
when empno_no_found then
raise_application_error(-20001,‘该编号的员工不存在！’);
end;

----------------------------------第九章--------------------------------------------
-----隐式游标：增删改查，赋值语句
declare
v_name varchar2(20);
begin
select ename into v_name from emp where empno=7934;
if sql%found then
dbms_output.put_line(‘找到’);
dbms_output.put_line(‘行数：’||sql%rowcount);–注意要用连接号！！！
end if;
if sql%isopen then
dbms_output.put_line(‘游标已打开’);
else
dbms_output.put_line(‘游标未打开’);
end if;
end;
------显式游标--------------------
----使用游标循环打印所有部门名称-------
declare
v_name dept.dname%type;
cursor cur_dept is select dname from dept;
begin
open cur_dept;
loop
fetch cur_dept into v_name;
exit when cur_dept%notfound;
dbms_output.put_line(‘部门名称’||v_name);
end loop;
close cur_dept;
end;
-----使用游标循环打印所有员工编号，员工姓名及工资------
declare
cursor cur_emp is select empno,ename,sal from emp order by sal;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open cur_emp;
loop
fetch cur_emp into v_empno,v_ename,v_sal;
exit when cur_emp%notfound;
dbms_output.put_line(v_empno||’ ‘||v_ename||’ ‘||v_sal);
end loop;
close cur_emp;
end;
-----使用”游标行“循环打印所有员工编号，员工姓名及工资------
declare
cursor cur_emp is select empno,ename,sal from emp order by sal;
r_emp cur_emp%rowtype;–或者用 r_emp emp%rowtype;
begin
open cur_emp;
dbms_output.put_line(‘编号 姓名 薪资’);
loop
fetch cur_emp into r_emp;
exit when cur_emp%notfound;
dbms_output.put_line(r_emp.empno||’ ‘||r_emp.ename||’ ‘||r_emp.sal);
end loop;
close cur_emp;
end;
------使用带参数的显式游标--------
declare
v_deptno emp.deptno%type;
cursor cur_emp(dno number) is select empno,ename,sal from emp order by sal;
r_emp cur_emp%rowtype;
begin
v_deptno:=’&部门编号’;
open cur_emp(v_deptno);
loop
fetch cur_emp into r_emp;
exit when cur_emp%notfound;
dbms_output.put_line(r_emp.empno||’ ‘||r_emp.ename||’ ‘||r_emp.sal);
end loop;
close cur_emp;
end;
------使用“循环游标”循环打印员工编号，姓名和工资
declare
cursor cur_emp is select empno,ename,sal from emp;
r_emp cur_emp%rowtype;
begin
dbms_output.put_line(‘编号 姓名 薪资’);
for r_emp in cur_emp loop
dbms_output.put_line(r_emp.empno||’ ‘||r_emp.ename||’ ‘||r_emp.sal);
end loop;
end;
--------使用游标更新活动集中的行--------------
declare
cursor cur_emp is select empno,ename,sal order by sal;
v_increase number:=0;
v_new_sal number;
begin
for r_emp in cur_emp loop
v_new_sal=r_emp.sal-v_increase;
update emp set sal=v_new_sal where current of cur_emp;
dbms_output.put_line(r_emp.empno||’ ‘||r_emp.ename||’ '||r_emp.sal);
v_increase:=v_increase+100;
end loop;
end;
--------REF游标和REF游标变量------------------

declare
type refcur is ref cursor;
ref_cur refcur;
r_emp emp%rowtype;
selection varchar2(1):=upper(substr(’&input’,1,1));
begin
if selection=‘E’ then
open ref_cur for select *from emp;
dbms_output.put_line(‘员工信息’);
loop
fetch ref_cur into r_emp;
exit when ref_cur%notfound;
dbms_output.put_line(r_emp.empno||’ ‘||r_emp.ename||’ ‘||r_emp.sal);
end loop;
else if selection=‘D’ then
open ref_cur for select *from dept;
dbms_output.put_line(‘部门信息=’);
loop
fetch ref_cur into r_dept;
exit when ref_cur%notfound;
dbms_output.put_line(r_dept.deptno||’ ‘||r_dept.dname||’ ');
end loop;
else
dbms_output.put_line(‘请输入员工信息（E）或部门信息（D）’);
return;
end if;
close ref_cur;
end;

--------------------------------第十章-----------------------------------------------
-----------创建无参数存储过程--------------
create or replace procedure sp_helloworld
as
begin
dbms_output.put_line(‘Hello World!’);
end sp_helloworld;
–调用
begin
sp_helloworld;
end;
------------创建带有输入参数的存储过程2---------
create or replace procedure sp_find_empval(v_no number)
as
v_ename varchar2(20);
v_sal number;
begin
select ename,sal into v_ename,v_sal from emp where empno=v_no;
dbms_output.put_line(‘雇员姓名’||v_ename||‘薪水：’||v_sal);
exception
when no_data_dound then dbms_output.put_line(‘没有此编号的雇员’);
end sp_find_empval;
–调用
declare
v_no number;
begin
v_no:=&no;
sp_find_empval(v_no);
end;
------------创建带有输出参数的存储过程1---------
create or replace procedure sp_getcount(v_count out number)
as
begin
select count(*) into v_count from dept;
end sp_getcount;

—调用
declare
v_count number;
begin
sp_getcount(v_count);
dbms_output.put_line(‘部门数量:’||v_count);
end;
----------------创建带有输入和输出参数的存储过程--------------
create or replace procedure sp_empnam_sal(v_no in number,v_name out varchar2,v_sal out varchar2)
as
begin
select ename,sal into v_name,v_sal from where empno=v_no;
end sp_emonam_sal;
—调用
declare
v_no number;
v_name varchar2(40);
v_sal number;
begin
v_no :=&no;
sp_empnam_sal(v_no,v_name,v_sal);
dbms_output.put_line(‘员工姓名’||v_name||‘薪水’||v_sal);
end;
-----------创建即是输入也是输出的参数------------
create or replace procedure sp(v_name in out varchar2)
as
v_count number;
begin
select count() into v_count from dept where dname=v_name;
if v_count>0 then
v_name:=‘存在’;
else
v_name:=‘不存在’;
end if;
end sp;
–调用
declare
v_name varchar2(40):=‘SALES’;
begin
sp(v_name);
dbms_output.put_line(v_name);
end;
----删除存储过程
drop procedure sp;
-------创建函数1无参函数，注意不能加（）-----------
create or replace function func_hello return varchar2
as
begin
return ‘hello world!’;
end;
—调用1，只能在sql和plsql块中用
select func_hello() from dual;
—调用2，只能在sql和plsql块中用
declare
v_info varchar2(100);
begiin
v_info:=func_hello;
dbms_output.put_line(‘返回：’||v_info);
end;
---------创建函数2有参函数-----------
create or replace function func(v_dno number) return varchar2
as
v_dname varchar2(50);
begin
select dname into v_dname from dept where deptno=v_dno;
return v_dname;
end;
—调用1
select func(30) from dual;
-------------------创建程序包---------------------
create or replace package pkg_dept
as
procedure sp(v_name in out varchar2);–声明过程
function func(v_dno number) return varchar2;–声明函数
end;
------------------创建程序包---------------------
create or replace package body pkg_dept
as
procedure sp(v_name in out varchar2)
as
v_count number;
begin
select count() into v_count from dept where dname=v_name;
if v_count>0 then
v_name:=‘存在’;
else
v_name:=‘不存在’;
end if;
end;
function func(v_dno number) return varchar2
as
v_dname varchar2(50);
begin
select dname into v_dname from dept where deptno=v_dno;
return v_dname;
end func;
end;
------------------包的使用-----------------
---------调用包中的存储过程
declare
v_name varchar2(40):=‘SALES’;
begin
pkg_dept.sp(v_name);
dbms_output.put_line(v_name);
end;
---------调用包中的函数
select pkg_dept.func(30) from dual;

------------包的删除-----------------
—只删除程序包主体而不删除包规范
drop package body pkg_dept;
—将程序包全部删除
drop package pkg_dept;
----------------------第十一章 触发器-----------------------
–触发器是当特定事件出现时自动执行的存储过程
-------DML-before 行集触发器1------------------
create or replace trigger tr_emp_addsal
before update of sal on emp for each row
declare
v_scale number;
begin
v_scale:=(:new.sal-:old.sal)/:old.sal;
if v_scale>0.1 then
:new.sal:=:old.sal*1.1;
dbms_output.put_line(‘对不起加薪不能超过10%，薪水更新成：’||:new.sal);
end if;
end;
select *from emp;
update emp set sal=sal+50 where empno=7369;
update emp set sal=sal+200 where empno=7369;

------------DML-after 行级触发器2---------------
—1、创建一个表 存放工资变动情况
create table t_emp_sal_change(
time1 date,
empno number(4),
oldsal number(7,2),
newsal number(7,2)
);
—2、创建触发器
create or replace trigger tr_emp1_aft_row
after update of sal on emp for each row
begin
insert into t_emp_sal_change values(sysdate,:old.empno,:old.sal,:new.sal);
end;
-----测试
update emp set sal=200 where empno=7777;
select *from t_emp_sal_change;
select *from emp;
------------语句级触发器-----------------------
对emp表进行增删改查操作，添加约束
create or replace trigger tr_emp_bef
before insert or delete or update on emp
begin
if user<>‘SCOTT’ then
dbms_output.put_line(‘你无权修改emp表中的数据:’);
raise_application_error(-20001,‘你无权修改emp表中的数据:’);
end if;
end;
-----------在sqlplus窗口以其他用户登录，进行测试
update scott.emp set sal=600 where empno=7369;
-------DML-after-语句级触发器-----------
-----1、创建表
create table t_emp_dml
(
who varchar2(10),
when date,
operater varchar2(10)
);
------2、创建触发器
create or replace trigger tr_emp_bef
after insert or delete or update on emp
begin
case
when inserting then insert into t_emp_dml values(user,sysdate,‘inserting’);
when updating then insert into t_emp_dml values(user,sysdate,‘updating’);—是values，不要写错
when deleting then insert into t_emp_dml values(user,sysdate,‘deleting’);
end case;
end;
--------3、测试
update emp set sal=600 where empno=‘7777’;
delete from emp where empno=‘7521’;
select from t_emp_dml;
----------instead of 触发器----------------
—1、创建视图
create or replace view v_dept_emp
as
select d.deptno,dname,empno,ename from dept d,emp e where d.deptno=e.deptno;
–查看视图
select * from v_dept_emp;
—2、创建触发器
create or replace trigger tr_v_dept_emp_insteadof
instead of insert
on v_dept_emp
for each row
declare v_temp pls_integer;
begin
select count() into v_temp from dept where deptno=:new.deptno;
if v_temp=0 then
insert into dept(deptno,dname)values(:new.deptno,:new.dname);
end if;

select count(*) into v_temp from emp where empno=:new.empno;
if v_temp=0 then
insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno)
end if;
end;
----3、测试触发器
insert into v_dept_emp values(66,‘caiwu’,7777,‘wangwu’);
select *from emp where empno=7777;
select *from dept where deptno=66;
-------------系统触发器-------------------
create or replace trigger tr_emp1_ddl
before drop on schema
begin
if ora_dict_obj_name=‘EMP1’ then
raise_application_error(-20003,‘表emp1不允许删除！’);
end if;
end;
-----测试
create table emp1 as select *from emp;
select *from emp1;
drop table emp1;
----------------查看用户触发器的信息--------------
select *from user_triggers where trigger_name=‘TR_T_STUDENT_DDL’;
-------------启用和禁用触发器-----------
alter trigger tr_emp1_ddl disable;
alter trigger tr_emp1_ddl enable;
----------------------oracle 综合设计题-------------------------

1、sqlplus system/manager@orcl as sysdba
2、create table renyongqi ( ename varchar2(20));
3、create user renyq identified by 123;
4、grant create table,create session,resource to renyq;
6、
drop table clerk;
create table clerk(
no number ,
name varchar2(10) not null,
sex varchar2(5) default ‘男’,
birthday date,
address varchar2(10),
salary number(20),
constraint fk check(sex='男’or sex=‘女’),
constraint uk check(salary>0),
constraint s1 unique(no)
);
drop table item;
create table item(
itemio number(20) primary key,
itemname varchar2(20) not null,
startdate date,
enddate date,
budget number(20),
constraint t1 unique(itemname)
);
7、
create sequence ss start with 1000 increment by 1;
segment creation immediate;–取消使用“延迟短”技术
insert into item values(ss.nextval,‘小型监控系统’,‘6-8月-2009’,‘1-1月-2010’,100000);
insert into item values(ss.nextval,‘办公网络安全’,‘30-12月-2009’,‘1-10月-2010’,450000);
insert into item values(ss.nextval,‘电子购物广场’,‘30-12月-2009’,‘31-12月-2010’,12000);
select * from item;
insert into clerk values(1,‘王晓明’,‘女’,‘3-1月-1980’,‘山东青岛’,8900);
insert into clerk values(2,‘王涵’,‘男’,‘12-6月-1978’,‘广东佛山’,760);
insert into clerk values(3,‘李有才’,‘男’,‘23-5月-1978’,‘上海’,7800);
insert into clerk values(4,‘张晓晓’,‘女’,‘7-9月-1982’,‘山东济南’,4500);
8、
create table clerk_item(
empno number(20),
itemno number(20),
constraint m1 foreign key(empno) references clerk(no),
constraint m2 foreign key(itemno) references item(itemio)
);
insert into clerk_item values(1,1001);
insert into clerk_item values(1,1002);
insert into clerk_item values(1,1003);
insert into clerk_item values(2,1002);
insert into clerk_item values(2,1003);
insert into clerk_item values(3,1001);
insert into clerk_item values(3,1002);
insert into clerk_item values(4,1001);
select *from clerk_item;
2、
1）select 读者姓名 from 读者信息表 where 读者学号 not in(
select 读者学号 from 借阅表
);
2）update 借阅表 set 还书日期=还书日期+30 where 读者学号 =(
select 读者学号 from 读者信息表 where 所在系=‘计算机专业’
);
3)select 读者学号 from 读者信息表 where 累计借书 in(
select max(累计借书) from 读者信息表
);