Informix 常用函数

一、内部函数
　 1、内部合计函数
　　　 1）COUNT（*）　　　　　　　　　　返回行数
　　　 2）COUNT（DISTINCT COLNAME）　　 返回指定列中唯一值的个数
　　　 3）SUM（COLNAME/EXPRESSION）　　 返回指定列或表达式的数值和；
　　　 4）SUM（DISTINCT COLNAME）　　　 返回指定列中唯一值的和
　　　 5）AVG（COLNAME/EXPRESSION）　　 返回指定列或表达式中的数值平均值
　　　 6）AVG（DISTINCT COLNAME）　　　 返回指定列中唯一值的平均值
　　　 7）MIN（COLNAME/EXPRESSION）　　 返回指定列或表达式中的数值最小值
　　　 8）MAX（COLNAME/EXPRESSION）　　 返回指定列或表达式中的数值最大值
　 2、日期与时间函数
　　　 1）DAY（DATE/DATETIME EXPRESSION）　　　返回指定表达式中的当月几号
　　　 2）MONTH（DATE/DATETIME EXPRESSION）　　返回指定表达式中的月份
　　　 3）YEAR（DATE/DATETIME EXPRESSION）　　 返回指定表达式中的年份
　　　 4）WEEKDAY（DATE/DATETIME EXPRESSION）　返回指定表达式中的当周星期几
　　　 5）DATE（NOT DATE EXPRESSION）　　　　　返回指定表达式代表的日期值
　　　 6）TODAY　　　　　　　　　　　　　　　　返回当前日期的日期值
　　　 7）CURRENT[first to last]　　　　　　　 返回当前日期的日期时间值
　　　 8）COLNAME/EXPRESSION UNITS PRECISION　 返回指定精度的指定单位数
　　　 9）MDY（MONTH，DAY，YEAR）　　　　　　　返回标识指定年、月、日的日期值
　　　 10）DATETIME（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回表达式代表的日期时间值
　　　 11）INTERVAL（DATE/DATETIME EXPRESSION）FIRST TO LAST 返回表达式代表的时间间隔值
　　　 12）EXTEND（DATE/DATETIME EXPRESSION，[first to last]）返回经过调整的日期或日期时间

值

　　　 例子1、和UNITS合用，指定日期或时间单位(year,month,day,hour,minute,seond,fraction)：
　　　　　　　let tmp_date = today + 3 UNITS day
　　　 例子2、let tmp_date = MDY(10,30,2002)　　-- 2002-10-30
　　　 例子3、let tmp_date = today + interval(7) day to day　--当前时间加上7天；
　　　　　　　注：该功能与1相似；
　　　 例子4、EXTEND转换日期或日期时间值
　　　　　　　let tmp_inthour = extend(datetime1,hour to hour)

　 3、代数函数
　　　1）ABS(COLNAME/EXPRESSION)：　　　　　　 取绝对值
　　　2）MOD（COLNAME/EXPRESSION，DIVISOR）　　返回除以除数后的模（余数）
　　　3）POW（COLNAME/EXPRESSION，EXPONENT）　 返回一个值的指数冥
　　　　 例子：let tmp_float = pow(2,3)　--8.00000000

　　　4）ROOT（COLNAME/EXPRESSION，[index]）　 返回指定列或表达式的根值

　　　5）SQRT（COLNAME/EXPRESSION）　　　　　　返回指定列或表达式的平方根值

　　　6）ROUND（COLNAME/EXPRESSION，[factor]） 返回指定列或表达式的圆整化值
　　　7）TRUNC（COLNAME/EXPRESSION，[factor]） 返回指定列或表达式的截尾值
　　　　 说明：上两者中FACTOR指定小数位数，若不指定，则为0；若为负数，则整化到小数点左边；
　　　　 注：ROUND是在指定位上进行4舍5入；TRUNC是在指定位上直接截断；
　　　　　let tmp_float = round(4.555,2) --4.56
　　　　　let tmp_float = trunc(4.555,2) --4.55

