一個讓我的年沒有過好的問題﹐我會永遠記得它

1，数据库处理代码：
 public string UpdateLastClass(int data_id, int grp_id, string ch_name, string data_no, string data_nm, string data_eng_nm, string user_create_mk, int upd_id)
 { string s="";
 OracleParameter [] arParms = new OracleParameter[9]; arParms[0] = new OracleParameter("sl_data_id", OracleType.Int32 , 8);
 arParms[0].Value = data_id; arParms[1] = new OracleParameter("sl_grp_id", OracleType.Int32 , 8);
 arParms[1].Value = grp_id; arParms[2] = new OracleParameter("sl_ch_name", OracleType.VarChar, 50);
 arParms[2].Value = ch_name; arParms[3] = new OracleParameter("sl_data_no", OracleType.VarChar, 10);
 arParms[3].Value = data_no; arParms[4] = new OracleParameter("sl_data_nm", OracleType.VarChar, 200);
 arParms[4].Value = data_nm; arParms[5] = new OracleParameter("sl_data_eng_nm", OracleType.VarChar, 200);
 arParms[5].Value = data_eng_nm; arParms[6] = new OracleParameter("sl_user_create_mk", OracleType.Char, 1);
 arParms[6].Value = user_create_mk[0];

 arParms[7] = new OracleParameter("sl_upd_id", OracleType.Int32, 5);
 arParms[7].Value = upd_id; arParms[8] = new OracleParameter("errmsg", OracleType.VarChar, 100);
 arParms[8].Value = System.DBNull.Value;
 arParms[8].Direction = ParameterDirection.Output; CommandType commandType = CommandType.StoredProcedure;
 string commandText = "PS_UPDATE_LAST_CLASSOK"; try
 {
 DBTools db = new DBTools();
 db.ExecuteNonQuery(commandType, commandText, arParms);
 s=arParms[8].Value.ToString();

 }
 catch(OracleException ex)
 {
 s += ex.Message;
 s += "\n code----------"+ex.Source;
 s += "\n code----------"+ex.Code;
 s += "\n innerException----------"+ex.InnerException;
 s += "\n stackTrace----------"+ex.StackTrace;
 s += "\n targetSite----------"+ ex.TargetSite;
 //throw ex;
 }
 return s;

2，数据库 Procedure
create or replace procedure PS_UPDATE_LAST_CLASSOK(
 sl_data_id IN prod_rule_data.data_id%TYPE,
 sl_grp_id IN prod_rule_data.grp_id%TYPE,
 sl_ch_name IN prod_rule_data.ch_name%TYPE,
 sl_data_no IN prod_rule_data.data_no%TYPE,
 sl_data_nm IN prod_rule_data.data_nm%TYPE,
 sl_data_eng_nm IN prod_rule_data.data_eng_nm%TYPE,
 sl_user_create_mk IN prod_rule_data.user_create_mk%TYPE,
 sl_int_id IN prod_rule_data.int_id%TYPE,
 errmsg OUT VARCHAR2
)
AS
--定義游標start
 cursor cu_grp(p_no VARCHAR2) is
 SELECT grp_id
 From prod_class_setting
 where set_groupno=p_no;
--定義游標end
 chk_data NUMBER;
 no_grp_id EXCEPTION;--EXCEPTION是申明錯誤類型變量;
 no_ch_name EXCEPTION;
 no_data_no EXCEPTION;
 no_data_nm EXCEPTION;
 no_int_id EXCEPTION;
 B_data_no prod_rule_data.data_no%TYPE
 B_data_nm prod_rule_data.data_nm%TYPE;
 B_data_eng_nm prod_rule_data.data_eng_nm%TYPE; v_grp_id prod_rule_data.grp_id%TYPE;
 v_data_id prod_rule_data.data_id%TYPE;
 set_no VARCHAR2(4);
BEGIN
 errmsg := ''; --Check grp_id
 IF sl_grp_id IS NULL THEN RAISE no_grp_id;
 END IF;
 --Check ch_name
 IF sl_ch_name IS NULL THEN RAISE no_ch_name;
 END IF;
 --Check data_no
 IF sl_data_no IS NULL THEN RAISE no_data_no;
 END IF;
 --Check data_nm
 IF sl_data_nm IS NULL THEN RAISE no_data_nm;
 END IF;
 --Check int_id
 IF sl_int_id IS NULL THEN RAISE no_int_id;
 END IF;
 SELECT data_no,data_nm,data_eng_nm INTO B_data_no,B_data_nm,B_data_eng_nm FROM prod_rule_data WHERE data_id = sl_data_id;
 COMMIT;
 BEGIN
 chk_data := 0;
 Select count(*) into chk_data from prod_class_setting where grp_id = sl_grp_id and ch_name = sl_ch_name;
 IF chk_data > 0 THEN
 Select distinct set_groupno into set_no from prod_class_setting where grp_id = sl_grp_id and ch_name = sl_ch_name;
 --在prod_class_setting找到記錄,表示有關聯!
 --根據set_no取出所有關聯的grp_id,循環,保存.
 for lr_grp in cu_grp(set_no) Loop
 v_grp_id := lr_grp.grp_id;
 --找出data_id(依中類grp_id,小類ch_name,小細類data_no data_nm data_eng_nm完全相符的)
 --將該data_id對應的修改前的小細類資料Insert到歷史記錄表Prod_rule_data_history.
 chk_data := 0;
 SELECT count(*) INTO chk_data FROM prod_rule_data
 WHERE grp_id = v_grp_id and ch_name=sl_ch_name
 and (RTRIM(data_no)=RTRIM(B_data_no) or RTRIM(data_nm)=RTRIM(B_data_nm) or RTRIM(data_eng_nm)=RTRIM(B_data_eng_nm))
 and act_mk='Y'; --如果找不到該資料則新增
 IF chk_data<1 THEN
 LOCK TABLE prod_rule_data IN EXCLUSIVE MODE;
 Insert into prod_rule_data(
 data_id,data_no,data_nm,data_eng_nm,
 grp_id,ch_name,int_id,upd_id,
 create_date,update_date,
 user_create_mk,set_groupno
 ) VALUES(
 sq_data_id.NEXTVAL,
 sl_data_no,sl_data_nm,sl_data_eng_nm,
 v_grp_id,sl_ch_name,sl_int_id,
 sl_int_id,SYSDATE,SYSDATE,
 sl_user_create_mk,set_no
 );
 END IF; v_data_id := 0;
 IF chk_data>1 THEN
 SELECT min(data_id) INTO v_data_id
 FROM prod_rule_data
 WHERE grp_id = v_grp_id and ch_name=sl_ch_name
 and (RTRIM(data_no)=RTRIM(B_data_no)
 or RTRIM(data_nm)=RTRIM(B_data_nm)
 or RTRIM(data_eng_nm)=RTRIM(B_data_eng_nm))
 and act_mk='Y';
 END IF;
 IF chk_data=1 THEN
 SELECT data_id INTO v_data_id FROM prod_rule_data
 WHERE grp_id = v_grp_id and ch_name=sl_ch_name
 and (RTRIM(data_no)=RTRIM(B_data_no)
 or RTRIM(data_nm)=RTRIM(B_data_nm)
 or RTRIM(data_eng_nm)=RTRIM(B_data_eng_nm))
 and act_mk='Y';
 End IF;

IF v_data_id >0 THEN
                     --Insert:Prod_rule_data_history
                     LOCK TABLE prod_rule_data_history IN EXCLUSIVE MODE;
                     Insert into prod_rule_data_history(
                              data_id,
                              data_no,
                              data_nm,
                              data_eng_nm,
                              grp_id,
                              ch_name,
                              act_mk,
                              int_id,
                              upd_id,
                              create_date,
                              update_date,
                              user_create_mk,
                              his_updid,
                              his_upddate,
                              his_fromact,
                              his_fromdataid,
                              set_groupno
                     )
                     SELECT
                              data_id,
                              data_no,
                              data_nm,
                              data_eng_nm,
                              grp_id,
                              ch_name,
                              act_mk,
                              int_id,
                              upd_id,
                              create_date,
                              update_date,
                              user_create_mk,
                              sl_int_id,
                              SYSDATE,
                              'M',
                              sl_data_id,
                              set_groupno
                    FROM prod_rule_data WHERE data_id = v_data_id;
                 END IF;                 --修改Update: prod_rule_data
                  LOCK TABLE prod_rule_data IN EXCLUSIVE MODE;
                  UPDATE prod_rule_data
                      SET data_no = sl_data_no,
                          data_nm = sl_data_nm,
                          data_eng_nm = sl_data_eng_nm,
                          user_create_mk = sl_user_create_mk,
                          upd_id  = sl_int_id,
                          update_date= SYSDATE
                       WHERE data_id= v_data_id;             END LOOP;
         ELSE
            --在prod_class_setting中找不到記錄,表示沒有關聯資料! 直接執行修改.
                  --將該data_id對應的修改前的小細類資料Insert到歷史記錄表Prod_rule_data_history.
                     LOCK TABLE prod_rule_data_history IN EXCLUSIVE MODE;
                     Insert into prod_rule_data_history(
                              data_id,
                              data_no,
                              data_nm,
                              data_eng_nm,
                              grp_id,
                              ch_name,
                              act_mk,
                              int_id,
                              upd_id,
                              create_date,
                              update_date,
                              user_create_mk,
                              his_updid,
                              his_upddate,
                              his_fromact,
                              his_fromdataid
                     )
                     SELECT
                              data_id,
                              data_no,
                              data_nm,
                              data_eng_nm,
                              grp_id,
                              ch_name,
                              act_mk,
                              int_id,
                              upd_id,
                              create_date,
                              update_date,
                              user_create_mk,
                              sl_int_id,
                              SYSDATE,
                              'M',
                              sl_data_id
                    FROM prod_rule_data WHERE data_id = sl_data_id;
                  --執行修改語句
                  LOCK TABLE prod_rule_data IN EXCLUSIVE MODE;
                  UPDATE prod_rule_data
                      SET data_no = sl_data_no,
                          data_nm = sl_data_nm,
                          data_eng_nm = sl_data_eng_nm,
                          user_create_mk = sl_user_create_mk,
                          upd_id  = sl_int_id,
                          update_date= SYSDATE
                       WHERE data_id= sl_data_id;
         END IF;         COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            errmsg := '更新資料發生錯誤！';
    END;EXCEPTION
    WHEN no_grp_id THEN
        errmsg := '中類別ID未輸入！';    WHEN no_ch_name THEN
        errmsg := '小類別名稱不能為空！';    WHEN no_data_no THEN
        errmsg := '小細類別代號未輸入！';    WHEN no_data_nm THEN
        errmsg := '小細類別中文名稱未輸入！';    WHEN no_int_id THEN
        errmsg := '輸入人員資料不能留空！';    --WHEN OTHERS THEN
    --    errmsg := '發生其他不明錯誤終止！';
end PS_UPDATE_LAST_CLASSOK;

3,出现的异常
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PS_UPDATE_LAST_CLASSOK' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
code----------System.Data.OracleClient
code----------6550
innerException----------
stackTrace---------- at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList& refCursorParameterOrdinals) at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciHandle& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at ItemCoding.DBTools.ExecuteNonQuery(CommandType commandType, String commandText, OracleParameter[] commandParameters) at ItemCoding.LastClassOK.UpdateLastClass(Int32 data_id, Int32 grp_id, String ch_name, String data_no, String data_nm, String data_eng_nm, String user_create_mk, Int32 upd_id)
targetSite----------Void CheckError(System.Data.OracleClient.OciErrorHandle, Int32)

异常信息：wrong number or types of arguments in call to 'PS_UPDATE_LAST_CLASSOK' ORA-06550
就是说调用存储过程时参数个数或参数类型错误，请仔细检查UpdateLastClass中的存储过程参数的定义，是否与存储过程完全一致。（你的存储过程中的类别用的是%type，因此我无法帮你检查了）

問題搞定﹐發現是
arParms[6] = new OracleParameter("sl_user_create_mk", OracleType.Char, 1);
arParms[6].Value = user_create_mk;
前面可以傳遞一個空值進來﹐但是在這里不能接受此值﹐修改為下面的代碼
arParms[6] = new OracleParameter("sl_user_create_mk", OracleType.VarChar);
if(user_create_mk!="")
{
arParms[6].Value = user_create_mk;
}
else
{
arParms[6].Value = System.DBNull.Value;
}
謝謝大家﹗

调试易

一個讓我的年沒有過好的問題﹐我會永遠記得它

解决方案 »