Ksql语法、使用案例

1     简介

1.1      目的

本文档用于指导开发人员如何使用KSQL编写数据库执行脚本。介绍KSQL的语法特性及功能特性。

同时本文里面存在大量的ksql案例，基本可以直接复制修改，比

https://vip.kingdee.com/article/266494159807695616?productLineId=29 用起来简单一点

1.2      范围

适用于金蝶系的产品（苍穹、EAS...）设计及开发过程。

还有补丁包输出

1.3      读者

苍穹、EAS架构设计师、开发工程师和技术经理。要求读者具备一定的SQL基础。

因为这里的内容是从附件的htm中复制出来的，所以有些字符显示存在问题，比如一些重复多余的l、n ，完整内容通过下载附件学习

2     KSQL概述

KSQL对金蝶产品实现多数据库支持非常重要。所有与金蝶ERP产品支持的数据库通讯的应用程序都通过向数据库服务器发送KSQL语句来实现通讯，与应用程序的用户界面无关。

KSQL是SQL92的子集。它抽取了SQL92的部分语法来满足金蝶ERP产品的最大功能集合，并随着金蝶ERP产品的发展逐渐丰富和完善。

3     KSQL的语法特性

3.1   数据类型

KSQL兼容大多数据数据库产品所使用的数据类型。

3.1.1   类型表

KSQL能够支持的类型如下表所示

3.1.2   优先级

当两个不同数据类型的表达式用运算符组合后，数据类型的优先顺序规则确定哪种数据类型要向另一种转换。优先顺序低的数据类型向优先顺序高的数据类型转换。如果此转换不是目标数据库所支持的固有转换，则返回错误。当两个操作数表达式有相同的数据类型时，运算的结果就为那种数据类型。

下面是KSQL数据类型的优先顺序：

CHAR

VARCHAR

NCHAR

NVARCHAR

BINARY

VARBINARY

BLOB

CLOB

NCLOB

DECIMAL

INT

BINARY

VARBINARY

BLOB

CLOB

NCLOB

DECIMAL

INT INTEGER

DATETIME(最高)

3.2   运算符

KSQL中支持多种运算符，方便进行查询及更新等操作。运算符分两种：算术运算符和比较（逻辑）运算符。算术运行符用来对一个或者两个数值常量和数值型字段进行操作得出新的数值结果；比较运算符主要用于在WERE等条件子句中进行选择判断。这些运算符包括以下几种

3.2.1   算术运算符

 ＋（加）

两个数相加。KSQL不允许使用‘＋’进行字符串和日期的运算。

语法

expression + expression

参数

expression 是数值类型中任何数据类型的任何有效KSQL表达式。

结果类型：返回优先级较高的参数的数据类型

示例

SELECT 1 + ABS (2.0) FROM sample

  ＋（正）

一元运算符，返回数字表达式的正值（一元运算符）。

语法

(+ numeric_expression)

参数

numeric_expression： 是数值类型中任何数据类型的任何有效KSQL表达式。

结果类型

返回numeric_expression的类型。

示例

    SELECT (+ 1) FROM sample

  －（减）

两个数相减。KSQL不允许使用‘-’进行字符串和日期的运算。

语法

expression – expression

参数

expression

    是数值类型中任何数据类型的任何有效KSQL表达式。

结果类型

返回优先级较高的参数的数据类型

示例

    SELECT ABS (2.0) – 1 FROM sample

  －（负）

一元运算符，返回数字表达式的负值（一元运算符）。

语法

（－numeric_expression）

参数

numeric_expression：是数值类型中任何数据类型的任何有效KSQL表达式。

结果类型

    返回numeric_expression的类型。

  *（乘）

两个表达式相乘（算术乘法运算）。

语法

expression * expression

参数

Expression：是数值类型中任何数据类型的任何有效KSQL表达式。

结果类型

    返回优先级较高的参数的数据类型

示例

    SELECT price * 1.5 AS NewPrice FROM titles

  /（除）

用一个数除以另一个数（算术除法运算符）。

语法

dividend / divisor

参数

Dividend：是被除的数字表达式，dividend可以是数值类型中任何数据类型的有效表达式。

Divisor：除数的数字表达式。divisor可以是数值类型中任何数据类型的有效表达式。

