【Oracle】代码题汇总

一、结构篇

1)表空间

1,简单创建表空间
其中一个数据文件带有自动扩展功能，每次增加1000K一个数据文件自动扩展关闭。

create tablespace ts1
datafile 'd:/t1.dbf' size 5000k;

2,创建表空间，且自动扩展大小，每次增加1000k

create tablespace ts1
datafile 'd:/t1.dbf' size 5000k
autoextend on next 1000k;   //自动扩展大小，每次增加1000k

3,创建表空间 区管理 为自动分配的表空间

create tablespace ts1
datafile 'd:/t2.dbf' size 5000k ;//默认为自动分配

4,创建表空间 区管理 为指定分配

create tablespace ts1
datafile 'd:/t1.dbf' size 5000k
autoextend on next 1000k
extent management local
uniform size 128k;

5,创建表空间 区管理为指定分配，段管理为手动管理

create tablespace ts1
datafile 'd:/t1.dbf' size 5000k
autoextend on next 1000k
extent management local uniform size 128k
segment space management manual;

6,删除表空间

drop tablespace ts1 including contents and datafiles;

7,创建undo表空间

create undo tablespace ts2
datafile 'd:/t2.dbf' size 5000k;

8,创建临时表空间

create temporary tablespace ts2
datafile 'd:/t2.dbf' size 5000k;

9,创建大文件表空间

create bigfile tablespace ts2
datafile 'd:/t2.dbf' size 5000k;

10,三种增加表空间容量的方法
~1追加数据文件

alter tablespace ts1 
add datafile 'd:/t2.dbf' size 5000k;

~2修改其自动扩展性

alter database datafile 'd:/t1.dbf' autoextend on next 1000k;

~3修改已经存在的数据文件的大小

alter database datafile 'd:/t1.dbf' resize 9000k;

11,重命名表空间

alter tablespace ts1 rename to ts01;

12,重命名数据文件

alter tablespace ts1
rename 'd:/t1.dbf' to 'd:/t01.dbf';

13,如何把表table1移动到表空间users中

alter table table1 move tablespace users;

2)日志文件

1,追加一组日志文件

alter database add logfile
group 10('d:/t1.log','d:/t2.log')
size 5m;

2,追加一个member

alter database add logfile 
member 'd:/3.log' to group 10;

3,删除member

alter database drop logfile member 'd:/3.log';

4,删除组

alter database drop logfile group 10;

二，对象篇

1）分区表

1，list分区

create table book1(
bid number(4),
bookname varchar2(20),
bookpress varchar2(30),
booktime date )
partition by list(bookpress)
(partition part1 values('清华大学出版社')tablespace system,
partition part2 values('教育出版社')tablespace users);

操作：
插入记录

insert into book1 values('1','oracle','清华大学出版社'，to_date('20110102','yyyymmdd'));

增加分区
alter table book1
add partition part3 values(default) tablespace system;

2,range分区

create table book2(bid number(4), bookname varchar2(20),bookpress varchar2(30),booktime date)
partition by range(booktime)
(partition part1 values less than(to_date('20100101','yyyymmdd')) tablespace system,
partition part2 values less than(to_date('20120101','yyyymmdd'))tablespace users,
partition part3 values less than(maxvalue)tablespace users);

操作：
插入记录

insert into book2 values('1','oracle','清华大学出版社',to_date('20110102','yyyymmdd'));

3,分区分割

alter table book2 split partition part2
at(to_date('20110101','yyyymmdd'))
into(partition part2,partition part4);

4,删除分区

alter table book2 drop partition part4;

2)merge

//将insert和updata结合的方法，没有则insert，有则updata

create table xs1 as select * from xs;
truncate table xs1;
insert into xs1 select * from xs;
merge into xs a
using xs1 b
on(a.xh=b.xh)
when not matched then 
	insert(a.xh,a.xm,a.zym,a.xb) values(b.xh,b.xm,b.zym,b.xb)
when matched then 
	updata set a.xm=b.xm,a.zym=b.zym,a.xb=b.xb;

3)同义词

为表，索引，试图等模式对象定义别名

create public synonym emp for scott.emp;

4)序列

create sequence s1
	start with 20166283
	increment by 1  //递增（正数）、递减（负数）的间隔数值
	maxvalue 20166333
cycle

实现

