SYBASE 資料庫遷移到AS 400 db2的FAQ（三） (轉)

SYBASE 資料庫遷移到AS 400 db2的FAQ（三） (轉)[@more@]

繼續上一篇文章的內容

．2中STATE的代號如何定義？

答：如下:namespace prefix = o ns = "urn:schemas--com::office" />

ul Completion

ntifier

nation

Errors

Not in Prerequisite State

Not Available or Operator Intervention

．如何對當前遊標的行資料進行刪除，操作？

答：例如  DECLARE THISEMP CURSOR FOR EMPNO, LASTNAME,
WORKDEPT, JOB
FROM CORPDATA.EMPLOYEE
FOR UPDATE OF JOB ;

open THISEMP ;

…

UPDATE CORPDATA.EMPLOYEE
SET JOB = :NEW-CODE
WHERE CURRENT OF THISEMP

…

 

．中使用 NOT  FOUND 控制遊標？

答：NOT FOUND是DB2中的全域性變數，可以等同於如下

IF SQLCODE =100 GO TO DATA-NOT-FOUND.

 or

 EXEC SQL

 WHENEVER NOT FOUND GO TO symbolic-address

END-EXEC.

 IF SQLSTATE ='02000' GO TO DATA-NOT-FOUND.

．DB2下動態遊標如何定義和使用？

答：給出一個例子

CREATE PROCEDURE CREATE_DEPT_TABLE (IN P_DEPT CHAR(3))

  LANGUAGE SQL

  BEGIN

  DECLARE STMT CHAR(1000);

  DECLARE MESSAGE CHAR(20);

  DECLARE TABLE_NAME CHAR(30);

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

  SET MESSAGE = 'ok';

  SET TABLE_NAME = 'CORPDATA.DEPT_' CONCAT P_DEPT CONCAT '_T';

  SET STMT = 'DROP TABLE ' CONCAT TABLE_NAME;

  PREPARE S1 FROM STMT;

  EXECUTE S1;

  SET STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT

  '( EMPNO CHAR(6) NOT NULL,

  FIRSTNME VARCHAR(12) NOT NULL,

  MIDINIT CHAR(1) NOT NULL,

  LASTNAME CHAR(15) NOT NULL,

  SALARY DECIMAL(9,2))';

  PREPARE S2 FROM STMT;

  EXECUTE S2;

  SET STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT

  'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY

  FROM CORPDATA.EMPLOYEE

  WHERE  WORKDEPT = ?';

  PREPARE S3 FROM STMT;

  EXECUTE S3 USING P_DEPT;

END;

 

．DB2下在過程中，直接sql語句，如何定義和使用？

答：舉例如下

CREATE PROCEDURE CREATE_DEPT_TABLE (IN P_DEPT CHAR(3))

  LANGUAGE SQL

  BEGIN

  DECLARE STMT CHAR(1000);

  DECLARE MESSAGE CHAR(20);

  DECLARE TABLE_NAME CHAR(30);

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

  SET MESSAGE = 'ok';

   SET TABLE_NAME = 'CORPDATA.DEPT_' CONCAT P_DEPT CONCAT '_T';

  SET STMT = 'DROP TABLE ' CONCAT TABLE_NAME;

  PREPARE S1 FROM STMT;

  EXECUTE S1;

  SET STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT

  '( EMPNO CHAR(6) NOT NULL,

   FIRSTNME VARCHAR(12) NOT NULL,

  MIDINIT CHAR(1) NOT NULL,

  LASTNAME CHAR(15) NOT NULL,

  SALARY DECIMAL(9,2))';

  PREPARE S2 FROM STMT;

  EXECUTE S2;

  SET STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT

  'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY

  FROM CORPDATA.EMPLOYEE

  WHERE  WORKDEPT = ?';

  PREPARE S3 FROM STMT;

  EXECUTE S3 USING P_DEPT;

END;

．DB2是否支援多重事務？是如何實現的？

答: DB2支援多重事務，使用SAVEPOINT的機制管理多重事務處理。允許在一個事務中設定多個儲存點，而出錯是回滾到指定儲存點。

COMMIT

ROLLBACK

SET TRANSACTION

 

SAVEPOINT STOP_HERE  ON ROLLBACK RETAIN CURSORS;

 

SAVEPOINT START_OVER UNIQUE  ON ROLLBACK RETAIN CURSORS；

RELEASE SAVEPOINT START_OVER

． 使用raiserror 99999 ‘xxxx’自定義錯誤資訊，db2如何實現自定義錯誤？

答：使用signal SQLSTATE　＇ii0002＇ set message_text  = ‘dddd’;  指定SQLSTATE資訊返回自定義的錯誤資訊。 注意sqlstate 必須是 5 位字元，可以是 0 – 9 ，不允許大寫字元A-Z和其他特殊字元。不允許前兩個字元是‘00’。Message_text 資訊限制在70位元組長度。

舉例如下

CREATE PROCEDURE raise ( IN rating INTEGER )

  LANGUAGE SQL

  BEGIN

  DECLARE new_salary DECIMAL(9,2);

  DECLARE service DECIMAL(8,0);

  DECLARE v_empno CHAR(6) DEFAULT '123456';

  SELECT salary, current_date - hiredate

  INTO new_salary, service

  FROM employee

  WHERE empno = v_empno;

  IF service < 600

   THEN SIGNAL SQLSTATE 'II001'

  SET MESSAGE_TEXT = 'Insufficient time in service.';

  END IF;

  IF rating = 1

  THEN SET new_salary =

  new_salary + (new_salary * .10);

  ELSEIF rating = 2

  THEN SET new_salary =

  new_salary + (new_salary * .05);

  END IF;

   UPDATE employee

  SET salary = new_salary

  WHERE empno = v_empno;

  END;

 

．Db2中return的限制和使用？

答： return 不允許在中使用。

 

．Db2中如何建立trigger？

答：Db2的trigger 和Sybase 的觸發器有些區別，Sybase中觸發器全部市after方式。

Db2可以定義觸發器的觸發時機(after,before)

具體格式：

create Trigger info_plu_ti after insert on info_plu_tab Referencing New as new for each row

．Db2的CASE 控制語句用法和例子？

答：用法舉例如下：

  CASE evaluation

WHEN 100 THEN UPDATE employee SET salary = salary * 1.3;

WHEN 90 THEN UPDATE employee SET salary = salary * 1.2;

WHEN 80 THEN UPDATE employee SET salary = salary * 1.1;

ELSE UPDATE employee SET salary = salary * 1.05;

END CASE;

或者：

CASE

WHEN evaluation = 100 THEN UPDATE employee SET salary = salary * 1.3;

WHEN evaluation = 90 THEN UPDATE employee SET salary = salary * 1.2;

WHEN evaluation = 80 THEN UPDATE employee SET salary = salary * 1.1;

ELSE UPDATE employee SET salary = salary * 1.05;

END CASE;

 

qq:50839655

to::zhangtao@XHZQ.COM,dekker99@163.com">email:zhangtao@XHZQ.COM,dekker99@163.com

html://