DB2開發(fā)基礎(chǔ)第1講

1、DB2開發(fā)基礎(chǔ)第1講DB2開發(fā)基礎(chǔ)第1講培訓(xùn)大綱一、DB2介紹二、DB2數(shù)據(jù)類型三、DB2 DDL四、DB2 DML五、DB2函數(shù)六、DB2 PL/SQL開發(fā)培訓(xùn)大綱一、DB2介紹IBM DB2發(fā)展史四十年來，從理論研究到實(shí)際應(yīng)用， IBM DB2在數(shù)據(jù)庫的研究和發(fā)展領(lǐng)域做出了巨大的貢獻(xiàn)70年代之前，層次型數(shù)據(jù)庫占主導(dǎo)地位1970年，IBM發(fā)明了第一個(gè)關(guān)系型數(shù)據(jù)庫和 SQL語言1992年，IBM將DB2推向開放平臺(tái)IBM DB2發(fā)展史四十年來，從理論研究到實(shí)際應(yīng)用， IBMDB2概念介紹 什么是模式 模式是已命名對(duì)象(如表和視圖等對(duì)象)的集合,模式提供了數(shù)據(jù)庫中對(duì)象的邏輯分類. 當(dāng)創(chuàng)建表、視圖

2、或任何其它命名對(duì)象時(shí)，即隱式創(chuàng)建了模式。或者，可以使用CREATE SCHEMA語句顯示創(chuàng)建它。 在創(chuàng)建命名對(duì)象時(shí)，可用特定模式的名稱來限定該對(duì)象的名稱。命名對(duì)象的名稱有兩個(gè)部分，其中，名稱的前一個(gè)部分是對(duì)其指定對(duì)象的模式名。如果不指定模式名，則系統(tǒng)自動(dòng)選擇缺省模式。（缺省模式的名稱是執(zhí)行語句的用戶的權(quán)限ID）DB2概念介紹 什么是模式培訓(xùn)大綱一、DB2介紹二、DB2數(shù)據(jù)類型三、DB2 DDL四、DB2 DML五、DB2函數(shù)六、DB2 PL/SQL開發(fā)培訓(xùn)大綱一、DB2介紹DB2數(shù)據(jù)類型-字符串 定長字符串 char(x) 是定長字符串。長度屬性x必須在1和254之間 變長字符串 變長字符串有

3、兩種類型：varchar、long varchar 圖形字符串 定長圖形字符串 GRAPHIC(x)是定長字符串，長度屬性x必須在1和127之間 變長圖形字符串 變長圖形字符串有兩種類型：vargraphic、long vargraphic 二進(jìn)制字符串 二進(jìn)制字符串是一個(gè)字節(jié)序列。它用于保存非傳統(tǒng)數(shù)據(jù)，如圖象等DB2數(shù)據(jù)類型-字符串 定長字符串 DB2數(shù)據(jù)類型數(shù)字 所有的數(shù)字都有符號(hào)和精度。精度是除符號(hào)以外的位數(shù)或數(shù)字?jǐn)?shù)。 smallint smallint(小型整數(shù))是精度為5位的兩字節(jié)整數(shù) integer integer(整數(shù))，是精度為10位的四字節(jié)整數(shù)。 bigint 一個(gè)bigin

4、t(大整數(shù)),是一個(gè)精度為19位的8字節(jié)整數(shù)。 real real（單精度浮點(diǎn)數(shù)）是實(shí)數(shù)的32位近似值 double double(雙精度浮點(diǎn)數(shù))，是實(shí)數(shù)的64位近似值。DOUBLE也稱FLOAT. decimal(p,s) decimal 是一個(gè)十進(jìn)制數(shù)，小數(shù)點(diǎn)的位置由數(shù)字的精度(p)和小數(shù)位(s)確定。精度是數(shù)字的總位數(shù)，必須小于32,小數(shù)位是小數(shù)部分?jǐn)?shù)字的位數(shù)且總是小于或等于精度值。如果未指定精度和小數(shù)位，則十進(jìn)制值的缺省精度為5，缺省小數(shù)位為0DB2數(shù)據(jù)類型數(shù)字 所有的數(shù)字都有符號(hào)和精度。精度是除DB2數(shù)據(jù)類型-日期時(shí)間 日期時(shí)間值是日期、時(shí)間以及時(shí)間戳記（一個(gè)格式為yyyyMMddh

5、hmmss的表示有效日期和時(shí)間的14位字符串）的表示法。 日期時(shí)間值可以用于某些算術(shù)運(yùn)算和字符串運(yùn)算并且與某些字符串是相容的，但是它們既非字符串，也非數(shù)字。 日期 日期值分為三個(gè)部分（年、月以及日） 時(shí)間 時(shí)間是用24小時(shí)制式來指定一天內(nèi)的時(shí)間的值，分為三個(gè)部分（小時(shí)、分鐘和秒） 時(shí)間戳記 時(shí)間戳記是指定日期和時(shí)間的值，分為7個(gè)部分（年、月、日、小時(shí)、分鐘、秒以及微秒）DB2數(shù)據(jù)類型-日期時(shí)間DB2數(shù)據(jù)類型對(duì)象類型對(duì)象類型（BLOB、CLOB、DBCLOB）DB2數(shù)據(jù)類型對(duì)象類型DB2數(shù)據(jù)類型匯總DB2數(shù)據(jù)類型匯總DB2數(shù)據(jù)類型數(shù)據(jù)類型類型特性示例或范圍CHAR(15)定長字符串最大長度為25

