Simple use of Oracle stored procedures

Today, I encountered a problem where I needed to query data within two months, but the SQL statement could not be implemented. I wrote a simple stored procedure to implement the required functions. The PLSQL tool is used to output data in the stored procedure. 1. Create a "testwindow" (namely, a test window) and write the code in it. 2. Then

Today, I encountered a problem where I needed to query data within two months, but the SQL statement could not be implemented. I wrote a simple stored procedure to implement the required functions. The PL/SQL tool is used to output data in the stored procedure. 1. Create a "test window" (that is, a test window) and write code in it. 2. Then

Today, I encountered a problem where I needed to query data within two months, but the SQL statement could not be implemented. I wrote a simple stored procedure to implement the required functions.

The PL/SQL tool is used to output data in the stored procedure.

1. Create a "test window" (that is, a test window) and write code in it.

2. Write your own code. My code is as follows:

You can also set the parameter as needed.

declare  v_date   date := to_date('2014-11-01', 'yyyy-mm-dd');  e_date   date := to_date('2014-11-30', 'yyyy-mm-dd');  v_cur    sys_refcursor;  v_pay    number;  v_source varchar2(20);  v_new    number;  v_invest number;  p_date   date;begin  while (v_date <= e_date) loop    open v_cur for      select v_date,             aa.source,             nvl(aa.pay_money, 0),             nvl(bb.new_open_nums, 0),             nvl(cc.invest, 0)        from (select sum(order_money_rmb) as pay_money, source                from (select distinct order_id, order_money_rmb, source                        from pps_adsmd_wt_report_order t                       where t.addtime >= v_date                         and t.addtime < v_date + 1                         and t.order_kind >= 1                         and t.order_kind <= 30                         and t.user_type = 1)               group by source) aa        left join (select sum(bb.new_open_nums) as new_open_nums, bb.source                     from pps_adsmd_wt_report bb                    where bb.report_date >= v_date                      and bb.report_date < v_date + 1                    group by bb.source) bb          on aa.source = bb.source        left join (select sum(t1.cost_money) as invest, source_name                     from wt_adsmd_source_cost t1                    where t1.start_time = v_date                    group by source_name) cc          on aa.source = cc.source_name;    v_date := v_date + 1;    loop      fetch v_cur        into p_date, v_source, v_pay, v_new, v_invest;      exit when v_cur%notfound;      DBMS_OUTPUT.PUT_LINE(p_date || '  ' || v_source || '  ' || v_pay || '  ' ||                           v_new || '  ' || v_invest);    end loop;  end loop;end;

3. Run the SQL statement. The result is displayed in the output.

This record is only available to individuals.

Zookeeper