Atomicity: Adhoc Query

I have a source as table loading the target.Suppose there are duplicate records in the middle of the record flow,what will be the target load resulted in?

CREATE TABLE INS(ID INTEGER);

CREATE TABLE INS_T(ID INTEGER PRIMARY KEY);

INSERT INTO INS VALUES(1);
INSERT INTO INS VALUES(2);
INSERT INTO INS VALUES(3);
INSERT INTO INS VALUES(4);
INSERT INTO INS VALUES(5);
INSERT INTO INS VALUES(5);
INSERT INTO INS VALUES(6);
INSERT INTO INS VALUES(7);
INSERT INTO INS VALUES(8);


DECLARE
INS VARCHAR2(50);
BEGIN
INS:='SELECT ID FROM INS';
DBMS_OUTPUT.PUT_LINE('STARTED');
EXECUTE IMMEDIATE('INSERT INTO INS_T '||INS);
COMMIT;
DBMS_OUTPUT.PUT_LINE('ENDED');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ABORTED');
END;

The above program in turn returns the message as ABORTED due to the exception raised by unique violation.

Since the control of the program goes from EXECUTE IMMEDIATE statement to the exception section, the COMMIT statement is unaffected.Hence no records inserted into the target table.

SELECT * FROM INS_T;

0 rows selected.

PLSQL Interaction with SQL

Example1:

DECLARE
V_EMPNO NUMBER(4);

BEGIN
SELECT EMPNO INTO V_EMPNO FROM EMP WHERE ENAME='KING';
DBMS_OUTPUT.PUT_LINE(V_EMPNO);
END;

Example2:

DECLARE
V_EMPNO NUMBER(4);
V_ENAME VARCHAR2(20);
V_JOB VARCHAR2(15);

BEGIN
SELECT EMPNO,ENAME,JOB INTO V_EMPNO,V_ENAME,V_JOB FROM EMP WHERE ENAME='KING';
DBMS_OUTPUT.PUT_LINE(V_EMPNO || CHR(9)|| V_ENAME ||CHR(9) ||V_JOB);
END;

Example3:

DECLARE
V_EMPNO NUMBER(4);
V_ENAME VARCHAR2(10);
V_JOB VARCHAR2(9);
BEGIN
SELECT  EMPNO,ENAME,JOB INTO  V_EMPNO,V_ENAME,V_JOB FROM EMP;
DBMS_OUTPUT.PUT_LINE(V_EMPNO||CHR(9)||V_ENAME||CHR(9)||V_JOB);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS');
END;

Example4:

DECLARE
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_MGR EMP.MGR%TYPE;
V_HIREDATE EMP.HIREDATE%TYPE;
V_SAL EMP.SAL%TYPE;
V_COMM EMP.COMM%TYPE;
V_DEPTNO EMP.DEPTNO%TYPE;
BEGIN
SELECT  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
 INTO  V_EMPNO,V_ENAME,V_JOB,V_MGR,V_HIREDATE,V_SAL,V_COMM,V_DEPTNO
 FROM EMP WHERE ENAME='KING';
DBMS_OUTPUT.PUT_LINE(V_EMPNO||CHR(9)||V_ENAME||CHR(9)||V_JOB);
END;

Example5:

DECLARE
EMP_REC EMP%ROWTYPE;
BEGIN
SELECT * INTO EMP_REC FROM EMP WHERE ENAME='KING';
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO || CHR(9)|| EMP_REC.ENAME ||CHR(9) ||EMP_REC.JOB);
END;

PL/SQL Basics

Basic Blocks

DECLARE      
 v_width INTEGER;
 v_height INTEGER := 2;
 v_area INTEGER ;
 BEGIN
v_area := 6;
v_width := v_area / v_height;
DBMS_OUTPUT.PUT_LINE('width = ' || width);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero');
END;

IF clause:

DECLARE
v_A Number := 50;
 v_B Number;
BEGIN
IF v_A > 40 THEN
v_B := v_A - 40;
DBMS_OUTPUT.PUT_LINE('V_B = ' || v_B);
 ELSE
v_B := 0;
DBMS_OUTPUT.PUT_LINE('V_B = ' || v_B);
END IF;
END;

/**IF -THEN-ELSE LADDER*/

DECLARE
 v_Score Number := 85; --Percentage
