Oracle PL/SQL编程学习笔记：序列和自动编号

 首先在PL/SQL中创建一个序列，右键单击Sequences节点，点击【新建】，弹出对话框。

对应的PL/SQL语句:

1 -- Create sequence 
2 create sequence IN_OUT_SEQ
3 minvalue 0
4 maxvalue 9999999
5 start with 200
6 increment by 1
7 cache 20;

在存储过程中调用（黄色部分），这样就实现了字段的自动编号：

 1 create or replace procedure SENDERINOUTREPORT_INSERT(
 2 V_SENDER_ID in NVARCHAR2,
 3 V_IN_BRANCH_ID in NVARCHAR2,
 4 V_IN_REPORT_TIME in DATE,
 5 V_IN_FLAG in NUMBER,
 6 V_INDATE in DATE) is
 7 v_num NUMBER:=0;
 8 begin
 9   select count(IN_OUT_ID) into v_num from SENDERINOUTREPORT 
10   where SENDER_ID=V_SENDER_ID and INDATE =to_date(to_char(V_INDATE,'yyyy-mm-dd'),'yyyy-mm-dd');
11   if (v_num=0)  then
12   insert into SENDERINOUTREPORT(IN_OUT_ID,SENDER_ID,IN_BRANCH_ID,IN_REPORT_TIME,
13   IN_FLAG,INDATE) values(IN_OUT_SEQ.NEXTVAL,V_SENDER_ID,V_IN_BRANCH_ID,V_IN_REPORT_TIME,
14   V_IN_FLAG,to_date(to_char(V_INDATE,'yyyy-mm-dd'),'yyyy-mm-dd'));
15   end if;
16 end SENDERINOUTREPORT_INSERT;