我寫一個Oracle的例子給你,看看是否有幫助!
[PHP]
-- 創建表
SQL> create table text
2(
3 orderId varchar2(10)
4);
Table created
[/PHP]
[PHP]
--創建函數
/* Formatted on 2006/03/23 14:13 (Formatter Plus v4.8.7) */
CREATE OR REPLACE FUNCTION textfun
RETURN VARCHAR2
IS
tmpvar text.orderid%TYPE;
BEGIN
SELECTTO_CHAR (SYSDATE, 'yyyymmdd')
|| LPAD ((NVL (MAX (SUBSTR (orderid, 9)), 0) + 1), 2, '0')
INTO tmpvar
FROM text
WHERE SUBSTR (orderid, 1, 8) = TO_CHAR (SYSDATE, 'yyyymmdd');
RETURN tmpvar;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END textfun;
/
[/PHP]
[PHP]
-- inser record
SQL> insert into text values(textfun());
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> select * from text;
ORDERID
----------
2006032301
2006032302
2006032303
2006032304
2006032305
[/PHP]
|