捕风之巢

SQL与Oracle对比

001 、 SQL 与 ORACLE 的内存分配
ORACLE 的内存分配大部分是由 INIT.ORA 来决定的，一个数据库实例可以有 N 种分配方案，不同的应用（ OLTP 、 OLAP ）它的配置是有侧重的。 SQL 概括起来说，只有两种内存分配方式：动态内存分配与静态内存分配，动态内存分配充许 SQL 自己调整需要的内存，静态内存分配限制了 SQL 对内存的使用。
002 、 SQL 与 ORACLE 的物理结构
    总得讲，它们的物理结构很相似， SQL 的数据库相当于 ORACLE 的模式（方案）， SQL 的文件组相当于 ORACLE 的表空间，作用都是均衡 DISK I/O ， SQL 创建表时，可以指定表在不同的文件组， ORACLE 则可以指定不同的表空间。
 CREATE TABLE A001 （ ID DECIMAL （ 8 ， 0 ）） ON [ 文件组 ]
    --------------------------------------------------------------------------------------------
 CREATE TABLE A001 （ ID NUMBER （ 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 的事务隔离
SET TRANSACTION ISOLATION LEVEL
SQL 有四种事务隔离级别：
READ COMMITTED 、 READ UNCOMMITTED 、 REPEATABLE READ 、 SERIALIZABLE
ORACLE 有两种事务隔离级别
READ COMMITTED 、 SERIALIZABLE
SQL 虽然有四种事务隔离，事务之间还是经常发生阻塞； ORACLE 则利用回退段很好地实现了事务隔离，不会产生阻塞。 SQL 与 ORACLE 如果发生死锁，都可以很快地识别并将之处理掉。
007 SQL 与 ORACLE 的外键约束
SQL 的外键约束可以实现级联删除与级联更新， ORACLE 则只充许级联删除。
CREATE TABLE A001 （ ID INT PRIMARY KEY ， NAME VARCHAR （ 20 ））
CREATE TABLE A002 （ ID INT REFERENCES A001 （ ID ） ON DELETE CASCADE ON UPDATE CASCADE ， AGE TINYINT ）
CREATE TABLE A001 （ ID INT PRIMAY KEY ， NAME VARCHAR2 （ 20 ））
CREATE TABLE A002 （ ID INT REFERENCES A001 （ ID ） ON DELETE CASCADE ， AGE NUMBER （ 2 ， 0 ））
008 、 SQL 与 ORACLE 的临时表
SQL 的临时表用 # 或 ## 开头，使用完后自动释放， ORACLE 的临时表则存在数据库中，每个会话的数据都互不干涉。 oracle 临时表中的纪录可以被定义为自动删除（分 commit 方式和 transaction 方式），而表结构不会被自动删除。临时表的 DML ， DDL 操作和标准表一样。
CREATE TABLE #TEMP （ ID INT ， NAME VARCHAR （ 20 ））
-------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE TEMP （ ID INT ， VARCHAR2 （ 20 ））
009 、 SQL 与 ORACLE 的类型转换
SQL 常用类型转换函数有： CAST 、 CONVERT 、 STR
ORACLE 常用类型转换函数有： TO_CHAR 、 TO_NUMBER 、 TO_DATE
SELECT CONVERT （ VARCHAR （ 20 ）， GETDATE （）， 112 ）
------------------------------------------------------------------------------------------------
SELECT TO_CHAR （ SYSDATE ，‘ YYYYMMDD ’） FROM DUAL
010 、 SQL 与 ORACLE 的自动编号
SQL 的编号一般由 IDENTITY 字段来提供，可以灵活地设定种子值，增量，取值范围有 BIGINT 、 INT 、 SMALLINT 、 TINYINT 、 DEIMAL 等； ORACLE 的编号一般由 SEQUENCE 来提供，由 NEXTVAL 与 CURVAL 函数从 SEQUENCES 取值。
CREATE TABLE A003 （ ID INT IDENTITY （ -9999 ， 9 ）， NAME VARCHAR （ 20 ））
-------------------------------------------------------------------------------------------------------
CREATE SEQUENCE SEQ_001 START 9999 INCREMENT BY 9
CREATE TABLE A004 （ ID INT ）
INSERT INTO A004 VALUES （ SEQ_001.NEXTVAL ）
INSERT INTO A004 VALUES （ SEQ_001.CURVAL+1 ）
011 、 SQL 与 ORACLE 的分区表
  从严格意思上来讲， SQL 还没有分区表，它的分区表是以 UNION 为基础，将多个结果集串起来，实际上是视图； ORACLE 的分区表有多种： PARTITION BY RANGE 、 PARTITION BY HASH 、 PARTITION BY LIST ，其它就是混合分区，以上三种基础分区的混合使用。当然 ORACLE 也可以象 SQL 那样分区视图。