v_LetterGrade Char(1);
BEGIN
IF v_Score >= 90 THEN
v_LetterGrade := 'A';
ELSIF v_Score >= 80 THEN
v_LetterGrade := 'B';
ELSIF v_Score >= 70 THEN
v_LetterGrade := 'C';
ELSIF v_Score >= 60 THEN
v_LetterGrade := 'D';
ELSE
v_LetterGrade := 'E';
END IF;
DBMS_OUTPUT.PUT_LINE('Your Letter Grade is: ' || v_LetterGrade);
END;

declare
v NUMBER;
begin
if v=1 then
DBMS_OUTPUT.put_line('Equal to 1');
elsif v!= 1 then
DBMS_OUTPUT.put_line('Not equal to 1');
elsif v = v then
DBMS_OUTPUT.put_line('Equal to itself');
else
DBMS_OUTPUT.put_line('Undefined result');
end if;
v:= v+1;
DBMS_OUTPUT.put_line('New value: <'||v||'>');
end;

LOOPS

DECLARE
 hundreds_counter  NUMBER(1,-2);
BEGIN
hundreds_counter := 100;
LOOP
DBMS_OUTPUT.PUT_LINE(hundreds_counter);
hundreds_counter := hundreds_counter + 100;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Done.');
END;

DECLARE
     v_Radius NUMBER := 2;
     MYPI NUMBER:=3.14;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('The AREA of the circle is ' ||
                         v_RADIUS*v_RADIUS * mypi);
  v_Radius := v_Radius + 2;
  EXIT WHEN v_Radius > 10;
  END LOOP;
END;
  /

 BEGIN
FOR just_a_num IN 1..10 LOOP
 dbms_output.put_line(just_a_num);
END LOOP;
END;
 /

DECLARE
V_LOW INTEGER:=1;
V_HIGH INTEGER:=10;
BEGIN
FOR JUST_A_NUM IN V_LOW..V_HIGH LOOP
DBMS_OUTPUT.PUT_LINE(JUST_A_NUM);
END LOOP;
END;


BEGIN
 FOR v_loopcounter IN 1..5 LOOP
 DBMS_OUTPUT.PUT_LINE('Loop counter is ' || v_loopcounter);
END LOOP;
END;
 /


DECLARE
 v_Start Integer := 1;
BEGIN
FOR v_loopcounter IN REVERSE v_Start..5 LOOP
DBMS_OUTPUT.PUT_LINE('Loop counter is ' || v_loopcounter);
END LOOP;
END;
 /
DECLARE
MYPI NUMBER:=3.14;
BEGIN
FOR v_loopcounter IN 1..20 LOOP
IF MOD(v_loopcounter,2) = 0 THEN
 DBMS_OUTPUT.PUT_LINE('The AREA of the circle is ' ||v_loopcounter*v_loopcounter * mypi);
END IF;
IF v_loopcounter = 10 THEN
EXIT;
END IF;
END LOOP;
END;
/



BEGIN
<<outerloop>>
FOR v_outerloopcounter IN 1..2 LOOP
<<innerloop>>
FOR v_innerloopcounter IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE('Outer Loop counter is ' ||v_outerloopcounter ||
' Inner Loop counter is ' ||v_innerloopcounter);
END LOOP innerloop;
END LOOP outerloop;
END;
 /


DECLARE
v_Status NUMBER := 1;
BEGIN
IF v_Status = 1 THEN
GOTO mybranch;
ELSE
v_Status := 1;
END IF;
<<mybranch>>
NULL;
END;
/


DECLARE
v_Radius NUMBER := 2;
MYPI NUMBER:=3.14;
BEGIN
WHILE v_Radius <=10 LOOP
DBMS_OUTPUT.PUT_LINE('The Area is ' ||mypi * v_Radius * v_Radius);
v_Radius := v_Radius + 2 ;
END LOOP;
END;
/


DECLARE
v_Radius NUMBER := 2;
MYPI NUMBER:=3.14;
BEGIN
WHILE TRUE LOOP
DBMS_OUTPUT.PUT_LINE('The Area is '||mypi * v_Radius * v_Radius);
IF v_Radius = 10 THEN
EXIT;
END IF;
v_Radius := v_Radius + 2 ;
END LOOP;
 END;
 /