我的oracle工具箱（常用函数指令)

最新推荐文章于 2023-04-28 21:36:27 发布

只要你一直跑

最新推荐文章于 2023-04-28 21:36:27 发布

阅读量332

点赞数

分类专栏：学习笔记文章标签： pl/sql

本文链接：https://blog.csdn.net/lingfy1234/article/details/90179134

版权

学习笔记专栏收录该内容

38 篇文章 1 订阅

订阅专栏

一.函数

1.1字符函数

1.1.1大小写转化函数

A ：select * from emp where ename=‘king’;
B ：select * from emp where lower（ename）=‘king’;
C：select * from emp where ename= upper(‘king’);
D：select initcap(‘hello world’ ) from dual;–由于Oracle的语句完整性约束，需要写from，所以dual这张伪表

1.由于Oracle数据库的是对表中数据大小写敏感的，所以当表中数据是大写的时候，A语句无法查询出数据，此时可以用B语句来实现
2.由于B会使整个表的ename都变为小写，效率是非常低的，此时可以使用C语句
3.语句使得单词的首字母是大写的，这个函数是无法识别单词，它是通过检测空格来实现的

1.1.2其他函数：

A：substr（Hellolfy,1,6）–>Hellol 相当于“||”，从1开始，包含首尾
B：concat（A，B）–>AB：
C1：trim（’h‘ from ‘helloword’）–>elloworld(去除函数，一般用于去除空格)
C2 ：trim(’ hello world’)–>helloworld
D： replace（‘abc’,‘a’,‘hello’）–>hellobc(将abc中的a替换为hello)

1.2数字函数

trunc(18.56)=18–抹零头
round(18.56)=19–四舍五入，传其他的参数可以有别的处理方法

1.3日期函数

问题：日期是否可以相减，可以相加吗？
答：可以相减，有意义，但是相加无意义
当前日期：sysdate ：2019/6/11 9:44
systimestamp:11-6月 19 10:04:55 3000000 上午+8(精确到毫秒数，东八区)
通过例子来了解：
需求1：计算员工工龄,多少天(四舍五入)，几个月了(向下取整)，几年了(向下取整)：
select round(sysdate-hireday) 天，trunc(months_between(sysdate,hireday) ) 月， trunc(months_between(sysdate,hireday)/12 ) 年 from dual;
需求2：查看当月最后一天
last_day（sysdate）
需求3：
查看指定日期的下一个星期几是几号：
next_day（‘sysdate’，‘2’）；–得到17，表示这个星期的下一个星期一是17号，西方人的一周从周日开始，所以‘1’表示周天，‘2’表示周一，以此类推

1.4滤空函数

NVL(a,b):如果a为空的话，令它为b
NVL2(a,b,c):如果a为空的话，令它为b,否则为c
NULLIF（a,b）当a=b时，返回null，否则返回a
COALESCE(a,b,c,d),从左往右开始找，返回第一个不为空的值

1.5条件表达式

1.5.1 case when 与count的结合

第一种形式：- – 查询 CASE 语句（Searched CASE
count(CASE WHEN mh.valid_flag =‘2’ THEN 1 else null end) as validMedicalHisCount,
count(CASE WHEN mh.valid_flag in(‘1’,‘3’) THEN 1 else null end) as invalidMedicalHisCount,
count(CASE WHEN mh.valid_flag in(‘1’,‘2’,‘3’) THEN 1 else null end) as pushMedicalCount
第二种形式
CASE GENDER
WHEN 1 THEN ‘男’
ELSE ‘女’
END AS GENDER,

1.5.2DECODE 函数

语法：DECODE(value,if1,then1,if2,then2,if3,then3,…,else)
例子：SELECT DECODE(GENDER, 1, ‘男’, 2, ‘女’, ‘未知’) FROM USER_INFO;

1.6 嵌套函数

distinct（dept）过滤重复的部门

1.7转换函数

1.7.1隐式转换：

select * from emp where dept_no=10;
select * from emp where dept_no=‘10’;会帮你隐式转换,但是会影响效率的
支持的转换：
varchar2 or char–>number
varchar2 or char–>date
number ->varchar2
date ->varchar2
非法转换：select * from emp where dept_no=‘10q’

1.7.2显示转换

在这里插入图片描述
需求1：显示今天的完整日期，参考‘2019-6-11 10:46’
select to_char(‘yyyy-mm-dd hh24:mm:ss’,sysdate) from dual
细节：无法正确显示分钟，原因：yyyy-mm-dd hh:mm:ss是Java的日期格式，在这里用就不能正确显示
Java与Oracle的日期格式的区别：
1.Orcle 的大小写不区分yyyY-mM-dd hh24:mm:ss与yyyy-mm-dd hh24:mm:ss是一样的
2.Oracle的分钟用mi表示，yyyy-mm-dd hh24:mi:ss才能正确显示

