首页 新闻 搜索 专区 学院

过程怎样传入返回一个结果集呢?一定要用游标吗?

0
悬赏园豆:50 [已解决问题] 解决于 2013-01-04 10:35

前几天做项目的时候遇到这样一个问题 ?写 一个存储过程 返回一个结果集,以前在  SqlServer上面 很很简单的 ,但是  Oracle 上面还没有试过,希望大家帮忙解决一下 ,谢谢 ,具体示例如下 :

CREATE OR REPLACE FUNCTION executeUnionCount (startime DATE, endtime DATE)
return TABLE
as
f_result table;
BEGIN
f_result:=SELECT * FROM(SELECT SUM(xinding) AS
xinding, SUM(xuding) AS
xuding, SUM(tuiding) AS
tuiding, SUM(dianbo) AS
dianbo, SUM(tixing) AS
tixing, SUM(xindingprice) AS
xindingprice, SUM(xudingprice) AS
xudingprice, SUM(tuidingprice) AS
tuidingprice, SUM(dianboprice) AS
dianboprice, SUM(tixingprice) AS
tixingprice, SUM(price) AS
price, SUM(total) AS
total, 1 AS
id FROM(SELECT to_date(a.year || '-' || a.month || '-' || a.day, 'yyyy-mm-dd') AS
createtime, SUM(nvl(decode(a.jllx, 1, a.total), 0)) AS
xinding, SUM(nvl(decode(a.jllx, 2, a.total), 0)) AS
xuding, SUM(nvl(decode(jllx, 3, a.total), 0)) AS
tuiding, SUM(nvl(decode(a.jllx, 4, a.total), 0)) AS
dianbo, SUM(nvl(decode(a.jllx, 5, a.total), 0)) AS
tixing, SUM(nvl(decode(a.jllx, 1, a.price), 0)) AS
xindingprice, SUM(nvl(decode(a.jllx, 2, a.price), 0)) AS
xudingprice, SUM(nvl(decode(jllx, 3, a.price), 0)) AS
tuidingprice, SUM(nvl(decode(a.jllx, 4, a.price), 0)) AS
dianboprice, SUM(nvl(decode(a.jllx, 5, a.price), 0)) AS
tixingprice, SUM(a.price) AS
price, SUM(a.total) AS
total FROM v$historyrecordforall a WHERE to_date(a.year || '-' || a.month || '-' || a.day, 'yyyy-mm-dd') BETWEEN to_date('2012-5-4', 'yyyy-mm-dd') AND to_date('2012-09-04', 'yyyy-mm-dd') GROUP BY to_date(a.year || '-' || a.month || '-' || a.day, 'yyyy-mm-dd') ORDER BY to_date(a.year || '-' || a.month || '-' || a.day, 'yyyy-mm-dd') DESC) v) v,

(SELECT 1 AS
id, SUM(total) AS
recordtotal, SUM(dxjg) AS
dxjg, SUM(accept) AS
accept, SUM(send) AS
send FROM sea_msg_v$daycount d WHERE to_date(d.year || '-' || d.month || '-' || d.day, 'yyyy-mm-dd') BETWEEN to_date('2012-01-01', 'yyyy-mm-dd') AND to_date('2012-12-01', 'yyyy-mm-dd')) r WHERE v.id = r.id;
return f_result;
END;

 

 

 

CREATE OR REPLACE PROCEDURE getcounterresult(starttime IN DATE,
                                             endtime   IN DATE,
                                             res       OUT SYS_REFCURSOR) AS
