Create simple table in Oracle

最新推荐文章于 2023-08-27 00:17:57 发布

最新推荐文章于 2023-08-27 00:17:57 发布 · 203 阅读

文章标签：

#Oracle #F#

Database 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一个名为COTS_TAB_BENE_LOOKUP_CODE的数据库表的创建过程及其结构。该表包括BRANCH、MSGTYPE等字段，并通过复合主键确保记录的唯一性。此外，还展示了如何为该表插入示例数据。

CREATE TABLE COTS_TAB_BENE_LOOKUP_CODE(
BRANCH         VARCHAR2(3 BYTE)              NOT NULL,
MSGTYPE        VARCHAR2(50 BYTE)              NOT NULL,
PARAM          VARCHAR2(30 BYTE)              NOT NULL,
VALUE          VARCHAR2(70 BYTE)                      ,
ACTIVE_IND     VARCHAR2(1 BYTE)              NOT NULL,
COTS_UPD_DATE DATE                                   ,
COTS_UPD_BY    VARCHAR2(18 BYTE)
--REMARK         VARCHAR2(30 BYTE)                    --(E FOR EMAIL,F FOR FAX)
)

--INDEX
CREATE UNIQUE INDEX COTS_PK_BENE_LOOKUP_CODE ON COTS_TAB_BENE_LOOKUP_CODE(BRANCH,MSGTYPE,PARAM);

--PRIMARY KEY
ALTER TABLE COTS_TAB_BENE_LOOKUP_CODE ADD (
CONSTRAINT COTS_PK_BENE_LOOKUP_CODE
PRIMARY KEY(BRANCH, MSGTYPE, PARAM)

)

CREATE PUBLIC SYNONYM COTS_TAB_BENE_LOOKUP_CODE FOR COTS_TAB_BENE_LOOKUP_CODE;

GRANT DELETE, INSERT, SELECT, UPDATE ON COTS_TAB_BENE_LOOKUP_CODE TO RCOT_APPS_ROLE;

GRANT SELECT ON COTS_TAB_BENE_LOOKUP_CODE TO RCOT_SELECT_ROLE;

--- Insert data to table,FOR EPO_FTBENE

INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','SOURCESYSTEM','FFTI','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','COTSUPDBY','BENEADVISING','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','DIR','/ucotreg1/wrk/output/712/','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','DELIVERYMEDIA','FAX','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','DELIVERYSTATUS','GENERATED','A','','')
INSERT INTO COTS_TAB_BENE_LOOKUP_CODE VALUES(712,'FTBNADV','FILENAME_EMAILMSG','','A','','')

SELECT * FROM COTS_TAB_BENE_LOOKUP_CODE WHERE BRANCH=712 AND MSGTYPE='FTBNADV'

--- INSERT DATA TO TABLE,FOR EMAIL BENE ADV,HONGYI WILL UPDATE....