CONTINUE Statement in PL/SQL (Oracle 11g):

We -as developers- know that most programming languages have CONTINUE statement to skip to next iteration of the loop, but unfortunately we didn't have in Oracle, so we used to do workaround code to imitate CONTINUE functionality. Say we have the following loop code:

SET serveroutput ON

DECLARE

BEGIN

  FOR i IN 1 .. 10

  LOOP

    DBMS_OUTPUT.put_line ('The current number is ' || i);

  END LOOP;

END;

And if we don't want to print for 4,6 and 8 numbers we would do some of the following workarounds:

Use label:

SET serveroutput ON

DECLARE

BEGIN

  FOR i IN 1 .. 10

  LOOP

    IF (I IN (4,6,8)) THEN

      GOTO FOO ;

    END IF;

    DBMS_OUTPUT.PUT_LINE ('The current number is ' || I);

    <<FOO>> NULL;

  END LOOP;

END;

Or use IF ELSE:

SET serveroutput ON

DECLARE

BEGIN

  FOR i IN 1 .. 10

  LOOP

    IF (I NOT IN (4,6,8)) THEN

      DBMS_OUTPUT.PUT_LINE ('The current number is ' || I);

    ELSE

      NULL;

    END IF;

  END LOOP;

END;

etc...

The good news is we don't have to do previous code. In Oracle 11g a new feature is introduced to enable user to enable the developer to explicitly use CONTINUE statement:

SET serveroutput ON

DECLARE

BEGIN

  FOR i IN 1 .. 10

  LOOP

    CONTINUE WHEN i IN (4,6,8);

    DBMS_OUTPUT.put_line ('The current number is ' || i);

  END LOOP;

END;

Or write:

SET serveroutput ON

DECLARE

BEGIN

  FOR i IN 1 .. 10

  LOOP

    IF I IN (4,6,8) THEN

      CONTINUE;

    END IF;

    DBMS_OUTPUT.put_line ('The current number is ' || i);

  END LOOP;

END;

And viola....

Saad Nayef,

Escape special character (Still some people don't know 10g mechanism!):

To escape single quote in a string we used to add another single quote before -or after :)- the original single quote to consider those two single quote as one single quote within the string and not a string terminator, as the following:

SELECT 'I don''t like my brother''s car'

  FROM DUAL

But do we have to do that for every single quote? Well we had to do it, but not anymore. In oracle 10g a new feature is introduced to make it easier to Oracle developers. The previous query would be:

SELECT q'!I don't like my brother's car!'

  FROM DUAL

You may notice the exclamation mark "!" which is not the only character you can use, you can use "[" and "]" as start and end respectively, or any character that is found in the original string with a succeeding single quote and it's better to choose a special character like "[ and ]", "{ and }", "!" ....

All of these queries are right:

SELECT q'[I don't like my brother's car]'

  FROM DUAL

 

SELECT q'{I don't like my brother's car}'

  FROM DUAL

Even:

SELECT q'ZI don't like my brother's carZ'

  FROM DUAL




Saad Nayef,

Converting rows to columns and vice versa:

          Converting rows to columns:

Sometimes we need some reports that somehow require converting rows to columns. Here we are going to put a simple example on how to do so.

We all know emp table in scott schema that contains deptno column, and we know that deptno column has three distinct values: 10, 20 and 30 –no value 40, but any way you can include it to make sure-.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	17-Nov-81	5000	-	10
7698	BLAKE	MANAGER	7839	1-May-81	2850	-	30
7782	CLARK	MANAGER	7839	9-Jun-81	2450	-	10
7566	JONES	MANAGER	7839	2-Apr-81	2975	-	20
7788	SCOTT	ANALYST	7566	9-Dec-82	3000	-	20
7902	FORD	ANALYST	7566	3-Dec-81	3000	-	20
7369	SMITH	CLERK	7902	17-Dec-80	800	-	20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7844	TURNER	SALESMAN	7698	8-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100	-	20
7900	JAMES	CLERK	7698	3-Dec-81	950	-	30
7934	MILLER	CLERK	7782	23-Jan-82	1300	-	10

 Let’s say we need a report that shows each department –as columns- and its employees under it:

SELECT empno, 


       DECODE(deptno, 10, ename) "10", 
       DECODE(deptno, 20, ename) "20", 
       DECODE(deptno, 30, ename) "30" 
FROM   emp 
ORDER  BY 2, 3, 4; 

EMPNO	10	20	30
7782	CLARK	-	-
7839	KING	-	-
7934	MILLER	-	-
7876	-	ADAMS	-
7902	-	FORD	-
7566	-	JONES	-
7788	-	SCOTT	-
7369	-	SMITH	-
7499	-	-	ALLEN
7698	-	-	BLAKE
7900	-	-	JAMES
7654	-	-	MARTIN
7844	-	-	TURNER
7521	-	-	WARD

If we want to employees count for each department:

SELECT COUNT(DECODE(deptno, 10, ename)) "10", 


       COUNT(DECODE(deptno, 20, ename)) "20", 
       COUNT(DECODE(deptno, 30, ename)) "30" 
FROM   emp 
GROUP  BY deptno ;

10	20	30
0	0	6
0	5	0
3	0	0

And so on we can convert rows to columns as long as the values we want to make them as columns are not so many.

Note that the key is DECODE function.

-          Converting columns to rows:

Create test table:

CREATE TABLE convert_c2r 


  ( 
     id     NUMBER, 
     value1 NUMBER, 
     value2 NUMBER, 
     value3 NUMBER, 
     value4 NUMBER 
  ); 

Insert random data:

INSERT INTO convert_c2r 


VALUES      (1, 1, 2, 3, 4); 

INSERT INTO convert_c2r 
VALUES      (2, 2, 4, 6, 8); 

INSERT INTO convert_c2r 
VALUES      (3, 3, 6, 9, 12);

INSERT INTO convert_c2r 
VALUES      (4, 4, 8, 12, 16);

View table data to ckeck:

SELECT * 


FROM   convert_c2r; 

ID	VALUE1	VALUE2	VALUE3	VALUE4
1	1	2	3	4
2	2	4	6	8
3	3	6	9	12
4	4	8	12	16

and to convert columns to rows:

SELECT id, 


       ser, 
       DECODE(ser, '1', value1, 
                   '2', value2, 
                   '3', value3, 
                   '4', value4) d 
FROM   convert_c2r, 
       (SELECT '1' ser 
        FROM   dual 
        UNION 
        SELECT '2' ser 
        FROM   dual 
        UNION 
        SELECT '3' ser 
        FROM   dual 
        UNION 
        SELECT '4' ser 
        FROM   dual) ; 

ID	SER	D
1	1	1
2	1	2
3	1	3
4	1	4
1	2	2
2	2	4
3	2	6
4	2	8
1	3	3
2	3	6
3	3	9
4	3	12
1	4	4
2	4	8
3	4	12
4	4	16

Saad Nayef,