`
LeeYee
  • 浏览: 72145 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

PL/SQL学习笔记[3]-常用函数

    博客分类:
  • SQL
阅读更多

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   customer
GROUP BY   name;
0
2
分享到:
评论

相关推荐

    pl/sql学习笔记

    以下是对PL/SQL的学习笔记的详细解析: 1. **什么是PL/SQL语言** PL/SQL是Oracle数据库为数据库管理员和开发人员设计的一种编程语言。它扩展了SQL的功能,允许编写包含控制结构、变量、过程和函数的程序段,这些...

    PL/SQL学习笔记

    PL/SQL是Oracle公司开发的一种过程化SQL扩展,它是Oracle数据库的重要组成部分,用于在数据库服务器上编写存储过程、函数、触发器、包等可执行代码。PL/SQL可以处理复杂的数据操作和业务逻辑,支持编程结构如循环、...

    PL/SQL学习教程,附笔记

    PL/SQL,全称是Procedural Language/Structured Query Language,是Oracle数据库提供的一种结合了SQL语言和过程式编程的编程环境。它扩展了SQL的功能,使得开发人员能够编写复杂的数据库应用程序,处理事务、实现...

    pl/sql数据库学习笔记

    PL/SQL数据库学习笔记 PL/SQL是一种高级的程序语言,主要用于Oracle数据库管理系统中。下面是PL/SQL数据库学习笔记的知识点总结。 一、基本原则 *瀑布模型:需求分析→设计(概要设计,详细设计:SQL语句,变量...

    PL/SQL学习笔记4

    ### PL/SQL学习笔记4 —— 集合与成员函数 #### 一、PL/SQL 表(索引表) 在PL/SQL中,**索引表**(也称为**PL/SQL表**)是一种非常有用的结构,它类似于数组但具有更多的灵活性。这种表是非持久化的,即它们不会...

    PL/SQl超级笔记

    总的来说,“PL/SQL超级笔记”应该涵盖了从基本语法到高级特性的全面教程,通过学习,新手可以逐步掌握如何使用PL/SQL进行数据库编程,从而更好地管理和操作Oracle数据库。配合"oracle_ppt"中的PPT材料,学习效果会...

    ORACLE PL/SQL从入门到精通

    ORACLE PL/SQL是从入门到精通的专业知识,涵盖了数据库开发与管理的多个方面,包括触发器、过程、函数、软件包、异常处理、游标、循环、分支、变量使用、数据库安装等关键知识点。 触发器是数据库中用来保证数据...

    PL/SQL听课笔记

    ### PL/SQL听课笔记 #### 一、PL/SQL简介 **PL/SQL**(Procedural Language for SQL)是一种专门为Oracle数据库设计的过程化语言扩展。它是在标准SQL基础上增加了一系列高级编程特性,如变量、控制结构、函数、...

    PL/SQL学习笔记7

    在PL/SQL编程中,存储过程和函数是关键的组件,它们允许我们将可重用的代码逻辑存储在数据库中,以便于管理和调用。本文主要探讨了存储过程、函数以及程序包的概念,特点,创建方法,执行方式,权限管理以及参数处理...

    sql,PL/SQl学习笔记

    本文将深入探讨从"SQL,PL/SQL学习笔记"中提取的关键知识点,帮助编程人员更好地理解和运用这两种语言。 首先,我们关注SQL并行查询。通过`ALTER SESSION ENABLE PARALLEL DMl`,我们可以开启会话的并行DML操作,这...

    oracle/SQL和PL/SQL课堂笔记

    PL/SQL部分未在提供的内容中出现,但它是Oracle的扩展,允许编写存储过程、函数、触发器等,增强了SQL的功能。它结合了SQL查询与过程式编程语言,使数据库管理更加灵活和高效。 这些笔记提供了学习SQL和Oracle...

    pl/sql学习小结笔记

    PL/SQL,全称Procedural Language/Structured Query Language,是Oracle数据库的一种扩展语言,用于处理数据库中的数据和实现复杂的业务逻辑。以下是对PL/SQL的基础知识进行的详细阐述: 1. **匿名块与命名块**: ...

    PL/SQL存储过程笔记

    ### PL/SQL 存储过程知识点详解 #### 1. PL/SQL 存储过程概述 PL/SQL(Procedure Language for SQL)是...通过这些基本概念的学习,可以帮助开发者更好地掌握 PL/SQL 的使用技巧,提高数据库应用程序的效率和性能。

    PL/SQL文本文档

    PL/SQL,全称为Procedural Language/Structured Query Language,是Oracle数据库的一种扩展,它将SQL(结构化查询语言)与过程式编程语言相结合,为数据库管理提供了强大的编程能力。以下是对PL/SQL的一些核心知识点...

    oracle笔记 (PL-SQL)

    Oracle笔记(PL-SQL)是关于数据库管理和编程的一个重要主题,主要聚焦于Oracle数据库系统中的结构化查询语言(SQL)的扩展——PL/SQL。PL/SQL是一种过程化语言,结合了SQL的功能,使得开发者能够编写复杂的数据库应用...

    Oracle PL SQL Programming 第五版-带书签目录超清文字版

    Oracle PL/SQL编程是Oracle数据库开发中的核心技能之一,它是一种强大的过程式语言,用于在Oracle环境中编写存储过程、函数、触发器等数据库程序。第五版的《Oracle PL SQL Programming》是一本深入学习PL/SQL的权威...

Global site tag (gtag.js) - Google Analytics