Oracle数据库迁移培训FINAL.ppt

《Oracle数据库迁移培训FINAL.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库迁移培训FINAL.ppt（61页珍藏版）》请在三一办公上搜索。

1、Oracle 数据迁移,Oracle存储过程简介Informix转Oracle修改要点前台修改后台修改研发中心测试环境存储过程测试方法,存储过程的建立存储过程的语法结构存储过程的控制语句存储过程的开发存储过程的运行存储过程的调试Informix与oracle存储过程的差异函数包,Oracle存储过程简介,存储过程的建立编写后缀名为sql的文件，一个存储过程一个文件。过程格式：CREATE OR REPLACE PROCEDURE 存储过程名字(参数1 IN NUMBER,参数2 IN NUMBER)IS 变量1 INTEGER:=0;变量2 DATE;BEGIN END 存储过程名字;,Ora

2、cle存储过程简介,创建一个简单存储过程的方式1.SQL*Plus并且从SQL*Plus登录到你的数据库;打开test.sql文件.2.在SQL命令提示符下输入以下命令：SQLskeleton 注释:(SQL*Plus装载stest.sql文件的内容到SQL*Plus缓冲区,并且执行SQL*Plus语句;SQL*Plus会通知你存储过程已经被成功地创建)3.写一个存储过程,实例:CREATE OR REPLACE PROCEDURE test IS BEGIN DBMS_OUTPUT.PUT_LINE(HelloWorld!);END;,Oracle存储过程简介,运行查看信息1.SQLEXEC

3、UTE skeleton;注释(SQL*Plus输出一下信息确信存储过程成功执 即PL/SQLproceduresuccessfullycompleted).2.在SQL*Plus命令行提示符,键入:SQLSET SERVEROUTPUT ON 再次敲入SQLEXECUTE skeleton即可.注释:查看存储过程中的打印语句信息,实行以上命令.,Oracle存储过程简介,删除一个存储过程1.在SQL命令提示符下输入以下命令：命令:SQLDROP PROCEDURE 存储过程名;存储过程的注释-注释一行,Oracle存储过程简介,存储过程入参与返回值create or replace proc

4、edure runbyparmeters(isal in emp.sal%type,sname out varchar,sjob in out varchar)as icount number;begin if icount=1 then.else.end if;end;,Oracle存储过程简介,存储过程变量定义、常用变量类型realsal emp.sal%type;-与emp表中的sal字段类型相同realname varchar2(40);realjob varchar2(40);Price number(5,2);-543.21Product_id interger;注释:同一存储过程

5、中，变量名最好不要重复。,Oracle存储过程简介,存储过程赋值语句:=realjob:=work;Product_id:=100001;realname:=张三;Price:=3.1415;this_day:=TODAY;,Oracle存储过程简介,存储过程变量先声明且必须声明才能使用。Begin end 块外声明的变量影响全局。Begin end 块内声明的变量影响本Begin end。变量声明必须在存储过程开头或者Begin end 块的开头部分,Oracle存储过程简介,存储过程操作符+-*/|合并 如：sp_str1=“ABC”|”DEF”,则：sp_str1=“ABCDEF”,Or

6、acle存储过程简介,存储过程异常控制Exception when too_many_rows then DBMS_OUTPUT.PUT_LINE(返回值多于1行);when others then DBMS_OUTPUT.PUT_LINE(在RUNBYPARMETERS过程中出错！);,Oracle存储过程简介,存储过程结构块BEGINBEGIN 第一步处理；END;BEGIN 第二步处理；END;BEGIN 第三步处理；END;END;,Oracle存储过程简介,存储过程游标带参数的游标CURSOR C_USER(C_ID NUMBER)IS SELECT NAME FROM USER W