CREATE TABLE A1999 （ ID INT ， NAME VARCHAR （ 20 ））
CREATE TABLE A2000 （ ID INT ， NAME VARCHAR （ 20 ））
CREATE VIEW V_PART AS
SELECT * FROM A1999 UNION SELECT * FROM A2000
--------------------------------------------------
CREATE TABLE A_PART1(ID INT,NAME VARCHAR2(20))
PARTITON BY RANGE(ID)(
PARTITION P1 VALUES LESS THEN (2000000) PATITION P2 VALUES LESS THEN (MAXVALUE))
CREATE TABLE A_PART2(ID INT,NAME VARCHAR2(20))
PARTITION BY HASH(ID) PARTITIONS 2 STORE IN (USERS01,USERS02)
CREATE TABLE A_PART3(ID INT,NAME VARCHAR2(20))
PARTITION BY LIST(ID)(
PARTIION P1 VALUES( ‘ 01 ’ , ’ 03 ’ , ’ 05 ’ ) PARTITON P2 VALUES( ‘ 02 ’ , ’ 04 ’ ))
012 、 SQL 与 ORACLE 的存储过程
SQL 的存储过程可以很方便地返回结果集， ORACLE 的存储过程只有通过游标类型返回结果集，这种结果集 ADO 不可识别，如果想使用 ORACLE 存储过程的结果集，只有使用 ODAC 开发包（ DELPHI/BCB 控件组 www.51delphi.com 与 www.playicq.com 有下载）， SQL 的过程参数如果是字符必须指定参数长度， ORACLE 的过程则不充许指定字符参数的长度。
CREATE PROCEDURE UP_001(@ID INT) AS
BEGIN
SELECT ID ,SUM(QTY) FROM A_TEST WHERE ID=@ID GROUP BY @ID
END
------------------------------------------------------------
CREATE OR REPLACE PACKAGE UP_002 AS
TYPE MYCURSOR IS REF CURSOR ；
FUNCTION GETRECORD RETURN MYCURSOR ；
END ；
CEEATE OR REPLACE PACKAGE BODY UP_002 AS
    FUNCTION GETRECORD RETURN MYCURSOR AS
      MC MYCURSOR ；
   SL VARCHAR2 （ 999 ）；
    BEGIN
    OPEN MC FOR SELECT * FROM A001 ；
    RETURN MC ；
    END ；
END ；
ORACLE 的存储函数返回结果这么艰难，但 SQL 的触发器竟然也充许返回结果集就令人费解啦，触发器的调试比较麻烦，在 SQL 实际开发中，一般都将触发器要执行的代码放到过程中进行调试，在查询分析器中可以对过程进行设断点调试。
013 、 SQL 与 ORACLE 的触发器
触发器的事务是引起触发动作事务的延续，在 SQL 的触发器中是可以无 BEGIN TRAN 而可以直接 COMMIT TRAN 的。 SQL 的触发器是表级触发器， DML 影响一行或无数行触发动作只触发一次， ORACLE 分表级触发器与行级触发器，触发的粒度更细腻一些， SQL 在处理多行时就必须使用 CURSOR 啦。 ORACLE 使用 INSERTING 、 DELTING 、 UPDATING 判断执行了什么 DML 操作， SQL 只有判断 INSERTED 、 DELETED 的记录数来判断执行了什么操作，只有 INSERTED 映象表记录数大于 0 表示 INSERT ，只有 DELETED 映象表记录数大于 0 表示 DELETE ，若两个表记录数都大于 0 表示 UPDATE 。
用 SQL 的触发器实现级联添加、级联删除、级联更新
CREATE TABLE A1 （ ID INT ， NAME VARCHAR （ 20 ））
CREATE TABLE A2 （ ID INT ， NAME VARCHAR （ 20 ））
CREATE TRIGGER TRI_A1_INS ON A1
FOR INSERT , DELETE , UPDATE AS BEGIN
  DECLARE @I INT,@D INT,@ID INT
  SELECT @I=COUNT(*) FROM INSERTED
  SELECT @D=COUNT(*) FROM DELETED
  --IF (@I>0 AND @D>0) 执行更新，由于用到游标，故略去
  IF @I>0
     INSERT INTO A2 SELECT * FROM INSERTED
  IF @D>0
     DELETE FROM A2 WHERE ID=@ID
