テーブル作成 - lookworld @ 簡単メモ～

----------------------------------------------------------------------
-- 表：　名称
----------------------------------------------------------------------
①テーブル削除
DROP TABLE table_name CASCADE CONSTRAINTS;

②テーブル作成
CREATE TABLE table_name
(
testno CHAR(size) NOT NULL,
yyyymmdd NUMBER(size) NOT NULL,
col1 VARCHAR2(size) NOT NULL,
col2 VARCHAR2(size) ,
col3 VARCHAR2(size) ,
col4 VARCHAR2(size) ,
. ,
. ,
. ,
. ,
. ,
sysuserid CHAR(8) NOT NULL, --システム利用者ID
sysregdate DATE NOT NULL, --システム登録日時
sysupddate DATE NOT NULL, --システム更新日時
);

③テーブル作成後のカラム追加
ALTER TABLE table_name ADD CONSTRAINT PK_table_name
PRIMARY KEY
(
testno,
yyyymmdd
);


----------------------------------------------------------------------
-- 索引
----------------------------------------------------------------------
CREATE INDEX index_name ON table_name
(
col1,
col2,
col3
);

----------------------------------------------------------------------
-- 順序
----------------------------------------------------------------------
DROP SEQUENCE table_nameSEQ;

CREATE SEQUENCE table_nameSEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999
MINVALUE 1
CYCLE
CACHE 20
NOORDER;
----------------------------------------------------------------------
-- シノニム
----------------------------------------------------------------------
DROP SYNONYM synonym_name 

CREATE SYNONYM synonym_name FOR table_name