SQL 与 ORACLE 的比较

最近参加了一个ORACLE的培训，ORACLE与平日惯用的SQL SERVER果然有很大不同，在网上搜索了一下转了这篇比较SQL与ORACLE的帖子，总体上感觉SQL SERVER使用更加便捷，人性化，在网上的资料支持很丰富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开发包 ，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 SELECT CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE='R';
CURSOR CUR_T IS SELECT TABLE_NAME FROM USER_TABLES;
BEGIN
FOR V IN CUR_F LOOP
S:='ALTER TABLE '||V.TABLE_NAME||' DROP CONSTRAINT '||V.CONSTRAINT_NAME;
EXECUTE IMMEDIATE S;
END LOOP;
FOR T IN CUR_T LOOP
S:='DROP TABLE '||T.TABLE_NAME;
EXECUTE IMMEDIATE S;
END LOOP;
END;
说明 SQL删除数据库时，用到了两个系统表：SYSREFERENCES、SYSOBJECTS，前一个可以获得所有外键键信息，后者可以获得所有表的信息，在删除表时还在表名上加了一对中括号，即使用表名含有空格键或其它特殊这符也可以顺利删除。
在ORACLE中，要删除模式的所有表，方法和SQL差不多，需要用到的数据字典也有两个：USER_CONSTRAINTS、USER_TABLES； USER_CONSTRAINTS中CONSTRAINT_TYPE值等于R表示是外键，同样也要用到CURSOR与动态SQL，这里提醒一下大家， FOR … LOOP内的变量变量是FOR … LOOP声明的，可以ORACLE的程序结构比SQL简洁。
24、如何统计数据库所有用户表的记录数（ORACLE统计模式内所有表的记录数）？
CREATE TABLE #TMP (QTY INT)
CREATE TABLE #TMP1 (TBL VARCHAR(30),QTY INT)
DECLARE @SQL VARCHAR(99),@TBL VARCHAR(30),@QTY INT
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='SELECT COUNT(*) FROM '+@TBL
INSERT INTO #TMP EXEC(@SQL)
SELECT @QTY=QTY FROM #TMP
INSERT INTO #TMP1 VALUES(@TBL,@QTY)
DELETE FROM #TMP
FETCH CUR_FKS INTO @TBL
END
CLOSE CUR_FKS
SELECT * FROM #TMP1
---------------------------------------------------------------
DESC DBMS_UTILITY
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
SELECT TABLE_NAME,NUM_ROWS FROM USER_TABLES;
说明 SQL的EXEC功能可谓十分强大，竟然可以和INSERT INTO合用，将结果集存入一个表中，MS可真牛。ORACLE就只好用个偷懒的方法，首先将要统计的模式进行统计分析，在数据字典中就记载了每个表的行数，ORACLE很简单吧。
25、SQL与ORACLE快速建表的方法？
SELECT * INTO 新表名称 FROM 子查询|表名
-----------------------------------------
CREATE TABLE 新表名称 AS 子查询
说明 快速建表可以有效地消除碎片，速度极快。
26、如何实现有一组有规则的编号（如200305310001…200305310999）?
DECLARE @I INT,@C VARCHAR(20)
SELECT @I=1
WHILE @I<1000 BEGIN
SELECT @C=CASE WHEN @I<10 THEN '000'+CAST(@I AS CHAR(1))
WHEN @I BETWEEN 10 AND 99 THEN '00'+CAST(@I AS CHAR(2))
WHEN @I BETWEEN 100 AND 999 THEN '0'+CAST(@I AS CHAR(3))
END
SELECT @C=CONVERT(VARCHAR(20),GETDATE(),112)+@C
SELECT @C --在查询分析器中输出
SELECT @I=@I+1
END
---------------------------------------------------------
DECLARE
C VARCHAR2(20);
BEGIN
FOR I IN 1 .. 999 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYYMMDD')||LPAD(I,4,'0'));
END LOOP;
END;
说明 虽然都可以实现，但ORACLE的LPAD果然身手不凡，可怜的MS还没有类似LPAD的函数，只有用CASE进行判断组合，真得很蠢，如果你有好的办法，请明示，甚至连循环结构，SQL稍也不慎，就死循环啦（如果注释掉加蓝显示那条语句的话）。
27、关于SQL与ORACLE的分布式结构
SQL在分布式方面做得不错，不仅提供了链接服务器的方式供初级用户使用，还提供了OPENDATASOURCE、OPENXML、OPENQUERY、 OPENROWSET等行集函数，可以方便地通过SQL语句从*.TXT、*.XLS、*.XML、*.MDB、*.DBF等介质获取数据，还可以从 ORACLE、DB2、SYBASE等数据库获取数据；ORACLE在同构数据库之间提供了DB LINK，异构数据库之间提供了透明网关软件。
28、现在有三个表，结构如下
Score(FScoreId 成绩记录号,FSubID 课程号,FStdID 学生号,FScore 成绩)
student:（FID 学生号，FName 姓名）
subject:（FSubID 课程号，FSubName 课程名）,
怎么能实现这个表：
姓名 英语 数学 语文 历史
张萨 78 67 89 76
王强 89 67 84 96
李三 70 87 92 56
李四 80 78 97 66
SELECT A.FNAME AS 姓名,
英语 = SUM(CASE B.FSUBNAME WHEN '英语' THEN C.FSCORE END),
数学 = SUM(CASE B.FSUBNAME WHEN '数学' THEN C.FSCORE END),
语文 = SUM(CASE B.FSUBNAME WHEN '语文' THEN C.FSCORE END),
历史 = SUM(CASE B.FSUBNAME WHEN '历史' THEN C.FSCORE END)
FROM STUDENT A, SUBJECT B, SCORE C
WHERE A.FID = C.FSTDID AND B.FSUBID = C.FSUBID GROUP BY A.FNAME
------------------------------------------------------------------------
SELECT A.FNAME AS 姓名,
英语 = SUM（DECODE（B.FSUBNAME,’ 英语’,C.FSORE）），
数学 = SUM（DECODE（B.FSUBNAME,’ 数学’,C.FSORE）），
语文 = SUM（DECODE（B.FSUBNAME,’ 语文’,C.FSORE）），
历史 = SUM（DECODE（B.FSUBNAME,’ 历史’,C.FSORE）），
FROM STUDENT A, SUBJECT B, SCORE C
WHERE A.FID = C.FSTDID AND B.FSUBID = C.FSUBID GROUP BY A.FNAME
说明 这个案例主要是运用CASE与DECODE，当然也涉及GROUP BY的用法。
29、有两个表，用一条SQL语句算出商品A,B目前还剩多少？表结构如下：
商品名称mc 商品总量sl 表一(AAA)
A 100
B 120
商品名称mc 出库数量sl 表二(BBB)
A 10
A 20
B 10
B 20
SELECT TA.商品名称,A-B AS 剩余数量 FROM
(SELECT 商品名称,SUM(商品总量) AS A FROM AAA GROUP BY 商品名称)TA,
(SELECT 商品名称,SUM(出库数量) AS B FROM BBB GROUP BY 商品名称)TB
WHERE TA.商品名称=TB.商品名称
----------------------------------------------------------
SELECT 商品名称,SUM（商品总量）剩余数量 FROM
（SELECT * FROM AAA
UNION ALL
SELECT 商品名称,-出库数量 FROM BBB）A GROUP BY 商品名称
30、如何将A表的某个字段更新到表B的某个字段？
UPDATE A SET QTY=B.QTY FROM B WHERE A.CODE=B.CODE
---------------------------------------------------
UPDATE A SET QTY=(SELECT QTY FROM B WHERE B.CODE=A.CODE)
说明 这两道题在语法上SQL与ORACLE没有发别，只不过是两种思路而已。
31、有一个商品销售表，记载了某月商品的销售数量，现在要为所有商品排名次，放到一个单独的字段中，就是说达到右边显示的效果，如何作？
BU1032 5 NULL BU1032 5 2
PS2091 3 NULL PS2092 3 3
PC8888 50 NULL PC8888 50 1
UPDATE X SET ORD=(SELECT COUNT(*)+1 FROM X B WHERE B.QTY>X.QTY)
----------------------------------------------------------------
SELECT CODE,QTY,RANK() OVER (ORDER BY QTY) ORD FROM A_TEST
说明 SQL中的排序是通过UPDATE更新，然后再显示出来，而ORACLE使用了RANK OVER函数，直接将数据集显示出来，而且RANK OVER函数还可以通过PARTITION BY对分组中的数据进行排序。
32、SQL与ORACLE的文件结构
SQL文件被格式化为8K为单位的页，每8个相邻的页称为盘区（64K），若该盘区分配给一个对象，称为一致盘区，若分配给多个对象等为混合盘区，SQL 有全局分配页面、数据页面、索引页页、BLOB页面、TEXT页面。ORACLE的文件最小逻辑单位是由INIT.ORA中的BLOCK_SIZE的值决 定的，可以取2K、4K、6K、8K、16K、32K等，ORACLE的盘区是由一些块组成的，ORACLE的段是由盘区组成的，ORACLE有数据段、 索引段、回退段（UNDO段）、临时段、CLOB/BLOB段、CLUSTER段等。
33、SQL与ORACLE如何取得一个全局唯一标识标（GUID）
SELECT NEWID（）
----------------------------------
SELECT SYS_GUID() FROM DUAL
34、本人有一张表单, 要求统计COL1~COL6中不等于2的列的个数，数据如下：
————————————————————————————————
ROW_ID | COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |
1 | 2 | 1 | 1 | 2 | 3 | 2 |
2 | 1 | 1 | 2 | 2 | 2 | 2 |
3 | 2 | 3 | 2 | 2 | 1 | 2 |
4 | 2 | 2 | 2 | 2 | 1 | 2 |
5 | 1 | 2 | 2 | 2 | 2 | 2 |
6 | 2 | 2 | 2 | 2 | 2 | 1 |
————————————————————————————————
要求结果如下：
—————————
ROW_ID | COUNT |
1 | 3 |
2 | 2 |
3 | 2 |
4 | 1 |
5 | 1 |
6 | 1 |
SELECT ROW_ID,(6-(CASE WHEN COL1=2 THEN COL1 / 2 ELSE 0 END)
-(CASE WHEN COL2=2 THEN COL2 / 2 ELSE 0 END)
-(CASE WHEN COL3=2 THEN COL3 / 2 ELSE 0 END)
-(CASE WHEN COL4=2 THEN COL4 / 2 ELSE 0 END)
-(CASE WHEN COL5=2 THEN COL5 / 2 ELSE 0 END)
-(CASE WHEN COL6=2 THEN COL6 / 2 ELSE 0 END))AS COUNT FROM TABLE_A
35、有一客户表，数据如下：
客户 日期 资金
F001 2003-03-05 123.00
F002 2003-03-04 1223.00
F002 2003-03-02 1123.00
F003 2003-03-05 1231.00
F003 2003-03-04 1232.00
要求选出每个客户最新的哪条记录 组成一个结果集，结果如下：
F001 2003-03-05 123.00
F002 2003-03-04 1223.00
F003 2003-03-05 1231.00
实现方法：
SELECT A.客户, B.日期, A.资金 FROM 客户资金表 A,
(SELECT 客户, MAX(日期) 日期 FROM 客户资金表 GROUP BY 客户 ) B
WHERE A.客户 = B.客户 AND A.日期 = B.日期
36 现在看一个高难度的作业，后来解决办法和本例不同，请看需求。
视图1 CITYWATER_VIEW
行政区划名称 城市用水量（亿M3） 。。。
北京市 15000 …
上海市 9000 …
天津市 5400 …
重庆市 9500 …



