1. DB2 編程
1.1 建存儲(chǔ)過(guò)程時(shí) Create  后一定不要用 TAB 鍵
create procedure
的 create 后只能用空格 , 而不可用 tab 健，否則編譯會(huì)通不過(guò)。
切記，切記。

1.2 使用臨時(shí)表

   要注意，臨時(shí)表只能建在 user tempory tables space  上，如果 database 只有 system tempory table space 是不能建臨時(shí)表的。
   另外， DB2 的臨時(shí)表和 sybase 及 oracle 的臨時(shí)表不太一樣， DB2 的臨時(shí)表是在一個(gè) session 內(nèi)有效的。所以，如果程序有多線程，最好不要用臨時(shí)表，很難控制。
    建臨時(shí)表時(shí)最好加上   with  replace 選項(xiàng)，這樣就可以不顯示的 drop  臨時(shí)表，建臨時(shí)表時(shí)如果不加該選項(xiàng)而該臨時(shí)表在該 session 內(nèi)已創(chuàng)建且沒(méi)有 drop, 這時(shí)會(huì)發(fā)生錯(cuò)誤。
1.3 從數(shù)據(jù)表中取指定前幾條記錄
select  *  from tb_market_code fetch first 1 rows only

但下面這種方式不允許
select market_code into v_market_code 
        from tb_market_code fetch first 1 rows only;     
    
選第一條記錄的字段到一個(gè)變量以以下方式代替
    declare v_market_code char(1);
    declare cursor1 cursor for select market_code from tb_market_code 
fetch first 1 rows only for update;
    open cursor1;
    fetch cursor1 into v_market_code;
    close cursor1;

1.4 游標(biāo)的使用
注意 commit 和 rollback
使用游標(biāo)時(shí)要特別注意如果沒(méi)有加 with hold  選項(xiàng) , 在 Commit 和 Rollback 時(shí) , 該游標(biāo)將被關(guān)閉。 Commit  和 Rollback 有很多東西要注意。特別小心

游標(biāo)的兩種定義方式
一種為
declare continue handler for not found
   begin
     set v_notfound = 1;
   end;

declare cursor1 cursor with hold for select market_code from tb_market_code  for update;
open cursor1;
set v_notfound=0;
fetch cursor1 into v_market_code;
while v_notfound=0 Do
--work
set v_notfound=0;
fetch cursor1 into v_market_code;
end while;
close cursor1;
這種方式使用起來(lái)比較復(fù)雜，但也比較靈活。特別是可以使用 with hold  選項(xiàng)。如果循環(huán)內(nèi)有 commit 或 rollback  而要保持該 cursor 不被關(guān)閉，只能使用這種方式。

另一種為
      pcursor1: for loopcs1 as  cousor1  cursor  as
select  market_code  as market_code
           from tb_market_code
           for update
        do
        end for;
        這種方式的優(yōu)點(diǎn)是比較簡(jiǎn)單，不用（也不允許）使用 open,fetch,close 。
   但不能使用 with  hold  選項(xiàng)。如果在游標(biāo)循環(huán)內(nèi)要使用 commit,rollback 則不能使用這種方式。如果沒(méi)有 commit 或 rollback 的要求，推薦使用這種方式 ( 看來(lái) For 這種方式有問(wèn)題 ) 。

修改游標(biāo)的當(dāng)前記錄的方法
update tb_market_code set market_code='0' where current of cursor1;
不過(guò)要注意將 cursor1 定義為可修改的游標(biāo)
  declare cursor1 cursor for select market_code from tb_market_code 
for update;

for update  不能和 GROUP BY 、  DISTINCT 、  ORDER BY 、  FOR READ ONLY 及 UNION, EXCEPT, or INTERSECT 但  UNION ALL 除外）一起使用。



1.5 類似 decode 的轉(zhuǎn)碼操作
oracle 中有一個(gè)函數(shù)  select decode(a1,'1','n1','2','n2','n3') aa1 from
db2 沒(méi)有該函數(shù)，但可以用變通的方法
select case a1 
when '1' then 'n1' 
when '2' then 'n2' 
else 'n3'
    end as aa1 from

1.6 類似 charindex 查找字符在字串中的位置
Locate(‘y’,’dfdasfay’)
查找 ’y’  在 ’dfdasfay’ 中的位置。

1.7 類似 datedif 計(jì)算兩個(gè)日期的相差天數(shù)
days(date(‘2001-06-05’)) – days(date(‘2001-04-01’))
days  返回的是從   0001-01-01  開(kāi)始計(jì)算的天數(shù)
1.8 寫(xiě) UDF 的例子
C 寫(xiě)見(jiàn) sqllib\samples\cli\udfsrv.c

1.9 創(chuàng)建含 identity 值 ( 即自動(dòng)生成的 ID) 的表
建這樣的表的寫(xiě)法
CREATE TABLE test
     (t1 SMALLINT NOT NULL
        GENERATED ALWAYS AS IDENTITY
        (START WITH 500, INCREMENT BY 1),
      t2 CHAR(1));
在一個(gè)表中只允許有一個(gè) identity 的 column.

 

1.10 預(yù)防字段空值的處理
SELECT DEPTNO ,DEPTNAME ,COALESCE(MGRNO ,'ABSENT'),ADMRDEPT
FROM DEPARTMENT
   COALESCE 函數(shù)返回 () 中表達(dá)式列表中第一個(gè)不為空的表達(dá)式，可以帶多個(gè)表達(dá)式。
    和 sqlserver 的 isnull 類似，但 isnull 好象只能兩個(gè)表達(dá)式； oracle 為 NVL 。
     

1.11 取得處理的記錄數(shù)
declare v_count int;
update tb_test set t1=’0’
where t2=’2’;
-- 檢查修改的行數(shù) , 判斷指定的記錄是否存在
get diagnostics v_ count=ROW_COUNT;     
只對(duì) update,insert,delete 起作用 .
不對(duì) select into  有效


1.12 從存儲(chǔ)過(guò)程返回結(jié)果集(游標(biāo))的用法
1.12.1 建一 sp 返回結(jié)果集
CREATE PROCEDURE DB2INST1.Proc1 (  )
    LANGUAGE SQL
    result sets 2( 返回兩個(gè)結(jié)果集 )
------------------------------------------------------------------------
-- SQL  存儲(chǔ)過(guò)程  
------------------------------------------------------------------------
P1: BEGIN
        declare c1 cursor  with return to caller for 
            select  market_code
            from    tb_market_code;
        -- 指定該結(jié)果集用于返回給調(diào)用者
        declare c2 cursor  with return to caller for 
            select  market_code
            from    tb_market_code;
         open c1;
         open c2;
END P1                                       


1.12.2 建一 SP 調(diào)該 sp 且使用它的結(jié)果集

CREATE PROCEDURE DB2INST1.Proc2 (
out out_market_code char(1))
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL  存儲(chǔ)過(guò)程  
------------------------------------------------------------------------
P1: BEGIN

 declare loc1,loc2 result_set_locator varying; 
