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;