继续上一篇文章的内容
21．Db2中SQLSTATE的代号如何定义？
答：如下
Class Code 00: Unqualified Successful Completion
Class Code 01: Warning
Class Code 02: No Data
Class Code 07: Dynamic SQL Error
Class Code 08: Connection Exception
Class Code 09: Triggered Action Exception
Class Code 0A: Feature Not Supported
Class Code 0E: Invalid Schema Name List Specification
Class Code 0F: Invalid Token
Class Code 0K: Resignal When Handler Not Active
Class Code 20: Case Not Found for Case Statement
Class Code 21: Cardinality Violation
Class Code 22: Data Exception
Class Code 23: Constraint Violation
Class Code 24: Invalid Cursor State
Class Code 25: Invalid Transaction State
Class Code 26: Invalid SQL Statement Identifier
Class Code 27: Triggered Data Change Violation
Class Code 28: Invalid Authorization Specification
Class Code 2D: Invalid Transaction Termination
Class Code 2E: Invalid Connection Name
Class Code 2F: SQL Function Exception
Class Code 34: Invalid Cursor Name
Class Code 38: External Function Exception
Class Code 39: External Function Call Exception
Class Code 3B: Savepoint Exception
Class Code 3C: Ambiguous Cursor Name
Class Code 42: Syntax Error or Access Rule Violation
Class Code 44: WITH CHECK OPTION Violation
Class Code 46: Java Errors
Class Code 51: Invalid Application State
Class Code 54: SQL or Product Limit Exceeded
Class Code 55: Object Not in Prerequisite State
Class Code 56: Miscellaneous SQL or Product Error
Class Code 57: Resource Not Available or Operator Intervention
Class Code 58: System Error
22．如何对当前游标的行数据进行删除，更新操作？
答：例如 DECLARE THISEMP CURSOR FOR SELECT EMPNO, LASTNAME,
WORKDEPT, JOB
FROM CORPDATA.EMPLOYEE
FOR UPDATE OF JOB ;
open THISEMP ;
…
UPDATE CORPDATA.EMPLOYEE
SET JOB = :NEW-CODE
WHERE CURRENT OF THISEMP
…
23．Db2中使用 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.
24．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;
25．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;
26．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
27．Sybase 使用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;
28．Db2中return的限制和使用？
答： return 不允许在触发器中使用。
29．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
30．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
email:[email protected],[email protected]
html://www.xhzq.com 
|
本类阅读TOP10