6、4Sunny day VARCHAR(15)變長字符串最大長度為32672Sunny daySMALLINT數(shù)字長度為2個(gè)字節(jié)，精度為5位范圍為：-3276832767INTEGER數(shù)字長度為4個(gè)字節(jié)，精度為10位范圍為：21474836482147483647BIGINT數(shù)字長度為8個(gè)字節(jié)，精度為19位REAL數(shù)字單精度浮點(diǎn)32位近似值DOUBLE數(shù)字雙精度浮點(diǎn)64位近似值DECIMAL(5,2)數(shù)字精度是5，小數(shù)位是2DATE日期由三部分組成的值2011-10-09TIME時(shí)間由三部分組成的值16.07.34TIMESTAMP日期時(shí)間由七部分組成的值2011-10-09- 16.07.34

7、.000000DB2數(shù)據(jù)類型數(shù)據(jù)類型類型特性示例或范圍CHAR(15)定長培訓(xùn)大綱一、DB2介紹二、DB2數(shù)據(jù)類型三、DB2 DDL四、DB2 DML五、DB2函數(shù)六、DB2 PL/SQL開發(fā)培訓(xùn)大綱一、DB2介紹DB2表、視圖語句創(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 )更改

8、表 alter table db2inst1.PERS alter column JOB set data type char(10) 刪除表 drop table db2inst1.PERSDB2表、視圖語句創(chuàng)建表語句創(chuàng)建表表、數(shù)據(jù)、索引可以創(chuàng)建在默認(rèn)的表空間上Create table artists( artno smallint not null, name varchar(50), picture blob(2M) not logged compact )Create table artists( artno smallint not null primary key, name va

9、rchar(50), picture blob(2M) not logged compact)In dms01Index in dms02 -Supported for DMS onlyLong in dms03;Create index idx_name ON artists(name);Table data .dms01Indexes for primary key and idx_name.dms02Picture(blob) data .dms03創(chuàng)建表表、數(shù)據(jù)、索引可以創(chuàng)建在默認(rèn)的表空間上SESSION臨時(shí)表使用臨時(shí)表只能建立在用戶臨時(shí)表空間上，而不能建立在系統(tǒng)臨時(shí)表空間上。臨時(shí)表是

10、在一個(gè)SESSION內(nèi)有效的。如果程序有多線程，最好不要使用臨時(shí)表，因?yàn)楸容^難控制。建立臨時(shí)表最好加上with replace選項(xiàng)，這樣可以不顯示地drop臨時(shí)表。被程序創(chuàng)建，并在程序退出時(shí)自動(dòng)清除。只能被創(chuàng)建的程序訪問。交易可以選擇不記日志連接斷開后，自動(dòng)清除性能 沒有系統(tǒng)編目表的開銷 沒有行鎖 可以不選擇日志 沒有認(rèn)證的選擇可以創(chuàng)建索引 可以給全局臨時(shí)表創(chuàng)建正規(guī)的索引提供統(tǒng)計(jì)信息的支撐 SESSION臨時(shí)表使用臨時(shí)表只能建立在用戶臨時(shí)表空間上，而定義全局臨時(shí)表CREATE user temporary tablespace apptemps managed by system using