表2 CODE
区划 代码
北京市 100000
上海市 200000
天津市 300000
表3 CITYWATER
代码 城市用水
100000 15000
200000 9000
300000 5400
表1 DICTIONARY
字段别名 字段全名
区划 行政区划名称
代码 行政区划代码
城市用水 城市用水量（亿M3）

表1-2是数据库public中的基表,表3是数据库water中的基表；在数据库water中创建视图1，用T-SQL语句怎样实现？把查询结果的“字 段别名”修改为视图中的“字段全名”，如果采用T-SQL中的常用修改列标题方式（SELECT column_name AS expression或者SELECT expression= column_name ），很烦，每个基表里的字段太多，并且基表有近200个，字段近3000个。
说明：其实现在要作的就是将表3中的“代码“、“城市用水”替代成表1中的行政区划代码、城市用户量（亿M3）等。
CREATE VIEW V_GOD
AS SELECT A.[100000],B.[310000],B.[114011],B.[114111],B.[114421],B.[114311],B.[114321] FROM CODE A,FA01P B WHERE A.[200000]=B.[200000]
DECLARE CUR_COL CURSOR LOCAL FOR
SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('V_GOD')
DECLARE @COL VARCHAR(20),@SQL VARCHAR(999),
@COL_TOTAL VARCHAR(8000),@ALIAS VARCHAR(99),
@SOURCE VARCHAR(8000)
OPEN CUR_COL
FETCH CUR_COL INTO @COL
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @ALIAS=字段名 FROM DICTIONARY WHERE 段码=@COL
IF @COL_TOTAL IS NULL
SELECT @COL_TOTAL=@ALIAS
ELSE
SELECT @COL_TOTAL=@COL_TOTAL+','+@ALIAS
FETCH CUR_COL INTO @COL
END
CLOSE CUR_COL
SELECT @SOURCE=RTRIM(TEXT) FROM SYSCOMMENTS WHERE ID=OBJECT_ID('V_GOD')
SELECT @SOURCE=RTRIM(SUBSTRING(@SOURCE,CHARINDEX('AS',@SOURCE),LEN(@SOURCE)))
SELECT @SOURCE='ALTER VIEW V_GOD('+@COL_TOTAL+') '+@SOURCE
EXEC(@SOURCE)
说明 由于该实例需要的表有两个已没有记录，所以大家只有看看T-SQL的语法及动态SQL的编写，ORACLE也类似。
37、如何用SQL操作一段XML文件？
CREATE PROCEDURE UP_XML_TEST(@DOC VARCHAR(7999))
AS
BEGIN
DECLARE @IDOC INT
EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @DOC
SELECT *
FROM OPENXML (@IDOC, '/ROOT/DATASET/BOOKS',2)
WITH(TITLE VARCHAR(32) 'TITLE',
AUTHOR VARCHAR(20) 'AUTHOR',
PRICE DECIMAL(9,2) 'PRICE')
EXEC SP_XML_REMOVEDOCUMENT @IDOC
END

