1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to use variable in PL/SQL Block

Discussion in 'SQL PL/SQL' started by roshan.zanwar, Aug 30, 2013.

  1. Following is my query in Oracle.

    WITH years AS (
    SELECT ROWNUM rn
    FROM dual
    CONNECT BY LEVEL <= (select round( SYSDATE-to_date('2010-01-01','YYYY-MM-DD')) from dual))
    Select
    to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') * 10000 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'mm') * 100 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As Date_ID
    ,to_date('2010-01-01','YYYY-MM-DD')-1 +rn As "Date"
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') As Year
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'MM') As Month_Number
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Month') As Month
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As DayOfMonth
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Day') As DayOfWeek
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'D') As DayOfWeekNo
    ,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Q') As Quarter
    From years;



    If you run this query you will see my expected output.

    Now my concern is i don't want to hard code date. In above query i have hard coded the date '2010-01-01'.
    Can we somehow use it from variable? :confused::confused:
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Roshan,

    This query should be in sql pl/sql section. Please follow the section wise queries for immediate responses. For now am moving this thread to sql pl/sql section.

    Yes, we can use variable in your query. Please find the following sample code:

    Code (Text):

    declare
    date1 varchar2(15):='2013-08-25';
    max_val number;
    a number;
    b date;
    c number;
    d number;
    e varchar2(10);
    f number;
    g varchar2(13);
    h number;
    i number;

    j number;


    begin
    select round( SYSDATE-to_date(date1,'YYYY-MM-DD')) into max_val from dual;

    for j in 1 .. max_val loop

    Select
    to_char(to_date(date1,'YYYY-MM-DD')-1 +j ,'yyyy') * 10000 + to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'mm') * 100 + to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'dd') As Date_ID
    ,to_date(date1,'YYYY-MM-DD')-1 +j As "Date"
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j ,'yyyy') As Year
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'MM') As Month_Number
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Month') As Month
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'dd') As DayOfMonth
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Day') As DayOfWeek
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'D') As DayOfWeekNo
    ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Q') As Quarter into a,b,c,d,e,f,g,h,i
    From dual;

    dbms_output.put_line(a||'  '||b||'  '||c||'  '||d||'  '||e||'  '||f||'  '||g||'  '||h||'  '||i);

    end loop;
    end;
     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Of course without 'set serveroutput on size 1000000' (or some other size of your choosing) you won't see anything except this:

    Code (SQL):
    SQL> DECLARE
      2  date1 varchar2(15):='2013-08-25';
      3  max_val    NUMBER;
      4  a NUMBER;
      5  b DATE;
      6  c NUMBER;
      7  d NUMBER;
      8  e varchar2(10);
      9  f NUMBER;
     10  g varchar2(13);
     11  h NUMBER;
     12  i NUMBER;
     13
     14  j NUMBER;
     15
     16
     17  BEGIN
     18
     19  dbms_output.enable(1000000);
     20
     21  SELECT round( SYSDATE-to_date(date1,'YYYY-MM-DD')) INTO max_val FROM dual;
     22
     23  FOR j IN 1 .. max_val loop
     24
     25  SELECT
     26  to_char(to_date(date1,'YYYY-MM-DD')-1 +j ,'yyyy') * 10000 + to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'mm') * 100 + to_char(to_date(DATE
    1,'YYYY-MM-DD')-1 +j,'dd') AS Date_ID
     27  ,to_date(date1,'YYYY-MM-DD')-1 +j AS "Date"
     28  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j ,'yyyy') AS YEAR
     29  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'MM') AS Month_Number
     30  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Month') AS MONTH
     31  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'dd') AS DayOfMonth
     32  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Day') AS DayOfWeek
     33  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'D') AS DayOfWeekNo
     34  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Q') AS Quarter INTO a,b,c,d,e,f,g,h,i
     35  FROM dual;
     36
     37  dbms_output.put_line(a||'  '||b||'  '||c||'  '||d||'  '||e||'  '||f||'  '||g||'  '||h||'  '||i);
     38
     39  END loop;
     40  END;
     41  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    It does work but no one will know that until they do this:

    Code (SQL):
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> DECLARE
      2  date1 varchar2(15):='2013-08-25';
      3  max_val    NUMBER;
      4  a NUMBER;
      5  b DATE;
      6  c NUMBER;
      7  d NUMBER;
      8  e varchar2(10);
      9  f NUMBER;
     10  g varchar2(13);
     11  h NUMBER;
     12  i NUMBER;
     13
     14  j NUMBER;
     15
     16
     17  BEGIN
     18
     19  SELECT round( SYSDATE-to_date(date1,'YYYY-MM-DD')) INTO max_val FROM dual;
     20
     21  FOR j IN 1 .. max_val loop
     22
     23  SELECT
     24  to_char(to_date(date1,'YYYY-MM-DD')-1 +j ,'yyyy') * 10000 + to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'mm') * 100 + to_char(to_date(DATE
    1,'YYYY-MM-DD')-1 +j,'dd') AS Date_ID
     25  ,to_date(date1,'YYYY-MM-DD')-1 +j AS "Date"
     26  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j ,'yyyy') AS YEAR
     27  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'MM') AS Month_Number
     28  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Month') AS MONTH
     29  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'dd') AS DayOfMonth
     30  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Day') AS DayOfWeek
     31  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'D') AS DayOfWeekNo
     32  ,to_char(to_date(date1,'YYYY-MM-DD')-1 +j,'Q') AS Quarter INTO a,b,c,d,e,f,g,h,i
     33  FROM dual;
     34
     35  dbms_output.put_line(a||'  '||b||'  '||c||'  '||d||'  '||e||'  '||f||'  '||g||'  '||h||'  '||i);
     36
     37  END loop;
     38  END;
     39  /
    20130825  25-AUG-13  2013  8  August     25  Sunday     1  3
    20130826  26-AUG-13  2013  8  August     26  Monday     2  3
    20130827  27-AUG-13  2013  8  August     27  Tuesday    3  3
    20130828  28-AUG-13  2013  8  August     28  Wednesday  4  3
    20130829  29-AUG-13  2013  8  August     29  Thursday   5  3
    20130825  25-AUG-13  2013  8  August     25  Sunday     1  3
    20130826  26-AUG-13  2013  8  August     26  Monday     2  3
    20130827  27-AUG-13  2013  8  August     27  Tuesday    3  3
    20130828  28-AUG-13  2013  8  August     28  Wednesday  4  3
    20130829  29-AUG-13  2013  8  August     29  Thursday   5  3
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    Bharat likes this.