oracle 创建表序列注释主键

最新推荐文章于 2024-05-17 11:19:43 发布

原创最新推荐文章于 2024-05-17 11:19:43 发布 · 125 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

本文介绍了一个Oracle PL/SQL脚本，用于检查并创建序列SEQ_TCAPITALNOTIFY及表TCAPITALNOTIFY。该表记录了资金通知的情况，包括序列号、日期、账户名称等关键字段。

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;
/