结果类型

返回优先级较高的参数的数据类型。如果用一个整型的 divisor 去除整型的 dividend，其结果是一个整数，小数部分被截断。

注释

    由 / 运算符返回的实际值是用第一个表达式除以第二个表达式所得的商。

示例

SELECT ((ytd_sales * price) * royalty)/100 AS 'Royalty Amout' FROM titles

WHERE type = 'business' ORDER BY title_id

3.2.2   比较运算符

 =（等于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果两个操作数相等，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数决定。转换取决于数据类型优先级。

语法

expression = expression

参数

expression：可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

  >（大于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果左边操作数的值大于右边的操作数，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数设置决定。

语法

expression > expression

参数

expression：可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

 <（小于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果左边操作数的值小于右边的操作数，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数设置决定。

语法

expression < expression

参数

    expression： 可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

 >=（大于或等于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果左边操作数的值大于或等于右边的操作数，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数设置决定。

语法

expression > = expression

参数

expression：可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

  <=（小于或等于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果左边操作数的值小于或等于右边的操作数，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数设置决定。

语法

expression = < expression

参数

expression；可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

  <>（不等于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果左边操作数的值不等于右边的操作数，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL, 比较结果由目标数据库的环境参数设置决定。

语法

expression < > expression

参数

expression：可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

  !=（不等于）

比较两个表达式（比较运算符）。当比较非空表达式时，如果左边操作数的值不等于右边的操作数，则结果为 TRUE；否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数设置决定。

语法

expression != expression

参数

expression：可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。

结果类型

Boolean

3.3   注释

KSQL允许在语句中使用注释。与其它SQL语句一样，KSQL支持两种格式的注释，单行注释和多行注释，并在多行注释的基本上规定了KSQL语法的特殊注释。

3.3.1   单行注释 --

表示用户提供的文本。可以将注释插入单独行中、嵌套（只限--和//）在 KSQL 命令行的末端,或者KSQL语句中。目标数据库不对注释进行评估。--是SQL92标准的注释符号。

语法

-- text_of_comment

参数

text_of_comment：包含注释文本的字符串

注释

将 -- 用于单行或嵌套的注释。用 -- 插入的注释由换行字符分界。注释没有最大长度限制

示例

SELECT * FROM authors ORDER BY au_id ASC -- We don't have to specify ASC because that

--is the default

3.3.2   单行注释 //

表示用户提供的文本。可以将注释插入单独行中、嵌套（只限--和//）在 KSQL 命令行的末端,或者KSQL语句中。目标数据库不对注释进行评估。//是KSQL引用了其他高级语言的注释方法。

语法

// text_of_comment

参数

text_of_comment： 包含注释文本的字符串

注释

将 // 用于单行或嵌套的注释。用 // 插入的注释由换行字符分界。注释没有最大长度限制。

示例

SELECT * FROM authors ORDER BY au_id ASC  // We don't have to specify ASC because that

//is the default.

3.3.3   多行注释

表示用户提供的文本。可以将注释插入单独行中、嵌套在 KSQL 命令行的末端,或者KSQL语句中。目标数据库不对注释进行评估。

语法

/* text_of_comment*/

参数

text_of_comment：包含注释文本的字符串

注释

将/* */用于单行的和嵌套的注释。由/* */插入的注释不受换行分界符的限制。

示例

SELECT * FROM authors ORDER BY au_id ASC  /* We don't have to specify ASC because that is the default.*/

3.3.4   特殊注释

一般来说，注释是为了方便读者分析脚本，标示脚本的用途，而不会对查询有所影响。但在KSQL中有一些特殊的注释，这些注释不是为了方便读者分析，它们影响KSQL最终翻译成目标数据库时的方言SQL语句，称这些注释为特殊注释。

特殊注释是在多行注释的基础上发展的，即它们都是以/* 开头，以 */结束，并且/* 和*/之间的内容是固定的。当前有三种特殊注释：方言注释，内部查询注释和Hint注释。

KSQL的特殊注释可以与其它注释共存。但位于行首的特殊注释只能有一个。

n  方言注释

