C#调用oracle存储过程

OGeek|极客世界-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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;