The realization of Oracle PRIMARY key self-increasing and stored procedure

To use a trigger:

　　

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

　　

Insert test: (using Stored procedures)

　

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< /p>

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 (' Duplicate user number ');

When Rowtype_mismatch then

Dbms_output.put_line (' type of character entered does not match ');

When others then

Dbms_output.put_line (' Other errors occurred ');

End Insertuser;

Execute:

Execute insertuser (' xhot ', ' 42000′,1, ' xhot@maoegg.com ');