BEGIN
  OPEN res FOR
    SELECT *
      FROM (SELECT SUM(xinding) AS xinding,
                   SUM(xuding) AS xuding,
                   SUM(tuiding) AS tuiding,
                   SUM(dianbo) AS dianbo,
                   SUM(tixing) AS tixing,
                   SUM(xindingprice) AS xindingprice,
                   SUM(xudingprice) AS xudingprice,
                   SUM(tuidingprice) AS tuidingprice,
                   SUM(dianboprice) AS dianboprice,
                   SUM(tixingprice) AS tixingprice,
                   SUM(price) AS price,
                   SUM(total) AS total,
                   1 AS id
              FROM (SELECT to_date(a.year || '-' || a.month || '-' || a.day,
                                   'yyyy-mm-dd') AS createtime,
                           SUM(nvl(decode(a.jllx, 1, a.total), 0)) AS xinding,
                           SUM(nvl(decode(a.jllx, 2, a.total), 0)) AS xuding,
                           SUM(nvl(decode(jllx, 3, a.total), 0)) AS tuiding,
                           SUM(nvl(decode(a.jllx, 4, a.total), 0)) AS dianbo,
                           SUM(nvl(decode(a.jllx, 5, a.total), 0)) AS tixing,
                           SUM(nvl(decode(a.jllx, 1, a.price), 0)) AS xindingprice,
                           SUM(nvl(decode(a.jllx, 2, a.price), 0)) AS xudingprice,
                           SUM(nvl(decode(jllx, 3, a.price), 0)) AS tuidingprice,
                           SUM(nvl(decode(a.jllx, 4, a.price), 0)) AS dianboprice,
                           SUM(nvl(decode(a.jllx, 5, a.price), 0)) AS tixingprice,
                           SUM(a.price) AS price,
                           SUM(a.total) AS total
                      FROM v$historyrecordforall a
                     WHERE to_date(a.year || '-' || a.month || '-' || a.day,
                                   'yyyy-mm-dd') BETWEEN
                           starttime AND
                           endtime
                     GROUP BY to_date(a.year || '-' || a.month || '-' ||
                                      a.day,
                                      'yyyy-mm-dd')
                     ORDER BY to_date(a.year || '-' || a.month || '-' ||
                                      a.day,
                                      'yyyy-mm-dd') DESC) v) v,
           
           (SELECT 1 AS id,
                   SUM(total) AS recordtotal,
                   SUM(dxjg) AS dxjg,
                   SUM(accept) AS accept,
                   SUM(send) AS send
              FROM sea_msg_v$daycount d
             WHERE to_date(d.year || '-' || d.month || '-' || d.day,
                           'yyyy-mm-dd') BETWEEN
                   starttime AND
                  endtime) r
     WHERE v.id = r.id;
END;



DECLARE
  paramstartime DATE := to_date('2012-01-01', 'yyyy-mm-dd');
  paramendtime  DATE := to_date('2012-12-30', 'yyyy-mm-dd');
  cur_res       SYS_REFCURSOR;
BEGIN
  getcounterresult(starttime => paramstartime,
                   endtime   => paramendtime,
                   res       => cur_res);
  FOR cur_next IN cur_res LOOP
    dbms_output.put_line(cur_next.xinding);
  END LOOP;
END;

为什么这样写都会报错呢? Cur_res 不是过程或尚未定义 ORa-06550 

CDFanFan的主页 CDFanFan | 初学一级 | 园豆:3
提问于:2012-12-10 09:16
< >
分享
最佳答案
0

得用游标吧。给你举一个很简单的例子:

create or replace procedure P_GetList(
                                            out_cursor out Sys_refcursor) is
begin
  open out_cursor for
    select * from tablename;
end P_GetDetailList;
收获园豆:30
jone_e | 小虾三级 |园豆:1410 | 2012-12-10 09:33

如果是函数的话,也必须使用游标吗?我总觉得.net 中对 游标进行处理是一件比较麻烦的事情?所以 如果可以尽量避免游标 就最好避免哈

CDFanFan | 园豆:3 (初学一级) | 2012-12-10 09:36

@CDFanFan: 不麻烦啊,OracleClient可以很好的支持游标啊。跟P_SQL一样啊。你这么调用就OK了。

            string StrProceName = "P_GetDetailListByKeyWords";
            OracleParameter[] OParam ={
                                         new OracleParameter("in_Btime",OracleType.NVarChar,50),
                                         new OracleParameter("in_Etime",OracleType.NVarChar,50),
                                         new OracleParameter("out_cursor",OracleType.Cursor)
                                     };
            OParam[0].Direction = ParameterDirection.Input;
            OParam[0].Value = StrBtime;
            OParam[1].Direction = ParameterDirection.Input;
            OParam[1].Value = StrEtime;
            OParam[3].Direction = ParameterDirection.Output;
            DataTable Dt = DbHelper.ExecuteProcDataTable(StrProceName, OParam);

然后再找一个可以执行存储过程的OracleDBHerlper。

这里有一个执行Oracle存储过程的方法你可以试试:

        public static DataTable ExecuteProcDataTable(string storedProcName, OracleParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataTable dt = new DataTable();
                try
                {
                    connection.Open();
                    OracleDataAdapter sqlDA = new OracleDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
                //connection.Close();
                return dt;
            }
        }

        private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, OracleParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }
jone_e | 园豆:1410 (小虾三级) | 2012-12-10 10:04
CREATE OR REPLACE PROCEDURE getcounterresult(starttime IN DATE,
                                             endtime   IN DATE,
                                             res       OUT SYS_REFCURSOR) AS
