DB2存儲過程語法

1、DB2存儲過程語法語法：   CREATE PROCEDURE <schema-name>.<procedure-name> (參數(shù)) 屬性 <語句>     -參數(shù)：SQL PL 存儲過程中有三種類型的參數(shù)：        IN：輸入?yún)?shù)(默認值,也可以不指定)        OUT：輸出參數(shù)        INOUT：

2、輸入和輸出參數(shù)     -屬性   1、LANGUAGE SQL   指定存儲過程使用的語言。LANGUAGE SQL 是其默認值。還有其它的語言供選擇，比如Java 或者C，可以將這一屬性值分別設(shè)置為LANGUAGE JAVA 或者 LANGUAGE C。     2、DYNAMIC RESULT SETS <n>   如果您的存儲過程將返回n 個結(jié)果集，那么需要填寫這一選項。     3、S

3、PECIFIC my_unique_name   賦給存儲過程一個唯一名稱，如果不指定，系統(tǒng)將生成一個惟一的名稱。一個存儲過程是可以被重載的，也就是說許多個不同的存儲過程可以使用同一個名字，但這些存儲過程所包含的參數(shù)數(shù)量不同。通過使用SPECIFIC 關(guān)鍵字，您可以給每一個存儲過程起一個唯一的名字，這可以使得我們對于存儲過程的管理更加容易。例如，要使用SPECIFIC 關(guān)鍵字來刪除一個存儲過程，您可以運行這樣的命令：DROP SPECIFIC PROCEDURE。如果沒有使用SPECIFIC 這個關(guān)鍵字，您將不得不使用DROP PROCEDURE 命令，并且指明存儲過程的名

4、字及其參數(shù)，這樣DB2 才能知道哪個被重載的存儲過程是您想刪除的。     4、SQL 訪問級別   NO SQL：存儲過程中不能有 SQL 語句   CONTAINS SQL：存儲過程中不能有可以修改或讀數(shù)據(jù)的 SQL 語句   READS SQL：存儲過程中不能有可以修改數(shù)據(jù)的 SQL 語句   MODIFIES SQL：存儲過程中的 SQL 語句既可以修改數(shù)據(jù)，也可以讀數(shù)據(jù)      默認值是 MODIFIES SQL，

5、一個存儲過程不能調(diào)用具有更高 SQL 數(shù)據(jù)訪問級別的其他存儲過程。例如，被定義為 CONTAINS SQL 的存儲過程可以調(diào)用被定義為 CONTAINS SQL 或 NO SQL 的存儲過程。但是這個存儲過程不能調(diào)用被定義為 READS SQL DATA 或 MODIFIES SQL 的其他存儲過程。     -語句   可以是一條單獨的語句或者是一組由BEGIN ATOMIC . END 復合語句  DB2數(shù)據(jù)庫 動態(tài)SQL應用今天寫了一個db2的存儲過程,分享一下:動態(tài)SQL的應用 介紹DB2存儲過程

6、中,三種操作的動態(tài)SQL的應用 動態(tài)表名  DECLARE db_tbname VARCHAR(50);/定義動態(tài)表名變量SET db_tbname = |;/給動態(tài)表名變量賦值,|是連接符,相當于java中的+號DECLARE db_sqlstr VARCHAR(1024);/定義動態(tài)SQL變量/準備動態(tài)插入SQL的拼裝SET db_sqlstr = INSERT INTO |db_tbname|(name,age)VALUES(?,?);/預

7、定義兩個問號PREPARE s2 FROM db_sqlstr;/給問號賦值并執(zhí)行SQLEXECUTE s2 USING name,age;  查詢SQL的拼裝及執(zhí)行SET db_sqlstr =' set (?,?) = (SELECT name,age FROM table where pid=?); PREPARE s1 FROM db_sqlstr;E

8、XECUTE s1 INTO out_name,out_ageUSING pid; Set(?,?)  這段的解釋是: SELECT name,age 將以預定義的方式,輸出到某個字段中去,下一句EXECUTE s1 INTO out_name,out_age則是承上的,也就是查出來的name和age,將賦值給out_name和out_age;USING pid ,則是查詢語句中,where條件;DB2 存儲過程批量插入數(shù)據(jù)存儲過程CREATE&

