分类：
2009-12-14 19:21:47
DB2 提供了关连式资料库的查询语言sql(structured query language)，是一种非常口语化、既易学又易懂的语法。此一语言几乎是每个资料库系统都必须提供的，用以表示关连式的操作，包含了资料的定义（ddl）以及资料的处理（dml）。sql原来拼成sequel，这语言的原型以"系统 r"的名字在 ibm 圣荷西实验室完成，经过ibm内部及其他的许多使用性及效率测试，其结果相当令人满意，并决定在系统r 的技术基础发展出来 ibm 的产品。而且美国国家标准学会（ansi）及国际标准化组织（iso）在1987遵循一个几乎是以 ibm sql 为基础的标准关连式资料语言定义。
一、资料定义 DDL（data definition language)
资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。
１、建表格：
create table table_name(
column1 datatype [not null] [not null primary key],
column2 datatype [not null],
...）
说明：
datatype --是资料的格式，详见表。
nut null --可不可以允许资料有空的（尚未有资料填入）。
primary key --是本表的主键。
２、更改表格
alter table table_name
add column column_name datatype
说明：增加一个栏位（没有删除某个栏位的语法。
alter table table_name
add primary key (column_name)
说明：更改表得的定义把某个栏位设为主键。
alter table table_name
drop primary key (column_name)
说明：把主键的定义删除。
３、建立索引
create index index_name on table_name (column_name)
说明：对某个表格的栏位建立索引以增加查询时的速度。
４、删除
drop table_name
drop index_name
二、DDL的资料形态 datatypes
smallint 16 位元的整数。
interger 32 位元的整数。
decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点后有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float 32位元的实数。
double 64位元的实数。
char(n) n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。
date 包含了 年份、月份、日期。
time 包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
三、资料操作DML （data manipulation language)
资料定义好之後接下来的就是资料的操作。资料的操作不外乎增加资料（insert)、查询资料（query）、更改资料（update) 、删除资料（delete）四种模式，以下分 别介绍他们的语法：
１、增加资料：
insert into table_name (column1,column2,...)
values ( value1,value2, ...)
说明：
1.若没有指定column 系统则会按表格内的栏位顺序填入资料。
2.栏位的资料形态和所填入的资料必须吻合。
3.table_name 也可以是景观 view_name。
insert into table_name (column1,column2,...)
select columnx,columny,... from another_table
说明：也可以经过一个子查询（subquery）把别的表格的资料填入。
２、查询资料：
基本查询
select column1,columns2,...
from table_name
说明：把table_name 的特定栏位资料全部列出来
select *
from table_name
where column1 = xxx
[and column2 > yyy] [or column3 <> zzz]
说明：
1.''''*''''表示全部的栏位都列出来。
2.where 之後是接条件式，把符合条件的资料列出来。
select column1,column2
from table_name
order by column2 [desc]
说明：order by 是指定以某个栏位做排序，[desc]是指从大到小排列，若没有指明，则是从小到大排列
组合查询
组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的表格才能够得到结果的。
select *
from table1,table2
where table1.colum1=table2.column1
说明：
1.查询两个表格中其中 column1 值相同的资料。
2.当然两个表格相互比较的栏位，其资料形态必须相同。
3.一个复杂的查询其动用到的表格可能会很多个。
整合性的查询：
select count (*)
from table_name
where column_name = xxx
说明：查询符合条件的资料共有几笔。
select sum(column1)
from table_name
说明：1.计算出总和，所选的栏位必须是可数的数字形态。
2.除此以外还有 avg() 是计算平均、max()、min()计算最大最小值的整合性查询。
select column1,avg(column2)
from table_name
group by column1
having avg(column2) > xxx
说明：1.group by: 以column1 为一组计算 column2 的平均值必须和 avg、sum等整合性查询的关键字一起使用。
2.having : 必须和 group by 一起使用作为整合性的限制。
复合性的查询
select *
from table_name1
where exists (
select *
from table_name2
where conditions )
说明：1.where 的 conditions 可以是另外一个的 query。
2.exists 在此是指存在与否。
select *
from table_name1
where column1 in (
select column1
from table_name2
where conditions )
说明：1. in 後面接的是一个集合，表示column1 存在集合里面。
2. select 出来的资料形态必须符合 column1。
其他查询
select *
from table_name1
where column1 like ''''x%''''
说明：like 必须和後面的''''x%'''' 相呼应表示以 x为开头的字串。
select *
from table_name1
where column1 in (''''xxx'''',''''yyy'''',..)
说明：in 後面接的是一个集合，表示column1 存在集合里面。
select *
from table_name1
where column1 between xx and yy
说明：between 表示 column1 的值介於 xx 和 yy 之间。
３、更改资料：
update table_name
set column1=''''xxx''''
where conditoins
说明：
1.更改某个栏位设定其值为''''xxx''''。
2.conditions 是所要符合的条件、若没有 where 则整个 table 的那个栏位都会全部被更改。
４、删除资料：
delete from table_name
where conditions
说明：删除符合条件的资料。
说明：关于where条件后面如果包含有日期的比较，不同数据库有不同的表达式。具体如下：
(1)如果是access数据库，则为：where mydate>#2000-01-01#
(2)如果是oracle数据库，则为：where mydate>cast(''''2000-01-01'''' as date)
或：where mydate>to_date(''''2000-01-01'''',''''yyyy-mm-dd'''')
在delphi中写成：thedate=''''2000-01-01'''';
query1.sql.add(''''select * from abc where mydate>cast(''''+''''''''''''''''+thedate+''''''''''''''''+'''' as date)'''');
如果比较日期时间型，则为：where mydatetime>to_date(''''2000-01-01 10:00:01'''',''''yyyy-mm-dd hh24:mi:ss'''')
审校by lei
--------------
1、组合语句执行
BEGIN ATOMIC
表达式1 分号 空格/回车
表达式2 分号 空格/回车
END
2、应该限制访问权限的表（应该撤销这些表PUBLIC SELECT访问权）
SYSCAT.DBAUTH
SYSCAT.TABAUTH
SYSCAT.PACKAGEAUTH
SYSCAT.INDEXAUTH
SYSCAT.COLAUTH
SYSCAT.PASSTHRUAUTH
SYSCAT.SCHEMAAUTH
比较有用的目录表
SYSCAT.COLUMNS：包含每一行对应于表或视图中定义的列
SYSCAT.INDEXCOLUSE：包含每一行包含的所有列
SYSCAT.INDEXES：包含每一行对应于表或视图中定义的每个索引
SYSCAT.TABLES：所创建每个表，视图，别名对应其中一行
SYSCAT.VIEWS：所创建每个视图对应其中一行或几行
通过索引保持数据唯一性：CREATE UNIQUE INDEX INDEXNAME ON TABLE (COLUMN)
消除重复行：SELECT DISTINCT COLUMN FROM TABLE
3、DB2关于时间的一些函数
得到当前时间的年份、月份、天、小时等等：
YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)
分别得到当时的日期和时间
DATE (current timestamp)
TIME (current timestamp)
关于时间的一些计算：
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS
计算两个日期之间有多少天：
days (current date) - days (date(’1999-10-22′))
得到去除毫秒的当前时间:
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS
将时间转换成字符串：
char(current date)
char(current time)
char(current date + 12 hours)
将字符串转换成时间：
TIMESTAMP (’2002-10-20-12.00.00.000000′)
TIMESTAMP (’2002-10-20 12:00:00′)
DATE (’2002-10-20′)
DATE (’10/20/2002′)
TIME (’12:00:00′)
TIME (’12.00.00′)
注意：在DB2的命令编辑器中可以输入SQL语句和DB2中的内部命令。要想显示当前时间的话，不能直接输入current time，这个只能在SQL语言中引用，要想显示有下面方式：
1） VALUES （current time）
2） SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1
这个与SQL SERVER2000中不一样，在SQL SERVER2000中可以输入Getdate（）得到时间，既可以显示，也可以在语句SQL中用。
4、所有返回前N条数据的表达式
在SQL SERVER2000中使用TOP N 格式
比如： SELECT TOP 10 CARDNO FROM CARD
在DB2中使用fetch first N rows only 格式
比如：SELECT CARDNO FROM SEALCARD fetch first 10 rows only
5、函数使用
查看系统函数： SELECT * FROM SYSibm.sysfunctions;
比如：ABS（-89）可以作为值输入到SQL中，但是要想在命令编辑器中显示函数的结果的话可以用下列方式：
1）SELECT ABS(-89) FROM SYSIBM.SYSDUMMY1;
2）VALUES ABS(-89);
6、存储过程
在进行DB2存储过程开发时，我们可以利用很多DB2自带的工具，例如开发中心，控制中心等。但有时使用脚本能带给开发人员更大的灵活性和更高的工作效率。
在开始开发一个新的或修改一个已存在的存储过程时，我们通常会进行下面一些准备工作：
1. 查看该存储过程会用到的表的表结构、字段类型、相关索引和示例数据。
2. 查看相关的存储过程或用户自定义函数（UDF）的定义。
3. 查找无效的存储过程并生成绑定语句。
4. 如某个表发生了改变，查看依赖于该表的所有视图、存储过程和用户自定义函数（UDF）
虽然上述信息都可以通过DB2提供的开发工具和管理工具获得,但是通过脚本可以更快速的获得所需信息,并且可以重复执行。
使用脚本完成上述任务的关键是理解和使用DB2的系统表。我们先简单回顾一下有关的DB2的系统表和视图:
1. syscat.routines：存储所有的存储过程和用户自定义函数（UDF）信息。其中routinename字段为存储过程或用户自定义函数（UDF）的名称，routinetype字段表示该记录表示的是存储过程（P）还是用户自定义函数（F），lib_id字段为存储过程编译后生成的包序列号，origin字段表示该存储过程或用户自定义函数的出处（Q表示是由SQL组成的，E表示用户定义的且是外部的），valid字段表示该存储过程或用户自定义函数是否有效，如果origin字段不是Q的话，该域为空。
2. syscat.packages：存储所有被绑定的程序包。其中pkgname表示包名称，valid字段表示该包是否合法。
3. syscat.packagedep：存储关于程序包的依赖关系。其中pkgname字段表示程序包名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
4. syscat.routinedep：存储关于程序（routine）的依赖关系。其中routinename字段表示程序名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
5. syscat.viewdep：存储了关于视图的依赖关系。其中viewname字段表示视图名，btype字段表示被依赖对象的类型，bname字段表示被依赖对象的名称。
回顾并了解了上述系统表和视图，我们就可以很容易的创建脚本以完成前面提到的开发存储过程所要做的准备工作。
1． 查看该表结构、字段类型、相关索引和示例数据
虽然我们可以查询sysibm.systables表获得表结构，但有一种更简便的方法来获取表结构，即使用db2look工具。该工具可以生成创建该表和相关的索引的DDL。如我们要获取指定数据库中指定表的结构和前20条数据作为参考，可编写脚本viewtbl.cmd如下，传入参数分别为数据库名和表名。
@echo ------------------ DDL of table %2 and related index(ex) ------------------
@db2look -d %1 -t %2 -e
@echo ------------------ fisrt 20 rows in table %2 ------------------
@db2 select * from %2 fetch first 20 rows only
2．查看已存在的存储过程和用户自定义函数（UDF）的定义，将结果存入文件并自动打开结果文件。
可以从syscat.routines表中做简单查询实现脚本viewrtn.cmd。
@db2 SELECT text FROM SYSCAT.ROUTINES WHERE ROUTINENAME=upper('%1') > %1.sql
@start %1.sql
3．查看所有无效的存储过程并生成绑定语句
删除存储过程引用的表会引起该存储过程无效。无效存储过程可以采用查询syscat.routines和syscat.packages的方法获得：
SELECT
RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname ,
RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AS pkgname
FROM
SYSCAT.routines r
WHERE
r.routinetype = 'P'
AND (
(r.origin = 'Q' AND r.valid != 'Y')
OR EXISTS (
SELECT 1 FROM syscat.packages
WHERE pkgschema = r.routineschema
AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)
AND valid !='Y'
)
)
ORDER BY spname
注意要同时查询syscat.routines和syscat.packages表，因为在程序包无效时syscat.routines中的valid值仍可能为Y。
如果要自动生成重新绑定语句，只需将上述SQL改写并存成invalidSP.cmd：
@echo off
db2 "SELECT rebind package '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' resolve any' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS (SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y') )" >rebindsp.bat
4．查看某个表所依赖的视图、存储过程和用户自定义函数（UDF）
使用上述系统视图，我们很容易编写出脚本：
@echo off
echo --- dependent SPs ---
db2 "select proc.procschema, proc.procname from syscat.routines r, syscat.procedures proc, syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1') and r.specificname=proc.specificname AND pdep.pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)"
echo --- dependent UDF ---
db2 select routineschema, routinename from syscat.routinedep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
echo --- dependent view ---
db2 select viewschema, viewname from syscat.viewdep where bschema = upper('%1') and bname = upper('%2') and btype ='T' order by bname
行业借鉴经验：提高DB2存储过程性能和健壮性的3个最佳实践
最佳实践1：在创建存储过程语句中提供必要的参数
创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供这些参数可以提高执行效率。下面是一些常用的参数:
l 容许SQL（allowed－SQL）
容许SQL（allowed－SQL）子句的值指定了存储过程是否会使用SQL语句，如果使用，其类型如何。它的可能值如下所示：
NO SQL： 表示存储过程不能够执行任何SQL语句。
CONTAINS SQL： 表示存储过程可以执行SQL语句，但不会读取SQL数据，也不会修改SQL数据。
READS SQL DATA： 表示在存储过程中包含不会修改SQL数据的SQL语句。也就是说该储存过程只从数据库中读取数据。
MODIFIES SQL DATA： 表示存储过程可以执行任何SQL语句。即可以对数据库中的数据进行增加、删除和修改。
如果没有明确声明allowed-SQL，其默认值是MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中NO SQL效率最好，MODIFIES SQL DATA最差。如果存储过程只是读取数据，但是因为没有声明allowed-SQL类型，它会被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其allowed-SQL类型。
l 返回结果集个数（DYNAMIC RESULT SETS n）
存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：
在CREATE PROCEDURE 语句的DYNAMIC RESULT SETS子句中声明存储过程将要返回的结果集的数量。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。
使用WITH RETURN子句，在存储过程体中声明游标。
为结果集打开游标。当存储过程返回的时候，保持游标打开。
在创建存储过程时指定返回结果集的个数可以帮助程序员验证存储过程是否返回了所期待数量的结果集，提高了程序的完整性。
最佳实践2：对输入参数进行必要的的检查和预处理
无论使用哪种编程语言，对输入参数的判断都是必须的。正确的参数验证是保证程序良好运行的前提。同样的，在DB2存储过程中对输入参数的验证和处理也是很重要的。正确的验证和预处理操作包括：
如果输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。
根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，NULL与空字符串或0的转换等。
在DB2储存过程开发中，如需要遇到对空(NULL)进行初始化，我们可以使用COALESCE函数。该函数返回第一个非NULL的参数。例如，COALESCE(piName,'')，如果变量piName为NULL，那么函数会返回''，否则就会返回piName本身的值。因此，可以用下面的代码检查piName是否非NULL并且非空字符串：
SET poGenStatus = 0;
SET piName =RTRIM(COALESCE(piName, ''));
IF (piName ='')
THEN
SET poGenStatus = 34100;
RETURN poGenStatus;
ENDIF;
同理，使用COALESCE可以对任何类型的输入参数进行初始化或验证。下面是对参数初始化规则的一个总结：
1. 输入参数为字符类型，且允许为NULL的，如希望缺省值为空字符串，可以使用COALESCE(inputParameter, '')把NULL转换成空字符串;
2. 输入类型为整型，且允许为NULL的，如希望缺省值为0，可以使用COALESCE(inputParameter,0)，把NULL转换成0；
3. 输入参数为字符类型，且不允许是NULL或空字符串的，可以使用RTRIM(COALESCE(inputParameter, ''))把NULL转换成空字符串，然后验证函数返回值是否为空字符串；
4. 输入类型为整型，且不允许是NULL的，不需要使用COALESCE函数，直接使用IS NULL进行验证。
最佳实践3：异常（condition）处理
在存储过程执行的过程中，经常因为数据或者其他问题产生异常（condition）。根据业务逻辑，存储过程应该对异常进行相应处理或直接返回给调用者。此处暂且将condition译为异常以方便理解。实际上有些异常（condition）并非是由于错误引起的。
当存储过程中的语句返回的SQLSTATE
