18、oracle：存储过程

--心得一

create or replace procedure proc_LogTbSysOpmonitorDetail(matchStr1 in varchar2, matchStr2 in varchar2, lastOpTime in varchar2)

is
    num1 number;
    sqltext varchar2(1000);
    createtable_sql varchar2(1000);
    createindex_sql varchar2(1000);
    updatetable_sql varchar2(1000);
  begin
    select count(1) into num1 from all_tables where TABLE_NAME = 'TEMP_1' and owner = 'xxx123';
    if num1 = 1 then
      execute immediate 'drop table crm.temp_1';   
    end if;
  
    sqltext := 'grant update any table to xxx123'; 
    execute immediate sqltext;
  
    createtable_sql := '
    create table crm.temp_1 as
    SELECT a.DetailID, substr(opcontent,instr(opcontent,'''||matchStr1||''',-1) +4) AS OPTIME
    FROM LOG.aaabbbb     a
    WHERE 1=1
    AND OPTIME > to_date('''||lastOpTime||''',''yyyy-MM-dd hh24:mi:ss'')
    AND OPCONTENT LIKE '''||matchStr2||'''   
    AND buzobjecttype=1';
    dbms_output.put_line(createtable_sql);
    EXECUTE IMMEDIATE createtable_sql;    
    
    createindex_sql := 
    'CREATE INDEX idx_Tmp1_DetailId ON crm.temp_1(DetailID)';
    dbms_output.put_line(createindex_sql);
    execute immediate createindex_sql;  
    
    updatetable_sql := 
    'UPDATE LOG.aaabbbb    a
    SET OPTIME=(SELECT to_date(OPTIME,''yyyy-MM-dd hh24:mi:ss'') FROM crm.temp_1 b WHERE b.DETAILID=a.DETAILID)
    WHERE a.DETAILID IN (
        SELECT DETAILID FROM crm.temp_1
    )';
    dbms_output.put_line(updatetable_sql);
    execute immediate updatetable_sql;
    commit;
end proc_LogTbSysOpmonitorDetail;
----------------------------------------------------------------------------------------------------------------------------------------------------------
begin
  proc_LogTbSysOpmonitorDetail('【时间】', '%客户经%', '2017/8/21 15:48:41');
end; 
---------------------------------------------------------------------------------------------------------------------------------------------------------
/*
注意：1、到all_tables所有用户空间查询表的时候，表名必须大写；并且最好利用owner速度更快，到all_tab_comments里面找也可以找到（表名同样要大写）
            2、删除表格的时候直接使用('用户名.表名')就可以了
            3、对表格的修改，有可能没有权限，需要先grant授予权限
            4、外部传入最好是传字符串或者int类型，尤其是时间类型改用字符串型更方便
            5、外部变量传入后的连接，使用（'''|| 变量名 ||'''）的形式
            6、所有数据库表本身的字段不可以加单引号，只能使用原型

*/

存储过程一个总结的非常全的博客：http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html

--心得二：使用存储过程直接使用临时表保存结果更方便，不要使用输出参数游标查看结果，相比于上次写的直接创建表涉及到权限的问题不好解决，此时使用临时表更方便

create or replace procedure proc_InvalidCustMgrCntByDay(invalid_begintime in varchar2, invalid_endtime in varchar2)
as
   tabext varchar2(10);
   inva_delestr varchar2(500);
   inva_createstr varchar2(1000);
   begin
   
   select count(1) into tabext
   from all_tables
   where TABLE_NAME = 'INVALID_TMP';


   inva_delestr := 'delete from INVALID_TMP';
   inva_createstr := 'create global temporary table INVALID_TMP
   (
      customerid number(20) not null,
      customercode varchar(30) not null,
      customername varchar(100),
      ......
   )On Commit Preserve Rows';


   if tabext = 0 then
     execute immediate inva_createstr;
     dbms_output.put_line('创建临时表成功');
   else
     execute immediate inva_delestr;
     dbms_output.put_line('删除临时表记录完成');
   end if;
   
   execute immediate '
     insert into INVALID_TMP(customerid, customercode, customername, xxx)
     select c.customerid, d.customercode, d.customername, c.invalidcnt from 
      (
          with tmpT1 as
            (
               select distinct(customerid) from xxxx
                where 1=1
                and to_date('''||invalid_begintime||''',''yyyy-MM-dd HH24:mi:ss'') <= begintime   
                and end< to_date('''||invalid_endtime||''',''yyyy-MM-dd HH24:mi:ss'')
                ......
            )
            select a.customerid, count(distinct b.employeeid) xxx from xxxx b
            inner join tmpT1 a
            on b.customerid = a.customerid
            and b.begintime < to_date('''||invalid_endtime||''',''yyyy-MM-dd HH24:mi:ss'') 
            group by a.customerid
      )c
      inner join crm.tb_cus_customer d
      on d.customerid = c.customerid
      where 1=1
      and c.invalidcnt>1
      order by d.customerid
   ';
   commit;
end proc_InvalidCustMgrCntByDay;

---------------------------------------------------------------------------------------------------------
--执行存储过程
declare 
invalidbegintime varchar2(30);
invalidendtime varchar2(30);
begin
  invalidbegintime := '2017/1/1 00:00:00';
  invalidendtime := '2017/9/1 00:00:00';
  proc_InvalidCustMgrCntByDay(invalidbegintime, invalidendtime);
end;
---------------------------------------------------------------------------------------------------------
--查看结果
select * from INVALID_TMP
---------------------------------------------------------------------------------------------------------

分享一篇觉得写得很好的文章：

http://blog.csdn.net/zengcong2013/article/details/17185367