9、#160;OR REPLACE PROCEDURE "CSSSUPRD"."TEST_ADD_DATA"()LANGUAGE SQLSPECIFIC SQL130327103544500BEGIN  DECLARE   v_casekey INT;   DECLARE   v_fpn INT;  DECLARE  v_seqNum

10、0;INT;  DECLARE I INT default 0;  SET v_casekey=755;  SET v_fpn=1;  SET v_seqNum=1;  WHILE I<32000 DOIF mod(I,2) = 0 THEN          i

11、nsert into table.A           values (v_casekey, v_fpn, 'F', v_seqNum); ELSE         insert intotable.A values (v_casekey, v_fpn, 

12、9;F', v_seqNum); -END;END IF;  SET v_seqNum=v_seqNum+1;  SET I=I+1; END WHILE;END#db2db2中的case語句兩種語法模式：(1):CASE       WHEN 條件  THEN 結(jié)果1       ELSE 

13、;           結(jié)果2     END(2):CASE 表達式1       WHEN 表達式2   THEN 結(jié)果1       ELSE       

14、0;    結(jié)果2  END上面的WHEN可以重復多次，就像C中的SWITCH .CASE的表達下面詳細的各舉幾個例子，這些例子可以用于ESQL和交互式的SQL中eg1:處理被0除SELECTCASEWHEN fileld1=0 THEN 0ELSE fileld2/field1ENDFROM  FILEeg2:字段值對應轉(zhuǎn)換SELECT ORDNO,CUSNAM,SHIPDATE,CASE     

15、60;         WHEN SHIPDATE<CURDATE()  THEN 'OVERDUE'               WHEN SHIPDATE=CURDATE()  THEN 'PROCESSING'  &

16、#160;            WHEN SHIPDATE>CURDATE()  THEN 'ACTIVE'             ENDFROM FILEeg3:獲取月份的名字：上面的例子SELECT ORDNO,CUSNO,  CASE&#

17、160; MONTH(SHIPDATE)  WHEN  '01'   THEN 'Jan'  WHEN  '02'   THEN 'Feb'  WHEN  '03'   THEN 'Mar'  WHEN  '

18、;04'   THEN 'Apr'  WHEN  '05'   THEN 'May'  WHEN  '06'   THEN 'Jun'  WHEN  '07'   THEN 'Jul' &#

19、160;WHEN  '08'   THEN 'Aug'  WHEN  '09'   THEN 'Sep'  WHEN  '10'   THEN 'Oct'  WHEN  '11'   THEN

20、60; 'Nov'  WHEN  '12'   THEN  'Dec'  ENDFROM FILEdb2命令大全1. 建立數(shù)據(jù)庫DB2_GCB CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN

21、 COLLATE USING SYSTEM DFT_EXTENT_SZ 32 2. 連接數(shù)據(jù)庫 connect to sample1 user db2admin using 8301206 3. 建立別名 create alias db2admin.tables for sysstat.tables; CREATE ALIAS DB2ADMIN.VIEWS&

22、#160;FOR SYSCAT.VIEWS create alias db2admin.columns for syscat.columns; create alias guest.columns for syscat.columns; 4. 建立表 create table zjt_tables as (select * from tables) definition

23、0;only; create table zjt_views as (select * from views) definition only; 5. 插入記錄 insert into zjt_tables select * from tables; insert into zjt_views select * from views;&#

24、160;6. 建立視圖 create view V_zjt_tables as select tabschema,tabname from zjt_tables; 7. 建立觸發(fā)器 CREATE TRIGGER zjt_tables_del AFTER DELETE ON zjt_tables REFERENCING OLD AS O FOR EACH

25、60;ROW MODE DB2SQL Insert into zjt_tables1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10) 8. 建立唯一性索引 CREATE UNIQUE INDEX I_ztables_tabname size=3ON zjt_tables(tabname); 9. 查看表 select tabname from

26、60;tables where tabname='ZJT_TABLES' 10. 查看列 select SUBSTR(COLNAME,1,20) as 列名,TYPENAME as 類型,LENGTH as 長度 from columns where tabname='ZJT_TABLES' 11. 查看表結(jié)構(gòu) db2 describe table

27、0;user1.department db2 describe select * from user.tables 12. 查看表的索引 db2 describe indexes for table user1.department 13. 查看視圖 select viewname from views where viewname='V_ZJT_TABLES'

