我现在写了一个Oracle存储过程,当插入记录时,先检测记录是否存在,如果记录存在,则返回一个标识,不执行插入;否则继续执行。这是存储过程:Create or Replace package ref_types
AS
TYPE ref_cursor is REF CURSOR;
End;
/
CREATE OR REPLACE PROCEDURE INSERT_USERTABLE
(
USERNAME_str IN usertable.USERNAME%TYPE,
USERTYPE IN usertable.USERTYPE%TYPE,
PWD IN usertable.PWD%TYPE,
AFTERINSERT OUT ref_types.ref_cursor,
ret OUT integer
)
AS
bExist int;--记录是否已经存在,0:不存在;1:存在
ErrorStr EXCEPTION;
BEGIN
SELECT COUNT(*) into bExist
FROM USERTABLE WHERE USERNAME = USERNAME_str;
IF(bExist >0)
THEN
dbms_output.put_line('该记录已经存在 ');
ret:=0;
RETURN;
END IF;
INSERT INTO USERTABLE VALUES (USERNAME_str, USERTYPE, PWD);
IF SQL%ROWCOUNT=0 THEN
RAISE ErrorStr;
END IF;
COMMIT;
ret:=1;
OPEN AFTERINSERT FOR
SELECT ROWNUM AS COLUMID, USERTABLE.* FROM USERTABLE;
EXCEPTION
WHEN ErrorStr THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
WHEN OTHERS THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
END INSERT_USERTABLE;
/
红色字体是我自定义的异常,当插入的记录已经存在时,就处理异常,那么我怎样在VC中获取这个存储过程返回的异常呢?
另外,我想知道:IF SQL%ROWCOUNT=0 THEN的作用是什么?
AS
TYPE ref_cursor is REF CURSOR;
End;
/
CREATE OR REPLACE PROCEDURE INSERT_USERTABLE
(
USERNAME_str IN usertable.USERNAME%TYPE,
USERTYPE IN usertable.USERTYPE%TYPE,
PWD IN usertable.PWD%TYPE,
AFTERINSERT OUT ref_types.ref_cursor,
ret OUT integer
)
AS
bExist int;--记录是否已经存在,0:不存在;1:存在
ErrorStr EXCEPTION;
BEGIN
SELECT COUNT(*) into bExist
FROM USERTABLE WHERE USERNAME = USERNAME_str;
IF(bExist >0)
THEN
dbms_output.put_line('该记录已经存在 ');
ret:=0;
RETURN;
END IF;
INSERT INTO USERTABLE VALUES (USERNAME_str, USERTYPE, PWD);
IF SQL%ROWCOUNT=0 THEN
RAISE ErrorStr;
END IF;
COMMIT;
ret:=1;
OPEN AFTERINSERT FOR
SELECT ROWNUM AS COLUMID, USERTABLE.* FROM USERTABLE;
EXCEPTION
WHEN ErrorStr THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
WHEN OTHERS THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
END INSERT_USERTABLE;
/
红色字体是我自定义的异常,当插入的记录已经存在时,就处理异常,那么我怎样在VC中获取这个存储过程返回的异常呢?
另外,我想知道:IF SQL%ROWCOUNT=0 THEN的作用是什么?
1. 在存儲過程的異常代碼中返回相應整數給 ret OUT integer,然後在程式裏面判斷ret的值
2. IF SQL%ROWCOUNT=0 THEN的作用是什么? SQL%ROWCOUNT作用是判斷你的SQL語句影響的行數,=0表示如果沒有數據插入或沒有數據受到SQL語句的影響!請參考!
我现在是想获得
WHEN ErrorStr THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
WHEN OTHERS THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
END INSERT_USERTABLE;
中的错误信息,就是如何'出错误了!'这句话