oracle运用(二) oracle数据库完整建表语句

--判断表是否存在，如果存在则删除
declare
num
number;
begin
select count(1) into num from all_tables where TABLE_NAME = 'LOGISTICS_SUGGESTION';
--
and OWNER='SCOTT'
if
num=1
then
execute immediate 'drop table LOGISTICS_SUGGESTION';
end
if;
end;
-- Create table
CREATE TABLE LOGISTICS_SUGGESTION
(
id
NUMBER not null,
industry_id
NUMBER,
contact_name
VARCHAR2(128),
contact_phone
VARCHAR2(128),
handle_status
VARCHAR2(32),
suggestion_remark VARCHAR2(500),
handle_remark
VARCHAR2(500),
create_time
DATE default sysdate,
handle_time
DATE
);
--指定表空间(可以不指定)
tablespace DSW
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table LOGISTICS_SUGGESTION is '投诉建议表';
-- Add comments to the columns
comment on column LOGISTICS_SUGGESTION.id is '自增，主键';
comment on column LOGISTICS_SUGGESTION.industry_id is '行业类别';
comment on column LOGISTICS_SUGGESTION.contact_name is '联系人';
comment on column LOGISTICS_SUGGESTION.contact_phone is '联系方式';
comment on column LOGISTICS_SUGGESTION.handle_status is '处理状态（处理中WORKING、已处理FINISH）';
comment on column LOGISTICS_SUGGESTION.suggestion_remark is '投诉建议';
comment on column LOGISTICS_SUGGESTION.handle_remark is '处理备注';
comment on column LOGISTICS_SUGGESTION.create_time is '创建时间';
comment on column LOGISTICS_SUGGESTION.handle_time is '处理时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table LOGISTICS_SUGGESTION
add constraint PK_LOGISTICS_SUGGESTION primary key (ID)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create sequence
create sequence LOGISTICS_SUGGESTION_SEQ
minvalue 1
-- 最小值=1
maxvalue 999999999999999999999999999
-- 指定最大值
-- 或nomaxvalue
-- 没有最大值
-- NOCYCLE;
-- 不循环
start with 1
-- 从1开始
increment by 1
-- 每次递增1
cache 20;
--触发器
create or replace trigger LOGISTICS_SUGGESTION_TRI
before insert on LOGISTICS_SUGGESTION
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
begin
SELECT LOGISTICS_SUGGESTION_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
end;

最后

以上就是活力酒窝为你收集整理的oracle运用(二) oracle数据库完整建表语句的全部内容，希望文章能够帮你解决oracle运用(二) oracle数据库完整建表语句所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。