11、(apptemps);Declare global temporary table t_employees like employee not logged;Declare global temporary table session.t_dept(deptid char(6),deptname char(20) on commit delete rows not logged;Declare global temporary table session.t_projects as (full select) definition only ON commit preserve rows no

12、t logged with replace in tablespace apptemps;DECLARE GLOBAL TEMPORARY TABLE ETL_TEMPSQL (TGTSCHEMA VARCHAR(128), TGTTABLE VARCHAR(128), XH INTEGER, TYPE CHARACTER(1), SQL VARCHAR(3000) ) NOT LOGGED WITH REPLACE ;定義全局臨時(shí)表CREATE user temporary tDB2表、視圖語句 DB2表、視圖語句 create view staff_only as select id,na

13、me,dept,job,years from staff where jobMgr and dept=20 數(shù)據(jù)庫使用Check option 來限制每一條通過視圖來插入的數(shù)據(jù)。 create view Fixed_income(lname,depart,jobtitile,newsalary) as select name,dept,job,salary from pers where jobSales with check option 條件 jobSales將為通過該視圖的每一條插入/更新的語句作檢查。 除了校驗(yàn)選擇項(xiàng)以外，先前視圖中的更新等效于對(duì)基表PERS的更新： update pe

14、rs set salary=salary*1.10 where name=Li and jobSales DB2表、視圖語句 DB2表、視圖語句自增長列表中的數(shù)值型列可以在插入每行新數(shù)時(shí)，自動(dòng)產(chǎn)生一個(gè)唯一的數(shù)值每張表最多只能有一個(gè)自增長列值的產(chǎn)生方式有ALWAYS或BY DEFAULT GENERATED ALWAYS -值總是被數(shù)據(jù)庫產(chǎn)生 -應(yīng)用程序不能顯示的提供一個(gè)新值 GENERATED BY DEFAULT -插入式，如果程序沒提供，數(shù)據(jù)庫才產(chǎn)生一個(gè)新值 -數(shù)據(jù)庫不能保證每次產(chǎn)生的值唯一自增長列表中的數(shù)值型列可以在插入每行新數(shù)時(shí)，自動(dòng)產(chǎn)生一個(gè)唯一GENERATED ALWAYS例子CR

15、EATE TABLE inventory( partno integer generated always as identity(start with 100,increment by 1), description char(20) );INSERT INTO inventory values(default,door); -100INSERT INTO inventory(description) values(hinge); -101INSERT INTO inventory values(102,window); -error,value always generatedCOMMIT

16、;INSERT INTO inventroy(description) values(lock); -102;Rollback;INSERT INTO inventory(description) values(frame); 103COMMIT;Select * from inventory; Door hinge103 frameGENERATED ALWAYS例子CREATE TABLEGENERATED BY DEFAULTCREATE TABLE inventory( partno integer primary key generated by default as identit

17、y(start with 100,increment by 1), description char(20) );Insert into inventory values(DEFAULT,door); -100Insert into inventory (description) values(hinge); -101Insert into inventory values(101,bolt); -error,duplicate partnoInsert into inventory values(102,window); -102COMMIT;INSERT INTO inventory(de

18、scription) values(lock); error,duplication partnoInsert into inventory(description) values(lock); 103Rollback;Insert into inventory (description) values(frame); 104Commit;GENERATED BY DEFAULTCREATE TAB序列不同于自增長字段，序列是獨(dú)立于表的,例如： create sequence myseq start with 1 increment by 1 no cycle; 語法： nextval for

19、 prevval for 例如： insert into t1 values(nextval for myseq,); select prevval for myseq from sysibm.sysdummy1;序列不同于自增長字段，序列是獨(dú)立于表的,例如：創(chuàng)建索引Index characteristics assending or descending unique or non-unique bi-directional(no storage overhead) compound cluster include columns例如： create unique index itemno

20、on albums(itemno desc); create index item on stock(itemno) allow reverse scans; create index clx1 on stock(shipdate) cluster allow reverse scans; create unique index incidx on stock(itemno) include(shipdate);創(chuàng)建索引Index characteristics數(shù)據(jù)完整性（外鍵）Alter table department add constraint pk_deptno primary ke

21、y (deptno);Alter table employee add constraint fk_workdept foreign key(workdept) references department;數(shù)據(jù)完整性（外鍵）Alter table departmenCheck限制的使用保證表數(shù)據(jù)的完整性一旦定義，插入和更新記錄必須遵守，否則失敗 create table artists( artno smallint not null, name varchar(50), classification char(1) not null, picture blob(2M) not logged

22、compact, constraint classify check(classification in (C,E,P,R)In dms01;可以使用alter talbe drop constraint 的方式刪除限制。Check限制的使用保證表數(shù)據(jù)的完整性物化查詢表（MQT)定義基于一定結(jié)果統(tǒng)計(jì)值的表，可以優(yōu)化數(shù)據(jù)庫的執(zhí)行計(jì)劃 SQL compiler 判斷到可以使用MQT運(yùn)行，運(yùn)行效率遠(yuǎn)遠(yuǎn)高于使用一般表的速度。MQTBaseTable #1BaseTable #3BaseTable #2Query #4Query #5Query #3Query #2Query #1物化查詢表（MQT)定

23、義基于一定結(jié)果統(tǒng)計(jì)值的表，可以優(yōu)化數(shù)據(jù)物化查詢表的選項(xiàng)數(shù)據(jù)選項(xiàng) Data Initially deferred刷新頻率 refresh deferred refresh immediateOptimizer-aware enable query optimization disable query optimization兩種類型 maintained by system 如果基礎(chǔ)表被更新，系統(tǒng)自動(dòng)根據(jù)refresh選項(xiàng)更新 maintained by user 僅僅應(yīng)用REFRESH DEFERRED 表；通過客戶應(yīng)用維護(hù)和更新物化表 手工刷新物化查詢表物化查詢表的選項(xiàng)數(shù)據(jù)選項(xiàng)MQT例子CR

24、EATE TABLE DB2INST2.summary_customer_order_2002As( select trans_dt,status,count(*) as count_all, sum(amount) as sum_amount, count(amount) as count_amount from db2inst2.customer_order group by trans_dt,status)Data initially deferredRefresh immediateEnable query optimizationMaintained by system;MQTBas

25、eTable #1BaseTable #3BaseTable #2Query #4Query #5Query #3Query #2Query #1MQT例子CREATE TABLE DB2INST2.sumDesign AdvisorDesign Advisor能幫助您設(shè)計(jì)和定義合適的索引，也可以使用db2advis來運(yùn)行命令。 db2advis -d database-name -w workload-name -s statement -I filename -g -a userid -l disk-limit -/passwd -t max-advise-time -h -p -o ou

26、tfile 例如： db2advis -d prototype w prouction l 53 t 20Design AdvisorDesign Advisor能幫有用的命令List tablesList tables for user | all | system | schemaList tables for schema schemaDescribe table table nameDescribe select statementGet instance 獲得當(dāng)前的實(shí)例名有用的命令List tables數(shù)據(jù)一致性和完整性事務(wù)在多用戶環(huán)境中并發(fā)的幾種情況：臟讀這種情況發(fā)生在一個(gè)事務(wù)讀取

27、還未提交的數(shù)據(jù)時(shí)。例如：事務(wù)1改變了一行數(shù)據(jù)，而在事務(wù)1提交修改之前事務(wù)2讀取了事務(wù)1所改變的行的數(shù)據(jù)。如果事務(wù)1回滾了修改，那么事務(wù)2就讀取了實(shí)際上并不存在的數(shù)據(jù)。不可重復(fù)讀這種情況發(fā)生在事務(wù)兩次讀取同一行中的數(shù)據(jù)卻得到不同的數(shù)值時(shí)。例如：事務(wù)1讀取了一行數(shù)據(jù)，而事務(wù)2改變或刪除了那些行并提交了修改。如果事務(wù)1再次讀取了那一行，那么，事務(wù)1就得到了不同的值（如果那一行被更新）或發(fā)現(xiàn)那一行已不存在（如果那一行被刪除）幻影數(shù)據(jù)這種情況發(fā)生在一行數(shù)據(jù)滿足搜索規(guī)則，卻在開始沒有被看到時(shí)。例如：事務(wù)1讀取了一系列滿足搜索規(guī)則的行，而事務(wù)2插入了一個(gè)滿足事務(wù)1搜索規(guī)則的行。如果事務(wù)1再次查詢語句，就會(huì)得

28、到不同的一系列行。數(shù)據(jù)一致性和完整性事務(wù)在多用戶環(huán)境中并發(fā)的幾種情況：事務(wù)隔離級(jí)別DB2使用四個(gè)不同的事務(wù)隔離級(jí)別來實(shí)現(xiàn)并發(fā)。可重復(fù)讀可重復(fù)讀(RR)鎖定應(yīng)用程序在一個(gè)事務(wù)中引用的所有行，可以防止幻影行。例如如果掃描10000行數(shù)據(jù)并對(duì)它們進(jìn)行過濾，盡管只有10行滿足條件，但仍會(huì)鎖定全部的10000行數(shù)據(jù)。在可重復(fù)讀隔離級(jí)別下，其他事務(wù)不能更新、刪除、插入數(shù)據(jù)，執(zhí)行的事務(wù)看不到其他事務(wù)的未提交修改?？芍貜?fù)讀可能會(huì)持有大量鎖定最終導(dǎo)致行鎖定升級(jí)到表鎖定。讀穩(wěn)定性讀穩(wěn)定性(RS)只鎖定滿足查詢條件的行，有可能看到幻影行。也就是如果一個(gè)使用讀穩(wěn)定性隔離級(jí)別的事務(wù)多次執(zhí)行同一個(gè)查詢，該事務(wù)可能會(huì)得到別

29、的事物插入的符合該搜索規(guī)則的新行。游標(biāo)穩(wěn)定性游標(biāo)穩(wěn)定性(CS)是缺省隔離級(jí)別，事務(wù)只鎖定當(dāng)前游標(biāo)位置的行，可能出現(xiàn)幻影行和不可重復(fù)讀取數(shù)據(jù)。別的事務(wù)不能對(duì)游標(biāo)位置的行進(jìn)行更新或刪除，但還能在被鎖定行的兩端插入、刪除或修改行。未提交讀未提交讀(UR)允許一個(gè)事務(wù)訪問其他事務(wù)未提交的修改數(shù)據(jù)，可能出現(xiàn)幻影行和不可重復(fù)讀取數(shù)據(jù)。使用未提交讀不需要對(duì)任何行鎖定，適用于只讀表上的查詢和那些并不關(guān)心是否可從其他應(yīng)用程序中看到未提交的數(shù)據(jù)。舉例說明為查詢語句指定隔離級(jí)別：select*fromstockwithur 事務(wù)隔離級(jí)別DB2使用四個(gè)不同的事務(wù)隔離級(jí)別來實(shí)現(xiàn)并發(fā)。事務(wù)隔離級(jí)別DB2通用數(shù)據(jù)庫支持的四

30、種隔離級(jí)別：DB2事務(wù)隔離級(jí)別臟讀不可重復(fù)讀幻影數(shù)據(jù)未提交讀有有有游標(biāo)穩(wěn)定性無有有讀穩(wěn)定性無無有可重復(fù)讀無無無事務(wù)隔離級(jí)別DB2通用數(shù)據(jù)庫支持的四種隔離級(jí)別：DB2事務(wù)隔培訓(xùn)大綱一、DB2介紹二、DB2數(shù)據(jù)類型三、DB2 DDL四、DB2 DML五、DB2函數(shù)六、DB2 PL/SQL開發(fā)培訓(xùn)大綱一、DB2介紹DML語句INSERT語句 插入單條數(shù)據(jù) INSERT INTO 表名(字段1，字段2,) VALUES(值1，值2,) 插入多條數(shù)據(jù) INSERT INTO 表名(f1,f2,) VALUES(v1,v2,), (v11,v22,), 根據(jù)其他表數(shù)據(jù)插入多條數(shù)據(jù) INSERT INTO

31、表名(f1,f2,) SELECT f11,f12 from DML語句INSERT語句UPDATE語句 UPDATE table_name set f1=v1, f2=v2 where 條件 使用UPDATE語句來更改表中的數(shù)據(jù)。使用此語句，可以更改滿足where子句搜索條件的每行中的一列或多列的值。 下列示例更新ID為410的雇員的信息： UPDATE pers SET job=Prgmr,SALARY=salary+300 WHERE id=410; SET子句指定要更新的列并提供值UPDATE語句DELETE語句 DELETE FROM table_name WHERE 條件 注意：如

32、果去掉where 條件的話，將刪除表中所有的記錄。DELETE語句 DELETE FROM table_nSELECT語句 選擇列 1.選擇某些字段 SELECT f1,f2, FROM table_name 2.選擇表中全部字段 SELECT * from table_name 3.選擇行 SELECT f1,f2, FROM table_name WHERE 條件10 and 條件20 謂詞X=yXyXyx=yIs null/is not nullSELECT語句謂詞X=yXyXyx=yIs排序 SELECT f1,f2, FROM table_name WHERE 條件 ORDER BY

33、 f1,f2 ASC/DESC ASC:升序（默認(rèn)） DESC:降序去除重復(fù)行SELECT DISTINCT f1,f2 FROM 表名排序運(yùn)算次序 1.FROM 2.WHERE 3.JOIN 4.GROUP BY 5.HAVING 6.ORDER BY運(yùn)算次序 1.FROM培訓(xùn)大綱一、DB2介紹二、DB2數(shù)據(jù)類型三、DB2 DDL四、DB2 DML五、DB2函數(shù)六、DB2 PL/SQL開發(fā)培訓(xùn)大綱一、DB2介紹DB2函數(shù)字符串函數(shù)（一）ASCII返回字符串首字符對(duì)應(yīng)的ASCII值(整型)CHAR返回固定長度的字符串CHARACTER_LENGTH/CHAR_LENGTH返回字符串長度CHR返

34、回ASCII對(duì)應(yīng)的字符CONCAT/|連接兩個(gè)字符串，任意一個(gè)為null,返回nullLCASE/LOWER返回小寫UCASE/UPPER返回大寫LTRIM/RTRIM去掉左側(cè)或右側(cè)的空格LEFT/RIGHT返回最左右邊的LENGTH個(gè)字符DB2函數(shù)字符串函數(shù)（一）ASCII返回字符串首字符對(duì)應(yīng)的ADB2函數(shù)字符串函數(shù)（續(xù)二）INSERTINSERT(ARG1,POS,SIZE,ARG2)返回一個(gè)字符串，將ARG1從POS處刪除SIZE個(gè)字符，將ARG2插入該位置LOCATELOCATE(ARG1,ARG2,POS)函數(shù)在ARG2中查找ARG1第一次出現(xiàn)的位置。如果指定POS,則從POS處開始

35、查找ARG1第一次出現(xiàn)的位置POSSTRPOSSTR(EXP1,EXP2)函數(shù)返回EXP2在EXP1中的位置REPEATREPEAT(ARG1,NUM_TIMES)函數(shù)返回ARG1被重復(fù)NUM_TIMES次的字符串REPLACEREPLACE(exp1,exp2,exp3)函數(shù)返回用exp3代替exp1中所有的exp2SPACESPACE(size)函數(shù)返回一個(gè)包含在SIZE個(gè)空格的字符串SUBSTRSUBSTR(arg1,pos,length)函數(shù)返回arg1中，pos 位置開始的length個(gè)字符。如果沒有指定length,則返回剩余的字符。DB2函數(shù)字符串函數(shù)（續(xù)二）INSERTINSE

36、RT(ARGDB2函數(shù)字符串函數(shù)（續(xù)三）SUBSTRINGSubstring(exp1,start,length)返回子字符串左邊第start個(gè)字符開始起length個(gè)字符的部分CHARINDEXCHARINDEX(substring,expression)返回字符串中某個(gè)指定的子串出現(xiàn)的開始位置，REPLICATE返回一個(gè)重復(fù)指定次數(shù)的字符串REVERSE返回反序字符串DB2函數(shù)字符串函數(shù)（續(xù)三）SUBSTRINGSubstriDB2函數(shù)數(shù)學(xué)函數(shù)ABS/ABSVAL返回絕對(duì)值CEILING/CEIL返回上整FLOOR返回下整MODMOD(N,M) 返回余數(shù)POWERPOWER(N,M)返回N

37、的M次方RANDRAND(N)返回隨機(jī)數(shù)ROUNDRound(n,m)SIGN符號(hào)函數(shù)SQRT返回算術(shù)平方根TRUNCTRUNC(N,M)返回截?cái)嗪蟮慕Y(jié)果DB2函數(shù)數(shù)學(xué)函數(shù)ABS/ABSVAL返回絕對(duì)值CEILINDB2函數(shù)日期函數(shù)（一）DAY返回日DAYNAME返回一個(gè)日期的星期部分DAYOFWEEK返回一個(gè)日期的星期部分DAYOFYEAR返回一個(gè)日期的年日部分(1366)DAYS返回一個(gè)日期對(duì)應(yīng)的整型值Current date獲取當(dāng)前日期Current time獲取當(dāng)前時(shí)間Current timestamp返回當(dāng)前日期時(shí)間戳year返回年month返回月hour返回小時(shí)DB2函數(shù)日期函數(shù)（

38、一）DAY返回日DAYNAME返回一個(gè)日DB2函數(shù)日期函數(shù)（續(xù)二）MINUTE返回分鐘SECOND返回秒microsecond返回毫秒timestampdiffTimestampdiff(inter1,char(ts1-ts2)Inter1:1-毫秒 2-秒4分 8小時(shí) 16-天 32-周 64-月 128-季度 256-年dateaddDateadd(datepart,number,date)返回指定日期date加上指定的額外時(shí)間間隔number產(chǎn)生的日期datediffDatediff(datepart,date1,date2)返回兩個(gè)指定日期在datepart方面的不同之處，即date2

39、超過date1的差距值，其結(jié)果值是一個(gè)帶有正負(fù)號(hào)的整數(shù)值DB2函數(shù)日期函數(shù)（續(xù)二）MINUTE返回分鐘SECOND返DB2函數(shù)日期函數(shù)（續(xù)三）DATENAMEDatename(datepart,date)以字符串的形式返回日期的指定部分，由datepart來指定DATEPARTDatepart(datepart,date)以整數(shù)值的形式返回日期的指定部分，此部分由datepart來指定Datepart(dd,date) 等同于day(date)Datepart(mm,date) 等同于month(date)Datepart(yy,date) 等同于year(date)GETDATE以datet

40、ime的缺省格式返回系統(tǒng)當(dāng)前的日期和時(shí)間Last_day自定義D+1 month-day(d+1 month) dayDB2函數(shù)日期函數(shù)（續(xù)三）DATENAMEDatename(DB2函數(shù)位操作函數(shù)BITANDBitand(7,4)=4BITANDNOTBitandnot(2049,2048) 1,清除第一個(gè)參數(shù)對(duì)應(yīng)的位BITORORBITXORXORBITNOTNOTBITNOT(CAST(2 AS SMALLINT)=-3DB2函數(shù)位操作函數(shù)BITANDBitand(7,4)=4BDB2函數(shù)轉(zhuǎn)換函數(shù)CASTCast(1 as varchar(10)BIGINT支持輸入?yún)?shù)為數(shù)字型，字符串，

41、和日期型若為日期型：Date 返回yyyyymmddTime 返回hhmmssTimestamp 返回yyyymmddhhmmssDATE返回一個(gè)日期DATE(1988-12-25)DATE(35) 0001-02-24DB2函數(shù)轉(zhuǎn)換函數(shù)CASTCast(1 as varcharDB2函數(shù)其它函數(shù)CARDINALITY返回?cái)?shù)組元素個(gè)數(shù)COALESCE返回第一個(gè)非null函數(shù)DATAPARTITIONNUM這個(gè)函數(shù)返回?cái)?shù)據(jù)行所屬的分區(qū)序號(hào)。從開始。Select datapartitionnum(empno) from employeeDBPARTITIONNUM返回?cái)?shù)據(jù)行所屬的分區(qū)號(hào)DECODE

