请教oracle 月查询存储过程

zjmfeeling183 2010-12-06 03:32:08
我刚接触oracle存储过程，所以请教下大家，现在需求如下：主要字段equipName（设备种类，String类型），
checkindate(入所时间，date类型)
比如有数据如下：查询的数量是这个设备种类的记录数
id equipName cheindate
t001 交流电动转辙机 2010-01-10
t001 交流电动转辙机 2010-02-11
t002 直流电动转辙机 2010-01-12
t002 直流电动转辙机 2010-02-13
t002 直流电动转辙机 2010-01-11
t002 直流电动转辙机 2010-03-11

要显示的结果为：
设备种类 1月 2月 3月 一季度 4月 5月 6月 2季度 7月 8月 9月 3季度 10月 11月 12月 4季度 总计
交流电动转辙机 1 1 0 2 。 。 。
直流电动转辙机 2 3 1 6 。 。 。
总计 3 4 1 8 。 。 。

要达到这样的效果，请大侠们帮我写写这个oracle存储过程 。
拜托高手们了，我是新手，所以分不多，拜托了
...全文
264 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
gelyon 2010-12-07
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 zjmfeeling183 的回复:]
allableStatement cstmt= conn2.prepareCall("{call p_get_info(?,?)}");
cstmt.setString(1, "years");//年份条件 cstmt.registerOutParameter(2, OracleTypes.CURSOR);//返回游标
cstmt.execute();
rs=(ResultSet)cstmt……
[/Quote]
你不是已经封装到对象里去了吗？
Obj1是第一个字段，Obj2是第二个字段，Obj3是第三个字段。。。。。。
zjmfeeling183 2010-12-07
  • 打赏
  • 举报
 回复
allableStatement cstmt= conn2.prepareCall("{call p_get_info(?,?)}");
cstmt.setString(1, "years");//年份条件 cstmt.registerOutParameter(2, OracleTypes.CURSOR);//返回游标
cstmt.execute();
rs=(ResultSet)cstmt.getObject(2);
while (rs.next())
{
object =new Object();
object.setObj1(rs.getInt(1)) ;
object.setObj2(rs.getInt(2)) ；
。。。。。 list.add(object );
}
}
retrun list；

我是通过这种方式获得的，也不知道能行不
还有就是表字段名 那个“一月” “二月” “总计”这一行怎么获取结果
我要把它输入到页面上，作为表单标题栏 那一行
gelyon 2010-12-07
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 zjmfeeling183 的回复:]
对于这个结果集是用游标指引的吧，后台获取这个游标后，再进行遍历存储，那表字段，也就是起的表名如“一月”，二月 如何获取呢
[/Quote]
这个获取结果集，进行遍历就可以了啊，至于哪个字段，你可以通过索引号来取的，如rs.getInt(1<2,3,4,.....>)
zjmfeeling183 2010-12-07
  • 打赏
  • 举报
 回复
对于这个结果集是用游标指引的吧，后台获取这个游标后，再进行遍历存储，那表字段，也就是起的表名如“一月”，二月 如何获取呢
gelyon 2010-12-07
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 zjmfeeling183 的回复:]
呵呵 太感谢了 ， 我看的都是这样的，用v_sql:='' 来把查询的语句拼接，然后用游标指向，传到要调用的地方进行解析，所以不知道“” 该怎么拼接， 要是我加条件呢 ， 比如，传入时间，设备状态进行查询，该怎么写存储过程呢，再次麻烦了
[/Quote]
哦，这样啊？那就要用动态SQL拼接了，我上面的过程是固定的，因为对于你那个需求，查询的SQL语句是固定的，没有传入参数，因为不需要用v_sql:='' 来把查询的语句拼接
至于你后面说的，你要传入时间，设备状态进行查询，那肯定要动态拼接查询语句了，拼接稍微麻烦点，你可以再发个帖子，把你需求说明白点
zjmfeeling183 2010-12-07
  • 打赏
  • 举报
 回复
