DB2开发基础第1讲

1、DB2开发基础第1讲DB2开发基础第1讲培训大纲一、DB2介绍二、DB2数据类型三、DB2 DDL四、DB2 DML五、DB2函数六、DB2 PL/SQL开发培训大纲一、DB2介绍IBM DB2发展史四十年来，从理论研究到实际应用， IBM DB2在数据库的研究和发展领域做出了巨大的贡献70年代之前，层次型数据库占主导地位1970年，IBM发明了第一个关系型数据库和 SQL语言1992年，IBM将DB2推向开放平台IBM DB2发展史四十年来，从理论研究到实际应用， IBMDB2概念介绍 什么是模式 模式是已命名对象(如表和视图等对象)的集合,模式提供了数据库中对象的逻辑分类. 当创建表、视图

2、或任何其它命名对象时，即隐式创建了模式。或者，可以使用CREATE SCHEMA语句显示创建它。 在创建命名对象时，可用特定模式的名称来限定该对象的名称。命名对象的名称有两个部分，其中，名称的前一个部分是对其指定对象的模式名。如果不指定模式名，则系统自动选择缺省模式。（缺省模式的名称是执行语句的用户的权限ID）DB2概念介绍 什么是模式培训大纲一、DB2介绍二、DB2数据类型三、DB2 DDL四、DB2 DML五、DB2函数六、DB2 PL/SQL开发培训大纲一、DB2介绍DB2数据类型-字符串 定长字符串 char(x) 是定长字符串。长度属性x必须在1和254之间 变长字符串 变长字符串有

3、两种类型：varchar、long varchar 图形字符串 定长图形字符串 GRAPHIC(x)是定长字符串，长度属性x必须在1和127之间 变长图形字符串 变长图形字符串有两种类型：vargraphic、long vargraphic 二进制字符串 二进制字符串是一个字节序列。它用于保存非传统数据，如图象等DB2数据类型-字符串 定长字符串 DB2数据类型数字 所有的数字都有符号和精度。精度是除符号以外的位数或数字数。 smallint smallint(小型整数)是精度为5位的两字节整数 integer integer(整数)，是精度为10位的四字节整数。 bigint 一个bigin

4、t(大整数),是一个精度为19位的8字节整数。 real real（单精度浮点数）是实数的32位近似值 double double(双精度浮点数)，是实数的64位近似值。DOUBLE也称FLOAT. decimal(p,s) decimal 是一个十进制数，小数点的位置由数字的精度(p)和小数位(s)确定。精度是数字的总位数，必须小于32,小数位是小数部分数字的位数且总是小于或等于精度值。如果未指定精度和小数位，则十进制值的缺省精度为5，缺省小数位为0DB2数据类型数字 所有的数字都有符号和精度。精度是除DB2数据类型-日期时间 日期时间值是日期、时间以及时间戳记（一个格式为yyyyMMddh

5、hmmss的表示有效日期和时间的14位字符串）的表示法。 日期时间值可以用于某些算术运算和字符串运算并且与某些字符串是相容的，但是它们既非字符串，也非数字。 日期 日期值分为三个部分（年、月以及日） 时间 时间是用24小时制式来指定一天内的时间的值，分为三个部分（小时、分钟和秒） 时间戳记 时间戳记是指定日期和时间的值，分为7个部分（年、月、日、小时、分钟、秒以及微秒）DB2数据类型-日期时间DB2数据类型对象类型对象类型（BLOB、CLOB、DBCLOB）DB2数据类型对象类型DB2数据类型汇总DB2数据类型汇总DB2数据类型数据类型类型特性示例或范围CHAR(15)定长字符串最大长度为25

