create table t_client (id number(4) primary key,
pid number(4) not null,
name varchar2(30) not null,
client_id varchar2(10),
client_level char(3),
bank_acct_no varchar2(30),
contact_tel varchar2(30),
address varchar2(30),
zip_code varchar2(10),
is_leaf char(1) default 'y' check (is_leaf in('y','n')),
is_client char(1) default 'n' check (is_client in('y','n')
));//请问ID 主键自动递增的约束如何写 来完成创建表
pid number(4) not null,
name varchar2(30) not null,
client_id varchar2(10),
client_level char(3),
bank_acct_no varchar2(30),
contact_tel varchar2(30),
address varchar2(30),
zip_code varchar2(10),
is_leaf char(1) default 'y' check (is_leaf in('y','n')),
is_client char(1) default 'n' check (is_client in('y','n')
));//请问ID 主键自动递增的约束如何写 来完成创建表
假设关键字段为id,建一个序列,代码为:
create sequence seq_t_client_id
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache
order;
建解发器代码为:
CREATE OR REPLACE TRIGGER trg_t_client_id
BEFORE INSERT ON t_client
FOR EACH ROW
BEGIN
SELECT seq_t_client_id.nextval INTO :new.id FROM dual;
END;
/
String sql = "insert into t_client(pid, name, client_id, client_level, bank_acct_no, " +
"contact_tel, address, zip_code, is_leaf, is_client) " +
"values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
我也可以手动插入ID 我只是想知道如何自动递增ID 可以说的具体点吗 触发器和序列 看视频的时候没接触过 很迷茫 不知道怎么去操作啊
先建一个序列,如下
create sequence seq_t
start with 1
increment by 1; 然后插数据可以这么写:String sql = "insert into t_client(pid, name, client_id, client_level, bank_acct_no, " +
"contact_tel, address, zip_code, is_leaf, is_client) " +
"values(seq_t.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?)";则pid从1开始,每次自动加一
Increment by 1 增长值
Start with 1 启始值
Maxvalue nomaxvalue
Minvalue no minvalue
Cycle nocycle 循环
Nextval 下一个值
Currval 获取当前的序列中值
ORacle没有直接提供类似sqlserver和mysql这样的auto increment的功能,你可以用sequence结合trigger的方式实现。