SQL转Orcal

1.程序过不去的地方先将SQL在PL/SQL中执行一遍，看是否正确
2.数据集中字段名改为大写，如
   CITYCODEcmb.DataSource=myds.Tables[0];
   CITYCODEcmb.ValueMember="AREACODE";//"areacode";
   CITYCODEcmb.DisplayMember="CITYNAME";//"cityname";

3.新增一条记录最后保存（subSaveDB方法），SQL语句增加ID（原最大值+1），如
 int iNextCode = myconn.GetMaxItemCode("EAER_GASSTATION","ID")+1;//取得新增记录的ID，GetMaxItemCode定义在comm/DBConncls类中
 Sqlstr = "Insert Into EAER_GASSTATION(ID,AreaCode, OwnerName, OwnerCode, OwnerPrincipal,……）；//SQL语句中增加ID及其值

4.删除记录SQL语句分开写，如
 string delsql="delete from eaer_RADIATOR   where COMPANYCODE="+ID+" ;delete from EAER_RADIATORCRISES  where companycode="+ID;
 delsql +=";delete from EAER_RADIATORCRISES  where COMPANYCODE="+ID;//在SQL Server中可以执行，Oracle中不行
改为：  
 string strDelSql="delete from EAER_GASSTATION where ID=" + strStationID;
 errstr=conn.ExecuteSQL(strDelSql);//执行操作1
 strDelSql="delete from EAER_GasStationObsWell where GS_ID=" + strStationID;
 errstr=errstr + conn.ExecuteSQL(strDelSql);//执行操作2
5.有些带条件SQL语句@符号改为：符号，
 //string LocalSql="Select a.*,b.GraphCode gcode,b.showname gname From EAER_GASSTATION a left join EAER_COMPANYGRAPHICS b on a.GraphCode = b.GraphCode Where a.ID=@ID";
 //string[,] Exp=new string[1,3] {{"ID",strStationID,"numberic"}};
 string LocalSql="Select a.*,b.GraphCode gcode,b.showname gname From EAER_GASSTATION a left join EAER_COMPANYGRAPHICS b on a.GraphCode = b.GraphCode Where a.ID=:ID";
 string[,] Exp=new string[1,3] {{"ID",strStationID,"int"}};
6.有些SQL自带函数在SqlServer中可以执行，在Oracle中不能执行,如日期格式化函数
 SqlServer: Convert(char(12),A.BIRTHDAY,111) AS BIRTHDAY 
 Oracle:    TO_DATE(A.BIRTHDAY,'YYYY-MM-DD') AS BIRTHDAY

7.有些SQL函数中汉字单引号变双引号，如 Then '辐射防护专家小组' 改为 \"辐射防护专家小组\"
 myds=myconn.GetDataSet("select distinct a.EXPERTCODE as 序号, c.cityname as 区县,a.username as 姓名,(case when a.sex=0 then \"男\" else \"女\" end) as 性别,a.SPECIALTY as 专业,COMPANYNAME as 所在单位,CALLINGCODE as 单位电话,MPHONECODE as 移动电话,Case A.SpecType When 0 Then \"水污染专家小组\" When 1 Then \"危险品专家小组\" When 2 Then \"辐射防护专家小组\" When 3 Then \"防生(毒理医学)专家小组\" When 4 Then \"消防专家小组\" Else \"\" End \"所属专家小组\" "+
 "from eaer_expert a left join eaer_citycode b on a.statecode=b.statecode left join eaer_citycode c on a.citycode=c.citycode " +QuerySelect);

8.Oracle中没有top,取前N条记录 select * from table where rownum <  N  
 //string strGetAccident = "SELECT top " + sNums + " A.*,B.CITYNAME AS 行政区域 FROM EAER_ACCIDENT A,EAER_CITYCODE B  WHERE A.AREACODE = B.AREACODE AND A.ENDTIME IS NULL AND A.RECTARGET <> 3 ORDER BY A.BEGINTIME DESC";
 string strGetAccident = "SELECT A.*,B.CITYNAME AS 行政区域 FROM EAER_ACCIDENT A,EAER_CITYCODE B WHERE A.AREACODE = B.AREACODE AND A.ENDTIME IS NULL AND A.RECTARGET <> 3 AND rownum<"+ sNums + " ORDER BY A.BEGINTIME DESC";

9.Oracle中字符串连接用||
 //strSearchSQL = "SELECT A.*,B.STATENAME+B.CITYNAME AS 行政区域 FROM EAER_HISTORY A Inner JOIN EAER_CITYCODE B ON a.Areacode = b.AreaCode ";
 strSearchSQL = "SELECT A.*,B.STATENAME||B.CITYNAME AS 行政区域 FROM EAER_HISTORY A Inner JOIN EAER_CITYCODE B ON a.Areacode = b.AreaCode ";

 

 

来看一下，我在从SQLSERVER2000 导数据到ORACLE9i中遇到的问题以及解决的方法

1、ORACLE 字符集不匹配
 字符类型不正确，注意导入的字符类型即可
2、ORA-01461: 仅可以为插入LONG 列的LONG 值赋值
 是因为导数据的长度varchar2类型只能识别2000字符长度.需要调整一下数据库设置即可
3、ora-24801: 在OCI lob 函数中非法的参数值
 从ntext类型导入到CLOB类型的时候可能会出现的问题，造成的原因是由于ntext的字段时空的
4、ORA 标识中缺少双引号
 这是因为从SQLSERVER导到ORACLE中的表名长度的问题引起的，解决的方法是创建一个短一点名字的表，创建一个同义词命名为原来的表名即可

另外

还有一些常用的语法

oracle 中的字符串连接符是 ||, sql server,sybase中是 +.
oracle 中取uuid 的方法是select sys_guid() from dual ; sql server 中是 select newId();
oracle 中取头n行数据是 select ... from ... where ... and ROWNUM < N; sql server 是用 select top n ... from ... where ...
oracle 中取当前时间是用select sysdate from dual;sql server是用select getdate();
oracle 中自动增一的方法是create sequence seq_object_name,然后每次使用时,调用 seq_object_name.nextval; sql server 中是直接创建带identity类型的字段，不用再去管他.
oracle 中创建一个临时表 select a.* from (select * from table) a, sql server是 select a.* from (select * from table) as a

 

to_date( '" + obj.BEGINTIME + "','yyyy-mm-dd hh24:mi:ss')
Convert.ToDateTime(objTable.Rows[0]["BEGINTIME"].ToString()).ToString("yyyy-MM-dd");

////////////////////去重复//////////////
string strGetCRISES = "SELECT A.*,B.* FROM (select distinct * from EAER_CRISESTABLE) A,EAER_ITEM B WHERE B.ACCIDENTCODE = " + intACCIDENTCODE;
/////////////加id////////////////////
Data.oraDataRead odr = new oraDataRead();
int intCdcode = odr.getSqlInt("SELECT MAX(ID) FROM EAER_PROCESS")+1;

///////////////////去方括号///////////////////
select [SOURCECODE],[SHOWNAME],[FILETYPE],[FILEBODY] from EAER_ACCESSORIES where ID = " + strID;