KSQL中允许直接使用目标数据库的方言SQL来进行查询，此时可以使用目标数据库特有的语法，KSQL不会试图去翻译方言注释，而是直接把该SQL发送到数据库执行。一般有两种情况需要使用方言注释：

1．必须使用数据库特有的语法，以提高SQL的执行效率。

2．KSQL没有等价的语法可以实现某种目标的查询或者更新等。

      方言注释是在方言SQL的句行首加上字符串 /*dialect*/ 来表示的。注意各字符之间并没有空格。

例子

/*dialect*/ SELECT f1 FROM t1 FOR XML RAW /* 这是SQL SERVER的方言SQL，因为ksql本身并不支持FOR XML RAW 的语法 */

n  内部查询注释

内部查询注释用于查询数据库连接的内部信息，包括连接信息，选项设置等。不建议开发人员直接使用内部查询注释，而是通过相应的接口来获取相应的信息。

内部查询注释是在KSQL行首加上字符串 /*ksql_internal*/ 来表示的，注意字符之间没有空格。

例子

/*ksql_internal*/SELECT DBTYPE FROM KSQL_ENV -- 查询数据库类型

n  Hint注释

Hint注释类似于ORACLE的Hints语法，它用于对数据库的执行计划进行干涉。目前该注释仅对ORACLE数据库有效。

Hint注释有以下约束

l  以 "/*+" 开始, 并以"*/" 结束, 注意"/*+"中间不能有空格.

l  "/*+" 与 "*/" 之间可以有多个Hints, Hints之间用空格分开, 如 /*+ NO_EXPAND USE_CONCAT*/

l  部分Hints可以带一个或多个参数,参数必须包含在括号"()"中,并跟在Hint之后.多个参数之间用逗号","分隔, 括号与Hint之间允许有空格. 如 /*+ USE_NL (t1, t2) */

l  KSQL只支持部分Hints, 如果传入错误或不支持的Hints,则会在解析的时候抛出异常;

3.4   通配符

KSQL允许在LIKE/NOT LIKE表达式中使用通配符。

3.4.1   []（通配符 — 需匹配的字符）

匹配指定范围内或者属于方括号所指定的集合中的任意单个字符。

例子

WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen 等。

3.4.2   [^]（通配符字符 — 无需匹配的字符）

匹配不处于指定范围内或者不属于方括号内指定集合中的任意单个字符。

例子

WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。

3.4.3   _（通配符 — 匹配一个字符）

匹配任意单个字符，并且可以被用作前缀或后缀。

例子

SELECT * FROM authors WHERE name LIKE '_ichael' //查询authors表中所有名称（name）以字符串ichael结尾的记录

3.4.4   %（通配符 — 匹配一个或多个字符）

匹配任意单个或多个字符，并且可以被用作前缀或后缀。

例子

SELECT * FROM authors WHERE name LIKE '%mic' //查询authors表中所有名称（name）中包括字符串mic的记录

3.5   标识符

KSQL规定，数据库名，数据库别名，数据表名，数据表别名，字段名，字段别名，视图名，视图别名，索引名，索引别名， 约束名都属于标识符范围。

KSQL包含两种方式的标识符：一般标识符和引号标识符

3.5.1   一般标识符

一般标识符的命名规则为：以英文字母开头，后跟连续的一个或多个英文字母，下划线或数字。

示例

合法的命名：tTable, t, t123, tTable, tTable123, t123, t123等。

不合法的命名：_t, _123, 123table, $table, tTable 123等。

3.5.2   引号标识符

某些情况下需要使用不符合一般标识符命名规则的字符串作为标识符，比如Person.id，是个不合法的标识符，此时就需要将该字符串用双引号包含，以表示这是个标识符。在EAS中经常使用引号标识符作为别名以方便阅读。引号标识符中不能直接包含引号。

引号标识符就是：包含在双引号内的除双引号以外的连续或不连续字符序列。

示例

合法的命名：””, ”123”, ”table      123”等

不合法的命名：””””, ”””, ””tTable””等。

3.6   常量

除了数值常量和普通字符串常量（这两种常量比较简单不再描述）之外，KSQL还支持时间常量和UNICODE字符串常量。

3.6.1   时间常量

时间常量表示一个日期或者时间戳常量，它屏蔽了各数据库时间常量表示的差异，开发人员不需要关心具体数据库上的时间格式。

