CREATE TABLE "user" (
"UID" tinyint(4) NOT NULL AUTO_INCREMENT,
"Account" varchar(20) NOT NULL,
"Password" varchar(50) NOT NULL,
"Role" tinyint(4) NOT NULL,
"PerferListCount" tinyint(4) NOT NULL DEFAULT '20',
PRIMARY KEY ("UID")
)
这是一个mysql的sql语句,我想把它转成oracle的sql语句。请问怎么转?希望能把代码贴出来。
难点:1.”UID“自增长 2.设定default值。可能要用到触发器和序列。
"UID" int(4) NOT NULL PRIMARY KEY ,
"Account" varchar2(20) NOT NULL,
"Password" varchar2(50) NOT NULL,
"Role" int(4) NOT NULL,
"PerferListCount" tinyint(4) NOT NULL DEFAULT '20'
)
增加一个序列:create sequence seq_test
start with 1
minvalue 1
maxvalue 999999999999
increment by 1;
default不是问题,oracle支持,"UID" int(4) NOT NULL PRIMARY KEY ,
"PerferListCount" tinyint(4) NOT NULL DEFAULT '20'
上面两个数据类型需要换成oracle的number”UID“自增长需要定义一个sequence先,然后定义一个"user"表的before insert的trigger
其中:new.id := seq_test.nextval;同时最好把表名换个名字,user是关键字同时把ddl语句里的双引号都去掉,否则sql也要加双引号,麻烦
code:create table "BadDut.user"
(
"UID" NUMBER(4) not null,
"Account" VARCHAR2(20) not null,
"Password" VARCHAR2(50) not null,
"Role" NUMBER(4) not null,
"PerferListCount" NUMBER(4) not null DEFAULT "20",
PRIMARY KEY ("UID")
)
怎么写?
(
"UID" NUMBER(4) not null,
"Account" VARCHAR2(20) not null,
"Password" VARCHAR2(50) not null,
"Role" NUMBER(4) not null,
"PerferListCount" NUMBER(4) DEFAULT 20 not null,
PRIMARY KEY ("UID")
);create sequence seq_test
start with 1
minvalue 1
maxvalue 9999
increment by 1;create trigger test_tri
before insert on "BadDut.user"
FOR EACH ROW
begin
:new."UID" := seq_test.nextval;
end;
/insert into "BadDut.user" values(null, 'a','b',1,2);
insert into user values(seq_test.nextvalue,.....)
insert into user(UID,Account,Password,Role) values(seq_test.nextval,'account','password',1)