DECLARE
--***********************************
V_USERID USERSTEST.USERID %TYPE;
V_PWD USERSTEST.PWD %TYPE ;
V_NAME USERSTEST.NAME %TYPE ;
V_AGE USERSTEST.AGE %TYPE ;
V_SEX USERSTEST.SEX %TYPE ;
V_ADDRESS USERSTEST.ADDRESS %TYPE;
V_TEL USERSTEST.TEL %TYPE;
V_TAG CONSTANT NUMBER := 1;
V_TABLECCOUNT NUMBER ;
V_CHANGECOUNT NUMBER ;
--***************
REPEAT_EXCEPTION EXCEPTION;
--***********************************BEGIN
V_USERID := '2005112108';
V_PWD := '9527007';
V_NAME := '曼曼';
V_AGE := '22' ;
V_SEX := '2' ;
V_ADDRESS := '不告诉你' ;
V_TEL := '00000' ;
SELECT COUNT(*) INTO V_TABLECCOUNT FROM USERSTEST ;
INSERT INTO USERSTEST VALUES(V_USERID,V_PWD,V_NAME,V_AGE,V_SEX,V_ADDRESS,V_TEL);
SELECT COUNT(*) INTO V_CHANGECOUNT FROM USERSTEST ;
IF V_CHANGECOUNT > V_CHANGECOUNT THEN
DBMS_OUTPUT.put_line('执行成功');
ELSE IF V_CHANGECOUNT < V_CHANGECOUNT THEN
RAISE REPEAT_EXCEPTION ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不能将重复的值添加到Userstest表中');
END ;
--***********************************
V_USERID USERSTEST.USERID %TYPE;
V_PWD USERSTEST.PWD %TYPE ;
V_NAME USERSTEST.NAME %TYPE ;
V_AGE USERSTEST.AGE %TYPE ;
V_SEX USERSTEST.SEX %TYPE ;
V_ADDRESS USERSTEST.ADDRESS %TYPE;
V_TEL USERSTEST.TEL %TYPE;
V_TAG CONSTANT NUMBER := 1;
V_TABLECCOUNT NUMBER ;
V_CHANGECOUNT NUMBER ;
--***************
REPEAT_EXCEPTION EXCEPTION;
--***********************************BEGIN
V_USERID := '2005112108';
V_PWD := '9527007';
V_NAME := '曼曼';
V_AGE := '22' ;
V_SEX := '2' ;
V_ADDRESS := '不告诉你' ;
V_TEL := '00000' ;
SELECT COUNT(*) INTO V_TABLECCOUNT FROM USERSTEST ;
INSERT INTO USERSTEST VALUES(V_USERID,V_PWD,V_NAME,V_AGE,V_SEX,V_ADDRESS,V_TEL);
SELECT COUNT(*) INTO V_CHANGECOUNT FROM USERSTEST ;
IF V_CHANGECOUNT > V_CHANGECOUNT THEN
DBMS_OUTPUT.put_line('执行成功');
ELSE IF V_CHANGECOUNT < V_CHANGECOUNT THEN
RAISE REPEAT_EXCEPTION ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不能将重复的值添加到Userstest表中');
END ;
--***********************************
V_USERID USERSTEST.USERID %TYPE;
V_PWD USERSTEST.PWD %TYPE ;
V_NAME USERSTEST.NAME %TYPE ;
V_AGE USERSTEST.AGE %TYPE ;
V_SEX USERSTEST.SEX %TYPE ;
V_ADDRESS USERSTEST.ADDRESS %TYPE;
V_TEL USERSTEST.TEL %TYPE;
V_TAG CONSTANT NUMBER := 1;
V_TABLECCOUNT NUMBER ;
V_CHANGECOUNT NUMBER ;
--***************
REPEAT_EXCEPTION EXCEPTION;
--***********************************BEGIN
V_USERID := '2005112108';
V_PWD := '9527007';
V_NAME := '曼曼';
V_AGE := '22' ;
V_SEX := '2' ;
V_ADDRESS := '不告诉你' ;
V_TEL := '00000' ;SELECT COUNT(*) INTO V_TABLECCOUNT FROM USERSTEST ;INSERT INTO USERSTEST VALUES(V_USERID,V_PWD,V_NAME,V_AGE,V_SEX,V_ADDRESS,V_TEL);SELECT COUNT(*) INTO V_CHANGECOUNT FROM USERSTEST ;IF V_CHANGECOUNT > V_CHANGECOUNT THENDBMS_OUTPUT.put_line('执行成功');ELSE IF V_CHANGECOUNT < V_CHANGECOUNT THEN -- change ELSE IF to eslifRAISE REPEAT_EXCEPTION ; --没有定义错误的处理
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不能将重复的值添加到Userstest表中');
END ;
--***********************************
V_USERID USERSTEST.USERID %TYPE;
V_PWD USERSTEST.PWD %TYPE ;
V_NAME USERSTEST.NAME %TYPE ;
V_AGE USERSTEST.AGE %TYPE ;
V_SEX USERSTEST.SEX %TYPE ;
V_ADDRESS USERSTEST.ADDRESS %TYPE;
V_TEL USERSTEST.TEL %TYPE;
V_TAG CONSTANT NUMBER := 1;
V_TABLECCOUNT NUMBER ;
V_CHANGECOUNT NUMBER ;
--***************
REPEAT_EXCEPTION EXCEPTION;
--***********************************BEGIN
V_USERID := '2005112108';
V_PWD := '9527007';
V_NAME := '曼曼';
V_AGE := '22' ;
V_SEX := '2' ;
V_ADDRESS := '不告诉你' ;
V_TEL := '00000' ;SELECT COUNT(*) INTO V_TABLECCOUNT FROM USERSTEST ;INSERT INTO USERSTEST VALUES(V_USERID,V_PWD,V_NAME,V_AGE,V_SEX,V_ADDRESS,V_TEL);SELECT COUNT(*) INTO V_CHANGECOUNT FROM USERSTEST ;IF V_CHANGECOUNT > V_CHANGECOUNT THENDBMS_OUTPUT.put_line('执行成功');elsif V_CHANGECOUNT < V_CHANGECOUNT THENRAISE REPEAT_EXCEPTION ;
END IF ;
EXCEPTION
when REPEAT_EXCEPTION then
DBMS_OUTPUT.put_line('没有插入数据');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不能将重复的值添加到Userstest表中');
when others then
null;
END ;
V_CHANGECOUNT > V_CHANGECOUNT
可以用 sql/rowcount>0 来代替DECLARE
--***********************************
V_USERID USERSTEST.USERID %TYPE;
V_PWD USERSTEST.PWD %TYPE ;
V_NAME USERSTEST.NAME %TYPE ;
V_AGE USERSTEST.AGE %TYPE ;
V_SEX USERSTEST.SEX %TYPE ;
V_ADDRESS USERSTEST.ADDRESS %TYPE;
V_TEL USERSTEST.TEL %TYPE;
V_TAG CONSTANT NUMBER := 1;
V_TABLECCOUNT NUMBER ;
V_CHANGECOUNT NUMBER ;
--***************
REPEAT_EXCEPTION EXCEPTION;
--***********************************BEGIN
V_USERID := '2005112108';
V_PWD := '9527007';
V_NAME := '曼曼';
V_AGE := '22' ;
V_SEX := '2' ;
V_ADDRESS := '不告诉你' ;
V_TEL := '00000' ;SELECT COUNT(*) INTO V_TABLECCOUNT FROM USERSTEST ;INSERT INTO USERSTEST VALUES(V_USERID,V_PWD,V_NAME,V_AGE,V_SEX,V_ADDRESS,V_TEL);--SELECT COUNT(*) INTO V_CHANGECOUNT FROM USERSTEST ;IF sql%rowcount>0 THENDBMS_OUTPUT.put_line('执行成功');elseRAISE REPEAT_EXCEPTION ;
END IF ;
EXCEPTION
when REPEAT_EXCEPTION then
DBMS_OUTPUT.put_line('没有插入数据');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('不能将重复的值添加到Userstest表中');
when others then
null;
END ;