oracle中fn_getpy函数,davidliu

1. 分区表简介

SQL SERVER的表分区功能是为了将一个大表(表中含有非常多条数据)的数据根据某条件(仅限该表的主键)拆分成多个文件存放，以提高查询数据时的效率.分区表在逻辑上是一个表，而物理上是多个表。从用户角度来看，分区表和普通表是一样的。使用分区表的主要目的是为改善大型表以及具有多个访问模式的表的可伸缩性和可管理性。

对SQL

Server数据表进行分区的过程分为三个步骤：

1) 建立分区函数

2) 建立分区方案

3) 对表格进行分区

拆分与合并分区

ALTERPARTITIONFUNCTION语法如下：ALTERPARTITIONFUNCTIONpartition_function_name()

{

SPLIT RANGE

( boundary_value )|MERGE RANGE ( boundary_value )

}

值得注意的是，当新增一分区时，若没有指定可用的文件组，可用下面SQL指定下一个可用的文件组。

ALTERPARTITION SCHEME PARTITION_FUNCTION_NAME

NEXTUSED[FILE_GROUP_NAME]

http://www.cnblogs.com/libingql/p/4087598.html

public DataTable

GetListByPageIndex(PagingInfo pPageInfo, string condition, string

orderby)

{

Paginator p = new Paginator();

p.Fields = @" t0.*, case when IS_SYNC_CRM ='false' then '否'

else  '是' end IsSyncCrm

,t1.DEALER_CODE,t1.DEALER_SHORTNAME,t2.VALUE,

t0.SYNC_DATETIME AS SYNC_CRM_DATETIME,

case  c.IS_SYNC when 1 then '是' else '否' end as

IsSyncApp,

case  c.IS_SYNC when 1 then c.SYNC_TIME else NULL

end as SYNC_APP_DATETIME ";

p.Tables = @" IVIEW_SALES_LEADS t0  left

join  VIEW_DEALER t1 on

t0.DEALER_ID = t1.DEALER_ID

left join PARA t2 on t0.STATUS = t2.CODE

left join COMMON_SYNC_FLAG c on t0.LEAD_ID=c.RECORD_ID and

c.THIRD_SYS='SCRM' AND c.TABLE_NAME='SALES_LEADS'

";

if (!string.IsNullOrEmpty(condition))

{

p.Condition = @" " + condition;

}

if (!string.IsNullOrEmpty(orderby))

{

p.OrderBy = @" " + orderby;

}

p.PagingInfo = pPageInfo;

PaginatorHelper helper = new PaginatorHelper();

helper.Paginator = p;

DataTable dt = helper.GetPagingData();

return dt;

}

----------------------------------------------------------------------------------

public class PagingInfo

{

///

/// 每页记录数

///

public int PageSize { get; set; }

///

/// 页的索引，从1开始

///

public int PageIndex { get; set; }

///

/// 总记录数

///

public int RecordCounts { get; set; }

///

/// 是否获取页的总数

///

public bool IsGetPageNumers { get; set;

}

public PagingInfo()

{

PageIndex = 1;

PageSize = CommonHelper.GetPageSize();

}

public PagingInfo(int pPageSize)

{

PageIndex =1;

PageSize = pPageSize;

}

}

------------------------------------------------------------------------------------------------------------------

public class PaginatorHelper

