当前位置:Gxlcms > mysql > oracle主键自增及存储过程的实现

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

时间:2021-07-01 10:21:17 帮助过:147人阅读

要用到触发器:

  

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

人气教程排行