请教一下我有一段Oracle脚本急需转换为SQL Server脚本
有人能帮忙告诉一下它们之间的区别吗
需要改的地方
如tablespace 怎么改----------------------------------------
Oracle脚本如下
---------------------------------------------------------------------------
------------------------------------------------------
-- Export file for user SBDJ --
-- Created by Administrator on 2005-03-11, 21:39:21 --
------------------------------------------------------create table T_BDATA
(
PERNO VARCHAR2(4) not null,
CHECKNUM NUMBER not null,
MSRNO NUMBER not null,
MSRVALUE VARCHAR2(20),
UDATE DATE,
CDATE DATE,
CBZ VARCHAR2(50)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);create table T_CHECKPT
(
EUPETNNO VARCHAR2(4),
NPARTNO VARCHAR2(4),
MSRNNO VARCHAR2(4),
MSRNO NUMBER not null,
MSRITEM VARCHAR2(20),
DWV NUMBER,
UWV NUMBER,
IB VARCHAR2(15),
PCDATE DATE,
PCLINE NUMBER,
MSRNAME VARCHAR2(30)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_CHECKPT
add constraint MSRNO primary key (MSRNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_DEPT
(
DEPTNO VARCHAR2(2) not null,
DEPTNAME VARCHAR2(10)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_DEPT
add constraint DEPTNO primary key (DEPTNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_EMPLOYEE
(
NO VARCHAR2(4) not null,
USERNAME VARCHAR2(20),
PASSWORD VARCHAR2(20),
PURVIEW NUMBER,
DEPTNO VARCHAR2(2),
PREFESSION VARCHAR2(20),
REMARK VARCHAR2(50),
PLANNO VARCHAR2(2),
PURVIEW2 VARCHAR2(10)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_EMPLOYEE
add constraint NO primary key (NO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);
insert into t_Employee (no,Username,Password,Purview,Purview2) values ('0001','Admin','123',1,'高级用户');create table T_EUPET
(
EUPETNNO VARCHAR2(4) not null,
EUPETNO VARCHAR2(20),
EUPETNAME VARCHAR2(30)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_EUPET
add constraint EUPETNNO primary key (EUPETNNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_NCPT
(
MSRNO NUMBER not null,
PERNO VARCHAR2(4) not null,
CHECKNUM NUMBER not null,
UDATE DATE,
MEMO VARCHAR2(100)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_NCPT
add constraint KNCPT primary key (MSRNO, CHECKNUM, PERNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_PART
(
NPARTNO VARCHAR2(4) not null,
PARTNAME VARCHAR2(50) not null,
EUPETNNO VARCHAR2(4) not null,
IB VARCHAR2(20),
PARTNO VARCHAR2(20),
IBMEMO VARCHAR2(20)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
comment on column T_PART.NPARTNO
is '部件内码';
comment on column T_PART.PARTNAME
is '部件名';
comment on column T_PART.EUPETNNO
is '机组';create table T_PERPLAN
(
MSRNO NUMBER not null,
PLANNO VARCHAR2(2) not null,
PERDATE DATE,
SPECIALTY VARCHAR2(2) not null,
PLANSORTID NUMBER
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);create or replace view abc as
select T_employee.No 人员编号,T_employee.Username 用户名称,T_employee.Purview 用户权限,T_employee.Purview2 用户权限2,T_employee.Deptno 部门编号,T_dept.Deptname 部门名称,T_employee.prefession 专业 ,T_employee.re 备注
from T_dept,T_employee where T_dept.Deptno=T_employee.Deptno
/create or replace view view_employee as
select T_employee.*,
T_dept.Deptname
from T_dept,T_employee where T_dept.Deptno=T_employee.Deptno
/create or replace view view_sbdj_part1 as
select
T_part.Partno,T_part.Partname,T_part.Ib,T_part.Ibmemo,
tc.npartno,tc.eupetnno,tc.msrnno,tc.msrno,tc.msritem,tc.dwv,tc.uwv,tc.pcdate,tc.pcline,tc.msrname
from T_part,T_checkpt tc
where tc.npartno=T_part.npartno and tc.Eupetnno=T_part.Eupetnno
/create or replace view view_sbdj as
select t_Eupet.Eupetno,t_Eupet.Eupetname,
view_sbdj_part1.*
from t_Eupet,view_sbdj_part1
where t_Eupet.Eupetnno=view_sbdj_part1.Eupetnno
create or replace view view_sbdj_plandate as
select e.no,e.username,e.deptno,e.prefession,e.re,
e.planno,p.msrno,p.perdate,p.plansortid
from t_Employee e,t_Perplan p where e.deptno=p.specialty and e.planno=p.planno
/create or replace view view_ncpt as
select c.eupetno,c.eupetname,c.partno,c.partname,a.username,
c.msrno,c.msrname,c.msritem,b.perno,b.checknum,b.udate,b.memo from view_sbdj_plandate a,
T_ncpt b,view_sbdj c where a.msrno=b.msrno
and c.msrno=b.msrno and b.perno=a.nocreate or replace view view_sbdjvalue as
select a.*,b.perno,
b.msrvalue,b.udate,b.cdate,
b.cbz
from view_sbdj a,T_bdata b
where a.msrno=b.msrno
/--
-- Creating view VIEW_NEWSBDJVALUE_PER
-- ===================================
--
create or replace view view_newsbdjvalue_per as
select a.*,
"EUPETNO","EUPETNAME","PARTNO","PARTNAME","EUPETNNO","NPARTNO","MSRNNO","MSRNO","MSRITEM",
"DWV","UWV","IB","PCDATE","PCLINE","MSRNAME","PERNO","MSRVALUE","UDATE","CDATE",
"CBZ" from view_employee a,view_sbdjvalue b where a.no=b.perno
--"人员编号","用户名称","用户权限","部门编号","部门名称","专业","备注"
/create or replace view view_perplan as
select t_employee.no,t_employee.planno,T_perplan.Msrno
from T_perplan,t_Employee where T_perplan.Planno=t_Employee.Planno
and T_perplan.Specialty=t_Employee.Deptnocreate or replace view view_percurplan as
select view_perplan.no,view_perplan.planno,view_sbdj.* from view_perplan,view_sbdj
where view_sbdj.msrno=view_perplan.Msrnocreate or replace view view_sbdjvalue_per as
select "人员编号","用户名称","用户权限","部门编号","部门名称","专业","备注",
"EUPETNO","EUPETNAME","PARTNAME","EUPETNNO","NPARTNO","MSRNNO","MSRNO","MSRITEM",
"DWV","UWV","IB","PCDATE","PCLINE","MSRNAME","PERNO","MSRVALUE","UDATE","CDATE",
"CBZ" from abc a,view_sbdjvalue b where a.人员编号=b.perno
有人能帮忙告诉一下它们之间的区别吗
需要改的地方
如tablespace 怎么改----------------------------------------
Oracle脚本如下
---------------------------------------------------------------------------
------------------------------------------------------
-- Export file for user SBDJ --
-- Created by Administrator on 2005-03-11, 21:39:21 --
------------------------------------------------------create table T_BDATA
(
PERNO VARCHAR2(4) not null,
CHECKNUM NUMBER not null,
MSRNO NUMBER not null,
MSRVALUE VARCHAR2(20),
UDATE DATE,
CDATE DATE,
CBZ VARCHAR2(50)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);create table T_CHECKPT
(
EUPETNNO VARCHAR2(4),
NPARTNO VARCHAR2(4),
MSRNNO VARCHAR2(4),
MSRNO NUMBER not null,
MSRITEM VARCHAR2(20),
DWV NUMBER,
UWV NUMBER,
IB VARCHAR2(15),
PCDATE DATE,
PCLINE NUMBER,
MSRNAME VARCHAR2(30)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_CHECKPT
add constraint MSRNO primary key (MSRNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_DEPT
(
DEPTNO VARCHAR2(2) not null,
DEPTNAME VARCHAR2(10)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_DEPT
add constraint DEPTNO primary key (DEPTNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_EMPLOYEE
(
NO VARCHAR2(4) not null,
USERNAME VARCHAR2(20),
PASSWORD VARCHAR2(20),
PURVIEW NUMBER,
DEPTNO VARCHAR2(2),
PREFESSION VARCHAR2(20),
REMARK VARCHAR2(50),
PLANNO VARCHAR2(2),
PURVIEW2 VARCHAR2(10)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_EMPLOYEE
add constraint NO primary key (NO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);
insert into t_Employee (no,Username,Password,Purview,Purview2) values ('0001','Admin','123',1,'高级用户');create table T_EUPET
(
EUPETNNO VARCHAR2(4) not null,
EUPETNO VARCHAR2(20),
EUPETNAME VARCHAR2(30)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_EUPET
add constraint EUPETNNO primary key (EUPETNNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_NCPT
(
MSRNO NUMBER not null,
PERNO VARCHAR2(4) not null,
CHECKNUM NUMBER not null,
UDATE DATE,
MEMO VARCHAR2(100)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table T_NCPT
add constraint KNCPT primary key (MSRNO, CHECKNUM, PERNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);create table T_PART
(
NPARTNO VARCHAR2(4) not null,
PARTNAME VARCHAR2(50) not null,
EUPETNNO VARCHAR2(4) not null,
IB VARCHAR2(20),
PARTNO VARCHAR2(20),
IBMEMO VARCHAR2(20)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
comment on column T_PART.NPARTNO
is '部件内码';
comment on column T_PART.PARTNAME
is '部件名';
comment on column T_PART.EUPETNNO
is '机组';create table T_PERPLAN
(
MSRNO NUMBER not null,
PLANNO VARCHAR2(2) not null,
PERDATE DATE,
SPECIALTY VARCHAR2(2) not null,
PLANSORTID NUMBER
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);create or replace view abc as
select T_employee.No 人员编号,T_employee.Username 用户名称,T_employee.Purview 用户权限,T_employee.Purview2 用户权限2,T_employee.Deptno 部门编号,T_dept.Deptname 部门名称,T_employee.prefession 专业 ,T_employee.re 备注
from T_dept,T_employee where T_dept.Deptno=T_employee.Deptno
/create or replace view view_employee as
select T_employee.*,
T_dept.Deptname
from T_dept,T_employee where T_dept.Deptno=T_employee.Deptno
/create or replace view view_sbdj_part1 as
select
T_part.Partno,T_part.Partname,T_part.Ib,T_part.Ibmemo,
tc.npartno,tc.eupetnno,tc.msrnno,tc.msrno,tc.msritem,tc.dwv,tc.uwv,tc.pcdate,tc.pcline,tc.msrname
from T_part,T_checkpt tc
where tc.npartno=T_part.npartno and tc.Eupetnno=T_part.Eupetnno
/create or replace view view_sbdj as
select t_Eupet.Eupetno,t_Eupet.Eupetname,
view_sbdj_part1.*
from t_Eupet,view_sbdj_part1
where t_Eupet.Eupetnno=view_sbdj_part1.Eupetnno
create or replace view view_sbdj_plandate as
select e.no,e.username,e.deptno,e.prefession,e.re,
e.planno,p.msrno,p.perdate,p.plansortid
from t_Employee e,t_Perplan p where e.deptno=p.specialty and e.planno=p.planno
/create or replace view view_ncpt as
select c.eupetno,c.eupetname,c.partno,c.partname,a.username,
c.msrno,c.msrname,c.msritem,b.perno,b.checknum,b.udate,b.memo from view_sbdj_plandate a,
T_ncpt b,view_sbdj c where a.msrno=b.msrno
and c.msrno=b.msrno and b.perno=a.nocreate or replace view view_sbdjvalue as
select a.*,b.perno,
b.msrvalue,b.udate,b.cdate,
b.cbz
from view_sbdj a,T_bdata b
where a.msrno=b.msrno
/--
-- Creating view VIEW_NEWSBDJVALUE_PER
-- ===================================
--
create or replace view view_newsbdjvalue_per as
select a.*,
"EUPETNO","EUPETNAME","PARTNO","PARTNAME","EUPETNNO","NPARTNO","MSRNNO","MSRNO","MSRITEM",
"DWV","UWV","IB","PCDATE","PCLINE","MSRNAME","PERNO","MSRVALUE","UDATE","CDATE",
"CBZ" from view_employee a,view_sbdjvalue b where a.no=b.perno
--"人员编号","用户名称","用户权限","部门编号","部门名称","专业","备注"
/create or replace view view_perplan as
select t_employee.no,t_employee.planno,T_perplan.Msrno
from T_perplan,t_Employee where T_perplan.Planno=t_Employee.Planno
and T_perplan.Specialty=t_Employee.Deptnocreate or replace view view_percurplan as
select view_perplan.no,view_perplan.planno,view_sbdj.* from view_perplan,view_sbdj
where view_sbdj.msrno=view_perplan.Msrnocreate or replace view view_sbdjvalue_per as
select "人员编号","用户名称","用户权限","部门编号","部门名称","专业","备注",
"EUPETNO","EUPETNAME","PARTNAME","EUPETNNO","NPARTNO","MSRNNO","MSRNO","MSRITEM",
"DWV","UWV","IB","PCDATE","PCLINE","MSRNAME","PERNO","MSRVALUE","UDATE","CDATE",
"CBZ" from abc a,view_sbdjvalue b where a.人员编号=b.perno
想问的是:
(1) CreateTable 和 tablespace 怎么改为SQL Server的语句,是不是不要后者就可以?
(2) alter table 语句中storage什么意思?
(3) comment on 是备注么?很急!!!!请各位帮帮忙!谢谢!!!!
create table T_DEPT
(
DEPTNO VARCHAR2(2) not null,
DEPTNAME VARCHAR2(10)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create table T_BDATA
(
PERNO VARCHAR2(4) not null,
CHECKNUM NUMBER not null,
MSRNO NUMBER not null,
MSRVALUE VARCHAR2(20),
UDATE DATE,
CDATE DATE,
CBZ VARCHAR2(50)
)
tablespace SBDJ_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);alter table T_CHECKPT
add constraint MSRNO primary key (MSRNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);comment on column T_PART.NPARTNO
is '部件内码';-- Creating view VIEW_NEWSBDJVALUE_PER
create table T_DEPT
(
DEPTNO VARCHAR2(2) not null,
DEPTNAME VARCHAR2(10)
) 即可!
2、storage中是Oracle的块和区的初始值和扩展值设置。SQL Server不需要设置,只有固定大小的页。你直接使用:alter table T_CHECKPT
add constraint MSRNO primary key (MSRNO)
这些就行了。
pctfree 10
initrans 2
maxtrans 255
也是Oracle独有的。3、comment on 是备注么?
是的。