CODEWITHSUNDEEP
sql
Davide
Davide

Reputation: 57

PL/SQL Skip value on basic loop

I am new to PL/SQL and just want to know what I can do here.

I have created a table that loops a counter up to 10 that is displayed in the table data.

How do I achieve it so I can count to 1-10 but exclude a number such a 5 so that it displays 1, 2, 3, 4, 6, 7, 8, 9, 10?

Current code is as follows;

DROP TABLE COUNTER

CREATE TABLE COUNTER (
COUNTER VARCHAR2(60)
);

DECLARE V_COUNTER NUMBER(2) := 1;

BEGIN 
LOOP
INSERT INTO COUNTER (COUNTER)
VALUES (V_COUNTER);

V_COUNTER := V_COUNTER + 1

EXIT WHEN V_COUNTER = 11;
END LOOP;
END;

Table data;

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10

Upvotes: 0

Views: 828

Answers (2)

Elzzz
Elzzz

Reputation: 131

In general it's better to process such statements in bulk and avoid context switching ( here's more info on this topic.

DECLARE
    TYPE t_values IS
        TABLE OF NUMBER;
    l_values t_values;
BEGIN
    SELECT
        level counter
    BULK COLLECT
    INTO l_values
    FROM
        dual
    WHERE
        level NOT IN (
            6,
            7
        )
    CONNECT BY
        level <= 11;

    FORALL i IN l_values.first..l_values.last
        INSERT INTO counter VALUES ( l_values(i) );

END;
/

if code is simple enough you can always use plain insert stmt.

INSERT INTO counter
    SELECT
        level
    FROM
        dual
    WHERE
        level NOT IN (
            6,
            7
        )
    CONNECT BY
        level <= 11;

Upvotes: 0

doberkofler
doberkofler

Reputation: 10341

Something like:

DROP TABLE COUNTER
CREATE TABLE COUNTER (COUNTER VARCHAR2(60));

BEGIN
   FOR i IN 1 .. 10 LOOP
      IF i <> 5 THEN
         INSERT INTO COUNTER (COUNTER) VALUES (i);
      END IF;
   END LOOP;
END;
/

Upvotes: 1

Related Questions