set serveroutput on;
declare
i int;
begin
for i in 2..50
loop
insert into student(sno) values(s1.nextval);
end loop;
end;

三，PL/SQL篇

1)%type和%rowtype

%type精确到列的类型 v_empno emp.empno%type
%rowtype整个数据表的类型 v_emp scott.emp%rowtype

2)分支结构

1.if then elsif then else end if
要求：针对scott.emp表，计算7788号雇员的应交税金情况，
薪金>=3000,应缴税金为薪金的0.08,薪金在1500和3000之间，
应缴薪金的0.06，其它应缴0.04.

declare
v_sal  scott.emp.sal%type;
v_shui scott.emp.sal%type;
begin
select sal into v_sal from scott.emp where empno=7788;
if v_sal>=3000 then v_shui:= v_sal*0.08;
elsif v_sal>=1500 then v_shui:=v_sal*0.06;
else v_shui:=v_sal*0.04;
end if;
dbms_output.put_line('应缴税金'||v_shui);
end;

要求：向学生表中添加 记录，值为’007’ ‘Jame’ ‘计算机’ 45，并说明是否成功

declare
v_xm varchar2(8):='jame';
v_zy varchar2(10):='计算机';
v_zxf number(2):=45;
begin
insert into xs(xh,xm,zy,zxf)values('007',v_xm,v_zy,v_zxf);
if sql%found then dbms_output.put_line('操作成功');
else dbms_output.put_line('插入错误');
end if;
end;

2,case
~1,涉及表为Scott.emp，输入一个员工号，修改该员工的工资，如果该员工为10号部门（deptno），则工资加100；
若为20号部门，工资增加150，若为30号部门，工资加200，否则加300

declare
v_empno emp.empno%type;
v_deptno emp.deptno%type;
v_gongzi emp.sal%type;
begin 
v_empno:=&x;
select deptno into v_deptno from emp where empno=v_empno;
case v_deptno
	when 10 then v_gongzi:=100;
	when 20 then v_gongzi:=150;
	when 30 then v_gongzi:=200;
	else v_gongzi:=300;
end case;
update emp set sal=sal+v_gongzi where empno=v_empno;
if sql%found then dbms_output.put_line('更改成功');
end if;
end;

~2,搜索型

declare
chengji int(2);
begin
chengji:=&x;
case  
	when chengji>=90 then dbms_output.put_line('优秀');
	when chengji>=80 then dbms_output.put_line('良好');
	when chengji>=60 then dbms_output.put_line('及格');
	else dbms_output.put_line('不及格');
end case;
end;	

~3，嵌入到select

select xh,xm,xf,(case
when xf>50 then '合格'
else '不合格'
end) as 学业情况 from xs;

select bookno,bookname,bookdate,(case
when trunc(sysdate-bookdate)>60 then '已逾期'
when bookdate is null then '未借出'
else '未逾期'
end)as 借书情况,(case
when trunc(sysdate-bookdate)>60 then trunc(sysdate-bookdate)*0.1
else 0
end)as 缴费 from scott.emp;

3)循环结构
1,for

declare
	sum number:=0;
	i int;
begin
	for i in 1..100
loop
	sum:=i+sum;
end loop
dbms_output.put_line(sum);
end;

2,do_while

declare
	sum number:=0;
	i int:=1;
begin
loop 
	sum:=sum+i;
	i:=i+1;
exit when i>100
end loop;
dbms_output.put_line(sum);
end;

3,while

declare 
	sum number:=0;
	i int:=1;
begin
while(i<=100)
loop
	sum=i+sum;
	i=i+1;
end loop
dbms_output.put_line(sum);
end;

4)异常处理

查询名为smith的员工工资，如果该员工不存在，输出异常，如果匹配到多个重名的员工，输出异常

set serveroutput on;
declare 
v_sal emp.sal%type;
begin
select sal into v_sal from emp where name='smith';
dbms_output.put_line(v_sal);
exception
	when no_data_found then
	dbms_output.put_line('没有此员工');
	when too_many_rows then
	dbms_output.put_line('有重名的员工');
end;

5)游标

声明游标 cursor
打开游标 open
检索游标 fetch
关闭游标 close

1，简单操作