7、HERE TYPEID=C_ID NUMBER;OPEN C_USER(变量值);LOOP FETCH C_USER INTO V_NAME;EXIT WHEN C_USER%NOTFOUND;END LOOP;CLOSE C_USER;,Oracle存储过程简介,存储过程游标不带参数的游标CURSOR C_USER IS SELECT NAME FROM USER WHERE TYPEID=C_ID NUMBER;OPEN C_USER;LOOP FETCH C_USER INTO V_NAME;EXIT WHEN C_USER%NOTFOUND;END LOOP;CLOSE C_USER;

8、,Oracle存储过程简介,存储过程LOOP循环 Counter:=0;LOOP counter:=counter+1;EXIT WHEN counter=5;END LOOP;,Oracle存储过程简介,存储过程for循环方式一:BEGIN FOR cur_result in cur(结果集)LOOP BEGIN V_SUM:=cur_result.列名1+cur_result.列名2;END;END LOOP;END;,Oracle存储过程简介,存储过程for循环方式二:BEGIN FOR cur_result REVERSE 1.5 LOOP BEGIN DBMS_OUTPUT.PUT_

9、LINE(cur_result);END;END LOOP;END;,Oracle存储过程简介,存储过程while循环Counter:=0;WHILE counter 6 LOOP counter:=counter+1;END LOOP;,Oracle存储过程简介,存储过程if控制IF str1 str2 thenresult:=1;ELSIF str2 str1 THENresult:=-1;ELSEresult:=0;END IF;,Oracle存储过程简介,存储过程if条件表达式比较符，=，=，!=AND，OR，NOT(NOT)BETWEEN AND(NOT)IN(,)IS(NOT)NU

10、LL(NOT)LIKE,Oracle存储过程简介,%type定义方法在pl/sql中可以将变量和常量声明为内建或用户定义的数据类型,以引用一个列名,同时继承他的数据类型和大小.注:v_a number(5):=10;v_b v_a%type:=15;v_c v_a%type;,Oracle存储过程简介,存储过程调用方式（种方式）Declare realsal emp.sal%type;realname varchar(40);realjob varchar(40);BEGIN realsal:=1100;realname:=;realjob:=CLERK;（1）runbyparmeters(r

11、ealsal,realname,realjob);必须按顺序（2）runbyparmeters(sname=realname,isal=realsal,sjob=realjob);-不按顺序 DBMS_OUTPUT.PUT_LINE(REALNAME|REALJOB);(输出模式)END;,Oracle存储过程简介,函数结构 CREATE OR REPLACE FUNCTION 函数名(参数1 IN NUMBER,参数2 IN NUMBER)RETURN 类型 IS|ASBEGIN FUNCTION _BODYEND;注:函数与存储过程相似,唯一区别函数必须向调用他的语句返回一个值,它与存储过

12、合起来被称为存储子程序,从某种意义讲为小程序.,Oracle存储过程简介,包头和包体结构 CREATE OR REPLACE PACKAGE 包名 IS|AS PACKAGE _SPECIFICATION END包名;CREATE OR REPLACE PACKAGE BODY 包名 IS|AS PACKAGE 过程 END包名;注:PACKAGE _SPECIFICATION 是指包的用户使用的过程和函数的列表.PACKAGE 过程 是指包的用户可以使用的存储过程和函数的列表(包含变量,类型,定义和游标)包主要分为:规范和包体.它的用途主要是把存储过程和函数组织到包中.通过PL/SQL代码模

13、块化,构 建其他编程人员重用的代码库.,Oracle存储过程简介,Oracle 数据迁移修改要点,前台SQL的修改,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：first（存储过程适用）对于first语句，如果不包含排序，例如select frist 1*from tabx，可以修改为 select*from tabx where rownum=1；如果包含排序，select frist 1*from tabx order by pid，则只能修改为子查询的形式，在子查询内部排序，然后在外部增加rownum条件，例如select*from(select*from tabx or

14、der by pid)where rownum=1,关键字：outer（存储过程适用）当只有1个outer时，需要在WHERE子句中这个表所有字段的后面加上(+)标识。例如：查询所有团队，及其所辖26职级的人：Informix:select*from t50_tdept b,outer(t01_psn a)where a.tid=b.tid and a.t01actrank=26 Oracle:select*from t01_psn a,t50_tdept b where a.tid(+)=b.tid and a.t01actrank(+)=26 当有2个或多个outer时，需要写成标准的ou

