我现在写了一个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;
/
红色部分是我用来检测记录是否存在的,但是如果记录已经存在,还是会出现运行时错误;如果记录不存在,则可以正常插入,应该怎样改进呢?
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中获取这些异常,并如何处理呢?我的存储过程是将自定义的一场返回到游标中
EXCEPTION
WHEN ErrorStr THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
WHEN OTHERS THEN
OPEN AFTERINSERT FOR SELECT '出错误了!' FROM dual;
我该如何在VC中获取呢?
另外,我覺得樓主把問題複雜話了。
begin
insert語句
ret := 0;
exception
WHEN OTHERS THEN
ret := 1;
end;
就可以了!
MERGE INTO schema . table t_alias USING schema . { table | view | subquery } t_alias ON(condition) WHEN MATCHED THEN merge_update_clause WHEN NOT MATCHED THEN merge_insert_clause;