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         }