ORACLE 테이블 컬럼

테이블 생성

--< 테이블생성/삭제/PK/코멘트/롤/권한 부여 >
- DROP TABLE MYOWN.TB_XXX CASCADE CONSTRAINTS;
CREATE TABLE MYOWN.TB_XXX
(
	MNG_NO VARCHAR2(10 BYTE) NOT NULL,
	AGESON_MNG_NO NUMBER (22) NOT NULL,
	VLD_DT DATE,
	RGTR_ID VARCHAR2(10 BYTE) NOT NULL,
	REG_DT 	DATE DEFAULT SYSDATE NOT NULL,
	MDFR ID VARCHAR2(10 BYTE) NOT NULL,
	MDFCN_ DT DATE DEFAULT SYSDATE NOT NULL
)
TABLESPACE TS_XXX;

-- 테이블 코멘트
COMMENT ON TABLE MYOWN.TB_XXX IS 'AB_테이블';

-- 컬럼 코멘트
COMMENT ON COLUNN MYOWN.TB_XXX.MNG_NO IS '관리번호 ';

- PK 생성1-UNIOUE INDEX 생성
CREATE UNIQUE INDEX MYOWN.PK_TB_XXX
ON MYOWN.테이블명(컬럼1, 컬럼2)
TABLESPACE TS_명;
- PK 생성2-PK 제약조건 지정
ALTER TABLE MYOWN.TB_XXX ADD (
CONSTRAINT PK_TB_XX PRIMARY KEY (컬럼1, 컬럼2)
USING INDEX MYOWN.PK_TB_XXX);

-- 시노님 생성
CREATE OR REPLACE PUBLIC SYNONYM TB_XXX FOR MYOWN.TB_XXX;

-- 롤에 권부여
GRANT DELETE, INSERT, SELECT, UPDATE ON MYOWN.TB_XXX TO RL_ABALL;
GRANT DELETE, INSERT, SELECT, UPDATE ON MYOWN.TB_XXX TO RL_IGWALL;
GRANT SELECT ON MYOWN.TB_XXX TO RL_IGWSEL;

테이블 생성

ct=--< 테이블생성/삭제/PK/코멘트/롤/권한 부여 > {ENTER}- DROP TABLE MYOWN.TB_XXX CASCADE CONSTRAINTS; {ENTER}CREATE TABLE MYOWN.TB_XXX {ENTER}({ENTER} MNG_NO VARCHAR2(10 BYTE) NOT NULL, {ENTER} AGESON_MNG_NO NUMBER (22) NOT NULL, {ENTER} VLD_DT DATE, {ENTER} RGTR_ID VARCHAR2(10 BYTE) NOT NULL, {ENTER} REG_DT DATE DEFAULT SYSDATE NOT NULL, {ENTER} MDFR ID VARCHAR2(10 BYTE) NOT NULL, {ENTER} MDFCN_ DT DATE DEFAULT SYSDATE NOT NULL {ENTER}) {ENTER}TABLESPACE TS_XXX; {ENTER} {ENTER}

ctc=--< 테이블 코멘트 >{ENTER}COMMENT ON TABLE MYOWN.TB_XXX IS 'AB_테이블';

ccc=--< 컬럼 코멘트 >{ENTER}COMMENT ON COLUNN MYOWN.TB_XXX.MNG_NO IS '관리번호 ';

cpk=--< PK 생성1-UNIOUE INDEX 생성 >{ENTER}CREATE UNIQUE INDEX MYOWN.PK_TB_XXX {ENTER}ON MYOWN.테이블명(컬럼1, 컬럼2) {ENTER}TABLESPACE TS_명; {ENTER}-- PK 생성2-PK 제약조건 지정 {ENTER}ALTER TABLE MYOWN.TB_XXX ADD ({ENTER}CONSTRAINT PK_TB_XX PRIMARY KEY (컬럼1, 컬럼2) {ENTER}USING INDEX MYOWN.PK_TB_XXX);

csy=--< 시노님 생성 >{ENTER}CREATE OR REPLACE PUBLIC SYNONYM TB_XXX FOR MYOWN.TB_XXX;

gr=--< 롤에 권부여 >{ENTER}GRANT DELETE, INSERT, SELECT, UPDATE ON MYOWN.TB_XXX TO RL_ABALL; {ENTER}GRANT DELETE, INSERT, SELECT, UPDATE ON MYOWN.TB_XXX TO RL_IGWALL; {ENTER}GRANT SELECT ON MYOWN.TB_XXX TO RL_IGWSEL;