END
----------------------------------------------------------------------
用 ORACLE 的触发器实现级联添加、级联删除、级联更新
CREATE OR REPLACE TRI_A1_INS
AFTER INSERT OR DELETE OR UPDATE ON A1
FOR EACH ROW
BEGIN
 IF INSERTING THEN
   INSERT INTO A2 SELECT * FROM ： NEW ；
    END IF ；
 IF DELETING THEN
   DELETE FROM A2 WHERE ID = ： OLD.ID ；
    END IF ；
 IF UPDATING THEN
   UPATE A2 SET ID = ： NEW.ID , NAME =   ： NEW.NAME WHERE ID = ： OLD.ID ；
    END IF ；
END
014 、 SQL 与 ORACLE 的游标
SQL 的游标用 @@FETCH_STATUS 判断是否还有数据， ORACLE 的游标用 %FOUND 、 %NOTFOUND 来判断游标是否结束，游标分服务端游标与客户端游标，在存储过程、函数、触发器中声明的游标为服务端游标，其它处声明的游标为客户端游标，游标需要使用较多的内存，但它比临时表的性能要优一些，因为临时表占用的是 DISK I/O ， DISK I/O 应该比服务器的内存要更珍贵一些吧。
015 、 SQL 与 ORACLE 的重复记录删除
好的数据库设计重复记录是不存在的，如果有重复记录如何删除呢？ SQL 可以使用 SET ROWCOUNT N 设置客户端缓冲区的记录来删除， ORACLE 可以使用 ROWID 来进行，都必须进行一些简单的编程， SQL 可以做用过程，更通用一些， ORACLE 如果想做得通过不太容易，字段小些会更方便处理一些。
DECLARE @M INT
SELECT @M=COUNT(*) FROM A_TEST WHERE ID=X
SELECT @M=@M-1
SET ROWCOUNT @M    -- 限制客户端缓冲区的记录数
DELETE FROM A_TEST WHERE ID=X
SET ROWCOUNT 0     -- 取消限制
说明 删除某条记录的重复值，如果想清除表的所有重值要使用游标，取得所有的 X
---------------------------------------------------------------------
DELETE FROM A_TEST A WHERE ROWID!=(SELECT MAX(ROWID) FROM A_TEST B
   WHERE A.ID=B.ID AND A.NAME=B.NAME)
说明 当数据量较大时，这种方法将会使用系统大量的资源
016 SQL 与 ORACLE 的对象加密
SQL 与 ORACLE 的某些对象如过程、视图、函数、触发器可能涉及商业，开发商通常希望对这些对象进行加密， SQL 的加密方法在创建时带上 WITH ENCRYPTION ， ORACLE 的对象加密明显复杂一些，要使用 WRAP 工具，在 ORACLE 的 BIN 目录内。
017 SQL 与 ORACLE 的表生成 SQL 语句
SQL 与 ORACLE 的表如何才导成 SQL 语句呢？如果一定要编程实现， SQL 需要将其它数据类型的字段转换成 VARCHAR 类型， ORACLE 则可以隐式进行数据类型转换。
CREATE TABLE A_SQL(ID INT,NAME VARCHAR(20)) –假如有两万记录
SELECT ‘ INSERT INTO A_SQL VALUES( ‘ +CAST(ID
AS VARCHAR(20))+ ’ , ’’’ +NAME+ ’’’ ) ’ FROM A_SQL
----------------------------------------------------------------
SELECT ‘ INSERT INTO A_SQL VALUES( ‘ ||ID|| ’ , ’ || ’’’ ||NAME|| ’’’ || ’ ) ’
 FROM A_SQL
