SQL與Oracle對比

網上已經有很多SQL與ORACLE的對比 ，但本人要講的即不是單純的SQL，也不是單純的Oracle，更不是評價誰好誰壞（意思不大），而是兩種數據庫之相同和異同，本人曾講授過SQL與ORACLE的課程，講SQL時說SQL好，講ORACLE時又說Oracle棒，現在終於可以平心而評啦。
估計有人現在會嘿嘿冷笑（又一個誤人子弟的騙子），老實說，當初每次講完課，就有這種感覺—教的人不得其法，學的人不得其道。說點嚴肅的事吧，據說比爾與艾裡森在洗手間相遇，兩個又是擁抱，又是KISS，不多久就吵了起來，比爾對查詢分析器（SQL QUERY ANALYZE）贊不經絕口，艾裡森嘿嘿冷笑，只說了一句話—SQL PLUS內秀。
言歸正傳，且聽我一一道來
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);<br />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的列的個數，數據如下：
———————————————&mdash;————————————————
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 &nbsp;
說明 本例摘自WWW.DELPHIBBS.COM，有名的Delphi開發網站，本人不擁有版權。該SQL的實現方法與Oracle的實現寫法完全一樣，不在多述。
 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.日期
 說明 Oracle的寫法與SQL一樣,本例也摘自WWW.DelphiBBS.COM，本人不擁有版權。
 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 font color="#CC0000" target=_blank>收藏本篇文章 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] [21] [22] [23] [24] [25]

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)
說明 用過程可以方便地對XML進行操作，但編寫成FUNCTION時就報錯，大概MS的函數內部不充許執行OPENXML等這類行集函數。另一個重要的問題是，SQL的這種語法竟然不支持漢字字串，真是要命。
38、使用DBMS_REPAIR檢測與修復破損的BLOCK？
ADMIN_TABLES  提供管理函數修復或孤立關鍵表，包含創建、淨化與刪除函數。
CHECK_OBJECT  檢測並報告表或索引的破損塊。
DUMP_ORPHAN_KEYS  導出破損塊的數據
FIX_CORRUPT_BLOCKS  在CHECK_OBJECT檢測出的破損塊上做標記
REBUILD_FREELISTS  重建對象的FREELISTS
SKIP_CORRUPT_BLOCKS  設置在表或索引掃描時是否不掃描被做了破損標記的塊。
SEGMENT_FIX_STATUS  整理BITMAP實體上的破損標志
 上表列舉了DBMS_REPAIR包所有的過程，下邊將對這些過程要引入的參數的枚舉值進行
說明，這引些參數將在過程應用中起決定作用。
object_type  TABLE_OBJECT, INDEX_OBJECT, CLUSTER_OBJECT
action  CREATE_ACTION, DROP_ACTION, PURGE_ACTION
table_type  REPAIR_TABLE, ORPHAN_TABLE
flags  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;

>    /
創建JOB
SQL> VARIABLE JOB1 NUMBER;
SQL>
SQL> BEGIN
DBMS_JOB.SUBMIT(:JOB1,''UP_TEST;'',SYSDATE,''SYSDATE+1/(24*60)'');　　
--每天1440分鐘，即一分鐘運行TEST過程一次
    END;
    /
運行JOB
SQL> 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:21
2001/01/07 23:52:22
2001/01/07 23:53:24
刪除JOB
SQL> 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的表的情況
    &nbsp; 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 DUAL
43、SQL與Oracle取系統時間的方法
SELECT GETDATE（）
-------------------------------------------
SELECT TO_CHAR(SYSDATE,

''YYYY-MM-DD HH24:MM:SS'') FROM DUAL
44、關於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     1
001   abc   12:01     2
001   abc   13:28     3
001   abc   17:40     4
002   def    07:30    1
002   def    22:59    2
SQL的解決方法如下：
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
AS
BEGIN
  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 INT
SELECT @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 M
END

EXEC 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                 &nbsp;                 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…THEN
INSERT 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 BBB
AS
SELECT * 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|SCHEMA
TRIGGER 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_TABLES
INPUT WHERE TABLE_NAME=''EMP'';
 SQL> DECLARE
FBLKS 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  4
B  2  3  4  6
C  5  7  10  11
最後的出的是
A  1  2  3  4  10
B  2  3  4  6  15
C  5  7  10  11  33
總數  8  12  17  21  58
CREATE 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_SUM
UNION
SELECT ''匯總'',SUM(I1), SUM(I2),SUM(I1)+SUM(I2) FROM A_SUM
55、表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 VALUE

S(''廣州-長沙-武漢'');
INSERT INTO A VALUES(''武漢-長沙-廣州'');
INSERT INTO A VALUES(''北京-東京'');

CREATE OR REPLACE FUNCTION NORMALIZE(STR VARCHAR2) RETURN VARCHAR2
AS
  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;