mercredi 19 septembre 2018

Change value of variable in pl/sql

I am running a unix *_sh script but with methods that use pl/sql in it.

Method in the script:

method(){

sqlplus -s ${DB_CONNECTION} << EOF
set echo on;
set timing on;
set serveroutput on;

DECLARE
      v_monthNow Date:=to_date('2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
      v_charMonthNow Varchar(5):=to_char(v_monthNow,'MM');
      v_charMonthBefore Varchar(5) :=to_char(v_monthNow, 'MM')-1;

BEGIN
      dbms_output.Put_line('SYSDATE is '|| v_monthNow ||' || MonthNow is '|| v_charMonthNow);
      IF v_charMonthBefore = '0'
           set v_charMonthBefore = '12'
           dbms_output.Put_line('MonthBefore is '|| v_charMonthBefore);
      ELSE
           dbms_output.Put_line('MonthBefore is '|| v_charMonthBefore);

END;
/
EOF
}

My question is: how do I set value of v_charMonthBefore to what I want?

Actually if sysdate is Jan 2018, i need v_monthNow=1 and v_charMonthBefore=12

if sysdate is Feb 2018, i need v_monthNow=2 and v_charMonthBefore=1

Other way to do this is not required, i just need to fix what i have now as i am not really familiar with pl/sql. Thank you

Aucun commentaire:

Enregistrer un commentaire