Oracle数据库学习

Oracle数据库学习第四章

本章学习要点：

• 数据泵技术导入导出

• PL/SQL 块

• 异常

数据泵技术导入导出

注：适用范围：11g不能导出空表，跨版本到导入导出
1）电脑上创建一个导出的存放目录如：c:\mydb
2 ) 连接Oracle数据库(必须以SYSTEM用户连接，不然权限不足无法创建目录)
3 ) 创建一个操作目录
–CREATE DIRECTORY 目录名 as ‘指定文件存放的位置’
create directory bak as ‘c:\mydb’;
–查看目录
select * from dba_directories（DBA只有系统管理员或者超级管理员才能访问）
4）授予用户操作dump_dir目录的权限
–GRANT READ,WRITE ON DIRECTORY导入导出文件夹TO 用户名；
grant read,write on directory bak to scott;
5）使用命令expdp导出数据, 使用命令impdp导入数据
–expdp 用户名/密码 directory=导出导入文件夹 dumpfile= 导出文件名.dmp
–logfile=导出日志.log(可写可不写) SCHEMAS=用户名 version =版本号
–【注意:尾部不能写”;”】
1)按用户导
expdp scott/aaa@ORCL directory=bak dumpfile=scott.dmp schemas=scott

2)导整个数据库
expdp system/system@ORCL directory=bak dumpfile=full.dmp full=y

3)按表名导
expdp scott/aaa@ORCL directory=bak dumpfile=table.dmp tables=emp,dept

4)按表空间导
expdp system/system@ORCL directory=bak dumpfile=tablespace1.dmp tablespaces=USER1_TABLESPACE

还原数据
– impdp 用户名/密码 directory=导出导入文件夹 dumpfile= 导入文件名.dmp logfile=导入日志.log REMAP_SCHEMA=导出用户名 :导入用户名
–REMAP_TABLESPACE=导出表空间:导入表空间 version=版本号
注： REMAP_SCHEMA导出的用户名和导入的用户名不相同时用
REMAP_SCHEMA把用户A的数据导入到用户BREMAP_SCHEMA=A:B
REMAP_TABLESPACE和REMAP_SCHEMA功能一样只是REMAP_TABLESPACE实现了不同表空间的之间的导入导出

1)导整个数据库
impdp system/system@ORCL directory=bak dumpfile=full.dmp full=y

2)按表空间导
impdp system/system@ORCL directory=bak dumpfile=tablespace1.dmp tablespaces=USER1_TABLESPACE

3)按用户导
impdp scott/aaa@ORCL directory=bak dumpfile=scott.dmp schemas=scott

4)改变表的owner
impdp system/system@ORCL directory=bak dumpfile=table.dmp remap_schema=scott:sqb

6）使用exp/imp 命令导入
EXP: 导出
有三种主要的方式（完全、用户、表）
1、完全：
exp system/system@ORCL file=c:\DB\full.dmp full=y

2、用户模式：
exp scott/aaa@ORCL file=c:\DB\scott.dmp

3、表模式：
exp scott/aaa@ORCL file=c:\DB\table.dmp tables=(dept,emp)

IMP: 导入
具有三种模式（完全、用户、表）
1、完全：
imp system/system@ORCL file=c:\DB\full.dmp full=y

2、用户模式： (拥有DBA ==>sys/system)
imp system/system@ORCL file=c:\DB\scott.dmp fromuser=scott touser=sqb

3、表模式：
imp scott/aaa@ORCL file=c:\DB\table.dmp tables=(dept,emp)

–exp/imp 与 expdp/impdp 区别
EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。

EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。

IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。

PL/SQL 块

PL/SQL 块是构成 PL/SQL 程序的基本单元
将逻辑上相关的声明和语句组合在一起
PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
语法:
declare
–声明部分
begin
–执行部分
–execption 异常处理
end;

在声明部分赋值的语法：
变量名/常量名 [constant] 数据类型 [not null]值不为空 [:=] default(初始值) 如：
declare
v_name varchar2(20):=&name; –声明变量
v_age number(3,0):=&age; –从键盘输入
v_money number(5,2):=152.56; –给变量赋值
NUMBERS constant number(4):=1200; –常量
这里的&name和&age 指的是从键盘输入
“:=”相当于JAVA中的“=”
begin
–赋值
–v_name:=’张三’;
–v_age:=20;

–重新赋值 (常量不可以再赋值的!不能修改值)
– NUMBERS:=1500;

–显示结果（”||”相当于JAVA中的“+”）
dbms_output.put_line(v_name||’->’||v_age||’->’||v_money||’–>’||NUMBERS);
end;

