一种动态构建PLSQL包进行批量数据处理的方法

本处理方法来源自PetShop的数据访问层处理方式,本文对执行存储过程时包长度限制问题做了处理。

数据库连接在企业级的系统中是一种非常重要的资源，所以，程序应该尽量减少对数据库的频繁访问，访问后应该释放数据库连接资源。存储过程具备ACID特性，在我所在开发系统中，对数据库的访问都是通过存储过程来完成。存储过程是存储在数据库服务器中，然而目前安装的oracle版本没有引入数组参数，所以，当需要批量创建或者其他数据量大的批量操作时，是非常困难的。下面代码提供了一种动态构建PL/SQL包，达到批量创建的目的。同时，本示例也为对数据库的访问操作提供了另外一种方式：并非存储过程一定要写死，写定的由数据库管理，由应用程序动态存储过程执行更具备灵活性。

 

namespace XXX.Dal.ParameterTechnicsDal

{

    /// <summary>

    /// 为批量创建特征参数提供服务

    /// </summary>

    public class ParametersCreater

    {

        const string SQL_INSERT_PARAMETER = "INSERT INTO PRM_PARAMETERS VALUES (prm_parameters_S.NEXTVAL,:ename{0},:cname{0},:sort{0},:num{0},:source{0},:value{0},:valuetype{0},:paramtype{0},:default{0},:discription{0},:comment{0},:partid{0});";

 

        const string PRM_ENAME = ":ename{0}";

        const string PRM_CNAME = ":cname{0}";

        const string PRM_SORT = ":sort{0}";

        const string PRM_NUM = ":num{0}";

        const string PRM_SOURCE = ":source{0}";

        const string PRM_VALUE = ":value{0}";

        const string PRM_VALUETYPE = ":valuetype{0}";

        const string PRM_PARAMTYPE = ":paramtype{0}";

        const string PRM_DEFAULT = ":default{0}";

        const string PRM_DISCRIPTION = ":discription{0}";

        const string PRM_COMMENT = ":comment{0}";

        const string PRM_PARTID = ":partid{0}";

 

       void CreaterParametersNoLimit(IList<ParameterInfo> parameters)

        {

            if (parameters == null || parameters.Count == 0)

                return;

 

            CDBAccess db = new CDBAccess();

            StringBuilder finalSQLInsert = new StringBuilder("BEGIN ");//存储PL/SQL包

 

            int numberOfParameters = parameters.Count * 12;

            IDbDataParameter[] param = new IDbDataParameter[numberOfParameters];

 

            int i = 1;

            int index = 0;

            foreach (ParameterInfo parameter in parameters)

            {

                string sql = string.Format(SQL_INSERT_PARAMETER, i);

                finalSQLInsert.Append(sql);

 

                param[index++] = db.CreateInParameter(string.Format(PRM_ENAME, i), DbType.String, parameter.ObjectName);

                param[index++] = db.CreateInParameter(string.Format(PRM_CNAME, i), DbType.String, parameter.CName);

                param[index++] = db.CreateInParameter(string.Format(PRM_SORT, i), DbType.String, parameter.SortType);

                param[index++] = db.CreateInParameter(string.Format(PRM_NUM, i), DbType.Decimal, parameter.ParamNum);

                param[index++] = db.CreateInParameter(string.Format(PRM_SOURCE, i), DbType.String, parameter.ValueSource);

                param[index++] = db.CreateInParameter(string.Format(PRM_VALUE, i), DbType.String, parameter.Value);

                param[index++] = db.CreateInParameter(string.Format(PRM_VALUETYPE, i), DbType.String, parameter.ValueType);

                param[index++] = db.CreateInParameter(string.Format(PRM_PARAMTYPE, i), DbType.String, parameter.ParamType);

                param[index++] = db.CreateInParameter(string.Format(PRM_DEFAULT, i), DbType.String, parameter.DefaultValue);

                param[index++] = db.CreateInParameter(string.Format(PRM_DISCRIPTION, i), DbType.String, parameter.Discription);

                param[index++] = db.CreateInParameter(string.Format(PRM_COMMENT, i), DbType.String, parameter.Remark);

                param[index++] = db.CreateInParameter(string.Format(PRM_PARTID, i), DbType.Decimal, parameter.PartId);

 

                i++;

            }

 

            finalSQLInsert.Append("END;");

 

            db.ExecuteSQL(finalSQLInsert.ToString(), param);

        }

        public void CreaterParameters(IList<ParameterInfo> parameters)

        {

            //估算需要的sql长度

            int n = 60000 / SQL_INSERT_PARAMETER.Length;

            int allcount = parameters.Count;

            int round = allcount / n;

            List<ParameterInfo>[] parametersArray = new List<ParameterInfo>[round];

            for (int i = 0; i < round; i++)

            {

                int start = i * round;

                int tempend = (i + 1) * round;

                int end = tempend > allcount ? allcount : tempend;

                parametersArray[i] = new List<ParameterInfo>();

                for (int j = start; j < end; j++)

                {

                    parametersArray[i].Add(parameters[j]);

                }

            }

 

            foreach (IList<ParameterInfo> parametersList in parametersArray)

            {

                CreaterParametersNoLimit(parametersList);

            }

        }

    }

}

转载于:https://www.cnblogs.com/magiclee/archive/2008/07/11/1241084.html