Oracle数据库的学习

1)字段方式：VARCHAR2(50) 、NUMBER、DATE、INTEGER、 CHAR(10)
2)时间默认值：OPRTIME DATE DEFAULT SYSDATE NOT NULL ; CALL_TIME datetime DEFAULT getdate() NOT NULL,
3)建立索引：create index IDX_ACCOUNTCHANGE on ACCOUNT_CHANGEHISTORY (EMAIL ASC);
create unique index IDX_VOXEMAIL_ACCOUNT on VOXEMAIL (VOXACCOUNT ASC);
CREATE INDEX IDX_CALLER_CALLED ON dbo.PLATFORM_X_INFO(X_CALLER,X_CALLED)
4)建立表的主键：create table AREATABLE (
AREACODE VARCHAR2(20) NOT NULL ,
AREANAME VARCHAR2(20) NOT NULL ,
CONSTRAINT PK_AREATABLE PRIMARY KEY (AREACODE))
5)添加外键：
alter table FAX_ACCOUNTDATA
add CONSTRAINT FK_FAX_ACCO_REFERENCE_FAXUSER_ foreign KEY (USERCLASS)
references FAXUSER_TYPE (USERCLASS)

CREATE TABLE dbo.SMS_RETREC_LOG
(
MSGID varchar(30) NOT NULL,
SMSID numeric(15,0) NOT NULL,
PRIMARY KEY CLUSTERED (MSGID),
CONSTRAINT FK_SMS_RETR_REFERENCE_SMS_SEND
FOREIGN KEY (SMSID)
REFERENCES dbo.SMS_SEND_RECORD (SMSID)
)
6)自动增长的表字段： rowid numeric(10,0) IDENTITY,
7)创建触发器：--创建触发器
CREATE OR REPLACE TRIGGER INSERT_CORPHB
before insert on CORPHB for each row
begin
select SEQ_CORPHB.nextval into :new.userid from dual;
end;

8.create table WF_SCHEDULE
(
SCHEDULEID INTEGER not null,
SCHEDULENAME VARCHAR2(100) not null,
CREATEDATE DATE default sysdate not null,
constraint pk_WF_SCHEDULE primary key (SCHEDULEID)
);


create sequence WF_SCHEDULE_TB_SEQ
minvalue 1
maxvalue 99999999
start with 281
increment by 1
cache 20;


CREATE OR REPLACE TRIGGER INSERT_WF_SCHEDULE
before insert on WF_SCHEDULE for each row
begin
select WF_SCHEDULE_TB_SEQ.nextval into :new.SCHEDULEID from dual;
end;