源码如下:
string connectionstring = "data source=whyd;user id=whyd;password=whyd123"; OracleConnection con = new OracleConnection(connectionstring); OracleCommand command = new OracleCommand();
DataSet ds = new DataSet();
try
{ command.Connection = con;
command.CommandText = "PG_RPT_LLSJFJK.p_rpt_llsjfjk"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("S_RQ1", OracleType.VarChar, 200).Value = "20110801";
command.Parameters.Add("S_RQ2", OracleType.VarChar, 200).Value = "20110803";
command.Parameters.Add("S_JRJC", OracleType.VarChar, 200).Value = "0";
command.Parameters.Add("S_WLBZ", OracleType.VarChar, 200).Value = "2";
command.Parameters.Add("I_CXBZ", OracleType.Int32).Value = 1; command.Parameters.Add("P_CURSOR", OracleType.Cursor);
command.Parameters["P_CURSOR"].Direction = ParameterDirection.Output; OracleDataAdapter adapter = new OracleDataAdapter(command);
TextBox4.Text = "OK";
con.Open(); adapter.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
GridView2.DataSource = ds;
GridView2.DataBind(); }
else
{
Response.Write("<script>alert('NO log!');</script>");
} con.Close();
}
catch (System.Exception ex)
{
throw ex;
Response.Write(ex.Message.ToString());
}
finally
{
con.Close();
command.Dispose();
//adapter.Dispose();
}
string connectionstring = "data source=whyd;user id=whyd;password=whyd123"; OracleConnection con = new OracleConnection(connectionstring); OracleCommand command = new OracleCommand();
DataSet ds = new DataSet();
try
{ command.Connection = con;
command.CommandText = "PG_RPT_LLSJFJK.p_rpt_llsjfjk"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("S_RQ1", OracleType.VarChar, 200).Value = "20110801";
command.Parameters.Add("S_RQ2", OracleType.VarChar, 200).Value = "20110803";
command.Parameters.Add("S_JRJC", OracleType.VarChar, 200).Value = "0";
command.Parameters.Add("S_WLBZ", OracleType.VarChar, 200).Value = "2";
command.Parameters.Add("I_CXBZ", OracleType.Int32).Value = 1; command.Parameters.Add("P_CURSOR", OracleType.Cursor);
command.Parameters["P_CURSOR"].Direction = ParameterDirection.Output; OracleDataAdapter adapter = new OracleDataAdapter(command);
TextBox4.Text = "OK";
con.Open(); adapter.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
GridView2.DataSource = ds;
GridView2.DataBind(); }
else
{
Response.Write("<script>alert('NO log!');</script>");
} con.Close();
}
catch (System.Exception ex)
{
throw ex;
Response.Write(ex.Message.ToString());
}
finally
{
con.Close();
command.Dispose();
//adapter.Dispose();
}
解决方案 »
- asp.net 的购物车,订单怎么实现麻烦高手给下
- 求“服务器无法在发送 HTTP 标头之后设置内容类型”解决方法
- 微软企业库连接 sqlite 总是提示出错:
- 有没有.net平台下实现易度文档管理系统中的外部编辑器功能
- 关于ActiveX控件
- DropDownList如何让其中的一个选项变灰不可用
- 有关ASP.NET的一些小问题
- 为什么textbox的text属性不能得到数据??
- 在gridview中,有两个模板列,都设置了edit模板,在点击编辑时,我想在后台根据条件控制某个模板列不进入编辑状态,该如何实现呢
- 关于错误处理的问题,在线等。。。。。
- asp.net dataTable添加列问题
- 大家帮我看看后台生成的img无法显示!
CREATE OR REPLACE PACKAGE BODY "PG_RPT_LLSJFJK" as
--包名:pg_rpt_fjkdj 作用:定义p_rpt_fjkdj存储过程及myrctye为cursor
--参数说明
--p_cursor 为cursor类型并作为输出参数
--s_rq1 类型:varchar2,输入参数,起始日期
--s_rq2 类型:varchar2,输入参数,终止日期
--s_jrjc 类型:varchar2,输入参数,含义:接入'0'/交出'1'/全部'2'
--s_wlbz 类型:varchar2,输入参数,含义:违流标志
--s_wlbz 违流标志 '0'不违流
--‘1’违流
--'2'全部
--i_cxbz 类型:int,输入参数,含义:查询标志
--i_cxbz 查询标志 1 查询吨数
-- 2 查询车数
-- 0 查询吨数与车数
PROCEDURE p_rpt_llsjfjk(p_cursor OUT myrctype, s_rq1 in varchar2, s_rq2 in varchar2, s_jrjc in varchar2, s_wlbz in varchar2, i_cxbz in int)
isbegin
commit; if s_wlbz = '2' then --违流全部
if i_cxbz = 1 then --查询吨数
if s_jrjc = '0' or s_jrjc = '2' then --接入或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz) select in_out_flag, rkdm, fjkdm, sum(zaz) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '0' and (wlbz = '0' or wlbz = '1')
group by rkdm, fjkdm, in_out_flag;
end if;
if s_jrjc = '1' or s_jrjc = '2' then --交出或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz) select in_out_flag, ckdm, fjkdm, sum(zaz) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '1' and (wlbz = '0' or wlbz = '1')
group by ckdm, fjkdm, in_out_flag;
end if;
elsif i_cxbz = 2 then --查询车数
if s_jrjc = '0' or s_jrjc = '2' then --接入或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, lcls) select in_out_flag, rkdm, fjkdm, count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '0' and (wlbz = '0' or wlbz = '1')
group by rkdm, fjkdm, in_out_flag;
end if;
if s_jrjc = '1' or s_jrjc = '2' then --交出或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, lcls) select in_out_flag, ckdm, fjkdm, count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '1' and (wlbz = '0' or wlbz = '1')
group by ckdm, fjkdm, in_out_flag;
end if;
else --查询吨数与车数
if s_jrjc = '0' or s_jrjc = '2' then --接入或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz, lcls) select in_out_flag, rkdm, fjkdm, sum(zaz), count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '0' and (wlbz = '0' or wlbz = '1')
group by rkdm, fjkdm, in_out_flag;
end if;
if s_jrjc = '1' or s_jrjc = '2' then --交出或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz, lcls) select in_out_flag, ckdm, fjkdm, sum(zaz), count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '1' and (wlbz = '0' or wlbz = '1')
group by ckdm, fjkdm, in_out_flag;
end if;
end if;
else --违流或不违流
if i_cxbz = 1 then --查询吨数
if s_jrjc = '0' or s_jrjc = '2' then --接入或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz) select in_out_flag, rkdm, fjkdm, sum(zaz) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '0' and wlbz = s_wlbz
group by rkdm, fjkdm, in_out_flag;
end if;
if s_jrjc = '1' or s_jrjc = '2' then --交出或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz) select in_out_flag, ckdm, fjkdm, sum(zaz) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '1' and wlbz = s_wlbz
group by ckdm, fjkdm, in_out_flag;
end if;
elsif i_cxbz = 2 then --查询车数
if s_jrjc = '0' or s_jrjc = '2' then --接入或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, lcls) select in_out_flag, rkdm, fjkdm, count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '0' and wlbz = s_wlbz
group by rkdm, fjkdm, in_out_flag;
end if;
if s_jrjc = '1' or s_jrjc = '2' then --交出或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, lcls) select in_out_flag, ckdm, fjkdm, count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '1' and wlbz = s_wlbz
group by ckdm, fjkdm, in_out_flag;
end if;
else --查询吨数与车数
if s_jrjc = '0' or s_jrjc = '2' then --接入或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz, lcls) select in_out_flag, rkdm, fjkdm, sum(zaz), count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '0' and wlbz = s_wlbz
group by rkdm, fjkdm, in_out_flag;
end if;
if s_jrjc = '1' or s_jrjc = '2' then --交出或全部
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkdm, sjfjkdm, zaz, lcls) select in_out_flag, ckdm, fjkdm, sum(zaz), count(*) from t_dzqbsj
where rq >= s_rq1 and rq <= s_rq2 and in_out_flag = '1' and wlbz = s_wlbz
group by ckdm, fjkdm, in_out_flag;
end if;
end if;
end if; --更新分界口名称
update T_TMP_RPT_LLSJFJK a set a.llfjkmc = (select min(b.zm) from t_fjk b where a.llfjkdm = b.zmdm),
a.sjfjkmc = (select min(c.zm) from t_fjk c where a.sjfjkdm = c.zmdm); --发局合计
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkmc, sjfjkmc, zaz, lcls) select jrjc, llfjkmc, '合计', sum(zaz), sum(lcls) from T_TMP_RPT_LLSJFJK group by llfjkmc, jrjc;
--到局合计
insert into T_TMP_RPT_LLSJFJK(jrjc, llfjkmc, sjfjkmc, zaz, lcls) select jrjc, '合计', sjfjkmc, sum(zaz), sum(lcls) from T_TMP_RPT_LLSJFJK group by sjfjkmc, jrjc;
--总合计
-- insert into t_tmp_rpt_fdju(fjmc, djmc, zaz, lcls) select '合计', '合计', sum(zaz), sum(lcls) from t_tmp_rpt_fdju where fjmc = '合计' and djmc <> '合计' ;open p_cursor for select jrjc, llfjkmc, sjfjkmc, sum(zaz), sum(lcls) from T_TMP_RPT_LLSJFJK group by jrjc, sjfjkmc, llfjkmc order by jrjc, sjfjkmc, llfjkmc;
end p_rpt_llsjfjk;--存储过程设定结束
end;