PRAGMA AUTONOMOUS TRANSACTION:
*******************************
-->Autonomous transactions are indepedent transactions used in either procedures or functions or triggers.
-->generally autonomous transactions are used in child procedures,These procedures are not effected from the main
transactions when we are using commit or rollback.
Syntax:
*******
pragma autonomous_transaction;
-->This pragma used in declare section of the procedure.
EX:
***
CREATE TABLE XX_TEST(NAME VARCHAR2(50));
WITHOUT AUTONOMOUS TRANSACTION:
*******************************
CREATE OR REPLACE PROCEDURE CHILD_P
IS
BEGIN
INSERT INTO XX_TEST VALUES('INDIA');
COMMIT;
END;
/
SELECT *
FROM XX_TEST;
/
EXEC CHILD_P;
/
SELECT * FROM XX_TEST;
/
DELETE FROM XX_TEST;
/
COMMIT;
/
SELECT * FROM XX_TEST; --NO ROWS
/
--CALL CHILD PROCEDURE IN MAIN PROCEDURE
CREATE OR REPLACE PROCEDURE MAIN_P
IS
BEGIN
INSERT INTO XX_TEST VALUES('USA');
INSERT INTO XX_TEST VALUES('UK');
CHILD_P;
ROLLBACK;
END;
/
SELECT * FROM XX_TEST;
/
EXEC MAIN_P;
/
--HOW MANY RECORDS ARE INSERTED IN TABLE
/
SELECT * FROM XX_TEST;
/
DELETE FROM XX_TEST;
/
COMMIT;
/
--MY REQUIREMENT IS I DON'T WANT COMMIT ALL THE RECORDS IN MAIN PROCEDURE.
--WHENEVER I AM GOING TO EXECUTE THIS MAIN PROCEDURE I WANT ROLLBACK THIS DATA,THE ROLLBACK DOENOT APPLICABLE FOR THIS PROCEDURE.
--THIS RESPECTED CHILD PROCEDURE DOES NOT IMPACT TO THE MAIN PROCEDURE,CHILD PROCEDURE ALSO HAVING COMMIT, THAT COMMIT DOES NOT IMPACT TO THE MAIN PROCEDURE.
--I WANT TO DELETE THIS DATA FROM MAIN PROCEDURE BUT I DON'T WANT DELETE THE CHILD PROCEDURE
-->CHILD PROCEDURE TRANSACTION DATA IS DIFFERENT AND MAIN PROCEDURE TRANSACTION IS DIFFERENT BUT I WNAT TO COMMIT THE CHILD PROCEDURE DATA.
/
CREATE OR REPLACE PROCEDURE CHILD_P
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO XX_TEST VALUES('INDIA');
COMMIT;
END;
/
SELECT * FROM XX_TEST;
/
EXEC CHILD_P;
/
SELECT * FROM XX_TEST;
/
DELETE FROM XX_TEST;
COMMIT;
/
CREATE OR REPLACE PROCEDURE MAIN_P
IS
INSERT INTO XX_TEST VALUES('USA');
INSERT INTO XX_TEST VALUES('UK');
CHILD_P;
ROLLBACK;
END;
/
SELECT * FROM MAIN_P;
/
EXEC MAIN_P;
/
SELECT * FROM MAIN_P;
/
CREATE OR REPLACE PROCEDURE CHILD_P
IS
BEGIN
INSERT INTO XX_TEST VALUES('INDIA');
ROLLBACK;
END;
/
SELECT *
FROM XX_TEST;
/
EXEC CHILD_P;
/
SELECT * FROM XX_TEST;
/
DELETE FROM XX_TEST;
/
COMMIT;
/
SELECT * FROM XX_TEST; --NO ROWS
/
CREATE OR REPLACE PROCEDURE MAIN_P
IS
BEGIN
INSERT INTO XX_TEST VALUES('USA');
INSERT INTO XX_TEST VALUES('UK');
CHILD_P;
COMMIT;
END;
/
SELECT * FROM XX_TEST;
/
EXEC MAIN_P;
/
--HOW MANY RECORDS ARE INSERTED IN TABLE
/
SELECT * FROM XX_TEST; --NO ROWS SELECTED
/
DELETE FROM XX_TEST;
/
COMMIT;
/
CREATE OR REPLACE PROCEDURE CHILD_P(P_EMPNO IN NUMBER)
IS
BEGIN
UPDATE EMP1 SET SAL=SAL+500 WHERE EMPNO=P_EMPNO;
ROLLBACK;
END;
/
CREATE OR REPLACE PROCEDURE MAIN_P
IS
BEGIN
UPDATE EMP1 SET SAL=SAL+1000 WHERE EMPNO=7369;
CHILD_P(7499);
COMMIT;
END;
/
EXEC CHILD_P(7499);
/
SELECT * FROM EMP1 WHERE EMPNO=7499;
/
EXEC MAIN_P;
/
SELECT * FROM EMP1 WHERE EMPNO=7369;
/
CREATE OR REPLACE PROCEDURE CHILD_P(P_EMPNO IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE EMP1 SET SAL=SAL+500 WHERE EMPNO=P_EMPNO;
ROLLBACK;
END;
/
CREATE OR REPLACE PROCEDURE MAIN_P
IS
BEGIN
UPDATE EMP1 SET SAL=SAL+1000 WHERE EMPNO=7369;
CHILD_P(7499);
COMMIT;
END;
/
EXEC MAIN_P;
/
SELECT * FROM EMP1 WHERE EMPNO=7499;
/
SELECT * FROM EMP1 WHERE EMPNO=7369;
/
CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER)
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE EMP1
SET SAL=SAL+4000
WHERE EMPNO=P_EMPNO;
ROLLBACK;
END;
/
BEGIN
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(1,'A',1000);
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(2,'B',2000);
DELETE FROM EMP1 WHERE EMPNO=2;
UPDATE EMP1 SET SAL=SAL+2000 WHERE EMPNO=7369;
P1(7839);
COMMIT;
END;
/
SELECT * FROM EMP1;
/
CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE EMP1
SET SAL=SAL+4000
WHERE EMPNO=P_EMPNO;
ROLLBACK;
END;
/
BEGIN
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(1,'A',1000);
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(2,'B',2000);
DELETE FROM EMP1 WHERE EMPNO=2;
UPDATE EMP1 SET SAL=SAL+2000 WHERE EMPNO=7369;
P1(7839);
COMMIT;
END;
/
SELECT * FROM EMP1;
/
CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER)
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE EMP1
SET SAL=SAL+4000
WHERE EMPNO=P_EMPNO;
COMMIT;
END;
/
BEGIN
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(1,'A',1000);
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(2,'B',2000);
DELETE FROM EMP1 WHERE EMPNO=2;
UPDATE EMP1 SET SAL=SAL+2000 WHERE EMPNO=7369;
P1(7839);
ROLLBACK;
END;
/
SELECT * FROM EMP1;
/
CREATE OR REPLACE PROCEDURE P1(P_EMPNO IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE EMP1
SET SAL=SAL+4000
WHERE EMPNO=P_EMPNO;
COMMIT;
END;
/
BEGIN
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(1,'A',1000);
INSERT INTO EMP1(EMPNO,ENAME,SAL) VALUES(2,'B',2000);
DELETE FROM EMP1 WHERE EMPNO=2;
UPDATE EMP1 SET SAL=SAL+2000 WHERE EMPNO=7369;
P1(7839);
ROLLBACK;
END;
/
SELECT * FROM EMP1;
/
No comments:
Post a Comment