oracle游标分页
来源:互联网 发布:phpcms v9 小程序源码 编辑:程序博客网 时间:2024/06/05 16:33
1、先建一个包声明一个游标类型
- create or replace package pkg_dividepage as
- type cur_page is ref cursor;
- end pkg_dividepage;
2、然后创建存储过程 如下:
- create or replace procedure proc_dividepage(p_tableName varchar2,
- p_pageIndex number,
- p_pageSize number,
- p_pageCount out number,
- p_totalCount out number,
- v_cursor out pkg_dividepage.cur_page,
- p_where varchar2,
- p_order_key varchar2,
- p_order_column varchar2
- ) as
-
- v_pageIndex number;
- v_pageSize number;
- v_startCurs number;
- v_endCurs number;
- v_sql varchar2(2000);
-
-
- begin
-
- v_sql := 'select count(*) from ' || p_tableName;
-
- if p_where is not null or p_where <> '' then
- v_sql := v_sql || ' where ' || p_where;
- end if;
- execute immediate v_sql
- into p_totalCount;
-
- p_pageCount := ceil(p_totalCount / p_pageSize);
-
- v_pageSize := p_pageSize;
-
- if v_pageSize < 0 then
- v_pageSize := 0;
- end if;
-
- v_pageIndex := p_pageIndex;
-
- if v_pageIndex < 0 then
- v_pageIndex := 1;
- end if;
-
- if v_pageIndex > p_pageCount then
- v_pageIndex := p_pageCount;
- end if;
-
- v_startCurs := (v_pageIndex - 1) * v_pageSize + 1;
- v_endCurs := v_pageIndex * v_pageSize;
-
- v_sql := 'select * from (select rownum num,t.* from (select * from ' ||
- p_tableName;
-
- if p_where is not null or p_where <> '' then
- v_sql := v_sql || ' where ' || p_where;
- end if;
-
- if p_order_column is not null or p_order_column <> '' then
- v_sql := v_sql || ' order by ' || p_order_column || ' ' || p_order_key;
- end if;
-
- v_sql := v_sql || ') t where rownum<=' || v_endCurs || ')where num>=' ||
- v_startCurs;
-
- open v_cursor for v_sql;
- dbms_output.put_line(v_sql);
- end proc_dividepage;
3、c#绑定游标
OracleConnection conn = new OracleConnection("YourConnectString");
OracleCommand cmd = new OracleCommand("testpro", conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter op = new OracleParameter("c", OracleType.Cursor);
op.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(op);
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds,"test");
this.dataGridView1.DataSource = ds.Tables["test"];
转载:http://xzuse.iteye.com/blog/746318