-- 建立一個(gè)結(jié)果集數(shù)組
call proc1;
-- 調(diào)用該 SP 返回結(jié)果集。
associate result set locator(loc1,loc2) with procedure proc1;
-- 將返回結(jié)果集和結(jié)果集數(shù)組關(guān)聯(lián)
 allocate cursor1 cursor for result set loc1;
 allocate cursor2 cursor for result set loc2;
-- 將結(jié)果集數(shù)組分配給 cursor
fetch  cursor1 into out_market_code;
-- 直接從結(jié)果集中賦值
close cursor1;         

END P1

1.12.3 動(dòng)態(tài) SQL 寫(xiě)法
     DECLARE CURSOR C1 FOR STMT1; 
     PREPARE STMT1 FROM
        'ALLOCATE C2 CURSOR FOR RESULT SET ?';
1.12.4 注意：
(1) 如果一個(gè) sp 調(diào)用好幾次，只能取到最近一次調(diào)用的結(jié)果集。  
(2) allocate 的 cursor 不能再次 open ，但可以 close ，是 close sp 中的對(duì)應(yīng) cursor 。

1.13 類型轉(zhuǎn)換函數(shù)
select cast ( current time as char(8)) from tb_market_code

1.14 存儲(chǔ)過(guò)程的互相調(diào)用
目前 ,c sp 可以互相調(diào)用。
Sql sp  可以互相調(diào)用，
Sql sp  可以調(diào)用 C sp ，
但 C sp  不可以調(diào)用 Sql sp( 最新的說(shuō)法是可以 )

1.15 C 存儲(chǔ)過(guò)程參數(shù)注意
create procedure pr_clear_task_ctrl(
IN IN_BRANCH_CODE char(4),
              IN IN_TRADEDATE   char(8),
           IN IN_TASK_ID     char(2),
       IN IN_SUB_TASK_ID char(4),
       OUT OUT_SUCCESS_FLAG INTEGER )
 
DYNAMIC RESULT SETS 0
LANGUAGE C 
PARAMETER STYLE GENERAL WITH NULLS( 如果不是這樣， sql  的 sp 將不能調(diào)用該用 c 寫(xiě)的存儲(chǔ)過(guò)程，產(chǎn)生保護(hù)性錯(cuò)誤 )
NO DBINFO
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'pr_clear_task_ctrl!pr_clear_task_ctrl'@

 

1.16 存儲(chǔ)過(guò)程 fence 及 unfence
fence 的存儲(chǔ)過(guò)程單獨(dú)啟用一個(gè)新的地址空間 , 而 unfence 的存儲(chǔ)過(guò)程和調(diào)用它的進(jìn)程使用同一個(gè)地址空間。
一般而言， fence 的存儲(chǔ)過(guò)程比較安全。
但有時(shí)一些特殊的要求，如要取調(diào)用者的 pid ，則 fence 的存儲(chǔ)過(guò)程會(huì)取不到，而只有 unfence 的能取到。

1.17 SP 錯(cuò)誤處理用法
如果在 SP 中調(diào)用其它的有返回值的，包括結(jié)果集、臨時(shí)表和輸出參數(shù)類型的 SP ，
DB2 會(huì)自動(dòng)發(fā)出一個(gè) SQLWarning 。而在我們?cè)瓉?lái)的處理中對(duì)于 SQLWarning 都
會(huì)插入到日志，這樣子最后會(huì)出現(xiàn)多條 SQLCODE=0 的警告信息。
處理辦法：
定義一個(gè)標(biāo)志變量，比如 DECLARE V_STATUS INTEGER DEFAULT 0,
在 CALL SPNAME 之后 , SET V_STATUS = 1,
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
IF V_STATUS <> 1 THEN
-- 警告處理，插入日志
SET V_STATUS = 0;
END IF;
END;
1.18 import 用法
db2 import  from  gh1.out   of  DEL messages err.txt insert into  db2inst1.tb_dbf_match_ha

注意要加 schma

1.19 values 的使用
如果有多個(gè)  set   語(yǔ)句給變量付值，最好使用 values 語(yǔ)句，改寫(xiě)為一句。這樣可以提高效率。
 
但要注意， values 不能將 null 值付給一個(gè)變量。
values(null) into out_return_code;
這個(gè)語(yǔ)句會(huì)報(bào)錯(cuò)的。


1.20 給 select  語(yǔ)句指定隔離級(jí)別
select * from tb_head_stock_balance with ur
 
1.21 atomic 及 not atomic 區(qū)別
atomic 是將該部分程序塊指定為一個(gè)整體 , 其中任何一個(gè)語(yǔ)句失敗 , 則整個(gè)程序塊都相當(dāng)于沒(méi)做 , 包括包含在 atomic 塊內(nèi)的已經(jīng)執(zhí)行成功的語(yǔ)句也相當(dāng)于沒(méi)做，有點(diǎn)類似于 transaction 。

1.22 日期和時(shí)間的使用
要使用 SQL 獲得當(dāng)前的日期、時(shí)間及時(shí)間戳記，請(qǐng)參考適當(dāng)?shù)?/span> DB2 寄存器：

SELECT current date FROM sysibm.sysdummy1

SELECT current time FROM sysibm.sysdummy1

SELECT current timestamp FROM sysibm.sysdummy1

sysibm.sysdummy1 表是一個(gè)特殊的內(nèi)存中的表，用它可以發(fā)現(xiàn)如上面演示的 DB2 寄存器的值。您也可以使用關(guān)鍵字 VALUES 來(lái)對(duì)寄存器或表達(dá)式求值。例如，在 DB2 命令行處理器（ Command Line Processor ， CLP ）上，以下 SQL 語(yǔ)句揭示了類似信息：

VALUES current date

VALUES current time

VALUES current timestamp

在余下的示例中，我將只提供函數(shù)或表達(dá)式，而不再重復(fù) SELECT ... FROM sysibm.sysdummy1 或使用 VALUES 子句。

要使當(dāng)前時(shí)間或當(dāng)前時(shí)間戳記調(diào)整到 GMT/CUT ，則把當(dāng)前的時(shí)間或時(shí)間戳記減去當(dāng)前時(shí)區(qū)寄存器：

current time - current timezone

current timestamp - current timezone

給定了日期、時(shí)間或時(shí)間戳記，則使用適當(dāng)?shù)暮瘮?shù)可以單獨(dú)抽取出（如果適用的話）年、月、日、時(shí)、分、秒及微秒各部分：

YEAR (current timestamp)

MONTH (current timestamp)

DAY (current timestamp)

HOUR (current timestamp)

MINUTE (current timestamp)

SECOND (current timestamp)

MICROSECOND (current timestamp)

從時(shí)間戳記單獨(dú)抽取出日期和時(shí)間也非常簡(jiǎn)單：

DATE (current timestamp)

TIME (current timestamp)

因?yàn)闆](méi)有更好的術(shù)語(yǔ)，所以您還可以使用英語(yǔ)來(lái)執(zhí)行日期和時(shí)間計(jì)算：

current date + 1 YEAR

current date + 3 YEARS + 2 MONTHS + 15 DAYS

current time + 5 HOURS - 3 MINUTES + 10 SECONDS

要計(jì)算兩個(gè)日期之間的天數(shù)，您可以對(duì)日期作減法，如下所示：

days (current date) - days (date('1999-10-22'))

