过程2:(重设'QW_SE') CREATE OR REPLACE PROCEDURE RESET_QW_SE(COMMIT_FLAG VARCHAR2) ISVARTMP NUMBER; BEGIN SELECT COUNT(*) INTO VARTMP FROM USER_SEQUENCES WHERE SEQUENCE_NAME='QW_SE';
IF VARTMP=1 THEN EXECUTE IMMEDIATE 'DROP SEQUENCE QW_SE'; END IF;
SELECT COUNT(*) INTO VARTMP FROM USER_SEQUENCES WHERE SEQUENCE_NAME='QW_SE';
IF VARTMP=0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE QW_SE START WITH 1 INCREMENT BY 1 NOCYCLE'; END IF;
IF UPPER(COMMIT_FLAG)='COMMIT' THEN COMMIT; END IF;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001,'RESET_QW_SE ERROR');
Create or replace procedure Main
is
.
.
.
.
procedure Sub_Procedure
end Sub_Procedure;begin
.
.
.
Sub_procedure;
.
.
.end Main_Procedure;
CREATE OR REPLACE PROCEDURE SET_QW(RECORDS NUMBER,DEL_FLAG VARCHAR) IS
TMPVAR NUMBER(10);BEGIN TMPVAR := 0;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'rrrr-mm-dd hh24:mi:ss'));
IF UPPER(DEL_FLAG)='DEL' THEN
DELETE FROM QW;
RESET_QW_SE('');
END IF;
FOR TMPVAR IN 1..RECORDS LOOP
INSERT INTO QW(NAME,SCORE) VALUES ('NAME'||TRIM(TO_CHAR(TMPVAR,'000000000000000')),QW_GET_RND);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'rrrr-mm-dd hh24:mi:ss'));
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END SET_QW;
CREATE OR REPLACE PROCEDURE RESET_QW_SE(COMMIT_FLAG VARCHAR2) ISVARTMP NUMBER;
BEGIN SELECT COUNT(*) INTO VARTMP FROM USER_SEQUENCES WHERE SEQUENCE_NAME='QW_SE';
IF VARTMP=1 THEN
EXECUTE IMMEDIATE 'DROP SEQUENCE QW_SE';
END IF;
SELECT COUNT(*) INTO VARTMP FROM USER_SEQUENCES WHERE SEQUENCE_NAME='QW_SE';
IF VARTMP=0 THEN
EXECUTE IMMEDIATE 'CREATE SEQUENCE QW_SE START WITH 1 INCREMENT BY 1 NOCYCLE';
END IF;
IF UPPER(COMMIT_FLAG)='COMMIT' THEN
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,'RESET_QW_SE ERROR');
END RESET_QW_SE;
你现在执行ddl语句
而ddl语句即使在一个存储过程中,
只要那个语句执行,就已经不能回滚了
rollback对他还有什么意义
所谓皮之不存毛将焉附
岂不隐式提交呼。
DELETE FROM QW;
RESET_QW_SE('');
换为:
RESET_QW_SE('');
DELETE FROM QW;