ORACLE与SQL脚本区别.doc

ORACLE与SQL脚本区别1、 在SQL中，SQL参数前面用,Oracle参数前面使用:。2、 字段对应。SQLOracle大文本类型NtextNClob二进制数据ImageBLOB3、 在Sql中，多个sql语句之间不需要使用符号分开，在Oracle中，语句之间需要用;分开，前面需要加begin 后面需要加end;（如：begin 语句1; 语句2;end;）4、 自增长字段处理.在Sql中有自增长字段，在Oracel中没有自增长字段，我们需要建立一个序列（Sequence），命名方式：sq_+对应当数据表名（如：sq_DBMail_AllMails）。使用比较：Sql中：INSERT INTO DBMail_AllMails(MailGuid,FromUserGuid,FromDispName,ToUserGuidList,ToUserDispNameList,Subject,MailContent,OwnerUserGuid,SendTime,mailTypeName,mailTypeId,deleteFlag,HasAttach,Importance,BoxTypeGuid,OutMailGuid) VALUES(MailGuid,FromUserGuid,FromDispName,ToUserGuidList,ToUserDispNameList,Subject,MailContent,OwnerUserGuid,SendTime,mailTypeName,mailTypeId,deleteFlag,HasAttach,Importance,BoxTypeGuid,OutMailGuid)Oracle中：INSERT INTO DBMail_AllMails(Row_ID,MailGuid,FromUserGuid,FromDispName,ToUserGuidList,ToUserDispNameList,Subject,MailContent,OwnerUserGuid,SendTime,mailTypeName,mailTypeId,deleteFlag,HasAttach,Importance,BoxTypeGuid,OutMailGuid) VALUES(sq_DBMail_AllMails.nextval,:MailGuid,:FromUserGuid,:FromDispName,:ToUserGuidList,:ToUserDispNameList,:Subject,:MailContent,:OwnerUserGuid,:SendTime,:mailTypeName,:mailTypeId,:deleteFlag,:HasAttach,:Importance,:BoxTypeGuid,:OutMailGuid)。如果需要返回值，参考方法：public int Insert(string ResourceName) Database db = DatabaseFactory.CreateDatabase(Conn.GetConnectionString(OAMisc_ConnectionString); string strSql = (db.DbProviderFactory.ToString() != System.Data.OracleClient.OracleClientFactory) ? INSERT INTO Misc_Schedule_Resource(ResourceName) VALUES(ResourceName) SELECT ResourceID=SCOPE_IDENTITY() : begin select sq_Misc_Schedule_Resource.Nextval into :ResourceID from dual; INSERT INTO Misc_Schedule_Resource(ResourceID,ResourceName) VALUES(:ResourceID,:ResourceName);end; ); DbCommand cmd = db.GetSqlStringCommand(strSql); db.AddInParameter(cmd, ResourceName, DbType.String, ResourceName); db.AddOutParameter(cmd, ResourceID, DbType.Int32, 0); db.ExecuteNonQuery(cmd); int returnId = Convert.ToInt32(db.GetParameterValue(cmd, ResourceID); return returnId; 5、 Sql中不区分大小写，Oracle中区分的，用lower() 转换成小写（如：SELECT UserGuid FROM Frame_User WHERE lower(LoginID)=:LoginID and lower(password)=:password）6、 Oracle中对Clob字段的处理。不能使用微软的企业库方式。例子：OracleConnection myConn = new OracleConnection(Conn.GetConnectionString(Email_ConnectionString); string strSql = UPDATE Email_Message SET FolderGuid=:FolderGuid,Subject=:Subject,MailSize=:MailSize,ReTo=:ReTo,ReCc=:ReCc,ReBcc=:ReBcc,HeadersText=:HeadersText,FromName=:FromName,FromEmail=:FromEmail,SendDate=:SendDate,Body=:Body,HtmlBody=:HtmlBody,MessageId=:MessageId,Priority=:Priority,HasAttach=:HasAttach WHERE MailGuid=:MailGuid ; OracleCommand myCommand = new OracleCommand(strSql, myConn); myCommand.Parameters.Add(new OracleParameter(FolderGuid, OracleType.NVarChar).Value = FolderGuid; myCommand.Parameters.Add(new OracleParameter(Subject, OracleType.NVarChar).Value = Subject; myCommand.Parameters.Add(new OracleParameter(MailSize, OracleType.Int32).Value = MailSize; myCommand.Parameters.Add(new OracleParameter(ReTo, OracleType.NClob).Value = ReTo; myCommand.Parameters.Add(new OracleParameter(ReCc, OracleType.NClob).Value = ReCc; myCommand.Parameters.Add(new OracleParameter(ReBcc, OracleType.NClob).Value = ReBcc; myCommand.Parameters.Add(new OracleParameter(HeadersText, OracleType.NClob).Value = HeadersText; myCommand.Parameters.Add(new OracleParameter(FromName, OracleType.NVarChar).Value = FromName; myCommand.Parameters.Add(new OracleParameter(FromEmail, OracleType.NVarChar).Value = FromEmail; myCommand.Parameters.Add(new OracleParameter(SendDate, OracleType.DateTime).Value = SendDate; myCommand.Parameters.Add(new OracleParameter(Body, OracleType.NClob).Value = Body; myCommand.Parameters.Add(new OracleParameter(HtmlBody, OracleType.NClob).Value = HtmlBody; myCommand.Parameters.Add(new OracleParameter(MessageId, OracleType.NVarChar).Value = MessageId; myCommand.Parameters.Add(new OracleParameter(Priority, OracleType.NVarChar).Value = Priority; myCommand.Parameters.Add(new OracleParameter(HasAttach, OracleType.Int32).Value = HasAttach; myCommand.Parameters.Add(new OracleParameter(MailGuid, OracleType.NVarChar).Value = MailGuid; myConn.Open(); myCommand.ExecuteNonQuery(); myConn.Close();7、 Oracle中没有Top方法，必须使用企业库中提供的Conn.Generate_Top_Select_Sql方法。例子：public DataView GetNewsMailTop(string OwnerUserGuid, string BoxTypeGuid, int Rows) Database db = DatabaseFactory.CreateDatabase(Conn.GetConnectionStringName(DBMail_ConnectionString); string strSql = (db.DbProviderFactory.ToString() != System.Data.OracleClient.OracleClientFactory) ? select top + Rows.ToString() + MailGuid ,Subject,FromDispName,Row_ID,SendTime from DBMail_AllMails where OwnerUserGuid = + OwnerUserGuid + and deleteFlag=0 and BoxTypeGuid= + BoxTypeGuid + and haveread = 0 order by Row_ID desc : Conn.Generate_Top_Select_Sql( select MailGuid ,Subject,FromDispName,Row_ID,SendTime from DBMail_AllMails where OwnerUserGuid = + OwnerUserGuid + and deleteFlag=0 and BoxTypeGuid= + BoxTypeGuid + and haveread = 0 order by Row_ID desc ,Rows) ); DbCommand cmd = db.GetSqlStringCommand(strSql); return db.ExecuteDataView(cmd); 8、 函数前面不需要添加dbosql中：select distinct dbo.getYearno(wdate) as YearNum from Frame_WorkingDay : oracle中： select distinct getYearno(wdate) as YearNum from Frame_WorkingDay 9、 Oracle中的一些字符串函数：SqlOracleCharIndexINSTRSubStringSUBSTR转换为日期TO_DATE(+fromdate+,YYYY- MM-DD HH24:MI:SS)转换成字符串To_CHAR是否为NULLIsNULLnvl取得当前时间Getdate()sysdate转换为数字to_num