从sqlserver向oracle8迁移的技术实现方案.doc

从SQL SERVER 向ORACLE 8迁移的技术实现方案 数据库端SQL语法的迁移以下为常用的SQL语法迁移，包括数据类型、ID列向SEQUENCE迁移、表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）、游标、存储过程、函数、触发器、常用SQL语法与函数几个方面，考虑SQLSERVER的实际情况，没有涉及ORACLE特有的PACKAGE、EXCEPTION等。在以下的描述中，将SQLSERVER的TRANSACT-SQL简称为T-SQL。在ORACLE中，其语法集称为PL/SQL。数据类型的迁移、ORACLE端语法说明在ORACLE中，分析其数据类型，大致可分为数字、字符、日期时间和特殊四大类。其中，数字类型有NUMBER；字符类型有CHAR与VARCHAR2；日期时间类型只有DATE一种；除此之外，LONG、RAW、LONGRAW、BLOB、CLOB和BFILE等数据类型都可视为特殊数据类型。、SQLSERVER端语法说明在SQLSERVER中，参照上面对ORACLE的划分，数据类型也大致可分为数字、字符、日期时间和特殊四大类。数字类型又可分为精确数值、近似数值、整数、二进制数、货币等几类，其中，精确数值有DECIMAL(P,S)与NUMERIC(P,S)；近似数值有FLOAT(N)；整数有INT、SMALLINT、TINYINT；二进制数有BINARY(N)、VARBINARY(N)；货币有MONEY、SMALLMONEY。字符类型有CHAR(N)与VARCHAR(N)。日期时间类型有DATETIME、SMALLDATETIME。除此之外，BIT、TIMESTAMP、TEXT和IMAGE、BINARYVARING等数据类型都可视为特殊数据类型。、从SQLSERVER向ORACLE的迁移方案比较ORACLE与SQLSERVER在数据类型上的不同，当从SQLSERVER向ORACLE迁移时，可以做如下调整：SQLSERVERORACLE数字类型DECIMAL(P,S) NUMBER(P,S)NUMERIC(P,S) NUMBER(P,S)FLOAT(N) NUMBER(N)INTNUMBERSMALLINTNUMBERTINYINTNUMBERMONEYNUMBER19,4SMALLMONEYNUMBER19,4字符类型CHAR(N) CHAR(N)VARCHAR(N) VARCHAR2(N)日期时间类型DATETIMEDATESMALLDATETIMEDATE其它TEXTCLOBIMAGEBLOBBITNUMBER（1）方法:公司原系统中的Money用于金额时转换用number(14,2);用于单价时用number(10,4)代替;ID列向SEQUENCE迁移、SQLSERVER端语法说明在SQLSERVER中，可以将数据库中的某一字段定义为IDENTITY列以做主键识别，如：jlbhnumeric(12,0)identity(1,1)/*记录编号字段*/CONSTRAINTPK_tbl_examplePRIMARYKEYnonclustered(jlbh)/*主键约束*/在这里，jlbh是一个ID列，在向具有该列的表插入记录时，系统将从1开始以1的步长自动对jlbh的值进行维护。、ORACLE端语法说明但在ORACLE中，没有这样的ID列定义，而是采用另一种方法，即创建SEQUENCE。如：/*-1、创建各使用地区编码表-*/droptableLT_AREA;createtableLT_AREA(area_idnumber(5,0)NOTNULL,/*地区编码*/area_namevarchar2(20)NOTNULL,/*地区名称*/constraintPK_LT_AREAPRIMARYKEY(area_id);/*-2、创建SEQUENCE，将列area_id类ID化-*/dropsequenceSEQ_LT_AREA;createsequenceSEQ_LT_AREAincrementby1/*该SEQUENCE以1的步长递增*/startwith1maxvalue99999;/*从1开始，最大增长到99999*/*-3、实际操作时引用SEQUENCE的下一个值-*/insertintoLT_AREA(area_id,area_name)values(SEQ_LT_AREA.NEXTVAL,深圳);insertintoLT_AREA(area_id,area_name)values(SEQ_LT_AREA.NEXTVAL,广州);insertintoLT_AREA(area_id,area_name)values(SEQ_LT_AREA.NEXTVAL,北京);/*-4、新插入连续三条记录后，下一条语句运行后，上海地区的area_id为4-*/insertintoLT_AREA(area_id,area_name)values(SEQ_LT_AREA.NEXTVAL,上海);、从SQLSERVER向ORACLE的迁移方案根据以上分析，当从SQLSERVER向ORACLE迁移时，可以做如下调整：1、去掉建表语句中有关ID列的identity声明关键字；2、创建SEQUENCE，将此SEQUENCE与需类ID化的列对应；3、在INSERT语句中对相应列引用其SEQUENCE值：SEQUENCENAME.NEXTVAL实际上，处理以上情况在ORACLE中采用的方法为对有自动增长字段的表增加一插入前触发器（具体资料见后“触发器”一节），如下：CREATEORREPLACETRIGGERGenaerateAreaIDBEFOREINSERTONLT_AREAFOREACHROWSelectSEQ_LT_AREA.NEXTVALINTO:NEW.IDFROMDUAL;BEGINENDGenaerateAreaID;GenaerateAreaID实际上修改了伪记录:new的area_id值。:new最有用的一个特性-当该语句真正被执行时，:new中的存储内容就会被使用。所以系统每次都能自动生成新的号码。表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）、SQLSERVER端语法说明有如下SQLSERVER语句：/*-创建employee表-*/IFEXISTS(SELECT1FROMSYSOBJECTSWHERENAME=employeeANDTYPE=U)DROPTABLEemployeeGOCREATETABLEemployee(emp_idempid/*empid为用户自定义数据类型*/*创建自命名主键约束*/CONSTRAINTPK_employeePRIMARYKEYNONCLUSTERED/*创建自命名CHECK约束*/CONSTRAINTCK_emp_idCHECK(emp_idLIKEA-ZA-ZA-Z1-90-90-90-90-9FMoremp_idLIKEA-Z-A-Z1-90-90-90-90-9FM),/*CHECK约束说明：EachemployeeIDconsistsofthreecharactersthatrepresenttheemployeesinitials,followedbyafivedigitnumberrangingfrom10000to99999andthentheemployeesgender(MorF).A(hyphen)-isacceptableforthemiddleinitial.*/fnamevarchar(20)NOTNULL,minitchar(1)NULL,lnamevarchar(30)NOTNULL,ss_idvarchar(9)UNIQUE,/*创建唯一性约束*/ job_idsmallintNOTNULLDEFAULT1,/*设定DEFAULT值*/job_lvltinyintDEFAULT10,/*设定DEFAULT值*/*Entryjob_lvlfornewhires.*/pub_idchar(4)NOTNULLDEFAULT(9952)/*设定DEFAULT值*/REFERENCESpublishers(pub_id),/*创建系统命名外键约束*/*Bydefault,theParentCompanyPublisheristhecompanytowhomeachemployeereports.*/hire_datedatetimeNOTNULLDEFAULT(getdate(),/*设定DEFAULT值*/*Bydefault,thecurrentsystemdatewillbeentered.*/CONSTRAINTFK_employee_jobFOREIGNKEY(job_id)REFERENCESjobs(job_id)/*创建自命名外键约束*/)GO/*-创建employee表上的index-*/IFEXISTS(SELECT1FROMsysindexesWHEREname=emp_pub_id_ind)DROPINDEXemployee.emp_pub_id_indGOCREATEINDEXemp_pub_id_indONemployee(pub_id)GO、ORACLE端语法说明在ORACLE端的语法如下：/*-创建employee表-*/DROPTABLEemployee；CREATETABLEemployee(emp_idvarchar2(9)/*根据用户自定义数据类型的定义调整为varchar2(9)*/*创建自命名主键约束*/CONSTRAINTPK_employeePRIMARYKEYNONCLUSTERED/*创建自命名CHECK约束*/CONSTRAINTCK_emp_idCHECK(emp_idLIKEA-ZA-ZA-Z1-90-90-90-90-9FMoremp_idLIKEA-Z-A-Z1-90-90-90-90-9FM),/*CHECK约束说明：EachemployeeIDconsistsofthreecharactersthatrepresenttheemployeesinitials,followedbyafivedigitnumberrangingfrom10000to99999andthentheemployeesgender(MorF).A(hyphen)-isacceptableforthemiddleinitial.*/fnamevarchar2(20)NOTNULL,minitvarchar2(1)NULL,lnamevarchar2(30)NOTNULL,ss_idvarchar2(9)UNIQUE,/*创建唯一性约束*/ job_idnumber(5,0)NOTNULL DEFAULT1,/*设定DEFAULT值*/*这里考虑了SMALLINT的长度，也可调整为number*/job_lvlnumber(3,0) DEFAULT10,/*设定DEFAULT值*/*这里考虑了TINYINT的长度，也可调整为number*/*Entryjob_lvlfornewhires.*/pub_idvarchar2(4)NOTNULLDEFAULT(9952)/*设定DEFAULT值*/REFERENCESpublishers(pub_id),/*创建系统命名外键约束*/*Bydefault,theParentCompanyPublisheristhecompanytowhomeachemployeereports.*/hire_datedateNOTNULLDEFAULTSYSDATE,/*设定DEFAULT值*/*这里，SQLSERVER的getdate()调整为ORACLE的SYSDATE*/*Bydefault,thecurrentsystemdatewillbeentered.*/CONSTRAINTFK_employee_jobFOREIGNKEY(job_id)REFERENCESjobs(job_id)/*创建自命名外键约束*/);/*-创建employee表上的index-*/DROPINDEXemployee.emp_pub_id_ind;CREATEINDEXemp_pub_id_indONemployee(pub_id);、从SQLSERVER向ORACLE的迁移方案比较这两段SQL代码，可以看出，在创建表及其主键、外键、CHECK、UNIQUE、DEFAULT、INDEX时，SQLSERVER与ORACLE的语法大致相同,但时迁移时要注意以下情况:（1）Oracle定义表字段的default属性要紧跟字段类型之后，如下：CreatetableMZ_Ghxx(ghlxhnumberprimaykey,rqdatedefaultsysdatenotnull,.)而不能写成CreatetableMZ_Ghxx(ghlxhnumberprimaykey,rqdatenotnulldefaultsysdate,.)（2）T-SQL定义表结构时，如果涉及到用默认时间和默认修改人员，全部修改如下：ZHXGRQDATEDEFAULTSYSDATENULL，ZHXGRCHAR（8）DEFAULTFUTIANNULL，（3）如表有identity定段，要先将其记录下来，建完表之后，马上建相应的序列和表触发器，并作为记录。游标、SQLSERVER端语法说明1、DECLARECURSOR语句语法：DECLAREcursor_nameINSENSITIVESCROLLCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_list例：DECLAREauthors_cursorCURSORFORSELECTau_lname,au_fnameFROMauthorsWHEREau_lnameLIKEB%ORDERBYau_lname,au_fname2、OPEN语句语法：OPENcursor_name例：OPENauthors_cursor3、FETCH语句语法：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEnFROMcursor_nameINTOvariable_name1,variable_name2, 例：FETCHNEXTFROMauthors_cursorINTOau_lname,au_fname4、CLOSE语句语法：CLOSEcursor_name例：CLOSEauthors_cursor5、DEALLOCATE语句语法：DEALLOCATEcursor_name例：DEALLOCATEauthors_cursor6、游标中的标准循环与循环终止条件判断（1）FETCHNEXTFROMauthors_cursorINTOau_lname,au_fname（2）-CheckFETCH_STATUStoseeifthereareanymorerowstofetch.WHILEFETCH_STATUS=0BEGIN-Concatenateanddisplaythecurrentvaluesinthevariables.PRINTAuthor:+au_fname+au_lname-Thisisexecutedaslongasthepreviousfetchsucceeds. FETCHNEXTFROMauthors_cursorINTOau_lname,au_fnameEND（3）CLOSEauthors_cursor7、隐式游标MSSqlServer中对于数据操纵语句受影响的行数，有一个全局的变量：rowcount，其实它是一个隐式的游标，它记载了上条数据操纵语句所影响的行数，当rowcount小于1时，表时，上次没有找到相关的记录,如下：Updatestudentssetlastname=Johnwherestudent_id=301Ifrowcount1thenInsertintostudentsvalues(301,stdiv,john,996-03-02)表示如果数据表中有学号为“301”的记录，则修改其名字为“John”，如果找不到相应的记录，则向数据库中插入一条“John”的记录。8、示例：-DeclarethevariablestostorethevaluesreturnedbyFETCH.DECLAREau_lnamevarchar(40),au_fnamevarchar(20)DECLAREauthors_cursorCURSORFORSELECTau_lname,au_fnameFROMauthorsWHEREau_lnameLIKEB%ORDERBYau_lname,au_fnameOPENauthors_cursor-Performthefirstfetchandstorethevaluesinvariables.-Note:Thevariablesareinthesameorderasthecolumns-intheSELECTstatement.FETCHNEXTFROMauthors_cursorINTOau_lname,au_fname-CheckFETCH_STATUStoseeifthereareanymorerowstofetch.WHILEFETCH_STATUS=0BEGIN-Concatenateanddisplaythecurrentvaluesinthevariables.PRINTAuthor:+au_fname+au_lname-Thisisexecutedaslongasthepreviousfetchsucceeds.FETCHNEXTFROMauthors_cursorINTOau_lname,au_fnameENDCLOSEauthors_cursorDEALLOCATEauthors_cursor、ORACLE端语法说明1、DECLARECURSOR语句语法：CURSORcursor_nameISselect_statement;例：CURSORauthors_cursorISSELECTau_lname,au_fnameFROMauthorsWHEREau_lnameLIKEB%ORDERBYau_lname,au_fname;2、OPEN语句语法：OPENcursor_name例：OPENauthors_cursor;3、FETCH语句语法：FETCHcursor_nameINTOvariable_name1,variable_name2,;例：FETCHauthors_cursorINTOau_lname,au_fname;4、CLOSE语句语法：CLOSEcursor_name例：CLOSEauthors_cursor;5、简单游标提取循环结构与循环终止条件判断1用%FOUND做循环判断条件的WHILE循环（1）FETCHauthors_cursorINTOau_lname,au_fname；（2）WHILEauthors_cursor%FOUNDLOOP-Concatenateanddisplaythecurrentvaluesinthevariables.DBMS_OUTPUT.ENABLE;DBMS_OUTPUT.PUT_LINE(Author:|au_fname|au_lname);FETCHauthors_cursorINTOau_lname,au_fname；ENDLOOP;（3）CLOSEauthors_cursor;2用%NOTFOUND做循环判断条件的简单LOOP.ENDLOOP循环（1）OPENauthors_cursor;（2）LOOPFETCHauthors_cursorINTOau_lname,au_fname；-Exitloopwhentherearenomorerowstofetch.EXITWHENauthors_cursor%NOTFOUND;-Concatenateanddisplaythecurrentvaluesinthevariables.DBMS_OUTPUT.ENABLE;DBMS_OUTPUT.PUT_LINE(Author:|au_fname|au_lname);ENDLOOP;（3）CLOSEauthors_cursor;3用游标式FOR循环,如下：DECLARECURSORc_HistoryStudentsISSELECTid,first_name,last_nameFROMStudentsWHEREmajor=HistoryBEGINFORv_StudentDataINc_HistoryStudentsLOOP INSERTINTOregistered_students (student_id,first_name,last_name,department,course) VALUES(v_StudentData.ID,v_StudentData.first_name,v_StudentData.last_name,HIS,301);ENDLOOP; COMMIT;END;首先，记录v_StudentData没有在块的声明部分进行声明，些变量的类型是c_HistoryStudents%ROWTYPE，v_StudentData的作用域仅限于此FOR循环本身；其实，c_HistoryStudents以隐含的方式被打开和提取数据，并被循环关闭。6、隐式游标SQL%FOUND与SQL%NOTFOUND与MSSQLSERVER一样，ORACLE也有隐式游标，它用于处理INSERT、DELETE和单行的SELECT.INTO语句。因为SQL游标是通过PL/SQL引擎打开和关闭的，所以OPEN、FETCH和CLOSE命令是无关的。但是游标属性可以被应用于SQL游标，如下：BEGINUPDATEroomsSETnumber_seats=100WHEREroom_id=9990;-如果找不相应的记录，则插入新的记录 IFSQL%NOTFOUNDTHENINSERTINTOrooms(room_id,number_seats)VALUES(9990,100) ENDIFEND;7、示例：-DeclarethevariablestostorethevaluesreturnedbyFETCH.-DeclaretheCURSORauthors_cursor.DECLAREau_lnamevarchar2(40);au_fnamevarchar2(20); CURSORauthors_cursorISSELECTau_lname,au_fnameFROMauthorsWHEREau_lnameLIKEB% ORDERBYau_lname,au_fname;BEGINOPENauthors_cursor;FETCHauthors_cursorINTOau_lname,au_fname；WHILEauthors_cursor%FOUNDLOOP-Concatenateanddisplaythecurrentvaluesinthevariables.DBMS_OUTPUT.ENABLE;DBMS_OUTPUT.PUT_LINE(Author:|au_fname|au_lname);FETCHauthors_cursorINTOau_lname,au_fname；ENDLOOP;CLOSEauthors_cursor;END;、从SQLSERVER向ORACLE的迁移方案比较上述SQL代码，在迁移过程中要做如下调整：（1）T-SQL对CURSOR的声明在主体代码中，而PL/SQL中对CURSOR的声明与变量声明同步，都要在主体代码（BEGIN关键字）之前声明，所以在迁移时要将游标声明提前,MSSQLSERVER的Cursor定义后的参数省去；（2）对CUOSOR操作的语法中PL/SQL没有T-SQL里DEALLOCATECURSOR这一部分，迁移时要将该部分语句删除。（3）PL/SQL与T-SQL对游标中的循环与循环终止条件判断的处理不太一样，根据前面的讨论并参考后面对两种语法集进行控制语句对比分析部分的叙述，建议将T-SQL中的游标提取循环调整为PL/SQL中的WHILE游标提取循环结构，这样可保持循环的基本结构大致不变，同时在进行循环终止条件判断时要注意将T-SQL中的对FETCH_STATUS全局变量的判断调整为对CURSOR_NAME%FOUND语句进行判断。（4）对于T-SQL，没有定义语句结束标志，而PL/SQL用“；”结束语句。（5）对于原MSSQLSERVER类型的游标，如果游标取出的值没有参与运算的，全部采用FOR循环方式来替换；而对于取出的值还要进行其它运算的，可以采用直接在定义变量位置定义变量。(6)MSSQL中对于同一游标重复定义几次的情况在ORACLE中可通过游标变量来解决.如下：MSSQLSERVER中：Declarecur_ypdmcursorforSelect*fromypOpencur_ypFetchcur_ypintoyp,mcWhilefetch_status-1BeginIffetch_status-2Begin.EndFetchcur_ypintoyp,mcEndClosecur_ypdmDeallocatecur_ypdm.Declarecur_ypdmcursorforSelect*fromypwherecondition1Opencur_ypFetchcur_ypintoyp,mcWhilefetch_status-1BeginIffetch_status-2Begin.EndFetchcur_ypintoyp,mcEndClosecur_ypdmDeallocatecur_ypdm.Declarecur_ypdmcursorforSelect*fromypwherecondition2Opencur_ypFetchcur_ypintoyp,mcWhilefetch_status-1BeginIffetch_status-2Begin.EndFetchcur_ypintoyp,mcEndClosecur_ypdmDeallocatecur_ypdm.在程序中，三次定义同一游标cur_yp在迁移过程中，最好先定义一游标变量，在程序中用open打开，如下：declaretypecur_typeisrefcur_type;cur_ypcur_type;beginopencur_ypforselect*fromyp;loopfetchcur_ypintoyp,mcExitWhencur_yp%NotFound;.endloop;closecur_yp;opencur_ypforselect*fromypwherecondition1;loopfetchcur_ypintoyp,mcExitWhencur_yp%NotFound;.endloop;closecur_yp;opencur_ypforselect*fromypwherecondition2;loopfetchcur_ypintoyp,mcExitWhencur_yp%NotFound;.endloop;closecur_yp;end;(7)请注意，游标循环中中一定要退出语名，要不然执行时会出现死循环。存储过程/函数、SQLSERVER端语法说明1、语法：CREATEPROCEDUREcedure_name;number(parameter1,parameter2,parameter255)FORREPLICATION|WITHRECOMPILEWITH|,ENCRYPTIONASsql_statement.n其中，Parameter=parameter_namedatatype=defaultoutput说明：T-SQL中存储过程的结构大致如下CREATEPROCEDUREprocedure_name/*输入、输出参数的声明部分*/ASDECLARE/*局部变量的声明部分*/BEGIN/*主体SQL语句部分*/*游标声明、使用语句在此部分*/END2、示例：IFEXISTS(SELECT1FROMsysobjectsWHEREname=titles_sumANDtype=P)DROPPROCEDUREtitles_sumGOCREATEPROCEDUREtitles_sumTITLEvarchar(40)=%,SUMmoneyOUTPUTASBEGINSELECTTitleName=titleFROMtitlesWHEREtitleLIKETITLESELECTSUM=SUM(price)FROMtitlesWHEREtitleLIKETITLEEND、ORACLE端PROCEDURE语法说明1、语法：CREATEORREPLACEPROCEDUREprocedure_name(parameter1IN|OUT|INOUTtype,parameternIN|OUT|INOUTtype)IS|ASBEGINsql_statement.n;END;说明：PL/SQL中存储过程的结构大致如下CREATEORREPLACEPROCEDUREprocedure_name（/*输入、输出参数的声明部分*/）AS/*局部变量、游标等的声明部分*/BEGIN/*主体SQL语句部分*/*游标使用语句在此部分*/EXCEPTION /*异常处理部分*/END；2、示例：CREATEORREPLACEPROCEDUREdrop_class (arg_student_idINvarchar2,arg_class_idINvarchar2,statusOUTnumber)AScounternumber;BEGINstatus:=0;-Verifythatthisclassreallyispartofthestudentsschedule.selectcount(*)intocounterfromstudent_schedulewherestudent_id=arg_student_idandclass_id=arg_class_id; IFcounter=1THENdeletefromstudent_schedulewherestudent_id=arg_student_idandclass_id=arg_class_id;status:=-1; ENDIF;END;ORACLE端FUNCTION语法说明(1)语法CREATEORREPLACEFUNCTIONfunction_name(argumentIN|OUT|INOUT)type,(argumentIN|OUT|INOUT)typeRETURNreturn_typeIS|ASBEGINEND;关键字return指定了函数返回值的数据类型。它可以是任何合法的PL/SQL数据类型。每个函数都必须有一个return子句，因为在定义上函数必须返回一个值给调用环境。（2）示例CREATEORREPLACEFUNCTIONblanace_check(Person_NameINvarchar2)RETURNNUMBERISBalan