set serveroutput on;
declare
cursor c_1 is select xh from xs;
v_1 xs.xh%type;
begin
open c_1;
fetch c_1 into v_1;
dbms_output.put_line(v_1);
fetch c_1 into v_1;
dbms_output.put_line(v_1||' '||c_1%rowcount);
close c_1;
end;

set serveroutput on;
declare
cursor c_1(v_xb xs.xb%type) is select * from xs where xb=v_xb;
v_1 xs%rowtype;
begin
open c_1('男');
fetch c_1 into v_1;
dbms_output.put_line(v_1.xh);
fetch c_1 into v_1;
dbms_output.put_line(v_1.xm||' '||c_1%rowcount);
close c_1;
end;

2,游标遍历
~1while

declare
cursor c_1(v_xb xs.xb%type)is select *from xs where xb=v_xb;
v_1 c_1%rowtype;
begin
open c_1('男');
fetch c_1 into v_1;
while(c_1%found)
loop
dbms_output.put_line(v_1.xm);
fetch c_1 into v_1;
end loop;
close c_1;
end;

~2do_while

set serveroutput on;
declare
cursor c_1(v_xb xs.xb%type)is select * from xs where xb=v_xb;
v_1 c_1%rowtype;
begin
open c_1('男');
loop
fetch c_1 into v_1;
dbms_output.put_line(v_1.xm);
exit when c_1%notfound;
end loop;
close c_1;
end;

~3for

set serveroutput on;
declare
cursor c_1(v_xb xs.xb%type)is select * from xs where xb=v_xb;
v_1 c_1%rowtype;
begin
for v_1 in c_1('男')
loop
dbms_output.put_line(v_1.xm);
end loop;
end;

3,应用：统计并输出各部门的平均工资

create view t1
as
select deptno,avg(sal)avgsal from scott.emp group by detpno;

~1

set serveroutput on;
declare
cursor c_1 is select deptno,avgal,deptname from t1,scott.dept where scott.dept.deptno=t1.deptno;
v_1 c_1%rowtype;
begin
for v_1 in c_1
loop
dbms_output.put_line(v_1.deptno||' '||v_1.deptname||' '||v_1.avgal);
end loop;
end;

~2

set serveroutput on;
begin
for v_1 in (select deptno,deptname,avgsal from t1,scott.dept where scott.dept.deptno=t1.deptno)
loop
dbms_output.put_line(v_1.deptno||' '||v_1.deptname||' '||v_1.avgsal)
end loop;
end;

6)存储过程————类似于方法，先在主main前的声明写出函数体，直接在main内调用

头部
as|is
begin
exception
end

1,简单:无需变量
测试在scott用户下创建一存储过程update_emp，该过程用于将emp表中empno为7876的员工姓名修改为candy

create or replace procedure update_emp
as
begin
update scott.emp set ename='candy' where empno='7876';
end update_emp;

命令窗口：

declare
begin
	update_emp;
end;

2,需要变量（in，out，in out）

create or replace procedure update_emp(v_empno in scott.emp.empno%type)
as
begin
update scott.emp set ename='candy' where empno=v_empno;
end update_emp;

命令窗口:

declare
v_empno scott.emp.empno%type;
begin
v_empno:=&x;
update_emp(v_empno);
end;

测试在scott用户下计算机指定专业的总学分大于40的人数

create or replace procedure p1(v_zym in xs.zym%type,v_num out number)
as
begin
select count(zxf) into v_num from xs where zym=v_zym and zxf>=40;
end p1;

命令窗口：

set serveroutput on;
declare
v_zym xs.zym%type:='计算机';
v_num int;
begin
p1(v_zym,v_num);
dbms_output.put_line(v_num);
end;

创建一个存储过程，以部门号为参数，返回该部门的人数和平均工资

create or replace procedure p2(v_deptno in emp.empno%type,v_avgsal out emp.sal%type,v_num out number)
as
begin
select avg(sal),count(*) into v_avgsal,v_count from scott.emp where deptno=v_deptno;
exception
end p2;

命令窗口：

set serveroutput on;
declare
v_deptno emp.empno%type;
v_avgsal emp.sal%type;
v_num number;
begin
v_deptno:=&x;
p2(v_deptno,v_avgsal,v_num);
dbms_output.put_line(v_avgsal||' '||v_num);
end;

3,直接在存储过程中输出，则无需加out变量
以部门号为该存储过程的in类型的参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号，员工名

