从sql server 向oracle 8迁移的技术实现方案.docx
- 文档编号:18191056
- 上传时间:2023-08-13
- 格式:DOCX
- 页数:52
- 大小:42.13KB
从sql server 向oracle 8迁移的技术实现方案.docx
《从sql server 向oracle 8迁移的技术实现方案.docx》由会员分享,可在线阅读,更多相关《从sql server 向oracle 8迁移的技术实现方案.docx(52页珍藏版)》请在冰点文库上搜索。
从sqlserver向oracle8迁移的技术实现方案
从SQLSERVER向ORACLE8迁移的技术实现方案
数据库端SQL语法的迁移
以下为常用的SQL语法迁移,包括数据类型、ID列向SEQUENCE迁移、表(主键、外键、CHECK、UNIQUE、DEFAULT、INDEX)、游标、存储过程、函数、触发器、常用SQL语法与函数几个方面,考虑SQL SERVER的实际情况,没有涉及ORACLE特有的PACKAGE、EXCEPTION等。
在以下的描述中,将SQL SERVER的TRANSACT-SQL简称为T-SQL。
在ORACLE中,其语法集称为PL/SQL。
<一> 数据类型的迁移
<1>、ORACLE端语法说明
在ORACLE中,分析其数据类型,大致可分为数字、字符、日期时间和特殊四大类。
其中,数字类型有NUMBER;字符类型有CHAR与VARCHAR2;日期时间类型只有DATE一种;除此之外,LONG、RAW、LONG RAW、BLOB、CLOB和BFILE等数据类型都可视为特殊数据类型。
<2>、SQL SERVER端语法说明
在SQL SERVER中,参照上面对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、BINARY VARING等数据类型都可视为特殊数据类型。
<3>、从SQL SERVER向ORACLE的迁移方案
比较ORACLE与SQL SERVER在数据类型上的不同,当从SQL SERVER向ORACLE迁移时,可以做如下调整:
SQL SERVER<=> ORACLE
数字类型
DECIMAL[(P[, S])]<=> NUMBER[(P[, S])]
NUMERIC[(P[, S])]<=> NUMBER[(P[, S])]
FLOAT[(N)]<=> NUMBER[(N)]
INT<=> NUMBER
SMALLINT<=> NUMBER
TINYINT<=> NUMBER
MONEY<=> NUMBER[19,4]
SMALLMONEY<=> NUMBER[19,4]
字符类型
CHAR[(N)]<=> CHAR[(N)]
VARCHAR[(N)]<=> VARCHAR2[(N)]
日期时间类型
DATETIME<=> DATE
SMALLDATETIME<=> DATE
其它
TEXT<=> CLOB
IMAGE<=> BLOB
BIT<=> NUMBER
(1)
方法:
公司原系统中的Money 用于金额时转换用number(14,2);用于单价时用 number(10,4)代替;
<二> ID列向SEQUENCE迁移
<1>、SQL SERVER端语法说明
在SQL SERVER中,可以将数据库中的某一字段定义为IDENTITY列以做主键识别,如:
jlbh numeric(12,0) identity(1,1) /*记录编号字段*/
CONSTRAINT PK_tbl_example PRIMARY KEY nonclustered (jlbh) /*主键约束*/
在这里,jlbh是一个ID列,在向具有该列的表插入记录时,系统将从1开始以1的步长自动对jlbh的值进行维护。
<2>、ORACLE端语法说明
但在ORACLE中,没有这样的ID列定义,而是采用另一种方法,即创建SEQUENCE。
如:
/*--1、创建各使用地区编码表--*/
drop table LT_AREA;
create table LT_AREA
( area_id number(5,0) NOT NULL, /*地区编码*/
area_name varchar2(20) NOT NULL, /*地区名称*/
constraint PK_LT_AREA PRIMARY KEY(area_id)
);
/*--2、创建SEQUENCE,将列area_id 类ID化--*/
drop sequence SEQ_LT_AREA;
create sequence SEQ_LT_AREA increment by 1 /*该SEQUENCE以1的步长递增*/
start with 1 maxvalue 99999; /*从1开始,最大增长到99999*/
/*--3、实际操作时引用SEQUENCE的下一个值--*/
insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '深圳');
insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '广州');
insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '北京');
/*--4、新插入连续三条记录后,下一条语句运行后,‘上海’地区的area_id为4--*/
insert into LT_AREA(area_id, area_name) values(SEQ_LT_AREA.NEXTVAL, '上海');
<3>、从SQL SERVER向ORACLE的迁移方案
根据以上分析,当从SQL SERVER向ORACLE迁移时,可以做如下调整:
1、去掉建表语句中有关ID列的identity声明关键字;
2、创建SEQUENCE,将此SEQUENCE与需类ID化的列对应;
3、在INSERT语句中对相应列引用其SEQUENCE值:
SEQUENCENAME.NEXTVAL
实际上,处理以上情况在ORACLE中采用的方法为对有自动增长字段的表增加一插入前触发器(具体资料见后“触发器”一节),如下:
CREATE OR REPLACE TRIGGER GenaerateAreaID
BEFORE INSERT ON LT_AREA
FOR EACH ROW
Select SEQ_LT_AREA.NEXTVAL INTO :
NEW.ID FROM DUAL;
BEGIN
END GenaerateAreaID;
GenaerateAreaID实际上修改了伪记录:
new的area_id值。
:
new最有用的一个特性----当该语句真正被执行时,:
new中的存储内容就会被使用。
所以系统每次都能自动生成新的号码。
<三> 表(主键、外键、CHECK、UNIQUE、DEFAULT、INDEX)
<1>、SQL SERVER端语法说明
有如下SQL SERVER语句:
/* ------------------------ 创建employee 表------------------------ */
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = ‘employee’
AND TYPE = ‘U’)
DROP TABLE employee
GO
CREATE TABLE employee
( emp_id empid /*empid为用户自定义数据类型*/
/*创建自命名主键约束*/
CONSTRAINT PK_employee PRIMARY KEY NONCLUSTERED
/*创建自命名CHECK约束*/
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* CHECK约束说明:
Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 to 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char
(1) NULL,
lname varchar(30) NOT NULL,
ss_id varchar(9) UNIQUE, /*创建唯一性约束*/
job_id smallint NOT NULL DEFAULT 1, /*设定DEFAULT值*/
job_lvl tinyint DEFAULT 10, /*设定DEFAULT值*/
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL DEFAULT ('9952') /*设定DEFAULT值*/
REFERENCES publishers(pub_id), /*创建系统命名外键约束*/
/* By default, the Parent Company Publisher is the company to whom each employee reports. */
hire_date datetime NOT NULL DEFAULT (getdate()), /*设定DEFAULT值*/
/* By default, the current system date will be entered. */
CONSTRAINT FK_employee_job FOREIGN KEY (job_id)
REFERENCES jobs(job_id) /*创建自命名外键约束*/
)
GO
/* --------------------- 创建employee表上的index --------------------- */
IF EXISTS (SELECT 1 FROM sysindexes WHERE name = 'emp_pub_id_ind')
DROP INDEX employee. emp_pub_id_ind
GO
CREATE INDEX emp_pub_id_ind ON employee(pub_id)
GO
<2>、ORACLE端语法说明
在ORACLE端的语法如下:
/* ---------------------- 创建employee 表---------------------- */
DROP TABLE employee;
CREATE TABLE employee
( emp_id varchar2(9) /*根据用户自定义数据类型的定义调整为varchar2(9)*/
/*创建自命名主键约束*/
CONSTRAINT PK_employee PRIMARY KEY NONCLUSTERED
/*创建自命名CHECK约束*/
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* CHECK约束说明:
Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 to 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar2(20) NOT NULL,
minit varchar2
(1) NULL,
lname varchar2(30) NOT NULL,
ss_id varchar2(9) UNIQUE, /*创建唯一性约束*/
job_id number(5,0) NOT NULL DEFAULT 1, /*设定DEFAULT值*/
/*这里考虑了SMALLINT的长度,也可调整为number*/
job_lvl number(3,0) DEFAULT 10, /*设定DEFAULT值*/
/*这里考虑了TINYINT的长度,也可调整为number*/
/* Entry job_lvl for new hires. */
pub_id varchar2(4) NOT NULL DEFAULT ('9952') /*设定DEFAULT值*/
REFERENCES publishers(pub_id), /*创建系统命名外键约束*/
/* By default, the Parent Company Publisher is the company to whom each employee reports. */
hire_date date NOT NULL DEFAULT SYSDATE, /*设定DEFAULT值*/
/*这里,SQL SERVER的getdate()调整为ORACLE的SYSDATE*/
/* By default, the current system date will be entered. */
CONSTRAINT FK_employee_job FOREIGN KEY (job_id)
REFERENCES jobs(job_id) /*创建自命名外键约束*/
);
/* -------------------- 创建employee表上的index -------------------- */
DROP INDEX employee. emp_pub_id_ind;
CREATE INDEX emp_pub_id_ind ON employee(pub_id);
<3>、从SQL SERVER向ORACLE的迁移方案
比较这两段SQL代码,可以看出,在创建表及其主键、外键、CHECK、UNIQUE、DEFAULT、INDEX时,SQL SERVER 与ORACLE的语法大致相同,但时迁移时要注意以下情况:
(1) Oracle定义表字段的default属性要紧跟字段类型之后,如下:
Create table MZ_Ghxx
( ghlxh number primay key ,
rq date default sysdate not null,
….
)
而不能写成
Create table MZ_Ghxx
( ghlxh number primay key ,
rq date not null default sysdate,
….
)
(2)T-SQL定义表结构时,如果涉及到用默认时间和默认修改人员,全部修改如下:
ZHXGRQ DATE DEFAULT SYSDATE NULL,
ZHXGR CHAR(8) DEFAULT ‘FUTIAN’ NULL,
(3)如表有identity定段,要先将其记录下来,建完表之后,马上建相应的序列和表触发器,并作为记录。
<四> 游标
<1>、SQL SERVER端语法说明
1、DECLARE CURSOR语句
语法:
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_list ]}]
例:
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors WHERE au_lname LIKE ‘B%’
ORDER BY au_lname, au_fname
2、OPEN语句
语法:
OPEN cursor_name
例:
OPEN authors_cursor
3、FETCH语句
语法:
FETCH [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ]
FROM cursor_name
[INTO @variable_name1, @variable_name2,… ]
例:
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
4、CLOSE语句
语法:
CLOSE cursor_name
例:
CLOSE authors_cursor
5、DEALLOCATE语句
语法:
DEALLOCATE cursor_name
例:
DEALLOCATE authors_cursor
6、游标中的标准循环与循环终止条件判断
(1)FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname
(2)-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT "Author:
" + @au_fname + " " + @au_lname
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname
END
(3)CLOSE authors_cursor
7、隐式游标
MSSqlServer中对于数据操纵语句受影响的行数,有一个全局的变量:
@@rowcount,其实它是一个隐式的游标,它记载了上条数据操纵语句所影响的行数,当@@rowcount小于1时,表时,上次没有找到相关的记录,如下:
Update students set lastname = ‘John’ where student_id = ‘301’
If @@rowcount < 1 then
Insert into students values (‘301’,’stdiv’,’john’,’996-03-02’)
表示如果数据表中有学号为“301”的记录,则修改其名字为“John”,如果找不到相应的记录,则向数据库中插入一条“John”的记录。
8、示例:
-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors WHERE au_lname LIKE‘B%’ORDER BY au_lname, au_fname
OPEN authors_cursor
-- Perform the first fetch and store the values in variables.
-- Note:
The variables
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 从sql server 向oracle 8迁移的技术实现方案 sql oracle 迁移 技术 实现 方案