Oracle常用sql

数据库

查看被锁的表

select b.object_id,b.object_name,b.owner,a.* from v$locked_object a,dba_objects b where b.object_id=a.object_id;

查看所有连接进程

  set linesize 400;
  set pagesize 400;
  set long 4000;
  col SQL_FULLTEXT format a100;
  col machine format a25;
  col username format a15;

  SELECT a.username,a.machine, b.sql_id, b.SQL_FULLTEXT
  FROM v$session a, v$sqlarea b
  WHERE a.sql_address = b.address
  AND a.SQL_HASH_VALUE = b.HASH_VALUE;

杀掉锁表进程

-- 1.查询引起了锁表的原因
select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
  where l.session_id = s.sid
  and s.prev_sql_addr = a.address
  order by sid, s.serial#;

-- 2.查询被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

-- 3.查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time from  v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

-- 4.杀掉对应进程
alter system kill session 'sid,serial';

plsql查询

整个数据库中查询某个值

/*
整个数据库中查询某个值
param: 表所属用户  要查询的值
return: 表名.字段名
*/
declare
  v_owner varchar2(2000) := 'YZ_PUBSER';
  v_param varchar2(2000) := '扬州市';
  v_Sql   varchar2(2000);
  v_count number;
begin
  for xx in (select t.OWNER, t.TABLE_NAME, t.COLUMN_NAME from dba_tab_columns t where t.OWNER = 'SXZP_PUBSER') loop
    begin
      v_Sql := 'select count(1) from ' || xx.owner || '.' || xx.table_name ||' where ' || xx.column_name || ' like ''%'||v_param||'%'' ';
      execute immediate v_Sql into v_count;
      if (v_count >= 1) then
        -- 打印表名及列名
        dbms_output.put_line(xx.table_name || '.' || xx.column_name);
        -- 打印查询sql
        dbms_output.put_line('select t.rowid,t.* from '||xx.table_name || ' t where t.' || xx.column_name || ' like ''%'||v_param||'%'' ');
      end if;
    exception
      when others then
        null;
    end;
  end loop;
end;

数据库中查询字段所在表及其注释

-- 查询字段所在表
select table_name from DBA_TAB_COLUMNS where COLUMN_NAME='AAA005';
-- 查询字段所在表及其注释，user_xx_xx只能查询当前所有者的表，如果要查询其他拥有者的表要用all_xx_xx
select * from user_col_comments 
where  COLUMN_NAME='AAB301' order by column_name,TABLE_NAME;

查询表注释

-- 查看数据库中所有表注释
select * from user_tab_comments order by table_name;

-- 查看表中所有列注释
select * from user_col_comments 
where table_name = 'CD39' order by column_name;

plsql-DML操作

execute动态执行DML语句

PL/SQL程序是通过PL/SQL执行时，把SQL语句当做字符串的形式传给动态SQL执行语句执行。动态SQL语句的写法如下：
EXECUTE IMMEDIATE 动态SQL语句 [into 变量列表] [ using 参数列表]

declare
    mobile varchar2(20) :=  '153*****';
    v_sql1 varchar2(2000);
    v_sql2 varchar2(2000);
