SQL与ORACLE比较.docx
- 文档编号:5102916
- 上传时间:2022-12-13
- 格式:DOCX
- 页数:48
- 大小:40.06KB
SQL与ORACLE比较.docx
《SQL与ORACLE比较.docx》由会员分享,可在线阅读,更多相关《SQL与ORACLE比较.docx(48页珍藏版)》请在冰豆网上搜索。
SQL与ORACLE比较
KeyLife富翁笔记
作者 :
gkrong
标题 :
SQL与Oracle对比
关键字:
分类 :
开发经验
密级 :
公开
(评分:
回复:
0,阅读:
8896) »»
作者:
wzmbox
转自;
网上已经有很多SQL与ORACLE的对比,但本人要讲的即不是单纯的SQL,也不是单纯的ORACLE,更不是评价谁好谁坏(意思不大),而是两种数据库之相同和异同,本人曾讲授过SQL与ORACLE的课程,讲SQL时说SQL好,讲ORACLE时又说ORACLE棒,现在终于可以平心而评啦。
估计有人现在会嘿嘿冷笑(又一个误人子弟的骗子),老实说,当初每次讲完课,就有这种感觉—教的人不得其法,学的人不得其道。
说点严肃的事吧,据说比尔与艾里森在洗手间相遇,两个又是拥抱,又是KISS,不多久就吵了起来,比尔对查询分析器(SQLQUERYANALYZE)赞不经绝口,艾里森嘿嘿冷笑,只说了一句话—SQLPLUS内秀。
言归正传,且听我一一道来
001、SQL与ORACLE的内存分配
ORACLE的内存分配大部分是由INIT.ORA来决定的,一个数据库实例可以有N种分配方案,不同的应用(OLTP、OLAP)它的配置是有侧重的。
SQL概括起来说,只有两种内存分配方式:
动态内存分配与静态内存分配,动态内存分配充许SQL自己调整需要的内存,静态内存分配限制了SQL对内存的使用。
002、SQL与ORACLE的物理结构
总得讲,它们的物理结构很相似,SQL的数据库相当于ORACLE的模式(方案),SQL的文件组相当于ORACLE的表空间,作用都是均衡DISKI/O,SQL创建表时,可以指定表在不同的文件组,ORACLE则可以指定不同的表空间。
CREATETABLEA001(IDDECIMAL(8,0))ON[文件组]
--------------------------------------------------------------------------------------------
CREATETABLEA001(IDNUMBER(8,0))TABLESPACE表空间
注:
以后所有示例,先SQL,后ORACLE
003、SQL与ORACLE的日志模式
SQL对日志的控制有三种恢复模型:
SIMPLE、FULL、BULK-LOGGED;ORACLE对日志的控制有二种模式:
NOARCHIVELOG、ARCHIVELOG。
SQL的SIMPLE相当于ORACLE的NOARCHIVELOG,FULL相当于ARCHIVELOG,BULK-LOGGED相当于ORACLE大批量数据装载时的NOLOGGING。
经常有网友抱怨SQL的日志庞大无比且没法处理,最简单的办法就是先切换到SIMPLE模式,收缩数据库后再切换到FULL,记住切换到FULL之后要马上做完全备份。
004、SQL与ORACLE的备份类型
SQL的备份类型分的极杂:
完全备份、增量备份、日志备份、文件或文件组备份;ORACLE的备份类型就清淅多啦:
物理备份、逻辑备份;ORACLE的逻辑备份(EXP)相当于SQL的完全备份与增量备份,ORACLE的物理备份相当于SQL的文件与文件组备份。
SQL的各种备份都密切相关,以完全备份为基础,配合其它的备份方式,就可以灵活地备分数据;ORACLE的物理备份与逻辑备份各司其职。
SQL可以有多个日志,相当于ORACLE日志组,ORACLE的日志自动切换并归档,SQL的日志不停地膨胀……SQL有附加数据库,可以将数据库很方便地移到别一个服务器,ORACLE有可传输表空间,可操作性就得注意啦。
005、SQL与ORACLE的恢复类型
SQL有完全恢复与基于时间点的不完全恢复;ORACLE有完全恢复与不完全恢复,不完全恢复有三种方式:
基于取消的、基于时间的、基于修改的(SCN)的恢复。
不完全恢复可以恢复数据到某个稳定的状态点。
006、SQL与ORACLE的事务隔离
SETTRANSACTIONISOLATIONLEVEL
SQL有四种事务隔离级别:
READCOMMITTED、READUNCOMMITTED、REPEATABLEREAD、SERIALIZABLE
ORACLE有两种事务隔离级别
READCOMMITTED、SERIALIZABLE
SQL虽然有四种事务隔离,事务之间还是经常发生阻塞;ORACLE则利用回退段很好地实现了事务隔离,不会产生阻塞。
SQL与ORACLE如果发生死锁,都可以很快地识别并将之处理掉。
007SQL与ORACLE的外键约束
SQL的外键约束可以实现级联删除与级联更新,ORACLE则只充许级联删除。
CREATETABLEA001(IDINTPRIMARYKEY,NAMEVARCHAR(20))
CREATETABLEA002(IDINTREFERENCESA001(ID)ONDELETECASCADEONUPDATECASCADE,AGETINYINT)
CREATETABLEA001(IDINTPRIMAYKEY,NAMEVARCHAR2(20))
CREATETABLEA002(IDINTREFERENCESA001(ID)ONDELETECASCADE,AGENUMBER(2,0))
008、SQL与ORACLE的临时表
SQL的临时表用#或##开头,使用完后自动释放,ORACLE的临时表则存在数据库中,每个会话的数据都互不干涉。
oracle临时表中的纪录可以被定义为自动删除(分commit方式和transaction方式),而表结构不会被自动删除。
临时表的DML,DDL操作和标准表一样。
CREATETABLE#TEMP(IDINT,NAMEVARCHAR(20))
-------------------------------------------------------
CREATEGLOBALTEMPORARYTABLETEMP(IDINT,VARCHAR2(20))
009、SQL与ORACLE的类型转换
SQL常用类型转换函数有:
CAST、CONVERT、STR
ORACLE常用类型转换函数有:
TO_CHAR、TO_NUMBER、TO_DATE
SELECTCONVERT(VARCHAR(20),GETDATE(),112)
------------------------------------------------------------------------------------------------
SELECTTO_CHAR(SYSDATE,‘YYYYMMDD’)FROMDUAL
010、SQL与ORACLE的自动编号
SQL的编号一般由IDENTITY字段来提供,可以灵活地设定种子值,增量,取值范围有BIGINT、INT、SMALLINT、TINYINT、DEIMAL等;ORACLE的编号一般由SEQUENCE来提供,由NEXTVAL与CURVAL函数从SEQUENCES取值。
CREATETABLEA003(IDINTIDENTITY(-9999,9),NAMEVARCHAR(20))
-------------------------------------------------------------------------------------------------------
CREATESEQUENCESEQ_001START9999INCREMENTBY9
CREATETABLEA004(IDINT)
INSERTINTOA004VALUES(SEQ_001.NEXTVAL)
INSERTINTOA004VALUES(SEQ_001.CURVAL+1)
011、SQL与ORACLE的分区表
从严格意思上来讲,SQL还没有分区表,它的分区表是以UNION为基础,将多个结果集串起来,实际上是视图;ORACLE的分区表有多种:
PARTITIONBYRANGE、PARTITIONBYHASH、PARTITIONBYLIST,其它就是混合分区,以上三种基础分区的混合使用。
当然ORACLE也可以象SQL那样分区视图。
CREATETABLEA1999(IDINT,NAMEVARCHAR(20))
CREATETABLEA2000(IDINT,NAMEVARCHAR(20))
CREATEVIEWV_PARTAS
SELECT*FROMA1999UNIONSELECT*FROMA2000
--------------------------------------------------
CREATETABLEA_PART1(IDINT,NAMEVARCHAR2(20))
PARTITONBYRANGE(ID)(
PARTITIONP1VALUESLESSTHEN(2000000)PATITIONP2VALUESLESSTHEN(MAXVALUE))
CREATETABLEA_PART2(IDINT,NAMEVARCHAR2(20))
PARTITIONBYHASH(ID)PARTITIONS2STOREIN(USERS01,USERS02)
CREATETABLEA_PART3(IDINT,NAMEVARCHAR2(20))
PARTITIONBYLIST(ID)(
PARTIIONP1VALUES(‘01’,’03’,’05’)PARTITONP2VALUES(‘02’,’04’))
012、SQL与ORACLE的存储过程
SQL的存储过程可以很方便地返回结果集,ORACLE的存储过程只有通过游标类型返回结果集,这种结果集ADO不可识别,如果想使用ORACLE存储过程的结果集,只有使用ODAC开发包(DELPHI/BCB控件组与有下载),SQL的过程参数如果是字符必须指定参数长度,ORACLE的过程则不充许指定字符参数的长度。
CREATEPROCEDUREUP_001(@IDINT)AS
BEGIN
SELECTID,SUM(QTY)FROMA_TESTWHEREID=@IDGROUPBY@ID
END
------------------------------------------------------------
CREATEORREPLACEPACKAGEUP_002AS
TYPEMYCURSORISREFCURSOR;
FUNCTIONGETRECORDRETURNMYCURSOR;
END;
CEEATEORREPLACEPACKAGEBODYUP_002AS
FUNCTIONGETRECORDRETURNMYCURSORAS
MCMYCURSOR;
SLVARCHAR2(999);
BEGIN
OPENMCFORSELECT*FROMA001;
RETURNMC;
END;
END;
ORACLE的存储函数返回结果这么艰难,但SQL的触发器竟然也充许返回结果集就令人费解啦,触发器的调试比较麻烦,在SQL实际开发中,一般都将触发器要执行的代码放到过程中进行调试,在查询分析器中可以对过程进行设断点调试。
013、SQL与ORACLE的触发器
触发器的事务是引起触发动作事务的延续,在SQL的触发器中是可以无BEGINTRAN而可以直接COMMITTRAN的。
SQL的触发器是表级触发器,DML影响一行或无数行触发动作只触发一次,ORACLE分表级触发器与行级触发器,触发的粒度更细腻一些,SQL在处理多行时就必须使用CURSOR啦。
ORACLE使用INSERTING、DELTING、UPDATING判断执行了什么DML操作,SQL只有判断INSERTED、DELETED的记录数来判断执行了什么操作,只有INSERTED映象表记录数大于0表示INSERT,只有DELETED映象表记录数大于0表示DELETE,若两个表记录数都大于0表示UPDATE。
用SQL的触发器实现级联添加、级联删除、级联更新
CREATETABLEA1(IDINT,NAMEVARCHAR(20))
CREATETABLEA2(IDINT,NAMEVARCHAR(20))
CREATETRIGGERTRI_A1_INSONA1
FORINSERT,DELETE,UPDATEASBEGIN
DECLARE@IINT,@DINT,@IDINT
SELECT@I=COUNT(*)FROMINSERTED
SELECT@D=COUNT(*)FROMDELETED
--IF(@I>0AND@D>0)执行更新,由于用到游标,故略去
IF@I>0
INSERTINTOA2SELECT*FROMINSERTED
IF@D>0
DELETEFROMA2WHEREID=@ID
END
----------------------------------------------------------------------
用ORACLE的触发器实现级联添加、级联删除、级联更新
CREATEORREPLACETRI_A1_INS
AFTERINSERTORDELETEORUPDATEONA1
FOREACHROW
BEGIN
IFINSERTINGTHEN
INSERTINTOA2SELECT*FROM:
NEW;
ENDIF;
IFDELETINGTHEN
DELETEFROMA2WHEREID=:
OLD.ID;
ENDIF;
IFUPDATINGTHEN
UPATEA2SETID=:
NEW.ID,NAME= :
NEW.NAMEWHEREID=:
OLD.ID;
ENDIF;
END
014、SQL与ORACLE的游标
SQL的游标用@@FETCH_STATUS判断是否还有数据,ORACLE的游标用%FOUND、%NOTFOUND来判断游标是否结束,游标分服务端游标与客户端游标,在存储过程、函数、触发器中声明的游标为服务端游标,其它处声明的游标为客户端游标,游标需要使用较多的内存,但它比临时表的性能要优一些,因为临时表占用的是DISKI/O,DISKI/O应该比服务器的内存要更珍贵一些吧。
015、SQL与ORACLE的重复记录删除
好的数据库设计重复记录是不存在的,如果有重复记录如何删除呢?
SQL可以使用SETROWCOUNTN设置客户端缓冲区的记录来删除,ORACLE可以使用ROWID来进行,都必须进行一些简单的编程,SQL可以做用过程,更通用一些,ORACLE如果想做得通过不太容易,字段小些会更方便处理一些。
DECLARE@MINT
SELECT@M=COUNT(*)FROMA_TESTWHEREID=X
SELECT@M=@M-1
SETROWCOUNT@M --限制客户端缓冲区的记录数
DELETEFROMA_TESTWHEREID=X
SETROWCOUNT0 --取消限制
说明删除某条记录的重复值,如果想清除表的所有重值要使用游标,取得所有的X
---------------------------------------------------------------------
DELETEFROMA_TESTAWHEREROWID!
=(SELECTMAX(ROWID)FROMA_TESTB
WHEREA.ID=B.IDANDA.NAME=B.NAME)
说明当数据量较大时,这种方法将会使用系统大量的资源
016SQL与ORACLE的对象加密
SQL与ORACLE的某些对象如过程、视图、函数、触发器可能涉及商业,开发商通常希望对这些对象进行加密,SQL的加密方法在创建时带上WITHENCRYPTION,ORACLE的对象加密明显复杂一些,要使用WRAP工具,在ORACLE的BIN目录内。
017SQL与ORACLE的表生成SQL语句
SQL与ORACLE的表如何才导成SQL语句呢?
如果一定要编程实现,SQL需要将其它数据类型的字段转换成VARCHAR类型,ORACLE则可以隐式进行数据类型转换。
CREATETABLEA_SQL(IDINT,NAMEVARCHAR(20))–假如有两万记录
SELECT‘INSERTINTOA_SQLVALUES(‘+CAST(ID
ASVARCHAR(20))+’,’’’+NAME+’’’)’FROMA_SQL
----------------------------------------------------------------
SELECT‘INSERTINTOA_SQLVALUES(‘||ID||’,’||’’’||NAME||’’’||’)’
FROMA_SQL
说明SQL的字符串连接用+号,ORACLE字符串连接用||,单引号可以做转义符。
018、SQL与ORACLE的动态SQL
SQL与ORACLE都支持动态SQL语句,SQL用EXEC()执行的动态SQL语句,ORACLE用EXECUTEIMMEDIATE执行动态SQL。
动态SQL的效率要比非动态SQL性能差,但使用起来非常灵活,可以根据不同条件执行不同的任务。
DECLARE@SQLVARCHAR(99)
SELECT@SQL=’declare@mintselect@m=count(*)fromsysobjectsselect@m’
EXEC(@SQL)
--------------------------------------------
DECLARE
SVARCHAR2(99);
BEGIN
S:
='SELECTCOUNT(*) FROM'||'USER_TABLES';
EXECUTEIMMEDIATES;
END;
19、返回记录集中前N条记录的语法?
SQL只有使用TOP,ORACLE可以使用ROWNUM
SELECTTOPN*FROM记录集(表,视图,子查询)
---------------------------------------------
SELECT*FROM记录集WHEREROWNUM<=N
20如何返回记录集中相临两记录之间某字段的差值?
CREATETABLEA001(IDINT,QTYINT)
INSERTINTOA001VALUES(1,20)
INSERTINTOA001VALUES(4,10)
SELECTIDENTITY(INT,1,1)CODE,QTYINTO#XFROMA001
SELECTB.QTY-A.QTYFROM#XA,#XBWHEREA.CODE=B.CODE-1
DROPTABLE#X
--------------------------------------------------------
CREATETABLEA002(IDINT)
INSERTINTOA002VALUES
(1)
INSERTINTOA002VALUES(9)
WITHAAS(SELECTROWNUMRN,IDFROMA002)
SELECTA2.ID-A1.IDFROMAA2,AA1WHEREA2.RN=A1.RN-1
说明虽然语法大不相同,但最大的特点是两者都使用了自连接技术。
21如何返回任意某个范围之间的记录集?
CREATETABLEA03(IDINT)
DECLARE@IINT
SELECT@I=1
WHILE@I<1000BEGIN
INSERTINTOA03VALUES(@I)
SELECT@I=@I+1
END
--前部分是创建环境,后一部分是实现方法,比较牵强
SELECTIDENTITY(INT,1,1)CODE,IDINTO#XFROMA03
SELECTIDFROM#XWHERECODEBETWEEN10AND20
DROPTABLE#X
------------------------------------------------------
BEGIN
FORIIN1..999LOOP
INSERTINTOA03VALUES(I);
ENDLOOP;
END;
SELECT*FROMA03WHEREROWNUM<20
MINUS
SELECT*FROMA03WHEREROWNUM<10;
说明在数据提取方面,ORACLE有ROWID,ROWNUM使之有相当强的优势,SQL只有使用函数IDENTITY来构建一个临时表,这样来说还不好使用CURSOR来性能会好一些。
通过这个例子,大家还可以看出SQL与ORACLE的程序结构,ORACLE更严谨、人性化一些。
22、表A04中的含有A、B、C、D四个字段,当按A字段分组后,如果D等1,则只统计B的值,如果D等0,则只统计C的值。
CREATETABLEA04(AVARCHAR(20),BINT,CINT,DINT)
INSERTINTOA04VALUES('01',20,7,'0')
INSERTINTOA04VALUES('01',10,8,'1')
INSERTINTOA04VALUES('02',20,7,'1')
INSERTINTOA04VALUES('02',10,8,'0')
SELECTA,SUM(CASEDWHEN1THENBWHEN0THENCEND)FROMA04GROUPBYA
---------------------------------------------------------------
SELECTA,SUM(DECODE(D,1,B,0,C))FROMA04GROUPBYA
说明ORACLE9I可以使用CASE语句来实现这种操作,但也可以用DECODE来作统计,使用CASE比DECODE提供了更为强大的功能,但DECODE的语法显然比CASEWHENTHENEND要简洁得多。
23、如何删除数据库所有表?
(ORACLE则是删除模式
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL ORACLE 比较
![提示](https://static.bdocx.com/images/bang_tan.gif)