ORACLE DYNAMIC SQL Script

Attention:

1. the dynamic sql script do not need ';'

2. the ' in the script should be write as ''

3. there should be space in the script

 

ONE RECORD

v_script:='SELECT to_char(min(PRICE_DATE),''yyyy/mm/dd'') from '||v_table_name_1||'';

DBMS_OUTPUT.PUT_LINE(v_script);      

EXECUTE IMMEDIATE v_script7 into v_table1_start;

 

v_script:='select description from dm_convert where result='''||v_t_result_1_tmp||'''';

dbms_output.put_line(v_script);

EXECUTE IMMEDIATE v_script into v_t_result_1;

 

MORE THAN ONE record

v_script:='SELECT price from '||v_table_name_1||' where price_date between to_date('''||v_table1_start||''',''yyyy/mm/dd'') and to_date('''||v_table1_end||''',''yyyy/mm/dd'') order by '||v_table_name_1||'.price_date';

v_t1_id:=1;

DBMS_OUTPUT.PUT_LINE(v_script);      

open v_t1_cursor for v_script5;

loop

fetch v_t1_cursor into v_t1_price;

exit when v_t1_cursor%notfound;

IF v_t1_id<>1 then

         v_t1_process:= (v_t1_price - v_t1_price_prev) / v_t1_price_prev;

         case

         when v_t1_process > 0.1 then v_t1_result:='A';

         when v_t1_process <= 0.1 and v_t1_process > 0.05 then v_t1_result:='B';

         when v_t1_process <= 0.05 and v_t1_process > 0 then v_t1_result:='C';

         when v_t1_process = 0 then v_t1_result:='D';

         when v_t1_process <0 and v_t1_process >= -0.05 then v_t1_result:='E';     

         when v_t1_process < -0.05 and v_t1_process > -0.1 then v_t1_result:='F';

         when v_t1_process <= -0.1 then v_t1_result:='G';

         end case;

         v_t1_price_prev:=v_t1_price;

      ELSE

         v_t1_process:=0;         

         v_t1_price_prev:=v_t1_price;

         v_t1_result:='D';

      END IF;

      insert into DM_COMPARE_1 values(v_t1_id,v_t1_price,v_t1_process,v_t1_result);      

      commit;

      v_t1_id := v_t1_id +1;

      end loop;

      close v_t1_cursor;