Oracle SQL学习笔记

参考：FreeIT-Oracle教程

实用sql

0.1、update内容中含有单引号

方案：用两个单引号代替一个单引号
select to_char('aa''否''aa') from dual;--aa'否'aa

0.2、找表

-- 找表
select a.OWNER 表所属用户,a.TABLE_NAME 表明,a.* from all_tables a where a.TABLE_NAME like upper('%zyy%');

0.3、找最大的表和表空间

-- 分析各个表空间的大小
SELECT a.tablespace_name                      "表空间名称",
       total / (1024 * 1024)                  "表空间大小(M)",
       free / (1024 * 1024)                   "表空间剩余大小(M)",
       (total - free) / (1024 * 1024)         "表空间使用大小(M)",
       total / (1024 * 1024 * 1024)           "表空间大小(G)",
       free / (1024 * 1024 * 1024)            "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024)  "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
      FROM dba_free_space
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) total
      FROM dba_data_files
      GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
--查找特定的表空间，占用最大空间的表
select t.owner, t.segment_name, t.tablespace_name, bytes / 1024 / 1024 / 1024 as sizes, q.num_rows, t.segment_type
from dba_segments t
         left join dba_tables q
                   on t.segment_name = q.table_name
                       and t.owner = q.owner
where t.segment_type = 'TABLE'
  and t.tablespace_name = 'CCPP' --需要查看的表空间
order by bytes / 1024 / 1024 / 1024 desc
--Oracle查询表占用的空间
SELECT segment_name "表名",
       segment_type "对象类型",
       sum(bytes) / 1024 / 1024 "占用空间（MB）"
  FROM dba_extents
 WHERE segment_name = 'MCPP_WEBSERVICE_LOG'--表名
 GROUP BY segment_name, segment_type;

0.4、消除数字转字符异常

SELECT to_char(00.00800) FROM dual;-- .008
SELECT to_char(1111.008, 'fm9999999999999990.00') FROM dual;-- 1111.008
select trim(trailing '0' from 0.0780)+0 from dual; --0.078

0.5、添加同义词

create synonym pf_get_ordersource for gzmpcmis.pf_get_ordersource;

0.6、按照特定顺序排序

参考：MySQL或Oracle按照自定义顺序进行排序语法 ORDER BY

-- 利用现有字段
select a.printline,a.goodsqty,a.invid,a.* from bms_sa_settle_dtl a where a.sasettleid=29783058 order by a.printline asc,a.goodsqty desc;

-- 构造新排序字段
with dtl as
 (select 1 id, 50 unitprice, 200 goodsid from dual
  union all select 2, 100, 300 from dual
  union all select 3, -100, 300 from dual
  union all select 4, -52, 400 from dual
  union all select 5, 52, 400 from dual)
select a.*
  from dtl a
 order by (select 1 from dtl aa where aa.unitprice<0 and aa.goodsid=a.goodsid)   asc,a.goodsid asc,a.unitprice desc;

0.7、查询慢sql

-- 慢查询耗时
select *
 from (select sa.SQL_TEXT "执行 SQL",
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE,sa.MODULE,sa.*
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0 and sa.LAST_ACTIVE_TIME>sysdate-1
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc);

--# 查询次数最多的 SQL 
select *
 from (select s.SQL_TEXT,
        s.EXECUTIONS "执行次数",
        s.PARSING_USER_ID "用户名",
        rank() over(order by EXECUTIONS desc) EXEC_RANK
     from v$sql s
     left join all_users u
      on u.USER_ID = s.PARSING_USER_ID) t;

0.8、plsql调用存储过程

declare
	licenseid varchar2(100);
	info varchar2(100);
begin
	licenseid := '0';
  info := zx_chk_busicontrol_pk.pf_chk_supply_license(247340,1000200782,sysdate + 15,licenseid);
	dbms_output.put_line('结果：'||licenseid);
end;

0.9、删除表、复制表数据

-- 用于数据备份
-- 删
drop table zx_goodslimit_to_customer_zyy;
-- 复制
CREATE TABLE zx_goodslimit_to_customer_zyy AS SELECT * FROM zx_goodslimit_to_customer;

0.10、关于锁

-- 查询表被谁锁了
SELECT a.* FROM v$locked_object a WHERE object_id in (SELECT object_id FROM all_objects WHERE object_name = 'ZX_BMS_SA_PLAN_CTM');

0.11触发器审核日志