6、4Sunny day VARCHAR(15)变长字符串最大长度为32672Sunny daySMALLINT数字长度为2个字节，精度为5位范围为：-3276832767INTEGER数字长度为4个字节，精度为10位范围为：21474836482147483647BIGINT数字长度为8个字节，精度为19位REAL数字单精度浮点32位近似值DOUBLE数字双精度浮点64位近似值DECIMAL(5,2)数字精度是5，小数位是2DATE日期由三部分组成的值2011-10-09TIME时间由三部分组成的值16.07.34TIMESTAMP日期时间由七部分组成的值2011-10-09- 16.07.34

7、.000000DB2数据类型数据类型类型特性示例或范围CHAR(15)定长培训大纲一、DB2介绍二、DB2数据类型三、DB2 DDL四、DB2 DML五、DB2函数六、DB2 PL/SQL开发培训大纲一、DB2介绍DB2表、视图语句创建表语句 CREATE TABLE PERS ( ID SMALLINT NOT NULL, NAME VARCHAR(9), DEPT SMALLINT WITH DEFAULT 10, JOB CHAR(5), YEARS SMALLINT, SALARY DECIMAL(7,2), COMM DECIMAL(7,2), BIRTH_DATE DATE )更改

8、表 alter table db2inst1.PERS alter column JOB set data type char(10) 删除表 drop table db2inst1.PERSDB2表、视图语句创建表语句创建表表、数据、索引可以创建在默认的表空间上Create table artists( artno smallint not null, name varchar(50), picture blob(2M) not logged compact )Create table artists( artno smallint not null primary key, name va

9、rchar(50), picture blob(2M) not logged compact)In dms01Index in dms02 -Supported for DMS onlyLong in dms03;Create index idx_name ON artists(name);Table data .dms01Indexes for primary key and idx_name.dms02Picture(blob) data .dms03创建表表、数据、索引可以创建在默认的表空间上SESSION临时表使用临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是

10、在一个SESSION内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。建立临时表最好加上with replace选项，这样可以不显示地drop临时表。被程序创建，并在程序退出时自动清除。只能被创建的程序访问。交易可以选择不记日志连接断开后，自动清除性能 没有系统编目表的开销 没有行锁 可以不选择日志 没有认证的选择可以创建索引 可以给全局临时表创建正规的索引提供统计信息的支撑 SESSION临时表使用临时表只能建立在用户临时表空间上，而定义全局临时表CREATE user temporary tablespace apptemps managed by system using

