sqltranslate ()
描述：
将SQL语句中的标准函数翻译成指定数据库中的格式。
语法：
sql.sqltranslate(dbtype)
备注：
将标准SQL中的函数翻译成dbtype数据库中的格式。
标准函数是指SPL脚本中SQL语句使用的函数，使用sqltranslate翻译SQL时，程序会根据function.xml配置文件将标准函数翻译为对应的数据库函数。标准函数或数据库类型在function.xml中找不到则直译。
参数dbtype表示数据库类型，默认支持的数据库类型有：ORACLE ,SQLSVR ,DB2 ,MYSQL ,HSQL ,TERADATA ,POSTGRES,DUCKDB,BIGQUERY，默认提供的标准函数可参考下面的标准函数列表，除此之外用户还可以通过修改配置文件自定义数据库类型及函数。
标准函数：
字符串函数
|
标准函数 |
含义 |
|
LOWER(str) |
转成小写。 |
|
UPPER(str) |
转成大写。 |
|
LTRIM(str) |
删除前导空白。 |
|
RTRIM(str) |
删除后缀空白。 |
|
TRIM(str) |
删除两端空白。 |
|
SUBSTR(str,start,len) SUBSTR(str,start) |
子串。 |
|
LEN(str) |
串长。 |
|
INDEXOF(sub,str[,start]) |
子串的位置。 |
|
IPOS(str1,str2[,n]) |
在字符串str1中，从左边第n位开始查找子串str2，返回str2的位置。n缺省为1。大小写不敏感。 |
|
GETSUBS(str1,str2[,n]) |
在字符串str1中，从左边开始查找第n个字符串str2，找到后返回str2位置后面的串。n缺省为1。 |
|
IGETSUBS (str1,str2[,n]) |
在字符串str1中，从左边开始查找第n个字符串str2，找到后返回str2位置后面的串。n缺省为1。大小写不敏感。 |
|
LEFT(str,len) |
str中最左边len个字符。 |
|
RIGHT(str,len) |
str中最右边len个字符。 |
|
CONCAT(str1,str2) |
连接2个字符串。 |
|
CONCAT(s1,s2,…) |
连接多个字符串。 |
|
CONCAT_WS(d,str1,str2,…) |
用分隔符d连接多个字符串。 |
|
REPLACE(str,sub,rplc) |
将str中的sub替换成rplc。 |
|
ISDIGIT(str) |
判定str是否全由数字构成。 |
|
ISALPHA(str) |
判定str是否全由字母构成。 |
数值函数
|
标准函数 |
含义 |
|
ABS(x) |
求绝对值。 |
|
ACOS(x) |
求反余弦。 |
|
ASIN(x) |
求反正弦。 |
|
ATAN(x) |
求反正切。 |
|
ATAN2(x,y) |
求反正切。 |
|
CEIL(x) |
求大于等于x的最小整数。 |
|
CEIL(x,n) |
根据精度n对x向上取整；n缺省为0，n>0 保留 n 位小数，n=0 取整到个位，n<0 按十位、百位、千位等整数位向上取整。 |
|
COS(x) |
求余弦。 |
|
EXP(x) |
求e的x次幂。 |
|
FLOOR(x) |
求小于等于x的最大整数。 |
|
FLOOR(x,n) |
根据精度n对x向下取整；n缺省为0，n>0 保留 n 位小数，n=0 取整到个位，n<0 按十位、百位、千位等整数位向下取整。 |
|
LN(x) |
求自然对数。 |
|
LOG10(x) |
求以10为底的对数。 |
|
MOD(x,m) |
x模m。 |
|
POWER(x,y) |
x的y次幂。 |
|
ROUND(x,n) |
四舍五入。 |
|
SIGN(x) |
求符号。 |
|
SIN(x) |
求正弦。 |
|
SQRT(x) |
平方根。 |
|
TAN(x) |
正切。 |
|
TRUNC(x,n) |
截断。 |
|
RAND(seed) |
随机数。 |
|
PI() |
计算圆周率。 |
时间函数
|
标准函数 |
含义 |
|
YEAR(d) |
取年。 |
|
MONTH(d) |
取月。 |
|
DAY(d) |
取天。 |
|
HOUR(d) |
取小时。 |
|
MINUTE(d) |
取分钟。 |
|
SECOND(d) |
取秒。 |
|
QUARTER(d) |
取季度。 |
|
TODAY() |
今天。 |
|
NOW() |
当前时间。 |
|
ADDYEARS(d,n) |
增加年数。 |
|
ADDMONTHS(d, n) |
增加月数。 |
|
ADDDAYS(d, n) |
增加天数。 |
|
ADDHOURS(d, n) |
增加小时数。 |
|
ADDMINUTES(d, n) |
增加分钟数。 |
|
ADDSECONDS(d, n) |
增加秒数。 |
|
DAYOFYEAR(d) |
当年中第几天。 |
|
DAYOFWEEK(d) |
星期几，1表示星期日。 |
|
WEEKOFYEAR(d) |
当年中第几星期。 |
|
YEARSDIFF(d1,d2) |
计算两个日期相差年数。 |
|
MONTHSDIFF(d1,d2) |
计算两个日期相差月数。 |
|
DAYSDIFF(d1,d2) |
计算两个日期相差天数。 |
|
HOURSDIFF(d1,d2) |
计算两个日期相差小时数。 |
|
MINUTESDIFF(d1,d2) |
计算两个日期相差分钟数。 |
|
SECONDSDIFF(d1,d2) |
计算两个日期相差秒数。 |
|
TRUNCHOUR(d) |
截断到小时。 |
|
TRUNCMINUTE(d) |
截断到分钟。 |
|
TRUNCSECOND(d) |
截断到秒。 |
|
MONTHSTART(d) |
当月首日。 |
|
MONTHEND(d) |
当月末日。 |
|
WEEKSTART(d) |
当周首日，周一为每周第一天。 |
|
WEEKEND(d) |
当周末日，周一为每周第一天。 |
|
QUARTERSTART(d) |
当季首日。 |
|
QUARTEREND(d) |
当季末日。 |
|
YEARSTART(d) |
当年首日。 |
|
YEAREND(d) |
当年末日。 |
|
MONTHDAYS(d) |
当月日数。 |
|
QUARTERDAYS(d) |
当季日数。 |
|
YEARDAYS(d) |
当年日数。 |
|
ITX(s) |
根据时间分量生成时间区间常数，s表示秒。 |
|
ITX (null,d) |
根据时间分量生成时间区间常数，d表示日，第一个参数值固定为null。 |
|
ITX(null,d,m) |
根据时间分量生成时间区间常数，d表示日，m表示月，第一个参数值固定为null。 |
|
ADDX(t,k) |
日期加法。 k可以ITX()返回的时间区间常数。 t是日期/日期时间且 k是数值时，k 表示日。 t / k是其它数据类型则正常做加法。 |
|
SUBX(t1,t2) |
日期减法。 t1是日期/日期时间且 t2是数值时，t2 表示日。 t1 / t2是其它数据类型则正常做减法。 |
|
CMPX(it1,it2) |
日期比较。 it1 / it2 可以是日期/时间/日期时间/时间区间常数/数值。 |
转换函数
|
标准函数 |
含义 |
|
|
ASCII(str) |
串中最左边字符的ASCII码。 |
|
|
CHR(n) |
整数n转换成字符。 |
|
|
INT(x) |
字符串或数值转成整数。 |
|
|
DECIMAL(x,len,scale) |
字符串或数值转成数值。 |
|
|
TIMESTAMP(d[,str]) |
将d根据格式串str转换成日期时间类型。str缺省为yyyy-MM-dd HH:mm:ss。 |
|
|
NUMTOCHAR(d) |
数值转成字符串。 |
|
|
DATETOCHAR(date) |
将日期转化成yyyy-MM-dd HH:mm:ss格式串。 |
|
|
NULLIF(x1,x2) |
若x1=x2返回null，否则返回x1。 |
|
|
DATE(sd[,fd]) |
将sd根据格式串fd转换成日期类型。fd缺省为yyyy-MM-dd 。 |
|
|
DATE(y,m,d) |
将整数y,m,d转换成日期型数据，失败则返回null。 |
|
|
TIME(sdt[,fd]) |
将sdt根据格式串fd转换成时间类型。fd缺省为HH:mm:ss。 |
|
|
TIME(h,m,s) |
将整数h,m,s转换成时间型数据。 |
|
|
CAST(x,y) |
将x转换为数据类型y。 |
|
|
STRING(exp[,format][,loc]) |
将 exp 转为字符串类型，可通过 format 格式化输出、loc 指定语言地区。参数format 、loc 可省略。 |
|
|
CHN(n) |
将数字n转为中文数字写法。 |
|
任意函数
|
标准函数 |
含义 |
|
CASE(when1,then1,…[,else]) |
when=true则返回相应的then，否则返回else。 |
|
COALESCE(x1,…) |
返回第一个非null的参数。 |
|
NULLIF(x1,x2) |
若x1=x2返回null，否则返回x1。 |
参数：
|
sql |
待翻译的SQL语句。 |
|
dbtype |
数据库类型。 |
返回值：
SQL语句
示例：
|
|
A |
|
|
1 |
SELECT ID,WEEKOFYEAR(DATES),CUSTOMER,AREA FROM CLUE |
使用标准函数WEEKOFYEAR(d)的SQL语句，function.xml中的WEEKOFYEAR函数配置如下：
|
|
2 |
=A1.sqltranslate("ORACLE") |
返回结果： SELECT ID,TO_NUMBER(TO_CHAR(DATES,'WW')),CUSTOMER,AREA FROM CLUE |
|
3 |
=A1.sqltranslate("SQLSVR") |
返回结果： SELECT ID,DATEPART(WW,DATES),CUSTOMER,AREA FROM CLUE |
|
4 |
=A1.sqltranslate("DB2") |
返回结果： SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
5 |
=A1.sqltranslate("MYSQL") |
返回结果： SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
6 |
=A1.sqltranslate("HSQL") |
返回结果： SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
7 |
=A1.sqltranslate("TERADATA") |
返回结果： SELECT ID,TD_WEEK_OF_YEAR(DATES),CUSTOMER,AREA FROM CLUE |
|
8 |
=A1.sqltranslate("POSTGRES") |
返回结果： SELECT ID,EXTRACT(WEEK FROM DATES),CUSTOMER,AREA FROM CLUE |
通过配置function.xml文件，可对SQL标准函数进行扩展，实现对数据库函数的修改和增加。该文件位于esproc-bin-xxxx.jar中/com/scudata/dm/sql/路径下（社区版时位于esproc-xxxx.jar中），内容如下：
FUNCTION节点中，name属性表示标准函数名称；paramcount属性表示函数中的参数个数；value表示数据库中对应的函数语法。
INFO节点中，dbtype属性表示数据库类型；value属性表示数据库中对应的函数写法，此处value为空则默认使用FUNCTION节点中的value的值。
增加自定义函数步骤：
1、定义函数名及其参数个数，在<FUNCTION>节点下配置name及paramcount属性值；
2、在<INFO>子节点下设置数据库类型，dbtype对应数据库类型，dbtype值必须使用大写字母，value对应数据库的函数。每个FUNCTION节点下可以有多个INFO子节点。
1、 增数据库类型时，用户只需在function.xml中的FUNCTION节点中增加INFO节点，并在该节点中配置dbtype及value属性，dbtype设置为数据库类型，value设置为该数据库的对应函数即可。
2、 目前仅支持以下数据库类型：
|
ORACLE |
SQLSVR |
SYBASE |
SQLANY |
|
INFMIX |
FOXPRO |
ACCESS |
FOXBAS |
|
DB2 |
MYSQL |
KINGBASE |
DERBY |
|
HSQL |
TERADATA |
POSTGRES |
DATALOGIC |
|
IMPALA |
HIVE |
GREENPLUM |
DBONE |
|
ESPROC |
DAMENG |
ESPROC_SQL |
ESSBASE |
|
DUCKDB |
BIGQUERY |
|
|