请求会C#和oracle技术的达人诊断诊断merge into的问题

kerry_cheng 2009-03-20 12:02:06

我在C#中想执行一段Merge into的语句，但提示"不是所有的参数都已绑定"。



        public int Upd_SyokunikuList(string I_UserId, string I_AcsKey, string I_Kacd, string I_Kocd, string I_Klcd, DataSet I_Ds, out string O_Msg, out DataSet O_Ds)

        {

            int intResult = ComConst.FAILED;

            O_Ds = new DataSet();

            O_Msg = string.Empty;                       

            //SQL String

            StringBuilder strSQL = new StringBuilder();

            strSQL.Remove(0, strSQL.Length);

            strSQL.Append("MERGE INTO ZNTSMP");

            strSQL.Append(" USING (SELECT ? AS KACD, ? AS KOCD, ? AS NKDT, ? AS SKNO FROM DUAL) CK");

            strSQL.Append(" ON (SMKACD = KACD AND SMKOCD = KOCD AND SMNKDT = NKDT AND SMSKNO = SKNO)");

            //++++++++++更新++++++++++

            strSQL.Append(" WHEN MATCHED THEN");

            strSQL.Append(" UPDATE SET");

            strSQL.Append("  SMSKMT = ?");

            strSQL.Append(", SMDLCD = ?");

            strSQL.Append(", SMHBMT = ?"); 

            strSQL.Append(", SMHNNM = ?");

            strSQL.Append(", SMKOSU = ?");

            strSQL.Append(", SMNISG = ?"); 

            strSQL.Append(", SMJURY = ?"); 

            strSQL.Append(", SMTANI = ?"); 

            strSQL.Append(", SMBRND = ?");

            strSQL.Append(", SMESTN = ?");

            strSQL.Append(", SMKLDT = ?"); 

            strSQL.Append(", SMSMKG = ?");

            strSQL.Append(", SMBIKO = ?"); 

            strSQL.Append(",SMUPDT = (SELECT CURRENT_TIMESTAMP FROM DUAL)");    //更新日時

            strSQL.Append(",SMUPGM = 'afa010'");            //更新プログラムID

            strSQL.Append(",SMUPID = ?");                   //更新ユーザID

            strSQL.Append(",SMUPCD = ?");                   //更新ユーザ会社CD

            //++++++++++追加++++++++++

            strSQL.Append(" WHEN NOT MATCHED THEN");

            strSQL.Append(" INSERT (");

            strSQL.Append("  SMKACD"); 

            strSQL.Append(", SMKOCD");

            strSQL.Append(", SMNKDT");

            strSQL.Append(", SMSKNO");

            strSQL.Append(", SMSKMT");

            strSQL.Append(", SMDLCD");

            strSQL.Append(", SMHBMT"); 

            strSQL.Append(", SMHNNM");

            strSQL.Append(", SMKOSU");

            strSQL.Append(", SMNISG");

            strSQL.Append(", SMJURY");

            strSQL.Append(", SMTANI");

            strSQL.Append(", SMBRND");

            strSQL.Append(", SMESTN"); 

            strSQL.Append(", SMKLDT");

            strSQL.Append(", SMSMKG");

            strSQL.Append(", SMBIKO");

            strSQL.Append(", SMCRDT");  //登録日時

            strSQL.Append(", SMCRGM");  //登録プログラムID

            strSQL.Append(", SMCRID");  //登録ユーザID

            strSQL.Append(", SMCRCD");  //登録ユーザ会社CD

            strSQL.Append(")");

            strSQL.Append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?");

            strSQL.Append(",(SELECT CURRENT_TIMESTAMP FROM DUAL)");    //登録日時

            strSQL.Append(",'afa010'"); //登録プログラムID

            strSQL.Append(",?, ?)");

            //チェック＆更新

            ComDB db = new ComDB();

            try

            {

                //トランザクション開始

                db.DbBeginTrans();

                bool blnErr = false;

                int rtn = 0;



                //更新

                foreach (DataRow dr in I_Ds.Tables[0].Rows)

                {

                    db.DbParametersClear();

                    //conditions

                    db.DbPsetString("KACD", I_Kacd);                                

                    db.DbPsetString("KOCD", I_Kocd);                                

                    db.DbPsetInt("NKDT", int.Parse(dr["NKDT"].ToString()));         

                    db.DbPsetString("SKNO", dr["SKNO"].ToString());                 

                    //update values

                    db.DbPsetString("SMSKMT",dr["SKMT"].ToString());                

                    db.DbPsetString("SMDLCD",dr["DLCD"].ToString());                

                    db.DbPsetString("SMHBMT",dr["HBMT"].ToString());                

                    db.DbPsetString("SMHNNM",dr["HNNM"].ToString());                

                    db.DbPsetInt("SMKOSU",int.Parse(dr["KOSU"].ToString()));        

                    db.DbPsetString("SMNISG",dr["NISG"].ToString());                

                    db.DbPsetDouble("SMJURY",double.Parse(dr["JURY"].ToString()));  

                    db.DbPsetString("SMTANI",dr["TANI"].ToString());                

                    db.DbPsetString("SMBRND",dr["BRND"].ToString());                

                    db.DbPsetString("SMESTN",dr["ESTN"].ToString());                

                    db.DbPsetInt("SMKLDT",int.Parse(dr["KLDT"].ToString()));        

                    db.DbPsetInt("SMSMKG",int.Parse(dr["SMKG"].ToString()));        

                    db.DbPsetString("SMBIKO",dr["BIKO"].ToString());                

                    db.DbPsetString("SMUPID",I_UserId);                             //更新ユーザID

                    db.DbPsetString("SMUPCD",I_Kacd);                               //更新ユーザ会社CD

                    //insert values

                    db.DbPsetString("SMKACD", I_Kacd);                              

                    db.DbPsetString("SMKOCD", I_Kocd);                              

                    db.DbPsetInt("SMNKDT", int.Parse(dr["NKDT"].ToString()));       

                    db.DbPsetString("SMSKNO", dr["SKNO"].ToString());               

                    db.DbPsetString("SMSKMT", dr["SKMT"].ToString());               

                    db.DbPsetString("SMDLCD", dr["DLCD"].ToString());               

                    db.DbPsetString("SMHBMT", dr["HBMT"].ToString());               

                    db.DbPsetString("SMHNNM", dr["HNNM"].ToString());               

                    db.DbPsetInt("SMKOSU", int.Parse(dr["KOSU"].ToString()));       

                    db.DbPsetString("SMNISG", dr["NISG"].ToString());               

                    db.DbPsetDouble("SMJURY", double.Parse(dr["JURY"].ToString())); 

                    db.DbPsetString("SMTANI", dr["TANI"].ToString());               

                    db.DbPsetString("SMBRND", dr["BRND"].ToString());               

                    db.DbPsetString("SMESTN", dr["ESTN"].ToString());               

                    db.DbPsetInt("SMKLDT", int.Parse(dr["KLDT"].ToString()));       

                    db.DbPsetInt("SMSMKG", int.Parse(dr["SMKG"].ToString()));       

                    db.DbPsetString("SMBIKO", dr["BIKO"].ToString());               

                    db.DbPsetString("SMCRID", I_UserId);                            //登録ユーザID

                    db.DbPsetString("SMCRCD", I_Kacd);                              //登録ユーザ会社CD

                    rtn = db.DbExecute(strSQL.ToString());

                    if (rtn == ComConst.FAILED)

                    {

                        O_Msg = db.expmsg;

                        db.DbRollback();

                        return intResult;

                    }

                }



                //コミット

                if (blnErr == false)

                {

                    db.DbCommit();

                    O_Msg = "正常に更新しました。";

                    intResult = ComConst.SUCCEED;

                }

                else

                {

                    O_Ds = I_Ds;

                    O_Msg = "エラーがあります。更新は中止しました。";

                    intResult = ComConst.CHECK_ERROR;

                    db.DbRollback();

                }

            }

            catch (Exception ex)

            {

                O_Msg = ex.Message;

            }

            finally

            {

                if (db.State() == ConnectionState.Open)

                    db.DbClose();

            }

            return intResult;

        }