CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Ravi Tej
Ravi Tej

Reputation: 3

How to make this code dynamic

I want to make this code dynamic i.e in place of n_numb number := 1990 if i enter a different year it should execute based on that year you enter.

set SERVEROUTPUT ON

declare

  v_test varchar2(80) := 'BORN';
  n_numb number := 1990;
begin

  while v_test <> 'Birthday' 
 loop 
      if    n_numb = 1991 then v_test := 'Birthday';
      end if;
    dbms_output.put_line (v_test||': '||n_numb);
    n_numb := n_numb + 1;
  end loop;

 v_test := 'Playschool';
  while n_numb < 1994 AND v_test = 'Playschool' loop
     dbms_output.put_line (v_test||': '||n_numb);
      n_numb := n_numb + 1;
 end loop;

 v_test := 'Regularschool';
  while n_numb < 2007 AND v_test = 'Regularschool' loop
     dbms_output.put_line (v_test||': '||n_numb);
      n_numb := n_numb + 1;
 end loop;

  v_test := 'Engineering';
  while n_numb < 2011 AND v_test = 'Engineering' loop
     dbms_output.put_line (v_test||': '||n_numb);
      n_numb := n_numb + 1;
 end loop;

  v_test := 'SearchingJob';
  while n_numb < 2013 AND v_test = 'SearchingJob' loop
     dbms_output.put_line (v_test||': '||n_numb);
      n_numb := n_numb + 1;
 end loop;

  v_test := 'Working';
  while n_numb < 2014 AND v_test = 'Working' loop
     dbms_output.put_line (v_test||': '||n_numb);
      n_numb := n_numb + 1;
 end loop;
 end;

Upvotes: 0

Views: 116

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

To make this code take a year input and print out based on the years elapsed, you will need to switch from changing the activity based on year-number to years-accumulated.

I'll add a couple examples below.
The first will modify your original code to allow switching n_numb.
The second will refactor to use procedures to simplify things.

For a first example, I'll start with your original code, and add an accumulator V_YEAR_ACCUMULATION to measure the years. Then changing n_numb to different values will get a different output. 

DECLARE
  V_TEST VARCHAR2(80) := 'BORN';
  N_NUMB NUMBER := 1990;
  V_YEAR_ACCUMULATION NUMBER := 0;
BEGIN

  WHILE V_TEST <> 'Birthday'
  LOOP
    IF V_YEAR_ACCUMULATION = 1
    THEN V_TEST := 'Birthday';
    END IF;
    DBMS_OUTPUT.put_line(V_TEST || ': ' || N_NUMB);
    N_NUMB := N_NUMB + 1;
    V_YEAR_ACCUMULATION := V_YEAR_ACCUMULATION + 1;
  END LOOP;

  V_TEST := 'Playschool';
  WHILE V_YEAR_ACCUMULATION < 4 AND V_TEST = 'Playschool' LOOP
    DBMS_OUTPUT.put_line(V_TEST || ': ' || N_NUMB);
    N_NUMB := N_NUMB + 1;
    V_YEAR_ACCUMULATION := V_YEAR_ACCUMULATION + 1;
  END LOOP;

  V_TEST := 'Regularschool';
  WHILE V_YEAR_ACCUMULATION < 17 AND V_TEST = 'Regularschool' LOOP
    DBMS_OUTPUT.put_line(V_TEST || ': ' || N_NUMB);
    N_NUMB := N_NUMB + 1;
    V_YEAR_ACCUMULATION := V_YEAR_ACCUMULATION + 1;
  END LOOP;

  V_TEST := 'Engineering';
  WHILE V_YEAR_ACCUMULATION < 21 AND V_TEST = 'Engineering' LOOP
    DBMS_OUTPUT.put_line(V_TEST || ': ' || N_NUMB);
    N_NUMB := N_NUMB + 1;
    V_YEAR_ACCUMULATION := V_YEAR_ACCUMULATION + 1;
  END LOOP;

  V_TEST := 'SearchingJob';
  WHILE V_YEAR_ACCUMULATION < 23 AND V_TEST = 'SearchingJob' LOOP
    DBMS_OUTPUT.put_line(V_TEST || ': ' || N_NUMB);
    N_NUMB := N_NUMB + 1;
    V_YEAR_ACCUMULATION := V_YEAR_ACCUMULATION + 1;
  END LOOP;

  V_TEST := 'Working';
  WHILE V_YEAR_ACCUMULATION < 24 AND V_TEST = 'Working' LOOP
    DBMS_OUTPUT.put_line(V_TEST || ': ' || N_NUMB);
    N_NUMB := N_NUMB + 1;
    V_YEAR_ACCUMULATION := V_YEAR_ACCUMULATION + 1;
  END LOOP;
END;
/

And test it:

using n_numb := 1990 gives:

BORN: 1990
Birthday: 1991
Playschool: 1992
Playschool: 1993
Regularschool: 1994
...
...
SearchingJob: 2012
Working: 2013

but with n_numb := 1899, you get:

BORN: 1899
Birthday: 1900
Playschool: 1901
Playschool: 1902
Regularschool: 1903

