oracle分页存储过程

oracle过程代码如下

 1 --创建一个包
 2 CREATE OR REPLACE PACKAGE PKG_QUERY AS
 3   TYPE CUR_QUERY IS REF CURSOR;
 4 END PKG_QUERY;
 5 
 6 CREATE OR REPLACE PROCEDURE PRC_QUERY(P_TABLENAME    IN VARCHAR2, --表名
 7                                       P_COLUMNS      IN VARCHAR2, --查询的栏位
 8                                       P_CONDITION    IN VARCHAR2, --查询条件
 9                                       P_SORT         IN VARCHAR2, --排序字段
10                                       P_DIR          IN VARCHAR2, --排序方式
11                                       P_PAGEINDEX    IN NUMBER, --当前页
12                                       P_PAGESIZE     IN NUMBER, --每页记录数
13                                       P_TOTALRECORDS OUT NUMBER, --总记录数
14                                       V_CUR          OUT PKG_QUERY.CUR_QUERY) IS
15   V_SQL   VARCHAR2(1000) := ''; --SQL语句
16   V_START NUMBER(4); --开始显示的记录条数
17   V_LIMIT NUMBER(4); --结束的记录条数
18 BEGIN
19 
20   --查询总记录数
21   V_SQL := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || P_TABLENAME ||
22            ' WHERE 1=1 ';
23   IF P_CONDITION IS NOT NULL OR P_CONDITION <> '' THEN
24     V_SQL := V_SQL || P_CONDITION;
25   END IF;
26   EXECUTE IMMEDIATE V_SQL
27     INTO P_TOTALRECORDS;
28 
29   --实现分页查询
30   V_START := (P_PAGEINDEX - 1) * P_PAGESIZE + 1;
31   V_LIMIT := P_PAGEINDEX * P_PAGESIZE;
32   V_SQL   := 'SELECT ' || P_COLUMNS ||
33              ' FROM (SELECT ROW_NUMBER() OVER(ORDER BY ' || P_SORT || ' ' ||
34              P_DIR || ')TEMP_ROW_NUM,T1.' || P_COLUMNS || ' FROM ' ||
35              P_TABLENAME || ' T1 WHERE 1=1 ';
36   IF P_CONDITION IS NOT NULL OR P_CONDITION <> '' THEN
37     V_SQL := V_SQL || P_CONDITION;
38   END IF;
39   V_SQL := V_SQL || ') WHERE TEMP_ROW_NUM BETWEEN ' || V_START || ' AND ' ||
40            V_LIMIT;
41   --DBMS_OUTPUT.put_line(V_SQL);
42   OPEN V_CUR FOR V_SQL;
43 
44 END PRC_QUERY;

 

.NET调用代码（用ODP.NET）

 1 public DataTable QueryPage(string table, string fields, string condition, Pager pager, ref int totalRecords)
 2         {
 3             _conn = new OracleConnection(_connStr);
 4             _conn.Open();
 5             _cmd = _conn.CreateCommand();
 6             _cmd.CommandText = "PRC_QUERY";
 7             _cmd.CommandType = CommandType.StoredProcedure;
 8             _cmd.Parameters.Add("P_TABLENAME", OracleDbType.Varchar2);
 9             _cmd.Parameters["P_TABLENAME"].Direction = ParameterDirection.Input;
10             _cmd.Parameters["P_TABLENAME"].Value = table;
11             _cmd.Parameters.Add("P_COLUMNS", OracleDbType.Varchar2);
12             _cmd.Parameters["P_COLUMNS"].Direction = ParameterDirection.Input;
13             _cmd.Parameters["P_COLUMNS"].Value = fields;
14             _cmd.Parameters.Add("P_CONDITION", OracleDbType.Varchar2);
15             _cmd.Parameters["P_CONDITION"].Direction = ParameterDirection.Input;
16             _cmd.Parameters["P_CONDITION"].Value = condition;
17             _cmd.Parameters.Add("P_SORT", OracleDbType.Varchar2);
18             _cmd.Parameters["P_SORT"].Direction = ParameterDirection.Input;
19             _cmd.Parameters["P_SORT"].Value = pager.OrderBy;
20             _cmd.Parameters.Add("P_DIR", OracleDbType.Varchar2);
21             _cmd.Parameters["P_DIR"].Direction = ParameterDirection.Input;
22             _cmd.Parameters["P_DIR"].Value = pager.OrderType == OrderType.DESC ? "DESC" : "ASC";
23             _cmd.Parameters.Add("P_PAGEINDEX", OracleDbType.Int32);
24             _cmd.Parameters["P_PAGEINDEX"].Direction = ParameterDirection.Input;
25             _cmd.Parameters["P_PAGEINDEX"].Value = pager.PageIndex;
26             _cmd.Parameters.Add("P_PAGESIZE", OracleDbType.Int32);
27             _cmd.Parameters["P_PAGESIZE"].Direction = ParameterDirection.Input;
28             _cmd.Parameters["P_PAGESIZE"].Value = pager.PageSize;
29             _cmd.Parameters.Add("P_TOTALRECORDS", OracleDbType.Int32);
30             _cmd.Parameters["P_TOTALRECORDS"].Direction = ParameterDirection.Output;
31             _cmd.Parameters["P_TOTALRECORDS"].Value = 0;
32             _cmd.Parameters.Add("V_CUR", OracleDbType.RefCursor);
33             _cmd.Parameters["V_CUR"].Direction = ParameterDirection.Output;
34 
35             _dataAdapter = new OracleDataAdapter(_cmd);
36             DataTable dt = new DataTable();
37             _dataAdapter.Fill(dt);            
38             totalRecords = int.Parse(_cmd.Parameters["P_TOTALRECORDS"].Value.ToString());
39             _conn.Close();
40             _conn.Dispose();
41             _cmd.Dispose();
42             return dt;
43         }



 

posted @ 2012-03-30 16:50  lzone6  阅读(448)  评论(1)    收藏  举报