oracle笔记(oracle10数据库基础教程(孙风栋 著))

Oracle复习资料

Pl/sql

例一：输入一个员工号，修改该员工的工资。
如果该员工为10号部门，则工资加100；
如果部门号为20，则加150；
如果部门号为30，则工资加200；
其余部门号加300.

解一：（用if语句）
Declare
V_deptNO emp.deptNO%type ;
V_empNO emp.empNO%type ;
Increment number(4) ;

Begin
V_empNO := &x ;

Select depNO into v_deptNO
From emp
Where empNO = v_empNO ;

If v_deptNO = 10 then increment := 100 ;
Else if v_deptNO = 20 then increment := 150 ;
Else if v_deptNO = 30 then increment := 200 ;
Else v_increment := 300 ;
End if ;

Update emp
Set sal = sal + increment
Where emp.empNO = v_empNO ;

commit ;
end

解二（用游标）
Declare
cursor c_emp
is
select *
from emp for update ;

increment number(3) ;
begin
for v_emp in c_emp loop
case v_emp.deptNO
when 10 then increment := 100 ;
when 20 then increment := 150 ;
when 30 then increment := 200 ;
else increment := 300 ;
end case ;

update emp
set sal = sal + increment
where current of c_emp ; --current 表示当前游标的位置

end loop ;

commit ; --这个提交必须在游标循环外面
end


例二：根据输入的员工号，修改员工的工资。
如果员工的工资低于1000，则加200；
如果工资在1000到2000之间的，则工资增加150；
如果工资在2000到3000之间的，则增加100；
否则增加50。
解：（利用case语句）

declare
v_sal emp.sal%type ;
increment number(4) ;
v_empNO emp.empNO%type ;

begin
v_empNO := &x ;

select sal into v_sal
from emp
where emp.empNO = v_empNO ;

case
when v_sal<1000 then increment := 200 ;
when v_sal < 2000 then increment :=150 ;
when v_sal < 3000 then increment :=100 ;
else increment := 50 ;
end case

update emp
set sal = sal + increment
where emp.empNO = v_empNO ;
end


例三：创建一个存储过程，以部门号为参数 ，查询该部门的平均工资，
并输出该部门中比平均工资要高的的员工号码、名字
解：
create procedure show_emp ( p_deptNO emp.deptNO%type )
as
avgSal emp.sal%type --声明变量
begin
--首先算出平均值
select avg(sal) into avgSal
from emp
where emp.deptNO = p_deptNO ;

--输出结果
dbms_output.put_line ( p_deptNO || ' ' || 'average salary is ' || avgSal ) ;

--for循环中列出大于平均值的
for v_emp in (
select *
from emp
where emp.deptNO = p_deptNO
and emp.sal > avgSal
)
loop
d

bms_output.put_line( v_emp .empNO || v_emp .empName ) ;
end loop ;

--异常处理
exception
when NO_Data_Found
then dbms_output.put_line ('the department doesn't exists!');
end show_emp


例四：为emp创建一个触发器，禁止在周六和周日进行dml操作
解：
create trigger trg_emp_weekend
before insert or update or delete on emp
begin
if( to_char(sysDate , 'dy' , 'nls_date_language=american') in ( 'sat' , 'sun' ) )
then raise_application_error (-2000 , 'can't operate in weekend') ;
end if ;
end trg_emp_weekend ;

例五：为emp表创建一个触发器 ，
当插入新员工时显示新员工的员工号和姓名；
当更新员工工资的时候，显示修改前后员工工资 ；
当删除员工时，显示员工号和姓名。
解：
create trigger trg_emp_dml_row
before insert Oracle update Oracle delete on emp
begin
if inserting
then dbms_output .put_line (:new.empNO || :new .empName ) ;

else if updating
then dbms_output.put_line( :old.sal || :new.sal ) ;

else if deleting
then dbms_output .put_line ( :old.empNO || ' ' || :old .empName ) ;

end if ;
end trg_emp_dml_row ;


数据文件
1，为user表空间添加一个大小为10mb的数据文件
alter tableSpace users
add dataFile 'D:\**\**.dbf' size 10m

2,为temp表空间添加一个5mb临时数据文件
alter tableSpace temp
add tempFile 'D:\**\**.dbf' size 5m

自动增长的数据文件
alter tableSpace users
add dataFile 'D:\**\**.dbf ' size 5m
autoExtend on next 512k MaxSize 50m --每次自动扩展为512k 最大为50m
上面还可以是 MaxSize unLimited

修改数据文件大小
alter database dataFile 'D:\**\**.dbf' reSize 8m

在归档模式下，修改数据文件的可用性(数据文件的脱机和在在线)
alter database dataFile '***.dbf' offLine(onLine)

数据文件的重命名和移动(这要在数据文件脱机状态下进行的)
alter tableSpace users
reName dataFile 'D:\a.dbf' to 'F:\aa.dbf'

删除数据文件
alter tableSpace users
drop dataFile（tempFile） '**.dbf'
要注意的是删除数据文件和临时数据文件的同时，将会删除控制文件和数据字典中与该
数据文件或临时数据文件的相关信息，同时也会删除操作系统中对应的物理文件

查询数据文件的相关信息可以用下面的表或者视图
dba_data_files
dba_temp_files
dba_free_space
user_free_space
v$dataFile
V$tempFile

如何进入归档模式
首先关闭数据库 shutdown immediate
然后装载数据库 startup mount
然后查看当前实在归档模式还是非归档模式 archive log list
如果处于非归档模式的话，就 alter database archiveLog 变成归档模式
然后打开数据库 alter database open
如果要还原为非归档模式，就把archiveLog 改为 noArchiveLog

备份控制文件
alter database

backup controlFile to_char '**.bkp'
如果数据文件的添加、删除或重命名 ；
表空间的添加、删除；
表空间读写状态的改变；
以及添加或删除重做日志文件等都需要备份控制文件


删除控制文件
1，首先关闭数据库 2，在操作系统删除控制文件 3，重庆数据库

创建一个永久性表空间，区自动扩展，段采用自动管理方式
create tableSpace mySpace
dataFile 'D\**.dbf' size 50m
默认情况下就是区自动扩展和段自动管理
如果是区定制分配，段自动管理
extent management local uniform size 512k
如果是区自动扩展，段采用手动管理
segment space management manual
如果区定制分配，段手动管理
extent management local uniform size 512k
segment space management manual
从上面几句话可以看出规律：默认都是自动管理的，是不需要写什么句子的，而手动或定制才要写句子

创建临时表空间
create temporary tableSpace myTempSpace
tempFile '**.dbf' size 20m

创建撤销表空间
create undo tableSpace myUndoSpace
dataFile '**.dbf' size 20m

为表空间添加数据文件
alter tableSpace mySpace
add dataFile(tempFile) '**.dbf' size 20m

增加数据文件的大小
alter database dataFile '**.dbf' reSize 500m
自动增长 autoExtend onLine next 5m MaxSize 100m

表空间的备份(热备份)
alter tableSpace *** begin backup
直接复制**.dbf到你想要存放的地方

删除表空间
drop tableSpace ** including contents (and dataFile)(cascade constraints)

查看表空间的信息可以用以下视图或这数据表
V$tableSpace
dba_tableSpaces
dba_tableSpaces_groups