June 13, 2011

Example of PL/Sql for monthly sales


 declare  
 start_date date;  
 end_date date;  
 begin  
 select min(sales_date),max(sales_date)  
 into start_date, end_date  
 from t1;  
 for start_date <= end_date  
 loop  
 Insert Into t1 (ShopName , sales_Date, Sales)  
 Select t2.ShopName , nvl(t1.sales_Date,start_date), nvl(t1.Sales,0)  
 From t1, t2  
 Where t1.sales_Date=start_date;  
 start_date:=start_date+1;  
 end loop;  
 end;