42、DECODE(c1,7,a,6,b,c)CASE WHEN ENDCase c1 when 7 then a when 6 then b else cENDVALUE返回一個(gè)非空的值，當(dāng)其第一個(gè)參數(shù)非空，直接返回第一個(gè)參數(shù)的值，否則返回第二個(gè)參數(shù)的值DB2函數(shù)其它函數(shù)CARDINALITY返回?cái)?shù)組元素個(gè)數(shù)CODB2函數(shù)專用寄存器,所有這些專用寄存器都可以通過在名稱中加下劃線來引用.例如:CURRENT_DATECURRENT DATECURRENT TIMECURRENT TIMESTAMPCURRENT USERCURRENT PATH函數(shù)路徑CURRENT SCHEMA當(dāng)前的模式USER當(dāng)

43、前登錄的用戶DB2函數(shù)專用寄存器,所有這些專用寄存器都可以通過在名稱中加常用函數(shù)列函數(shù) 列函數(shù)對(duì)列中的一組值進(jìn)行運(yùn)算以得到單個(gè)結(jié)果值。 示例如下: AVG 平均值 COUNT 非空記錄數(shù) MAX 最大值 MIN 最小值標(biāo)量函數(shù) 標(biāo)量函數(shù)對(duì)一個(gè)單一值進(jìn)行某個(gè)運(yùn)算以返回另一個(gè)單一值。下列就是一些由DB2通用數(shù)據(jù)庫提供的標(biāo)量函數(shù)。 ABS 返回絕對(duì)值 常用函數(shù)列函數(shù)常用函數(shù)DB2常用函數(shù)有以下一些：類似oracle中decode的判斷操作例如：selectcasea1 when1thenn1 when2thenn2 elsen3 endasaa1 from 表名 類似charindex查找字符在字符

