CREATE OR REPLACE PROCEDURE "HTGL"."LENDUPATE" (USERID IN
VARCHAR2,SESSIONID IN VARCHAR2,RETURNSIGN OUT VARCHAR2) AS
TEMP_SIGN VARCHAR2(50);
BEGIN
SELECT DECODE(USER_ID,'NULL','-1','','-1','0') INTO TEMP_SIGN FROM SYS_USER WHERE USER_ID=USERID AND GCOOKIES=SESSIONID;
IF TEMP_SIGN='' THEN
RETURNSIGN := '-1';
ELSE
RETURNSIGN := '0';
END IF;
END LENDUPATE;在java中调用:(java没问题)
当 SELECT DECODE(USER_ID,'NULL','-1','','-1','0') INTO TEMP_SIGN FROM SYS_USER WHERE USER_ID=USERID AND GCOOKIES=SESSIONID能查到记录的时候正常,但是当没有记录的时候抱错(
java.sql.SQLException: ORA-01403: 未找到数据)。
说明我的这个存储过程有问题,但是怎么修改呢?
VARCHAR2,SESSIONID IN VARCHAR2,RETURNSIGN OUT VARCHAR2) AS
TEMP_SIGN VARCHAR2(50);
BEGIN
SELECT DECODE(USER_ID,'NULL','-1','','-1','0') INTO TEMP_SIGN FROM SYS_USER WHERE USER_ID=USERID AND GCOOKIES=SESSIONID;
IF TEMP_SIGN='' THEN
RETURNSIGN := '-1';
ELSE
RETURNSIGN := '0';
END IF;
END LENDUPATE;在java中调用:(java没问题)
当 SELECT DECODE(USER_ID,'NULL','-1','','-1','0') INTO TEMP_SIGN FROM SYS_USER WHERE USER_ID=USERID AND GCOOKIES=SESSIONID能查到记录的时候正常,但是当没有记录的时候抱错(
java.sql.SQLException: ORA-01403: 未找到数据)。
说明我的这个存储过程有问题,但是怎么修改呢?
和sqlserver中的exits函数一样 , if not exits(select .....) then return 1;end if;
when no_data_found then
begin
你的处理;
end;
exception
when no_data_found then
insert into ***_table values(user_id);
commit;
end;
可以把发生错误的userid记录下来
多行的.
RETURNSIGN := '-1';
ELSE
RETURNSIGN := '0';
END IF;oracle中判断是否为空,必须写成
IF TEMP_SIGN IS NULL THEN如果你只是想判断某个用户是否存在,倒不如这么写:CREATE OR REPLACE PROCEDURE "HTGL"."LENDUPATE" (
USERID IN VARCHAR2,
SESSIONID IN VARCHAR2,
RETURNSIGN OUT VARCHAR2
) AS
CNT NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM SYS_USER WHERE USER_ID=USERID AND GCOOKIES=SESSIONID;
IF CNT=0 THEN
RETURNSIGN:='-1';
ELSE
RETURNSIGN:='0';
END IF;
END LENDUPATE;