Oracle函数学习（结合iBatis）

已于 2022-02-15 14:10:07 修改
阅读量744 收藏 1
点赞数
文章标签： oracle
于 2021-07-23 18:41:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_41523110/article/details/119038691
版权

把’- -'赋值给isgold

select '- -' isgold from dual;

在这里插入图片描述

dynamic

<dynamic prepend=" ">

当需要使用根据传入参数的值来动态组装SQL时，可以使用dynamic标签。
dynamic元素可以包含多个条件比较元素，并且按照条件比较元素的表述对参数值进行比较，来组装动态SQL。
这里主要的条件比较元素包含isGreaterThan、isNotNull、isEmpty……
dynamic元素和条件比较元素，都是组成动态SQL的一部分，其中的prepend属性根据实际情况的需要辅助的组装动态SQL。

dynamic要覆盖第一个为“真”的条件比较元素的prepend属性，首先需要检测该tag的prepend是否可用（tag.isPrependAvailable()），如果不可用的话，即便该比较条件为“真”，则不会覆盖，只是单独的添加该比较条件元素下的SQL


  public boolean isPrependAvailable() {
    return prependAttr != null && prependAttr.length() > 0;
  }

prepend可用的前提是prepend属性值已经设置，并且长度>0。

<![CDATA[]]>
在xml中用<![CDATA[<>]]>表示"<>"
在XML中，需要转义的字符有：
　　(1)&　　 &amp;
　　(2)<　　 &lt;
　　(3)>　　 &gt;
　　(4)＂　　 &quot;
　　(5)＇　　 &apos;
为了方便起见，使用<![CDATA[]]>来包含不被xml解析器解析的内容。但要注意的是：
　　(1) 此部分不能再包含”]]>”；
　　(2) 不允许嵌套使用；
　　(3)”]]>”这部分不能包含空格或者换行。

isNotNull和isNotEmpty
null不指向任何对象，相当于没有任何值；而’‘代表一个长度为0的字符串
null不分配内存空间；而’'会分配内存空间
isNotNull 参数不为Null时返回true，
isNotEmpty 参数既不为Null也不为空时返回true，
isNotEmpty 比 isNotNull 多了一个非空的判断。

isEqual 相当于String 里的equal方法
property：指定比较属性的名称，
compareValue：表示要比较的参数，
prepend：表示追加比较条件

“||”代表连接两个字符串
“+”代表作相加运算

row_number() over(partition by 分组列 order by 排序列)
分组排序：每一个分组后的组组内排序

count(*) over(partition by 分组列 order by 排序列)
当在over()中带order by xxx时，如果没分组，则相同xxx的记录的count()over()值都一样，为最大的那个值；如果分组，则相同组相同xxx的count()over()值一样，也为最大的那个值。

  • count(*) over()
    求总计数
select stuId,stuName,stuSex,stuAge,grade,count(*) over() coID from BIZ_FJTEST order by stuId;

在这里插入图片描述

  • count(*) over(order by xxx)
    递加求计数（当xxx有一样的时，值都为相同xxx的最下面那一行数据的顺序）
select stuName,stuSex,stuAge,grade,count(*) over(order by stuAge) coID from BIZ_FJTEST;

在这里插入图片描述

  • count(*) over(partition by xxx)
    分组求计数,
select stuId,stuName,stuSex,stuAge,grade,count(*) over(partition by stuAge) coID from BIZ_FJTEST;

在这里插入图片描述

  • count(*) over(partition by xxx order by yyy)
    分组递加求计数
select stuId,stuName,stuSex,stuAge,grade,count(*) over(partition by stuSex order by grade) coID from BIZ_FJTEST;

在这里插入图片描述

select stuId,stuName,stuSex,stuAge,grade,count(*) over(partition by stuSex order by stuAge) coID from BIZ_FJTEST;

在这里插入图片描述
row_number()over()与count(*)over()区别：同一组内不会存在重复的row_number()over()值，而可能存在一样的count(*)over()值

instr函数（字符查找）
注意！！！从1开始而不是从0开始。先找到这个字符，然后从左开始往右算这个字符排在第几位。起始位置为负数时，这个字符从右边往左找，-1则从最右边的字符开始。
格式一：instr( string1, string2 ) / instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

select instr('helloworld','l') from dual;	3   第一次出现"l"的位置	和('helloworld','l',1,1)效果一样
select instr('helloworld','lo') from dual;	4	第一次出现"lo"的位置
select instr('helloworld','l',2,2) from dual;	4	在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',-2,2) from dual;	4	在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置

