存储过程如下
:
create or replace procedure fy (
tablename in varchar2,
pagesize in number,
pagenow in number,
myrows out number,
pagecount out number,
p_cursor out fenye.test_cursor
)is
v_sql varchar2(1000);
v_bedin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
v_sql := 'select * from (select t1.*,rownum rn from(select * from '||tablename||' )t1 where rownum<= '||v_bedin||' where rn>='||v_end;
open p_cursor for v_sql;
v_sql := 'select cout(*)from '||tablename;
execute immediate v_sql into myrows;
if mod(myrows,pagesize)=0 then pagecount:=myrows/pagesize;
else
pagecount:=myrows/pagesize+1;
end if;
close p_cursor;
end;
在asp.net中调用
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//int myrows = 0;
//int pagecount = 0;
DataSet ds = new DataSet();
ds = QuickPage("test",3,1);
//返回记录总数
//Label1.Text = pagecount.ToString();
//返回结果集绑定GridView
GridView1.DataSource = ds.Tables["test"];
GridView1.DataBind(); } public DataSet QuickPage(string tablename, int pagesize, int pagenow)
{
OracleConnection con = new OracleConnection("data source=lzy;uid=system;pwd=123456");
OracleCommand cmd = new OracleCommand("FY", con);
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("tablename", OracleType.VarChar, 50);
cmd.Parameters["tablename"].Direction = ParameterDirection.Input;
cmd.Parameters["tablename"].Value = tablename; cmd.Parameters.Add("pagesize", OracleType.Number);
cmd.Parameters["pagesize"].Direction = ParameterDirection.Input;
cmd.Parameters["pagesize"].Value = pagesize; cmd.Parameters.Add("pagenow", OracleType.Number);
cmd.Parameters["pagenow"].Direction = ParameterDirection.Input;
cmd.Parameters["pagenow"].Value = pagenow; //cmd.Parameters.Add("myrows", OracleType.Number);
//cmd.Parameters["myrows"].Direction = ParameterDirection.Output; //cmd.Parameters.Add("pagecount", OracleType.Number);
//cmd.Parameters["pagecount"].Direction = ParameterDirection.Output; cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.ReturnValue; try
{
con.Open();
OracleDataAdapter da=new OracleDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds,"test");
//myrows = Convert.ToInt32(cmd.Parameters["myrows"].Value.ToString());
//pagecount = Convert.ToInt32(cmd.Parameters["pagecount"].Value.ToString());
con.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
} }
}RA-06550: 第 1 行, 第 20 列:
PLS-00306: 调用 'FY' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored老是这个 烦死了 救命啊
:
create or replace procedure fy (
tablename in varchar2,
pagesize in number,
pagenow in number,
myrows out number,
pagecount out number,
p_cursor out fenye.test_cursor
)is
v_sql varchar2(1000);
v_bedin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
v_sql := 'select * from (select t1.*,rownum rn from(select * from '||tablename||' )t1 where rownum<= '||v_bedin||' where rn>='||v_end;
open p_cursor for v_sql;
v_sql := 'select cout(*)from '||tablename;
execute immediate v_sql into myrows;
if mod(myrows,pagesize)=0 then pagecount:=myrows/pagesize;
else
pagecount:=myrows/pagesize+1;
end if;
close p_cursor;
end;
在asp.net中调用
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//int myrows = 0;
//int pagecount = 0;
DataSet ds = new DataSet();
ds = QuickPage("test",3,1);
//返回记录总数
//Label1.Text = pagecount.ToString();
//返回结果集绑定GridView
GridView1.DataSource = ds.Tables["test"];
GridView1.DataBind(); } public DataSet QuickPage(string tablename, int pagesize, int pagenow)
{
OracleConnection con = new OracleConnection("data source=lzy;uid=system;pwd=123456");
OracleCommand cmd = new OracleCommand("FY", con);
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("tablename", OracleType.VarChar, 50);
cmd.Parameters["tablename"].Direction = ParameterDirection.Input;
cmd.Parameters["tablename"].Value = tablename; cmd.Parameters.Add("pagesize", OracleType.Number);
cmd.Parameters["pagesize"].Direction = ParameterDirection.Input;
cmd.Parameters["pagesize"].Value = pagesize; cmd.Parameters.Add("pagenow", OracleType.Number);
cmd.Parameters["pagenow"].Direction = ParameterDirection.Input;
cmd.Parameters["pagenow"].Value = pagenow; //cmd.Parameters.Add("myrows", OracleType.Number);
//cmd.Parameters["myrows"].Direction = ParameterDirection.Output; //cmd.Parameters.Add("pagecount", OracleType.Number);
//cmd.Parameters["pagecount"].Direction = ParameterDirection.Output; cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.ReturnValue; try
{
con.Open();
OracleDataAdapter da=new OracleDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds,"test");
//myrows = Convert.ToInt32(cmd.Parameters["myrows"].Value.ToString());
//pagecount = Convert.ToInt32(cmd.Parameters["pagecount"].Value.ToString());
con.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
} }
}RA-06550: 第 1 行, 第 20 列:
PLS-00306: 调用 'FY' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored老是这个 烦死了 救命啊
pagesize in number,
pagenow in number,
myrows out number,
pagecount out number,
p_cursor out fenye.test_cursorc# 里面 你的参数不够啊myrows 和pagecount怎么注释了?
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int myrows = 0;
int pagecount = 0;
DataSet ds = new DataSet();
ds = QuickPage("test",3,1,ref myrows,ref pagecount);
//返回记录总数
//Label1.Text = pagecount.ToString();
//返回结果集绑定GridView
GridView1.DataSource = ds.Tables["test"];
GridView1.DataBind(); } public DataSet QuickPage(string tablename, int pagesize, int pagenow,ref int myrows,ref int pagecount )
{
OracleConnection con = new OracleConnection("data source=lzy;uid=system;pwd=123456");
OracleCommand cmd = new OracleCommand("FY", con);
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("tablename", OracleType.VarChar, 50);
cmd.Parameters["tablename"].Direction = ParameterDirection.Input;
cmd.Parameters["tablename"].Value = tablename; cmd.Parameters.Add("pagesize", OracleType.Number);
cmd.Parameters["pagesize"].Direction = ParameterDirection.Input;
cmd.Parameters["pagesize"].Value = pagesize; cmd.Parameters.Add("pagenow", OracleType.Number);
cmd.Parameters["pagenow"].Direction = ParameterDirection.Input;
cmd.Parameters["pagenow"].Value = pagenow; cmd.Parameters.Add("myrows", OracleType.Number);
cmd.Parameters["myrows"].Direction = ParameterDirection.Output; cmd.Parameters.Add("pagecount", OracleType.Number);
cmd.Parameters["pagecount"].Direction = ParameterDirection.Output; cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.ReturnValue; try
{
con.Open();
OracleDataAdapter da=new OracleDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds,"test");
myrows = Convert.ToInt32(cmd.Parameters["myrows"].Value.ToString());
pagecount = Convert.ToInt32(cmd.Parameters["pagecount"].Value.ToString());
con.Close();
return ds;
}
catch (Exception ex)
{
throw ex;
} }
}还是出现同样的问题
。。
{
OracleCommand cmd = new OracleCommand("getgatequeue", con);//为外部创建的连接对象
cmd.CommandType = CommandType.StoredProcedure;
OracleParameterCollection Oparams = cmd.Parameters;
Oparams.Add(new OracleParameter("out_cursor", OracleType.Cursor));
Oparams.Add(new OracleParameter("spgate", OracleType.VarChar));
Oparams["test"].Value = "wwww";
Oparams["spgate"].Value = "1065";
Oparams["out_cursor"].Direction = ParameterDirection.Output;
con.Open();
OracleDataReader dr = cmd.ExecuteReader(); IList<SpgateQueue> rbd = new List<SpgateQueue>();
while (dr.Read())
{
SpgateQueue mask = new SpgateQueue();
mask.Gatename = dr["GATENAME"].ToString();
mask.Signstr = dr["SIGNSTR"].ToString();
mask.Spgate = dr["SPGATE"].ToString();
mask.Status = Convert.ToInt32(dr["STATUS"].ToString());
mask.Speed = Convert.ToInt32(dr["SPEED"].ToString());
mask.Signlen = Convert.ToInt32(dr["SIGNLEN"].ToString());
rbd.Add(mask);
}
dr.Close();
return rbd;
}
catch (Exception ex)
{
throw;
}
code=C#] cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.ReturnValue;[/code]
改为
cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.Output;
应该就可以解决了
cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.ReturnValue;
改为
cmd.Parameters.Add("p_cursor", OracleType.Cursor);
cmd.Parameters["p_cursor"].Direction = ParameterDirection.Output;
应该就可以解决了