Oracle数据库迁移培训 FINAL

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,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,存储过程名字,;,Oracle,存储过程简介,创建一个简单存储过程的方式,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.SQLEXECUTE skeleton;,注释,(SQL*Plus,输出一下信息确信存储过程成功执,即,PL/,SQLproceduresuccessfullycompleted,).,2.,在,SQL*Plus,命令行提示符,键入,:,SQLSET SERVEROUTPUT ON,再次敲入,SQLEXECUTE skeleton,即可,.,注释,:,查看存储过程中的打印语句信息,实行以上命令,.,Oracle,存储过程简介,删除一个存储过程,1.,在,SQL,命令提示符下输入以下命令：,命令,: SQLDROP PROCEDURE,存储过程名,;,存储过程的注释,-,注释一行,Oracle,存储过程简介,存储过程入参与返回值,create or replace procedure,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.21,Product_id,interger,;,注释,:,同一存储过程中，变量名最好不要重复。,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”,Oracle,存储过程简介,存储过程异常控制,Exception,when,too_many_rows,then,DBMS_OUTPUT.PUT_LINE(,返回值多于,1,行,);,when others then,DBMS_OUTPUT.PUT_LINE(,在,RUNBYPARMETERS,过程中出错！,);,Oracle,存储过程简介,存储过程结构块,BEGIN,BEGIN,第一步处理；,END;,BEGIN,第二步处理；,END;,BEGIN,第三步处理；,END;,END;,Oracle,存储过程简介,存储过程游标,带参数的游标,CURSOR C_USER,(C_ID NUMBER),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;,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;,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_LINE(cur_result,);,END;,END LOOP;,END;,Oracle,存储过程简介,存储过程,while,循环,Counter :=0;,WHILE counter str2 then,result :=1;,ELSIF,str2 str1 THEN,result := -1;,ELSE,result := 0;,END IF;,Oracle,存储过程简介,存储过程,if,条件表达式,比较符,，,=,，,=,，,，,!=,AND,，,OR,，,NOT,(NOT) BETWEEN AND,(NOT) IN ( , , , ,),IS (NOT) NULL,(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(realsal,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 | AS,BEGIN,FUNCTION _BODY,END;,注,:,函数与存储过程相似,唯一区别函数必须向调用他的语句返回一个值,它与存储过合起来被称为存储子程序,从某种意义讲为小程序,.,Oracle,存储过程简介,包头和包体结构,CREATE OR REPLACE PACKAGE,包名, IS | AS ,PACKAGE _SPECIFICATION,END,包名,;,CREATE OR REPLACE PACKAGE BODY,包名, IS | AS ,PACKAGE,过程,END,包名,;,注,:,PACKAGE _SPECIFICATION,是指包的用户使用的过程和函数的列表,.,PACKAGE,过程 是指包的用户可以使用的存储过程和函数的列表,(,包含变量,类型,定义和游标,),包主要分为,:,规范和包体,.,它的用途主要是把存储过程和函数组织到包中,.,通过,PL/SQL,代码模块化,构 建其他编程人员重用的代码库,.,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,order 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,时,，需要写成标准的,outer join,的形式，例如,informix,：,select * from,filelines,t1,outer(filelines t2),outer(pathlines,t3,）,where t1.lines=t2.lines and t1.path=t3.path,Oracle,: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,（存储过程适用）,在,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_data.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, delete, ;,将,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, drop,1,、删除建临时表的语句，若需创建临时表，可报给数据库迁移人员，统一建立,2,、检查是否有删除临时表一并删除,Oracle,数据迁移修改要点,（前台,SQL,的修改）,关键字：,rowid,修改者只需将出现,rowid,的地方记录并上报，由程序组统一处理：,Oracle,不支持,rowid,保留字，实际上对于现有核心系统，如果使用,rowid,会对分片产生影响，因此原则上也应杜绝,rowid,的使用，对于此情况需要统一报架构组，对应用逻辑进行评估、调整。,Oracle,数据迁移修改要点,（前台,SQL,的修改）,关键字：,!=,，,，,=,(,存储过程适用,),Oracle,对,sql,语句中字段值为空字符处理时只会判断是否为,NULL,，所以在修改时凡是出现,!=,或,或,=,时需要修改：,将,!=,或,替换为,is not null,=,替换为,is null,Oracle,数据迁移修改要点,（前台,SQL,的修改）,后台的修改,存储过程,Oracle,数据迁移修改要点,为使存储过程在,Oracle,数据库中,方便编译调试,，把原存储过程中的,create,改为,create or replace,注：,对于返回单个值的存储过程，建议移植为函数,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,CREATE PROCEDURE,spm308(,sp_did,char(12),sp_pid,char(14),sp_StatDate,date,sp_verid,integer),RETURNING DECIMAL(12,2);,CREATE OR REPLACE,Procedure,spm308(,sp_did,varchar2,sp_pid,varchar2,sp_StatDate,date,sp_verid,integer),RETURN number as,Oracle,存储过程中的入参不需要定义参数的长度，在迁移过程中需要将原入参的长度定义部分删除。,Oracle,存储过程中，,char,类型字段统一改为,varchar2,类型,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,CREATE PROCEDURE spm308(,sp_did,char(12),sp_pid,char(14),sp_StatDate,date,sp_verid,integer),RETURNING DECIMAL(12,2);,CREATE OR REPLACE,Procedure,spm308(,sp_did,varchar2,sp_pid,varchar2,sp_StatDate,date,sp_verid,integer),RETURN number as,Oracle,存储过程改为函数并且返回单个参数时原,Informix,的,returning ,;,需要改为,Return as,或,Return is,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,CREATE PROCEDURE spm308(,sp_did,char(12),sp_pid,char(14),sp_StatDate,date,sp_verid,integer),RETURNING,DECIMAL(12,2);,CREATE OR REPLACE function,spm308(,sp_did,varchar2,sp_pid,varchar2,sp_StatDate,date,sp_verid,integer),RETURN,number,as,原,Informix,中定义的,decimal,类型的参数需要变为,number,类型,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,CREATE PROCEDURE spm308(,sp_did,char(12),sp_pid,char(14),sp_StatDate,date,sp_verid,integer),RETURNING,DECIMAL,(12,2);,CREATE OR REPLACE,Procedure,spm308(,sp_did,varchar2,sp_pid,varchar2,sp_StatDate,date,sp_verid,integer),RETURN,number,as,对于返回多个参数的存储过程，需要把返回的参数名和参数类型定义好,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,drop procedure spmgrp308;,CREATE PROCEDURE spmgrp308(,sp_did,char(12),sp_pid,char(14),sp_StatDate,date,sp_verid,integer),RETURNING,decimal(12,2),decimal(12,2),decimal(12,2);,CREATE OR REPLACE PROCEDURE SPMGRP308(SP_DID CHAR,SP_PID CHAR,SP_STATDATE DATE,SP_VERID INTEGER,SP_POSSITALLOWANCE out number, SP_TUTORALLOWANCE out number,SP_DUTYALLOWANCE out number),AS,对于存储过程中一个或多个参数既是入参，又是出参的情况，需要在去掉,returning,部分，并在参数定义里标明此参数既是入参又是出参。,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,CREATE PROCEDURE example(,sp_did,char(12),sp_pid,char(14),sp_StatDate,date,sp_verid,integer),RETURNING DECIMAL(12,2);,CREATE OR REPLACE,Procedure,example(,sp_did,varchar2,sp_pid,varchar2,sp_StatDate,date,sp_verid,in out,integer),as,Oracle,中声明变量时不需要加,DEFINE,（去掉,DEFINE,）,变量初始化也不需要加,LET,（去掉,LET,）,在,oracle,中定义字符串时只需要用,单引号,（替换“为,）,Oracle,中给变量赋值使用“,:=,”,而不是“,=,”,（替换“,=”,为“,:=”,）,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,DEFINE,sp_tid char(12);,DEFINE,sp_AllFYC decimal(12,2);,LET,sp_tid,=,;,LET,sp_AllFYC,=,0.0;,SP_TID,VARCHAR2,(12);SP_ALLFYC,NUMBER,(12, 2);,SP_TID,:=,;SP_ALLFYC,:=,0.0;,在,Oracle,中最外层只能有一个,beginend,此,beginend,内部可以嵌套多层其他的,beginend,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,Begin,begin,end;,End;,Begin,End;,Bigin,Begin,begin,end;,End;,Begin,End;,End;,在,IF,语句中原,informix,中的,ELIF,需要改成,ELSIF,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,LET,sp_Code,=;,IF (,sp_ActRank,= 22 ) THEN,-,分处经理,LET,sp_Code,= 42;,ELIF,(,sp_ActRank,= 23 ) THEN,-,处经理,LET,sp_Code,= 43;,END IF;,SP_CODE := ;,IF (SP_ACTRANK = 22) THEN,-,分处经理,SP_CODE := 42;,ELSIF,(SP_ACTRANK = 23) THEN,-,处经理,SP_CODE := 43;,END IF;,Informix,和,Oracle,在异常处理的语法上处理差异比较大,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,ON EXCEPTION,SET,sp_errsql,sp_errisam,sp_errstr,IF (,sp_errsql,!=0) THEN,RAISE EXCEPTION -746,sp_errisam,错误码,:|,sp_errsql,|;,END IF;,END EXCEPTION;,Exception,when others then,DBMS_OUTPUT.PUT_LINE(,在,XX,过程中出错！,);,Informix,和,oracle,对于游标的处理，语法是完全不一样的,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,Informix,Oracle,create procedure,pHasCursor,(),define v_f1 integer,；,begin,foreach,select f1 into v_f1 from t1,if (v_f1 = 1) then,update t1 set f2 = one where,f3=1001;,end if;,end,foreach,;,End ;,End,pHasCursor,;,create or replace procedure,pHasCursor,as v_f1 number(10,0);,cursor curt1 is select f1 from t1 for update ;,begin,open curt1;,loop,fetch curt1 Into v_f1;,exit when curt1%notfound;,end loop;,Close curt1;,End;,其他一些开发规范和注意事项：,trim(),函数问题：,当使用,trim(),函数对,select,的字段进行处理，比如：,select,trim(did,),，最后,Order by,时必须使用,trim(did,),而不能使用,did,出参精度问题：,PL/SQL,在处理精度，在显示时总是保留,2,位小数，但变量的实际值的精度是没有变化的,在定义中间变量的时候一定要考虑到此中间变量的精度问题。如：,A=B*3,如果,A,的精度是保留,2,位小数，而,B,的精度是保留,4,位小数，,A,得到的值就会出现精度问题。,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,其他一些开发规范和注意事项：,Beginend,的嵌套,在存储过程的开发时为了方便后期的错误跟踪，应尽量减少多层的嵌套，这样一旦抛出异常就能很快的跟踪到发生问题的地方,Errcode,的规范定义：,Oracle,的自定义异常代码从,-20000,到,-20999,，开发过程中应对不同自定义异常定义不同的自定义异常代码，尽量减少使用相同的异常代码，方便后期的错误跟踪,Oracle,数据迁移修改要点,（后台的修改,-,存储过程）,使用研发中心测试环境,安装,Oracle,客户端和,PL/SQL Developer,在,tnsnames.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,点击,OK,，进入,测试环境,PL/SQL,的基本使用,点击左上方的 图标，选择“,SQL Window”,，右边的主窗口会出现片空白的区域，在此空白的区域输入,SQL,或者存储过程代码，点击左上方的 图标（或者,F8,键）便可以执行,SQL,或者存储过程,测试环境,新建存储过程,新建,SQL Window,，输入存储过程，点击执行（,F8,）,如下图：,存储过程测试方法,新建存储过程,在执行完存储过程后，便可在左边目录栏中的,Procedures,文件夹下找到刚才新建的存储过程了（,SPM301,）,如下图：,图中带有红色,的存储过程是没有编译通过的，没有红色,是已经编译通过,可以进行测试的存储过程,存储过程测试方法,存储过程测试,右键点击,SPM301,的图标，选择“,Test”,在弹出的新窗口中会列出此存储过程的入参与出参（下图）,存储过程测试方法,存储过程测试,在“,Value”,列中输入该存储过程的各个入参（下图）,点击上图中左上方的 图标进行测试,存储过程测试方法,存储过程测试,如果没有报错且返回正确的出参则此存储过程通过测试,下图中的测试没有报错且,sp_newallowance,成功返回了,0,存储过程测试方法,存储过程测试,测试过程中可以逐行进行调试，下图是执行测试是可供调试的功能按钮,开发和测试人员可以根据需求对存储过程进行逐行调试,存储过程测试方法,谢谢！,