Oracle 建立表 序列 注釋 主鍵

DECLARE
   iCount   number(2) := 0;
BEGIN
   SELECT COUNT (*) INTO iCount FROM   user_sequences u WHERE   u.sequence_name = 'SEQ_TCAPITALNOTIFY';
   IF iCount = 0
   THEN
      /*建立序列*/
      EXECUTE IMMEDIATE 'create sequence SEQ_TCAPITALNOTIFY minvalue 1 nomaxvalue start with 1 increment by 1';
   END IF;  
    SELECT COUNT (*) INTO iCount FROM   user_tables u WHERE   u.TABLE_NAME = 'TCAPITALNOTIFY';
   IF iCount = 0
   THEN
     /*建立表TCAPITALNOTIFY*/
      EXECUTE IMMEDIATE 'create table TCAPITALNOTIFY(
        L_SERIALNO               number(11)   not null primary key,
        D_DATE                   DATE  not null,
        C_CUSTNO                 varchar2(12)   not null,
        F_BALENCE                NUMBER(16,2),
        C_FUNDCODE               VARCHAR2(6),         
        D_REPLYPAYTIME           DATE,
        C_ORGCONTACT             VARCHAR2(2000),
        C_ORGTEL                 VARCHAR2(2000),
        D_NOTIFICATIONTIME       DATE,
        C_ISCERTIFICATE          CHAR(1),
        C_REALPAYTIME            DATE,
        C_MEMO                   CHAR(1),
        C_ISCANCEL               VARCHAR2(2000),
        C_CREATOR                VARCHAR2(16),
        D_CREATEDATE             DATE
       ) nologging tablespace crm_htable';
       EXECUTE IMMEDIATE 'comment on table  TCAPITALNOTIFY is ''資金通知情況表''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.L_SERIALNO is ''序號''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.D_DATE is ''日期''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_CUSTNO is ''賬戶名稱''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.F_BALENCE is ''購買資金（元）''';     
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_FUNDCODE is ''購買產品''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.D_REPLYPAYTIME is ''機構回覆資金劃撥時間''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_ORGCONTACT is ''機構連絡人''';     
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_ORGTEL is ''機構聯絡電話''';
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.D_NOTIFICATIONTIME is ''我方通知時間''';    
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_ISCERTIFICATE is ''是否提供劃款憑證''';     
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_REALPAYTIME is ''資金實際到賬時間''';     
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_ISCANCEL is ''撤單''';   
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_MEMO is ''備忘''';       
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.C_CREATOR is ''建立人''';      
       EXECUTE IMMEDIATE 'comment on column TCAPITALNOTIFY.D_CREATEDATE is ''建立日期''';
   END IF;   
END;
/

更多Oracle相關資訊見Oracle 專題頁面 http://www.bkjia.com/topicnews.aspx?tid=12