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

最新推荐文章于 2025-06-18 19:37:39 发布

原创最新推荐文章于 2025-06-18 19:37:39 发布 · 2.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

数据库-oracle 专栏收录该内容

15 篇文章

订阅专栏

本文介绍如何使用SQL语句判断并操作数据库中的物流建议表，包括检查表是否存在并删除，以及创建表结构的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--判断表是否存在，如果存在则删除
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;