Oracle常用sql
数据库
查看数据库版本
select * from v$version
查看被锁的表
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秒钟 |
更新date字段的日期部分
1.to_char(DATE类型,'yyyy-MM-dd')获得日期部分字符串,这个DATE类型是字符串to_date获得
2.to_char(DATE类型,'hh24:mi:ss')获得时间部分字符串,这个DATE类型是数据库的date字段
3.to_date(日期字符串||时间字符串,'yyyy-mm-dd hh24:mi:ss')
update system set systime = to_date(
to_char(to_date('20220208','yyyy-MM-dd'),'yyyy-MM-dd') -- 先获得日期部分
|| to_char(systime,'hh24:mi:ss') -- 再获得数据库中的时间部分并拼接
,'yyyy-mm-dd hh24:mi:ss') --把获得的日期字符串和时间字符串拼接并转换为date
where id = ?
一对多查询
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;
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)