BEGIN
  OPEN res FOR
    SELECT *
      FROM (SELECT SUM(xinding) AS xinding,
                   SUM(xuding) AS xuding,
                   SUM(tuiding) AS tuiding,
                   SUM(dianbo) AS dianbo,
                   SUM(tixing) AS tixing,
                   SUM(xindingprice) AS xindingprice,
                   SUM(xudingprice) AS xudingprice,
                   SUM(tuidingprice) AS tuidingprice,
                   SUM(dianboprice) AS dianboprice,
                   SUM(tixingprice) AS tixingprice,
                   SUM(price) AS price,
                   SUM(total) AS total,
                   1 AS id
              FROM (SELECT to_date(a.year || '-' || a.month || '-' || a.day,
                                   'yyyy-mm-dd') AS createtime,
                           SUM(nvl(decode(a.jllx, 1, a.total), 0)) AS xinding,
                           SUM(nvl(decode(a.jllx, 2, a.total), 0)) AS xuding,
                           SUM(nvl(decode(jllx, 3, a.total), 0)) AS tuiding,
                           SUM(nvl(decode(a.jllx, 4, a.total), 0)) AS dianbo,
                           SUM(nvl(decode(a.jllx, 5, a.total), 0)) AS tixing,
                           SUM(nvl(decode(a.jllx, 1, a.price), 0)) AS xindingprice,
                           SUM(nvl(decode(a.jllx, 2, a.price), 0)) AS xudingprice,
                           SUM(nvl(decode(jllx, 3, a.price), 0)) AS tuidingprice,
                           SUM(nvl(decode(a.jllx, 4, a.price), 0)) AS dianboprice,
                           SUM(nvl(decode(a.jllx, 5, a.price), 0)) AS tixingprice,
                           SUM(a.price) AS price,
                           SUM(a.total) AS total
                      FROM v$historyrecordforall a
                     WHERE to_date(a.year || '-' || a.month || '-' || a.day,
                                   'yyyy-mm-dd') BETWEEN
                           starttime AND
                           endtime
                     GROUP BY to_date(a.year || '-' || a.month || '-' ||
                                      a.day,
                                      'yyyy-mm-dd')
                     ORDER BY to_date(a.year || '-' || a.month || '-' ||
                                      a.day,
                                      'yyyy-mm-dd') DESC) v) v,
           
           (SELECT 1 AS id,
                   SUM(total) AS recordtotal,
                   SUM(dxjg) AS dxjg,
                   SUM(accept) AS accept,
                   SUM(send) AS send
              FROM sea_msg_v$daycount d
             WHERE to_date(d.year || '-' || d.month || '-' || d.day,
                           'yyyy-mm-dd') BETWEEN
                   starttime AND
                  endtime) r
     WHERE v.id = r.id;
END;

DECLARE
  paramstartime DATE := to_date('2012-01-01', 'yyyy-mm-dd');
  paramendtime  DATE := to_date('2012-12-30', 'yyyy-mm-dd');
  cur_res       SYS_REFCURSOR;
BEGIN
  getcounterresult(starttime => paramstartime,
                   endtime   => paramendtime,
                   res       => cur_res);
  FOR cur_next IN cur_res LOOP
    dbms_output.put_line(cur_next.xinding);
  END LOOP;
END;

我也是你这样写的,但是为什么还是报错呢 ?

报错 Cur_res 不是过程或尚未定义 ORa-06550 

CDFanFan | 园豆:3 (初学一级) | 2012-12-10 13:11

@CDFanFan: 你是在pl-SQL环境里调用Procedure?你试试下面的调用方式(接着我上面给你的简单的Procedure):

declare
  out_cursor Sys_refcursor;
  emp_row tablename%rowtype;

begin
  p_test(Out_Cursor => out_cursor);
  loop
    fetch out_cursor
      into emp_row;
    if out_cursor%found then
      dbms_output.put_line(emp_row.fieldname);-- 这里不能写 *
    else
      exit;
    end if;
  end loop;
  close out_cursor;
end;

注意:在PL-SQL环境中缓冲区只有10000byte,可能输出不全。

祝你好运!

jone_e | 园豆:1410 (小虾三级) | 2012-12-10 17:25
其他回答(2)
0

你把写好的存储过程执行没有用吗?

收获园豆:10
chenping2008 | 园豆:9836 (大侠五级) | 2012-12-10 09:18

没有用,好像  table 不是一个数据类型的 

支持(0) 反对(0) CDFanFan | 园豆:3 (初学一级) | 2012-12-10 09:19
0

Oracle 存储过程返回数据集要使用游标的。

收获园豆:10
geass.. | 园豆:1759 (小虾三级) | 2012-12-10 09:26

那 没有其他的解决方法吗  

支持(0) 反对(0) CDFanFan | 园豆:3 (初学一级) | 2012-12-10 09:31
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册