C#呼叫 oracle儲存過程

浪花一朵朵發表於2013-08-08
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();
            }

        }

儲存過程如下：

 1 create or replace procedure PLM_ECMS_CheckPartType(pid  in nvarchar2,
 2                                                    parttype out integer) as
 3 
 4   intNum integer := 0;
 5   rc_id  varchar2(36) := '1dda4a6a-c633-4c63-bc1b-74efbb5b01e1'; --總部通用物料
 6 
 7 begin
 8 
 9   --用在經緯許可權判斷JW 根據傳入的物料id 來判斷頂級物料是不是產品通用物料 如果是返回1 不是返回0
10     parttype := 0;
11     select count(1) into intNum from Plm_Ecms_Rule rc where rc.r_id = pid;
12     if intNum >= 1 then
13       --是規則
14       intNum := 0;
15       select count(1)
16         into intNum
17         from Plm_Ecms_Rclass r
18        where r.rc_id in
19              (select r.rc_id
20                 from Plm_Ecms_Rclass r
21                start with r.rc_id = (select rc.rc_id
22                                        from Plm_Ecms_Rule rc
23                                       where rc.r_id = pid)
24               connect by r.rc_id = prior r.rc_pid)
25          and r.rc_id = rc_id;
26       if intNum >= 1 then
27         --是總部集中管控物料
28         parttype := 1;
29       end if;
30       --是型別
31     else
32       select count(1) into intNum
33         from Plm_Ecms_Rclass rc
34        where rc.rc_id in (select r.rc_id
35                             from Plm_Ecms_Rclass r
36                            start with r.rc_id = pid
37                           connect by r.rc_id = prior r.rc_pid)
38          and rc.rc_id = '1dda4a6a-c633-4c63-bc1b-74efbb5b01e1';
39     
40       if intNum >= 1 then
41         --是總部集中管控物料
42         parttype := 1;
43       end if;
44     
45     end if;
46   
47   end PLM_ECMS_CheckPartType;

 

相關文章