11、(apptemps);Declare global temporary table t_employees like employee not logged;Declare global temporary table session.t_dept(deptid char(6),deptname char(20) on commit delete rows not logged;Declare global temporary table session.t_projects as (full select) definition only ON commit preserve rows no

12、t logged with replace in tablespace apptemps;DECLARE GLOBAL TEMPORARY TABLE ETL_TEMPSQL (TGTSCHEMA VARCHAR(128), TGTTABLE VARCHAR(128), XH INTEGER, TYPE CHARACTER(1), SQL VARCHAR(3000) ) NOT LOGGED WITH REPLACE ;定义全局临时表CREATE user temporary tDB2表、视图语句 DB2表、视图语句 create view staff_only as select id,na

13、me,dept,job,years from staff where jobMgr and dept=20 数据库使用Check option 来限制每一条通过视图来插入的数据。 create view Fixed_income(lname,depart,jobtitile,newsalary) as select name,dept,job,salary from pers where jobSales with check option 条件 jobSales将为通过该视图的每一条插入/更新的语句作检查。 除了校验选择项以外，先前视图中的更新等效于对基表PERS的更新： update pe

14、rs set salary=salary*1.10 where name=Li and jobSales DB2表、视图语句 DB2表、视图语句自增长列表中的数值型列可以在插入每行新数时，自动产生一个唯一的数值每张表最多只能有一个自增长列值的产生方式有ALWAYS或BY DEFAULT GENERATED ALWAYS -值总是被数据库产生 -应用程序不能显示的提供一个新值 GENERATED BY DEFAULT -插入式，如果程序没提供，数据库才产生一个新值 -数据库不能保证每次产生的值唯一自增长列表中的数值型列可以在插入每行新数时，自动产生一个唯一GENERATED ALWAYS例子CR

15、EATE TABLE inventory( partno integer generated always as identity(start with 100,increment by 1), description char(20) );INSERT INTO inventory values(default,door); -100INSERT INTO inventory(description) values(hinge); -101INSERT INTO inventory values(102,window); -error,value always generatedCOMMIT

16、;INSERT INTO inventroy(description) values(lock); -102;Rollback;INSERT INTO inventory(description) values(frame); 103COMMIT;Select * from inventory; Door hinge103 frameGENERATED ALWAYS例子CREATE TABLEGENERATED BY DEFAULTCREATE TABLE inventory( partno integer primary key generated by default as identit

17、y(start with 100,increment by 1), description char(20) );Insert into inventory values(DEFAULT,door); -100Insert into inventory (description) values(hinge); -101Insert into inventory values(101,bolt); -error,duplicate partnoInsert into inventory values(102,window); -102COMMIT;INSERT INTO inventory(de

18、scription) values(lock); error,duplication partnoInsert into inventory(description) values(lock); 103Rollback;Insert into inventory (description) values(frame); 104Commit;GENERATED BY DEFAULTCREATE TAB序列不同于自增长字段，序列是独立于表的,例如： create sequence myseq start with 1 increment by 1 no cycle; 语法： nextval for

19、 prevval for 例如： insert into t1 values(nextval for myseq,); select prevval for myseq from sysibm.sysdummy1;序列不同于自增长字段，序列是独立于表的,例如：创建索引Index characteristics assending or descending unique or non-unique bi-directional(no storage overhead) compound cluster include columns例如： create unique index itemno

20、on albums(itemno desc); create index item on stock(itemno) allow reverse scans; create index clx1 on stock(shipdate) cluster allow reverse scans; create unique index incidx on stock(itemno) include(shipdate);创建索引Index characteristics数据完整性（外键）Alter table department add constraint pk_deptno primary ke

21、y (deptno);Alter table employee add constraint fk_workdept foreign key(workdept) references department;数据完整性（外键）Alter table departmenCheck限制的使用保证表数据的完整性一旦定义，插入和更新记录必须遵守，否则失败 create table artists( artno smallint not null, name varchar(50), classification char(1) not null, picture blob(2M) not logged

22、compact, constraint classify check(classification in (C,E,P,R)In dms01;可以使用alter talbe drop constraint 的方式删除限制。Check限制的使用保证表数据的完整性物化查询表（MQT)定义基于一定结果统计值的表，可以优化数据库的执行计划 SQL compiler 判断到可以使用MQT运行，运行效率远远高于使用一般表的速度。MQTBaseTable #1BaseTable #3BaseTable #2Query #4Query #5Query #3Query #2Query #1物化查询表（MQT)定

23、义基于一定结果统计值的表，可以优化数据物化查询表的选项数据选项 Data Initially deferred刷新频率 refresh deferred refresh immediateOptimizer-aware enable query optimization disable query optimization两种类型 maintained by system 如果基础表被更新，系统自动根据refresh选项更新 maintained by user 仅仅应用REFRESH DEFERRED 表；通过客户应用维护和更新物化表 手工刷新物化查询表物化查询表的选项数据选项MQT例子CR

24、EATE TABLE DB2INST2.summary_customer_order_2002As( select trans_dt,status,count(*) as count_all, sum(amount) as sum_amount, count(amount) as count_amount from db2inst2.customer_order group by trans_dt,status)Data initially deferredRefresh immediateEnable query optimizationMaintained by system;MQTBas

25、eTable #1BaseTable #3BaseTable #2Query #4Query #5Query #3Query #2Query #1MQT例子CREATE TABLE DB2INST2.sumDesign AdvisorDesign Advisor能帮助您设计和定义合适的索引，也可以使用db2advis来运行命令。 db2advis -d database-name -w workload-name -s statement -I filename -g -a userid -l disk-limit -/passwd -t max-advise-time -h -p -o ou

26、tfile 例如： db2advis -d prototype w prouction l 53 t 20Design AdvisorDesign Advisor能帮有用的命令List tablesList tables for user | all | system | schemaList tables for schema schemaDescribe table table nameDescribe select statementGet instance 获得当前的实例名有用的命令List tables数据一致性和完整性事务在多用户环境中并发的几种情况：脏读这种情况发生在一个事务读取

27、还未提交的数据时。例如：事务1改变了一行数据，而在事务1提交修改之前事务2读取了事务1所改变的行的数据。如果事务1回滚了修改，那么事务2就读取了实际上并不存在的数据。不可重复读这种情况发生在事务两次读取同一行中的数据却得到不同的数值时。例如：事务1读取了一行数据，而事务2改变或删除了那些行并提交了修改。如果事务1再次读取了那一行，那么，事务1就得到了不同的值（如果那一行被更新）或发现那一行已不存在（如果那一行被删除）幻影数据这种情况发生在一行数据满足搜索规则，却在开始没有被看到时。例如：事务1读取了一系列满足搜索规则的行，而事务2插入了一个满足事务1搜索规则的行。如果事务1再次查询语句，就会得

28、到不同的一系列行。数据一致性和完整性事务在多用户环境中并发的几种情况：事务隔离级别DB2使用四个不同的事务隔离级别来实现并发。可重复读可重复读(RR)锁定应用程序在一个事务中引用的所有行，可以防止幻影行。例如如果扫描10000行数据并对它们进行过滤，尽管只有10行满足条件，但仍会锁定全部的10000行数据。在可重复读隔离级别下，其他事务不能更新、删除、插入数据，执行的事务看不到其他事务的未提交修改。可重复读可能会持有大量锁定最终导致行锁定升级到表锁定。读稳定性读稳定性(RS)只锁定满足查询条件的行，有可能看到幻影行。也就是如果一个使用读稳定性隔离级别的事务多次执行同一个查询，该事务可能会得到别

29、的事物插入的符合该搜索规则的新行。游标稳定性游标稳定性(CS)是缺省隔离级别，事务只锁定当前游标位置的行，可能出现幻影行和不可重复读取数据。别的事务不能对游标位置的行进行更新或删除，但还能在被锁定行的两端插入、删除或修改行。未提交读未提交读(UR)允许一个事务访问其他事务未提交的修改数据，可能出现幻影行和不可重复读取数据。使用未提交读不需要对任何行锁定，适用于只读表上的查询和那些并不关心是否可从其他应用程序中看到未提交的数据。举例说明为查询语句指定隔离级别：select*fromstockwithur 事务隔离级别DB2使用四个不同的事务隔离级别来实现并发。事务隔离级别DB2通用数据库支持的四

30、种隔离级别：DB2事务隔离级别脏读不可重复读幻影数据未提交读有有有游标稳定性无有有读稳定性无无有可重复读无无无事务隔离级别DB2通用数据库支持的四种隔离级别：DB2事务隔培训大纲一、DB2介绍二、DB2数据类型三、DB2 DDL四、DB2 DML五、DB2函数六、DB2 PL/SQL开发培训大纲一、DB2介绍DML语句INSERT语句 插入单条数据 INSERT INTO 表名(字段1，字段2,) VALUES(值1，值2,) 插入多条数据 INSERT INTO 表名(f1,f2,) VALUES(v1,v2,), (v11,v22,), 根据其他表数据插入多条数据 INSERT INTO

31、表名(f1,f2,) SELECT f11,f12 from DML语句INSERT语句UPDATE语句 UPDATE table_name set f1=v1, f2=v2 where 条件 使用UPDATE语句来更改表中的数据。使用此语句，可以更改满足where子句搜索条件的每行中的一列或多列的值。 下列示例更新ID为410的雇员的信息： UPDATE pers SET job=Prgmr,SALARY=salary+300 WHERE id=410; SET子句指定要更新的列并提供值UPDATE语句DELETE语句 DELETE FROM table_name WHERE 条件 注意：如

32、果去掉where 条件的话，将删除表中所有的记录。DELETE语句 DELETE FROM table_nSELECT语句 选择列 1.选择某些字段 SELECT f1,f2, FROM table_name 2.选择表中全部字段 SELECT * from table_name 3.选择行 SELECT f1,f2, FROM table_name WHERE 条件10 and 条件20 谓词X=yXyXyx=yIs null/is not nullSELECT语句谓词X=yXyXyx=yIs排序 SELECT f1,f2, FROM table_name WHERE 条件 ORDER BY

33、 f1,f2 ASC/DESC ASC:升序（默认） DESC:降序去除重复行SELECT DISTINCT f1,f2 FROM 表名排序运算次序 1.FROM 2.WHERE 3.JOIN 4.GROUP BY 5.HAVING 6.ORDER BY运算次序 1.FROM培训大纲一、DB2介绍二、DB2数据类型三、DB2 DDL四、DB2 DML五、DB2函数六、DB2 PL/SQL开发培训大纲一、DB2介绍DB2函数字符串函数（一）ASCII返回字符串首字符对应的ASCII值(整型)CHAR返回固定长度的字符串CHARACTER_LENGTH/CHAR_LENGTH返回字符串长度CHR返

34、回ASCII对应的字符CONCAT/|连接两个字符串，任意一个为null,返回nullLCASE/LOWER返回小写UCASE/UPPER返回大写LTRIM/RTRIM去掉左侧或右侧的空格LEFT/RIGHT返回最左右边的LENGTH个字符DB2函数字符串函数（一）ASCII返回字符串首字符对应的ADB2函数字符串函数（续二）INSERTINSERT(ARG1,POS,SIZE,ARG2)返回一个字符串，将ARG1从POS处删除SIZE个字符，将ARG2插入该位置LOCATELOCATE(ARG1,ARG2,POS)函数在ARG2中查找ARG1第一次出现的位置。如果指定POS,则从POS处开始

35、查找ARG1第一次出现的位置POSSTRPOSSTR(EXP1,EXP2)函数返回EXP2在EXP1中的位置REPEATREPEAT(ARG1,NUM_TIMES)函数返回ARG1被重复NUM_TIMES次的字符串REPLACEREPLACE(exp1,exp2,exp3)函数返回用exp3代替exp1中所有的exp2SPACESPACE(size)函数返回一个包含在SIZE个空格的字符串SUBSTRSUBSTR(arg1,pos,length)函数返回arg1中，pos 位置开始的length个字符。如果没有指定length,则返回剩余的字符。DB2函数字符串函数（续二）INSERTINSE

36、RT(ARGDB2函数字符串函数（续三）SUBSTRINGSubstring(exp1,start,length)返回子字符串左边第start个字符开始起length个字符的部分CHARINDEXCHARINDEX(substring,expression)返回字符串中某个指定的子串出现的开始位置，REPLICATE返回一个重复指定次数的字符串REVERSE返回反序字符串DB2函数字符串函数（续三）SUBSTRINGSubstriDB2函数数学函数ABS/ABSVAL返回绝对值CEILING/CEIL返回上整FLOOR返回下整MODMOD(N,M) 返回余数POWERPOWER(N,M)返回N

37、的M次方RANDRAND(N)返回随机数ROUNDRound(n,m)SIGN符号函数SQRT返回算术平方根TRUNCTRUNC(N,M)返回截断后的结果DB2函数数学函数ABS/ABSVAL返回绝对值CEILINDB2函数日期函数（一）DAY返回日DAYNAME返回一个日期的星期部分DAYOFWEEK返回一个日期的星期部分DAYOFYEAR返回一个日期的年日部分(1366)DAYS返回一个日期对应的整型值Current date获取当前日期Current time获取当前时间Current timestamp返回当前日期时间戳year返回年month返回月hour返回小时DB2函数日期函数（

38、一）DAY返回日DAYNAME返回一个日DB2函数日期函数（续二）MINUTE返回分钟SECOND返回秒microsecond返回毫秒timestampdiffTimestampdiff(inter1,char(ts1-ts2)Inter1:1-毫秒 2-秒4分 8小时 16-天 32-周 64-月 128-季度 256-年dateaddDateadd(datepart,number,date)返回指定日期date加上指定的额外时间间隔number产生的日期datediffDatediff(datepart,date1,date2)返回两个指定日期在datepart方面的不同之处，即date2

39、超过date1的差距值，其结果值是一个带有正负号的整数值DB2函数日期函数（续二）MINUTE返回分钟SECOND返DB2函数日期函数（续三）DATENAMEDatename(datepart,date)以字符串的形式返回日期的指定部分，由datepart来指定DATEPARTDatepart(datepart,date)以整数值的形式返回日期的指定部分，此部分由datepart来指定Datepart(dd,date) 等同于day(date)Datepart(mm,date) 等同于month(date)Datepart(yy,date) 等同于year(date)GETDATE以datet

40、ime的缺省格式返回系统当前的日期和时间Last_day自定义D+1 month-day(d+1 month) dayDB2函数日期函数（续三）DATENAMEDatename(DB2函数位操作函数BITANDBitand(7,4)=4BITANDNOTBitandnot(2049,2048) 1,清除第一个参数对应的位BITORORBITXORXORBITNOTNOTBITNOT(CAST(2 AS SMALLINT)=-3DB2函数位操作函数BITANDBitand(7,4)=4BDB2函数转换函数CASTCast(1 as varchar(10)BIGINT支持输入参数为数字型，字符串，

41、和日期型若为日期型：Date 返回yyyyymmddTime 返回hhmmssTimestamp 返回yyyymmddhhmmssDATE返回一个日期DATE(1988-12-25)DATE(35) 0001-02-24DB2函数转换函数CASTCast(1 as varcharDB2函数其它函数CARDINALITY返回数组元素个数COALESCE返回第一个非null函数DATAPARTITIONNUM这个函数返回数据行所属的分区序号。从开始。Select datapartitionnum(empno) from employeeDBPARTITIONNUM返回数据行所属的分区号DECODE

42、DECODE(c1,7,a,6,b,c)CASE WHEN ENDCase c1 when 7 then a when 6 then b else cENDVALUE返回一个非空的值，当其第一个参数非空，直接返回第一个参数的值，否则返回第二个参数的值DB2函数其它函数CARDINALITY返回数组元素个数CODB2函数专用寄存器,所有这些专用寄存器都可以通过在名称中加下划线来引用.例如:CURRENT_DATECURRENT DATECURRENT TIMECURRENT TIMESTAMPCURRENT USERCURRENT PATH函数路径CURRENT SCHEMA当前的模式USER当

43、前登录的用户DB2函数专用寄存器,所有这些专用寄存器都可以通过在名称中加常用函数列函数 列函数对列中的一组值进行运算以得到单个结果值。 示例如下: AVG 平均值 COUNT 非空记录数 MAX 最大值 MIN 最小值标量函数 标量函数对一个单一值进行某个运算以返回另一个单一值。下列就是一些由DB2通用数据库提供的标量函数。 ABS 返回绝对值 常用函数列函数常用函数DB2常用函数有以下一些：类似oracle中decode的判断操作例如：selectcasea1 when1thenn1 when2thenn2 elsen3 endasaa1 from 表名 类似charindex查找字符在字符

44、串中的位置例如：Locate(y,dfdasfay)查找字符y在字符串dfdasfay中的位置类似datedif计算两个日期的相差天数函数DAYS例如：days(date(2001-06-05)days(date(2001-04-01) 预防空字段的处理函数COALESCE例如：处理字符型COALESCE(NAME,) 处理数字型COALESCE(BOX_NUM,0)常用函数DB2常用函数有以下一些：示例SELECT current date from sysibm.sysdummy1Insert into user_log values(user,current timestamp)Valu

45、es current schemaSet current schema myschemaSchema example:Connect to sample user db2adminCreate table t1(c1 int,c2 varchar(10) current schema=db2adminT1 is created as db2admin.t1Set schema sch2Create table t1(x int,y int)t1 is created as sch2.t1示例SELECT current date from sys使用NULLNULL不能使用=号TABLE t1

46、 TABLE t2C1 C2- - 1 1 2 2 - - 4 4Select * from t1 where c1=null select * from t1,t2 where t1.c1=t2.c1=error =3 rows returnedSelect * from t1 where t1 is null=1 row returnedNULLNULL空值 空值是一个区别于所有非空值的特殊值。它意味着行中的那一列无任何其他值。所有数据类型都存在空值。使用NULLNULL不能使用=号NULLNULL空值字符串连接NULLNULL表示“未知状态”。在未知状态下，使用NULL与其他值连接，其他

47、值还只是未知状态。(ANSI标准) SET v1=null; select hello | v1 from . -null 正确方法： set v1=null; select hello | coalesce(v1,) from -hello字符串连接NULLNULL表示“未知状态”。在未知状态下，使NULL值Null表示一个未知的状态表的列可以包含NOT NULL的限制NOT NULL必须包含到UNIQUE和PRIMARY限制中例如： create tale employee ( id smallint not null primary key, name varchar(30) not n

48、ull, department smallint not null with default 10, date_of_birth date );NULL值Null表示一个未知的状态培训大纲一、DB2介绍二、DB2数据类型三、DB2 DDL四、DB2 DML五、DB2函数六、DB2 PL/SQL开发培训大纲一、DB2介绍存储过程结构CREATE PROCEDURE sqlsamp ( IN dpt varchar(4) ) SPECIFIC sqlsamp RESULT SETS 1 LANGUAGE SQL P1: BEGIN DECLARE cursor1 CURSOR WITH RETUR

49、N FOR SELECT DEPARTMENT.DEPTNO, DEPARTMENT.DEPTNAME, EMPLOYEE.FIRSTNME, EMPLOYEE.MIDINIT,EMPLOYEE.LASTNAME FROM DEPARTMENT, EMPLOYEE WHERE EMPLOYEE.EMPNO =DEPARTMENT.MGRNO AND DEPARTMENT.DEPTNO = dpt; OPEN cursor1; END P1 CREATE PROCEDURE Sp1BEGIN ATOMIC/NOT ATOMICNOT ATOMIC:如果发生一个未处理的错误条件，不会回滚SQL语句

50、。ATOMIC: 在一条ATOMIC复合语句的执行期间，如果其中发生任何未处理的错误条件， 那么在此之前已执行的所有语句都被回滚。ATOMIC语句不能被嵌套在其他 ATOMIC复合语句中。存储过程结构参数定义DB2储存过程的参数分为两部分：输入和输出参数。参数表示方式:输入参数用IN开头输出参数用OUT开头既是输入又是输出参数用INOUT开头举例说明：create procedure sp_sample ( in var0 varchar(10), out var1 varchar(20), inout var2 varchar(20)参数定义DB2储存过程的参数分为两部分：输入和输出参数。变

51、量定义 存储过程中可以使用关键字DECLARE定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：CREATE PROCEDURE P2( INOUT a VARCHAR(8),OUT b INTEGER) LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0;DECLARE var2 VARCHAR(5) DEFAULT a | bc;- other SQL statements END BEGIN ATOMIC END注意声明的顺序变量定义 存储过程中可以使用关键字DECLARE定义变量定义全局变量：CRE

52、ATE VARIABLE var_name DATATYPE default value;说明：会话全局变量是在存储过程之外声明的。在存储过程中使用返回游标：1.使用以下语句声明一个结果集定位符： DECLARE rs_location_var1 RESULT_SET_LOCATOR VARYING;2.将这个结果集定位符与调用者过程相关联： ASSOCIATE RESULT SET LOCATOR(rs_locator_var1) WITH PROCEDURE proc_called;3.分配从调用过程指向结果集的指标 ALLOCATE cursor1 CURSOR FOR RESULT S

53、ET rs_locator_var1;Create procedure use_nested_cursor(deptin int,out tot_dept_com dec(12,2)Begin declare loc1 result_set_locator varying; call result_from_cursor(deptin); allociate result set locator(loc1) with procedure result_form_cursor; allocate c1 cursor for result set loc1; fetch from c1 into

54、变量定义全局变量：赋值语句存储过程使用关键字SET给变量赋值。举例说明:CREATE PROCEDURE P2(INOUT a VARCHAR(8), OUT b INTEGER) LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0; DECLARE var2 VARCHAR(5) DEFAULT a | bc; SET var1 = 0; SET var2 = var2 | def; SET a = var1; END 赋值语句存储过程使用关键字SET给变量赋值。条件控制语句1条件控制语句包括以下几种：IF THEN ELSEIF THEN E

55、LSE END IF举例说明:IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; ELSEIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus =500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empn

56、o = employee_number; END IF ; 条件控制语句1条件控制语句包括以下几种：条件控制语句2CASEsimple CASE:语句用于根据一个字面值进入某个逻辑 searched CASE:语句用于根据一个表达式的值进入某个逻辑举例说明：CASE WHEN v_workdept = A00 THEN UPDATE department SET deptname = DATA ACCESS 1; WHEN v_workdept = B01 THEN UPDATE department SET deptname = DATA ACCESS 2; ELSE UPDATE depa

57、rtment SET deptname = DATA ACCESS 3; END CASE 条件控制语句2CASEsimple CASE:语句用于根据循环语句循环语句包括以下几种：LOOP 简单的循环 L1:LOOP SQL statements; LEAVE L1; END LOOP L1;WHILE 循环 WHILE condition DO SQL statements; END WHILE;REPEAT 循环 REPEAT SQL statements; UNTIL condition; END REPEAT;FOR 循环 FOR loop_name AS select * from

58、DO SQL statements; END FOR; 注意，For语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。循环语句循环语句包括以下几种：循环语句示例For循环的例子：CREATE PROCEDURE dept1 ( deptin char(3), out p_counter INT)P1:BEGIN DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS SELECT lastname,hiredate,birthdate FROM employee WHERE workdept=deptin DO INSERT INTO

59、 report_info_dept values(dept_loo.lastname,dept_loop. hiredate,dept_loop.birthdate); set v_counter=v_counter+1; END FOR; SET p_counter=v_counter;END P1; 请注意：该过程没有打开游标、从游标中取数或关闭游标-所有这些都是FOR循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列(例如，dept_loop.lastname)而不必使用本地变量来存储这些值。循环语句示例For循环的例子：常用操作符有以下几种：关系运算符关系运算符

60、有六种：小于、小于等于、大于、大于等于、等于、不等于逻辑运算符逻辑运算符有三种：AND、OR、NOT获取返回码：GET DIAGNOSTICS ret_code=DB_RETURN_STATUS;常用操作符有以下几种：游标游标处理步骤：1)、游标声明2)、打开游标3)、将游标的结果取出到之前已声明的本地变量中4)、关闭游标（如果现在不关闭游标，当过程终止时将隐式的关闭游标）DECLARE CURSOR with return ;OPEN ;FETCH INTO -process data,fetch next row if required CLOSE ; 在SQL过程中，游标还可以用于将结果