而以下示例描述了如何獲得微秒部分歸零的當(dāng)前時(shí)間戳記：

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

如果想將日期或時(shí)間值與其它文本相銜接，那么需要先將該值轉(zhuǎn)換成字符串。為此，只要使用 CHAR() 函數(shù)：

char(current date)

char(current time)

char(current date + 12 hours)

要將字符串轉(zhuǎn)換成日期或時(shí)間值，可以使用：

TIMESTAMP ('2002-10-20-12.00.00.000000')

TIMESTAMP ('2002-10-20 12:00:00')

          DATE ('2002-10-20')

          DATE ('10/20/2002')

          TIME ('12:00:00')

          TIME ('12.00.00')

TIMESTAMP() 、 DATE() 和 TIME() 函數(shù)接受更多種格式。上面幾種格式只是示例，我將把它作為一個(gè)練習(xí)，讓讀者自己去發(fā)現(xiàn)其它格式。

 

 

 

 

 

 

 

 

 

 

 

 

 

日期函數(shù)

有時(shí)，您需要知道兩個(gè)時(shí)間戳記之間的時(shí)差。為此， DB2 提供了一個(gè)名為 TIMESTAMPDIFF() 的內(nèi)置函數(shù)。但該函數(shù)返回的是近似值，因?yàn)樗豢紤]閏年，而且假設(shè)每個(gè)月只有 30 天。以下示例描述了如何得到兩個(gè)日期的近似時(shí)差：

timestampdiff (<n>, char(

          timestamp('2002-11-30-00.00.00')-

          timestamp('2002-11-08-00.00.00')))

對(duì)于 <n> ，可以使用以下各值來(lái)替代，以指出結(jié)果的時(shí)間單位：

• 1 = 秒的小數(shù)部分
• 2 = 秒
• 4 = 分
• 8 = 時(shí)
• 16 = 天
• 32 = 周
• 64 = 月
• 128 = 季度
• 256 = 年

當(dāng)日期很接近時(shí)使用 timestampdiff() 比日期相差很大時(shí)精確。如果需要進(jìn)行更精確的計(jì)算，可以使用以下方法來(lái)確定時(shí)差（按秒計(jì)）：

(DAYS(t1) - DAYS(t2)) * 86400 +  

(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

為方便起見(jiàn)，還可以對(duì)上面的方法創(chuàng)建 SQL 用戶定義的函數(shù)：

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP)

RETURNS INT

RETURN (

(DAYS(t1) - DAYS(t2)) * 86400 + 

(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

)

@

如果需要確定給定年份是否是閏年，以下是一個(gè)很有用的 SQL 函數(shù)，您可以創(chuàng)建它來(lái)確定給定年份的天數(shù)：

CREATE FUNCTION daysinyear(yr INT)

RETURNS INT

RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE

        CASE (mod(yr, 4))   WHEN 0 THEN

        CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END

        ELSE 365 END

          END)@

最后，以下是一張用于日期操作的內(nèi)置函數(shù)表。它旨在幫助您快速確定可能滿足您要求的函數(shù)，但未提供完整的參考。有關(guān)這些函數(shù)的更多信息，請(qǐng)參考 SQL 參考大全。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

改變?nèi)掌诟袷?/span>

在日期的表示方面，這也是我經(jīng)常碰到的一個(gè)問(wèn)題。用于日期的缺省格式由數(shù)據(jù)庫(kù)的地區(qū)代碼決定，該代碼在數(shù)據(jù)庫(kù)創(chuàng)建的時(shí)候被指定。例如，我在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)使用 territory=US 來(lái)定義地區(qū)代碼，則日期的格式就會(huì)像下面的樣子：

values current date

1

----------

05/30/2003

 

1 record(s) selected.

也就是說(shuō)，日期的格式是 MM/DD/YYYY. 如果想要改變這種格式，你可以通過(guò)綁定特定的 DB2 工具包來(lái)實(shí)現(xiàn) . 其他被支持的日期格式包括 :

 

 

 

 

 

在 Windows 環(huán)境下，要將缺省的日期和時(shí)間格式轉(zhuǎn)化成 ISO 格式（ YYYY-MM-DD ），執(zhí)行下列操作：

1.       在命令行中，改變當(dāng)前目錄為 sqllibbnd 。

例如 :
在 Windows 環(huán)境 : c:program filesIBMsqllibbnd
在 UNIX 環(huán)境 : /home/db2inst1/sqllib/bnd

2.       從操作系統(tǒng)的命令行界面中用具有 SYSADM 權(quán)限的用戶連接到數(shù)據(jù)庫(kù) :

3.             db2 connect to DBNAME

4.             db2 bind @db2ubind.lst datetime ISO blocking all grant public

( 在你的實(shí)際環(huán)境中， 用你的數(shù)據(jù)庫(kù)名稱和想使用的日期格式分別來(lái)替換 DBNAME and ISO 。 )

現(xiàn)在，你可以看到你的數(shù)據(jù)庫(kù)已經(jīng)使用 ISO 作為日期格式了：

values current date

1

----------

2003-05-30

 

  1 record(s) selected.

 

定制日期 / 時(shí)間格式

在上面的例子中，我們展示了如何將 DB2 當(dāng)前的日期格式轉(zhuǎn)化成系統(tǒng)支持的特定格式。但是，如果你想將當(dāng)前日期格式轉(zhuǎn)化成定制的格式（比如 ‘yyyymmdd’ ），那又該如何去做呢？按照我的經(jīng)驗(yàn)，最好的辦法就是編寫(xiě)一個(gè)自己定制的格式化函數(shù)。

下面是這個(gè) UDF 的代碼 :

create function ts_fmt(TS timestamp, fmt varchar(20))

returns varchar(50)

return

with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as

(

    select

    substr( digits (day(TS)),9),

    substr( digits (month(TS)),9) ,

    rtrim(char(year(TS))) ,

    substr( digits (hour(TS)),9),

    substr( digits (minute(TS)),9),

    substr( digits (second(TS)),9),

    rtrim(char(microsecond(TS)))

    from sysibm.sysdummy1

    )

select

case fmt

    when 'yyyymmdd'

        then yyyy || mm || dd

    when 'mm/dd/yyyy'

        then mm || '/' || dd || '/' || yyyy

    when 'yyyy/dd/mm hh:mi:ss'

        then yyyy || '/' || mm || '/' || dd || ' ' ||

               hh || ':' || mi || ':' || ss

    when 'nnnnnn'

        then nnnnnn

    else

        'date format ' || coalesce(fmt,'  ') ||

        ' not recognized.'

    end

from tmp

乍一看，函數(shù)的代碼可能顯得很復(fù)雜，但是在仔細(xì)研究之后，你會(huì)發(fā)現(xiàn)這段代碼其實(shí)非常簡(jiǎn)單而且很優(yōu)雅。最開(kāi)始，我們使用了一個(gè)公共表表達(dá)式（ CTE ）來(lái)將一個(gè)時(shí)間戳記（第一個(gè)輸入?yún)?shù)）分別剝離為單獨(dú)的時(shí)間元素。然后，我們檢查提供的定制格式（第二個(gè)輸入?yún)?shù)）并將前面剝離出的元素按照該定制格式的要求加以組合。

