NDB规范

文件命名规则:
create table:                                   tab_{schema}_{name}.sql
create Sequence:                           seq_{schema}_{name}.sql
create primary key:                pkuk_{schema}_{name}.sql
trigger:                                    tri_{schema}_{name}.sql
package:                                         pkgh_{schema}_{name}.sql
package body:                        pkgb_{schema}_{name}.sql

新建表
1. 新建表必须包含3个文件:
tab_{属主}_{表名}.sql  ---------建表\索引\赋权
pkuk_{属主}_{表名}.sql -------建索引指定为主键
seq_{属主}_{标名}.sql  --------创建产生主键的sequence
往siebel属主下新建表必须以lpms_开头
2. table/index/sequence必须加属主
唯一索引: IX_{表名缩写}_{字段缩写}
普通索引:IDX_{表名缩写}_{字段缩写}
3. 表注释,表列注释必须要有;
4. 必须包含CREATED_DATE,CREATED_BY, UPDATE_DATE,UPDATED_BY字段, CREATED_DATE,CREATED_BY必须not null
5. 必须给UPDATED_DATE单独赋默认值sysdate
6. table sequence必须创建同名同义词
7. table必须给R_WLTDATA_QRY  SIEBEL赋SELECT权限
8. 为统一规范,属主一律大写
9. 表结构修改一律追加到对应文件结尾,生产执行时只提取文件增加

存储过程:
必须以/结尾,少了部署平台不会提交脚本!

下面以wlt_score_info为例进行说明:
tab_wltdata_wlt_score_info.sql:
create table WLTDATA.WLT_SCORE_INFO
(
  ID_WLT_SCORE_INFO varchar2(100) not null,
  POINTS            number(10,2),
  MEMBERID          varchar2(32),
  LOGINNAME         varchar2(32),
  CHANNEL           varchar2(2),
  CREATED_DATE      date not null,
  CREATED_BY        varchar2(100) not null,
  UPDATED_DATE      date,
  UPDATED_BY        varchar2(100)                          -------------注意最后一列后面不能有空行,否则部署平台一定报错
);
-- Add comments to table必须有
comment on table WLTDATA.WLT_SCORE_INFO
  is '加积分明细';                                          --------------必须有
-- Add comments to the columns                         --------------必须有
comment on column WLTDATA.WLT_SCORE_INFO.ID_WLT_SCORE_INFO
  is '主键';
comment on column WLTDATA.WLT_SCORE_INFO.POINTS
  is '积分数';
comment on column WLTDATA.WLT_SCORE_INFO.MEMBERID
  is '会员编号';
comment on column WLTDATA.WLT_SCORE_INFO.LOGINNAME
  is '当前登陆者';
comment on column WLTDATA.WLT_SCORE_INFO.CHANNEL
  is '渠道';
comment on column WLTDATA.WLT_SCORE_INFO.CREATED_DATE
  is '创建时间';
comment on column WLTDATA.WLT_SCORE_INFO.CREATED_BY
  is '创建人';
comment on column WLTDATA.WLT_SCORE_INFO.UPDATED_BY
  is '修改人';
comment on column WLTDATA.WLT_SCORE_INFO.UPDATED_DATE
  is '修改时间';

---add default value to UPDATED_DATE
alter table  WLTDATA.WLT_SCORE_INFO modify UPDATED_DATE default sysdate;       ------------必须要有

---create synonym
create public synonym WLT_SCORE_INFO for WLTDATA.WLT_SCORE_INFO;              ------------wltdata属主必须要有同义词,siebel用户建不了

--create index
create unique index  WLTDATA.IX_ID_WLT_SCORE_INFO on WLTDATA.WLT_SCORE_INFO(ID_WLT_SCORE_INFO);

-- Grant/Revoke object privileges
grant select, insert, update, delete on WLTDATA.WLT_SCORE_INFO to WLTOPR; ------------必须要有

grant select, insert, update, delete on WLTDATA.WLT_SCORE_INFO to R_WLTDATA_DML; ------------必须要有
grant select on WLTDATA.WLT_SCORE_INFO to R_WLTDATA_QRY;                ------------必须要有
grant select on WLTDATA.WLT_SCORE_INFO to SIEBEL;                          ------------必须要有

grant select, insert, update, delete on WLTDATA.WLT_SCORE_INFO to LPMSREPCDE; ------------etl专用，如果该被etl使用时才授限，非必需
grant select, insert, update, delete on WLTDATA.WLT_SCORE_INFO to WLTJOB; ------------job专用，如果该被job使用时才授限，非必需

pkuk_wltdata_wlt_score_info.sql:
alter table WLTDATA.WLT_SCORE_INFO add constraint PK_WLT_SCORE_INFO_ID primary key (ID_WLT_SCORE_INFO) using index  WLTDATA.IX_ID_WLT_SCORE_INFO ;

seq_wltdata_wlt_score_info.sql:
create sequence WLTDATA.SEQ_SCORE_INFO minvalue 0 maxvalue 99999999999 start with 1 increment by 1 cache 40 cycle noorder;

---create synonym
CREATE PUBLIC SYNONYM SEQ_SCORE_INFO FOR  WLTDATA.SEQ_SCORE_INFO; ------------wltdata属主必须要有同义词,siebel用户建不了
-- Grant/Revoke object privileges
grant select on WLTDATA.SEQ_SCORE_INFO to wltopr;
grant select on WLTDATA.SEQ_SCORE_INFO to SIEBEL;------------siebel表要用时，才授权，非必需

先由组内审核，再提交给NDB审核组审核

麻烦大家以后提交NDB版本时,发送给刘增,丁纪和季阳光,Siebel属主的语句需要再发送给玉锋,由林信炫替大家提交到NDB版本,然后有季阳光在测试库执行

容易出错的点：
1.少属主
2.存储过程最后一行上 /
3.少CREATED_DATE,CREATED_BY, UPDATE_DATE,UPDATED_BY这4个字段
4.少UPDATED_DATE单独赋默认值sysdate
5.sql文件编码不能是UTF-8,是ANSI或者ISO-8859-1格式
6.package执行失败，因有&符号
7.命名错误
8.多空格和下划线
9.表名后面缺少;