round函数（四舍五入）
传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果
round(number,decimal_places)
number：待处理的数值
decimal_places：四舍五入 , 小数取几位 ( 预设为 0 )

select round(123.456, 0) from dual； 返回123
select round(123.456, 1) from dual; 返回123.5
select round(-123.456, 2) from dual; 返回-123.46

ceil和floor函数（取整）
ceil(n) 取大于等于数值n的最小整数
floor(n)取小于等于数值n的最大整数

select ceil(1.5) a from dual; 返回2
select ceil(-1.5) a from dual; 返回-1
select floor(1.5) a from dual; 返回1
select floor(-1.5) a from dual; 返回-2

trunc函数（截取）
用于截取时间或者数值，返回指定的值
1）截取数值
trunc(number,decimals)
number ：待做截取处理的数值
decimals ：指明需保留小数点后面的位数。可选项，忽略它或者为0则截去所有的小数部分，保留整数部分。
trunc就是处理数字的显示位数，如果decimals为负数，就去掉小数部分，处理整数部分，处理完为0，-1就是个位为0，-2就是十位和个位为0，如果超过了或等于整数部分长度，则整个数字为0；如果decimals为正数，就处理小数部分，截取n位小数，超过了小数位长度仍是原值。

select trunc(12.34) from dual;     12
select trunc(12.34,0) from dual;   12
select trunc(12.34,-1) from dual;  10
select trunc(12.34,-2) from dual;   0
select trunc(12.34,-3) from dual;   0
select trunc(12.34,1) from dual;  12.3
select trunc(12.34,2) from dual; 12.34
select trunc(12.34,3) from dual; 12.34

2）截取时间
trunc(date,format)
date：日期格式的值
format：日期格式 如’mm’，‘yyyy’等将date从指定日期格式截取（没有’ss’，trunc函数没有秒的精确）

format格式表
无参yyyyyymmdddhhmi
当前date的年月日当年第一天当年第一天当月第一天当前年月日当前星期的第一天（星期日）当前日期截取到小时,分和秒补00当前日期截取到分,秒补00
select trunc(sysdate) from dual;        2021/7/30
select trunc(sysdate,'yyyy') from dual; 2021/1/1
select trunc(sysdate,'yy') from dual;   2021/1/1
select trunc(sysdate,'mm') from dual;   2021/7/1
select trunc(sysdate,'dd') from dual;   2021/7/30
select trunc(sysdate,'d') from dual;    2021/7/25
select trunc(sysdate,'hh') from dual;   2021/7/30 13:00:00
select trunc(sysdate,'mi') from dual;   2021/7/30 13:50:00
select trunc(to_date('2018-02-01 1:00:00','YYYY-MM-DD HH:MI:SS')) from dual;         2018/2/1
select trunc(to_date('2018-02-01 1:00:00','YYYY-MM-DD HH:MI:SS'),'yyyy') from dual;  2018/1/1
select trunc(to_date('2018-02-01 1:00:00','YYYY-MM-DD HH:MI:SS'),'mm') from dual;    2018/2/1
select trunc(to_date('2018-02-01 1:00:00','YYYY-MM-DD HH:MI:SS'),'dd') from dual;    2018/2/1
select trunc(to_date('2018-02-01 1:00:00','YYYY-MM-DD HH:MI:SS'),'d') from dual;     2018/1/28
select trunc(to_date('2018-02-01 1:12:12','YYYY-MM-DD HH:MI:SS'),'hh') from dual;    2018/2/1 1:00:00
select trunc(to_date('2018-02-01 1:12:12','YYYY-MM-DD HH:MI:SS'),'mi') from dual;    2018/2/1 1:12:00

decode()：相当于if/else if语句
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　…
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
string:需要进行正则处理的字符串
pattern：进行匹配的正则表达式
position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0
occurrence：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组）
modifier：模式（‘i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’）针对的是正则表达式里字符大小写的匹配

1、select REGEXP_SUBSTR(‘11a22A33a’,’[^A]+’,1,1,‘i’) as str from dual;
结果：11
分析：正则表达式是以A为标识进行分割，而’i’标识不区分大小写，所以结果是11，而不是11a22
2、select REGEXP_SUBSTR(‘11a22A33a’,’[^A]+’,1,1,‘c’) as str from dual;
结果：11a22
分析：正则表达式是以A为标识进行分割，而’c’标识区分大小写，所以结果是11a22，而不是11