呵呵 太感谢了 ， 我看的都是这样的，用v_sql:='' 来把查询的语句拼接，然后用游标指向，传到要调用的地方进行解析，所以不知道“” 该怎么拼接， 要是我加条件呢 ， 比如，传入时间，设备状态进行查询，该怎么写存储过程呢，再次麻烦了
gelyon 2010-12-07
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 zjmfeeling183 的回复:]
写了半天，存储过程写不对，那位仁兄帮我把gelyon写的那个改成存储过程啊
[/Quote]


         

Connected to:

Oracle Database 10g Release 10.1.0.2.0 - Production



SQL> set serveroutput ON

SQL> --创建过程：

SQL> CREATE OR REPLACE PROCEDURE p_get_info(resuleSet OUT sys_refcursor)

  2  IS 

  3  BEGIN 

  4       OPEN resuleSet FOR 

  5            SELECT

  6            Decode(Grouping(equipName),1,'总计',equipName) "设备种类",

  7            Sum(Decode(To_Char(cheindate,'mm'),'01',1,0)) "1月",

  8            Sum(Decode(To_Char(cheindate,'mm'),'02',1,0))  "2月",

  9            Sum(Decode(To_Char(cheindate,'mm'),'03',1,0))  "3月",

 10            Sum(Decode(to_char(cheindate,'q'),1,1,0)) "一季度",

 11            Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) "4月",

 12            Sum(Decode(To_Char(cheindate,'mm'),'05',1,0)) "5月",

 13            Sum(Decode(To_Char(cheindate,'mm'),'06',1,0)) "6月",

 14            Sum(Decode(to_char(cheindate,'q'),2,1,0)) "二季度",

 15            Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) "7月",

 16            Sum(Decode(To_Char(cheindate,'mm'),'08',1,0)) "8月",

 17            Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) "9月",

 18            Sum(Decode(to_char(cheindate,'q'),3,1,0)) "三季度",

 19            Sum(Decode(To_Char(cheindate,'mm'),'010',1,0)) "10月",

 20            Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) "11月",

 21            Sum(Decode(To_Char(cheindate,'mm'),'12',1,0)) "12月",

 22            Sum(Decode(to_char(cheindate,'q'),4,1,0)) "四季度",

 23            Count(1) "总计"

 24            FROM tab1 GROUP BY rollup(equipName);

 25  END;

 26  /



Procedure created.



SQL> --测试过程

SQL> var cur refcursor

SQL> exec p_get_info(:cur);



PL/SQL procedure successfully completed.



SQL> --结果：

SQL> print cur    



--结果：

设备种类        1月 2月 3月 一季度 4月 5月 6月 二季度 7月 8月 9月 三季度 10月 11月 12月 四季度 总计

---------------------------------------------------------------------------------------------------------

交流电动转辙机  1    1   0   2      0   1   0    1     0   0   1    1     0    0    1    1      5

直流电动转辙机  2    1   1   4      0   1   2    3     0   1   0    1     0    0    0    0      8

总计            3    2   1   6      0   2   2    4     0   1   1    2     0    0    1    1      13
zjmfeeling183 2010-12-07
  • 打赏
  • 举报
 回复
明天再重新弄个帖子送分，今天请高手有空先帮我简单看看怎么弄。thanks
zjmfeeling183 2010-12-07
  • 打赏
  • 举报
 回复
哎，超级郁闷。我做的这个只是最简单的一个报表，还得做好几个类型的，
Decode(Grouping(equipName),1,''总计'',equipName) as equipName,
这句话是第一列按照设备名进行统计，要是按照别的列进行统计，那么该如何动态传进这个equipname字段，意思就是这个字段是变化的，比如我按照生产厂家统计一年内的。
这个报表字段有设备种类，生产厂家，入时间，所在仓库，也就是说可能这个字段作为统计项，别的因素是条件，然后可能下次它就变成条件了，怎么动态传参啊