　 4、指数与对数函数
　　　1）EXP（COLNAME/EXPRESSION）　　　　返回指定列或表达式的指数值
　　　2）LOGN（COLNAME/EXPRESSION）　　　 返回指定列或表达式的自然对数值
　　　3）LOG10（COLNAME/EXPRESSION）　　　返回指定列或表达式的底数位10的对数值

　 5、三角函数
　　　1）COS（RADIAN EXPRESSION）　　　　　返回指定弧度表达式的余弦值
　　　2）SIN（RADIAN EXPRESSION）　　　　　正弦
　　　3）TAN（RADIAN EXPRESSION）　　　　　正切
　　　4）ACOS（RADIAN EXPRESSION）　　　　 反余弦
　　　5）ASIN（RADIAN EXPRESSION）　　　　 反正弦
　　　6）ATAN（RADIAN EXPRESSION）　　　　 反正切
　　　7）ATAN2（X，Y）　　　　　　　　　　 返回坐标（X，Y）的极坐标角度组件

　 6、统计函数
　　　1）RANGE（COLNAME）　　　 返回指定列的最大值与最小值之差 = MAX（COLNAME）-MIN

（COLNAME）
　　　2）VARIANCE（COLNAME）　　返回指定列的样本方差；
　　　3）STDEV（COLNAME）　　　 返回指定列的标准偏差；

　 7、其他函数
　　　1）USER　　　　　　　　　　　　　　返回当前用户名
　　　2）HEX（COLNAME/EXPRESSION）　　　 返回指定列或表达式的十六进制值
　　　3）LENGTH（COLNAME/EXPRESSION）　　返回指定字符列或表达式的长度
　　　4）TRIM（COLNAME/EXPRESSION）　　　删除指定列或表达式前后的字符
　　　5）COLNAME/EXPRESSION || COLNAME/EXPRESSION　返回并在一起的字符；

二、IDS内部函数
　 1、DBSERVERNAME　　　返回数据库服务器名　let tmp_char=DBSERVERNAME
　 2、SITENAME　　　　　返回数据库服务器名　let tmp_char=SITENAME
　　　说明：两者功能相同；

　 3、DBINFO（‘SPECIAL_KEYWORD')　　　返回只关键字值
　　　例子1：返回数据中每个表的DBSPACE名称
　　　　 select dbinfo('dbspace',partnum),tabname from systables
　　　　　where tabid>99 and tabtype='T'　(OK)
　　　例子2：返回任何表中插入的最后一个SERIAL值
　　　　　select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
　　　例子3：返回最后一个SELECT，INSERT，UPDATE，DELETE或EXECUTE PROCEDURE语句处理的行数；
　　　　　select dbinfo('sqlca.sqlerrd2') from systables where tabid=1;

 

Date Manipulation

One of the more difficult concepts in Informix's handling of date and time values concerns the use of the variables in arithmetic or relational expressions. You can add or subtract DATE and DATETIME variables from each other. You can add or subtract an INTERVAL to a DATE or DATETIME. Table 1 shows the results of different types of operations on DATE and DATETIME values.

Table 1. Operations on DATE and DATETIME Variables

First Operand	Operation	Second Operand	Result
DATE	-	DATETIME	INTERVAL
DATETIME	-	DATE	INTERVAL
DATE	+-	INTERVAL	DATETIME
DATETIME	-	DATETIME	INTERVAL
DATETIME	+-	INTERVAL	DATETIME
INTERVAL	+	DATETIME	DATETIME
INTERVAL	+-	INTERVAL	INTERVAL
DATETIME	-	CURRENT	INTERVAL
CURRENT	-	DATETIME	INTERVAL
INTERVAL	+	CURRENT	DATETIME
CURRENT	+-	INTERVAL	DATETIME
DATETIME	+-	UNITS	DATETIME
INTERVAL	+-	UNITS	INTERVAL
INTERVAL	*/	NUMBER	INTERVAL

Notice that it's always okay to subtract one DATE or DATETIME value from another, as shown here:

CURRENT - "07/01/1950" = INTERVAL (my age)
"12/25/2000" – CURRENT = INTERVAL (how long till Xmas?)