时间常量用花括号{}包含，它有三种格式，如下表所示：

3.6.2   UNICODE字符串常量

UNICODE字符串常量是在普通常量之前加上字符N来表示。与普通字符串常量不同，UNICODE字符串常量表明该字符串必须以UNICODE的方式被处理。它的其它规则与普通常量一致：必须以单号引包含，如果常量中包含单引号，必须用两个单引号替换一个单引号。

示例

合法的字符串常量: '', ' ', '               ', ' ^ ', 'hello'等。

合法的UNICODE字符串常量: n'', N' ', N'               ', n' ^ ', N'hello'等。

不合法的字符串常量: ''', '''''等。

不合法的字符串常量: N''', n'''''等。

3.7   函数

KSQL支持所有主流数据库共同的函数，并且专门为EAS系统提供了特有的函数支持。我们将这些函数分成五大类。

3.7.1   数学函数

n  ABS(p1)

返回给定数值表达式的绝对正值 

n  ACOS(p1)

返回以弧度表示的角度,其余弦值为给定浮点表达式

n  ASIN(p1)

返回以弧度表示的角度,其正弦值为给定浮点表达式

n  ATAN(p1)

返回以弧度表示的角度,其正切为给定浮点表达式

n  ATN2(p1, p2)

返回以弧度表示的角度,其正切为给定的两个浮点表达式之间

n  CEILING(p1)

返回大于或等于给定数值表达式的最小整数

n  COS(p1)

返回给定表达式中以弧度表示的给定角度的三角余弦

n  EXP(p1)

返回给定数值表达式的指数值

n  FLOOR(p1)

返回小于或等于给定数值表达式的最大整数

n  MOD(p, p2)

返回p1 % p2

n  LOG(p1)

返回给定数值表达式的自然对数

n  POWER(p1, p2)

返回给定数值表达式的指定幂的值

n  ROUND(p1, p2)

返回四舍五入到指定长度的数值表达式

n  ROUND(p1, p2, p3)

返回四舍五入到指定精度的数值表达式

n  SIGN(p1)

返回给定表达式的正(+),负(-)或零(0)号

n  SIN(p1)

返回浮点表达式中以弧度表示的给定角度的三角正弦

n  SQRT(p1)

返回给定表达式的平方根

n  TAN(p1)

返回给定表达式的余切

3.7.2   字符串函数

n  ASCII(p1)

返回字符串表达式最左侧的ASCII代码

n  CHAR(p1)

将int ASCII代码转为字符串

n  CHARINDEX(p1, p2)

返回字符串中指定表达式的起始位置

示例

SELECT CHARINDEX('TE', 'A_TE_FOR_TEST') -- 返回3

n  CHARINDEX(p1, p2, p3)

返回字符串中从指定索引开始后的指定表达式的起始位置

示例

SELECT CHARINDEX('TE', 'A_TE_FOR_TEST', 4) -- 返回10

n  CONCAT(p1, p2)

返回两个字符串表达式连接后的字符串

示例

SELECT CONCAT('PREFIX', 'SUFFIX') -- 返回 PREFIXSUFFIX

n  LEFT(p1, p2)

返回字符串的一部分,从左侧指定位置起开始返回

示例

SELECT LEFT('PREFIX', 2) -- 返回 PR

n  LEN(p1)

返回给定符串表达式的字符数,而非字节数

n  LENGTH(p1)

#SEE LENGTH

n  LCASE(p1)

将大写字符转换为小写后返回字符表达式

n  LOWER(p1)

#SEE LCASE 

n  LTRIM(p1)

删除起始空格后返回字符表达式

n  REPLACE(p1, p2, p3)

在p1中查找p2并将之替换为p3

示例

SELECT REPLACE('A_TEST_TE', 'TE', 'CON') -- 返回 A_CONST_CON

n  RIGHT(p1, p2)

返回字符串的一部分,从右侧指定位置起开始返回

示例

SELECT RIGHT('PREFIX', 2) -- 返回 IX

n  RTRIM(p1)

删除所有尾随空格后返回字符表达式

n  SOUNDEX(p1)

返回四字节字符(SOUNDEX)以评价两个字符的相似性

