《ORACLE与SQL脚本区别》由会员分享，可在线阅读，更多相关《ORACLE与SQL脚本区别（4页珍藏版）》请在金锄头文库上搜索。
1、ORACLE 与 SQL 脚本区别1、 在 SQL 中，SQL 参数前面用,Oracle 参数前面使用:。2、 字段对应。SQL Oracle大文本类型 Ntext NClob二进制数据 Image BLOB3、 在 Sql 中，多个 sql 语句之间不需要使用符号分开，在 Oracle 中，语句之间需要用;分开，前面需要加 begin 后面需要加 end;（如：begin 语句 1; 语句 2;end;）4、 自增长字段处理.在 Sql 中有自增长字段，在 Oracel 中没有自增长字段，我们需要建立一个序列（Sequence） ，命名方式：sq_+对应当数据表名（如：sq_DBMail_A
2、llMails） 。使用比较：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,T
3、oUserDispNameList,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,mai
4、lTypeName,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,:BoxTy
5、peGuid,: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
6、(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.A
7、ddInParameter(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_Use
8、r 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,Re
9、To=: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);myComm
10、and.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
11、 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).Valu
12、e = 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;myC
13、ommand.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 OracleP
14、arameter(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
15、、 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() != S
16、ystem.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,SendTim