這個(gè)函數(shù)還非常靈活。如果要增加另外一種模式，可以很容易地再添加一個(gè) WHEN 子句來(lái)處理。在使用過(guò)程中，如果用戶提供的格式不符合任何在 WHEN 子句中定義的任何一種模式時(shí)，函數(shù)會(huì)返回一個(gè)錯(cuò)誤信息。

使用方法示例：

values ts_fmt(current timestamp,'yyyymmdd')

 '20030818'

values ts_fmt(current timestamp,'asa')

 'date format asa not recognized.

 

 

2  DB2 編程性能注意
2.1 大數(shù)據(jù)的導(dǎo)表
應(yīng)該是 export 后再 load 性能更好，因?yàn)?/span> load 不寫(xiě)日志。
比 select into  要好。

2.2 SQL 語(yǔ)句盡量寫(xiě)復(fù)雜 SQL
    盡量使用大的復(fù)雜的 SQL 語(yǔ)句 , 將多而簡(jiǎn)單的語(yǔ)句組合成大的 SQL 語(yǔ)句對(duì)性能會(huì)有所改善。
   DB2 的 SQL Engieer 對(duì)復(fù)雜語(yǔ)句的優(yōu)化能力比較強(qiáng)，基本上不用當(dāng)心語(yǔ)句的性能問(wèn)題。
Oracle  則相反，推薦將復(fù)雜的語(yǔ)句簡(jiǎn)單化， SQL Engieer 的優(yōu)化能力不是特別好。
這是因?yàn)槊恳粋€(gè) SQL 語(yǔ)句都會(huì)有 reset SQLCODE 和 SQLSTATE 等各種操作，會(huì)對(duì)數(shù)據(jù)庫(kù)性能有所消耗。
一個(gè)總的思想就是盡量減少 SQL 語(yǔ)句的個(gè)數(shù)。
2.3 SQL  SP 及 C SP 的選擇
首先， C 的 sp 的性能比 sql  的 sp  的要高。
一般而言， SQL 語(yǔ)句比較復(fù)雜，而邏輯比較簡(jiǎn)單， sql sp  與  c sp  的性能差異會(huì)比較小，這樣從工作量考慮，用 SQL 寫(xiě)比較好。
而如果邏輯比較復(fù)雜， SQL 比較簡(jiǎn)單，用 c 寫(xiě)比較好。

2.4 查詢的優(yōu)化 (HASH 及 RR_TO_RS)
db2set  DB2_HASH_JOIN=Y (HASH 排序優(yōu)化 )
    指定排序時(shí)使用 HASH 排序，這樣 db2 在表 join 時(shí)，先對(duì)各表做 hash 排序，再 join ，這樣可以大大提高性能。
    劇沈剛說(shuō)做實(shí)驗(yàn)， 7 個(gè)一千萬(wàn)條記錄表的做 join 取 10000 條記錄，再?zèng)]有索引的情況下   72 秒。

db2set  DB2_RR_TO_RS=Y       
  該設(shè)置后，不能定義 RR 隔離級(jí)別，如果定義 RR ， db2 也會(huì)自動(dòng)降為 RS.
這樣， db2 不用管理 Next key ，可以少管理一些東西，這樣可以提高性能。      


2.5 避免使用 count(*)  及 exists 的方法
1 、首先要避免使用 count(*) 操作，因?yàn)?/span> count(*) 基本上要對(duì)表做全部掃描一遍，如果使用很多會(huì)導(dǎo)致很慢。
2 、 exists 比 count(*) 要快，但總的來(lái)說(shuō)也會(huì)對(duì)表做掃描，它只是碰到第一條符合的記錄就停下來(lái)。

如果做這兩中操作的目的是為
       select into  服務(wù)的話，就可以省略掉這兩步。
直接使用 select into  選擇記錄中的字段。

如果是沒(méi)有記錄選擇到的話， db2  會(huì)將   sqlcode=100  和  sqlstate=’20000’
如果是有多條記錄的話， db2 會(huì)產(chǎn)生一個(gè)錯(cuò)誤。

程序可以創(chuàng)建   continue handler for  exception 
              continue handler for  not found
來(lái)檢測(cè)。
這是最快速的方法。

3 、如果是判斷是不是一條 , 可以使用游標(biāo)來(lái)計(jì)算，用一個(gè)計(jì)數(shù)器，累加，達(dá)到預(yù)定值后就離開(kāi)。這個(gè)速度也比 count(*)  要快，因?yàn)樗灰獟呙璧筋A(yù)定值就不再掃描了，不用做全表的 scan ，不過(guò)它寫(xiě)起來(lái)比較麻煩。


3 DB2 表及 sp 管理
3.1 看存儲(chǔ)過(guò)程文本
select text from syscat.procedures where procname='PROC1';
3.2 看表結(jié)構(gòu)
describe table syscat.procedures
describe select * from syscat.procedures

3.3 查看各表對(duì) sp 的影響 ( 被哪些 sp 使用 )
select PROCNAME from SYSCAT.PROCEDURES where SPECIFICNAME in(select dname from sysibm.sysdependencies where bname in ( select PKGNAME  from syscat.packagedep where bname='TB_BRANCH'))

3.4 查看 sp 使用了哪些表
select bname from syscat.packagedep where btype='T' and pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='PR_CLEAR_MATCH_DIVIDE_SHA'))
3.5 查看 function 被哪些 sp 使用
select PROCNAME from SYSCAT.PROCEDURES where SPECIFICNAME in(select dname from sysibm.sysdependencies where bname in ( select PKGNAME  from syscat.packagedep where bname   in  (select SPECIFICNAME from SYSCAT.functions where funcname='GET_CURRENT_DATE')))


使用 function 時(shí)要注意，如果想 drop  掉該 function 必須要先將調(diào)用該 function 的其它存儲(chǔ)過(guò)程全部 drop 掉。
必須先創(chuàng)建 function ，調(diào)用該 function 的 sp 才可以創(chuàng)建成功。
3.6 修改表結(jié)構(gòu)
一次給一個(gè)表增加多個(gè)字段
db2 "alter table tb_test add column t1 char(1) add column t2 char(2) add column t3 int"


4 DB2 系統(tǒng)管理
4.1 DB2 安裝
   在 Windows 98  下安裝 db2 7.1  或其他版本，如果有 Jdbc 錯(cuò)誤或者是 Windwos 98 不能啟動(dòng)，則將 autoexec.bat  中的內(nèi)容用如下內(nèi)容替換：


C:\PROGRA~1\TRENDP~1\PCSCAN.EXE C:\ C:\WINDOWS\COMMAND\ /NS /WIN95 
rem C:\WINDOWS\COMMAND.COM /E:32768
REM [Header]

REM [CD-ROM Drive]

REM [Miscellaneous]

REM [Display]

