从sql server 向oracle 8迁移的技术实现方案.docx

1、从sql server 向oracle 8迁移的技术实现方案从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

2、端语法说明在ORACLE中，分析其数据类型，大致可分为数字、字符、日期时间和特殊四大类。其中，数字类型有NUMBER；字符类型有CHAR与VARCHAR2；日期时间类型只有DATE一种；除此之外，LONG、RAW、LONGRAW、BLOB、CLOB和BFILE等数据类型都可视为特殊数据类型。、SQLSERVER端语法说明在SQLSERVER中，参照上面对ORACLE的划分，数据类型也大致可分为数字、字符、日期时间和特殊四大类。数字类型又可分为精确数值、近似数值、整数、二进制数、货币等几类，其中，精确数值有DECIMAL(P,S)与NUMERIC(P,S)；近似数值有FLOAT(N)；整数有IN

3、T、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

4、,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迁移、SQLSE

5、RVER端语法说明在SQLSERVER中，可以将数据库中的某一字段定义为IDENTITY列以做主键识别，如：jlbhnumeric(12,0)identity(1,1)/*记录编号字段*/CONSTRAINTPK_tbl_examplePRIMARYKEYnonclustered(jlbh)/*主键约束*/在这里，jlbh是一个ID列，在向具有该列的表插入记录时，系统将从1开始以1的步长自动对jlbh的值进行维护。、ORACLE端语法说明但在ORACLE中，没有这样的ID列定义，而是采用另一种方法，即创建SEQUENCE。如：/*-1、创建各使用地区编码表-*/droptableLT_AREA

6、;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、实际操作时引用SE

7、QUENCE的下一个值-*/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(

8、SEQ_LT_AREA.NEXTVAL,上海);、从SQLSERVER向ORACLE的迁移方案根据以上分析，当从SQLSERVER向ORACLE迁移时，可以做如下调整：1、去掉建表语句中有关ID列的identity声明关键字；2、创建SEQUENCE，将此SEQUENCE与需类ID化的列对应；3、在INSERT语句中对相应列引用其SEQUENCE值：SEQUENCENAME.NEXTVAL实际上，处理以上情况在ORACLE中采用的方法为对有自动增长字段的表增加一插入前触发器（具体资料见后“触发器”一节），如下：CREATEORREPLACETRIGGERGenaerateAreaIDBEFOR

9、EINSERTONLT_AREAFOREACHROWSelectSEQ_LT_AREA.NEXTVALINTO:NEW.IDFROMDUAL;BEGINENDGenaerateAreaID;GenaerateAreaID实际上修改了伪记录:new的area_id值。:new最有用的一个特性-当该语句真正被执行时，:new中的存储内容就会被使用。所以系统每次都能自动生成新的号码。表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）、SQLSERVER端语法说明有如下SQLSERVER语句：/*-创建employee表-*/IFEXISTS(SELECT1FROMSYSOBJEC

10、TSWHERENAME=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约束说明：EachemployeeIDconsi

11、stsofthreecharactersthatrepresenttheemployeesinitials,followedbyafivedigitnumberrangingfrom10000to99999andthentheemployeesgender(MorF).A(hyphen)-isacceptableforthemiddleinitial.*/fnamevarchar(20)NOTNULL,minitchar(1)NULL,lnamevarchar(30)NOTNULL,ss_idvarchar(9)UNIQUE,/*创建唯一性约束*/ job_idsmallintNOTNULLD

12、EFAULT1,/*设定DEFAULT值*/job_lvltinyintDEFAULT10,/*设定DEFAULT值*/*Entryjob_lvlfornewhires.*/pub_idchar(4)NOTNULLDEFAULT(9952)/*设定DEFAULT值*/REFERENCESpublishers(pub_id),/*创建系统命名外键约束*/*Bydefault,theParentCompanyPublisheristhecompanytowhomeachemployeereports.*/hire_datedatetimeNOTNULLDEFAULT(getdate(),/*设定D

13、EFAULT值*/*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、ORAC

14、LE端语法说明在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),/*CHE

15、CK约束说明：EachemployeeIDconsistsofthreecharactersthatrepresenttheemployeesinitials,followedbyafivedigitnumberrangingfrom10000to99999andthentheemployeesgender(MorF).A(hyphen)-isacceptableforthemiddleinitial.*/fnamevarchar2(20)NOTNULL,minitvarchar2(1)NULL,lnamevarchar2(30)NOTNULL,ss_idvarchar2(9)UNIQUE,/

16、*创建唯一性约束*/ 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,thePa

17、rentCompanyPublisheristhecompanytowhomeachemployeereports.*/hire_datedateNOTNULLDEFAULTSYSDATE,/*设定DEFAULT值*/*这里，SQLSERVER的getdate()调整为ORACLE的SYSDATE*/*Bydefault,thecurrentsystemdatewillbeentered.*/CONSTRAINTFK_employee_jobFOREIGNKEY(job_id)REFERENCESjobs(job_id)/*创建自命名外键约束*/);/*-创建employee表上的index-

18、*/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,

19、.)而不能写成CreatetableMZ_Ghxx(ghlxhnumberprimaykey,rqdatenotnulldefaultsysdate,.)（2）T-SQL定义表结构时，如果涉及到用默认时间和默认修改人员，全部修改如下：ZHXGRQDATEDEFAULTSYSDATENULL，ZHXGRCHAR（8）DEFAULTFUTIANNULL，（3）如表有identity定段，要先将其记录下来，建完表之后，马上建相应的序列和表触发器，并作为记录。游标、SQLSERVER端语法说明1、DECLARECURSOR语句语法：DECLAREcursor_nameINSENSITIVESCROLL

20、CURSORFORselect_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_nam

21、e1,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_STATUStoseeifthereareanymorerowst

22、ofetch.WHILEFETCH_STATUS=0BEGIN-Concatenateanddisplaythecurrentvaluesinthevariables.PRINTAuthor:+au_fname+au_lname-Thisisexecutedaslongasthepreviousfetchsucceeds. FETCHNEXTFROMauthors_cursorINTOau_lname,au_fnameEND（3）CLOSEauthors_cursor7、隐式游标MSSqlServer中对于数据操纵语句受影响的行数，有一个全局的变量：rowcount，其实它是一个隐式的游标，它

23、记载了上条数据操纵语句所影响的行数，当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:Thevariables