15、ter join 的形式，例如informix：select*from filelines t1,outer(filelines t2),outer(pathlines t3）where t1.lines=t2.lines and t1.path=t3.pathOracle:select*from filelines t1 left outer join filelines t2 on t1.lines=t2.lines left outer join pathlines t3 on t1.path=t3.path,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：group by（

16、存储过程适用）在Informix中，GROUP BY语句可以写成如下形式：GROUP BY 1,2,3。但在Oracle中，这种语法不被支持，只能写成GROUP BY COL_NAME1,COL_NAME2,COL_NAME3。注：order by？,Oracle 数据迁移修改要点（前台SQL的修改）,可以使用！如：order by 1,2,3,关键字：insert（存储过程适用）如果向serial中包含的表插入数据，则需要修改其SQL语句，增加插入原serial类型的列，值为序列的nextval。例如insert into cbps_data(serno,.)values(s_cbps_da

17、ta.nextval,.)自增长字段：s_(TableName).nextval,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：date（存储过程适用）找到SQL语句中date类型字段，将SQL语句中其对应的值，从2010-03-01改为to_date(2010-03-01,yyyy-mm-dd)的形式,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：today将today 替换为 trunc(sysdate,dd)关键字：current将current 替换为 sysdate,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：select,update,d

18、elete,;将sql语句句末的分号;删除关键字：where,=将where子句中出现的=全部替换为=,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：自增长字段（存储过程适用）将sql语句中使用的截断字符串的mio_item_code1=B修改为：substr(mio_item_code，0，1)=B,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：into temp遇到sql语句中有into temp建临时表时需要预先建表，可报给数据库迁移人员，统一建立关键字：create.*temp,drop1、删除建临时表的语句，若需创建临时表，可报给数据库迁移人员，统一建立2

19、、检查是否有删除临时表一并删除,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：rowid修改者只需将出现rowid的地方记录并上报，由程序组统一处理：Oracle不支持rowid保留字，实际上对于现有核心系统，如果使用rowid会对分片产生影响，因此原则上也应杜绝rowid的使用，对于此情况需要统一报架构组，对应用逻辑进行评估、调整。,Oracle 数据迁移修改要点（前台SQL的修改）,关键字：!=，=(存储过程适用)Oracle对sql语句中字段值为空字符处理时只会判断是否为NULL，所以在修改时凡是出现!=或或=时需要修改：将!=或替换为is not null,=替换为is

20、null,Oracle 数据迁移修改要点（前台SQL的修改）,后台的修改存储过程,Oracle 数据迁移修改要点,为使存储过程在Oracle数据库中方便编译调试，把原存储过程中的create改为create or replace注：对于返回单个值的存储过程，建议移植为函数,Oracle 数据迁移修改要点（后台的修改-存储过程）,Oracle存储过程中的入参不需要定义参数的长度，在迁移过程中需要将原入参的长度定义部分删除。Oracle存储过程中，char 类型字段统一改为varchar2类型,Oracle 数据迁移修改要点（后台的修改-存储过程）,Oracle存储过程改为函数并且返回单个参数时原

21、Informix的returning;需要改为 Return as 或 Return is,Oracle 数据迁移修改要点（后台的修改-存储过程）,原Informix中定义的decimal类型的参数需要变为number类型,Oracle 数据迁移修改要点（后台的修改-存储过程）,对于返回多个参数的存储过程，需要把返回的参数名和参数类型定义好,Oracle 数据迁移修改要点（后台的修改-存储过程）,对于存储过程中一个或多个参数既是入参，又是出参的情况，需要在去掉returning部分，并在参数定义里标明此参数既是入参又是出参。,Oracle 数据迁移修改要点（后台的修改-存储过程）,Oracle

22、中声明变量时不需要加DEFINE（去掉DEFINE）变量初始化也不需要加LET（去掉LET）在oracle中定义字符串时只需要用单引号（替换“为）Oracle中给变量赋值使用“:=”而不是“=”（替换“=”为“:=”）,Oracle 数据迁移修改要点（后台的修改-存储过程）,在Oracle中最外层只能有一个beginend,此beginend内部可以嵌套多层其他的beginend,Oracle 数据迁移修改要点（后台的修改-存储过程）,在IF语句中原informix中的ELIF需要改成ELSIF,Oracle 数据迁移修改要点（后台的修改-存储过程）,Informix和Oracle在异常处理的

23、语法上处理差异比较大,Oracle 数据迁移修改要点（后台的修改-存储过程）,Informix和oracle对于游标的处理，语法是完全不一样的,Oracle 数据迁移修改要点（后台的修改-存储过程）,其他一些开发规范和注意事项：trim()函数问题：当使用trim()函数对select的字段进行处理，比如：select trim(did)，最后Order by时必须使用 trim(did)而不能使用 did出参精度问题：PL/SQL在处理精度，在显示时总是保留2位小数，但变量的实际值的精度是没有变化的在定义中间变量的时候一定要考虑到此中间变量的精度问题。如：A=B*3 如果A的精度是保留2位小

24、数，而B的精度是保留4位小数，A得到的值就会出现精度问题。,Oracle 数据迁移修改要点（后台的修改-存储过程）,其他一些开发规范和注意事项：Beginend的嵌套在存储过程的开发时为了方便后期的错误跟踪，应尽量减少多层的嵌套，这样一旦抛出异常就能很快的跟踪到发生问题的地方Errcode的规范定义：Oracle的自定义异常代码从-20000到-20999，开发过程中应对不同自定义异常定义不同的自定义异常代码，尽量减少使用相同的异常代码，方便后期的错误跟踪,Oracle 数据迁移修改要点（后台的修改-存储过程）,使用研发中心测试环境安装Oracle客户端和PL/SQL Developer在tn

25、snames.ora文件内添加中心测试环境的相关信息 tnsnames.ora在$ORACLE_HOME/network/admin/tnsnames.ora打开tnsnames.ora，添加如下内容：AMIS58=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.253.0.58)(PORT=1521)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=amis.clic),测试环境,使用研发中心测试环境打开PL/SQL（下图），database选择AMIS58用户名为：amisb 密码为：password点击O

