Oracle 异常--工作中出现的

① ORA-00936 缺失表达式

实例--错误的情况

 1         /// <summary>
 2         /// 获取城市信息结果集
 3         /// </summary>
 4         /// <returns></returns>
 5         public DataSet GetCityDS(CityModel cityModel)
 6         {
 7             string sql = @"select t1.CITY_ID,
 8                                   t1.CITY_NAME,
 9                                   t1.PRO_ID,(select t2.PRO_NAME from T_BASE_PROVINCE t2 where t2.PRO_ID = t1.PRO_ID) PRO_NAME
10                            from T_BASE_CITY t1
11                            where 1 = 1
12                         ";
13             if ((!"".Equals(cityModel.CITY_ID)) && (cityModel.CITY_ID != null))
14             {
15                 sql += " and t1.CITY_ID like '%" + cityModel.CITY_ID + "%'";
16             }
17             if ((!"".Equals(cityModel.CITY_ID_NEW)) && (cityModel.CITY_ID_NEW != null))
18             {
19                 sql += " and t1.CITY_ID like '%" + cityModel.CITY_ID_NEW + "%'";
20             }
21             if ((!"".Equals(cityModel.CITY_NAME)) && (cityModel.CITY_NAME != null))
22             {
23                 sql += " and t1.CITY_NAME like '%" + cityModel.CITY_NAME + "%'";
24             }
25             return OracleHelper.ExecuteDataset(CommandType.Text,sql);
26         }

把编译的sql放到Developer 没问题能正确查出数据，但是报ORA-00936 缺失表达式，下面是正确的代码

实例--正确的实例

 1         /// <summary>
 2         /// 获取城市信息结果集
 3         /// </summary>
 4         /// <returns></returns>
 5         public DataSet GetCityDS(CityModel cityModel)
 6         {
 7             string sql = @"select t1.CITY_ID,t1.CITY_NAME,t1.PRO_ID,(select t2.PRO_NAME from T_BASE_PROVINCE t2 where t2.PRO_ID = t1.PRO_ID) PRO_NAME
 8                            from T_BASE_CITY t1
 9                            where 1 = 1
10                         ";
11             if ((!"".Equals(cityModel.CITY_ID)) && (cityModel.CITY_ID != null))
12             {
13                 sql += " and t1.CITY_ID like '%" + cityModel.CITY_ID + "%'";
14             }
15             if ((!"".Equals(cityModel.CITY_ID_NEW)) && (cityModel.CITY_ID_NEW != null))
16             {
17                 sql += " and t1.CITY_ID like '%" + cityModel.CITY_ID_NEW + "%'";
18             }
19             if ((!"".Equals(cityModel.CITY_NAME)) && (cityModel.CITY_NAME != null))
20             {
21                 sql += " and t1.CITY_NAME like '%" + cityModel.CITY_NAME + "%'";
22             }
23             return OracleHelper.ExecuteDataset(CommandType.Text,sql);
24         }

总结：竖着写有可能出错，可能有中文之类的空格符，建议先紧紧横着写，然后再转换成竖写的形式。

 

posted @ 2012-12-25 17:03  杨斌_济南  阅读(678)  评论(0)  编辑  收藏  举报