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 ，后 ORACLE003 、 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 、 SERIALIZABLEORACLE 有两种事务隔离级别 READ COMMITTED 、 SERIALIZABLESQL 虽然有四种事务隔离，事务之间还是经常发生阻塞； 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 、 STRORACLE 常用类型转换函数有： TO_CHAR 、 TO_NUMBER 、 TO_DATESELECT CONVERT （ VARCHAR （ 20 ）， GETDATE （）， 112 ） ------------------------------------------------------------------------------------------------SELECT TO_CHAR （ SYSDATE ，‘ YYYYMMDD ’） FROM DUAL010 、 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 9CREATE 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) ASBEGIN SELECT ID ,SUM(QTY) FROM A_TEST WHERE ID=@ID GROUP BY @IDEND------------------------------------------------------------CREATE OR REPLACE PACKAGE UP_002 ASTYPE 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=@IDEND----------------------------------------------------------------------用 ORACLE 的触发器实现级联添加、级联删除、级联更新 CREATE OR REPLACE TRI_A1_INS AFTER INSERT OR DELETE OR UPDATE ON A1 FOR EACH ROWBEGIN 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 INTSELECT @M=COUNT(*) FROM A_TEST WHERE ID=XSELECT @M=@M-1SET ROWCOUNT @M    -- 限制客户端缓冲区的记录数 DELETE FROM A_TEST WHERE ID=XSET 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 的动态 SQLSQL 与 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)--------------------------------------------DECLARES VARCHAR2(99);BEGIN S:='SELECT COUNT(*)  FROM '||' USER_TABLES';EXECUTE IMMEDIATE S;END;19 、返回记录集中前 N 条记录的语法？ SQL 只有使用 TOP ， ORACLE 可以使用 ROWNUMSELECT TOP N * FROM 记录集（表，视图，子查询） ---------------------------------------------SELECT * FROM 记录集 WHERE ROWNUM<=N20 如何返回记录集中相临两记录之间某字段的差值？ 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 A001SELECT B.QTY-A.QTY FROM #X A,#X B WHERE A.CODE=B.CODE-1DROP 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 INTSELECT @I=1WHILE @I<1000 BEGININSERT INTO A03 VALUES(@I)  SELECT @I=@I+1END-- 前部分是创建环境，后一部分是实现方法，比较牵强 SELECT IDENTITY(INT,1,1) CODE,ID INTO #X FROM A03SELECT ID FROM #X WHERE CODE BETWEEN 10 AND 20DROP TABLE #X------------------------------------------------------BEGINFOR I IN 1..999 LOOP  INSERT INTO A03 VALUES(I);END LOOP;END;SELECT * FROM A03 WHERE ROWNUM<20MINUSSELECT * 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_FKFETCH CUR_FK INTO @FK,@TBLWHILE @@FETCH_STATUS =0BEGINSELECT @SQL='ALTER TABLE '+@TBL+' DROP CONSTRAINT '+@FK  EXEC(@SQL)  --SELECT @SQL='DROP TABLE '+@TBL  FETCH CUR_FK INTO @FK,@TBLENDCLOSE CUR_FKDECLARE CUR_FKS CURSOR LOCAL FOR   SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'OPEN CUR_FKSFETCH CUR_FKS INTO @TBLWHILE @@FETCH_STATUS =0BEGIN   SELECT @SQL='DROP TABLE ['+@TBL+']'   EXEC(@SQL)   FETCH CUR_FKS INTO @TBLENDCLOSE CUR_FKS  ----------------------------------------------------------------DECLARES 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_UTILITYEXECUTE 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=1WHILE @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+1END---------------------------------------------------------DECLAREC 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    66SELECT 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 CWHERE 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 CWHERE 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         20SELECT TA. 商品名称 ,A-B AS 剩余数量 FROM(SELECT 商品名称 ,SUM( 商品总量 ) AS A FROM AAA GROUP BY 商品名称 )TA,(SELECT 商品名称 ,SUM( 出库数量 ) AS B FROM BBB GROUP BY 商品名称 )TBWHERE TA. 商品名称 =TB. 商品名称 ----------------------------------------------------------SELECT 商品名称 ,SUM （商品总量）剩余数量 FROM （ SELECT * FROM AAA UNION ALLSELECT 商品名称 ,- 出库数量 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  2PS2091  3  NULL      PS2092 3  3PC8888  50  NULL      PC8888 50 1UPDATE 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 DUAL34 、本人有一张表单 , 要求统计 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  说明 本例摘自 WWW.DELPHIBBS.COM ，有名的 DELPHI 开发网站，本人不拥有版权。该 SQL 的实现方法与 ORACLE 的实现写法完全一样，不在多述。  35 、有一客户表，数据如下： 客户     日期           资金 F001    2003-03-05     123.00F002    2003-03-04     1223.00F002    2003-03-02     1123.00F003    2003-03-05     1231.00F003    2003-03-04     1232.00要求选出每个客户最新的哪条记录 组成一个结果集，结果如下： F001    2003-03-05     123.00F002    2003-03-04     1223.00F003    2003-03-05     1231.00实现方法： SELECT A. 客户 , B. 日期 , A. 资金 FROM   客户资金表 A,    (SELECT 客户 , MAX( 日期 ) 日期  FROM 客户资金表  GROUP BY 客户 ) BWHERE A. 客户 = B. 客户 AND A. 日期 = B. 日期   说明 ORACLE 的写法与 SQL 一样 , 本例也摘自 WWW.DELPHIBBS.COM ，本人不拥有版权。  36 现在看一个高难度的作业，后来解决办法和本例不同，请看需求。 视图 1 CITYWATER_VIEW行政区划名称   城市用水量（亿 M3 ）   。。。 北京市  15000   … 上海市  9000   … 天津市  5400   … 重庆市  9500   … 表 2 CODE区划   代码 北京市  100000上海市  200000天津市  300000表 3 CITYWATER代码   城市用水 100000  15000200000  9000300000  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_GODAS 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_COLFETCH CUR_COL INTO @COLWHILE @@FETCH_STATUS=0BEGIN  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 @COLENDCLOSE CUR_COLSELECT @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+') '+@SOURCEEXEC(@SOURCE)说明 由于该实例需要的表有两个已没有记录，所以大家只有看看 T-SQL 的语法及动态 SQL 的编写， ORACLE 也类似。  37 、如何用 SQL 操作一段 XML 文件？ CREATE PROCEDURE UP_XML_TEST(@DOC VARCHAR(7999))ASBEGINDECLARE @IDOC INTEXEC SP_XML_PREPAREDOCUMENT @IDOC OUTPUT, @DOCSELECT *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 @IDOCEND       CREATE FUNCTION UF_XML_TEST(@DOC VARCHAR(7999))RETURNS @T TABLE(TITLE VARCHAR(32),        AUTHOR VARCHAR(20),        PRICE DECIMAL(9,2))ASBEGIN 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 RETURNENDDECLARE @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)说明 用过程可以方便地对 XML 进行操作，但编写成 FUNCTION 时就报错，大概 MS 的函数内部不充许执行 OPENXML 等这类行集函数。另一个重要的问题是， SQL 的这种语法竟然不支持汉字字串，真是要命。 38 、使用 DBMS_REPAIR 检测与修复破损的 BLOCK ？ ADMIN_TABLES   提供管理函数修复或孤立关键表，包含创建、净化与删除函数。 CHECK_OBJECT   检测并报告表或索引的破损块。 DUMP_ORPHAN_KEYS   导出破损块的数据 FIX_CORRUPT_BLOCKS   在 CHECK_OBJECT 检测出的破损块上做标记 REBUILD_FREELISTS   重建对象的 FREELISTSSKIP_CORRUPT_BLOCKS   设置在表或索引扫描时是否不扫描被做了破损标记的块。 SEGMENT_FIX_STATUS   整理 BITMAP 实体上的破损标志   上表列举了 DBMS_REPAIR 包所有的过程，下边将对这些过程要引入的参数的枚举值进行 说明，这引些参数将在过程应用中起决定作用。 object_type  TABLE_OBJECT, INDEX_OBJECT, CLUSTER_OBJECTaction  CREATE_ACTION, DROP_ACTION, PURGE_ACTIONtable_type  REPAIR_TABLE, ORPHAN_TABLEflags  SKIP_FLAG, NOSKIP_FLAG SQL>  EXEC DBMS_REPAIR.ADMIN_TABLES('SCOTT.EMP',DBMS_REPAIR.REPAIR_TABLE,-DBMS_REPAIR.CREATE_ACTION,'USERS');ORA-24129: 表名 SCOTT.EMP 没有以前缀 REPAIR_ 开始 SQL> EXEC DBMS_REPAIR.ADMIN_TABLES('REPAIR_EMP',DBMS_REPAIR.REPAIR_TABLE,-     DBMS_REPAIR.CREATE_ACTION,'USERS');    SQL> SELECT OBJECT_NAME FROM REPAIR_EMP;    SQL> EXEC DBMS_REPAIR.ADMIN_TABLES('ORPHAN_EMP',DBMS_REPAIR.ORPHAN_TABLE,-DBMS_REPAIR.CREATE_ACTION,'USERS');SQL> SELECT TABLE_NAME FROM ORPHAN_EMP;ADMIN_TABLES 过程可以创建 DBMS_REPAIR 包的使用中需要的一些辅助表。 SQL> DECLARE    M INTEGER;    BEGIN      DBMS_REPAIR.CHECK_OBJECT(SCHEMA_NAME=>'SCOTT',                OBJECT_NAME=>'EMP',                REPAIR_TABLE_NAME =>'REPAIR_EMP',                CORRUPT_COUNT=>M);      DBMS_OUTPUT.PUT_LINE(M);    END;说明 统计 SCOTT 模式的 EMP 表有多少破损块。其它的过程本人就不再一一举例说明啦，引用方法类似与上边的实例，其它一些过程的参数列表可以通用 SQL>DESC DBMS_REPAIR 来查看。 39 、关于 UTL_FILE 包的使用方法 使用 UTL_FILE 时有个地方要注意 :INIT.ORA 文件中的 UTL_FILE_DIR 参数必须指定路径，即 UTL_FILE 包只有在 UTL_FILE_DIR 所指的目录中有权限读写，以下的实例表示本人已经修改 UTL_FILE_DIR=C:/ 啦。 SQL>DESC UTL_FILE    可以查看 UTL_FILE 包的所有类型与过程。 例将表中数据输出到文件： CREATE OR REPLACE PROCEDURE UP_FILEW  IS    ID NUMBER;    NAME VARCHAR2(20);    HANDLE  UTL_FILE.FILE_TYPE;    CURSOR REGION_CUR  IS   SELECT * FROM A_JOB;BEGIN   HANDLE :=UTL_FILE.FOPEN('C:/','JOB.OUT','W');   OPEN  REGION_CUR;   FETCH  REGION_CUR  INTO ID,NAME;   WHILE  REGION_CUR%FOUND LOOP     UTL_FILE.PUTF(HANDLE,'%S,%S/N',ID,NAME);     FETCH  REGION_CUR  INTO  ID,NAME;   END LOOP;   CLOSE REGION_CUR;   UTL_FILE.FFLUSH(HANDLE);   UTL_FILE.FCLOSE(HANDLE);   END UP_FILEW; 例将文件中数据写入到表中  CREATE OR REPLACE PROCEDURE UP_FILER  IS     STR VARCHAR(200);     ID NUMBER;     NAME VARCHAR2(20);     HANDLE  UTL_FILE.FILE_TYPE;     POS NUMBER(6); BEGIN    HANDLE :=UTL_FILE.FOPEN('C:/','JOB.OUT','R');    UTL_FILE.GET_LINE(HANDLE,STR);    WHILE  LENGTH(STR)>0 LOOP      POS:=INSTR(STR,',');      ID := TO_NUMBER(SUBSTR(STR,1,POS-1));      NAME:=SUBSTR(STR,POS+1,LENGTH(STR));      --DBMS_OUTPUT.PUT_LINE(TO_CHAR(ID)||NAME);      INSERT INTO A_JOB VALUES(ID,NAME);      UTL_FILE.GET_LINE(HANDLE,STR);    END LOOP;    EXCEPTION    WHEN OTHERS THEN         UTL_FILE.FCLOSE(HANDLE);END UP_FILER;说明：由于没办法判断文件是否结束，所以本例就使用 EXCEPTION 处理，在文件读完时触发异常，并关闭文件。 UTL_FILE.FILE_TYPE 是文件句柄，就象 C 或 PASCAL 中读写文件的方式是一样的。 40 、关于 DBMS_JOB 包的使用方法？ 首先在 SQL>DESC DBMS_JOB 来查看 DBMS_JOB 的一些过程，然后可以在 RDBMS 目录中找到 DBMSJOB.SQL ，这个文件就是 DBMS_JOB 包的源程序，并有参数说明，本例就给出来实例与常用的数据字典（ USER_JOBS 、 DBA_JOBS 、 ALL_JOBS ）。 创建测试表 SQL> CREATE TABLE A_JOB(DT DATE);创建一个自定义过程 SQL> CREATE OR REPLACE PROCEDURE UP_TEST AS    BEGIN       INSERT INTO A_JOB VALUES(SYSDATE);    END;    /创建 JOBSQL> VARIABLE JOB1 NUMBER;SQL> SQL> BEGINDBMS_JOB.SUBMIT(:JOB1,'UP_TEST;',SYSDATE,'SYSDATE+1/(24*60)'); 　　 -- 每天 1440 分钟，即一分钟运行 TEST 过程一次     END;    /运行 JOBSQL> BEGIN    DBMS_JOB.RUN(:JOB1);    END;    /查看结果 SQL> SELECT TO_CHAR(DT,'YYYY/MM/DD HH24:MI:SS') 时间 FROM A_JOB;时间 -------------------2001/01/07 23:51:212001/01/07 23:52:222001/01/07 23:53:24删除 JOBSQL> BEGIN  2  DBMS_JOB.REMOVE(:JOB1);  3  END;  4  /说明： JOB1 是 BIND VARIABLE （绑定变量），相当于一个常局变量，在当前会话的生命期内可以引用，如果我们要删除一个 JOB ，通常是从 USER_JOBS 字典中找到 JOB 的 ID 。 SQL> SELECT JOB FROM USER_JOBS;SQL>EXEC DBMS_JOB.REMOVE( 上一句查出来的 JOB 号 );41 、关于 DBMS_SQL 包的使用方法？ DBMS_SQL 包是动态执行 SQL 语句的一个包，它使用方法比 EXECUTE IMMEDIATE 复杂，但功能更强大一些，最主要的是它执行的 SQL 可以超出 64K 的限制， DBMSSQL.SQL 是该包的 SQL 源程序（ RDBMS 目录内）。 DECLARE      T_C1_TNAME      USER_TABLES.TABLE_NAME%TYPE;      T_COMMAND       VARCHAR2(200);      T_CID           INTEGER;      T_TOTAL_RECORDS NUMBER(10);      STAT            INTEGER;      ROW_COUNT       INTEGER;T_LIMIT         INTEGER := 0;  -- 限制只取出记录大于 0 的表的情况       CURSOR C1 IS SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME;  -- 查出所有表的名字 BEGIN    T_LIMIT := 0;    OPEN C1;LOOP       -- 取出一个表名            FETCH C1 INTO T_C1_TNAME;      -- 如果游标记录取完，退出循环               EXIT WHEN C1%NOTFOUND;                 T_COMMAND := 'SELECT COUNT(0) FROM '||T_C1_TNAME;           T_CID := DBMS_SQL.OPEN_CURSOR;    -- 创建一个游标            DBMS_SQL.PARSE(T_CID,T_COMMAND,DBMS_SQL.NATIVE);  -- 向服务器发出一个语句并检查这个语句的语法和语义错误            DBMS_SQL.DEFINE_COLUMN(T_CID,1,T_TOTAL_RECORDS);  -- 定义将从 FETCHROWS() 函数接收数据的变量的            STAT := DBMS_SQL.EXECUTE(T_CID);    -- 执行此语句，必须跟着 FETCH_ROWS 函数并为单个行检索数据            ROW_COUNT := DBMS_SQL.FETCH_ROWS(T_CID);  -- 取回一行数据放入局部缓冲区            DBMS_SQL.COLUMN_VALUE(T_CID,1,T_TOTAL_RECORDS);  -- 返回调用 FETCHROWS() 取回的值，值存储在 T_TOTAL_RECORDS 中            IF T_TOTAL_RECORDS > T_LIMIT THEN               DBMS_OUTPUT.PUT_LINE(RPAD(T_C1_TNAME,55,' ')||               TO_CHAR(T_TOTAL_RECORDS,'99999999')||' RECORD(S)');            END IF;           DBMS_SQL.CLOSE_CURSOR(T_CID);     END LOOP;    CLOSE C1;END;42 、 SQL 与 ORACLE 取随机数的方法，本例要求从 65 到 97 之间的随机数？ SELECT 65+FLOOR(RAND()*26)-------------------------------------------SELECT FLOOR(DBMS_RANDOM.VALUE(65,97)) FROM DUAL43 、 SQL 与 ORACLE 取系统时间的方法 SELECT GETDATE （） -------------------------------------------SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS') FROM DUAL44 、关于 DBMS_FLASHBACK 包的使用方法？ DBMS_FLASHBACK 包处理 ORACLE 的闪回功能，它是 ORACLE9I 新增的一个功能，可以方便地提取表中数据的前映象。你不要指望闪回功能帮你找回所有有意或无意删除的数据，它最多只能得到 5 天内的前映象而不用回退日志。 SQL>DESC DBMS_FLASHBACK  现在看示例如下  SQL>CREATE TABLE A_TEST(ID INT);/* 创建表后请退出 SQL PLUS 再进来做测试 */ SQL>INSERT INTO A_TEST VALUES(1); SQL>COMMIT; SQL>SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;    此处显示 SCN 号 1 （ 77056701 ）  SQL>INSERT INTO A_TEST VALUES(2); SQL>COMMIT; SQL>SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;    此处显示 SCN 号 2 (77056801) SQL>SELECT * FROM A_TEST AS OF SCN 77056701 SQL>SELECT * FROM A_TEST AS OF SCN 77056801 SQL>SELECT * FROM A_TEST  说明：如果创建表 A_TEST 后立即使用 SELECT * FROM  AS OF SCN 这种语法， ORACLE 会返回 ORA-01466: 无法读数据 - 表定义已更改 , 这是正常的，只有退出再进来就可以使用闪回语法啦， SYS 用户无法使用闪回语法。 相关链接： HTTP://WWW.ITPUB.NET/SHOWTHREAD.PHP?S=&THREADID=116321 45 、有一个表，记录数据较多，要求对同一分类的数据进行排序？ 工號   姓名   時間     序號 001   abc   08:00     1001   abc   12:01     2001   abc   13:28     3001   abc   17:40     4002   def    07:30    1002   def    22:59    2SQL 的解决方法如下： CREATE TABLE A_TEST(ID VARCHAR(10),SJ VARCHAR(20),ORD INT)INSERT INTO A_TEST VALUES('001','07',0)INSERT INTO A_TEST VALUES('001','08',0)INSERT INTO A_TEST VALUES('001','09',0)INSERT INTO A_TEST VALUES('002','07',0)INSERT INTO A_TEST VALUES('002','08',0)INSERT INTO A_TEST VALUES('002','09',0)INSERT INTO A_TEST VALUES('003','07',0)INSERT INTO A_TEST VALUES('003','08',0)INSERT INTO A_TEST VALUES('003','09',0)UPDATE A_TEST SET ORD=(SELECT COUNT(*)+1 FROM A_TEST B WHERE B.SJ<A_TEST.SJ AND B.ID=A_TEST.ID)46 、 SQL 与 ORACLE 如何延时执行程序？ WAITFOR DELAY ‘ 00:01:00 ’   -- 延时一分钟 WAITFOR TIME   ’ 12:00:00 ’   -- 定时到 12 点整 ------------------------------------------------SQL>EXEC DBMS_LOCK.SLEEP(1)说明： DBMS_LOCK.SLEEP 延时一分钟与 SQL 第一条语法作用相当。 47 、 SQL 与 ORACLE 如何返回服务器的 IP 地址？ CREATE PROCEDURE GETIP ASBEGIN  CREATE TABLE M(DEMO VARCHAR(7999))  DECLARE @SQL VARCHAR(99)  SELECT @SQL='XP_CMDSHELL '+'''IPCONFIG'''  INSERT INTO M EXEC(@SQL)DECLARE @S VARCHAR(99),@IP VARCHAR(24),@P INT,@L INTSELECT @S=RTRIM(LTRIM(DEMO)) FROM M WHERE DEMO LIKE '%IP ADDRESS%'SELECT @L=LEN(@S),@P=CHARINDEX(':',@S,1)SELECT @IP=RTRIM(LTRIM(RIGHT(@S,@L-@P)))SELECT @IP  DROP TABLE MENDEXEC GETIP------------------------------------------------------------SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL; EXEC DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);说明 SYS_CONTEXT 求客户端 IP 地址，一般在触发器中使用 48 、 SQL 与 ORACLE 中对象是如何重命名的？ EXEC SP_RENAME ‘旧表名’ , ’新表名’ EXEC SP_RENAME ‘表名 . 字段名’ , ’新字段名’ EXEC SP_RENAMEDB ‘旧数据库名’ , ’新数据库名’ ------------------------------------------------------------RENAME 旧表名 TO 新表名 数据库重命名可以用 NID （从 9I 开始），字段重命名暂缺。 49 、 ORACLE9I 中 INSERT 的新语法，源表的结构与数据示例如下：  SQL>SELECT * FROM sales_input_table;PRODUCT_ID CUSTOMER_ID WEEKLY_ST  SALES_SUN  SALES_MON  SALES_TUE  SALES_WED SALES_THU  SALES_FRI  SALES_SAT---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ----------       111         222 01-OCT-00        100        200        300        400       500        600        700       222         333 08-OCT-00        200        300        400        500       600        700        800       333         444 15-OCT-00        300        400        500        600       700        800        900 sales_input_table 表存储了商品每周的销售明细，将它转成如下所示？    PROD_ID    CUST_ID TIME_ID   C   PROMO_ID QUANTITY_SOLD     AMOUNT       COST---------- ---------- --------- - ---------- ------------- ---------- ----------       111        222 01-OCT-00                                   100       111        222 02-OCT-00                                   200       111        222 03-OCT-00                                   300       111        222 04-OCT-00                                   400       111        222 05-OCT-00                                   500       111        222 06-OCT-00                                   600       111        222 07-OCT-00                                   700       222        333 08-OCT-00                                   200       222        333 09-OCT-00                                   300       222        333 10-OCT-00                                   400       222        333 11-OCT-00                                   500       222        333 12-OCT-00                                   600       222        333 13-OCT-00                                   700       222        333 14-OCT-00                                   800       333        444 15-OCT-00                                   300       333        444 16-OCT-00                                   400       333        444 17-OCT-00                                   500       333        444 18-OCT-00                                   600       333        444 19-OCT-00                                   700       333        444 20-OCT-00                                   800       333        444 21-OCT-00                                   900请看下边的这组 SQL 语句，成功而方便地解决这个问题 SQL> INSERT ALL      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date, sales_sun)      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)      INTO sales (prod_id, cust_id, time_id, amount)      VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)   SELECT product_id, customer_id, weekly_start_date, sales_sun,      sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat      FROM sales_input_table;刚才看了 INSERT ALL 的用法，现在再看看 INSERT ALL 与 WHEN 的用法： CREATE TABLE small_orders    (order_id       NUMBER(12)   NOT NULL,    customer_id    NUMBER(6)    NOT NULL,    order_total    NUMBER(8,2),    sales_rep_id   NUMBER(6)   );CREATE TABLE medium_orders AS SELECT * FROM small_orders;CREATE TABLE large_orders AS SELECT * FROM small_orders;CREATE TABLE special_orders    (order_id       NUMBER(12)    NOT NULL,    customer_id    NUMBER(6)     NOT NULL,    order_total    NUMBER(8,2),    sales_rep_id   NUMBER(6),    credit_limit   NUMBER(9,2),    cust_email     VARCHAR2(30)   );  现在已经创建了四个表，将测试的环境搭起来啦。 INSERT ALL   WHEN order_total < 1000000 THEN      INTO small_orders   WHEN order_total > 1000000 AND order_total < 2000000 THEN      INTO medium_orders   WHEN order_total > 2000000 THEN       INTO large_orders   SELECT order_id, order_total, sales_rep_id, customer_id   FROM orders;当然，我们也可以使用 ELSE 来替代最后一个 WHEN … THENINSERT ALL   WHEN order_total < 100000 THEN      INTO small_orders   WHEN order_total > 100000 AND order_total < 200000 THEN      INTO medium_orders   ELSE      INTO large_orders   SELECT order_id, order_total, sales_rep_id, customer_id   FROM orders;以上两组 SQL 功能是一样的。现在再看一下 INSERT FIRST 的用法： INSERT FIRST   WHEN ottl < 100000 THEN      INTO small_orders         VALUES(oid, ottl, sid, cid)   WHEN ottl > 100000 and ottl < 200000 THEN      INTO medium_orders         VALUES(oid, ottl, sid, cid)   WHEN ottl > 290000 THEN      INTO special_orders   WHEN ottl > 200000 THEN      INTO large_orders         VALUES(oid, ottl, sid, cid)   SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,      o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem      FROM orders o, customers c      WHERE o.customer_id = c.customer_id;说明： Large_Orders 表中将不含有 OTT1>290000 这部分数据。 INSERT ALL   WHEN ottl < 100000 THEN      INTO small_orders   VALUES(oid, ottl, sid, cid)   WHEN ottl > 100000 and ottl < 200000 THEN      INTO medium_orders  VALUES(oid, ottl, sid, cid)   WHEN ottl > 200000 THEN      into large_orders   VALUES(oid, ottl, sid, cid)   WHEN ottl > 290000 THEN      INTO special_orders   SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,      o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem      FROM orders o, customers c  WHERE o.customer_id = c.customer_id;说明： Large_Orders 表中将含有 OTT1>290000 这部分数据，这就是 INSERT ALL 与 INSERT FIRST 的区别。  50 、 ORACLE9I 中 WITH 的新用法。可以理解成 WITH 是用来对 ORACLE9I 子查询定义别名 SQL> with total as ( select deptno,sum(sal) from emp group by deptno)    select count(*) from total; 51 、 ORACLE9i 中 MERGE 的用法，若目录表中有匹配数据就更新，否则就添加新数据  MERGE INTO TABLE_NAME AS TABLE_ALIAS USING (TABLE|VIEW|SUB_QUERY) AS ALIAS ON (JOIN CONDITION) WHEN MATCHED THEN UPDATE SET COL1 = COL_VAL1,COL2 = COL2_VAL WHEN NOT MATCHED THEN INSERT (COLUMN_LIST) VALUES (COLUMN_VALUES);  用 SCOTT/TIGER 登录测试 SQL> SELECT * FROM DEPT;SQL>CREATE TABLE BBBASSELECT * FROM DEPT WHERE DEPTNO IN (10,20);SQL>MERGE INTO BBB   USING DEPT ON (DEPT.DEPTNO=BBB.DEPTNO) WHEN MATCHED THEN UPDATE SET DNAME=DEPT.DNAME,LOC=DEPT.LOC WHEN NOT MATCHED THEN INSERT (DEPTNO,DNAME,LOC) VALUES (DEPT.DEPTNO,DEPT.DNAME,DEPT.LOC); 52 、 ORACLE 系统触发器的类型与使用介绍（ SQL 没有系统触发器）   系统级触发器事件： AFTER SERVERERROR   服务器错误触发 AFTER LOGON         登录后触发 BEFORE LOGOFF       退出登录前触发 AFTER STARTUP         启动数据库后触发 AFTER SUSPEND         数据库挂起后触发 (9i 新增 )BEFORE SHUTDOWN     数据库关闭前触发 猜想，即然有 SUSPEND 事件，应该就唤醒事件，不知如何写？创建系统触发器时需要指定作用范围： ON DATABASE 或 ON SCHEMA 。 CREATE OR REPLACE TRIGGER TRIGGER_NAME TIMING DATABASE_EVENT1 or DATABSE_EVENT2 ON DATABASE|SCHEMATRIGGER BODY 53 、 DBMS_SPACE 包的使用方法？（在 RDBMS/ADMIN/DBMSSPC.SQL 文件中）  SQL>DESC DBMS_SPACE 可以看到 DBMS_SPACE 包提供了三个过程：    PROCEDURE FREE_BLOCKS   对象未使用的块计算    PROCEDURE SPACE_USAGE   对象使用的空间计算    PROCEDURE UNUSED_SPACE   对象未使用空间计算   以 FREE_BLOCKS 过程为例：  SQL>SELECT FREELISTS,FREELIST_GROUPS,NUM_FREELIST_BLOCKS FROM DBA_TABLESINPUT WHERE TABLE_NAME='EMP'; SQL> DECLAREFBLKS NUMBER;BEGIN  DBMS_SPACE.FREE_BLOCKS('SCOTT','EMP','TABLE',0,FBLKS);  DBMS_OUTPUT.PUT_LINE(FBLKS);END;  说明： 0 是从 DBA_TABLES 字典表中求得的 EMP 表的 FREELIST_GROUP_ID ， FBLKS 就是求得的 EMP 表的未使用的 BLOCK NUMBER 。 54 、 SQL SERVER 2000 一個表裡有一個 ID 字段和若干 INT 字段，能不能用一個 SQL 語句對這些 INT 求和。表结构如下： ID    INT1   INT2  INT3  INT4 （ C ， I ， I ， I ， I ） 如這樣的表 A  1  2  3  4B  2  3  4  6C  5  7  10  11最後的出的是 A  1  2  3  4  10B  2  3  4  6  15C  5  7  10  11  33總數  8  12  17  21  58CREATE TABLE A_SUM(ID VARCHAR(20),I1 INT,I2 INT)INSERT INTO A_SUM VALUES('01',2,3)INSERT INTO A_SUM VALUES('02',3,4)SELECT ID,I1,I2,I1+I2 TOTAL FROM A_SUMUNIONSELECT ' 汇总 ',SUM(I1), SUM(I2),SUM(I1)+SUM(I2) FROM A_SUM55 、表 A 只有一列 LANE 。现在 A 中有如下行表示一些城市对 ，数据如下： LANE ------------------------------------------- 上海 - 北京 北京 - 上海 上海 - 南京 南京 - 上海 广州 - 长沙 - 武汉 武汉 - 长沙 - 广州 北京 - 东京 我希望通过一条 SQL 查询，能将其中“重复”的城市对过滤掉，即形成如下结果。至于出现的是“上海 - 北京”还是“北京 - 上海”我倒不在意。 LANE ------------------------------------------- 北京 - 上海 上海 - 南京 武汉 - 长沙 - 广州 北京 - 东京 网友 NYFOR 的解决方法如下： CREATE TABLE A(LANE VARCHAR2(255)); INSERT INTO A VALUES(' 上海 - 北京 '); INSERT INTO A VALUES(' 北京 - 上海 '); INSERT INTO A VALUES(' 上海 - 南京 '); INSERT INTO A VALUES(' 南京 - 上海 '); INSERT INTO A VALUES(' 广州 - 长沙 - 武汉 '); INSERT INTO A VALUES(' 武汉 - 长沙 - 广州 '); INSERT INTO A VALUES(' 北京 - 东京 '); CREATE OR REPLACE FUNCTION NORMALIZE(STR VARCHAR2) RETURN VARCHAR2AS  LTAB TAB_STR := TAB_STR();  LS VARCHAR2(255) := STR;  POS NUMBER := 0;  CURSOR CUR IS    SELECT COLUMN_VALUE FROM TABLE(CAST(LTAB AS TAB_STR)) ORDER BY 1;BEGIN  POS := INSTR(LS,'-');  WHILE POS > 0 LOOP    LTAB.EXTEND;    LTAB(LTAB.COUNT) := SUBSTR(LS,1,POS-1);    LS := SUBSTR(LS,POS+1);    POS := INSTR(LS,'-');  END LOOP;  LTAB.EXTEND;  LTAB(LTAB.COUNT) := LS;  LS := '';  FOR C IN CUR LOOP    LS := LS || '-' || C.COLUMN_VALUE;  END LOOP;  RETURN SUBSTR(LS,2);END;SQL> SELECT DISTINCT NORMALIZE(LANE) LANE FROM A;说明：原贴地址 HTTP://WWW.ITPUB.NET/SHOWTHREAD.PHP?S=&THREADID=126747   请看 mouse_jacky 网友的解决，一句话搞定： SELECT LANE FROM (SELECT LANE,RANK() OVER (PARTITION BY COL_ID ORDER BY LANE) AS RKFROM (SELECT LANE,DBMS_UTILITY.GET_HASH_VALUE(SUBSTR(LANE,1,INSTR(LANE,'-') -1),1000,2048) *DBMS_UTILITY.GET_HASH_VALUE(SUBSTR(SUBSTR(LANE,INSTR(LANE,'-')+1),1,INSTR(LANE,'-') -1),1000,2048) *DBMS_UTILITY.GET_HASH_VALUE(SUBSTR(SUBSTR(LANE,INSTR(LANE,'-')+1),INSTR(LANE,'-') +1),1000,2048) COL_ID FROM A)) WHERE RK=1 56 、关于 ORACLE 中的 UTL_ENCODE 包的使用方法  UTL_ENCODE 包是加密解密包，使用 64 的加密方法，把 RAW 类型的数值进行加密，解密时返回十六进制的，两个数字为一个字节，若长度不足则被 0 。通过 DESC UTL_ENCODE 可以显示包中所有的过程，简单举例：  SQL> SELECT UTL_ENCODE.BASE64_ENCODE('111') FROM DUAL; SQL> SELECT UTL_ENCODE.BASE64_DECODE('4152453D') FROM DUAL; 57 、 SQL 实现交叉表的方法？   交叉一般来讲是分组统计的一种，形式更复杂，显示更清淅，但数据库本身并没有提供实现交叉表的功能，自己创建交叉表不仅要对过程、游标、临时表、动态 SQL 等非常熟悉，而且思路也要清淅，本例以 PUBS.DBO.SALES 表的数据做样本： CREATE PROCEDURE UP_TEST(@T1 VARCHAR(30),@T2 VARCHAR(30),@T3 VARCHAR(30),@T4 VARCHAR(30)) AS--T1 表名 ,T2,T3 是交叉表的两上分类字段 ,T4 是汇总字段 --T2 是行字段， T3 列字段 BEGINDECLARE @SQL VARCHAR(7999),@FIELD VARCHAR(30)  SELECT @SQL='SELECT DISTINCT '+@T3+' FROM '+@T1  CREATE TABLE #FIELD(FIELD VARCHAR(30))  -- 将列字段提取到临时表 #FIELD 中   INSERT INTO #FIELD EXEC(@SQL)  SELECT @SQL='CREATE TABLE CROSS_TEST('+@T2+' VARCHAR(30),'  DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT * FROM #FIELD  OPEN CUR_FIELD  FETCH CUR_FIELD INTO @FIELD  WHILE @@FETCH_STATUS=0 BEGIN    SELECT @FIELD='['+@FIELD+']' SELECT @SQL=@SQL+@FIELD+' DECIMAL(8,2) DEFAULT 0,'    FETCH CUR_FIELD INTO @FIELD  END  SELECT @SQL=LEFT(@SQL,LEN(@SQL)-1)+')'  -- 创建临时交叉表 CROSS_TEST  EXEC(@SQL)  SELECT @SQL='INSERT INTO CROSS_TEST('+@T2+') SELECT DISTINCT '+@T2+' FROM '+@T1-- 将行数据存入交叉表 #CROSS_TESTEXEC(@SQL)  -- 创建分组数据表 TEMP  SELECT @SQL='CREATE TABLE TEMP('+@T2+' VARCHAR(30),'+@T3+' VARCHAR(30),'+@T4+' DECIMAL(8,2))'  EXEC(@SQL)   -- 将交叉汇总数据放入交叉表   SELECT @SQL='SELECT  '+@T2+','+@T3+', SUM(QTY) QTY FROM '+@T1 +' GROUP BY '+@T2+','+@T3  INSERT INTO TEMP EXEC(@SQL)  -- 将汇总数据写入交叉表   DECLARE CUR_SUM CURSOR LOCAL FOR SELECT * FROM TEMP  DECLARE @F1 VARCHAR(30),@F2 VARCHAR(30),@QTY DECIMAL(8,2),@Q1 VARCHAR(30)  OPEN CUR_SUM  FETCH CUR_SUM INTO @F1,@F2,@QTY  WHILE @@FETCH_STATUS=0 BEGIN    SELECT @F2='['+@F2+']',@Q1=CAST(@QTY AS VARCHAR(30))    SELECT @SQL='UPDATE CROSS_TEST SET '+@F2+'='+@Q1+' WHERE '+@T2+'='''+@F1+''''    EXEC(@SQL)    FETCH CUR_SUM INTO @F1,@F2,@QTY  END  CLOSE CUR_SUM  SELECT * FROM CROSS_TEST  DROP TABLE TEMP    DROP TABLE CROSS_TEST  DROP TABLE #FIELDEND--------------------------------------------------------EXEC UP_TEST 'SALES','TITLE_ID','STOR_ID','QTY'  说明：字段加中括号为了处理字段中含有特殊字符，值得注意得是要实现交叉表的表必须有两个分类，本例只支持分类字段的数据类型是字符型的，最大的问题就是高亮显示这行的 WHERE 条件啦，字符类型字段查询时条件必须加单引号，如果是数值类型就可以直接写，所以数值类型的分类字段更容易实现一些，更可以融合在一个过程中。通常大家看到的交叉表都有行汇总与列汇总等信息，本例就没有实现，最后一点工作大家自己练练手吧。 58 、有表结构如下，要求对部门进行汇总，把 ysdm 相同的 fs 相加， 得到右图所示： bmbm   ysdm   fs0301   307  10000302   307  20000301   308  1000302   308  2000301   309  200  这样的语法 SQL 与 ORACLE 如何实现呢？ SELECT SUBSTRING(BMBM,1,2) ,YSDM,SUM(FS)  FROM A  GROUP SUBSTRING(BMBM,1,2) ,YSDM-------------------------------------------------------------------------------------------------------------------SELECT SUBSTR(BMBM,1,2) ,YSDM,SUM(FS)  FROM A  GROUP BY SUBSTR(BMBM,1,2) ,YSDM  原贴地址： HTTP://WWW.DELPHIBBS.COM/DELPHIBBS/DISPQ.ASP?LID=1929393  59 、关于 ORACLE 的 DBMS_TTS 包的使用？  DBMS_TTS 是主要用于检测要传输的表空间是否是自包含的，有二个重要的存储对象。  PROCEDURE TRANSPORT_SET_CHECK FUNCTION ISSELFCONTAINED RETURNS BOOLEAN  在用 EXP 执行导出表空间时，要用上面两个存储对象啦， TRANSPORT_SET_CHECK 执行后，不符合传输的表被放到 TRANSPORT_SET_VIOLATIONS 表。 ISSELFCONTAINED 返回值是 TRUE ，表示是自包含的，可以用 EXP 导出表空间，否则不行。 SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('USERS');SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;第二种方法就是使用 ISSELFCONTAINED 检测表空间的自包含性： DECLAREIC BOOLEAN;FC BOOLEAN;RSLT BOOLEAN;BEGINIC := TRUE;FC := TRUE;RSLT := DBMS_TTS.ISSELFCONTAINED('USERS',IC,FC);END;EXP SYS/SYS AS SYSDBA FILE=USERS.DMP TRANSPORT_TABLESPACE=Y  TABLESPACES=SALES_TS TRIGGERS=N CONSTRAINTS=NIMP SYS/SYS AS SYSDBA FILE=USERS.DMP TRANSPORT_TABLESPACE=Y DATAFILES=(/DISK1/SALES01.DBF, /DISK2/SALES02.DBF)说明：如果 RSLT 返回 TRUE 就可以用 EXP 导出啦，请看 EXP 导出表空间的语法。导入导出表空间时要注意，必须使用 SYS AS SYSDBA 的权限，而且在导入时，不仅要拷贝 *.DMP, 而且还要拷贝导出表空间的相关数据文件。  60 、关于 MS SQL SERVER 的扩展存储过程的用法？   如何将 SQL 的查询结果保存到 OS 的文件中？ DECLARE @CMD SYSNAME, @VAR SYSNAMESET @VAR = 'DIR /P'SET @CMD = 'ECHO ' + @VAR + ' > DIR_OUT.TXT'EXEC MASTER..XP_CMDSHELL @CMD , NO_OUTPUT  说明：必须把查询数据用游标组织成一个字符串或 CLOB 字段，然后写入 *.TXT 中。 DECLARE @STRRETURN VARCHAR(500) EXEC @STRRETURN = MASTER..XP_CMDSHELL 'CD C:/WINNT' IF @STRRETURN='0'    PRINT 'THE PATH EXISTS.' ELSE    PRINT 'THE PATH DOESN''T EXIST.'说明： SQL 的文件 I/O 功能很弱，但可以通过扩展存储过程来实现，用户也可以自己开发扩展存储过程来实现 SQL 的功能。上边第一例是输出数据到 OS 的文件，另一例是判断磁盘上的目录是否存在， NO_OUTPUT 选项限制不产生输出信息。