欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle主键自增及存储过程的实现

程序员文章站 2024-02-15 16:40:34
...

要用到触发器:

  

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’);