IDC资讯中心
继续上一篇文章的内容
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:zhangtao@xhzq.com,dekker99@163.com
html://www.xhzq.com