44、串中的位置例如：Locate(y,dfdasfay)查找字符y在字符串dfdasfay中的位置類似datedif計(jì)算兩個(gè)日期的相差天數(shù)函數(shù)DAYS例如：days(date(2001-06-05)days(date(2001-04-01) 預(yù)防空字段的處理函數(shù)COALESCE例如：處理字符型COALESCE(NAME,) 處理數(shù)字型COALESCE(BOX_NUM,0)常用函數(shù)DB2常用函數(shù)有以下一些：示例SELECT current date from sysibm.sysdummy1Insert into user_log values(user,current timestamp)Valu

45、es current schemaSet current schema myschemaSchema example:Connect to sample user db2adminCreate table t1(c1 int,c2 varchar(10) current schema=db2adminT1 is created as db2admin.t1Set schema sch2Create table t1(x int,y int)t1 is created as sch2.t1示例SELECT current date from sys使用NULLNULL不能使用=號(hào)TABLE t1

46、 TABLE t2C1 C2- - 1 1 2 2 - - 4 4Select * from t1 where c1=null select * from t1,t2 where t1.c1=t2.c1=error =3 rows returnedSelect * from t1 where t1 is null=1 row returnedNULLNULL空值 空值是一個(gè)區(qū)別于所有非空值的特殊值。它意味著行中的那一列無任何其他值。所有數(shù)據(jù)類型都存在空值。使用NULLNULL不能使用=號(hào)NULLNULL空值字符串連接NULLNULL表示“未知狀態(tài)”。在未知狀態(tài)下，使用NULL與其他值連接，其他

47、值還只是未知狀態(tài)。(ANSI標(biāo)準(zhǔn)) SET v1=null; select hello | v1 from . -null 正確方法： set v1=null; select hello | coalesce(v1,) from -hello字符串連接NULLNULL表示“未知狀態(tài)”。在未知狀態(tài)下，使NULL值Null表示一個(gè)未知的狀態(tài)表的列可以包含NOT NULL的限制NOT NULL必須包含到UNIQUE和PRIMARY限制中例如： create tale employee ( id smallint not null primary key, name varchar(30) not n

48、ull, department smallint not null with default 10, date_of_birth date );NULL值Null表示一個(gè)未知的狀態(tài)培訓(xùn)大綱一、DB2介紹二、DB2數(shù)據(jù)類型三、DB2 DDL四、DB2 DML五、DB2函數(shù)六、DB2 PL/SQL開發(fā)培訓(xùn)大綱一、DB2介紹存儲(chǔ)過程結(jié)構(gòu)CREATE PROCEDURE sqlsamp ( IN dpt varchar(4) ) SPECIFIC sqlsamp RESULT SETS 1 LANGUAGE SQL P1: BEGIN DECLARE cursor1 CURSOR WITH RETUR

49、N FOR SELECT DEPARTMENT.DEPTNO, DEPARTMENT.DEPTNAME, EMPLOYEE.FIRSTNME, EMPLOYEE.MIDINIT,EMPLOYEE.LASTNAME FROM DEPARTMENT, EMPLOYEE WHERE EMPLOYEE.EMPNO =DEPARTMENT.MGRNO AND DEPARTMENT.DEPTNO = dpt; OPEN cursor1; END P1 CREATE PROCEDURE Sp1BEGIN ATOMIC/NOT ATOMICNOT ATOMIC:如果發(fā)生一個(gè)未處理的錯(cuò)誤條件，不會(huì)回滾SQL語句

50、。ATOMIC: 在一條ATOMIC復(fù)合語句的執(zhí)行期間，如果其中發(fā)生任何未處理的錯(cuò)誤條件， 那么在此之前已執(zhí)行的所有語句都被回滾。ATOMIC語句不能被嵌套在其他 ATOMIC復(fù)合語句中。存儲(chǔ)過程結(jié)構(gòu)參數(shù)定義DB2儲(chǔ)存過程的參數(shù)分為兩部分：輸入和輸出參數(shù)。參數(shù)表示方式:輸入?yún)?shù)用IN開頭輸出參數(shù)用OUT開頭既是輸入又是輸出參數(shù)用INOUT開頭舉例說明：create procedure sp_sample ( in var0 varchar(10), out var1 varchar(20), inout var2 varchar(20)參數(shù)定義DB2儲(chǔ)存過程的參數(shù)分為兩部分：輸入和輸出參數(shù)。變

51、量定義 存儲(chǔ)過程中可以使用關(guān)鍵字DECLARE定義變量，然后在后續(xù)程序過程中使用變量來處理邏輯。定義變量時(shí)可以指定一個(gè)初始值。舉例說明：CREATE PROCEDURE P2( INOUT a VARCHAR(8),OUT b INTEGER) LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0;DECLARE var2 VARCHAR(5) DEFAULT a | bc;- other SQL statements END BEGIN ATOMIC END注意聲明的順序變量定義 存儲(chǔ)過程中可以使用關(guān)鍵字DECLARE定義變量定義全局變量：CRE

52、ATE VARIABLE var_name DATATYPE default value;說明：會(huì)話全局變量是在存儲(chǔ)過程之外聲明的。在存儲(chǔ)過程中使用返回游標(biāo)：1.使用以下語句聲明一個(gè)結(jié)果集定位符： DECLARE rs_location_var1 RESULT_SET_LOCATOR VARYING;2.將這個(gè)結(jié)果集定位符與調(diào)用者過程相關(guān)聯(lián)： ASSOCIATE RESULT SET LOCATOR(rs_locator_var1) WITH PROCEDURE proc_called;3.分配從調(diào)用過程指向結(jié)果集的指標(biāo) ALLOCATE cursor1 CURSOR FOR RESULT S

53、ET rs_locator_var1;Create procedure use_nested_cursor(deptin int,out tot_dept_com dec(12,2)Begin declare loc1 result_set_locator varying; call result_from_cursor(deptin); allociate result set locator(loc1) with procedure result_form_cursor; allocate c1 cursor for result set loc1; fetch from c1 into

54、變量定義全局變量：賦值語句存儲(chǔ)過程使用關(guān)鍵字SET給變量賦值。舉例說明:CREATE PROCEDURE P2(INOUT a VARCHAR(8), OUT b INTEGER) LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0; DECLARE var2 VARCHAR(5) DEFAULT a | bc; SET var1 = 0; SET var2 = var2 | def; SET a = var1; END 賦值語句存儲(chǔ)過程使用關(guān)鍵字SET給變量賦值。條件控制語句1條件控制語句包括以下幾種：IF THEN ELSEIF THEN E

55、LSE END IF舉例說明:IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; ELSEIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus =500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empn

56、o = employee_number; END IF ; 條件控制語句1條件控制語句包括以下幾種：條件控制語句2CASEsimple CASE:語句用于根據(jù)一個(gè)字面值進(jìn)入某個(gè)邏輯 searched CASE:語句用于根據(jù)一個(gè)表達(dá)式的值進(jìn)入某個(gè)邏輯舉例說明：CASE WHEN v_workdept = A00 THEN UPDATE department SET deptname = DATA ACCESS 1; WHEN v_workdept = B01 THEN UPDATE department SET deptname = DATA ACCESS 2; ELSE UPDATE depa

57、rtment SET deptname = DATA ACCESS 3; END CASE 條件控制語句2CASEsimple CASE:語句用于根據(jù)循環(huán)語句循環(huán)語句包括以下幾種：LOOP 簡單的循環(huán) L1:LOOP SQL statements; LEAVE L1; END LOOP L1;WHILE 循環(huán) WHILE condition DO SQL statements; END WHILE;REPEAT 循環(huán) REPEAT SQL statements; UNTIL condition; END REPEAT;FOR 循環(huán) FOR loop_name AS select * from

58、DO SQL statements; END FOR; 注意，F(xiàn)or語句不同于其他的迭代語句，因?yàn)樗糜诘粋€(gè)定義好的結(jié)果集中的行。循環(huán)語句循環(huán)語句包括以下幾種：循環(huán)語句示例For循環(huán)的例子：CREATE PROCEDURE dept1 ( deptin char(3), out p_counter INT)P1:BEGIN DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS SELECT lastname,hiredate,birthdate FROM employee WHERE workdept=deptin DO INSERT INTO

59、 report_info_dept values(dept_loo.lastname,dept_loop. hiredate,dept_loop.birthdate); set v_counter=v_counter+1; END FOR; SET p_counter=v_counter;END P1; 請(qǐng)注意：該過程沒有打開游標(biāo)、從游標(biāo)中取數(shù)或關(guān)閉游標(biāo)-所有這些都是FOR循環(huán)語句隱式進(jìn)行的。而且，可以引用循環(huán)中隱式地獲取的值，使用循環(huán)名稱限定列(例如，dept_loop.lastname)而不必使用本地變量來存儲(chǔ)這些值。循環(huán)語句示例For循環(huán)的例子：常用操作符有以下幾種：關(guān)系運(yùn)算符關(guān)系運(yùn)算符

60、有六種：小于、小于等于、大于、大于等于、等于、不等于邏輯運(yùn)算符邏輯運(yùn)算符有三種：AND、OR、NOT獲取返回碼：GET DIAGNOSTICS ret_code=DB_RETURN_STATUS;常用操作符有以下幾種：游標(biāo)游標(biāo)處理步驟：1)、游標(biāo)聲明2)、打開游標(biāo)3)、將游標(biāo)的結(jié)果取出到之前已聲明的本地變量中4)、關(guān)閉游標(biāo)（如果現(xiàn)在不關(guān)閉游標(biāo)，當(dāng)過程終止時(shí)將隱式的關(guān)閉游標(biāo)）DECLARE CURSOR with return ;OPEN ;FETCH INTO -process data,fetch next row if required CLOSE ; 在SQL過程中，游標(biāo)還可以用于將結(jié)果