这里有新鲜出炉的精品教程,程序狗速度看过来!
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
Oracle 生成单据编号存储过程,在做订单类似的系统都可能会存在订单编号不重复,或是流水号按日,按年,按月进行重新编号。下面给大家分享 oracle 生成单据编号存储过程,需要的的朋友参考下吧
Oracle 生成单据编号存储过程,在做订单类似的系统都可能会存在订单编号不重复,或是流水号按日,按年,按月进行重新编号。
可以参考以下存储过程
- CREATE OR REPLACE procedure Pro_GetBillNO(TypeTable in varchar2, cur_mycursor out sys_refcursor) as DReceiptCode varchar2(40);
- DReceiptName varchar2(50);
- DPrefix1 varchar2(50);
- DISO varchar2(50);
- DIsAutoCreate varchar2(20);
- DPrefix2 varchar2(20);
- DPrefix3 varchar2(20);
- DDateValue date;
- DNO number;
- DLength number;
- DResetType number;
- DSeparator varchar2(20);
- DReturnValue varchar2(50);
- strSql varchar2(1000);
- begin DReturnValue: ='';
- select "ReceiptCode",
- "ReceiptName",
- "Prefix1",
- "ISO",
- "IsAutoCreate",
- "Prefix2",
- "Prefix3",
- "DateValue",
- "NO",
- "Length",
- "ResetType",
- "Separator"into DReceiptCode,
- DReceiptName,
- DPrefix1,
- DISO,
- DIsAutoCreate,
- DPrefix2,
- DPrefix3,
- DDateValue,
- DNO,
- DLength,
- DResetType,
- DSeparator from "SysReceiptConfig"where "ReceiptCode" = TypeTable;
- if to_number(DResetType) > 0 then
- if DIsAutoCreate = 1 THEN
- if DResetType = 1 then--按年份
- if to_number(to_char(sysdate, 'yyyy')) < >to_number(to_char(DDateValue, 'yyyy')) then update "SysReceiptConfig"set "NO" = 1,
- "DateValue" = to_date(sysdate) where "ReceiptCode" = TypeTable;
- else update "SysReceiptConfig"set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;
- end
- if; --年份end
- if; --DResetType = 1
- if DResetType = 2 then--按月份
- if to_number(to_char(sysdate, 'MM')) < >to_number(to_char(DDateValue, 'MM')) then update "SysReceiptConfig"set "NO" = 1,
- "DateValue" = to_date(sysdate) where "ReceiptCode" = TypeTable;
- else update "SysReceiptConfig"set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;
- end
- if; --月份end
- if; --DResetType = 2
- if DResetType = 3 then--按日
- if to_number(to_char(sysdate, 'dd')) < >to_number(to_char(DDateValue, 'dd')) then update "SysReceiptConfig"set "NO" = 1,
- "DateValue" = to_date(sysdate) where "ReceiptCode" = TypeTable;
- else update "SysReceiptConfig"set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;
- end
- if; --月份end
- if; --DResetType = 3
- else update "SysReceiptConfig"set "NO" = "NO" + 1 where "ReceiptCode" = TypeTable;
- end
- if; --DResetType end
- if;
- strSql: =' select * from "SysReceiptConfig" where 1=1 ';
- strSql: =strSql || ' and "ReceiptCode"=''' || TypeTable || '''';
- open cur_mycursor
- for strSql;
- end;
以上所述是小编给大家介绍的 Oracle 生成单据编号存储过程的实例代码,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 PHPERZ 网站的支持!
来源: http://www.phperz.com/article/17/0523/334731.html