说明 SQL 的字符串连接用 + 号， ORACLE 字符串连接用 || ，单引号可以做转义符。
018 、 SQL 与 ORACLE 的动态 SQL
SQL 与 ORACLE 都支持动态 SQL 语句， SQL 用 EXEC （）执行的动态 SQL 语句， ORACLE 用 EXECUTE IMMEDIATE 执行动态 SQL 。动态 SQL 的效率要比非动态 SQL 性能差，但使用起来非常灵活，可以根据不同条件执行不同的任务。
DECLARE @SQL VARCHAR(99)
SELECT @SQL= ’ declare @m int select @m=count(*) from sysobjects select @m ’
EXEC(@SQL)
--------------------------------------------
DECLARE
S VARCHAR2(99);
BEGIN
S:='SELECT COUNT(*)  FROM '||' USER_TABLES';
EXECUTE IMMEDIATE S;
END;
19 、返回记录集中前 N 条记录的语法？
SQL 只有使用 TOP ， ORACLE 可以使用 ROWNUM
SELECT TOP N * FROM 记录集（表，视图，子查询）
---------------------------------------------
SELECT * FROM 记录集 WHERE ROWNUM<=N
20 如何返回记录集中相临两记录之间某字段的差值？
CREATE TABLE A001(ID INT,QTY INT)
INSERT INTO A001 VALUES(1,20)
INSERT INTO A001 VALUES(4,10)
SELECT IDENTITY(INT,1,1) CODE,QTY INTO #X FROM A001
SELECT B.QTY-A.QTY FROM #X A,#X B WHERE A.CODE=B.CODE-1
DROP TABLE #X
--------------------------------------------------------
CREATE TABLE A002 （ ID INT ）
INSERT INTO A002 VALUES （ 1 ）
INSERT INTO A002 VALUES （ 9 ）
WITH A AS (SELECT ROWNUM RN,ID FROM A002)
SELECT A2.ID-A1.ID FROM A A2,A A1 WHERE A2.RN=A1.RN-1
说明 虽然语法大不相同，但最大的特点是两者都使用了自连接技术。
21 如何返回任意某个范围之间的记录集？
CREATE TABLE A03(ID INT)
DECLARE @I INT
SELECT @I=1
WHILE @I<1000 BEGIN
INSERT INTO A03 VALUES(@I)
  SELECT @I=@I+1
END
-- 前部分是创建环境，后一部分是实现方法，比较牵强
SELECT IDENTITY(INT,1,1) CODE,ID INTO #X FROM A03
SELECT ID FROM #X WHERE CODE BETWEEN 10 AND 20
DROP TABLE #X
------------------------------------------------------
BEGIN
FOR I IN 1..999 LOOP
  INSERT INTO A03 VALUES(I);
END LOOP;
END;

SELECT * FROM A03 WHERE ROWNUM<20
MINUS
SELECT * FROM A03 WHERE ROWNUM<10;
说明 在数据提取方面， ORACLE 有 ROWID ， ROWNUM 使之有相当强的优势， SQL 只有使用函数 IDENTITY 来构建一个临时表，这样来说还不好使用 CURSOR 来性能会好一些。通过这个例子，大家还可以看出 SQL 与 ORACLE 的程序结构， ORACLE 更严谨、人性化一些。
22 、表 A04 中的含有 A 、 B 、 C 、 D 四个字段，当按 A 字段分组后，如果 D 等 1 ，则只统计 B 的值，如果 D 等 0, 则只统计 C 的值。
CREATE TABLE A04(A VARCHAR(20),B INT,C INT,D INT)
INSERT INTO A04 VALUES('01',20,7,'0')
INSERT INTO A04 VALUES('01',10,8,'1')
INSERT INTO A04 VALUES('02',20,7,'1')
INSERT INTO A04 VALUES('02',10,8,'0')
SELECT A,SUM(CASE D WHEN 1 THEN B WHEN 0 THEN C END) FROM A04 GROUP BY A
---------------------------------------------------------------
SELECT A,SUM(DECODE(D,1,B,0,C)) FROM A04 GROUP BY A
说明 ORACLE 9I 可以使用 CASE 语句来实现这种操作，但也可以用 DECODE 来作统计，使用 CASE 比 DECODE 提供了更为强大的功能，但 DECODE 的语法显然比 CASE WHEN THEN END 要简洁得多。
23 、如何删除数据库所有表？（ ORACLE 则是删除模式所有表）
DECLARE @SQL VARCHAR(99),@TBL VARCHAR(30),@FK VARCHAR(30)
DECLARE CUR_FK CURSOR LOCAL FOR
   SELECT OBJECT_NAME(CONSTID),OBJECT_NAME(FKEYID) FROM SYSREFERENCES
-- 删除所有外键
OPEN CUR_FK
FETCH CUR_FK INTO @FK,@TBL
WHILE @@FETCH_STATUS =0
BEGIN
SELECT @SQL='ALTER TABLE '+@TBL+' DROP CONSTRAINT '+@FK
  EXEC(@SQL)
  --SELECT @SQL='DROP TABLE '+@TBL
  FETCH CUR_FK INTO @FK,@TBL
END
CLOSE CUR_FK
DECLARE CUR_FKS CURSOR LOCAL FOR
   SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN CUR_FKS
FETCH CUR_FKS INTO @TBL
WHILE @@FETCH_STATUS =0
BEGIN
   SELECT @SQL='DROP TABLE ['+@TBL+']'
   EXEC(@SQL)
   FETCH CUR_FKS INTO @TBL
END
CLOSE CUR_FKS  
----------------------------------------------------------------
DECLARE
S VARCHAR2(99);
CURSOR CUR_F IS SELE