45个非常有用的oracle语句（摘自尚学堂）

2023-08-25 14:46:16

日期/时间 相关查询

  1. 获取当前月份的第一天

    运行这个命令能快速返回当前月份的第一天。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    2
    SELECT TRUNC (SYSDATE, 'MONTH') 
        FROM DUAL;

    扩展：TRUNC函数返回以指定元素格式截去一部分的日期值或数字。

    语法格式
    1）日期：
    TRUNC（date,[fmt]），其中date是必须的，而[fmt]是非必须的，若没有[fmt]，则精确到当前给定日期的年月日，如：SELECT TRUNC(SYSDATE) FROM DUAL;
    结果：2015/8/13
    以下是fmt的参考值：可以根据参考值的不同返回不同的日期形式：
    fmt值参考如下:
    Unit
    Valid format parameters
    Year
    SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y（当年的第一天，即1月1日，精确到年月日，格式如：2015/1/1）
    ISO Year
    IYYY, IY, I(异年，返回该年第一周的星期一的日期，精确度同上，而关于怎么判断哪一周算是新年的首周，请参考：In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday.
    • If January 1 falls on a Friday, Saturday, or Sunday, then the week including January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.
    • If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the week is the first week of the new year, because most of the days in the week belong to the new year.

    )
    Quarter
    Q(表示上一个月的第一天，精确同上)
    Month
    MONTH, MON, MM, RM（当天月份的第一天，精确同上）
    Week
    WW（当前日期所对应的年月日，和无[fmt]的trunc异曲同工）
    IW
    IW（当前周周一所在的日期）
    W
    W（当前周的上一个最近的周六日期）
    Day
    DDD, DD, J（当前日期，精确到天）
    Start day of the week
    DAY, DY, D（当前星期的第一天，周日为一周的开始）
    Hour
    HH, HH12, HH24（当前小时数，如：2015/8/13 10:00:00）
    Minute
    MI（当前分钟数）
     
    2）数字：
    TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
    其具体的语法格式如下
    TRUNC（number[,decimals]）
    其中：
    number 待做截取处理的数值
    decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。
    下面是该函数的使用情况：
    TRUNC（89.985，2）=89.98
    TRUNC（89.985）=89 (即取整)
    TRUNC（89.985，-1）=80
    注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推；如果所设置的参数为负数，且负数的位数大于整数的字节数的话，则返回为0。如：TRUNC(89.985,-3)=0。
     

  2. 获取当前月份的最后一天

    这个查询语句类似于上面那个语句，而且充分照顾到了闰年，所以当二月份有 29 号，那么就会返回 29/2 。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    2
    SELECT TRUNC (LAST_DAY (SYSDATE))
        FROM DUAL;

  3. 获取当前年份的第一天

    每年的第一天都是1月1日，这个查询语句可以使用在存储过程中，需要对当前年份第一天做一些计算的时候。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    SELECT TRUNC (SYSDATE, 'YEAR')  FROM DUAL;

  4. 获取当前年份的最后一天

    类似于上面的查询语句。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 FROM DUAL

  5. 获取当前月份的天数

    这个语句非常有用，可以计算出当前月份的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    2
    SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) 
      FROM DUAL;

  6. 获取当前月份剩下的天数

    下面的语句用来计算当前月份剩下的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    2
    3
    4
    SELECT SYSDATE,
           LAST_DAY (SYSDATE) "Last",
           LAST_DAY (SYSDATE) - SYSDATE "Days left"
      FROM DUAL;

  7. 获取两个日期之间的天数

    使用这个语句来获取两个不同日期自检的天数。

    1
    2
    3
    4
    5
    6
    7
    SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
              num_of_days
      FROM DUAL;
     
    OR
     
    SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

    如果你需要查询一些特定日期的天数，可以使用第二个查询语句。这个例子是计算员工入职的天数。

  8. 显示当前年份截止到上个月每个月份开始和结束的日期

    这个是个很聪明的查询语句，用来显示当前年份每个月的开始和结束的日期，你可以使用这个进行一些类型的计算。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
           TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
      FROM XMLTABLE (
              'for $i in 0 to xs:int(D) return $i'
              PASSING XMLELEMENT (
                         d,
                         FLOOR (
                            MONTHS_BETWEEN (
                               ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
                               SYSDATE)))
              COLUMNS i INTEGER PATH '.');

  9. 获取直到目前为止今天过去的秒数（从 00：00 开始算）

    1
    2
    SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
      FROM DUAL;

  10. 获取今天剩下的秒数（直到 23：59：59 结束）

    1
    2
    SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
      FROM DUAL;

    数据字典查询

  11. 检查在当前数据库模式下是否存在指定的表

    这是一个简单的查询语句，用来检查当前数据库是否有你想要创建的表，允许你重新运行创建表脚本，这个也可以检查当前用户是否已经创建了指定的表（根据这个查询语句在什么环境下运行来查询）。

    1
    2
    3
    SELECT table_name
      FROM user_tables
     WHERE table_name = 'TABLE_NAME';

  12. 检查在当前表中是否存在指定的列

    这是个简单的查询语句来检查表里是否有指定的列，在你尝试使用 ALTER TABLE 来添加新的列新到表中的时候非常有用，它会提示你是否已经存在这个列。

    1
    2
    3
    SELECT column_name AS FOUND
      FROM user_tab_cols
     WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

  13. 显示表结构

    这 个查询语句会显示任何表的 DDL 状态信息。请注意我们已经将‘TABLE’作为第一个信息提交了。这个查询语句也可以用来获取任何数据库对象的 DDL 状态信息。举例说明，只需要把第一个参数替换成‘VIEW’，第二个修改成视图的名字，就可以查询视图的 DDL 信息了。

    1
    SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

  14. 获取当前模式

    这是另一个可以获得当前模式的名字的查询语句。

    1
    SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

  15. 修改当前模式

    这是另一个可以修改当前模式的查询语句，当你希望你的脚本可以在指定的用户下运行的时候非常有用，而且这是非常安全的一个方式。

    1
    ALTER SESSION SET CURRENT_SCHEMA = new_schema;

    数据库管理查询

  16. 数据库版本信息

    返回 Oracle 数据库版本

    1
    SELECT * FROM v$version;

  17. 数据库默认信息

    返回一些系统默认的信息

    1
    2
    3
    4
    5
    SELECT username,
           profile,
           default_tablespace,
           temporary_tablespace
      FROM dba_users;

  18. 数据库字符设置信息

    显示数据库的字符设置信息

    1
    SELECT * FROM nls_database_parameters;

  19. 获取 Oracle 版本

    1
    2
    3
    SELECT VALUE
      FROM v$system_parameter
     WHERE name = 'compatible';

  20. 存储区分大小写的数据，但是索引不区分大小写

    某些时候你可能想在数据库中查询一些独立的数据，可能会用 UPPER(..) = UPPER(..) 来进行不区分大小写的查询，所以就想让索引不区分大小写，不占用那么多的空间，这个语句恰好能解决你的需求 。

    1
    2
    3
    4
    5
    6
    CREATE TABLE tab (col1 VARCHAR2 (10));
     
    CREATE INDEX idx1
       ON tab (UPPER (col1));
     
    ANALYZE TABLE a COMPUTE STATISTICS;

  21. 调整没有添加数据文件的表空间

    另一个 DDL 查询来调整表空间大小

    1
    ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

  22. 检查表空间的自动扩展开关

    在给定的表空间中查询是否打开了自动扩展开关

    1
    2
    3
    4
    5
    SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;
     
    (OR)
     
    SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

  23. 在表空间添加数据文件

    在表空间中添加数据文件

    1
    2
    ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'
        SIZE 1000M AUTOEXTEND OFF;

  24. 增加数据文件的大小

    给指定的表空间增加大小

    1
    ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;

  25. 查询数据库的实际大小

    给出以 GB 为单位的数据库的实际大小

    1
    SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

  26. 查询数据库中数据占用的大小或者是数据库使用细节

    给出在数据库中数据占据的空间大小

    1
    SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

  27. 查询模式或者用户的大小

    以 MB 为单位给出用户的空间大小

    1
    2
    3
    SELECT SUM (bytes / 1024 / 1024) "size"
      FROM dba_segments
     WHERE owner = '&owner';

  28. 查询数据库中每个用户最后使用的 SQL 查询

    此查询语句会显示当前数据库中每个用户最后使用的 SQL 语句。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
             s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
             s.sid || '/' || s.serial# sid,
             s.status "Status",
             p.spid,
             sql_text sqltext
        FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
       WHERE     t.address = s.sql_address
             AND p.addr = s.paddr(+)
             AND t.hash_value = s.sql_hash_value
    ORDER BY s.sid, t.piece;

    性能相关查询

  29. 查询用户 CPU 的使用率

    这个语句是用来显示每个用户的 CPU 使用率，有助于用户理解数据库负载情况

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
        FROM v$session ss, v$sesstat se, v$statname sn
       WHERE     se.STATISTIC# = sn.STATISTIC#
             AND NAME LIKE '%CPU used by this session%'
             AND se.SID = ss.SID
             AND ss.status = 'ACTIVE'
             AND ss.username IS NOT NULL
    ORDER BY VALUE DESC;

  30. 查询数据库长查询进展情况

    显示运行中的长查询的进展情况

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT a.sid,
             a.serial#,
             b.username,
             opname OPERATION,
             target OBJECT,
             TRUNC (elapsed_seconds, 5) "ET (s)",
             TO_CHAR (start_time, 'HH24:MI:SS') start_time,
             ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
        FROM v$session_longops a, v$session b
       WHERE     a.sid = b.sid
             AND b.username NOT IN ('SYS', 'SYSTEM')
             AND totalwork > 0
    ORDER BY elapsed_seconds;

  31. 获取当前会话 ID，进程 ID，客户端 ID 等

    这个专门提供给想使用进程 ID 和 会话 ID 做些 voodoo magic 的用户。

    1
    2
    3
    4
    5
    6
    SELECT b.sid,
           b.serial#,
           a.spid processid,
           b.process clientpid
      FROM v$process a, v$session b
     WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

    • V$SESSION.SID AND V$SESSION.SERIAL# 是数据库进程 ID

    • V$PROCESS.SPID 是数据库服务器后台进程 ID

    • V$SESSION.PROCESS 是客户端 PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

  32. 查询特定的模式或者表中执行的最后一个 SQL 语句

    1
    2
    3
    4
    5
    SELECT CREATED, TIMESTAMP, last_ddl_time
      FROM all_objects
     WHERE     OWNER = 'MYSCHEMA'
           AND OBJECT_TYPE = 'TABLE'
           AND OBJECT_NAME = 'EMPLOYEE_TABLE';

  33. 查询每个执行读取的前十个 SQL

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT *
      FROM (  SELECT ROWNUM,
                     SUBSTR (a.sql_text, 1, 200) sql_text,
                     TRUNC (
                        a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
                        reads_per_execution,
                     a.buffer_gets,
                     a.disk_reads,
                     a.executions,
                     a.sorts,
                     a.address
                FROM v$sqlarea a
            ORDER BY 3 DESC)
     WHERE ROWNUM < 10;

  34. 在视图中查询并显示实际的 Oracle 连接

    1
    2
    3
    4
    5
    6
    SELECT osuser,
             username,
             machine,
             program
        FROM v$session
    ORDER BY osuser;

  35. 查询并显示通过打开连接程序打开连接的组

    1
    2
    3
    4
    SELECT program application, COUNT (program) Numero_Sesiones
        FROM v$session
    GROUP BY program
    ORDER BY Numero_Sesiones DESC;

  36. 查询并显示连接 Oracle 的用户和用户的会话数量

    1
    2
    3
    4
    SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
        FROM v$session
    GROUP BY username
    ORDER BY Numero_Sesiones DESC;

  37. 获取拥有者的对象数量

    1
    2
    3
    4
    SELECT owner, COUNT (owner) number_of_objects
        FROM dba_objects
    GROUP BY owner
    ORDER BY number_of_objects DESC;

    实用/数学 相关的查询

  38. 把数值转换成文字

    1
    SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

    输出:

    1
    one thousand five hundred twenty-six

  39. 在包的源代码中查询字符串

    这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING’ ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用。

    1
    2
    3
    4
    5
    --search a string foo_something in package source code
    SELECT *
      FROM dba_source
     WHERE UPPER (text) LIKE '%FOO_SOMETHING%' 
    AND owner = 'USER_NAME';

  40. 把用逗号分隔的数据插入的表中

    当 你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 IN 或者是 NOT IN 。这里我们把‘AA,BB,CC,DD,EE,FF’转换成包含 AA,BB，CC 等作为一行的表，这样你就很容易把这些字符串插入到其他表中，并快速的做一些相关的操作。

    1
    2
    3
    4
    5
    6
    7
    WITH csv
         AS (SELECT 'AA,BB,CC,DD,EE,FF'
                       AS csvdata
               FROM DUAL)
        SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
          FROM DUAL, csv
    CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

  41. 查询表中的最后一个记录

    这个查询语句很直接，表中没有主键，或者是用户不确定记录最大主键是否是最新的那个记录时，就可以使用这个语句来查询表中最后一个记录。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT *
      FROM employees
     WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);
     
    (OR)
     
    SELECT * FROM employees
    MINUS
    SELECT *
      FROM employees
     WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

  42. 在 Oracle 中做行数据乘法

    这个查询语句使用一些复杂的数学函数来做每个行的数值乘法。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    WITH tbl
         AS (SELECT -2 num FROM DUAL
             UNION
             SELECT -3 num FROM DUAL
             UNION
             SELECT -4 num FROM DUAL),
         sign_val
         AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
               FROM tbl
              WHERE num < 0)
      SELECT EXP (SUM (LN (ABS (num)))) * val
        FROM tbl, sign_val
    GROUP BY val;

  43. 在 Oracle 生成随机数据

    每个开发者都想能轻松生成一堆随机数据来测试数据库多好，下面这条查询语句就可以满足你，它可以在 Oracle 中生成随机的数据插入到表中。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT LEVEL empl_id,
               MOD (ROWNUM, 50000) dept_id,
               TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
               DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,
               TO_DATE (
                     ROUND (DBMS_RANDOM.VALUE (1, 28))
                  || '-'
                  || ROUND (DBMS_RANDOM.VALUE (1, 12))
                  || '-'
                  || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
                  'DD-MM-YYYY')
                  dob,
               DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
          FROM DUAL
    CONNECT BY LEVEL < 10000;

  44. 在 Oracle 中生成随机数值

    这是 Oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。

    1
    2
    --generate random number between 0 and 100
    SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

  45. 检查表中是否含有任何的数据

    这个可以有很多中写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。

    1
    2
    3
    SELECT 1
      FROM TABLE_NAME
     WHERE ROWNUM = 1;
上一篇：Oracle语句中IN和=的区别有哪些？


下一篇：MyBatis项目实战 快速将MySQL转换成Oracle语句