从SQL SERVER 向ORACLE 8迁移的技术实现方案

<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
/*这里考虑了SMALLINT的长度，也可调整为number*/
DEFAULT 1, /*设定DEFAULT值*/
job_lvl number(3,0)
/*这里考虑了TINYINT的长度，也可调整为number*/
DEFAULT 10, /*设定DEFAULT值*/
/* 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);