Int

非空

产品 ID

WeekDate

smalldatetime

非空

需求预测周

DemandQty

int

非空

特定产品和特定周的需求预测

给定一组产品，它们的库存和启动成本以及未来需求预测，我们创建了接受如下输入参数的存储过程：1)制订生产进度表的日期，2)按进度表生产所需要的周数。

存储过程返回带有下表中的架构的行集。

表 3：存储过程架构 列名 类型 说明

Product

nvarchar(256)

产品名称

Period

datetime

进度周

Quantity

int

在指定周内制造的产品的数量

将 C# 版本的代码复制到下面的代码中，以说明这种可以从 CLR 集成中大大获益的情况：

using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; public class ProductionSchedule { //4-year limit on scheduling public const int MAXPRODUCTS = 101; public const int MAXWEEKS = 210; public const int MAXNAME = 256; public ProductionSchedule() { } public static int Schedule(SqlDateTime startDate, int numWeeks) { SqlDateTime[] week = new SqlDateTime[MAXWEEKS]; int[] quantity; int[][] Cij; int[] Fk; int[] minK = new int[MAXWEEKS]; int product_id, current_product, product_count = 0; int startPeriod; // We'll use arrays to keep state about products and forecasts in memory. This is only viable given that we know we have a small number of products and weeks. // For larger data sets, we would have to consider cursors or temporary tables. // stored as CLR types since we know they can't be null int[] h = new int[MAXPRODUCTS]; int[] K = new int[MAXPRODUCTS]; // stored as nullable SqlChars since the table schema allows for null names SqlChars[] productNames = new SqlChars[MAXPRODUCTS]; bool moreProducts = true; int optimal_j; int period; int sum; SqlPipe pipe = SqlContext.GetPipe(); SqlDataRecord record; object[] values = new object[3]; SqlMetaData[] metadata = new SqlMetaData[3]; //Initialize algorithm arrays Cij = new int[MAXWEEKS][]; for( int l=0;l<MAXWEEKS;l++) Cij[l] = new int[MAXWEEKS]; Fk = new int[MAXWEEKS]; //Look up K and h for all products SqlCommand cmd = SqlContext.GetCommand(); cmd.CommandText = @"SELECT pname, InventoryCost, StartupCost from dbo.t_Products ORDER BY PID"; SqlDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { productNames[product_count] = reader.GetSqlChars(0); //product name h[product_count] = reader.GetInt32(1); //holding cost K[product_count] = reader.GetInt32(2); //startup cost product_count++; // if we exceeded number of expected products then bail out with an exception if (product_count >= MAXPRODUCTS) { throw new Exception("Too many products"); } } reader.Close(); product_count = 0; //Get the list of product ids; cmd = SqlContext.GetCommand(); cmd.CommandText = @"select PID, weekdate, DemandQty from dbo.t_SalesForecast ORDER BY PID, WeekDate"; reader = cmd.ExecuteReader(); moreProducts=reader.Read(); //Set up the record for returning results metadata[0] = new SqlMetaData( "Product", SqlDbType.NVarChar,MAXNAME ); metadata[1] = new SqlMetaData( "Period", SqlDbType.DateTime ); metadata[2] = new SqlMetaData( "Quantity", SqlDbType.Int ); record = new SqlDataRecord( metadata ); while( moreProducts ) { product_id = current_product = reader.GetInt32(0); int index = 1; quantity = new int[MAXWEEKS]; while( current_product == product_id ) { week[index] = reader.GetSqlDateTime(1); quantity[index] = reader.GetInt32(2); index++; moreProducts = reader.Read(); if( !moreProducts ) break; current_product = reader.GetInt32(0); } //Determine the ordinal start week startPeriod = 1; //For each product ID calculate Cij for( int i = startPeriod; i < (startPeriod + numWeeks); i++ ) { for( int j = i+1; j <= (startPeriod + numWeeks+1); j++ ) { Cij[i][j] = GetCij(quantity,i,j,K [product_count],h[product_count]); } } //Calculate Fk for( int k = startPeriod + numWeeks + 1; k >= startPeriod; k--) { minK[k] = GetFk_SO(k,startPeriod + numWeeks,Cij,Fk); } //Send the results record.SetSqlChars(0,productNames[product_count]); pipe.SendResultsStart(record,false); for( int k = startPeriod; k < startPeriod + numWeeks; ) { period = k; optimal_j = minK[k]; sum = 0; while( k < optimal_j ) { sum = sum + quantity[k++]; } values[1] = week[period]; record.SetValue(1,values[1]); values[2] = sum; record.SetValue(2,values[2]); pipe.SendResultsRow(record); } pipe.SendResultsEnd(); product_count++; } reader.Close(); return 0; } private static int GetCij(int[] quantities, int i, int j, int K, int h) { if( j == i+1 ) return K; else return (j-1-i) * h * quantities[j-1] + GetCij(quantities, i, j-1,K,h); } private static int GetFk_SO(int k,int n,int[][] Cij, int[] Fk) { int j,min; j = k+1; min = j; if ( k == n+1 ) { Fk[k] = 0; return j; } Fk[k] = Cij[k][j] + Fk[j]; for(;