28、 14. 查看索引 select indname from indexes where indname='I_ZTABLES_TABNAME' 15. 查看存貯過程 SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES; 16. 類型轉(zhuǎn)換(cast) ip datatype:varchar se

29、lect cast(ip as integer)+50 from log_comm_failed 17. 重新連接 connect reset 18. 中斷數(shù)據(jù)庫連接 disconnect db2_gcb 19. view application LIST APPLICATION; 20. kill application FORCE APPLICATION(0);&

30、#160;db2 force applications all (強迫所有應用程序從數(shù)據(jù)庫斷開) 21. lock tablelock table test in exclusive mode 22. 共享 lock table test in share mode 23. 顯示當前用戶所有表 list tables 24. 列出所有的系

31、統(tǒng)表 list tables for system 25. 顯示當前活動數(shù)據(jù)庫 list active databases 26. 查看命令選項 list command options 27. 系統(tǒng)數(shù)據(jù)庫目錄 LIST DATABASE DIRECTORY 28. 表空間 list tablespaces 29. 表空間容器 LIST&

32、#160;TABLESPACE CONTAINERS FOR Example: LIST TABLESPACE CONTAINERS FOR 1 30. 顯示用戶數(shù)據(jù)庫的存取權(quán)限 GET AUTHORIZATIONS DB2 中 merge into 的用法DB2 insert or update 解決方案（使用merge into 解決一條記錄，或者多表關(guān)系問題）merge into 

33、  table1  t1using (select column1 ，column2 from table2)  t2on t1.column1 = t2.column1when matched then  update set  t1.column2 = t2.column2when not matched then 

34、0;insert values(column1,column2) 以上是把 table2中的數(shù)據(jù)插入或者更新到table1中。 但是對于開發(fā)人員程序來說，我們是一條一條記錄來處理的，判斷這條數(shù)據(jù)是insert 還是 update 對于DB2的處理辦法如下:        merge into table1  t1        

35、using (        select 程序中的數(shù)據(jù)1 as column1,        程序中的數(shù)據(jù)2  as column2   from SYSIBM.SYSDUMMY1        )  t2 

36、0;      /保證t2 一定是有一條數(shù)據(jù)的，就是你程序中的那條數(shù)據(jù)，SYSIBM.SYSDUMMY1 是db2 專用的，當然這里你可以用別的辦法來處理，比如用select count（1） from 任何一個table都可以。        on a.column1= b.column1       &#

37、160;when matched then  update set  t1.column2 = t2.column2        when not matched then  insert values(column1,column2)DB2中的Merge語句可以將一個表中的數(shù)據(jù)合并到另一個表中，在合并的同時可以進行插入、刪除、更新等操作，功能非常強大。DB2

38、60;Merge語句是經(jīng)?？梢杂玫降腄B2語句，下面對DB2 Merge語句作了詳細的介紹，如果您對此方面感興趣的話，不妨一看。DB2 Merge語句的作用非常強大，它可以將一個表中的數(shù)據(jù)合并到另一個表中，在合并的同時可以進行插入、刪除、更新等操作。我們還是先來看個簡單的例子吧，假設(shè)你定義了一個雇員表（employe），一個經(jīng)理表（manager），如下所示：-雇員表（EMPLOYE）   CREATE TABLE EMPLOYE (   EMPLOYEID INTEGER&

39、#160;NOT NULL,-員工號   NAME VARCHAR(20) NOT NULL,-姓名   SALARY DOUBLE-薪水   );   INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES    (1,'張三',1000),   (2,&

40、#39;李四',2000),   (3,'王五',3000),   (4,'趙六',4000),   (5,'高七',5000);   -經(jīng)理表（MANAGER）   CREATE TABLE MANAGER (   EMPLOYEID INTEGER NOT NULL,-經(jīng)理號  

41、 NAME VARCHAR(20) NOT NULL,-姓名   SALARY DOUBLE-薪水   );   INSERT INTO MANAGER (MANAGERID,NAME,SALARY) VALUES    (3,'王五',5000),   (4,'趙六',6000); -雇員表（EMPLOY

