Continue to the content of the article
twenty one. How is the code of SQLSTATE in DB2?
A:
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: Cardinal Viology
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 Viology
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 viology
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
twenty two. How to delete the row data of the current cursor, update the action?
A: For example, Declare thisemp Cursor for Select Empno, Lastname, WorkDept, Jobfrom Corpdata.employeefor Update of Job
Open trisemp;
...
Update corpdata.employeeset job =: new-codewhere current of thisemp
...
twenty three. NOT FOUND control cursor in DB2?
A: NOT FOUND is the global variable in DB2, which can be equivalent to
If SQLCODE = 100 Go to Data-Not-Found.
oral
EXEC SQL
WHENEVER NOT FOUND Go to Symbolic-Address
End-exec.
If SQLSTATE = '02000' Go to Data-Not-Found.
twenty four. How does DB2 dynamometer cursors define and use?
A: Give an example
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' contat 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 During the stored procedure, execute the SQL statement directly, how to define and use?
A: For example,
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' contat 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. Does DB2 support multiple transactions? How is it realized?
A: DB2 supports multiple transactions, manages multi-transaction processing using SavePoint mechanisms. Allows multiple saving points in a transaction, and error is rolled back to the specified save point.
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 uses raiserror 99999 'xxxx' custom error message, how does DB2 implements a custom error?
A: Using Signal SqlState 'II0002' Set Message_Text = 'DDDD'; specifying the SQLState information returns a custom error message. Note that SQLState must be 5 characters, which can be 0 - 9, and do not allow uppercase characters A-Z and other special characters. The first two characters are not allowed to be '00'. Message_text information is limited to 70 bytes of length.
For example
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 SALY, 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 = 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 SALY = New_SAlary
WHERE EMPNO = V_EMPNO; END;
28. RETURN Limit and Use in DB2?
A: Return is not allowed to be used in the trigger.
29. How to create TRIGGER in DB2?
A: DB2 Trigger and Sybase triggers are somewhat different, and the triggers in Sybase all the city After mode.
DB2 can define triggering timing of triggers (after, before "
Specific format:
Create Trigger Info_PLU_TI AFTER INSERT ON INFO_PLU_TAB Referencing New As New for Each Row
30. DB2 CASE control statement usage and examples?
A: Usage is as follows:
Case Evaluation
WHEN 100 THEN UPDATE EMPLOYEE SET SALY = SALARY * 1.3;
When 90 TEN UPDATE EMPLOYEE SET SALARY = SALARY * 1.2;
When 80 Ten Update Employee Set Salry = SALARY * 1.1;
Else Update Employee Set Salry = Salary * 1.05;
End Case;
or:
Case
When evAluation = 100 THEN UPDATE EMPLOYEE SET SALY = SALARY * 1.3;
When evataration = 90 THEN UPDATE EMPLOYEE SET SALARY = SALARY * 1.2;
When evataration = 80 THEN UPDATE EMPLOYEE SET SALY = SALARY * 1.1;
Else Update Employee Set Salry = Salary * 1.05;
End Case;
QQ: 50839655
Email: zhangtao @ xhzq.com, dekker99 @ 163.com
HTML: //www.xhzq.com