begin 
    -- 先删除子表数据
    v_sql1 := 'delete from company t where uid = (select uid from s_user t where t.mobile = '''||mobile||''')';
    execute immediate v_sql1;
    dbms_output.put_line(v_sql1);
    dbms_output.put_line('影响行数'||sql%rowcount );

    -- 再删除主表数据
    v_sql2 := 'delete from s_user t where t.mobile = :1';
    execute immediate v_sql2 using mobile;
    dbms_output.put_line(v_sql2) ;
    dbms_output.put_line('影响行数'||sql%rowcount );

end;

sql查询

查询重复数据

-- 查询重复数据，重复记录是根据单个字段（groupid）来判断
select * from smt_group where groupid in (select groupid from smt_group group by groupid having count(groupid) > 1) order by groupid;

-- 删除重复数据，重复记录是根据单个字段（groupid）来判断，只留有rowid最小的记录
DELETE from smt_group WHERE 
(groupid) IN (SELECT groupid FROM smt_group GROUP BY groupid HAVING COUNT(groupid) > 1) 
AND ROWID NOT IN (SELECT MIN(ROWID) FROM smt_group GROUP BY groupid HAVING COUNT(*) > 1);

树型结构查询

start with condition是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
prior在id前面是自上而下查询，prior在parentid前面为自下而上查询。

-- 查询扬州321000下面的所有组织结构
select t.rowid,t.* from base_area t 
start with t.areaid = '321000' 
connect by prior t.areaid = t.parentid 
order by areaid;

类型转换

tochar日期转字符串

将数值型或者日期型转化为字符型

select to_char(sysdate, 'yyyy-mm-dd') from dual

to_date字符串转日期

入库时使用，oracle入库date可以只存储年月日格式，可以不存储时分秒。

insert into person(id,birthday) values(:id,to_date('birthdayStr','yyyy-MM-dd'));

判断查询

decode

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

SELECT decode(9,1,'autumn',2,'aeolian',3,'lalala~','deafult value') FROM dual;

decode进阶用法
先在子查询的字段关联查询中select count,然后外层包一个select,配合decode和sign函数判断有无子表数据返回对应的字符串。

case when语句

select case 1 
  when 1 then 'autumn' 
  when 2 then 'aeolian' 
  when 3 then 'lalala~' 
else 'deafult value' 
end from dual;

日期

默认时间

-- 默认timestamp时间
select to_timestamp(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') from dual;
-- 默认date时间
select to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;

根据生日查询年龄

select floor(months_between(sysdate,to_date('1995-09-26 00:00:00','yyyy-MM-dd hh24:mi:ss'))/12) as age 
from dual;

根据查询某天23:59:59

select to_timestamp('2022-06-25', 'yyyy-mm-dd hh24:mi:ss.ff')+0.99999 from dual

起始时间查询

-- 开始时间
begintime > to_timestamp('2015-06-25', 'yyyy-mm-dd hh24:mi:ss.ff')

-- 结束时间
begintime <= to_timestamp('2015-06-25', 'yyyy-mm-dd hh24:mi:ss.ff') ++0.99999

select * from log  
where logetime >= to_timestamp('2022-7-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 
and logetime <= to_timestamp('2022-7-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ++0.99999 
 order by logetime desc 

字段查询 - 子表统计是否在举办期间内

-- 查询子表中已经举行结束的总量
 (select count(1) from applytbl t1 where t1.fid = a.id 
 and finishtime < to_date(to_char(sysdate, 'yyyy-mm-dd'),'yyyy-mm-dd')) finished;
-- 查询子表中正在举行的总量
 (select count(1) from applytbl t1 where t1.fid = a.id 
 and finishtime >= to_date(to_char(sysdate, 'yyyy-mm-dd'),'yyyy-mm-dd') 
 and starttime <= to_date(to_char(sysdate, 'yyyy-mm-dd'),'yyyy-mm-dd')) isopening;

N小时/分钟/秒前

-- 一小时之前
select sysdate - 1/24  from dual;

SQL	含义
sysdate+1	加一天
sysdate+1/24	加1小时
sysdate+1/(24*60)	加1分钟
sysdate+1/(246060)	加1秒钟
sysdate-1	减一天
sysdate-1/24	减1小时
sysdate-1/(24*60)	减1分钟
sysdate-1/(246060)	减1秒钟

一对多查询

listagg() with group()

listagg(colName,',') within group(order by t.colName)查询从表，在每个分组内，LISTAGG根据order by子句对列进行排序，将排序后的结果以指定字符拼接起来

select listagg(t.code_name,',') within group(order by t.code_value) as education 
from code_value t
where t.code_type = 'AAC011'

基础sql

分页sql

无ORDER BY排序的写法

此方法成本最低，只嵌套一层，速度最快，即使查询的数据量再大，也几乎不受影响。

SELECT *
  FROM (SELECT ROWNUM AS rowno, t.*
          FROM emp t
          WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                             AND TO_DATE ('20060731', 'yyyymmdd')
          AND ROWNUM <= 20) table_alias
 WHERE table_alias.rowno >= 10;

有ORDER BY排序的写法

需要排序的语句需要单独再嵌一层，此方法随着查询范围的扩大，速度也会越来越慢

SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (SELECT t.*
                    FROM emp t
                    WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
                                       AND TO_DATE ('20060731', 'yyyymmdd')
                    ORDER BY create_time DESC, emp_no) tt
          WHERE ROWNUM <= 20) table_alias
  WHERE table_alias.rowno >= 10;

当使用curpage和pagesize时，因为rownum是从1开始，所以rownum > (curpage-1)pagesize并且ROWNUM < (curpagepagesize+1)

SELECT *
  FROM (SELECT tt.*, ROWNUM AS rowno
          FROM (
                 --sql语句
               ) tt
          WHERE ROWNUM < (curpage*pagesize+1) ) table_alias
  WHERE table_alias.rowno > (curpage-1)*pagesize;

多字段in

oracle中可以多字段组合in

SELECT t.*
  FROM tblName t
  WHERE (t.column1, t.column2) IN
        (('val_11', 'val_12'),('val_21', 'val_22'));

常用函数

TRUNC(date,[fmt])

date 为必要参数，是输入的一个日期值
fmt 参数可忽略，默认按天截断。
trunc(sysdate,'yyyy') --返回当年第一天。
trunc(sysdate,'mm') --返回当月第一天。
trunc(sysdate,'day') --返回当前星期的第一天(周末为第一天)。
trunc(sysdate,'dd') --返回当天零时。

substr

截取字符串substr(字符串,开始截取位数,截取位数)
截取身份证: substr(idcard,13,6)

concat

字符串拼接concat(字符串1,字符串2)

lower

字符转小写

sign判断是否开始

正数返回1，负数返回-1，0返回0

-- 判断主表记录是否已经开始,是则返回1，否则返回0
(select sign(count(1)) from dual where a.starttime < sysdate) isStarted

常用函数

md5函数

select utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'1')) from dual;

-- 创建md5函数
CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2) 
RETURN VARCHAR2 IS
  retval varchar2(32);
BEGIN
  /*DBMS_OBFUSCATION_TOOLKIT.MD5是md5函数, =>是给函数变量INPUT_STRING指定参数;
    utl_raw.cast_to_raw函数是为了防止乱码*/
  retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd));
  RETURN retval;
END;

-- 使用md5函数
select md5('123456') from dual;