INIX函数大全.doc

目 录INFORMIX数据类型2表达式用途3表达式4校正表达式4条件表达式4一般性CASE表达式4线形CASE表达式5常量表达式5引号字符串表达式6函数清单6ABS函数9DBINFO函数10CARDINALITY函数14长度函数14DATE函数15DAY函数16三角函数16ACOS函数16ASIN函数16ATAN函数16ATAN2函数17COUNT函数17COUNT（*）函数17COUNT DISTINCT和COUNT UNIQUE函数17AVG函数18USER函数18DBSERVERNAME 和SITENAME函数18TODAY函数18CURRENT函数18DATETIME19COLUMN19Concatonation 运算符19DECODE函数19EXP函数20EXTEND函数20FILETOBLOB函数和FILETOCLOB函数21HEX函数22IFX_ALLOW_NEWLINE函数22IPX_REPLACE_MODULE函数23INITCAP函数23LOCOPY函数23LOGN函数24LOGN10函数25LOTOFILE函数25LOWER函数26LPAD(源字符串，长度，嵌入字符串)26RPAD(源字符串，长度，嵌入字符串)26MAX(数字列|数字表达式)26MIN(数字列|数字表达式)26MDY(expr1，expr2，expr3)26MOD(被除数，除数)27MONTH(date/dtime_expr)27NVL(表达式1，表达式2)27OCTET_LENGTH(变量名|列命)27POW(底数，指数)27RANGE(数字列|数字表达式)27REPLACE(源字符串，匹配字符串，新字符串)27ROOT(被开方数，指数)27ROUND(数字表达式，圆整系数)27SIN(弧度)27SITENAME27STDEV(数字列)27SUBSTR(源字符串，开始位置，长度)28SUM(数字列|数字表达式)28TAN(弧度)28TO_CHAR(DATE|DATETIME,格式字符串)28TO_DATE(字符表达式，格式字符串)28TODAY28TRIM(LEADING|TRAILING|BOTH trim_expr FROM source_expr)28TRUNC(表达式，截去系数)29UPPER(源字符串)29VARIANCE(数字列)29WEEKDAY(date|dtime_expr)29YEAR(date|dtime_expr)29INFORMIX数据类型BYTE存储任何种类二进制数据CHAR(n)存储单个或多个字符序列，包括字母、数字、符号CHARACTER(n)同CHAR相似CHARACTERVRAYING(m,r)同CHAR，m为最大长度,r为最小保留空间DATE日期型DATETIME日期时间型DEC同DECIMAL相似DECIMAL定点数方式存储数值DOUBLE PRECISION双精度型FLOAT(n)浮点型INT同INTEGER相似INTEGER整型（-2147483647至2147483647INTERVAL时间间隔MONEY(p,s)货币型MULTISET(e)存储一个集合NCHAR(n)同CHARNUMERIC(p,s)同DECIMAL，p为总长度，s为小数位数，s为0表示存储的为整数NVARCHAR(m,r)同VARCHARREAL同SMALLFLOATRow, Named行类型SERIAL类似INTEGER，系统自动生成值SERIALS类似INTEGER，系统自动生成值SMALLFLOAT浮点型SMALLINT短整型（-32767to32767）TEXT存储可打印ASCII字符及ctrl-j，ctrl-I,ctrl-lVARCHAR(m,r)同CHAR，最长255）表达式用途表达式类型用途算术运算符号提供对表达式一个项目（一元操作符号）或一个项目（二元操作符号）数学运算的支持接合表达式提供接合两个字符串值的功能校正操作符提供从一种数据类型显示校正到另一种数据类型的功能列表达式提供在数据操作语句中使用部分或全部列值的功能条件表达式提供根据条件测试结果返回数值的功能常量表达式提供在数据操作语句中使用直接数值的功能构造器表达式提供动态生成复杂数据类型的功能函数表达式提供在数据操作语句中使用内部函数或用户定义函数的功能语句本地变量表达式指定如何在SQL语句其他地方使用定义的语句本地变量（SQL）合计函数提供在数据操作语句中使用内部合计函数或用户定义合计函数的功能YEAR(date|dtime_expr)返回DATE或DTETIME型日期中年份部分的整数。（4位数字）表达式校正表达式 CAST（表达式：target_data_type AS target_data_type） 可以用CAST AS关键字或双冒号校正操作符（：）将一种数据类型的表达式校正为另一种数据类型的表达式。下例显示两种不同方法，将x与y的和转换为用户定义类型user_type。两种方法产生相同的结果，都要求存在x+y返回的类型与用户定义类型间转换的显式或隐式校正。Cast(x+y) AS user_type(x+y): user_type 例insert into test(a,b,c,e,f,g) values (2abc,12-2-06:date,3456666,23.2,555,23.56);条件表达式条件表达式返回值取决于条件测试的输出，下列框图显示条件表达式的语法。CASE表达式CASE表达式使用SELECT语句之类的SQL语句可以根据某个条件测试求值为真而返回几个可能值之一。CASE表达式有两种形式， 搜索条件和结果值表达式中可以包含子查询。CASE表达式中可以嵌套另一个CASE表达式。合计表达式中出现CASE表达式时，不能在CASE表达式中使用合计函数。一般性CASE表达式一般性CASE表达式测试WHEN子句中的真值条件，并在发现真值时返回THEN子句中指定的结果。CASEWHEN 条件 THEN exprELSE exprEND例:SELECT cust_name, CASE WHEN number_of_problems=0 THEN 100 WHEN number_of_problems0 AND number_of_problems=4 and number_of_problems20DBINFO函数DBINFO函数实际上是一个返回数据库不同信息的函数集合。通过在DBINFO关键字后定义一个特定选项来调用每个函数。可在SQL语句和UDR中任意地方使用DBINFO选项。下表显示用DBINFOption获得不同的数据库信息。选项列显示了每个DBINFO选项的名字，用途列显示了 选项获得的该类数据库信息。选项用途dbspace-tblspace_num返回对应于Tblspace_num号的dbspace名Sqlca.sqlerrd1返回插入表中的最后一个序列值Sqlca.sqlerrd2返回选择、插入、删除、更新、EXECUTE PROCEDURE语句或EXECUTE FUNCTION语句处理的行数sessionid返回当前对话的对话IDDbhostname返回客户机应用程序所连接数据库服务器的主机名Verison返回客户机应用程序所连接数据库服务器的具体版本Serial8返回插入表中的最后一个SERIAL8值Coserverid返回输入查询的用户所连接合作服务器的合作服务器IDCoserverid table.column currentrow返回指定表的每一行所在合作服务器的合作服务器IDDbspace table.column currentrow返回指定表的每一行所在的dbspace名使用dbspace选项加tblspace号dbspace选项返回对应于tblspace号的包含dbspace名的一个字符串，必须提供一个另外的参数，即tblspace_num或者一个求值为tblspace_num的表达式。下面的例使用dbspace选项。首先，查询systables系统目录表，确定表customer的tblspace_num列。然后执行该函数确定dbspace名。SELECT tabname, partnum FROM systables Where tabname=customer如果语句返回10448892分区号，可以将该值插入第二个变元中来找到包含customer表的dbspace。如下例所示：SELECT DBINFO (dbspace,1048892) FROM systables Where tabname =customer如果想要查询dbspace名的表是分段表，必须查询sysfragments系统目录表查找每个表格分段的tblspace号。然后在一个单独的DBINFO查询中寻找每个tblspace号确定表格分段的所有dbspaces。使用sqlca.sqlerrd1选项sqlca.sqlerrd1选项返回一个整数，提供插入表中的最后一个序列值。为了确保结果有效，将这个选项放在将一行带序列值插入表中的单数INSERT语句后面。下面的例子使用sqlca.sqlerrd1选项：EXEC SQL create table fst_tab (ordernum serial,partnum int);EXEC SQL create table sec_tab (ordernum serial);EXEC SQL insert into fst_tab VALUES (0,1);EXEC SQL insert into fst_tab VALUES (0,4);EXEC SQL insert into fst_tab VALUES (0,6);EXEC SQL insert into sec_tab values (dbinfo(sqlca.sqlerrd1);该例在fst_tab表中插入包含主关键字序列值的行，然后用DBINFO函数插入同一系列值到sec_tab表。DBINFO函数返回插入fst_tab表中最后行的序列值。使用sqlca.sqlerrd2选项:该选项返回单个整数，提供SELECT ，INSERT，DELETE，UPDATE，EXECUTE PROCEDURE和EXECUTE FUNCTION 语句处理的行数。为了确保结果有效，在SELECT，EXECUTE PROCEDURE和EXECUTE FUNCTION语句已经完全执行后使用该选项。为了确保在临时表中使用该选项有效，确定先读取所有行之后再关闭临时表。下例演示SPL例程使用sqlca.sqlerrd2选项确定表中被删除的行数。CREATE FUNCTION del_rows( pnumb int)RETURNING int;DEFINE nrows int;DELETE FROM fst_tab where part_number=pnumb;LET nrows=DBINFO(sqlca.sqlerrd2);RETURN nrows;END FUNCTION使用sessionid选项：DBINFO的该选项返回当前对话的对话ID。当客户机应用程序与一个数据库服务器连接，该服务器和这个客户机开始对话并分配一个ID给这个客户。对话ID是客户和数据库服务器之间唯一标识符。数据库服务器储存对话ID值在一个叫做控制块的共享内存中。对于一个给定的对话控制块也包括用户ID，客户机进程ID，主计算机名和各种状态标识变量。当使用sessionid选项，数据库服务器从对话控制块中返回当前对话的对话ID，并将这个值作为整数值返回。Sysmaster数据库中有些system-monitoring interface(SMI）表格包括一个对话Id列，因此可以用DBINFO函数获得的对话ID从SMI表中提取你自己的对话的信息。在下例中，用户在一个SELECT语句中定义DBINFO函数以得到当前的对话ID的值。用户在systables系统目录表中使用查询并使用WHERE子句将查询结果限制于一行。SELECT DBINFO（sessionid） AS my_sessinoidFROM systablesWHERE tabname = systables在上例中，SELECT语句查询systables系统目录表。然而，你可以通过查询数据库中任何系统目录表或用户表获得当前对话的ID。例如，可用下面的查询获得当前对话的对话ID。SELECT DBINFO（sessionid） AS user_sessionidFROM customerWhere customer_num=101在SPL例程中也可使用sessionid选项。下例SPL函数返回当前的对话ID值到调用的例程或程序：CREATE FUNCTION get_sess()RETURNING INT;RETURN DBINFO(sessionin);END FUNCTION; 使用dbhostname选项该选项得到客户机应用程序所连接的数据库服务器的主机名。该选项得出数据库服务器运行的计算机的实际计算机名称。下例中，用户在SELECT语句中键入dbhostname选项得到DB-ACCESS连接的数据库服务器的主机名。SELECT DBINFO （dbhostname）FROM systablesWHERE tabid=1下表显示查询结果（constant） rd_lab1version选项可用version选项得出客户机应用程序连接的数据库服务器的确切版本号，从信息日志中得出确切的版本号字符串。完整版本字符串的值ononit实用程序-V选项显示的结果相同。可用version选项的specifier参数来 定义想要得到的版本字符串的那部分字符。下表是specifier参数所得出的对应的版本信息。以完全的版本字符串：Server version 9.20.UC1.为例。specifier参数值返回的版本信息 返回值server_type 数据库类型 Dynamic Serverminor当前服务器主要的版本号 9minor 当前服务器次级的版本号 20os 版本信息字符串的操作系统标识符T=windows NTU=运行在32-bit操作系统的32-bit UNIXH=运行在64-bit操作系统的32-bit UNIXF=运行在64-bit操作系统的64-bitUNIXUlevel 当前服务器版本的过渡版本C1full 如oninit V 显示的完全版本信息Dynamic Server Version 9.20.UC1下例显示怎样在SELECT语句中用version选项得出DB-Access客户连接的数据库服务器主要的版本信息：SELECT DBINFO（version,major）FROM systablesWHERE tabid=1下面是查询结果 （constant） 7使用serial8选项serial8选项选项返回一个整数，得出插入表中的最后SERIAL8值。为了确保结果有效，在插入了SERIAL8值的INSERT语句 后面使用该选项。下例使用了serial8选项：EXEC SQL Create table fst_tab (ordernum serial8,partnum int);EXEC SQL cerate table sec_tab (ordernum serial8);EXEC SQL insert into fst_tab VALUES (0,1)EXEC SQL insert into fst_tab VALUES (0,4)EXEC SQL insert into fst_tab VALUES (0,6) EXEC SQL insert into sec_tab Select dbinfo (serial8)From sec_tab where paartnum=6;该例将包含主关键字SERIAL8值的行插入fst_tab表在中，然后用DBINFO函数将同一SERIAL8值插入sec_tab表中。DBINFO函数返回的是插入fst_tab的最后一行的SERIAL8值。最后一行的子查询包含一个WHERE子句，因此返回一个单独的值。*使用conserverid选项不带其他变元使用coserverid选项不带其他变元返回输入查询的用户所连接合作服务器的合作服务器ID。假定用下面的语句创建mytab表。CREATE TABLE mytab (mycol INT)FRAGMENT BY EXPRESSIONMycol 5 in rootdbs.1Mycol 10在ESQL/C中，可以用LENGTH函数返回字符变量的长度。OCTET_LENGTH函数OCTET_LENGTH函数返回字符列中的字符数（而不是字节数），CHARACTER_LENGTH 是CHAR_LENGTH的同义词。DATE函数DATE函数返回对应于所调用非日期表达式的DATE值。可以指定能变为 DATE数据类型的数值表达式。通常指定求值为CHAR、DATETIME或INTEGER值的表达式。下列WEHERE子句指定CHAR值非日期表达式：WHERE order_dateDATE(12/31/97)DATE函数解释CHAR值非日期表达式时，它希望这个表达式符合DBDATE环境变量指定的DATE格式。例如，假设执行下列查询时DBDATE设置为Y2MD/：SELECT DISTINCT DATE（02/01/1998） FROM ship_info则这个SELECT语句产生一个错误，因为DATE函数不能转换这个非日期表达式。DATE函数将日期字符串的第一部分（02）解释为年份，第二部分（01）解释为月份，第三部分（1998）是个4位数，但DATE函数需要2位数（有效日期值在01到31之间）。因此，它无法转换这个非日期表达式。要在DBDATE设置为Y2MD/时让SELECT语句顺利执行，非日期表达式应为98/02/01。对非日期表达式指定正整数值时，DATE函数将其值解释为1899年12月31日之后的天数。如果该值为负数，则DATE函数将其值解释为1899年12月31日之前的天数。下列WHERE子句指定INTEGER非日期表达式值：WHERE order_dateDAY(CURRENT)三角函数三角函数取一个变元，如下列框图所示。COSRadian_exprSINTANASINNumeric_exprACOSATANATAN2Y,x元素用途限制语法Numeric_expr作为ASIN、ACOS或ATAN函数变元的数字表达式表达式应求值为-1到1之间的值表达式Radian_expr求值为弧度值的表达式表达式应求值为数字值表达式X表示直角坐标（x,y）中x的 表达式表达式应求值为数字值表达式y表示直角坐标（x,y）中x的 表达式表达式应求值为数字值表达式弧度表达式公式COS、SIN和TAN函数取弧度（radian_expr）数作为变元。如果要将度数换算为弧度值，用下列公式：# degrees *p/180=# radians如果要将弧度值换算为度数，用下列公式：# radians *180/ p =# degreesCOS函数COS函数返回弧度表达式公式的余弦值。下例返回anglestbl表中度数列的余弦值，表达式中的读数要换算为弧度值：SELECT COS（degrees*180/3.1416）FROM anglestblACOS函数ACOS函数返回数字表达式的反余弦值，下例返回（-0.73）弧度反余弦值：SELECT ACOS（-0.73）FROM anglestblASIN函数ASIN函数返回数字表达式的反正弦值，下例返回（-0.73）弧度反正弦值：SELECT ASIN（-0.73）FROM anglestblATAN函数ATAN函数返回数字表达式的反正切值，下例返回（-0.73）弧度反正切值：SELECT ATAN（-0.73）FROM anglestblATAN2函数ATAN2函数计算坐标（x,y）的极坐标（r, ）角度组件。下例对直角坐标（4，5）比较angles与：WHERE anglesATAN2(4,5) -determines for (4,5)and compares to angles可以用下例所示的表达式确定直角坐标（4，5）的径向坐标长r:SQRT（POW（x，2） + POW（y，2） -determines r for (x,y)可以用下例所示的表达式确定直角坐标（4，5）的径向坐标长r:SQRT(POW(4,2) + POW（5，2） - determines r for (4,5)COUNT函数COUNT函数实际上是一组函数，可以用不同方式计算列值和表达式。要调用COUNT函数，可以在COUNT关键字后面指定特定变元。下面几节将介绍COUNT函数的每种形式。COUNT（*）函数COUNT（*）函数返回符合SELECT语句WHERE子句的行数。下例寻找stock表中多少行的manu_code列取值为HRO：SELECT COUNT（*）FROM stock WHERE manu_code=HRO如果SELECT语句不用WHERE子句，则COUNT（*）函数返回表中的总行数。下例寻找stock表中有多少行：SELECT COUNT(*）FROM stock如果SELECT语句包含GROUP BY子句，则COUNT（*）函数反映每组中的数值个数。下例按名字组合，数据库服务器发现多个同名行时，选择这些行：SELECT fname,COUNT(*) FROM counterGROUP BY fnameHAVING COUNT(*)1如果多行的值为null,则COUNT（*）函数在计算时包括null列，除非WHERE子句显式将其省略。COUNT DISTINCT和COUNT UNIQUE函数COUNT DISTINCT函数返回列或表达式中的不同值个数，如下例所示。如果COUNT DISTINCT函数遇到null,则它将其忽略。SELECT COUNT（DISTINCT item_num） FROM item如果指定列的每个值都为null,则COUNT DISTINCT不是将其忽略，而是对该列返回0。UNIQUE关键字的含义在和COUNT关键字一起使用时，含义与DISTINCT相同。UNIQUE返回列或表达式的非null唯一值个数。下例用COUNT UNIQUE函数，与前面使用COUNT DISTINCT函数的示例是等价的；SELECT COUNT （UNIQUE item_num） FROM itemCOUNT column函数COUNT column函数返回列或表达式中的非null值总个数，如下例所示：SELECT COUNT（item_num） FROM items可以在指定列名之前加上ALL关键字以保证清晰，但无论是否包括ALL关键字，查询结果都是相同的。下例显示如何在COUNT column函数中包括ALL关键字：SELECT COUNT（ALL item_num） FROM items 比较不同COUNT函数可以用COUNT函数的不同形式读取表格的不同类型信息。下表 总结COUNT函数每种形式的含义。COUNT函数说明COUNT（*）返回符合查询条件的行数 如果不指定WHERE子句，则这个函数返回表中的总行数COUNT DISTINCT 或COUNT UNIQUE返回指定列中的唯一非null值个数COUNT （column）或COUNT(ALL column)返回指定列中的非null值总个数可以用一些示例显示COUNT函数不同形式之间的差别。下例对演示数据库中的orders表进行查询。大多数示例对orders表的ship_instruct列进行查询。AVG函数AVG函数返回指定列或表达式中所有值的平均值。只能对数字列采用AVG函数。如果使用DISTINCT关键字，则平均值为指定列或表达式中唯一值的平均值。下列查询寻找平均价格：SELECT AVG（unit_price） FROM stock WHERE stock_num=110除非指定列中所有值均为null，否则忽略null值。如果指定列中所有值均为null，则AVG函数对该列返回null值。USER函数USER 函数返回包含当前用户（即运行进程的人员）登陆名的字符串。下列语句显示如何使用USER函数：INSERT INTO cust_calls VALUES(221,CURRENT,USER,B,Decimal point off,NULL,NULL)SELECT * FROM cust_calls WHERE use