REGEXP_REPLACE(source, pattern, replace_string, occurrence)
● source: string类型，要替换的原始字符串。
● pattern: string类型常量，要匹配的正则模式，pattern为空串时抛异常。
● replace_string:string，将匹配的pattern替换成的字符串。
● occurrence: bigint类型常量，必须大于等于0，
大于0：表示将第几次匹配替换成replace_string，
等于0：表示替换掉所有的匹配子串。
其它类型或小于0抛异常。

select regexp_replace('01234abcde56789','[0-9]','#')FROM dual;			用'#'替换字符串中的所有数字
select regexp_replace('01234abcde56789','[09]','#')FROM dual;			用'#'替换字符串中的数字0和9
select regexp_replace('abcDEfg123456ABC','[a-z0-9]','',4) from dual;	遇到非小写字母或者数字跳过，从匹配到的第4个值开始替换，替换为''

Union：对两个结果集进行并集操作(合并结果)，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作(合并结果)，包括重复行，不进行排序；

select * from student union all
select * from student where stuSex='男';

在这里插入图片描述

select * from student union
select * from student where stuSex='男';

在这里插入图片描述

NVL函数:从两个表达式返回一个非 null 值
NVL(expression1, expression2)
如果 expression1 的计算结果为 null 值，则 NVL( ) 返回 expression2。如果 expression1 的计算结果不是 null 值，则返回 expression1。expression1 和 expression2 可以是任意一种数据类型。如果 expression1 与 expression2 的结果皆为 null 值，则 NVL( ) 返回 NULL。

LPAD:从左边对字符串使用指定的字符进行填充
lpad( string, padded_length, [ pad_string ] )
string：准备被填充的字符串
padded_length：填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符
pad_string：填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格

select lpad('Sibal',10,'abc') from dual;	"abcabSibal"
select lpad('Sibal',7,'abc') from dual;		"abSibal"
select lpad('Sibal',4,'abc') from dual;		"Siba"
select lpad('Sibal',10) from dual;			"     Sibal" 5个空格

to_date，to_char
to_date：将具有固定格式的字符串类型的数据转化为相对应的Date类型数据
to_char：将数值型或者日期型转化为字符型
使用方法
to_date(“需要转换的字符串”,“日期格式”) 前后要匹配
参数详解：日期格式
元素连接格式
元素使用“-”连接，如YYYY-MM-DD (2019-05-28)
元素使用“:”连接，如YYYY-MM-DD HH:MI:SS (2019-05-28 11:25:36)
元素使用“/”连接，如YYYY/MM/DD HH:MI:SS (2019/05/28 11:25:36)
组成元素（结合to_char使用）
YYYY：四位表示的年份
YYY，YY，Y：年份的最后三位、两位或一位
MM：01~12的月份编号
MONTH：九个字符表示的月份，右边用空格填补
MON：三位字符的月份缩写
D：星期中的第几天
DD：月份中的第几天
DDD：年份中的第几天
DAY：当周第几天全写，右边用空格补齐
DY：当周第几天缩写
W：当月中的第几周
WW：一年中的第几周
CC：世纪
Q：季度
HH，HH12：一天中的第几个小时，12进制表示法
HH24：一天中的第几个小时，取值为00~23
MI：一小时中的分钟
SS：一分钟中的秒
SSSS：从午夜开始过去的秒数

select sysdate,sysdate - interval '7' MINUTE from dual;		当前时间减去7分钟的时间 
select sysdate - interval '7' hour from dual; 				当前时间减去7小时的时间
select sysdate - interval '7' day from dual; 				当前时间减去7天的时间
select sysdate,sysdate - interval '7' month from dual;		当前时间减去7月的时间 
select sysdate,sysdate - interval '7' year from dual;		当前时间减去7年的时间
select sysdate,sysdate - 8*interval '2' hour from dual;		当前时间减去16小时的时间
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;		给出的时间是周几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;	给出的时间是周几
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;		两个日期间的天数

to_char(to_date('2021-07-30','yyyy-mm-dd'),'cc')	21	当前日期所在的世纪
to_char(to_date('2021-07-30','yyyy-mm-dd'),'yy')	21	当前日期所在的年份后两位
to_char(to_date('2021-07-30','yyyy-mm-dd'),'month')	7月	当前日期所在的月份
to_char(to_date('2021-07-30','yyyy-mm-dd'),'ddd')	211	当前日期所在的年份中第几天
to_char(to_date('2021-07-30','yyyy-mm-dd'),'ww')	31	当前日期所在的年份中第几周
to_char(to_date('2021-07-30 16:12:15','yyyy-mm-dd hh24:mi:ss'),'HH24')	16 当前时间所在天中一天的第几个小时

