PL/SQL学习笔记[3]-常用函数
1、常用数字函数
-- abs(n) : 返回n的绝对值-- ceil(n) : 返回大于等于数字n的最小整数-- floor(n) : 返回小于等于数字n的最大整数-- mod(m,n) : 取m/n的余数-- power(m,n) : 返回m的n次幂-- round(n[,m]) : 执行四舍五入运算；省略m，则四舍五入到整数位；m为负，四舍五入到小数点前m位；m为正，四舍五入到小数点后m位-- trunc(n[,m]) : 截取数字。省略m，数字n去掉小数部分；m为负,数字n截取小数点前m位；m为正，数字n截取小数点后m位SELECT ABS (-12), -- 12 CEIL (12.4), -- 13 FLOOR (12.9), -- 12 MOD (100, 3), -- 1 POWER (2, 3), -- 8 ROUND (12.4), -- 12 ROUND (12.5), -- 13 ROUND (12.09, -1), -- 10 ROUND (12.09, 1), -- 12.1 TRUNC (30.3), -- 30 TRUNC (20.358899, 3), -- 20.358 TRUNC (28.3565, -1) -- 20 FROM DUAL;?2、常用字符函数
-- 常用字符函数-- ascii(char) : char字符的ascii码-- chr(n) : 将ascii码值n转换成字符-- concat(str1,str2) : 连接str1\str2为一个字符串。相当于连接符（||）-- initcap(char) : 将字符串中的每个单词首字母大写，其他字符小写-- instr(char1,char2[,n[,m]]) : 获取子串char2在字符串char1中的位置。n为其实搜索位置，m为子串出现的次数。n为负，则从尾部开始搜索；n\m默认为1-- length(char) : 返回字符串的长度。如果char = null则返回null-- lower(char) : 返回char的小写格式-- upper(char) : 返回char的大写格式-- lpad(char1,n,char2) : 在字符串char1的左端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符-- rpad(char1,n,char2) : 在字符串char1的右端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符-- ltrim(char1[,set]) : 去掉字符串char1左端包含的set中的任意字符。-- rtrim(char1[,set]) : 去掉字符串char1右端包含的set中的任意字符。-- nls_initcap(char,'nls_param') : 同initcap，但这里的char用于指定NCHAR或者NVARCHAR2类型字符串；nls_param的格式为 nls_sort=sort,用于指定特定语言特征-- nls_lower(char,'nls_param') : 同lower,参数同nls_initcap-- nls_upper(char,'nls_param') : 同upper,参数同nls_initcap-- replace(char,search_str[,replacement_str]) : 将字符串char中的子串search_str替换成replacement_str；如果search_str=null，返回char；如果replacement_str=null，则会去掉char中的search_str-- soundex(char) : 返回字符串char的语音表示，使用该函数可比较发印相同的字符串-- substr(char,m[,n]) : 获取char的子字符串。m为字符起始位置，n为子串长度。m为0，从首字符开始;m为负从尾部开始-- trim(char|char From string) : 从字符串的头尾或者两端截断特定字符。-- translate(string,from_str,to_str) 将string按照from_str与to_str的对应关系进行转换SELECT ASCII ('a'), -- 字符的assii码 CHR (68), -- 数字对应的字符 CONCAT ('a', 'cd'), -- 字符串连接 ('a' || 'cd'), -- 字符串连接 INITCAP ('ok abc'), -- 单词首字母大写 INSTR ('abccdcdc', 'c', 1, 2), LENGTH (NULL), -- 字符串的长度 LENGTH ('abc'), LOWER ('ABCD'), -- 小写字符串 UPPER ('abcd'), -- 大写字符串 LPAD ('ac', 6, '$'), -- 在字符串前面填补$字段直到字符串长度达到6 LPAD ('accc', 2, '$'), RPAD ('ac', 4, '*'), -- 在字符串后面填补*字段直到字符串长度达到4 RPAD ('acccc', 4, '*'), LTRIM ('1234', '1'), -- 去掉字符串最左边为'1'的字符 LTRIM ('1234', '23'), RTRIM ('1234', '234'), -- 去掉字符串最右边为'234'的字符 RTRIM ('1234', '23'), NLS_INITCAP (n'ok abc'), -- 单词首字母大写 NLS_LOWER (n'SQL'), -- 单词小写 REPLACE ('abc', 'b', '123'), -- 字符串替换 REPLACE ('abc', 'b'), REPLACE ('', 'b'), SOUNDEX ('lawer'), -- 返回字符串语音表示 SOUNDEX ('lier'), SOUNDEX ('ok'), SUBSTR ('hello', 2, 2), -- 子字符串 substr(str,offset,len) SUBSTR ('hello', 2), SUBSTR ('hello', -2), SUBSTR ('hello', -3, 2), TRANSLATE ('234abcd', '12345abcde', 'ahellojack'), ' adc', TRIM (' adc '), -- 去除字符串两端空格 TRIM ('a' FROM 'aa123ab') -- 去除字符串两端指定字符 FROM DUAL;?3、常用日期时间函数(一)SELECT TO_CHAR (SYSDATE, 'yyyy-MM-dd hh24:mi:ss'), ADD_MONTHS (SYSDATE, 2), -- 当前时间加两个月 CURRENT_DATE, -- 当前日期 CURRENT_TIMESTAMP, -- 当前日期时间 DBTIMEZONE, -- 当前时区 EXTRACT (YEAR FROM SYSDATE) AS year, -- 获取from日期的年份 EXTRACT (MONTH FROM SYSDATE) month, EXTRACT (DAY FROM SYSDATE) day, FROM_TZ (TIMESTAMP '2011-10-12 21:49:30', '5:00'), LAST_DAY (SYSDATE), -- 当前日期所在月份的最后一天 MONTHS_BETWEEN (SYSDATE + 1, SYSDATE) AS 相差月数, NEW_TIME (SYSDATE, 'bst', 'est'), -- bst的时区对应est时区的时间 NEXT_DAY (SYSDATE, '星期二') -- 指定日期后的第一个工作日 FROM DUAL;?4、常用日期时间函数(二)
SELECT SESSIONTIMEZONE, -- 返回当前会话所在时区 -- numtodsinterval(n,char_expr) 将数字 n 转换成相应的 char_expr 日期格式 NUMTODSINTERVAL (60, 'SECOND'), -- 60秒 NUMTODSINTERVAL (60, 'MINUTE'), -- 60分钟 NUMTODSINTERVAL (60, 'HOUR'), -- 60小时 NUMTODSINTERVAL (60, 'DAY'), -- 60天 NUMTOYMINTERVAL (60, 'MONTH'), -- 60月 NUMTOYMINTERVAL (60, 'YEAR'), -- 60年 ROUND (SYSDATE, 'month'), -- 四舍五入到月 SYSDATE, -- 系统日期时间 SYSTIMESTAMP, -- 系统日期时间及时区 SYS_EXTRACT_UTC (SYSTIMESTAMP), --特定时区时间的格林威治时间 TO_TIMESTAMP ('2012-09', 'yyyy-mm'), -- 将符合特定格式的字符串转换成timestamp类型 TO_TIMESTAMP_TZ ('2012-09', 'yyyy-mm'), TO_YMINTERVAL ('11-11'), -- 将yyyy-mm/yy-mm格式的字符串转换成时间格式注意 mm 的取值范围在0-11 -- 日期截断函数 TRUNC (SYSDATE, 'MONTH'), -- 取到月 TRUNC (SYSDATE, 'YEAR'), -- 取到年 TRUNC (SYSDATE, 'mm') FROM DUAL;?5、转换函数set serveroutput on;DECLARE v_cast VARCHAR2 (20);BEGIN v_cast := CAST (SYSDATE AS varchar2); -- 日期格式转换成字符串格式 DBMS_OUTPUT.put_line (v_cast);END;SELECT ASCIISTR ('中国'), -- 返回参数的数据库字符串的ascii字符串 BIN_TO_NUM (1, 0), -- 返回二进制10表示的十进制数 CAST ('123' AS number), -- 将字符串123转成number类型输出 CONVERT ('abc', 'us7ascii', 'we8iso8859p1'), -- 将字符串有编码us7ascii转成we8iso8859p1编码 TO_CHAR (n'中国'), TO_NCHAR ('中国'), TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), TO_CHAR (100, 'L99G999D99MI'), -- 将数字100转换成人民币式字符串 TO_CLOB ('abc'), -- 将字符串转化成clob类型 TO_DATE ('2000-12-23', 'yyyy-mm-dd'), -- 将字符日期格式转化成日期格式 TO_NUMBER ('￥100', 'L99999D99') FROM DUAL;?6、其他单行函数SELECT id, money, DECODE (id, 1, money * 10, 2, money * 20, money) FROM customer;-- coalesce(expr1[,expr2][,expr3]..) : 返回第一个不为null的表达式结果SELECT COALESCE (NULL, NULL, 'ab') FROM DUAL;-- dump(expr,return_frm) : 返回表达式所对应的数据类型代码、长度及内部表示格式SELECT DUMP ('abc', '1016') FROM DUAL;-- empty_blob() : 初始化blob变量或字段-- empty_clob() : 初始化clob变量或字段-- 更新clob字段content为 empty_clob() 注意content = empty_clob() 与 content = null是不同的UPDATE yt_uniform_information SET content = EMPTY_CLOB () WHERE key_id = 20;?-- greatest(expr1[,expr2]...) : 返回表达式中值最大的一个-- least(expr1[,expr2]...) : 返回表达式中值最小的一个-- nls_charset_id(text) : 返回字符集的id号-- nls_charset_name(number) : 返回特定ID号所对应的字符集名-- nls_charset_decl_len(byte_count,charset[id]) : 返回字节数在特定字符集中占用的字符个数-- nullif(expr1,expr2) : expr1 = expr2 返回 null ; 否则返回expr1。可用在字段上-- nvl(expr1,expr2) : expr1 = null 返回 expr2 否则返回expr1-- nvl2(expr1,expr2,expr3) : expr1 != null 返回 expr2; expr1 = null 返回 expr3;参数expr1为任意数据类型，而expr2\expr3为long外之外-- sys_context('context','attribute') : 返回上下文特定属性值。context为应用上下文名 attribute为指定属性名-- uid : 当前会话对应的用户ID号-- user : 当前会话对应数据库用户名-- userenv(parameter) : 返回当前上下文的属性信息。paramer = isdba|language|terminal|client_info-- vsize(expr) : oracle内部存储expr的实际字节数.只能在SQL语句中使用SELECT GREATEST (1, 2, 4, 4, 10), LEAST (1, 2, 4, 4, 10), NULLIF ('abc', 'abc'), NVL (100, 23), SYS_CONTEXT ('userenv', 'os_user') "OS用户", SYS_CONTEXT ('userenv', 'session_user') "数据库用户", UID, USER, USERENV ('isdba'), -- 是否具有DBA权限 USERENV ('language'), -- 当前会话语言地区和字符集 USERENV ('terminal'), -- 当期会话所在终端的OS标识符 USERENV ('client_info'), -- 返回有包dbms_application_info所存储的用户会话信息（最长64字节） VSIZE ('ad') FROM DUAL;?-- sys_dburigen(colname) ：根据列或者属性生成类型为DBUriType的URL-- sys_xmlgen(expr[,fmt]) : 根据数据库表的行和列生成一个XMLType实例-- sys_xmlagg(expr[,fmt]) : 汇总所有xml文档，并生成一个xml文档(用于可分组的数据列中(一对多))-- xmlelement(identifier[,xml_attribute_clause][,value_expr] : 返回XMLType实例.identifier必选，指定元素名，xml_attribute_clause可选，指定元素属性子句，value_expr可选，指定元素值-- xmlcolattval(value_expr1[,value_expr2]...) : 生成XML块，并增加column做为属性名-- xmlconcat(XMLType_instance1[,XMLType_instance2]...) : 连接多个XMLType实例，并生成一个新的XMLType实例-- xmlforest(value_expr1[,value_expr2]...) : 返回XML块-- xmlsequence(XMLType_instance) : 返回XMLType实例中顶级节点一下的 VARRAY 元素SELECT name, SYS_DBURIGEN (name), SYS_XMLGEN (name), XMLELEMENT (id, name, money), -- <id>namemoney</id> XMLCOLATTVAL (name), -- <column name = "NAME">leeyee</column> XMLCOLATTVAL (name, money), -- <column name = "NAME">leeyee</column><column name = "MONEY">12.23</column> XMLELEMENT ("customer", XMLCOLATTVAL (id, name, money)), XMLCONCAT (XMLELEMENT (id, name), XMLELEMENT (money, money)), XMLFOREST (name, money), XMLSEQUENCE ( XMLELEMENT ("customer", XMLCOLATTVAL (id, name, money)) ) FROM customer a;?-- sys_xmlagg(expr[,fmt]) : 汇总所有xml文档，并生成一个xml文档-- xmlagg(XMLType_instance[ORDER BY sor_list]) : 汇总多个XML块，生成XML文档。SELECT SYS_XMLAGG (SYS_XMLGEN (name)), XMLAGG (XMLELEMENT (name, name)) FROM yt_site_function_menu WHERE function = 3;?
CREATE TABLE xml_tables OF XMLTYPE;INSERT INTO xml_tables VALUES ( xmltype('<address> <province name="北京市"> <city name="北京辖区"> <country name="东城区" /> <country name="西城区" /> <country name="崇文区" /> <country name="宣武区" /> <country name="朝阳区" /> <country name="丰台区" /> <country name="石景山区" /> <country name="海淀区" /> <country name="门头沟区" /> <country name="房山区" /> <country name="通州区" /> <country name="顺义区" /> <country name="昌平区" /> <country name="大兴区" /> <country name="怀柔区" /> <country name="平谷区" /> </city> <city name="北京辖县"> <country name="密云县" /> <country name="延庆县" /> </city> </province> <province name="天津市"> <city name="天津辖区"> <country name="和平区" /> <country name="河东区" /> <country name="河西区" /> <country name="南开区" /> <country name="河北区" /> <country name="红桥区" /> <country name="塘沽区" /> <country name="汉沽区" /> <country name="大港区" /> <country name="东丽区" /> <country name="西青区" /> <country name="津南区" /> <country name="北辰区" /> <country name="武清区" /> <country name="宝坻区" /> </city> <city name="天津辖县"> <country name="宁河县" /> <country name="静海县" /> <country name="蓟县" /> </city> </province></address>') );INSERT INTO xml_tables VALUES ( xmltype('<body> <form onsubmit="javascript:return check(this);"> <button onclick="test();">fd</button> <div id="ad"></div> <input type="submit" value="check" /> </form><div id="test">作为试验，还是让我们来测试一下效果吧。</div></body>') );-- existsnode(xmltype_instance,Xpath_string) : 确定xml节点路径是否存在。存在返回1否则返回0-- extract(xmltype_instance,Xpath_string) : 返回xml节点路径下的内容-- extractvalue(xmltype_instance,Xpath_string) : 返回xml节点路径下的内容值-- updatexml(xmltype_instance,Xpath_string,value_expr) : 更新特定XMLType实例相应节点路径内容SELECT EXISTSNODE (VALUE (p), '/address/province/city[0]'), EXTRACT (VALUE (p), '/address/province[1]/city[1]/country[1]'), EXTRACT (VALUE (p), '/body/div'), EXTRACTVALUE (VALUE (p), '/body/div'), XMLSEQUENCE (EXTRACT (VALUE (p), '/address/province/city/*')) FROM xml_tables p;UPDATE xml_tables p SET p = UPDATEXML (VALUE (p), '/body/div/text()', '作为试验，还是让我们来测试一下效果吧。');?7、分组函数-- avg([ALL|DISTINCT]expr) : 平均值-- count([ALL|DISTINCT]expr) : 总行数-- MAX([ALL|DISTINCT]expr) : 最大值-- MIN([ALL|DISTINCT]expr) : 最小值-- SUM([ALL|DISTINCT]expr) : 求和 SELECT name, COUNT ( * ), SUM (money), AVG (money), MAX (money), MIN (money), MAX (money) KEEP (DENSE_RANK LAST ORDER BY id DESC), MIN (money) KEEP (DENSE_RANK FIRST ORDER BY id DESC), GROUP_ID () FROM customerGROUP BY name;