n  SUBSTRING(p1, p2, p3)

返回表达式中的一部分

示例

SELECT SUBSTRING('A_TEST_TE', 3, 4) -- 返回 TEST

n  TRIM(p1)

删除所有起始和尾随空格后返回字符表达式

n  UCASE(p1)

返回将小写字符数据转为大写后的字符串

n  UPPER(p1)

#SEE UPPER

3.7.3   转换函数

n  CONVERT(p1, p2)

将某种数据类型的表达式显式转换为另一种数据类型

示例

SELECT CONVERT(DATETIME, F1) FROM T1; -- 转为时间类型

SELECT CONVERT(VARCHAR, F1) FROM T1; -- 转为字符类型

n  TO_DECIMAL(p1)

将某种数据类型的表达式显式转换为DECIMAL类型

n  TO_DECIMAL(p1,p2,p3)

将某种数据类型的表达式显式转换为指定精度的DECIMAL类型

示例

SELECT TO_DECIMAL('445.67', 10, 3) -- 转为 DECIMAIL(10, 3)类型

n  DECIMAL

#SEE TO_DECIMAL

n  DEC #SEE TO_DECIMAL

n  TO_BLOB(p1)

将某种数据类型的表达式转换为BLOB类型

n  BLOB(p1)

#SEE TO_BLOB

n  TOCHAR(p1)

将表达式类型转换为字符串类型(VARCHAR)

n  TOCHAR (p1, p2)

将日期表达式转换为指定格式的字符串(VARCHAR)

示例

1. SELECT TO_CHAR(getDate(), 'YYYY-MM-DD HH24:MI:SS')

2. SELECT TO_CHAR(getDate(), 'YYYY-MM-DD')

n  TOCHAR (p1, p2, p3)

 将数字表达式转换为指定格式的字符串(VARCHAR)

示例

1. SELECT TO_CHAR(19.200004, 'NUMBER', '9999D99D') -- 返回结果0019.20

2. SELECT TO_CHAR(19.200004, 'NUMBER', 2) -- 返回结果19.20

n  TO_CHAR

#SEE TOCHAR

n  TO_DATE(p1)

将某种数据类型的表达式显式转换为日期时间

n  TO_NUMBER(p1)

转换为浮点数

n  TO_INT(p1)

转换为整型

n  TO_INTEGER(p1)

#SEE TO_INT

n  TO_NVARCHAR(p1)

n  TONVARCHAR(p1)

3.7.4   日期函数

n  CURDATE

返回当前日期(不带时间)

n  CURTIME

返回当前时间和日期

n  DATEADD(p1, p2)

通过向指定时间添加间隔(以秒为单位),返回新的时间

n  DATEADD (p1, p2, p3)

通过向指定时间添加间隔(指定单位),返回新的时间

参考DATEDIFF(p1,p2,p3)

n  DATETIMEADD(p1, p2)

#SEE DATEADD

n  DATETIMEADD (p1, p2, p3)

#SEE DATEADD

n  DATEDIFF(p1, p2)

返回两个时间的间隔,以秒为单位 

n  DATEDIFF (p1, p2, p3)

返回两个时间的间隔,第一个参数指定比较的基本单位.其取值如下:

(yy, yyyy), (qq, q), (mm, m), (dy, y), (dd, d), (wk, ww), hh, (mi, n), (ss, s), ms

示例

select DATEDIFF(dd, {ts '2005-11-23 18:00:32'}, {ts '2005-11-23 18:00:32'})

n  DAYNAME(p1)

返回表示指定日期的表示日期部分的字符串 

n  DAYOFMONTH(p1)

返回表示指定日期是一个月中的第几天 

n  DAYOFWEEK(p1)

返回表示指定日期是一个星期中的第几天 

n  DAYOFYEAR(p1)

返回表示指定日期是一年中的第几天 

n  GETDATE

返回当前系统时间和日期 

n  HOUR(p1)

返回表示指定日期的小时 

n  MINUTE(p1)

返回表示指定日期的分钟 

n  MONTH(p1)

返回表示指定日期的月份 

n  MONTHNAME(p1)

返回表示指定日期的月份名称 

n  NOW

#SEE GETDATE

n  QUARTER(p1)