set PATH=%PATH%;C:\MSSQL\BINN;C:\PROGRA~1\SQLLIB\BIN;C:\PROGRA~1\SQLLIB\FUNCTION;C:\PROGRA~1\SQLLIB\SAMPLES\REPL;C:\PROGRA~1\SQLLIB\HELP
IF EXIST C:\PROGRA~1\IBM\IMNNQ\IMQENV.BAT CALL C:\PROGRA~1\IBM\IMNNQ\IMQENV.BAT
IF EXIST C:\PROGRA~1\IBM\IMNNQ\IMNENV.BAT CALL C:\PROGRA~1\IBM\IMNNQ\IMNENV.BAT
set DB2INSTANCE=DB2
set CLASSPATH=.;C:\PROGRA~1\SQLLIB\java\db2java.zip;C:\PROGRA~1\SQLLIB\java\runtime.zip;C:\PROGRA~1\SQLLIB\java\sqlj.zip;C:\PROGRA~1\SQLLIB\bin
set MDIS_PROFILE=C:\PROGRA~1\SQLLIB\METADATA\PROFILES
set LC_ALL=ZH_CN
set INCLUDE=C:\PROGRA~1\SQLLIB\INCLUDE;C:\PROGRA~1\SQLLIB\LIB;C:\PROGRA~1\SQLLIB\TEMPLATES\INCLUDE
set LIB=C:\PROGRA~1\SQLLIB\LIB
set DB2PATH=C:\PROGRA~1\SQLLIB
set DB2TEMPDIR=C:\PROGRA~1\SQLLIB
set VWS_TEMPLATES=C:\PROGRA~1\SQLLIB\TEMPLATES
set VWS_LOGGING=C:\PROGRA~1\SQLLIB\LOGGING
set VWSPATH=C:\PROGRA~1\SQLLIB
set VWS_FOLDER=IBM DB2
set ICM_FOLDER= 信息目錄管理器

win

4.2 創(chuàng)建 Database
create database head using codeset IBM-eucCN territory CN;
這樣可以支持中文。


4.3 手工做數(shù)據(jù)庫(kù)遠(yuǎn)程 ( 別名 ) 配置
db2  catalog tcpip  node   node1  remote   172.28.200.200 server  50000
db2  catalog db    head   as     test1 at  node   node1

然后既可使用：
   db2 connect to test1  user …  using …
連上 head 庫(kù)了

4.4 停止啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例
db2start
db2stop (force)


4.5 連接數(shù)據(jù)庫(kù)及看當(dāng)前連接數(shù)據(jù)庫(kù)
連接數(shù)據(jù)庫(kù)
db2  connect to head user db2inst1  using db2inst1

當(dāng)前連接數(shù)據(jù)庫(kù)
db2  connect
4.6 停止啟動(dòng)數(shù)據(jù)庫(kù) head
db2  activate  db  head
db2  deactivate db  head
要注意的是，如果有連接，使用 deactivate db  不起作用。
如果是用 activate db 啟動(dòng)的數(shù)據(jù)庫(kù)，一定要用 deactivate db 才會(huì)停止該數(shù)據(jù)庫(kù)。（當(dāng)然如果是 db2stop 也會(huì)停止）。
使用 activate db ，這樣可以減少第一次連接時(shí)的等待時(shí)間。
Database 如果不是使用 activate db 啟動(dòng)而是通過(guò)連接數(shù)據(jù)庫(kù)而啟動(dòng)的話，當(dāng)所有的連接都退出后， db 也就自動(dòng)停止。

4.7 查看及停止數(shù)據(jù)庫(kù)當(dāng)前的應(yīng)用程序
查看應(yīng)用程序：
db2   list   applications  show  detail 

授權(quán)標(biāo)識(shí)  |  應(yīng)用程序名  |  應(yīng)用程序句柄  |   應(yīng)用程序標(biāo)識(shí)  |  序號(hào) #  |  代理程序  |   協(xié)調(diào)程序  |  狀態(tài)  |   狀態(tài)更改時(shí)間  |  DB  名  | DB  路徑 |                                                      |     節(jié)點(diǎn)號(hào)  |   pid ／線程

其中：

1 、應(yīng)用程序標(biāo)識(shí)的第一部分是應(yīng)用程序的 IP 地址，不過(guò)是已 16 進(jìn)制表示的。
2 、 pid/ 線程即是在 unix 下看到的線程號(hào)。

停止應(yīng)用程序：
db2 "force application(236)"
db2 “force application all”

其中 : 該 236 是查看中的應(yīng)用程序句柄。

4.8 查看本 instance 下有哪些 database
db2 LIST DATABASE DIRECTORY  [ on /home/db2inst1 ]
4.9 查看及更改數(shù)據(jù)庫(kù) head 的配置
請(qǐng)注意，在大多數(shù)情況下，更改了數(shù)據(jù)的配置后，只有在所有的連接全部斷掉后才會(huì)生效。

查看數(shù)據(jù)庫(kù) head 的配制
db2 get db cfg for head


更改數(shù)據(jù)庫(kù) head 的某個(gè)設(shè)置的值
4.9.1 改排序堆的大小
db2 update db cfg for head using SORTHEAP 2048
將排序堆的大小改為 2048 個(gè)頁(yè)面，查詢比較多的應(yīng)用最好將該值設(shè)置比較大一些。
4.9.2 改事物日志的大小
db2 update db cfg for head using  logfilsiz  40000
該項(xiàng)內(nèi)容的大小要和數(shù)據(jù)庫(kù)的事物處理相適應(yīng)，如果事物比較大，應(yīng)該要將該值改大一點(diǎn)。否則很容易處理日志文件滿的錯(cuò)誤。

4.9.3 出現(xiàn)程序堆內(nèi)存不足時(shí)修改程序堆內(nèi)存大小
db2 update db cfg for head using  applheapsz  40000
該值不能太小 , 否則會(huì)沒(méi)有足夠的內(nèi)存來(lái)運(yùn)行應(yīng)用程序。

4.10 查看及更改數(shù)據(jù)庫(kù)實(shí)例的配置
查看數(shù)據(jù)庫(kù)實(shí)例配置
db2  get dbm cfg 
更改數(shù)據(jù)庫(kù)實(shí)例配制

4.10.1 打開(kāi)對(duì)鎖定情況的監(jiān)控。
db2 update dbm cfg using dft_mon_lock  on
4.10.2 更改診斷錯(cuò)誤捕捉級(jí)別
db2 update dbm cfg using diaglevel 3
0  為不記錄信息
1  為僅記錄錯(cuò)誤
2  記錄服務(wù)和非服務(wù)錯(cuò)誤
缺省是 3 ，記錄 db2 的錯(cuò)誤和警告
4  是記錄全部信息，包括成功執(zhí)行的信息
一般情況下，請(qǐng)不要用 4 ，會(huì)造成 db2 的運(yùn)行速度非常慢。

 

4.11 db2 環(huán)境變量
db2  重裝后用如下方式設(shè)置 db2 的環(huán)境變量 , 以保證 sp 可編譯
將 set_cpl  放到 AIX 上 , chmod +x set_cpl,  再運(yùn)行之

set_cpl 的內(nèi)容
db2set DB2_SQLROUTINE_COMPILE_COMMAND="xlc_r  -g \
-I$HOME/sqllib/include SQLROUTINE_FILENAME.c \
-bE:SQLROUTINE_FILENAME.exp -e SQLROUTINE_ENTRY \
-o SQLROUTINE_FILENAME -L$HOME/sqllib/lib -lc -ldb2"

