oracle主键自增及存储过程的实现
要用到触发器:
drop sequence users_tb_seq;
create sequence users_tb_seq minvalue 1 maxvalue 99999
increment by 1
start with 1;
create or replace trigger users_tb_tri
before insert on users
for each row
begin
select users_tb_seq.nextval into :new.user_id from dual;
end;
/
commit;
插入测试:(用到存储过程)
SET SERVEROUTPUT ON
create or replace procedure insertUser(
u_name in varchar2,
u_pass in varchar2,
u_per in number,
u_email in varchar2
) as
begin
insert into users(user_name,user_pass,user_per,user_email)
values(u_name,u_pass,u_per,u_email);
exception
when dup_val_on_index then
dbms_output.put_line(‘重复的用户编号’);
when rowtype_mismatch then
dbms_output.put_line(‘输入的字符类型不匹配’);
when others then
dbms_output.put_line(‘发生其他错误’);
end insertUser;
执行一下:
execute insertUser(‘xhot’,'42000′,1,’xhot@maoegg.com’);