sql语句创建表，索引， sequence。

最新推荐文章于 2025-06-30 15:49:19 发布

原创最新推荐文章于 2025-06-30 15:49:19 发布 · 3.4k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #null #table #date #cache

ORACLE 专栏收录该内容

76 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中创建两张表t_inspection_d和t_inspection，并为它们配置了主键约束及外键引用。同时，创建了两个序列t_inspection_seq和t_inspection_d_seq用于自动填充主键字段，并设置了两个触发器实现插入操作时自动获取序列值。

SQL> create table t_inspection_d
2 (id number(10) not null constraint pk_t_inspection_d primary key,
3 inspection_code varchar2(3),inspectionflag number(1),
4 staffcode varchar2(6),loggingdate DATE default SYSDATE)
5 tablespace cosf;

Table created.

SQL> create table t_inspection
2 (id number(10) not null constraint pk_t_truckinspection primary key,
3 lccode varchar2(6),truckcode varchar2(8),
4 inspection_did number(10) not null,
5 loggingdate DATE default SYSDATE,
6 constraint fk_t_inspection foreign key(inspection_did) references
7 t_inspection_d(id))
8 tablespace cosf;

Table created.

SQL> create sequence t_inspection_seq increment by 1
2 start with 1
3 nomaxvalue nocycle cache 20;

Sequence created.

SQL> create sequence t_inspection_d_seq increment by 1
2 start with 1 maxvalue 9999999999 nocycle cache 20;

Sequence created.

SQL> create or replace trigger "t_inspection_id"
2 before
3 insert on "COSF"."T_INSPECTION" for each row
4 declare next_checkup_no number;
5 begin
6 select t_inspection_seq.nextval into next_checkup_no from dual;
7 :new.id := next_checkup_no;
9 end;
10 /
Trigger created.

SQL> create or replace trigger "t_inspection_d_id"
2 before
3 insert on "COSF"."T_INSPECTION_D" for each row
4 declare next_no number;
5 begin
6 select t_inspection_d_seq.nextval into next_no from dual;
7 :new.id := next_no;
8 end;
9 /

Trigger created.

===========================================

创建sequence

CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE -- 不建缓冲区
==================

创建索引：CREATE UNIQUE INDEX LY.HARD ON LY.HARD(hard_id);