需求2：显示今天几号：
select to_char(‘dd’,sysdate) ||'日’from dual
需求3：显示这个月最后一天是几号：
select last_day(‘dd’,sysdate) ||‘号’from dual
需求4：显示2019年2月最后一天是几号：
select to_char(last_day(to_date(‘yyyy-mm’,2019-02) ),‘dd’)||‘号’from dual

二.查询

1.查询某表的所有字段的注释：

select * from user_col_comments where Table_Name = ‘ODS_MEDICAL_HISTORY_INFO’;

2.查询某表的所有字段：

select * from all_tab_columns where TABLE_NAME = ‘ODS_MEDICAL_HISTORY_INFO’ ;

3.性能优化

不是where里的语句不会影响优化，一般性能优化只是针对查询条件和大表关联，这种纯展示的随便折腾
在这里插入图片描述

4.where语句中加判断

查询字段值为空就查找全部的：
例如v_ename为传入的值，v_ename为空就找全部，否则查询条件为 t.ename=v_ename
解决办法1：
WHERE t.ename=v_ename=(case when v_ename is null then t._ename else v_ename end)
要点，并不需要等于*，而是让t_name等于自己
解决办法2：
WHERE t.ename = NVL(v_ename is null， t.ename)；
问题：数据量大的时候，case when使得性能很差
在自己写的例子中，用f5调试，发现有case的语句与简单的条件查询有差别，cost一样，candidate差十几倍

5.查询游标

查询所有打开的游标

select count(*) from v$open_cursor;

三.动态拼接

3.1.关于绑定单条记录与结果集的用法（https://www.cnblogs.com/zmlctt/p/3750198.html）

动态拼接用于解决上面的问题，提高效率的做法。需要注意的是，所有的变量都需要放在using后面，然而造成一个问题：当where条件是可选的时候，如果没有存入参数，那就还会提示未绑定参数

3.2关于变量的绑定的三种方法：（https://blog.csdn.net/qiuzhi__ke/article/details/81014821）

1.单引号法：

v_sql := ’ insert into BJTONGRENTANGTEMPTB select distinct h.sellerid,h.sellercode,h.sellername,h.prodcode,h.prodname from historyofsales_day h ’
||’ where h.sellerid in (select distinct ovalorgid from bjtongrentangpc ) ’
||’ and h.prodcode in (select prodcode from buproduct where bucode= ‘’’||v_bucode||’’’)’
||’ and to_char(h.salesdate,’‘yyyyMM’’) =’’’ || v_year||v_month||’’’’;
if v_productcode is not null then
v_sql := v_sql || ’ and h.prodcode = ‘’’||v_productcode||’’’’;
end if;
if v_seller is not null then
v_sql := v_sql || ’ and h.sellername like ‘’%’||v_seller||’%’’’;
end if;
if v_provincecode is not null then
v_sql := v_sql || ’ and h.buyerprovincecode = ‘’’||v_provincecode||’’’’;
end if;
if v_productspec is not null then
v_sql := v_sql || ’ and h.prodspec like ‘’%’||v_productspec||’%’’’;
end if;
execute immediate v_sql;
commit;

利用execute immediate using占位符语法处理

v_sql := ’ insert into BJTONGRENTANGTEMPTB select distinct h.sellerid,h.sellercode,h.sellername,h.prodcode,h.prodname from historyofsales_day h ’
||’ where h.sellerid in (select distinct ovalorgid from bjtongrentangpc ) ’
||’ and h.prodcode in (select prodcode from buproduct where bucode= :1)’
–||’ and to_char(h.salesdate,’‘yyyyMM’’) =:v2:v3’;
||’ and to_char(h.salesdate,’‘yyyy’’) =:v2’;
–execute immediate v_sql using v_bucode,v_year,v_month; --error ORA-01006：绑定变量不存在
execute immediate v_sql using v_bucode,v_year;
commit;

3.利用chr(39)转义单引号

v_sql := ’ insert into BJTONGRENTANGTEMPTB select distinct h.sellerid,h.sellercode,h.sellername,h.prodcode,h.prodname from historyofsales_day h ’
||’ where h.sellerid in (select distinct ovalorgid from bjtongrentangpc ) ’
||’ and h.prodcode in (select prodcode from buproduct where bucode= ‘||chr(39)||v_bucode||chr(39)||’)’
||’ and to_char(h.salesdate,’‘yyyyMM’’) =’ ||chr(39)|| v_year||v_month||chr(39);
if v_productcode is not null then
v_sql := v_sql || ’ and h.prodcode = ‘||chr(39)||v_productcode||chr(39);
end if;
if v_seller is not null then
v_sql := v_sql || ’ and h.sellername like ‘||chr(39)||’%’||v_seller||’%’||chr(39);
end if;
if p_provincename is not null then
v_sql := v_sql || ’ and h.buyerprovincename = ‘||chr(39)||p_provincename||chr(39);
end if;
if v_productspec is not null then
v_sql := v_sql || ’ and h.prodspec like ‘||chr(39)||’%’||v_productspec||’%’||chr(39);
end if;