create or replace procedure p3(v_deptno in scott.emp.deptno%type)
as
v_avgsal scott.emp.sal%type;
begin
select avg(sal) into v_avgsal from scott.emp where emp.deptno=v_ deptno;
dbms_output.put_line(v_avgsal);
for v_1 in (select ename,eno from scott.emp where emp.deptno=v_deptno and sal>v_avgsal)
loop
dbms_output.put_line(v_1.eno||' '||v.1ename);
end loop;
end p3;

命令窗口：

set serveroutput on;
declare
v_deptno scott.emp.deptno%type;
begin
v_deptno:=&x;
p3(v_deptno);
end;

4,要用游标，在存储过程声明中用 is 声明
用存储过程进行模糊查找，如查找ename中包含’L’的雇员信息

create or replace procedure p4(name in emp.ename%type)
is
cursor c_1 is select * from emp where ename like '%'||name||'%';
begin 
for v_1 in c_1
loop
dbms_output.put_line(v_1.empno||v_1.ename||v_1.job||v_1.deptno);
end loop;
end p4;

命令窗口：

set serveroutput on;
seclare
name enmp.ename%type;
begin
name:=&x;
p4('L');
end;

7)触发器trigger

头部（before after +
insert delect update of 列名 on 表或视图
+for each row）
declare
begin
exception
end

1,简单：
增加一新表xs_1,表结构和表xs相同，用来存放从xs表中删除的记录

create table xs_1 as xs select * from xs;
truncate table xs_1;
create or replace  trigger t1
before delete on xs
for each row
declare
begin
insert into xs_1(xh,xm,zym,xb,zxf) values(:old.xh,:old.xm,:old.zym,:old.xb,:old.zxf);
end t1;

触发：被动触发commit

2，inserting，deleting，updating
监控用户对xs表的操作，要求：当xs表插入，更新，删除三种操作后再sql_info表中给出相应提示和操作时间

 create or replace trigger t2
after insert or update or delete on xs
for each row
declare
v_info sql.info.info%type;
begin
if inserting then info:='插入';
elsif deleting then info:='删除';
else info:='更新';
end if;
insert into sql_info values(v_info,sysdate);
end t2;

触发：被动触发commit

3,为emp表创建一个触发器，
当插入新员工时显示新员工的员工号，员工名，
当更新员工工资时，显示修改前后的员工工资，
当删除员工时，显示被删除员工的员工号，员工名

create or replace trigger t3
before insert or delete or update on scott.emp
for each row
declare
v_eno emp.eno%type;
v_ename emp.ename%type;
begin
if inserting then dbsm_output.put_line(:new.eno||' '||:new.ename);
elsif updating then dbms_output.put_line(:old.sal||' '||:new.sal);
else dbms_out put.put_line(:old.eno||' '||:old.ename);
end if;
exception
end t3;

触发：被动触发commit

4,针对scott.emp表，记录其相应操作的信息，具体如下：（解决方案一）
当执行插入操作时，统计操作后人员的人数
当执行更新工资操作时统计更新后员工的平均工资
当执行删除操作时，统计删除后各个部门剩余的人数

create or replace trigger t4
ater insert or update or delete on scott.emp
declare 
v_num number;
v_avgsal scott.emp.sal%type;
begin
if inserting then select count(*) into v_num from scott.emp;
dbms_output.put_line(v_num);
elsif updating then select avg(sal) into v_avgsal from scott.emp;
dbms_output.put_line(avgsal);
else for v_1 in (select deptno, count(*)num from scott.emp group by deptno)
loop 
dbms_output.put_line(v_1.deptno||' '||v_1.num)
end loop;
end if;
end t4;

5,提升：禁止,直接报异常并且禁止用户当前的操作 raise_application_error( , )
创建触发器，作用为禁止在休息日（周六，周天）修改雇员信息(删除和修改)

create or replace  trigger t5
before delete or update on scott.emp
declare
begin
if to_char(sysdate,'day') in ('星期六','星期日') then raise_application_error(-20012,'不能再周末更改员工信息');
end if
exception
end t5;

6,细化到列 of
创建一个触发器，在修改dept表的部门号后，同时更新emp表中相应的员工的部门号

create or replace trigger t6
after update of deptno on scott.dept
for each row
declare
begin
update scott.emp set deptno=:new.edeptno where deptno=old.deptno;
end t6;