{

public Paginator Paginator { get; set; }

string SPName = "Proc_Pager"; //存储过程

public DataTable GetPagingData()

{

if (Paginator.PagingInfo.IsGetPageNumers)

{

Paginator.PagingInfo.RecordCounts = GetRecordCount();

}

if ((Paginator.PagingInfo.PageIndex - 1) *

Paginator.PagingInfo.PageSize + 1 >

Paginator.PagingInfo.RecordCounts)//如果当前页超出记录数

{

Paginator.PagingInfo.PageIndex = (Paginator.PagingInfo.RecordCounts

- 1) / Paginator.PagingInfo.PageSize + 1;

}

List list = PreparePaginator();

SqlParameter para6 = new SqlParameter("@IsTotal",

SqlDbType.Bit);

para6.Value = false;

list.Add(para6);

DataSet ds = DbHelperSql.QuerySP(SPName, list.ToArray());

if (ds != null && ds.Tables.Count > 0)

{

return ds.Tables[0];

}

return null;

}

public DataTable GetPagingDataNew()

{

if (Paginator.PagingInfo.IsGetPageNumers)

{

Paginator.PagingInfo.RecordCounts = GetRecordCountNew();

}

List list = PreparePaginator();

SqlParameter para6 = new SqlParameter("@IsTotal",

SqlDbType.Bit);

para6.Value = false;

list.Add(para6);

DataSet ds = DbHelperSql.QuerySP(SPName, list.ToArray());

if (ds != null && ds.Tables.Count > 0)

{

return ds.Tables[0];

}

return null;

}

int GetRecordCount()

{

List list = PreparePaginator();

SqlParameter para6 = new SqlParameter("@IsTotal",

SqlDbType.Bit);

para6.Value = true;

list.Add(para6);

DataSet ds = DbHelperSql.QuerySP(SPName, list.ToArray());

if (ds != null && ds.Tables.Count > 0)

{

DataTable dt = ds.Tables[0];

if (dt != null && dt.Rows.Count > 0)

{

return Convert.ToInt32(dt.Rows[0][0]);

}

}

return 0;

}

private List PreparePaginator()

{

List list = new List();

SqlParameter para = new SqlParameter("@Tables",

SqlDbType.NVarChar);

para.Value = Paginator.Tables;

SqlParameter para1 = new SqlParameter("@Fields",

SqlDbType.NVarChar);

para1.Value = Paginator.Fields;

SqlParameter para2 = new SqlParameter("@Order",

SqlDbType.NVarChar);

para2.Value = Paginator.OrderBy;

SqlParameter para3 = new SqlParameter("@PageSize",

SqlDbType.Int);

para3.Value = Paginator.PagingInfo.PageSize;

SqlParameter para4 = new SqlParameter("@PageIndex",

SqlDbType.Int);

para4.Value = Paginator.PagingInfo.PageIndex;

SqlParameter para5 = new SqlParameter("@Condition",

SqlDbType.NVarChar);

para5.Value = Paginator.Condition;

list.Add(para);

list.Add(para1);

list.Add(para2);

list.Add(para3);

list.Add(para4);

list.Add(para5);

if (!string.IsNullOrEmpty(Paginator.ConditionEx))

{

SqlParameter para6 = new SqlParameter("@ConditionEx",

SqlDbType.NVarChar);

para6.Value = Paginator.ConditionEx;

list.Add(para6);

}

return list;

}

int GetRecordCountNew()

{

List list = PreparePaginatorCount();

SqlParameter para6 = new SqlParameter("@IsTotal",

SqlDbType.Bit);

para6.Value = true;

list.Add(para6);

DataSet ds = DbHelperSql.QuerySP(SPName, list.ToArray());

if (ds != null && ds.Tables.Count > 0)

{

DataTable dt = ds.Tables[0];

if (dt != null && dt.Rows.Count > 0)

{

return Convert.ToInt32(dt.Rows[0][0]);

}

}

return 0;

}

private List PreparePaginatorCount()

{

List list = new List();

SqlParameter para = new SqlParameter("@Tables",

SqlDbType.NVarChar);

para.Value = Paginator.CountTables;

SqlParameter para1 = new SqlParameter("@Fields",

SqlDbType.NVarChar);

para1.Value = Paginator.Fields;

SqlParameter para2 = new SqlParameter("@Order",

SqlDbType.NVarChar);

para2.Value = Paginator.OrderBy;

SqlParameter para3 = new SqlParameter("@PageSize",

SqlDbType.Int);

para3.Value = Paginator.PagingInfo.PageSize;

SqlParameter para4 = new SqlParameter("@PageIndex",

SqlDbType.Int);

para4.Value = Paginator.PagingInfo.PageIndex;

SqlParameter para5 = new SqlParameter("@Condition",

SqlDbType.NVarChar);

para5.Value = Paginator.Condition;

list.Add(para);

list.Add(para1);

list.Add(para2);

list.Add(para3);

list.Add(para4);

list.Add(para5);

if (!string.IsNullOrEmpty(Paginator.ConditionEx))

{

SqlParameter para6 = new SqlParameter("@ConditionEx",

SqlDbType.NVarChar);

para6.Value = Paginator.ConditionEx;

list.Add(para6);

}

return list;

}

}