-- 触发器
-- 记录操作人
select SYS_CONTEXT('USERENV', 'SESSION_USER') from dual;
-- 机器ip
select sys_context('USERENV', 'IP_ADDRESS') from dual;
-- 机器程序
SELECT SYS_CONTEXT('USERENV', 'MODULE') FROM DUAL;
-- 机器程序动作
SELECT SYS_CONTEXT('USERENV', 'ACTION') FROM DUAL;
/*
1. **USERENV**：会话环境信息，比如当前用户、当前模式等。
2. **SESSION**：当前会话中的特定信息。
3. **CLIENT_IDENTIFIER**：客户端标识信息。
4. **IP_ADDRESS**：客户端IP地址。
5. **MODULE**：应用程序模块信息。
6. **ACTION**：应用程序动作信息。
7. **CLIENT_INFO**：客户端信息。
*/
select a.* from trig_sql_zyy a;
-- 日志表
create table trig_sql_zyy(
         LT DATE not null primary key,--操作时间
         SID NUMBER,--会话唯一标识
     　 SERIAL#       NUMBER,--唯一序列号
         USERNAME       VARCHAR2(30),--数据库用户
         OSUSER    VARCHAR2(64),--客户端操作系统用户名
         MACHINE       VARCHAR2(32),--客户端全名
         TERMINAL       VARCHAR2(16),--客户端名
         PROGRAM       VARCHAR2(64),--客户端应用程序
         SQLTEXT       VARCHAR2(2000),--SQL文本
         STATUS    VARCHAR2(30),-- 增删改
         CLIENT_IP       VARCHAR2(60)--IP地址
);
-- 创建索引
create index  idx_time on trig_sql (LT);

-- 触发器
create or replace trigger pri_test
  after insert or update or delete on bms_sa_dtl
  for each row
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF inserting THEN
    INSERT INTO trig_sql_zyy
      select sysdate,
             s.SID,
             s.SERIAL#,
             s.USERNAME,
             s.OSUSER,
             s.MACHINE,
             s.TERMINAL,
             s.PROGRAM,
             q.sql_text line,
             'INSERT',
             sys_context('userenv', 'ip_address')
        from v$sql q, v$session s
       where s.audsid = (select userenv('SESSIONID') from dual)
         and s.prev_sql_addr = q.address
         AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  ELSIF deleting then
    INSERT INTO trig_sql_zyy
      select sysdate,
             s.SID,
             s.SERIAL#,
             s.USERNAME,
             s.OSUSER,
             s.MACHINE,
             s.TERMINAL,
             s.PROGRAM,
             q.sql_text line,
             'DELETE',
             sys_context('userenv', 'ip_address')
        from v$sql q, v$session s
       where s.audsid = (select userenv('SESSIONID') from dual)
         and s.prev_sql_addr = q.address
         AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  ELSIF updating then
    INSERT INTO trig_sql_zyy
      select sysdate,
             s.SID,
             s.SERIAL#,
             s.USERNAME,
             s.OSUSER,
             s.MACHINE,
             s.TERMINAL,
             s.PROGRAM,
             q.sql_text line,
             'UPDATE',
             sys_context('userenv', 'ip_address')
        from v$sql q, v$session s
       where s.audsid = (select userenv('SESSIONID') from dual)
         and s.prev_sql_addr = q.address
         AND s.PREV_HASH_VALUE = q.hash_value;
    COMMIT;
  END IF;
END;

常用函数

1、字符串拼接

--LISTAGG函数用于将多个列的值进行连接，并返回一个已连接的字符串。这个函数在需要将多行合并为一个字符串的情况下非常有用。
SELECT department_id, LISTAGG(first_name, ', ') WITHIN GROUP(ORDER BY irst_name) AS employees FROM employees GROUP BY department_id;

-- strcat 函数，自动用英文逗号拼接
select strcat(aa.type) from (select 1 type from dual
union select 2 type from dual) aa;

2、替换数据中的空格、换行

chr(9) HT  制表符 \t  ：
chr(10) LF  换行 \n ：newline ：是换行，后者使光标下移一格，（line feed）
chr(13) CR  回车 \r ：return：是回车，前者使光标到行首，（carriage return）
chr(32) (space) 空格
select replace(' 123 ',chr(32),'') from dual;

3、查看oracle版本

--java对应版本查询 https://www.oracle.com/database/technologies/appdev/jdbc-drivers-archive.html
-- 查看oracle版本
SELECT * FROM v$version;

4、查客户端ip

select sys_context('USERENV', 'IP_ADDRESS') from dual;

5、字符串分割

参考：Oracle字符串拆分实例详解

-- 注：目前实践，例子的写法只能对一行的一个字段有效
select REGEXP_SUBSTR('99956,10565,100006,52560,1111115,1111116,100034', '[^,]+', 1, rownum) as newport 
    from dual connect by rownum <= REGEXP_COUNT('99956,10565,100006,52560,1111115,1111116,100034', '[^,]+');

1、基础

1.1、关键字

1.1.1、select 查询数据

-- select命令结构
select *|列名|表达式 from 表名 where 条件 order by 列名

-- 查询学生信息表（stuinfo）中“李四”同学的基本信息
select t.* from STUDENT.STUINFO t where t.stuname = '李四';

-- 查询班级“C201801”所有同学信息，按年龄进行升序展示
select t.*  from STUDENT.STUINFO t where t.classno = 'C201801' ORDER BY T.AGE ASC;

-- 备份查询数据
-- 备份查询数据命令结构
create table 表名 as select 语句