还有最后的条件也是变化的，有没有类似if，esle的，将这些条件拼装，然后再放入where后条件里
有没有一个动态的模板什么的 我学习下 弄过了半天 也只是会看别人的做法，自己不会拼装
zjmfeeling183 2010-12-07
  • 打赏
  • 举报
 回复
太感谢了 ，解决大问题了，再次感谢
gelyon 2010-12-07
  • 打赏
  • 举报
 回复 




Connected to:

Oracle Database 10g Release 10.1.0.2.0 - Production



SQL> set serveroutput on

SQL> alter session set nls_date_format='yyyy-mm-dd' ;



Session altered.



SQL> select * from tab1;



ID       EQUIPNAME                                  CHEINDATE

-------- ------------------------------------------ ----------

t001     交流电动转辙机                             2010-01-10

t001     交流电动转辙机                             2010-02-11

t002     直流电动转辙机                             2010-01-12

t002     直流电动转辙机                             2010-02-13

t002     直流电动转辙机                             2010-01-11

t002     直流电动转辙机                             2010-03-11

t001     交流电动转辙机                             2010-05-11

t002     直流电动转辙机                             2010-06-12

t002     直流电动转辙机                             2010-05-13

t002     直流电动转辙机                             2010-06-11

t002     直流电动转辙机                             2010-08-11



ID       EQUIPNAME                                  CHEINDATE

-------- ------------------------------------------ ----------

t001     交流电动转辙机                             2010-09-11

t001     交流电动转辙机                             2010-12-11



13 rows selected.



SQL> --带参数的过程：

SQL> CREATE OR REPLACE PROCEDURE p_get_info(v_id         IN  VARCHAR2,     --直流交流类型

  2                                         v_cheindate  IN  VARCHAR2,     --时间

  3                                         resuleSet    OUT sys_refcursor --返回结果集 

  4                                         )

  5       IS

  6         sql_str VARCHAR2(4000);

  7       BEGIN

  8            sql_str:=

  9            'SELECT

 10                  Decode(Grouping(equipName),1,''总计'',equipName) as equipName,

 11                  Sum(Decode(To_Char(cheindate,''mm''),''01'',1,0)) mon1,

 12                  Sum(Decode(To_Char(cheindate,''mm''),''02'',1,0)) mon2,

 13                  Sum(Decode(To_Char(cheindate,''mm''),''03'',1,0)) mon3,

 14                  Sum(Decode(to_char(cheindate,''q''),1,1,0)) one_quarter,

 15                  Sum(Decode(To_Char(cheindate,''mm''),''04'',1,0)) mon4,

 16                  Sum(Decode(To_Char(cheindate,''mm''),''05'',1,0)) mon5,

 17                  Sum(Decode(To_Char(cheindate,''mm''),''06'',1,0)) mon6,

 18                  Sum(Decode(to_char(cheindate,''q''),2,1,0)) two_quarter,

 19                  Sum(Decode(To_Char(cheindate,''mm''),''07'',1,0)) mon7,

 20                  Sum(Decode(To_Char(cheindate,''mm''),''08'',1,0)) mon8,

 21                  Sum(Decode(To_Char(cheindate,''mm''),''09'',1,0)) mon9,

 22                  Sum(Decode(to_char(cheindate,''q''),3,1,0)) three_quarter,

 23                  Sum(Decode(To_Char(cheindate,''mm''),''10'',1,0)) mon10,

 24                  Sum(Decode(To_Char(cheindate,''mm''),''11'',1,0)) mon11,

 25                  Sum(Decode(To_Char(cheindate,''mm''),''12'',1,0)) mon12,

 26                  Sum(Decode(to_char(cheindate,''q''),4,1,0)) four_quarter,

 27                  Count(1) cnt

 28            FROM (SELECT * FROM tab1 WHERE id='''||v_id||''' AND cheindate <= to_date('''||v_chei

ndate||''',''yyyy-mm-dd''))

 29            GROUP BY rollup(equipName)';

 30            --Dbms_Output.put_line(sql_str);

 31            OPEN resuleSet FOR sql_str;

 32  

 33       END;

 34  /



