优化上传导入名单模块之Oracle的存储过程简记

项目中要把号码名单（Excel，ACCESS，txt）导入Oracle数据库，以前只是用少量数据进行测试，一期项目上线后要求改进。

按照以前的代码的效率，5万条数据导下来要半小时，当然，里面有业务逻辑，需要每次从别的几张表中查询是否重复，并删除。

现在需求，20万条数据最多20分钟，于是，翻工重写。

我们的解决方案如下：

1、从每次插入1条数据变成每次向数据库插入1000条数据。2、编写存储过程，业务逻辑性的操作在存储过程中进行。3、利用多线程，让用户上传的时候可以做别的操作。

本篇文章先解决前两个内容。（当时我写此文章开头的时候，总监过来了，一番交流后，他建议我用ODP，100万条数据几秒钟搞定，当时我就感觉我们的方案弱爆了。。毕竟刚毕业的学生，不知道ODP，情有可原，情有可原。。）

一、废话少说，先解决每条sql语句向数据库插入1000条数据，代码如下：

  string sql = string.Empty;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i += 1000)//一千个号码提交一次
                {
                    sql = string.Empty;
                    sql += "begin  ";//bengin后留个空格，没有分号
                    for (int j = i; j < i + 1000 && j < ds.Tables[0].Rows.Count; j++)
                    {
                        ele[0] = ds.Tables[0].Rows[j][selectnum].ToString();
                        ele[1] = ds.Tables[0].Rows[j][selectname].ToString();
                        if (flag[j] == false)
                        {
                            if (CheckBox1.Checked == false)//这个是选择框是否被选择，不选的话不导入姓名。
                            {
                                sql += string.Format("insert into KA_BlackList (telnum,telname,inserttime,listtype,companyid,departid,operid) values('{0}',null,sysdate,{1},null,{2},{3});", ele[0], "1", "1", session);
                            }
                            else
                            {

                                sql += string.Format("insert into KA_BlackList (telnum,telname,inserttime,listtype,companyid,departid,operid) values('{0}','{1}',sysdate,{2},null,{3},{4});", ele[0], ele[1], "1", "1", session);
                            }

                        }
                    }
                    sql += "  commit; end;";

以前没做过，其实语法很简单，注意begin后面没分号，C#语句写的时候begin后要加个空格隔开

begin

sql语句。。。；

sql语句。。。；

sql语句。。。；

commit；

end；

二、下面说一下oracle的存储过程

　　1  CREATE OR REPLACE PROCEDURE 存储过程名（CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个存储过程, 如果存在就覆盖它;）

　　2  IS或者AS（单独的FUNCTION, PROCEDURE 习惯用AS，PACKAGE 中的FUNCTION, PROCEDURE 用IS，功能基本一样，但需要指定的时候用IS）

　　3  BEGIN（这个也是没有分号！！）

　　4  NULL;（有sql语句就写sql语句，没有的话必须加NULL）

　　5  END;

下面是项目中的存储过程：

CREATE OR REPLACE Procedure KEYACCOUNT.KA_blacklist_Pro
(--这些是返回的参数。
  count1   out   number,--本表号码重复数
  count2   out   number,-- 白名单号码重复数
  count3   out   number --例外名单号码重复数
) 
as--这些是定义的变量
 breforedel_count   number;--导入号码后，还没去重前的号码数
 afterdel_count    number;--去重后的号码数
 
begin

    select count(*) into breforedel_count  from KA_blacklist ;--查询出去重前的号码数
    
    delete from KA_blacklist a where  a.id!=(select min(id) from KA_blacklist b where a.telnum=b.telnum);--本表的去重
    select count(*) into  afterdel_count from KA_blacklist;--本表去重后的号码数
    count1:=breforedel_count-afterdel_count;--求出本表的重复数
   
    select count(*) into count2 from KA_blacklist where telnum in (select telnum from ka_whitelist);--查询与白名单的重复号码数
    delete KA_whitelist where telnum in (select telnum from ka_blacklist);--白名单的去重，即把白名单中与黑名单相同的号码删除
     
    select count(*) into count3 from KA_blacklist where telnum in (select telnum from ka_exceptlist);--查询与例外名单的重复号码数
    delete KA_exceptlist where telnum in (select telnum from ka_blacklist);--例外名单的去重，即把例外名单中与黑名单相同的号码删除

end;
/

 再写一下如何在C#中调用存储过程：

//定义三个出参

                OracleParameter[] pars = { new OracleParameter("count1", OracleType.Number), new OracleParameter("count2", OracleType.Number), new OracleParameter("count3", OracleType.Number) };
                pars[0].Direction = ParameterDirection.Output;
                pars[1].Direction = ParameterDirection.Output;
                pars[2].Direction = ParameterDirection.Output;

 

 //执行存储过程

 using (OracleConnection con = new OracleConnection(sConnectionString))
            {
                con.Open();
                OracleCommand cmd = new OracleCommand(存储过程名, con);
                if (pars!= null)
                {
                    foreach (OracleParameter par  in  pars)
                    {
                        cmd.Parameters.Add(par);
                    }
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                con.close();

 

//定义变量，把出参的值赋给它们

                int a1 = ds.Tables[0].Rows.Count - int.Parse(pars[0].Value.ToString())-err;
                int a2 = int.Parse(pars[0].Value.ToString());
                int a3 = int.Parse(pars[1].Value.ToString());
                int a4 = int.Parse(pars[2].Value.ToString());
                Response.Write(string.Format("<script language = javascript>alert('成功导入{0}条，重复{1}条，错误{4}条。白名单存在{2}条已删除，例外名单存在{3}条已删除。');window.location = 'BlackList.aspx';</script>", a1.ToString(), a2.ToString(), a3.ToString(), a4.ToString(), err));
               

 

三、线程的问题就不写了，总监说了用ODP，上面的批量导入和线程就没用了。在B/S项目最好不要用线程，会出现很多问题。

以前5万条数据需要半小时，用批量导入加存储过程后不到20分钟。目前项目用的是ODP，一分钟不到就搞定！

不过在写ODP的时候也出现了一些问题，毕竟刚接触。下一篇文章中会写一下关于ODP的内容。