- 论坛徽章:
- 0
|
--测试代码
DECLARE
lockerId NUMBER;
BEGIN
get_app_count('APP_LOCKER', 1, lockerId);
INSERT INTO APP_LOCKER
(LOCK_ID, LOCK_TIME, LOCK_PERSON_ID, EXPIRE_TIME, BUSINESS_KEY1, BUSINESS_KEY2, BUSINESS_KEY3, BUSINESS_KEY4, BUSINESS_KEY5,BUSINESS_CATEGORY, BUSINESS_TYPE)
VALUES
(lockerId, SYSDATE, '1234567', 300, '1201', null, null, null, null, 1, 1101);
COMMIT;
END;
注:
1、存储过程get_app_count执行是正常的,计数器正常的增加,但是insert语句执行没有把数据插入表中。
2、我用的oracle版本是10.2.0.1
--存储过程get_app_count(由于生产环境不能使用的复制工具不能复制序列,自己写了一个计数器)
CREATE OR REPLACE PROCEDURE get_app_count (key VARCHAR2, num NUMBER, retStartValue OUT NUMBER) AS
exLessThanZero EXCEPTION;
BEGIN
IF num < 0 THEN
RAISE exLessThanZero;
ELSE
UPDATE APP_COUNT SET COUNTER = COUNTER + num WHERE COUNTER_KEY = key;
IF SQL%FOUND THEN
SELECT COUNTER - num + 1 INTO retStartValue FROM APP_COUNT WHERE COUNTER_KEY = key;
ELSE
INSERT INTO APP_COUNT (COUNTER_KEY, COUNTER) VALUES (key, num);
retStartValue := 1;
END IF;
COMMIT;
END IF;
EXCEPTION
WHEN exLessThanZero THEN
retStartValue := 0;
WHEN OTHERS THEN
retStartValue := 0;
ROLLBACK;
END; |
|