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

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 DAYFROM 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 = TODAYWHER 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 memberWHERE 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 memberWHERE enrollment_date > DATE('01/01/99')

DAY

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

select * from memberWHERE 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 memberWHERE 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 memberWHERE 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

155026.html