属性类型
(1) %type 提供某个变量或数据库表列的数据类型
select * from dept;

dept.deptno%type 等同与 number(2);
declare
v_deptno dept.deptno%type:=&no;——–这里是指键盘输入的类型与dept表中deptno的类型相同
v_dname dept.dname%type :=&dname;———-这里是指键盘输入的类型与dept表中dname的类型相同
begin
dbms_output.put_line(v_deptno||’——>’||v_dname);
end;
(2) %rowtype 提供表示表中一行的记录类型
查询SCOTT用户的职位,以及他的上司
select job,mgr from emp where ename=’SCOTT’;

declare
v_emp emp%rowtype; – 一行的记录数据类型
begin
select job,mgr into v_emp.job,v_emp.mgr from emp where ename=’SCOTT’;
dbms_output.put_line(v_emp.job||’==>’||v_emp.mgr);
end;
使用属性类型的优点：
不需要知道被引用的表列的具体类型
如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变

（3）控制结构

<1>条件控制
if 语句语法:
IF condition THEN
Statements
END IF;
–查询SCOTT员工的工资>3000,获得职位;
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=’SCOTT’;
if(v_sal>=3000) then
dbms_output.put_line(‘OK!’);
end if;
end;

if..else语法:
IF condition THEN
Statements1
ELSE
Statements2
END IF;

declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=’SCOTT’;
if(v_sal>3000) then
dbms_output.put_line(‘OK!’);
else
dbms_output.put_line(‘NO!’);
end if;
end;

IF-THEN-ELSIF语句
IF condition1 THEN
Statements1
ELSIF condition2 THEN
Statements2
ELSE
Statements3
END IF;

–查询员工表的职位英文对应的中文
select job from emp;

declare
v_job emp.job%type:=&job;
begin
if(v_job=’CLERK’) then –>条件=
dbms_output.put_line(‘职员’);
elsif(v_job=’SALESMAN’) then
dbms_output.put_line(‘销售员’);
elsif(v_job=’MANAGER’) then
dbms_output.put_line(‘经理’);
elsif(v_job=’ANALYST’) then
dbms_output.put_line(‘分析师’);
else
dbms_output.put_line(‘董事’);
end if;
end;

CASE 语句
/*CASE variable
WHEN value1 THEN statements1;
WHEN value2 THEN statements2;
……
WHEN valuen THEN statementsn;
[ELSE else_statements;]
END CASE*/

declare
v_score number := &数字;
begin
case v_score
when 1 then dbms_output.put_line(‘星期一’);
when 2 then dbms_output.put_line(‘星期二’);
when 3 then dbms_output.put_line(‘星期三’);
when 4 then dbms_output.put_line(‘星期四’);
when 5 then dbms_output.put_line(‘星期五’);
when 6 then dbms_output.put_line(‘星期六’);
when 7 then dbms_output.put_line(‘星期日’);
else dbms_output.put_line(‘未知’);
end case;
end;

<2>循环控制
LOOP 循环 –LOOP循环是最简单的一种循环。需使用 EXIT 语句来退出循环。
LOOP
exit [when 条件] –退出循环
statements;
END LOOP;

–1-10数字
declare
v_number number:=1 ; –初始值
begin
loop
dbms_output.put_line(v_number); –输出值
v_number:=v_number+1; –改变初值
–退出循环
exit when v_number>10;
end loop;
end;

WHILE 循环
WHILE condition LOOP
statements;
END LOOP;

– 1-20的偶数
declare
v_number number:=2;
begin
while v_number<21 loop
dbms_output.put_line(v_number); –输出值
v_number:=v_number+2; –改变初值
end loop;
end;

FOR 循环
FOR loop_count IN [REVERSE] lower_bound..height_bound LOOP
statements;
END LOOP;
–for(int i=0;i<=10;i++){}
begin
for v_numbers in reverse 1..10 loop
dbms_output.put_line(v_numbers); –输出值
end loop;
end;
其中reverse为倒序输出
loop_count
–> 循环变量

lower_bound
–>循环次数最小值

height_bound
–>
循环次数最大值

补充：
drop table 表名 purge; purge为清空缓存

<3>顺序控制
GOTO 语句 –exit,continue 11g ; 如果是9i,10g不能用continue!

begin
for v_numbers in 1..10 loop

if(v_numbers=5)then
–exit; –相当于java的break;
continue;
end if;
dbms_output.put_line(v_numbers); –输出值
end loop;
end;

NULL 语句
在PL/SQL 程序中，NULL语句是一个可执行语句，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符或不执行任何操作的空语句，
可以使某些语句变得有意义，提高程序的可读性，保证其他语句结构的完整性和正确性。

