数据库编程与C#编程互译

墨蓝 2021-12-24 04:01 231阅读 0赞

今天有一段代码，先是用程序实现。

闲来无聊，又用存储过程实现了一次。

程序中实现。

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

/// <summary>
            /// 根据区域和用户名获取可访问的国家
            /// </summary>                        
            public DataTable GetCountry(string area,string user)
            {
                try
                {
                    NHibernate.Engine.ISessionImplementor FactoryImpl = FrameWork.Repository.NHb.SessionBuilder.CreateSession().GetSessionImplementation();
                    OracleCommand cmd = new OracleCommand();
    
                    string strWhere1 = "1=1 ";
                    string strWhere2 = "1=1 ";
    
                    if (!String.IsNullOrEmpty(user))
                    {
                        strWhere1 += String.Format( "and vrs.u_logname = '{0}'",user);
                    }
    
                    if (!String.IsNullOrEmpty(area))
                    {
                        strWhere2 += String.Format("and cc.sales_area = '{0}'", area);
                    }
                    string sqlstr =String.Format( @"select distinct scg.customer_country,cc.sales_area,cc.countrye,cc.countryc
                                      from ifce.vw_rsh_user              vru,
                                           ele_bill.sap_customer_general scg,
                                           shipuser.countryce            cc
                                     where vru.sap_cust_no = scg.customer_number
                                       and scg.customer_country = cc.short_e
                                       and vru.u_loginid in
                                           (select column_value countryId
                                              from table(splitstr_fun((select vrs.u_loginid || ',' ||
                                                                             vrs.agent_user_id
                                                                        from ifce.vw_rsh_user vrs
                                                                       where {0}),
                                                                      ',')))
                                       and {1}", strWhere1, strWhere2);
    
                    cmd.Parameters.Clear();
    
                    OracleConnection conn = (OracleConnection)FactoryImpl.Connection;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = sqlstr;
                    cmd.ExecuteNonQuery();
    
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    System.Data.DataSet ds = new System.Data.DataSet();
                    da.Fill(ds);
                    return ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw new FrameWork.THrException("Fail!", e);
                }
            }

存储过程中实现。

/*******************************************************/
      /* 根据区域和用户名获取可访问的国家                    */
      /* create bby wufei 2013 11 26                         */
      /*******************************************************/
    procedure Query_Country(p_area varchar2,
                           p_user varchar2,
                           x_cur  out cur_forlog) is
      strWhere1 varchar2(100);
      strWhere2 varchar2(100);
        sqlstr   varchar2(1000);
    begin
    
      strWhere1 := '1=1 ';
      strWhere2 := '1=1 ';
    
     
          if (p_user is not null) then
          strWhere1 := strWhere1 || ' and vrs.u_logname = ''''||''' || p_user || '''||''''';
        end if;
      
    /*  if p_area is not null then
        strWhere2 := strWhere2 || 'and cc.sales_area =''' || p_area '''';
      end if;*/
      
      sqlstr:='select distinct scg.customer_country,
                        cc.sales_area,
                        cc.countrye,
                        cc.countryc
          from ifce.vw_rsh_user              vru,
               ele_bill.sap_customer_general scg,
               shipuser.countryce            cc
         where vru.sap_cust_no = scg.customer_number
           and scg.customer_country = cc.short_e
           and vru.u_loginid in (select column_value countryId
                                   from table(splitstr_fun((select vrs.u_loginid || '','' ||
                                                                  vrs.agent_user_id
                                                             from ifce.vw_rsh_user vrs
                                                            where ' || strWhere1 ||'),
                                                           '','')))
           and '|| strWhere2 ;
    dbms_output.put_line(sqlstr);
      open x_cur for
       sqlstr;
    
    exception
      when others then  
         null;
    end Query_Country;

其中存储过程中还是有一些亮点的，比如将将查询table化，比如splitstr\_fun。

转载于:https://www.cnblogs.com/ushou/p/3444069.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211223/594454d24842496ba26286f5c9abfba1.png