本章学习要点：
数据泵技术导入导出
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 分为三个部分，声明部分、可执行部分和异常处理部分 语法: 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;
