wahaha603
- 浏览: 80056 次
- 来自: 上海
-
文章分类
最新评论
-
wahaha603：
<div class="quote_title ...
extjs grid 导出到excel -
豆豆糖：
var vExportContent = grid.getEx ...
extjs grid 导出到excel -
liguanghua：
var fd=Ext.get('frmDummy'); ...
extjs grid 导出到excel -
liguanghua：
按照您的方法写了，好像点击导出按钮没反应，
extjs grid 导出到excel -
wahaha603：
datawarehouse 写道缺了好几个建表语句。
写给自己 ...
oracle pl/sql 存储过程编写
create or replace package p_kfgl_bbtj is
-- Author : liucp
-- Created : 2012/7/2 10:01:28
-- Purpose :
--统计查询
procedure usp_bbtj(as_s_action in varchar2, -- 查询名目： 1=日报，2=周报，3=月报，4=员工服务，5=分点
as_s_cate in varchar2, --查询类别
as_s_begindate in varchar2, --开始时间
as_s_enddate in varchar2, --结束时间
as_s_year in varchar2, --查询年份
as_s_staff in varchar2, --查询员工
as_s_zone in varchar2, --查询地区
as_s_userid in varchar2, --执行人id
as_s_result out varchar2, --返回的统计结果
as_s_errorcode out varchar2,
as_s_errortext out varchar2);
end p_kfgl_bbtj;
create or replace package body p_kfgl_bbtj is
--统计查询
procedure usp_bbtj(as_s_action in varchar2, -- 查询名目： 1=日报，2=周报，3=月报，4=员工服务，5=分点
as_s_cate in varchar2, --查询类别
as_s_begindate in varchar2, --开始时间
as_s_enddate in varchar2, --结束时间
as_s_year in varchar2, --查询年份
as_s_staff in varchar2, --查询员工
as_s_zone in varchar2, --查询地区
as_s_userid in varchar2, --执行人id
as_s_result out varchar2, --返回的统计结果
as_s_errorcode out varchar2,
as_s_errortext out varchar2)
is
v_s_cate varchar(20);
v_s_begindate date;
v_s_enddate date;
v_s_year date;
v_s_staff varchar(20);
v_s_zone varchar(20);
v_s_zd1 varchar(20);
v_s_zd2 varchar(20);
v_s_zd3 varchar(20);
v_s_zd4 varchar(20);
v_s_zd5 varchar(20);
v_s_zd6 varchar(20);
v_s_zd7 varchar(20);
v_s_zd8 varchar(20);
v_s_zd9 varchar(20);
v_s_zd10 varchar(20);
v_s_zd11 varchar(20);
v_s_zd12 varchar(20);
v_s_zd13 varchar(20);
v_s_zd14 varchar(20);
v_s_zd15 varchar(20);
v_s_id number(15);
v_s_result varchar2(500);
v_s_sql varchar2(500); --查询条件拼接
v_s_sql2 varchar2(500);--统计SQL
v_s_sql3 varchar2(500);
v_s_sql4 varchar2(500);
v_s_sql5 varchar2(500);
begin
as_s_errorcode := '0';
as_s_errortext := to_char(sysdate, 'YYYY-MM-DD');
as_s_result :='1';
-- 数据校验
/**
if(as_s_cate is null) then
as_s_errorcode := '101';
as_s_errortext := '统计类别不能为空';
return;
end if;
if(as_s_begindate is null) then
as_s_errorcode := '102';
as_s_errortext := '查询开始日期不能为空';
return;
end if;
if(as_s_enddate is null) then
as_s_errorcode := '103';
as_s_errortext := '查询结束日期不能为空';
return;
end if;
if(as_s_year is null) then
as_s_errorcode := '104';
as_s_errortext := '查询年份不能为空';
return;
end if;
**/
-- 清理之前数据
delete from temp_stb where zd14 = as_s_userid;
--日报统计
if(as_s_action ='1') then
-- 类别为空时默认为按服务方式 ：上门 热线 大厅等等来分
if(as_s_cate is not null) then
v_s_sql := ' and t.service_mode =''' || as_s_cate || '''' ;
end if;
if(as_s_zone is not null) then
v_s_sql := ' and t.userid in (select userid from app_user app where app.zone= ''' || as_s_zone || ''')' ||v_s_sql;
v_s_sql4 := ' and t.userid in (select userid from app_user app where app.zone= ''' || as_s_zone || ''')' ||v_s_sql;
end if;
if(as_s_cate is not null) then
for i in ( select to_char(min_date + rownum -1,'yyyy-mm-dd') start_time from (
select to_date(''||as_s_begindate ||'','yyyy-mm-dd') min_date,to_date(''||as_s_enddate ||'','yyyy-mm-dd') max_date from dual )
connect by rownum<= max_date-min_date+1) loop
v_s_zd1 := i.start_time;
v_s_sql3 := ' and to_char(t.start_time,''yyyy-mm-dd'') ='''||i.start_time|| '''' ||v_s_sql ;
v_s_sql5 := ' and to_char(t.start_time,''yyyy-mm-dd'') ='''||i.start_time|| '''' ||v_s_sql4 ;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''数字证书'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''网上申报'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''网上开票'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''应急开票'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''网购发票'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''涉税申请'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''业务政策'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''在线咨询'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd9;
v_s_sql2 := 'select count(1) from service_info t where t.service_kind=''其它类'' ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd10;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd11; -- 小计
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql5 ;
Execute Immediate v_s_sql2 into v_s_zd12; -- 总计。。。
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd9,
zd10,
zd11,
zd12,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd9,
v_s_zd10,
v_s_zd11,
v_s_zd12,
v_s_zd14
);
end loop;
end if;
end if;
--月报统计
if(as_s_action ='3') then
-- 类别为空时默认为按服务方式 ：上门 热线 大厅等等来分
if(as_s_zone is not null) then
v_s_sql := ' and t.userid in (select userid from app_user app where app.zone= ''' || as_s_zone || ''')';
end if;
if(as_s_year is null) then
as_s_errorcode := '301';
as_s_errortext := '查询年份不能为空';
return;
end if;
if(as_s_cate is null) then
for i in (select d.itemvalue as service_type from dictionary d where d.itemname='服务类（别）型') loop
v_s_zd1 := i.service_type;
v_s_sql3 := 'and t.service_mode ='''|| v_s_zd1 ||''' ' || v_s_sql ;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-01'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-02'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-03'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-04'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-05'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-06'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-07'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-08'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd9;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-09'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd10;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-10'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd11;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-11'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd12;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-12'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd13;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd9,
zd10,
zd11,
zd12,
zd13,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd9,
v_s_zd10,
v_s_zd11,
v_s_zd12,
v_s_zd13,
v_s_zd14
);
end loop;
else
for i in (select d.itemvalue as service_type from dictionary d where d.itemname='服务类别') loop
v_s_zd1 := i.service_type;
v_s_sql3 := 'and t.service_kind ='''|| v_s_zd1 ||''' ' || v_s_sql ;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-01'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-02'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-03'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-04'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-05'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-06'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-07'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-08'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd9;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-09'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd10;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-10'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd11;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-11'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd12;
v_s_sql2 := 'select count(1) from service_info t where (to_char(t.start_time,''yyyy-mm'')='''|| as_s_year ||'-12'' ) ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd13;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd9,
zd10,
zd11,
zd12,
zd13,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd9,
v_s_zd10,
v_s_zd11,
v_s_zd12,
v_s_zd13,
v_s_zd14
);
end loop;
end if;
end if;
--员工服务方式统计
if(as_s_action ='4') then
if(as_s_zone is not null) then
v_s_sql := ' and t.userid in (select userid from app_user app where app.zone= ''' || as_s_zone || ''')';
end if;
if(as_s_begindate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')>=''' || as_s_begindate || '''' ||v_s_sql ;
end if;
if(as_s_enddate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')<=''' || as_s_enddate || '''' ||v_s_sql;
end if;
if(as_s_zone is not null) then
for i in (select fullname, userid from app_user app where app.depid='1' and app.status='1' and app.zone='' || as_s_zone || '') loop
v_s_zd1 := i.fullname;
v_s_sql3 := 'and t.userid ='''|| i.userid ||''' ' ||v_s_sql;
v_s_sql2 := 'select count(1) from service_info t where 1=1' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''热线''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''大厅''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''上门''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''自助办税机''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''在线咨询''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''大厅辅助服务''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd14
);
end loop;
else
for i in (select fullname, userid from app_user app where app.depid='1' and app.status='1') loop
v_s_zd1 := i.fullname;
v_s_sql3 := 'and t.userid ='''|| i.userid ||''' ' ||v_s_sql;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''热线''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''大厅''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''上门''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''自助办税机''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''在线咨询''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''大厅辅助服务''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd14
);
end loop;
end if;
end if;
--分点（周）日报统计
if(as_s_action ='5') then
-- 类别为空时默认为按服务方式 ：上门 热线 大厅等等来分
if(as_s_begindate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')>=''' || as_s_begindate || '''' ||v_s_sql ;
end if;
if(as_s_enddate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')<=''' || as_s_enddate || '''' ||v_s_sql;
end if;
if(as_s_cate is null) then
for i in (select d.itemvalue as service_type from dictionary d where d.itemname='服务类（别）型') loop
v_s_zd1 := i.service_type;
v_s_sql3 := 'and t.service_mode ='''|| v_s_zd1 ||''' ' || v_s_sql ;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''昆山'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''张家港'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''常熟'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''太仓'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''园区'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''吴中'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''市局'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd9;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd9,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd9,
v_s_zd14
);
end loop;
else
for i in (select d.itemvalue as service_type from dictionary d where d.itemname='服务类别') loop
v_s_zd1 := i.service_type;
v_s_sql3 := 'and t.service_kind ='''|| v_s_zd1 ||''' ' || v_s_sql ;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''昆山'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''张家港'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''常熟'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''太仓'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''园区'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd6;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''吴中'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd7;
v_s_sql2 := 'select count(1) from service_info t where t.userid in (select userid from app_user app where app.zone=''市局'') ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd8;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd9;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd6,
zd7,
zd8,
zd9,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd6,
v_s_zd7,
v_s_zd8,
v_s_zd9,
v_s_zd14
);
end loop;
end if;
end if;
-- 首页推送 （服务类型服务量统计 上门 热线 大厅。。占比等）
if(as_s_action ='6') then
-- 默认当前时间为查询条件
if(as_s_begindate is null and as_s_enddate is null ) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'') = to_char(sysdate,''yyyy-MM-dd'')' ;
else
if(as_s_begindate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')>=''' || as_s_begindate || '''' ||v_s_sql ;
end if;
if(as_s_enddate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')<=''' || as_s_enddate || '''' ||v_s_sql;
end if;
end if;
if(as_s_zone is not null) then
v_s_sql := ' and t.userid in (select userid from app_user app where app.zone= ''' || as_s_zone || ''')';
end if;
if(as_s_cate is not null) then
for i in (select d.itemvalue as service_type from dictionary d where d.itemname='服务类（别）型') loop
v_s_zd1 := i.service_type;
v_s_sql3 := 'and t.service_mode ='''|| v_s_zd1 ||''' ' || v_s_sql ;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql ;
Execute Immediate v_s_sql2 into v_s_zd3;
-- v_s_zd3 :=(v_s_zd2/(v_s_zd3+0.0000001));
v_s_sql2 := 'SELECT ROUND('''||v_s_zd2||''' /('''||v_s_zd3||'''+0.000001), 3) * 100 || ''%'' FROM DUAL';
Execute Immediate v_s_sql2 into v_s_zd3;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd14
);
end loop;
end if;
end if;
--首页推送 （主要类别服务量统计 网上开票.数字证书 占比等）
if(as_s_action ='7') then
-- 默认当前时间为查询条件
if(as_s_begindate is null and as_s_enddate is null ) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'') = to_char(sysdate,''yyyy-MM-dd'')' ;
else
if(as_s_begindate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')>=''' || as_s_begindate || '''' ||v_s_sql ;
end if;
if(as_s_enddate is not null) then
v_s_sql := ' and to_char(t.start_time,''yyyy-MM-dd'')<=''' || as_s_enddate || '''' ||v_s_sql;
end if;
end if;
if(as_s_zone is not null) then
v_s_sql := ' and t.userid in (select userid from app_user app where app.zone= ''' || as_s_zone || ''')';
end if;
if(as_s_zone is not null) then
for i in (select d.itemvalue as service_type from dictionary d where d.itemname='服务类别') loop
v_s_zd1 := i.service_type;
v_s_sql3 := 'and t.service_kind ='''|| v_s_zd1 ||''' ' || v_s_sql ;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''热线''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd2;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''大厅''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd3;
v_s_sql2 := 'select count(1) from service_info t where t.service_mode =''上门''' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd4;
v_s_sql2 := 'select count(1) from service_info t where 1=1 ' || v_s_sql3 ;
Execute Immediate v_s_sql2 into v_s_zd5;
v_s_zd14 :=as_s_userid;
select S_TEMP_STB.NEXTVAL into v_s_id from dual;
insert into temp_stb (id,
zd1,
zd2,
zd3,
zd4,
zd5,
zd14)
values (v_s_id,
v_s_zd1,
v_s_zd2,
v_s_zd3,
v_s_zd4,
v_s_zd5,
v_s_zd14
);
end loop;
end if;
end if;
--异常捕捉
Exception
When others then
as_s_errorcode := sqlcode;
as_s_errortext := sqlerrm;
RollBack;
end;
end p_kfgl_bbtj;
分享到：
评论
2 楼
wahaha603
2012-07-12
datawarehouse 写道
缺了好几个建表语句。
写给自己看的 就看个结构 呵呵
1 楼
datawarehouse
2012-07-09
缺了好几个建表语句。
发表评论
-
java 对存储过程的调用
2012-07-09 16:29 983/** * 统计计算 */ ... -
oracle中UTL_RAW.CAST_TO_RAW函数
2012-04-16 15:04 8156项目中遇到要给blod字段初始化问题，是从另外一张表中组合数据 ... -
oracle 临时表 机制
2012-03-06 20:24 1066基本概念，原理 临时表的特点，什么时候用 前段 ... -
oracle 常用日期函数
2012-03-04 22:14 1112D 一周中的星期几 ... -
财务月份维护（oracle，dorado，js,html）
2012-02-20 11:51 1073一、oracle中存储过程编写 1. 参数类型的合 ...
相关推荐
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据处理能力与PL的程序设计特性，是Oracle数据库系统中用于创建存储过程、函数、触发器和包的主要工具。在这个"Oracle PL/SQL实战（待续）"的主题中，我们将深入...
Oracle PL/SQL是一种强大的编程语言，它结合了SQL（结构化查询语言）的数据库操作功能与PL/SQL的程序设计特性，广泛应用于Oracle数据库的开发和管理。这本书"Oracle PL/SQL从入门到精通"的配套源代码，显然是为了...
ORACLE PL/SQL是从入门到精通的专业知识，涵盖了数据库开发与管理的多个方面，包括触发器、过程、函数、软件包、异常处理、游标、循环、分支、变量使用、数据库安装等关键知识点。 触发器是数据库中用来保证数据...
除了基本的数据库对象创建，PL/SQL还允许我们编写存储过程、函数、触发器等，以实现更复杂的业务逻辑。例如，我们可以创建一个存储过程来处理学生选课： ```sql CREATE OR REPLACE PROCEDURE enroll_student( p_...
- **PL/SQL简介**：PL/SQL（Procedural Language for SQL）是Oracle数据库的一种内嵌式过程化语言，用于增强SQL的功能。它允许在SQL查询的基础上添加控制流语句、变量定义、错误处理等特性。 - **环境搭建与配置**：...
PL/SQL是Oracle数据库中用于创建存储过程、函数、触发器、包等数据库对象的主要工具。第4版特别关注了在Oracle 11g环境下的新特性，如性能优化、错误处理和并发控制等。 1. **基础语法**：PL/SQL的基础包括声明变量...
Oracle数据库是全球广泛使用的大型关系型数据库管理系统，其在企业级数据存储和管理中占据着重要地位。PL/SQL（Procedural ...通过深入学习和实践，你将能够编写出高效、可靠的PL/SQL代码，有效管理Oracle数据库。
Oracle 10g PL/SQL 是Oracle数据库系统中用于创建和管理存储过程、函数、触发器等数据库对象的编程语言。本教程旨在为初学者提供一个全面的学习平台，同时也为经验丰富的开发者提供参考资料。PL/SQL是Oracle特有的...
PL/SQL是Oracle数据库特有的编程语言，它结合了SQL的查询能力与过程式编程语言的功能，使得数据库管理、数据处理和业务逻辑实现更为高效。本书针对那些希望提升PL/SQL编程技能，设计和开发复杂数据库解决方案的...
Oracle PL/SQL是一种强大的编程语言，它将SQL与过程编程语言的特性相结合，为数据库开发提供了丰富的功能。在Oracle数据库环境中，PL/SQL是开发高效、可靠和可维护的数据库应用程序的关键工具。以下是对"Oracle PL/...
### Oracle 9i PL/SQL程序设计笔记精要 #### PL/SQL基础知识概览 **标题与描述**：本文档围绕“Oracle 9i PL/SQL程序设计笔记”这一核心主题，深入探讨了PL/SQL语言的基础知识及其在Oracle 9i数据库环境中的应用。...
### Oracle PL/SQL Programming知识点概览 #### 一、书籍基本信息 - **书名**：Oracle PL/SQL Programming（第五版） - **作者**：Steven Feuerstein 和 Bill Pribyl - **出版日期**：2009年10月1日 - **出版社**：...
《Oracle PL/SQL by Example(4th Edition)》是一本专为Oracle数据库用户设计的PL/SQL编程指南，尤其适合那些希望通过实践学习这一强大的过程式语言的开发者。本书的第四版详细介绍了Oracle PL/SQL的各种核心概念和...
Oracle PL/SQL是一种强大的编程语言，它结合了SQL（结构化查询语言）的数据库操作能力和PL（过程化语言）的程序设计特性，是Oracle数据库系统中的核心组件之一。《精通Oracle PL/SQL》这本书深入探讨了这个语言的...
总之，Oracle PL/SQL语言初级教程将引导初学者逐步掌握PL/SQL的基础知识，包括语法、数据类型、函数、表和视图的管理、完整性约束、过程和函数、操作控制以及异常处理。通过学习，开发者可以编写出高效、稳定的...
1. **PL/SQL简介**：PL/SQL是Oracle数据库的内置编程语言，用于创建存储过程、函数、触发器和包。它的语法基于SQL，但增加了流程控制、异常处理和数据类型等特性。 2. **变量和数据类型**：PL/SQL支持多种数据类型...
Oracle PL/SQL是一种强大的编程语言，它结合了SQL的数据库操作能力和Procedural Language的控制结构，使得数据库管理员和开发人员能够创建复杂的数据处理逻辑和应用程序。本篇将深入探讨Oracle PL/SQL的最佳实践，...
首先，PL/SQL是Oracle数据库支持的一种过程化编程语言，结合了SQL查询和控制结构，用于创建复杂的数据处理逻辑。它包括声明变量、条件判断、循环控制、异常处理等多个组成部分，使得开发者能够编写出高效且健壮的...
PL/SQL是Oracle数据库特有的结构化查询语言扩展，它结合了SQL的强大功能与过程性编程语言的特点，使得开发者能够编写复杂的业务逻辑和数据库操作。 在本教程中，学生将学习以下核心知识点： 1. **PL/SQL概述**：...