But, there is some refactoring that can simplify things.
In this second example, I'll create procedures to do the printing and move from one activity to the next as the years proceed. This will require less code.

First, create a procedure to print the current activity and progress the years:

CREATE OR REPLACE PROCEDURE GROW_OLDER(P_ACTIVITY IN VARCHAR2, P_YEARS_TO_CONTINUE IN NUMBER, P_YEARS_ACCUMULATED IN OUT NUMBER)
IS
  V_LOCAL_ACCUMULATION NUMBER := 0;
  BEGIN
    WHILE V_LOCAL_ACCUMULATION < P_YEARS_TO_CONTINUE LOOP
      DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('%s: %s',P_ACTIVITY,TO_CHAR(P_YEARS_ACCUMULATED + V_LOCAL_ACCUMULATION)));
      V_LOCAL_ACCUMULATION := V_LOCAL_ACCUMULATION + 1;
      END LOOP;
    P_YEARS_ACCUMULATED := P_YEARS_ACCUMULATED + P_YEARS_TO_CONTINUE;
  END;
/

Then create a procedure to orchestrate the movement from activity to activity across the years:

CREATE OR REPLACE PROCEDURE MOVE_THROUGH_THE_YEARS(P_START_YEAR IN NUMBER)
IS
  V_YEAR_ACCUMULATION NUMBER := P_START_YEAR;
  BEGIN
    GROW_OLDER('Born', 1, V_YEAR_ACCUMULATION);
    GROW_OLDER('Birthday', 1, V_YEAR_ACCUMULATION);
    GROW_OLDER('Playschool', 2, V_YEAR_ACCUMULATION);
    GROW_OLDER('Regularschool', 13, V_YEAR_ACCUMULATION);
    GROW_OLDER('Engineering', 4, V_YEAR_ACCUMULATION);
    GROW_OLDER('SearchingJob', 2, V_YEAR_ACCUMULATION);
    GROW_OLDER('Working', 1, V_YEAR_ACCUMULATION);
  END;
/

Then Test it. Starting 1990:

BEGIN
  MOVE_THROUGH_THE_YEARS(1990);
END;
/

Born: 1990
Birthday: 1991
Playschool: 1992
Playschool: 1993
Regularschool: 1994
Regularschool: 1995
...
...
Engineering: 2010
SearchingJob: 2011
SearchingJob: 2012
Working: 2013

Or 2077: 

BEGIN
  MOVE_THROUGH_THE_YEARS(2077);
END;
/

Born: 2077
Birthday: 2078
Playschool: 2079
Playschool: 2080
Regularschool: 2081
Regularschool: 2082
...
...
Engineering: 2097
SearchingJob: 2098
SearchingJob: 2099
Working: 2100

EDIT If you only want to print each activity once, you can replace the GROW_OLDER procedure above with an alternative that does no looping.

CREATE OR REPLACE PROCEDURE GROW_OLDER(P_ACTIVITY IN VARCHAR2, P_YEARS_TO_CONTINUE IN NUMBER, P_YEARS_ACCUMULATED IN OUT NUMBER)
IS
  BEGIN
    DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('%s: %s',P_ACTIVITY,TO_CHAR(P_YEARS_ACCUMULATED)));
    P_YEARS_ACCUMULATED := P_YEARS_ACCUMULATED + P_YEARS_TO_CONTINUE;
  END;
/

CREATE OR REPLACE PROCEDURE MOVE_THROUGH_THE_YEARS(P_START_YEAR IN NUMBER)
IS
  V_YEAR_ACCUMULATION NUMBER := P_START_YEAR;
  BEGIN
    GROW_OLDER('Born', 1, V_YEAR_ACCUMULATION);
    GROW_OLDER('Birthday', 1, V_YEAR_ACCUMULATION);
    GROW_OLDER('Playschool', 2, V_YEAR_ACCUMULATION);
    GROW_OLDER('Regularschool', 13, V_YEAR_ACCUMULATION);
    GROW_OLDER('Engineering', 4, V_YEAR_ACCUMULATION);
    GROW_OLDER('SearchingJob', 2, V_YEAR_ACCUMULATION);
    GROW_OLDER('Working', 1, V_YEAR_ACCUMULATION);
  END;
/

Then test it:

BEGIN
  MOVE_THROUGH_THE_YEARS(1990);
END;
  /

Result:

Born: 1990
Birthday: 1991
Playschool: 1992
Regularschool: 1994
Engineering: 2007
SearchingJob: 2011
Working: 2013

Edit If you need an anonymous block with one row per activity, here's an example:

DECLARE
  V_YEARS NUMBER := 1990;
  BEGIN
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('Born: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 1;
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('Birthday: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 1;
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('Playschool: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 2;
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('Regularschool: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 13;
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('Engineering: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 4;
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('SearchingJob: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 2;
  DBMS_OUTPUT.put_line(UTL_LMS.FORMAT_MESSAGE('Working: '||TO_CHAR(V_YEARS)));
  V_YEARS := V_YEARS + 1;
END;
/

Output is same as all the other examples:

Born: 1990
Birthday: 1991
Playschool: 1992
Regularschool: 1994
Engineering: 2007
SearchingJob: 2011
Working: 2013

Upvotes: 1

Related Questions