练习九 组函数应用

1 表数据

 

SQL> select * from contract_kangjia;

         1 VIVI                 10-10月-14     1                          1000  
         2 VIVI                 10-9月 -14     2                          1000  
         3 VIVI                 10-10月-14     3                          1000  
         4 LILI                 10-10月-14     1                          1000  
         5 LILI                 10-9月 -14     2                          1000  
         6 LILI                 10-10月-14     3                          1000  

已选择6行。

2 查询vivi九月与十月销售差额

效率高

create or replace procedure TEST_KANGJIA2(Name_bi in VARCHAR2,
                                          v_date1 in date,
                                          v_date2 in date) is
  /***************************************************************
  *NAME : BATCH_INSERT_TABLEA_P
  *PURPOSE : --统计某个员工两个月份销售金额差额
  *IMPUT : --  表名子
  *OUTPUT : -- N/A
  *Author : -- CICI
  *CreateDate : -- 2014、10、17
  *UpdateDate : --
  ************************************************************/
  V_diff_money NUMBER(10, 5);
  v_timer2     date;
  v_timer1     date;
  ----V_diff_timer= 6000
begin
  select sysdate into v_timer1 from dual;

  for control_val in 0 .. 111111 loop
    select STDDEV(sell_money)
      into V_diff_money
      from (select busiman_name, sell_money, sell_date
              from CONTRACT_KANGJIA
             where (sell_date = v_date1 or sell_date = v_date2)
               and busiman_name = Name_bi)
     group by busiman_name;
   -- dbms_output.put_line('V_busiman_name= ' || Name_bi);
   -- dbms_output.put_line('V_sell_money=' || V_diff_money);
   -- dbms_output.put_line('date1 =' || v_date1);
   -- dbms_output.put_line('date2 =' || v_date2);
  end loop;
  select sysdate into v_timer2 from dual;
  --计算毫秒级别时间差V_diff_timer= 6000

  dbms_output.put_line('V_diff_timer= ' ||
                       ROUND(TO_NUMBER(v_timer2 - v_timer1) * 24 * 60 * 60 * 1000));
end TEST_KANGJIA2;

  /* call
  TEST_KANGJIA2
  ('VIVI',TO_DATE('2014/09/10','YYYY/MM/DD'),TO_DATE('2014/10/10','YYYY/MM/DD'));*/

效率低

create or replace procedure diff_money_kangjia(name_business in varchar2,
                                               v_date1       in date,
                                               v_date2       in date) as
  v_diff_money varchar2(20);
  v_timer1     date;
  v_timer2     date;
begin
  select sysdate into v_timer1 from dual;

  for control_val in 0 .. 111111 loop
    select abs(a.s10 - b.s09) into v_diff_money
      from (select sum(sell_money) as s10
              from contract_kangjia
             where busiman_name = name_business
               and to_char(sell_date, 'yyyy/mm') =
                   to_char(v_date1, 'yyyy/mm')) a,
           (select sum(sell_money) as s09
              from contract_kangjia
             where busiman_name = name_business
               AND to_char(sell_date, 'yyyy/mm') =
                   to_char(v_date2, 'yyyy/mm')) b;
  end loop;
  select sysdate into v_timer2 from dual;
  --计算毫秒级别时间差V_diff_timer= 10000
  dbms_output.put_line('V_diff_timer= ' ||
                       ROUND(TO_NUMBER(v_timer2 - v_timer1) * 24 * 60 * 60 * 1000));
end diff_money_kangjia;
  /* call
  diff_money_kangjia
  ('VIVI',TO_DATE('2014/09/10','YYYY/MM/DD'),TO_DATE('2014/10/10','YYYY/MM/DD'));*/

 

 

 

3 查询vivi九月与十月销售差值比例

create or replace procedure TEST_KANGJIA3(Name_bi in VARCHAR2,
                                          v_date1 in date,
                                          v_date2 in date) is
  /*统计某个员工两个月份销售金额差值百分比*/
  V_diff_money VARCHAR2(20);

begin
   select   round((GREATEST (a.s,b.s)-least(a.s,b.s))/GREATEST (a.s,b.s), 2) * 100 || '%' into V_diff_money
     from (select sum(sell_money) s
             from contract_kangjia
            where busiman_name = Name_bi
              AND SELL_DATE = (To_date(v_date1, 'yyyy/mm/dd'))) a,
           (select sum(sell_money) s
                     from contract_kangjia
                    where busiman_name = Name_bi
                      AND SELL_DATE = (To_date(v_date2, 'yyyy/mm/dd'))) b;

  dbms_output.put_line('V_diff_money= ' || V_diff_money);
end TEST_KANGJIA3;

/*
call
TEST_KANGJIA3
('VIVI',TO_DATE('2014/09/10','YYYY/MM/DD'),TO_DATE('2014/10/10','YYYY/MM/DD'));
*/

 ORA-01861文字与格式字符串不匹配