db2set DB2_SQLROUTINE_KEEP_FILES=1
4.12 db2 命令環(huán)境設(shè)置
db2=>list command options
db2=>update command options using C off-- 或 on ，只是臨時(shí)改變
db2=>db2set db2options=+c -- 或 -c ，永久改變

4.13 改變隔離級(jí)別
DB2SET DB2_SQLROUTINE_PREPOPTS=CS|RR|RS|UR

交互環(huán)境更改 session 的隔離級(jí)別，
       db2 change isolation  to UR
請(qǐng)注意只有沒(méi)有連接數(shù)據(jù)庫(kù)時(shí)可以這樣來(lái)改變隔離級(jí)別。

4.14 管理 db\instance 的參數(shù)
get db cfg for head(db)
get dbm cfg(instance)

4.15 升級(jí)后消除版本問(wèn)題
db2   bind  @db2ubind.lst
db2   bind   @db2cli.lst

4.16 查看數(shù)據(jù)庫(kù)表的死鎖
再用命令中心查詢數(shù)據(jù)時(shí)要注意 , 如果用了交互式查詢數(shù)據(jù) , 命令中心將會(huì)給所查的記錄加了 s 鎖 . 這時(shí)如果要 update 記錄 , 由于 update 要使用 x 鎖 , 排它鎖 , 將會(huì)處于鎖等待 .

首先 , 將監(jiān)視開(kāi)關(guān)打開(kāi)
db2 update dbm cfg using dft_mon_lock  on
快照
  db2 get snapshot for  Locks  on  cleardb   >snap.log
                    tables 
bufferpools
tablespaces
database
    然后再看 snap.log 中的內(nèi)容即可。
對(duì) Lock 可根據(jù) Application handle （應(yīng)用程序句柄）看每個(gè)應(yīng)用程序的鎖的情況。
  監(jiān)視完畢后，不要忘了將監(jiān)視器關(guān)閉
     db2 update dbm cfg using dft_mon_lock  off

 

 

5. DB2 SQL 概述

5.1 模式

5.1.1 模式是已命名的對(duì)象（如表和視圖）的集合。模式提供了數(shù)據(jù)庫(kù)中對(duì)象的邏輯分類。

5.1.2 當(dāng)在數(shù)據(jù)庫(kù)中創(chuàng)建對(duì)象的時(shí)候，系統(tǒng)就隱性的創(chuàng)建了模式。當(dāng)然，也可以使用 CREATE SCHEMA 顯式的創(chuàng)建模式。

5.1.3 當(dāng)命名對(duì)象的時(shí)候，需要注意對(duì)象的名稱有兩個(gè)部分，即，模式 . 對(duì)象名稱 , 形如： pjj.TempTable1 。如果不顯示指定模式，則系統(tǒng)使用默認(rèn)模式（默認(rèn)用戶的 ID ）。

5.2 數(shù)據(jù)類型

 

定長(zhǎng)字符串　　　　　

CHAR(x) 　　　　　　　　　　　 x 值域（ 1 ～ 254 ）　　　　　　　一個(gè)字節(jié)序列

 

變長(zhǎng)字符串　

VARCHAR(X)

LONG VARCHAR(X)

LOB( 大對(duì)象 )

 

定長(zhǎng)圖形字符串　　

GRAPHCI(X) 　　　　　　　 x 值域（ 1 ～ 127 ）　　　　　　　兩個(gè)字節(jié)序列

 

變長(zhǎng)圖形字符串　　

VARGRAPHCI(X)

LONG GRAPHCI(X)

DBCLOB( 大對(duì)象 )

  BLOB( 大對(duì)象 )

數(shù)字　　　　　　　　　　 ( 所有數(shù)字都有精度，精度是指除符號(hào)位以外的位數(shù)或者數(shù)字?jǐn)?shù) )

SMALLINT 　　　　　　　　　 精度為 5 　　　　　　　　　　　 2 字節(jié)整數(shù)

INTEGER 　　　　　　　　　　精度為 10 　　　　　　　　　　 4 字節(jié)整數(shù)

BIGINT 　　　　　　　　　　　　精度為 19 　　　　　　　　　　 8 字節(jié)整數(shù)

REAL 　　　　　　　　　　　　　實(shí)數(shù)的 32 位近似值

DOUBLE 　　　　　　　　　　 實(shí)數(shù)的 64 位近似值

DECIMAL(P,S) 　　　 P, 精度， S, 小數(shù)位數(shù)，十進(jìn)制數(shù)， P 必須 <=32 ， S 必須 <=P ，缺省 :P=5,S=0

日期時(shí)間型　　　　　 14 位字符串，即非數(shù)字類型也非字符串類型

日期　　　　　　　　　　　 DATE 　　　　　　　　　　　　　年月日

時(shí)間　　　　　　　　　　　 TIME 　　　　　　　　　　　　　 24 小時(shí)制，分為 小時(shí)分鐘秒

時(shí)間戳記　　　　　　　 TIMESTAMP 　　　　　　　　 1 日期和時(shí)間的值，分為 年月日小時(shí)分鐘秒微秒

空值　　　　　　　　　　　 不同于任何非空值

 

5.3 其他

DB2 不區(qū)分大小寫(xiě)（單引號(hào)或者雙引號(hào)內(nèi)的內(nèi)容除外）

 

 

5.4 創(chuàng)建表和視圖

 

5.4.1 創(chuàng)建表

 

CREATE TABLE PERS

(ID 　 SMALLINT 　　 NOT NULL,

NAME 　　　　　　　　　 VARCHAR(9),

DEPT 　　　　　　　　　　 SMALLINT 　　 WITH DEFAULT 10,

JOB 　　　　　　　　　　　 CHAR(5),

YEARS 　　　　　　　　　 SMALLINT,

SALARY 　　　　　　　　 DECIMAL(7,2),

COMM 　　　　　　　　　 DECIMAL(7,2),

BIRTH_DATE 　　　 DATE)

 

5.4.2 在表中插入值 ( 三種方式 )

 

INSERT INTO PERS

VALUES (12,'Harris',20,'Sales',5,18000,1000,'1950-1-1')

 

INSERT INTO PERS (NAME,JOB,ID)

VALUES 　　　 ('Swagerman','Pramr',500),('Limoges','Prgmr',510), 　　　　　　　　　　　　　　　 ('Li','Prgmr',520)

 

INSERT INTO PERS (ID,NAME,DEPT,JOB,YEARS,SALARY,COMM,BIRTH_DATE) SELECT ID,NAME,DEPT,JOB,YEARS,SALARY,COMM,BIRTH_DATE 　　　 FROM STAFF WHERE ID = 58

 

5.4.3 更新數(shù)據(jù)

 

UPDATE PERS SET JOB = 'Prgmr',SALARY = SALARY + 300 WHERE ID = 410

UPDATE PERS SET SALARY = SALARY * 1.15 WHERE JOB = 'Sales'

 

5.4.4 刪除數(shù)據(jù)

 

DELETE FROM PERS WHERE ID = 120

 

5.4.5 刪除表

 

DROP TABLE PERS

 

5.4.6 創(chuàng)建視圖

 

