这是我的存储过程,目的是向数据表中插入数据,然后再将记录全部显示出来:Create or Replace package ref_types
AS
TYPE ref_cursor is REF CURSOR;
End;
/
create or replace procedure insert_usertable
(username in usertable.USERNAME%TYPE,
usertype in usertable.USERTYPE%TYPE,
pwd in usertable.PWD%TYPE,
afterinsert out ref_types.ref_cursor
)
AS
begin
insert into usertable values(username,usertype,pwd);
commit;
open afterinsert for select rownum as columid,usertable.* from usertable;
end insert_usertable;
/这个存储过程是正确的,但我现在想添加一些功能:
就是插入后要判断是否插入成功,如果插入成功了,再显示所有数据记录;如果插入不成功,则要给出提示信息,并且这些错误信息要能在vc中使用
AS
TYPE ref_cursor is REF CURSOR;
End;
/
create or replace procedure insert_usertable
(username in usertable.USERNAME%TYPE,
usertype in usertable.USERTYPE%TYPE,
pwd in usertable.PWD%TYPE,
afterinsert out ref_types.ref_cursor
)
AS
begin
insert into usertable values(username,usertype,pwd);
commit;
open afterinsert for select rownum as columid,usertable.* from usertable;
end insert_usertable;
/这个存储过程是正确的,但我现在想添加一些功能:
就是插入后要判断是否插入成功,如果插入成功了,再显示所有数据记录;如果插入不成功,则要给出提示信息,并且这些错误信息要能在vc中使用
CREATE OR REPLACE PROCEDURE INSERT_USERTABLE
(
USERNAME IN usertable.USERNAME%TYPE,
USERTYPE IN usertable.USERTYPE%TYPE,
PWD IN usertable.PWD%TYPE,
AFTERINSERT OUT ref_types.ref_cursor
)
AS
ErrorStr EXCEPTION;
BEGIN
INSERT INTO USERTABLE VALUES (USERNAME, USERTYPE, PWD); IF SQL%ROWCOUNT=0 THEN
RAISE ErrorStr;
END IF;
COMMIT;
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;
RAISE ErrorStr;
我对这句不是很明白,RAISE和ErrorStr哪个是类型哪个是值呢?如果FALSE是类型,Oracle中定义变量不是类型在后面吗,像query_type in integer