dbms_lob.substr(clob_column)
● clob_column: clob类型
针对ORA-00932: inconsistent datatypes: expected - got CLOB
进行clob转字符串操作

select id,dbms_lob.substr(attributes) from ecas_message where rownum <= 8;

MINUS
●A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录。即差集B-A，A minus B，对于集合A、B，即集合{x∣x∈A,且x∉B}。
●minus是按列进行比较的，所以A能够minus B的前提条件是结果集A和结果集B需要有相同的列数，且相同列索引的列具有相同的数据类型。
●oracle会对minus后的结果集进行去重，即如果A中原本多条相同的记录数在进行A minus B后将会只剩一条对应的记录。

select id,dbms_lob.substr(attributes) from ecas_message where rownum <= 8 
minus 
select id,dbms_lob.substr(attributes) from ecas_message where rownum < 2;

INTERSECT
●和 UNION 指令类似，但UNION是并集，A∪B，INTERSECT是交集，A∩B。
●使用intersect的限制条件如下：1、所有查询中的列数和列的顺序必须相同。2、比较的两个查询结果集中的列数据类型可以不同但必须兼容。3、比较的两个查询结果集中不能包含不可比较的数据类型(xml、text、ntext、image 或非二进制 CLR 用户定义类型)的列。4、返回的结果集的列名与操作数左侧的查询返回的列名相同。ORDER BY 子句中的列名或别名必须引用左侧查询返回的列名。5、不能与 COMPUTE 和 COMPUTE BY 子句一起使用。6、通过比较行来确定非重复值时，两个 NULL 值被视为相等。

select id,content from (select id,content,rownum as num from ecas_message) where num >= 2 
intersect 
select id,content from ecas_message where rownum <= 8;

rownum
●分页查询时使用，例如返回表中的前n条记录或者是中间的几条记录
●rownum需要注意的问题
[1] rownum不支持以下方式的查询
a: select * from area where rownum > 2;
b: select * from area where rownum = n; --where n is a integer number lager than 1
注：rownum只支持select * from area where rownum =1的查询

因为rownum是根据查询的结果集来对记录进行编号，所以当你查询rownum大于2的记录时会得到一个空的结果集。因为当oracle查询得到第1条记录时，发现rownum为1不满足条件，然后就继续查询第2条记录，但此时第2条记录又被编号为1（也即rownum变为1），所以查询得到的始终是rownum=1，因此无法满足约束，最终查询的结果集为空。
所以rownum不支持使用>,>=,=(除了1),between...and,不会提示语法错误,但查询不出数据
[2] rownum的排序查询问题
Rownum的排序查询是根据表中数据的初始顺序来进行的。
例如：select * from area where rownum <= 8 order by district;只对area表中的前8条记录进行排序。那么，如果我要取表中的前8条记录并且要求是全表有序，那怎么办呢？还是老办法，使用子查询。我们可以使用以下语句得到：

select * from (select * from area order by district) where rownum <= 8;

