C#呼叫 oracle儲存過程

C#呼叫oracle 儲存過程與呼叫Sql server儲存過程類似，比較簡單：直接給出示例：

  /// <summary>
        /// 判斷物料型別是不是總部管控
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        /// <summary>
        public bool IsHeadquartersPart(string key)
        {
            isGroupPart = false;
            OracleCommand cmd = new OracleCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = (OracleConnection)this.dbParam.Connection;
            cmd.CommandText = "PLM_ECMS_CheckPartType";

            OracleParameter[] parameters = {
                    new OracleParameter(":pid", OracleDbType.Varchar2),
                    new OracleParameter(":parttype", OracleDbType.Int32)
                 };
            parameters[0].Value = key;
            parameters[0].Direction = ParameterDirection.Input;

            parameters[1].Direction = ParameterDirection.Output;

            for (int i = 0; i < parameters.Length; i++)
            {
                cmd.Parameters.Add(parameters[i]);
            }

            cmd.Prepare();

            try
            {
                int k = 0;
                cmd.ExecuteNonQuery();
                bool temp = int.TryParse(parameters[1].Value.ToString(), out k);//返回程式碼；0表示成功，非0表示不成功  

                return temp ? (k == 1 ? true : false) : false;
            }
            catch (Exception ex)
            {
                LogHelper.CreateErrorLogTxt("IsHeadquartersPart", ex.Message, cmd.CommandText);
                return false;
            }
            finally
            {
                cmd.Dispose();
            }

        }

儲存過程如下：

create or replace procedure PLM_ECMS_CheckPartType(pid  in nvarchar2,
                                                   parttype out integer) as

  intNum integer := 0;
  rc_id  varchar2(36) := '1dda4a6a-c633-4c63-bc1b-74efbb5b01e1'; --總部通用物料

begin

  --用在經緯許可權判斷JW 根據傳入的物料id 來判斷頂級物料是不是產品通用物料 如果是返回1 不是返回0
    parttype := 0;
    select count(1) into intNum from Plm_Ecms_Rule rc where rc.r_id = pid;
    if intNum >= 1 then
      --是規則
      intNum := 0;
      select count(1)
        into intNum
        from Plm_Ecms_Rclass r
       where r.rc_id in
             (select r.rc_id
                from Plm_Ecms_Rclass r
               start with r.rc_id = (select rc.rc_id
                                       from Plm_Ecms_Rule rc
                                      where rc.r_id = pid)
              connect by r.rc_id = prior r.rc_pid)
         and r.rc_id = rc_id;
      if intNum >= 1 then
        --是總部集中管控物料
        parttype := 1;
      end if;
      --是型別
    else
      select count(1) into intNum
        from Plm_Ecms_Rclass rc
       where rc.rc_id in (select r.rc_id
                            from Plm_Ecms_Rclass r
                           start with r.rc_id = pid
                          connect by r.rc_id = prior r.rc_pid)
         and rc.rc_id = '1dda4a6a-c633-4c63-bc1b-74efbb5b01e1';
    
      if intNum >= 1 then
        --是總部集中管控物料
        parttype := 1;
      end if;
    
    end if;
  
  end PLM_ECMS_CheckPartType;