In such a case, the result is always an INTERVAL variable. It would make no sense to add two DATE or DATETIME values together. What could such an addition represent?

UNITS Keyword

When working with INTERVAL values, sometimes it is necessary to specify the precision with which you are dealing. For example, suppose you have the following field defined:

lead_time INTERVAL DAY to DAY

To add 10 days to the lead time you could use a SQL statement like this:

SELECT lead_time + INTERVAL(10) DAY to DAY
FROM orders

You could achieve the same results using the UNITS keyword:

SELECT lead_time + 10 UNITS DAY 
FROM orders

Like most other programming languages, SQL often allows you to achieve the same ends with different statements. Sometimes the choice is one of personal style. Other times, one format fits in better with a structured style of code writing than another format does.

Functions

There are several built-in functions that affect date and time calculations. They can apply to either DATE or DATETIME values, with some exceptions.

TODAY

The TODAY function returns a DATE data value representing the current date. For example, you could execute a SQL function like this:

UPDATE member SET change_date = TODAY
WHER member_number = 12345

CURRENT

The CURRENT function is similar to the TODAY function, except it returns a DATETIME value. Without specific qualifiers, the default is YEAR to FRACTION(3). You can change the precision by using the same YEAR to FRACTION qualifiers as you use for DATETIMES. Thus, this would be legal:

SELECT * from member
WHERE elapsed_time < CURRENT YEAR to DAY

DATE

The DATE function takes as input a non-DATE value such as CHAR, DATETIME, or INTEGER and returns the corresponding DATE value. For example, the following SQL translates a CHARACTER value to a DATE:

SELECT * from member
WHERE enrollment_date > DATE('01/01/99')

DAY

This function returns an integer representing the day of the month. Here's an example:

SELECT * from member
WHERE DAY(enrollment_date) > DAY(CURRENT)

MONTH

This performs like the DAY function except it returns an integer between 1 and 12 representing the month:

SELECT * from member
WHERE enrollment_date > MONTH('01/01/99')

WEEKDAY

This returns an integer representing the day of the week, with 0 being Sunday and 6 being Saturday:

SELECT * from member
WHERE WEEKDAY(enrollment_date) > WEEKDAY(CURRENT)

YEAR

This function is like the ones above, only it returns a four-digit integer representing the year.

EXTEND

This function allows you to use different precisions in a DATETIME than you have specified in the declaration of the variable. It uses the same FIRST to LAST syntax as the DATETIME variables. This function is used to adjust the precision of a DATETIME variable to match the precision of an INTERVAL that you are using in a calculation. If the INTERVAL value has fields that are not part of the DATETIME value that you are using in a calculation, use the EXTEND function to adjust the precision of the DATETIME. EXTEND can either increase or decrease the precision of a DATETIME, depending upon the FIRST and LAST values.

Suppose myvariable is declared as DATETIME YEAR to DAY. If you want to add or subtract an INTERVAL defined as MINUTE, you first have to extend the DATETIME as follows:

SELECT EXTEND(myvariable, YEAR to MINUTE) – 
INTERVAL(5) MINUTE to MINUTE
FROM member

The resulting value will be DATETIME YEAR to MINUTE.

MDY

The MDY function converts three-integer values into a DATE format. The first integer is the month and must evaluate to an integer in the range 1–12. The second integer is the day and must evaluate to a number in the range from 1 to however many days are in the particular month (28–31). The third expression is the year and must be a four-digit integer. Thus, the following MDY functions would each be valid:

MDY(7,1,1950)

returns a DATE of "07/01/50"

MDY(MONTH(TODAY), 1, YEAR(TODAY))

returns a DATE equal to the first day of the current month in the current year

Informix has extensive capabilities for manipulating dates and times, which can make for long and complex SQL statements. Using the three time-related data types and the time-related functions and keywords, you can accomplish almost any type of manipulation of time data. Unfortunately, getting there may be cryptic and painful. If you regularly do extensive date and time manipulation, you should understand all of the intricacies of these data structures.

Have fun!

 

载自 http://www.cppblog.com/wmuu/archive/2006/10/30/14381.html