42、E）CREATE TABLE EMPLOYE (EMPLOYEID INTEGER NOT NULL,-員工號NAME VARCHAR(20) NOT NULL,-姓名SALARY DOUBLE-薪水);INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES (1,'張三',1000),(2,'李四',2000),(3,'王五',3000),(4,

43、9;趙六',4000),(5,'高七',5000);-經(jīng)理表（MANAGER）CREATE TABLE MANAGER (EMPLOYEID INTEGER NOT NULL,-經(jīng)理號NAME VARCHAR(20) NOT NULL,-姓名SALARY DOUBLE-薪水);INSERT INTO MANAGER (MANAGERID,NAME,SALARY) VALUES (3,'王五',5000),(

44、4,'趙六',6000);經(jīng)過一段時間，你發(fā)現(xiàn)這樣的數(shù)據(jù)模型，或者說表結(jié)構(gòu)設(shè)計簡直就是一大敗筆，經(jīng)理和雇員都是員工嘛，為什么要設(shè)計兩個表呢？發(fā)現(xiàn)錯誤后就需要改正，所以你決定，刪除經(jīng)理表（MANAGER）表，將MANAGER 表中的數(shù)據(jù)合并到EMPLOYE 表中，仔細分析發(fā)現(xiàn)，王五在兩個表中都存在（可能是干的好升官了），而劉八在EMPLOYE 表中并不存在，現(xiàn)在，我們要求把EMPLOYE 表中不存在的MANAGER都插入到EMPLOYE 表中，存在的更新薪水。該怎么辦呢？這個問題并不難，通常，我們可以分兩步，如下所示：-更新存在的

45、   UPDATE EMPLOYE AS EM SET SALARY=(SELECT SALARY FROM MANAGER WHERE MANAGERID=EM.EMPLOYEID)   WHERE EMPLOYEID IN (   SELECT MANAGERID FROM MANAGER   ); &#

46、160; -插入不存在的   INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY)   SELECT MANAGERID,NAME,SALARY FROM MANAGER WHERE MANAGERID NOT IN (   SELECT EMPLOYEID FROM EMPLOYE   );&

47、#160;-更新存在的UPDATE EMPLOYE AS EM SET SALARY=(SELECT SALARY FROM MANAGER WHERE MANAGERID=EM.EMPLOYEID)WHERE EMPLOYEID IN (SELECT MANAGERID FROM MANAGER);-插入不存在的INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY)SELE

48、CT MANAGERID,NAME,SALARY FROM MANAGER WHERE MANAGERID NOT IN (SELECT EMPLOYEID FROM EMPLOYE);上面的處理是可以的，但是我們還可以有更簡單的方法，就是用Merge語句，如下所示：MERGE INTO EMPLOYE AS EM   USING MANAGER AS MA  

49、60;ON EM.EMPLOYEID=MA.MANAGERID   WHEN MATCHED THEN UPDATE SET EM.SALARY=MA.SALARY   WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY); MERGE INTO EMPLOYE AS EMUSING&

50、#160;MANAGER AS MAON EM.EMPLOYEID=MA.MANAGERIDWHEN MATCHED THEN UPDATE SET EM.SALARY=MA.SALARYWHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);在上面的處理中，我們用經(jīng)理表（MANAGER）的薪水更新了雇員表（EMPLOYE）的薪水，假設(shè)現(xiàn)在要求，如果經(jīng)理表（MANAGER）的

51、薪水>雇員表（EMPLOYE）的薪水的時候更新，否則不更新，怎么辦呢？如下：MERGE INTO EMPLOYE AS EM   USING MANAGER AS MA   ON EM.EMPLOYEID=MA.MANAGERID   WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.

52、SALARY=MA.SALARY   WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY); MERGE INTO EMPLOYE AS EMUSING MANAGER AS MAON EM.EMPLOYEID=MA.MANAGERIDWHEN MATCHED AND EM.SALARY<MA.SA

53、LARY THEN UPDATE SET EM.SALARY=MA.SALARYWHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);不仔細的朋友可能沒有看出上面兩條語句的區(qū)別，哈哈，請仔細對比一下這兩條語句。上面的語句中多了ELSE IGNORE語句，它的意思正如它英文的意思，其它情況忽略不處理。如果你認為理論上應該不存在EM.SALARY>MA.SALARY的數(shù)據(jù)，如果有，說明有問題，你想拋個

54、異常，怎么辦？如下：MERGE INTO EMPLOYE AS EM   USING MANAGER AS MA   ON EM.EMPLOYEID=MA.MANAGERID   WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY   WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALAR