Oracle常用语句

create table tbluser
(
    id number(8) primary key,
    username VARCHAR2(100) not null,
    sex VARCHAR2(2) default ‘男‘,
    age number(3),
    companyaddress VARCHAR2(1000),
    homeaddress varchar2(1000),
    phone varchar2(40),
    tel varchar2(40),
    remark varchar2(4000)
)

--第一种方法
create sequence tbluser_id start with 10001 increment by 1;

--第二种方法：
create sequence user_id
minvalue 1
maxvalue 9999999999999999999999999999
start with 10001 --从10001开始
increment by 1 -- 增量1
cache 20;

select * from user_sequences t where t.sequence_name=‘USER_ID‘;

create or replace trigger user_id_trigger 
before insert on tbluser 
for each row --一定要加上eac row 表示触发器适应于每一行
--when(new.id is null) --加和不加都行
declare mid number;
begin 
select user_id.nextval into mid from dual;
:new.id:=mid;
--上面两句也可以写成如下语句(取消定义mid)
--select user_id.nextval into:new.id from dual;
end;

select * from user_triggers t where t.trigger_name=‘USER_ID_TRIGGER‘;

create index idx_tbluser_id on tbluser(id);

select * from user_constraints;

alter table tbluser add constraint ID_PK primary key(ID);

COMMENT on column tbluser.id is ‘自动增长列‘;
COMMENT on column tbluser.username is ‘用户名‘;

alter table tbluser add
(
    usercode varchar2(40),
    userclass varchar2(40)
);

alter table tbluser add memo varchar2(400);