--心得一
create or replace procedure proc_LogTbSysOpmonitorDetail(matchStr1 in varchar2, matchStr2 in varchar2, lastOpTime in varchar2)
isnum1 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