CREATE FUNCTION UF_XML_TEST(@DOC VARCHAR(7999))
RETURNS @T TABLE(TITLE VARCHAR(32),
AUTHOR VARCHAR(20),
PRICE DECIMAL(9,2))
AS
BEGIN
DECLARE @IDOC INT
EXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @DOC
INSERT INTO @T SELECT *
FROM OPENXML (@IDOC, '/ROOT/DATASET/BOOKS',2)
WITH(TITLE VARCHAR(32) 'TITLE',
AUTHOR VARCHAR(20) 'AUTHOR',
PRICE DECIMAL(9,2) 'PRICE')
EXEC SP_XML_REMOVEDOCUMENT @IDOC
RETURN
END

DECLARE @DOC VARCHAR(7999)
SELECT @DOC=
'<ROOT>
<DATASET>
<BOOKS>
<TITLE>DELPHI</TITLE>
<AUTHOR>ABC</AUTHOR>
<PRICE>38.00</PRICE>
</BOOKS>
<BOOKS>
<TITLE>MIDAS</TITLE>
<AUTHOR>DEF</AUTHOR>
<PRICE>26.00</PRICE>
</BOOKS>
</DATASET>
</ROOT>'
EXEC UP_XML_TEST @DOC
--SELECT * FROM DBO.UF_XML_TEST(@DOC)

转载于:https://www.cnblogs.com/ioriwuhj/archive/2008/06/27/1231357.html