Procedure created.



SQL> var cur refcursor

SQL> exec p_get_info('t001','2010-06-10',:cur);



PL/SQL procedure successfully completed.



SQL> print cur



EQUIPNAME  MON1 MON2  MON3  ONE_QUARTER  MON4 MON5 MON6  TWO_QUARTER MON7  MON8  MON9  THREE_QUARTER MON10 MON11  MON12  FOUR_QUARTER  CNT

-----------------------------------------------------------------------

交流电动转辙机 1 1 0 2 0 1 0 1 0 0 0 0 0 0 0 0 3

总计           1 1 0 2 0 1 0 1 0 0 0 0 0 0 0 0 3
zjmfeeling183 2010-12-06
  • 打赏
  • 举报
 回复
写了半天，存储过程写不对，那位仁兄帮我把gelyon写的那个改成存储过程啊
真的不知道取什么名 2010-12-06
  • 打赏
  • 举报
 回复 
--突然想起来，to_char(sysdate,'q'）这个可以返回季度



SQL> select to_char(to_date('20100310','yyyymmdd'),'q') from dual;



T

-

1





select equipName,

       sum(case when to_char(cheindate,'dd')=1 then 1 else 0 end) "1月",

       sum(case when to_char(cheindate,'dd')=2 then 1 else 0 end) "2月",

       sum(case when to_char(cheindate,'dd')=3 then 1 else 0 end) "3月",

       sum(case when to_char(cheindate,'q')=1 then 1 else 0 end) "1季度",

       sum(case when to_char(cheindate,'dd')=4 then 1 else 0 end) "4月",

       sum(case when to_char(cheindate,'dd')=5 then 1 else 0 end) "5月",

       sum(case when to_char(cheindate,'dd')=6 then 1 else 0 end) "6月",

       sum(case when to_char(cheindate,'q')=2 then 1 else 0 end) "2季度",

       sum(case when to_char(cheindate,'dd')=7 then 1 else 0 end) "7月",

       sum(case when to_char(cheindate,'dd')=8 then 1 else 0 end) "8月",

       sum(case when to_char(cheindate,'dd')=9 then 1 else 0 end) "9月",

       sum(case when to_char(cheindate,'q')=3 then 1 else 0 end) "3季度",

       sum(case when to_char(cheindate,'dd')=10 then 1 else 0 end) "10月",

       sum(case when to_char(cheindate,'dd')=11 then 1 else 0 end) "11月",

       sum(case when to_char(cheindate,'dd')=12 then 1 else 0 end) "12月",

       sum(case when to_char(cheindate,'q')=4 then 1 else 0 end) "4季度",

       count(*) "合计"

from tab1

group by equipName
zjmfeeling183 2010-12-06
  • 打赏
  • 举报
 回复
谢谢各位啊 受教了
gelyon 2010-12-06
  • 打赏
  • 举报
 回复 


--估计楼主是写错了

--下面这样是不是你想的？



WITH tab1 AS(

SELECT 't001' id,'交流电动转辙机' equipName,To_Date('2010-01-10','yyyy-mm-dd') cheindate FROM dual UNION ALL 

SELECT 't001', '交流电动转辙机',To_Date('2010-02-11','yyyy-mm-dd') FROM dual UNION ALL 

SELECT 't002', '直流电动转辙机',To_Date('2010-01-12','yyyy-mm-dd') FROM dual UNION ALL 

SELECT 't002', '直流电动转辙机',To_Date('2010-02-13','yyyy-mm-dd') FROM dual UNION ALL

SELECT 't002', '直流电动转辙机',To_Date('2010-01-11','yyyy-mm-dd') FROM dual UNION ALL

SELECT 't002', '直流电动转辙机',To_Date('2010-03-11','yyyy-mm-dd') FROM dual UNION ALL

SELECT 't001', '交流电动转辙机',To_Date('2010-05-11','yyyy-mm-dd') FROM dual UNION ALL 

SELECT 't002', '直流电动转辙机',To_Date('2010-06-12','yyyy-mm-dd') FROM dual UNION ALL 

SELECT 't002', '直流电动转辙机',To_Date('2010-05-13','yyyy-mm-dd') FROM dual UNION ALL

SELECT 't002', '直流电动转辙机',To_Date('2010-06-11','yyyy-mm-dd') FROM dual UNION ALL

SELECT 't002', '直流电动转辙机',To_Date('2010-08-11','yyyy-mm-dd') FROM dual UNION ALL

SELECT 't001', '交流电动转辙机',To_Date('2010-09-11','yyyy-mm-dd') FROM dual UNION ALL 

SELECT 't001', '交流电动转辙机',To_Date('2010-12-11','yyyy-mm-dd') FROM dual 

)

--以上是测试数据

SELECT 

      Decode(Grouping(equipName),1,'总计',equipName) "设备种类",

      Sum(Decode(To_Char(cheindate,'mm'),'01',1,0)) "1月",

      Sum(Decode(To_Char(cheindate,'mm'),'02',1,0))  "2月",

      Sum(Decode(To_Char(cheindate,'mm'),'03',1,0))  "3月",

      Sum(case when to_char(cheindate,'mm')<=3 then 1 else 0 end) "一季度",

      Sum(Decode(To_Char(cheindate,'mm'),'04',1,0)) "4月",

      Sum(Decode(To_Char(cheindate,'mm'),'05',1,0)) "5月",

      Sum(Decode(To_Char(cheindate,'mm'),'06',1,0)) "6月",

      Sum(case when to_char(cheindate,'mm')<=6 and to_char(cheindate,'mm')>3 then 1 else 0 end) "二季度",

      Sum(Decode(To_Char(cheindate,'mm'),'07',1,0)) "7月",

      Sum(Decode(To_Char(cheindate,'mm'),'08',1,0)) "8月",

      Sum(Decode(To_Char(cheindate,'mm'),'09',1,0)) "9月",

      Sum(case when to_char(cheindate,'mm')<=9 and to_char(cheindate,'mm')>6 then 1 else 0 end) "三季度",

      Sum(Decode(To_Char(cheindate,'mm'),'010',1,0)) "10月",

      Sum(Decode(To_Char(cheindate,'mm'),'11',1,0)) "11月",

      Sum(Decode(To_Char(cheindate,'mm'),'12',1,0)) "12月",

      Sum(case when to_char(cheindate,'mm')<=12 and to_char(cheindate,'mm')>9 then 1 else 0 end) "四季度",

      Count(1) "总计"

FROM tab1 GROUP BY rollup(equipName)



--结果：  

设备种类        1月 2月 3月 一季度 4月 5月 6月 二季度 7月 8月 9月 三季度 10月 11月 12月 四季度 总计

---------------------------------------------------------------------------------------------------------

交流电动转辙机  1    1   0   2      0   1   0    1     0   0   1    1     0    0    1    1      5

直流电动转辙机  2    1   1   4      0   1   2    3     0   1   0    1     0    0    0    0      8

总计            3    2   1   6      0   2   2    4     0   1   1    2     0    0    1    1      13
quanhj 2010-12-06
  • 打赏
  • 举报
 回复
gelyon想得很周到。
gelyon 2010-12-06
  • 打赏
  • 举报
 回复
请问下楼主，你的数据：直流电动转辙机 2 3 2月份的数据时3怎么得来的？
是不是因为该直流电动转辙机在1月份有两条数据，2月份也有该直流电动转辙机1条数据，在算2月份数据的时候，要加上1月份的两条？
具体需求说明白下，
如果后面比如6月份有该直流电动转辙机数据，那么又是怎么算的？汇总前面5各月的该直流电动转辙机数据？？？
物润声无 2010-12-06
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 zhuomingwang 的回复:]
SQL code
--这样？
select equipName,
sum(case when to_char(cheindate,'dd')=1 then 1 else 0 end) 1月,
sum(case when to_char(cheindate,'dd')=2 then 1 else 0 end) 2月,
sum(case when t……
[/Quote]
学习，
真的不知道取什么名 2010-12-06
  • 打赏
  • 举报
 回复 
--这样？

select equipName,

       sum(case when to_char(cheindate,'dd')=1 then 1 else 0 end) 1月,

       sum(case when to_char(cheindate,'dd')=2 then 1 else 0 end) 2月,

       sum(case when to_char(cheindate,'dd')=3 then 1 else 0 end) 3月,

       sum(case when to_char(cheindate,'dd')<=3 then 1 else 0 end) 1季度,

       sum(case when to_char(cheindate,'dd')=4 then 1 else 0 end) 4月,

       sum(case when to_char(cheindate,'dd')=5 then 1 else 0 end) 5月,

       sum(case when to_char(cheindate,'dd')=6 then 1 else 0 end) 6月,

       sum(case when 3<to_char(cheindate,'dd')<=6 then 1 else 0 end) 2季度,

       sum(case when to_char(cheindate,'dd')=7 then 1 else 0 end) 7月,

       sum(case when to_char(cheindate,'dd')=8 then 1 else 0 end) 8月,

       sum(case when to_char(cheindate,'dd')=9 then 1 else 0 end) 9月,

       sum(case when 6<to_char(cheindate,'dd')<=9 then 1 else 0 end) 3季度,

       sum(case when to_char(cheindate,'dd')=10 then 1 else 0 end) 10月,

       sum(case when to_char(cheindate,'dd')=11 then 1 else 0 end) 11月,

       sum(case when to_char(cheindate,'dd')=12 then 1 else 0 end) 12月,

       sum(case when 9<to_char(cheindate,'dd')<=12 then 1 else 0 end) 4季度,

       count(*) 合计

from tablename

group by equipName
sqlserver表数据插入到oracle表中的一种实现方式(表结构相同)
表结构的创建比较简单，但是表的数据量太大，一时也想不到怎么把sqlserver表数据复制到oracle中，于是请教公司主管，用存储过程实现可以查询出所有数据的insert脚本，在oracle库中创建好表，直接把sqlserver中的...
asp.net知识库
发布Oracle存储过程包c#代码生成工具(CodeRobot) New Folder XCodeFactory3.0完全攻略－－序 XCodeFactory3.0完全攻略－－基本思想 XCodeFactory3.0完全攻略－－简单示例 XCodeFactory3.0完全攻略－－IDBAccesser ...
java 调用 oracle存储过程_用java调用oracle存储过程总结
以前一直没有动存储过程是用来干嘛的，后来请教朋友才换为自己的理解方式，用自己通俗的语言来说，就是把sql语句换为一个过程，也可以说是一个方法，每次直接给参数调用就好，使用存储过程查询速度快，系统只编译一...
oracle存储过程延迟,请问oracle中select * from 某个存储过程时速度很慢的有关问题...
请教oracle中select * from 某个存储过程时速度很慢的问题。流程是：首先导入一些5000条左右的hphm到cph_tmp表中，然后执行存储过程P_QUERY_CAR，存储过程P_QUERY_CAR会获取cph_tmp表中的数据，然后进行一些过程的...
oracle对存储过程授权,PL/SQL包内的存储过程执行授权
请教一个PL/SQL包内的存储过程执行授权的问题：我发现可以将对包的执行权限赋予用户，但却无法将对包内的存储过程的执行权限赋予用户，请教如何才能赋权呢？[oracle@localhost ~]$ cat test.sqlCREATE OR REPLACE ...
Oracle

17,091

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章