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

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

对应的PL/SQL语句:

-- Create sequence 
create sequence IN_OUT_SEQ
minvalue 0
maxvalue 9999999
start with 200
increment by 1
cache 20;

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

create or replace procedure SENDERINOUTREPORT_INSERT(
V_SENDER_ID in NVARCHAR2,
V_IN_BRANCH_ID in NVARCHAR2,
V_IN_REPORT_TIME in DATE,
V_IN_FLAG in NUMBER,
V_INDATE in DATE) is
v_num NUMBER:=0;
begin
  select count(IN_OUT_ID) into v_num from SENDERINOUTREPORT 
  where SENDER_ID=V_SENDER_ID and INDATE =to_date(to_char(V_INDATE,'yyyy-mm-dd'),'yyyy-mm-dd');
  if (v_num=0)  then
  insert into SENDERINOUTREPORT(IN_OUT_ID,SENDER_ID,IN_BRANCH_ID,IN_REPORT_TIME,
  IN_FLAG,INDATE) values(IN_OUT_SEQ.NEXTVAL,V_SENDER_ID,V_IN_BRANCH_ID,V_IN_REPORT_TIME,
  V_IN_FLAG,to_date(to_char(V_INDATE,'yyyy-mm-dd'),'yyyy-mm-dd'));
  end if;
end SENDERINOUTREPORT_INSERT;