( 可以選用 WITH CHECK OPTION 選項(xiàng)，該選項(xiàng)針對(duì) WHERE 的條件進(jìn)行限定 )

CREATE VIEW STAFF_ONLY AS SELECT ID,NAME,DEPT,JOB,YEARS 　 FROM STAFF 　 WHERE JOB <> 'Mgr' AND DEPT = 20 WITH CHECK OPTION

 

 

5.5 使用 SQL 語(yǔ)句存取數(shù)據(jù)　

 

(? CREATE 顯示一般幫助提示信息 )

 

5.5.1 連接數(shù)據(jù)庫(kù)

 

CONNECT TO MYDB2 USER USERID USING PASSWORD

 

5.5.2 謂詞

 

　　　 x=y x<>y x<y x>y x>=y x<=y

 

　　　 IS NULL

 

　　　 IS NOT NULL

 

5.5.3 其他的一些去 SQL SERVER 類似的東西就省略

 

5.5.4 運(yùn)算次序

 

　　　 FROM

 

　　　 WHERE

 

　　　 GROUP BY

 

　　　 HAVING

 

　　　 SELECT

 

　　　 ORDER BY

 

5.5.5 函數(shù)

5.5.5 .1 列函數(shù)

( 列函數(shù)對(duì)列中的一組值進(jìn)行運(yùn)算以得到單一的結(jié)果值！ )

 

　　　　　　　 AVG

 

　　　　　　　 COUNT

 

　　　　　　　 MAX

 

　　　　　　　 MIN

 

　　　　　　　

 

5.5. 5 .2 標(biāo)量函數(shù)

 

( 標(biāo)量函數(shù)對(duì)一個(gè)單一值進(jìn)行運(yùn)算以返回另一個(gè)單一的結(jié)果值！ )

 

　　　　　　　 ABS 　　　　　　 絕對(duì)值

 

　　　　　　　 HEX 　　　　　　 十六進(jìn)制

 

　　　　　　　 LENGTH 　　　返回字節(jié)數(shù)（對(duì)于圖形字符串則返回雙字節(jié)字符串）

 

　　　　　　　 YEAR

 

5.5.5 .3 表函數(shù)

 

( 表函數(shù)僅可用于 FROM 子句，返回表的列！ )

 

5.6 表達(dá)式和子查詢

 

5.6.1 標(biāo)量全查詢

 

( 返回一行，該行只包括一個(gè)值，用于從數(shù)據(jù)庫(kù)中檢索值 )

 

SELECT LASTNAME,FIRSTNAME

 

FROM EMPLOYEE

 

WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)

 

 

SELECT AVG(SALARY) AS "Average_Employee",

 

(SELECT AVG(SALARY) AS "Average_Staff" FROM STAFF)

 

FROM EMPLOYEE

 

 

 

 

 

注意 :

 

SQL SERVER 中 , 字符串用單引號(hào) , 而 DB2 中則使用雙引號(hào) !

 

SELECT XX AS "OO" 也可以寫(xiě)為 SELECT XX "XX"

 

 

 

 

 

5.6.2 轉(zhuǎn)換數(shù)據(jù)類型

 

（使用 CAST,CAST 的另外一個(gè)用法是截?cái)鄶?shù)據(jù)）

 

SELECT CAST(NAME AS VARCHAR(2)) AS "NAME" FROM EMPLOYEE

 

 

 

 

 

5.6.3 條件表達(dá)式

 

(CASE 注意，單引號(hào)和雙引號(hào) )

 

 

 

 

 

SELECT DEPTNAME

 

CASE DEPTNUMB

 

WHEN 10 THEN 'Market'

 

WHEN 20 THEN 'Sales'

 

WHEN 30 THEN 'Development'

 

ELSE 'NULL'

 

END AS FUNCTION

 

FROM ORG

 

 

 

 

 

-- 避免產(chǎn)生被 0 除

 

SELECT NAME,WORKDEPT

 

FROM EMPLOYEE

 

WHERE (CASE WHEN BONUS = 0 THEN NULL ELSE SALARY/BONUS END) > 10

 

 

 

 

 

-- 替代簡(jiǎn)單的函數(shù)功能

 

CASE WHEN X<0 THEN -1 WHEN X=0 THEN 0 ELSE 1 END

 

 

 

 

 

5.6.4 表表達(dá)式（臨時(shí)的）

 

5.6.4 .1 嵌套表表達(dá)式

 

（嵌套于 FROM 字句中）

 

 

 

 

 

SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2)

 

FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,

 

SALARY+BONUS+COMM AS TOTAL_PAY

 

FROM EMPLOYEE

 

WHERE EDLEVEL > 16) AS PAY_LEVEL

 

GROUP BY EDLEVEL, HIREYEAR

 

ORDER BY EDLEVEL, HIREYEAR

 

 

 

 

 

5.6.4 .2 公共表達(dá)式

 

（以 WITH 開(kāi)頭，對(duì)公共表達(dá)式的重復(fù)引用使用同一結(jié)果，而使用嵌套表達(dá)式則可能會(huì)出現(xiàn)不同的結(jié)果。）

 

 

 

 

 

WITH

 

PAYLEVEL AS

 

(SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,

 

SALARY+BONUS+COMM AS TOTAL_PAY

 

FROM EMPLOYEE

 

WHERE EDLEVEL > 16),

 

 

 

 

 

PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS

 

(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)

 

FROM PAYLEVEL

 

GROUP BY EDLEVEL, HIREYEAR)

 

 

 

 

 

SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)

 

FROM PAYLEVEL, PAYBYED

 

WHERE EDLEVEL = EDUC_LEVEL

 

AND HIREYEAR = YEAR_OF_HIRE

 

AND TOTAL_PAY < AVG_TOTAL_PAY

 

 

 

 

 

5.6.4 .3 相關(guān)名

 

（注意，一旦使用了相關(guān)名，則不能再在上下文中使用原名，否則出錯(cuò)！）

 

 

SELECT NAME, DEPTNAME

 

FROM STAFF S, ORG O

 

WHERE O.MANAGER = S.ID

 

 

 

 

 

-- 另外，相關(guān)名還可以用來(lái)復(fù)制對(duì)象，例如說(shuō)自身

 

SELECT E2.FIRSTNME, E2.LASTNAME, E2.JOB, E1.FIRSTNME AS MGR_FIRSTNAME,

 

E1.LASTNAME AS MGR_LASTNAME, E1.WORKDEPT

 

FROM EMPLOYEE E1, EMPLOYEE E2

 

WHERE E1.WORKDEPT = E2.WORKDEPT

 

AND E1.JOB = 'MANAGER'

 

AND E2.JOB <> 'MANAGER'

 

AND E2.JOB <> 'DESIGNER'

 

 

 

 

 

5.6.4 .4 不 / 相關(guān)子查詢

 

5.6.4 .4.1 不相關(guān)子查詢

 

SELECT EMPNO, LASTNAME

 

FROM EMPLOYEE

 

WHERE WORKDEPT = 'A00'

 

AND SALARY > (SELECT AVG(SALARY)

 

FROM EMPLOYEE

 

WHERE WORKDEPT = 'A00')

 

 

5.6.4 .4.2 相關(guān)子查詢

 

SELECT E1.EMPNO, E1.LASTNAME, E1.WORKDEPT

 