返回表示指定日期的季度

n  SECOND(p1)

返回表示指定日期的秒

n  WEEK(p1)

返回表示指定日期位于一年当中的第几周

n  YEAR(p1)

返回表示指定日期的年份

n  MONTHS_BETWEEN(p1, p2)

返回两个指定日期的月份间距

示例

SELECT DATEDIFF(MM, '2007-08-09', '2007-10-12') -- 返回结果 2

SELECT DATEDIFF(MM, '2007-11-09', '2007-10-12') -- 返回结果 -1

n  DAYS_BETWEEN(p1, p2)

返回两个指定日期的日期间距 该函数DB2不支持

n  ADD_MONTHS(p1, p2)

返回在指定日期上加上指定月数后的日期

n  ADD_YEARS(p1, p2)

返回在指定日期上加上指定年数后的日期

n  ADD_DAYS(p1, p2)

返回在指定日期上加上指定天数后的日期

示例

select * from T_CAS_Bill where FBizDate < ADD_MONTHS({2007-2-31}, 1)

n  ADD_HOURS(p1, p2)

返回在指定日期上加上指定小时数后的日期

n  ADD_MINUTES(p1, p2)

返回在指定日期上加上指定分数后的日期

n  ADD_SECONDS(p1, p2)

返回在指定日期上加上指定秒数后的日期

n  DATENAME(p1, p2)

返回代表指定日期的指定日期部分的字符串

示例

SELECT DATENAME(YY, '2007-11-09') -- 返回结果 2007

3.7.5   其它函数

n  ISNULL(p1, p2)

判断一个表达式是否为空.如果为空返回另一个表达式,否则返回自身

n  NEWID 

创建 uniqueidentifier 类型的唯一值

n  NEWBOSID (p1)

n  NULLIF(p1, p2)

3.8   升级脚本

升级脚本是指在对数据库升级时使用的脚本，比如增加字段，修改字段，增加索引，删除约束等等，由于数据库的差异（如DB2不允许在一条JDBC指令中包含分号），KSQL将升级脚本与其它SQL脚本区分对待。KSQL约束声明：升级脚本的写法只能用于升级执行，在程序中执行将不保证它的可靠性。

3.8.1   注意事项

为了让升级脚本能够反复执行，必须在升级脚本中执行判断的逻辑，比如说需要在表T1中增加一个字段F1，则在增加脚本执行之前要先判断表T1中是否存在字段。该逻辑应该写成如下语句

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS

           WHERE KSQL_COL_TABNAME = 'T1' AND KSQL_COL_NAME = 'F1')

ALTER TABLE T1 ADD F1 VARCHAR(50) NULL;

其中KSQL_USERCOLUMNS， KSQL_COL_TABNAME， KSQL_COL_NAME都是KSQL的保留字，它们的含义可以参考4.1.1KSQL系统表。其它的升级脚本，如创建视图，创建表等都要先进行判断，这里不再重复说明。

脚本中的每一个升级逻辑都要以分号结束。

3.8.2   创建表

示例

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES

           WHERE KSQL_TABNAME = 'table_test')

CREATE TABLE table_test(column_test VARCHAR(50));

3.8.3   删除表

示例

IF EXISTS (SELECT 1 FROM KSQL_USERTABLES

           WHERE KSQL_TABNAME = 'table_test')

DROP TABLE table_test;

3.8.4   创建视图

示例

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES

           WHERE KSQL_TABNAME = 'view_test')

CREATE VIEW table_test AS

SELECT * FROM table_test;

3.8.5   删除视图

示例

IF EXISTS (SELECT 1 FROM KSQL_USERTABLES

           WHERE KSQL_TABNAME = 'view_test')

DROP VIEW view_test;

3.8.6   创建索引

示例

IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXS

           WHERE KSQL_INDNAME = 'index_test')

CREATE INDEX index_test ON table_test(column_test);

3.8.7   删除索引

示例

IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXS

           WHERE KSQL_INDNAME = 'index_test')

DROP INDEX table_test.index_test;

3.8.8   创建约束

示例

IF NOT EXISTS (SELECT 1 FROM KSQL_CONSTRAINTS

           WHERE KSQL_CONS_NAME = 'cons_test'

           AND KSQL_CONS_TABNAME = 'table_test')