DECLARE
v_num varchar2(20);
BEGIN
IF v_num IS NULL THEN
GOTO labelPrint; 跳转
END IF;
<>
NULL; –不需要处理任何数据。
END;

–查询某一个用户的工资<=3000的津贴
select sal,comm from emp where ename=’SCOTT’

select * from emp

declare
v_ename emp.ename%type:=&name;
v_emp emp%rowtype;
begin
select sal,comm into v_emp.sal,v_emp.comm from emp where ename=v_ename;
if(v_emp.sal<=3000) then
dbms_output.put_line(v_emp.comm);
else
null;
end if;
end;

异常

1、什么是异常：
在PL/SQL中的一个警告或错误的情形都可被称为异常。包括编译时错误(PLS)和运行时错误(ORA)。
一个异常通常包含一个错误代码和错误文本，分别指示异常的编号和具体错误信息

2、常用的函数和过程：
SQLCODE: 返回错误代码，没有错误时返回0
SQLERRM:返回错误消息.

3、异常的分类：
系统预定义异常
未预定义的异常
自定义异常

4、异常的结构：
①　系统预定义异常：
BEGIN
…….
EXCEPTION
WHEN 异常类型1[OR 异常类型2] THEN
……异常处理代码
WHEN 异常类型3[OR 异常类型4] THEN
……异常处理代码
WHEN OTHERS THEN
…..异常处理代码
END;

5、异常处理:
1)预定义异常 (21)
–系统异常：系统异常是有oracle预先定义好的异常类型，当发生这种异常是系统会自动触发
NO_DATA_FOUND 使用 select into未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS 执行 select into时，结果集超过一行
ZERO_DIVIDE 除数为 0

select ename from emp where deptno=10 and sal>1500;
查询部门的用户信息

declare
v_ename emp.ename%type;
begin
–select ename into v_ename from emp where deptno=12 and sal<1500;
select ename into v_ename from emp where deptno=10 and sal>1500;
dbms_output.put_line(v_ename);
–处理异常
exception
when NO_DATA_FOUND then
dbms_output.put_line(‘没有您要找的数据!’);
when TOO_MANY_ROWS then
dbms_output.put_line(‘返回多条数据!’);
when others then
dbms_output.put_line(sqlcode||’=>’||sqlerrm);
end;

②　未预定义异常
由PL/SQL或SQL触发，没有与之相关联的预定义异常名称。
两个处理方法
A. 使用包含WHEN …OTHERS字句处理异常程序，使用SQLCODE或SQLERRM捕获错误
– B.把用户自定义异常与相应的错误代码关联，使用用户自定义异常来捕获、处理错误,
–使用PRAGMA EXCEPTION_INIT(exce_name,error_code),其中error_code的范围是：-1 ~ -12999

–删除部门表 delete from dept where deptno=10;
select *from dept;
select * from emp where deptno=10;
delete from dept where deptno=10;

declare
–声明变量
v_deptno dept.deptno%type:=&no;
–声明异常;类型
ex1 exception;
–指定异常关联错误编码 (-1 ~ -12999)
pragma exception_init(ex1,-2292);
begin
delete from dept where deptno=v_deptno;
–处理异常
exception
when ex1 then
dbms_output.put_line(v_deptno||’部门有员工不能删除!’);
end;

③　自定义异常：是由用户根据自己的业务需要定义的异常。
查询SCOOT用户的工资不在3500-5000之间,就提示加工资!
select sal from emp where ename=’SCOTT’;

declare
–声明变量
v_sal emp.sal%type;
–(1)声明异常类型
ex1 exception;
begin
select sal into v_sal from emp where ename=’SCOTT’;
–判断
if( not(v_sal>=3500 and v_sal<=5000)) then
raise ex1; –(2)抛出异常
end if;

–(3)处理异常
exception
when ex1 then
dbms_output.put_line(‘SCOTT用户要求加薪!’);
end;

用户自定义错误消息：
使用RAISE_APPLICATION_ERROR过程定制，
–格式RAISE_APPLICATION_ERROR(error_no IN NUMBER，error_message IN VARCHAR2[,keep_errors IN BOOLEAN])
—error_no: -20000~ -20999之间的任意负整数
—error_message:错误提示消息，最大不能超过512个字符
—keep_errors:可选项，如果keep_errors为TRUE，则这个新的错误将加在已产生的错误列表之后。如果keep_errors为FALSE，则这个新错误将代替当前的错误列表。默认为FALSE

更新某个员工的津贴
select * from emp where ename=’SCOTT’
–
begin
update emp set comm = 100 where ename=’SCOTT1’;
if sql%notfound then
raise_application_error(-20012,’update failure!’);
end if;
end;