FROM EMPLOYEE E1

 

WHERE SALARY > (SELECT AVG(SALARY)

 

FROM EMPLOYEE E2

 

WHERE E2.WORKDEPT = E1.WORKDEPT)

 

ORDER BY E1.WORKDEPT

 

 

 

 

 

5.7 在查詢使用運(yùn)算符與謂詞

 

5.7.1 集合運(yùn)算符

 

5.7.1 .1UNION ：

 

組合兩個(gè)表，并消除重復(fù)行；如果 UNION ALL 則不消除重復(fù)行

 

SELECT ID, NAME FROM STAFF WHERE SALARY > 21000

 

UNION

 

SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8

 

ORDER BY ID

 

 

 

 

 

5.7.1 .2 EXCEPT:

 

包括所有在表 1 中而不在表 2 中的行并消除所有重復(fù)行；如果 EXCEPT ALL 則不消除重復(fù)行

 

 

 

 

 

SELECT ID, NAME FROM STAFF WHERE SALARY > 21000

 

EXCEPT

 

SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8

 

 

-- 也就是說(shuō)，凡是滿足 EXCEPT 后面語(yǔ)句的數(shù)據(jù)都不在選擇的范圍內(nèi)

 

 

 

 

 

5.7.1 .3 INTERSECT ：

 

只包括表 1 和表 2 都有的行并消除所有重復(fù)行；如果 INTERSECT ALL 則不消除重復(fù)行

 

 

SELECT ID, NAME FROM STAFF WHERE SALARY > 21000

 

INTERSECT

 

SELECT ID, NAME FROM STAFF WHERE JOB='Mgr' AND YEARS < 8

 

 

 

 

 

5.7.2 謂詞

 

5.7.2 .1 IN/NOT IN

 

SELECT NAME

 

FROM STAFF

 

WHERE DEPT IN (20, 15)

 

 

 

 

 

SELECT LASTNAME

 

FROM EMPLOYEE

 

WHERE EMPNO IN

 

(SELECT RESPEMP

 

FROM PROJECT

 

WHERE PROJNO = 'MA2100'

 

OR PROJNO = 'OP2012')

 

 

 

 

 

5.7.2 .2 BETWEEN/NOT BETWEEN

 

SELECT LASTNAME

 

FROM EMPLOYEE

 

WHERE SALARY BETWEEN 10000 AND 20000

 

 

 

 

 

5.7.2 .3 LIKE/NOT LIKE

 

注意 _ 表示任何單個(gè)字符， % 表示任何零個(gè)或者多個(gè)的字符

 

 

 

 

 

SELECT NAME

 

FROM STAFF

 

WHERE NAME NOT LIKE 'S%'

 

 

 

 

 

5.7.2 .4 EXISTS/NOT EXISTS

 

( 檢查存在性 )

 

 

 

 

 

SELECT DEPTNO, DEPTNAME

 

FROM DEPARTMENT X

 

WHERE NOT EXISTS

 

(SELECT *

 

FROM PROJECT

 

WHERE DEPTNO = X.DEPTNO)

 

ORDER BY DEPTNO

 

 

 

 

 

5.7.2 .5 定量謂詞

 

( 將一個(gè)值和一個(gè)值的集合進(jìn)行比較 )

 

 

 

 

 

2.5.1 > ALL

 

( 必須全部符合，謂詞結(jié)果才為真！ )

 

查詢所有收入超過(guò)所有經(jīng)理收入的雇員的姓名和職位（其實(shí)后面的子查詢中返回值有多個(gè)，所以用了 ALL ）

 

 

SELECT LASTNAME, JOB

 

FROM EMPLOYEE

 

WHERE SALARY > ALL

 

(SELECT SALARY

 

FROM EMPLOYEE

 

WHERE JOB='MANAGER')

 

5.7.2 .5.2 > ANY> SOME

 

( 兩個(gè)謂詞同意，只要有一個(gè)結(jié)果符合，謂詞結(jié)果即為真 )

 

查詢所有收入超過(guò)任何一個(gè)經(jīng)理收入的雇員的姓名和職位（其實(shí)后面的子查詢中返回值有多個(gè)，所以用了 ALL ）

 

 

SELECT LASTNAME, JOB

 

FROM EMPLOYEE

 

WHERE SALARY > ANY

 

(SELECT SALARY

 

FROM EMPLOYEE

 

WHERE JOB='MANAGER')

 

 

 

 

 

5.8 定制和增強(qiáng)數(shù)據(jù)操作

5.8.1 用戶定義類型 UDT

5.8.1 .1 創(chuàng)建

CREATE DISTINCT TYPE PAY AS DECIMAL WITH COMPARISONS

 

5.8.1 .2 使用

SELECT * FROM EMPLOYEE WHERE DECIMAL(SALARY)=5120

 

5.8.1 .3 注意：

UDT 認(rèn)為是于任何其他類型不同的類型，例如上例中的 PAY 和 DECIMAL 類型是不同的，但是可以相互轉(zhuǎn)換！

 

如 PAY(SALARY) 或者 DECIMAL (SALARY)

 

 

 

 

 

5.8.2 用戶自定義函數(shù) UDF

 

5.8.2 .1 創(chuàng)建

 

CREATE FUNCTION MAX(PAY) RETURNS PAY

 

SOURCE MAX(DECIMAL)

 

5.8.2 .2 使用

 

SELECT column1,FunctionName(XXX) FROM TableXXX

 

5.8.2 .3 注意：

UDF 可以調(diào)用其他已有庫(kù)函數(shù)和其他 UDF 。

 

 

 

 

 

5.8.3 大對(duì)象 LOB

5.8.3 .1 簡(jiǎn)介

　　　 BLOB,CLOB,DCLOB 分別代表二進(jìn)制數(shù)大對(duì)象、字符大對(duì)象（多用于字符串）、雙字節(jié)字符大對(duì)象（多用于圖形）

5.8.3 .2 操作（略）

　　　

 

5.8.4 專用寄存器

5.8.4 .1 定義 :

　　　 由 DBMS 為連接定義的存儲(chǔ)區(qū)，用于 SQL 引用的信息。

5.8.4 . 2 存儲(chǔ)的常用數(shù)據(jù)：

　　　 CURRENT DATE 、 USER 、 CURRENT TIMESTAMP 、 CURRENT TIME 、 CURRENT TIMEZONE( 指定與世界時(shí)間的差別 ) 、 CURRENT SERVER

5.8.4 .3 調(diào)用 VALUES(CURRENT DATE) 或者 SELECT CURRENT TIME FROM TABLENAME

 

5.8.5 常用系統(tǒng)視圖

SYSCAT.CHECKS

 

SYSCAT.COLUMNS

 

SYSCAT.COLCHECKS

 

SYSCAT.KEYCOLUSE

 

SYSCAT.DATATYPES

 

SYSCAT.FUNCPARMS

 

SYSCAT.REFERENCES

 

SYSCAT.SCHEMATA

 

SYSCAT.TABCONST

 

SYSCAT.TABLES-- 模式

 

SYSCAT.TRIGGERS

 

SYSCAT.FUNCTIONS

 

SYSCAT.VIEWS