26、K，进入,测试环境,PL/SQL的基本使用点击左上方的 图标，选择“SQL Window”，右边的主窗口会出现片空白的区域，在此空白的区域输入SQL或者存储过程代码，点击左上方的 图标（或者F8键）便可以执行SQL或者存储过程,测试环境,新建存储过程新建SQL Window，输入存储过程，点击执行（F8）如下图：,存储过程测试方法,新建存储过程在执行完存储过程后，便可在左边目录栏中的Procedures文件夹下找到刚才新建的存储过程了（SPM301）如下图：图中带有红色的存储过程是没有编译通过的，没有红色是已经编译通过,可以进行测试的存储过程,存储过程测试方法,存储过程测试右键点击SPM301的图标，选择“Test”在弹出的新窗口中会列出此存储过程的入参与出参（下图）,存储过程测试方法,存储过程测试在“Value”列中输入该存储过程的各个入参（下图）点击上图中左上方的 图标进行测试,存储过程测试方法,存储过程测试如果没有报错且返回正确的出参则此存储过程通过测试下图中的测试没有报错且sp_newallowance成功返回了0,存储过程测试方法,存储过程测试测试过程中可以逐行进行调试，下图是执行测试是可供调试的功能按钮开发和测试人员可以根据需求对存储过程进行逐行调试,存储过程测试方法,谢谢！,