- 浏览: 77350 次
- 来自: 上海
文章分类
最新评论
-
wahaha603:
liguanghua 写道按照您的方法写了,好像点击导出按钮没 ...
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 941/** * 统计计算 */ ... -
oracle中UTL_RAW.CAST_TO_RAW函数
2012-04-16 15:04 7965项目中遇到要给blod字段初始化问题,是从另外一张表中组合数据 ... -
oracle 临时表 机制
2012-03-06 20:24 1015基本概念,原理 临时表的特点,什么时候用 前段 ... -
oracle 常用日期函数
2012-03-04 22:14 1068D 一周中的星期几 ... -
财务月份维护(oracle,dorado,js,html)
2012-02-20 11:51 1039一、oracle中存储过程编写 1. 参数类型的合 ...
相关推荐
pl/sql编写的院校招生系统(通过包、存储过程、触发器、视图等完成的),可供学习oracle数据库pl/sql编程使用
PL/SQL还可以用来编写过程、函数、包及数据库触发器。过程和函数也称为子程序,在定义时要给出相应的过程名和函数名。它们可以存储在数据库中成为存储过程和存储函数,并可以由程序来调用,它们在结构上同程序模块...
PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL...
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL ...
PL/SQL Developer(pl/sql)是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。 <br>此版本包含注册文件和简体中文语言安装包 <br>Enhancements in PL/SQL Developer 7.1.5 ====================...
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL ...
PL/SQL代码可以使用任何ASCII文本编辑器编写,所以对任何ORACLE能够运行的操作系统都是非常便利的 . 对于SQL,ORACLE必须在同一时间处理每一条SQL语句,在网络环境下这就意味作每一个独立的调用都必须被oracle...
PL/SQL Developer(pl/sql 7.1.5)是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。 <br>此版本包含注册文件和简体中文语言安装包 <br>Enhancements in PL/SQL Developer 7.1.5 ==============...
Oracle存储过程LP/SQL练习题(含答案) 几个练习题
PL/SQL,Oracle对SQL标准的扩充,增加了面向过程的功能,所以可以用来编写存储过程、存储函数、触发器等等。 PL/SQL是结构化SQL,就是在标准SQL中加入了IF...ELSE... For....等
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL ...
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL ...
PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL...
PL/SQL Developer是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。如今,有越来越多的商业逻辑和应用逻辑转向了Oracle Server,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。PL/SQL ...
PL/SQL Developer 是一个为 Oracle 数据库开发存储程序单元的集成开发环境(IDE),使用 PL/SQL Developer,你能方便地创建你的客户端/服务器应用程序的服务器部分。 到目前为止,从最差处想,你还可以完成如下工作: ...
PL/SQL Developer(pl/sql 7.1.5)是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。 <br>此版本包含注册文件和简体中文语言安装包 <br>Enhancements in PL/SQL Developer 7.1.5 ==============...
PL/SQL Developer 是一个为 Oracle 数据库开发存储程序单元的集成开发环(IDE),使用PL/SQL Developer,你能方便地创建你的客户端/服务器应用程序的服务器部分。 到目前为止,从最差处想,你还可以完成如下工作: • ...
1.练习利用SQL*Plus编写、执行PL/SQL程序的命令。 2.记录执行命令和操作过程中遇到的问题及解决方法,注意从原理上解释原因。 3.练习利用PLSQL Developer编写和管理存储过程、存储函数和触发器等。