SELECT *
FROM causer
WHERE user_id IN up_causer_getmaxid
CREATE OR REPLACE PROCEDURE UP_CAUSER_GetMaxId
AS
DECLARE @TempID number
SELECT @TempID = max(USER_ID)+1 FROM CAUSER
IF @TempID IS NULL
RETURN 1
ELSE
RETURN @TempID
GOtable:
user_id user_pwd user_state
9001 1 1
9002 2 1
9003 3 0
9004 4 1
9005 5 0TOAD说我的PROCEDURE有问题。
FROM causer
WHERE user_id IN up_causer_getmaxid
CREATE OR REPLACE PROCEDURE UP_CAUSER_GetMaxId
AS
DECLARE @TempID number
SELECT @TempID = max(USER_ID)+1 FROM CAUSER
IF @TempID IS NULL
RETURN 1
ELSE
RETURN @TempID
GOtable:
user_id user_pwd user_state
9001 1 1
9002 2 1
9003 3 0
9004 4 1
9005 5 0TOAD说我的PROCEDURE有问题。
CREATE OR REPLACE FUNCTION test RETURN NUMBER IS
tmpVar NUMBER;BEGIN
tmpVar := 0;
select max(user_id)+1 into tmpVar from causer;
RETURN tmpVar;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END test;不过我有两个问题搞不懂:1、为啥是max(user_id)+1,不是max(user_id),比最大的还大一点,那你的sql还能找到么?2、为啥不直接用一个sql搞定?