Mr.KnightGb
关注
oracle游标cursor用ibatis获取
zhaoyf7746的博客
06-14 872
oracle游标是个非常强大的功能，之前曾经用来获取数据，却发现返回的数据（ResultSet）没法处理，现在看到一个老项目使用，于是给自己建一个demo。<select id="getBuyInfoListForProduct" parameterClass="int" resultMap="buyProductResult"> SELECT t1.*, CURSOR
Ibatis向oracle库中批量插入数据
etxiyao的博客
10-26 611
使用Ibatis向oracle库中批量插入数据foreach不能用选用iterate标签 foreach不能用 首先我想到的是foreach标签，但是在xml写出来后，一直提示报错（foreach未被定义）。我确认我写的语法上没什么问题，有可能是我所用ibatis的版本不支持foreach标签。 选用iterate标签 以下是经过验证的代码： 下面展示一些 内联代码片。 <insert id="methodName" parameterClass="java.util.HashMap">
oracle ibatis 配置
03-25
oracle ibatis 配置
iBatis入门
王旭东
01-01 167
iBatis 简介： iBatis 是apache 的一个开源项目，一个O/R Mapping 解决方案，iBatis 最大的特点就是小巧，上手很快。如果不需要太多复杂的功能，iBatis 是能够满足你的要求又足够灵活的最简单的解决方案，现在的iBatis 已经改名为Mybatis 了。 官网为：http://www.mybatis.org/ ...
IBATIS+ORACLE(一)
weixin_34368949的博客
06-01 208
  迁移时间：2017年6月1日15:55:17 Author:Marydon 　　（四）IBATIS + ORACLE 　　　　第一部分：基础篇 　　　　1.4.1.1 分页SQL　　 &lt;!-- 开头 --&gt; &lt;isNotNull prepend="" property="end"&gt; SELECT * FROM ( &lt;/isNotN...
[oracle] oracle-ibatis-整理
weixin_34206899的博客
06-09 101
① &lt;!-- 复用sql代码 --&gt; &lt;sql id="CUSTOM_CABINET_INFO.QUERY_CABINET"&gt; &lt;dynamic prepend="AND"&gt; &lt;isNotNull prepend="AND" property="requestObject.cabinetNo"&gt; ec
oracle ibatis操作
dnqxxovm976303的博客
05-19 105
1.oracle中删除id为空的某一表test中的数据 delete from test t where t.id is null; 2.oracle中字符串转日期 to_date('2017-05-19 11:32:45','yyyy-mm-dd hh24:mi:ss') 3.oracle中系统时间 sysdate 4.ibatis在插入记录的时候，id自增 ...
掌握ibatis调用oracle函数与存储过程技巧
### iBatis调用Oracle函数与存储过程的必要性 在进行Java数据库交互时，iBatis能够极大地简化开发者的工作。通过iBatis，可以将原始SQL语句映射到对应的Java方法上。当需要从数据库获取数据或者对数据进行更新时，...
ibatis调用oracle的函数，存储过程的方法 IN 和OUT /游标
04-08
在iBatis中，你可以创建一个Java方法来映射这个函数，方法的返回类型应与Oracle函数的返回类型匹配。在SQL映射文件中，使用`<select>`标签定义这个函数调用，并在`resultType`属性中指定返回类型。 2. **处理IN参数...
ibatis调用oracle的函数，存储过程的方法
05-17
在Ibatis中调用Oracle函数和存储过程，你需要以下步骤： 1. **配置映射文件（Mapper XML）**： - 创建一个XML映射文件，定义SQL语句。对于函数，使用`<select>`标签；对于存储过程，使用`<procedure>`标签。 - 在...
iBATIS操作Oracle CLOB数据
09-15
在这里，`empty_clob()`函数用于创建一个空的CLOB对象，然后通过`RETURNING`子句返回，以便于iBATIS能够填充到Java对象中。 3. **更新操作**：更新CLOB字段时，通常需要先获取原始的CLOB对象，然后用新的值覆盖。...
Ibatis+Oracle（含对应数据库sql) 源码
11-02
本资源适合刚刚接触ibatis+oracle的小伙伴使用，欢迎交流，吐槽。
ibatis的dynamicSQL中，关于prepend的使用
02-14
ibatis的dynamicSQL中，关于prepend的使用
bean的属性类型----ibatis类型-------oracle数据库类型
qingfeng_825的专栏
10-11 254
1、 ibatis中会通过resultMap把bean的类型与oralce的列对应起来，如下面的例子：   &lt;resultMap id="userMap" class="userBean" &gt; &lt;result column="ID" property="id" /&gt; &lt;result column="CIP_NAME" pr
oracle日期类型
u012427018的博客
12-21 857
查询慢的问题，经过验证和查询，已经初步得出结论，发现我们查询所用的时间字段传输的java.util.Date,在ibatis里通过jdbcType方式赋值#endTime:TIMESTAMP#， 而数据内字段的类型是DATE，在数据库内做类型转换会很耗时，Oracle支持Date和TIMESTAMP两种时间类型，而我们数据库设计都是Date类型但是itabits里使用的都是TIMESTAMP.
ibatis中得resultMap得property属性值得大小写
weixin_44434274的博客
07-10 1266
ibatis中得resultMap得property属性值得大小写总结准备一个实体类和一段sql实验一：改变实体类的大小写实验二：改变sql的resultMap的property属性值得大小写结论插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 总结 今天在公司写代码过程中，一不小心把sql
如果Oracle数据库中的字段大小超过4000，可以考虑使用CLOB或BLOB类型来存储文本或二进制数据。
编程菜鸟的博客
05-06 3483
clob类型和blob类型的使用
达梦数据库+mybatis返回字段转小写
zhSpace的博客
09-02 4176
达梦数据库+mybatis返回字段转小写
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值