ALTER TABLE table_test ADD CONSTRAINT cons_test PRIMARY KEY(colomn_test);

3.8.9   删除约束

示例

IF EXISTS (SELECT 1 FROM KSQL_CONSTRAINTS

           WHERE KSQL_CONS_NAME = 'cons_test'

           AND KSQL_CONS_TABNAME = 'table_test')

ALTER TABLE table_test DROP CONSTRAINT cons_test;

3.8.10  新增列

示例：新增列

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS

           WHERE KSQL_COL_NAME = 'column_test'

           AND KSQL_COL_TABNAME = 'table_test')

ALTER TABLE table_test ADD column_test VARCHAR(50);

3.8.11  删除列

示例：删除列

IF EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS

           WHERE KSQL_COL_NAME = 'column_test'

           AND KSQL_COL_TABNAME = 'table_test')

ALTER TABLE table_test DROP COLUMN column_test;

3.8.12  修改列类型

修改列的类型必须遵循一定的规则，新的类型要兼容原有的类型，并且新类型的取值范围不能比原类型的取值范围小。

示例：修改列类型

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS

           WHERE KSQL_COL_NAME = 'column_test'

           AND KSQL_COL_TABNAME = 'table_test')

ALTER TABLE table_test ALTER column_test VARCHAR(50);

3.8.13  修改列的默认值

列默认值的修改包括两个：增加默认值与删除默认值。

示例：增加默认值

IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS

           WHERE KSQL_COL_NAME = 'column_test'

           AND KSQL_COL_TABNAME = 'table_test'

           AND ISNULL(KSQL_COL_DEFAULT,'0') <> '0')

BEGIN

           ALTER TABLE table_test ADD DEFAULT 'def_test' FOR column_test;

           UPDATE table_test SET column_test = 'def_test' WHERE column_test IS NULL;

END

示例：删除默认值

ALTER TABLE table_test DROP DEFAULT FOR column_test;

3.8.14  修改列的非空约束

列非空约束的修改包括两个：增加非空约束与删除非空约束。

示例：增加非空约束

ALTER TABLE table_test ALTER COLUMN column_test VARCHAR(50) NOT NULL;

示例：删除非空约束

ALTER TABLE table_test ALTER COLUMN column_test VARCHAR(50) NULL;

3.9   约束及限制

数据类型转换

3.9.1   标识符长度

各数据库中对标识符最大长度的定义不相同，这些标识符包括表名称，字段名称，索引名称等。为了尽量使KSQL能够无差异地运行在各种支持的数据库上，KSQL系统规定了各种标识符的最大长度，这些长度是各主流数据库定义值的最小值。

3.10     方言指导

3.10.1  时机

开发过程中在以下两种情况需要使用方法

n  KSQL不支持的语句

      KSQL已经支持大部分主流程数据库中的各种语法，但对某些特殊的语法仍然无法满足，比如在SYBASE数据库中，不允许在UPDATE和DELETE的FROM子句中使用派生表（derived table），这是由于数据库本身无法支持这种语法导致。

n  KSQL执行的性能低下

KSQL是针对功能来实现支持的，实际中实现同一功能可以有多种不同的SQL语句，这些语句执行的效率各不相同。一般来说KSQL翻译的语句是最优的，但在某些特别复杂的情况下，仍然会存在更优的翻译方式。如果KSQL执行性能很低，这时候就应该使用方言来实现。

3.10.2  方法

使用方言很简单，只需要使用前面提到的方言注释即可，即在数据库相关（非KSQL）的SQL语句之前加上/*dialect*/即可。

4     KSQL的管理特性

4.1.1   KSQL系统表

为了统一对各数据库的系统进行访问，KSQL定义了若干个系统表来映射各数据库上各种系统表。这些系统表的结构及含义如下表所示：

说明：

n   KSQL_COL_NULLABLE列的取值包括：

Y|N (DB2, ORACLE)；

1|0 (SQL SERVER)；

n   KSQL_CONS_TYPE 列的取值包括：

KSQL_CT_F：外键约束；

KSQL_CT_U：唯一约束；

KSQL_CT_C：检查约束；

KSQL_CT_P：主键约束；