要求格式为:
SL+当前时间+0001
测试表
create table Pdms_polling(
p_id INT PRIMARY KEY,
p_year date,
p_num varchar2(20)
);
-- 触发器.
CREATE OR REPLACE TRIGGER tr_auto_set_id
AFTER INSERT ON Pdms_polling
DECLARE
v_newID INT; -- 暂存主键
v_newNum varchar(30); -- 新的 流水号
v_oldNum varchar(30); -- 旧的流水号
-- 游标: 用于检索 当前表的所有 流水号字段为空的数据。
-- 将当天的所有数据,按顺序,自动编号排序
-- 该查询结果包含三列,分别为 ID, 现有的流水号, 按照 ROW_NUMBER 排序后的流水号。
CURSOR c_need_auto IS
SELECT
p_id,
p_num,
'SL'
|| TO_CHAR(p_year, 'YYYYMMDD')
|| TRIM(TO_CHAR(
ROW_NUMBER() OVER(PARTITION BY TO_CHAR(p_year, 'YYYYMMDD') ORDER BY p_id ),
'0000')) AS new_num
FROM
Pdms_polling
WHERE
EXISTS( SELECT 1
FROM Pdms_polling p2
WHERE TRUNC(p2.p_year) = TRUNC(Pdms_polling.p_year)
AND p_num IS NULL );
BEGIN
-- 打开游标.
OPEN c_need_auto;
LOOP
-- 填充数据(主表).
FETCH c_need_auto INTO v_newID, v_oldNum, v_newNum;
-- 假如没有检索到(主表)数据,结束循环处理
Exit when c_need_auto%NOTFOUND;
-- 这里判断 旧流水号是否为 NULL, 假如为空的话,那么将其更新为 ROW_NUMBER 排序后的流水号。
-- 需要注意的是,假如你系统,不会去删除数据的话,那么没有问题。
-- 假如你的系统,会删除中间的数据的话,那么,会导致这里的流水号计算错误。
-- 那么可能就要把下面的 v_oldNum IS NULL 修改为 v_oldNum IS NULL OR v_oldNum != v_newNum
-- 结果也很明显,中间删除掉的流水号,会在某一次追加以后,被下一条依次填充上来。最后可能造成关联表的数据不匹配。
IF v_oldNum IS NULL THEN
UPDATE
Pdms_polling
SET
p_num = v_newNum
WHERE
p_id = v_newID;
END IF;
END LOOP;
-- 关闭游标
CLOSE c_need_auto;
END;
-- 测试:
SQL> INSERT INTO Pdms_polling VALUES(1, SYSDATE, NULL);
已创建 1 行。
SQL> INSERT INTO Pdms_polling VALUES(2, SYSDATE, NULL);
已创建 1 行。
SQL> INSERT INTO Pdms_polling VALUES(3, SYSDATE, NULL);
已创建 1 行。
SQL> INSERT INTO Pdms_polling
2 SELECT 4, SYSDATE, NULL FROM dual UNION ALL
3 SELECT 5, SYSDATE-1, NULL FROM dual UNION ALL
4 SELECT 6, SYSDATE+1, NULL FROM dual UNION ALL
5 SELECT 7, SYSDATE-1, NULL FROM dual UNION ALL
6 SELECT 8, SYSDATE+1, NULL FROM dual UNION ALL
7 SELECT 9, SYSDATE, NULL FROM dual ;
已创建6行。
SQL> ALTER session
2 SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> SELECT * FROM Pdms_polling;
P_ID P_YEAR P_NUM
---------- ------------------- --------------------
1 2011-06-09 20:51:55 SL201106090001
2 2011-06-09 20:51:55 SL201106090002
3 2011-06-09 20:51:55 SL201106090003
4 2011-06-09 20:51:55 SL201106090004
5 2011-06-08 20:51:55 SL201106080001
6 2011-06-10 20:51:55 SL201106100001
7 2011-06-08 20:51:55 SL201106080002
8 2011-06-10 20:51:55 SL201106100002
9 2011-06-09 20:51:55 SL201106090005
已选择9行。