oracle函数
PL/SQL单行函数和组函数详解
函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类：
单行函数
组函数
本文将讨论如何利用单行函数以及使用规则。
SQL中的单行函数
SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename)
Like 'AL%'ORDER BY SOUNDEX(ename)
单行函数也可以在其他语句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。
NULL和单行函数
在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。
下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿
column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table
datatype number number numberlength 11.2 11.2
不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子：
update empset salary=(salary+bonus)*1.1
这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。
所以正确的语句是：
update empset salary=(salary+nvl(bonus,0)*1.1
单行字符串函数
单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串
ASCII()
c1是一字符串，返回c1第一个字母的ASCII码，他的逆函数是CHR()
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122
CHR(＜i＞)[NCHAR_CS]
i是一个数字，函数返回十进制表示的字符
select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B
CONCAT(,)
c1,c2均为字符串，函数将c2连接到c1的后面，如果c1为null,将返回c2.如果c2为null,则返回c1，如果c1、c2都为null，则返回null。他和操作符||返回的结果相同
select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda
INITCAP()
c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格，控制字符，标点符号限制。
select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici
INSTR(,[,＜i＞[,]])
c1,c2均为字符串，i,j为整数。函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的缺省值为1.
select INSTR('Mississippi','i',3,3) from
dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3)
from dualINSTR('MISSISSIPPI','I',3,3)2
INSTRB(,[,i[,j])
与INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()
LENGTH()
c1为字符串，返回c1的长度，如果c1为null，那么将返回null值。
select LENGTH('Ipso Facto') ergo from dualergo10
LENGTHb()
与LENGTH()一样，返回字节。
lower()
返回c的小写字符，经常出现在where子串中
select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE
'%white%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格，参见RPAD。
select LPAD(answer,7,'') padded,answer unpadded from questionADDED
UNPADDED Yes YesNO NOMaybe maybe
LTRIM(,)
把c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，那么c1就不会改变。
select LTRIM('Mississippi','Mis') from dualLTRppi
RPAD(,＜i＞[,])
在c1的右侧用c2字符串补足致长度i,可多次重复，如果i小于c1的长度，那么只返回i那么长的c1字符，其他的将被截去。c2的缺省值为单空格,其他与LPAD相似
RTRIM(,)
把c1中最右边的字符去掉，使其第后一个字符不在c2中，如果没有c2，那么c1就不会改变。
REPLACE(,[,])
c1,c2,c3都是字符串，函数用c3代替出现在c1中的c2后返回。
select REPLACE('uptown','up','down') from dualREPLACEdowntown
STBSTR(,＜i＞[,])
c1为一字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。
select SUBSTR('Message',1,4) from dualSUBSMess
SUBSTRB(,＜i＞[,])
与SUBSTR大致相同，只是I,J是以字节计算。
SOUNDEX()
返回与c1发音相似的词
select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from
dualDawes Daws DawsonD200 D200 D250
TRANSLATE(,,)
将c1中与c2相同的字符以c3代替
select TRANSLATE('fumble','uf','ar') test from dualTEXTramble
TRIM([[]] from c3)
将c3串中的第一个，最后一个，或者都删除。
select TRIM(' space padded ') trim from dual TRIMspace padded
UPPER()
返回c1的大写，常出现where子串中
select name from dual where UPPER(name) LIKE 'KI%'NAMEKING
单行数字函数
单行数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，oracle没有提供内建的弧度和角度的转换函数。
ABS()
返回n的绝对值
ACOS()
反余玄函数，返回-1到1之间的数。n表示弧度
select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3.14159265 0
ASIN()
反正玄函数，返回-1到1，n表示弧度
ATAN()
反正切函数，返回n的反正切值，n表示弧度。
CEIL()
返回大于或等于n的最小整数。
COS()
返回n的余玄值，n为弧度
COSH()
返回n的双曲余玄值，n 为数字。
select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847
EXP()
返回e的n次幂，e=2.71828183.
FLOOR()
返回小于等于N的最大整数。
LN()
返回N的自然对数，N必须大于0
Oracle中的substr（）函数详解及应用
Oracle中的substr（）函数详解及应⽤
1、substr函数格式 (俗称：字符截取函数)
格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;
解析：
格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第⼀位开始截取）
3、b 要截取的字符串的长度
格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后⾯所有的字符串。
2、实例解析
格式1：
1、select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
2、select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
3、select substr('HelloWorld',2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
4、select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最⼤数量返回。
5、select substr('HelloWorld',5,3) value from dual; //返回结果：oWo
6、select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算⼀个字符串，结果是：o空格W)
7、select substr('HelloWorld',-1,3) value from dual; //返回结果：d （从后⾯倒数第⼀位开始往后取1个字符，⽽不是3个。原因：下⾯红⾊ 第三个注解）
oraclecast函数用法
oraclecast函数用法
Oracle的CAST函数是用来将一个表达式转换为指定的数据类型。它可以在SELECT、INSERT、UPDATE和DELETE语句中使用。语法如下：
```
CAST (expression AS data_type)
```
expression是需要转换的表达式，data_type是转换的目标数据类型。
Oracle中支持的数据类型包括数字类型、字符类型、日期类型等。下面详细介绍一些常见的使用情况。
1.数字类型转换：
-将字符类型转换为数字类型：
```
SELECTCAST('123'ASNUMBER)FROMDUAL;
```
结果为：123
-将日期类型转换为数字类型：
```
SELECTCAST(SYSDATEASNUMBER)FROMDUAL;
``` 结果为：转换为对应的数值类型，时间戳在Oracle中是以1970年1月1日午夜（GMT）以来的秒数表示的。
2.字符类型转换：
-将数字类型转换为字符类型：
```
SELECTCAST(123ASVARCHAR2(10))FROMDUAL;
```
结果为：'123'
-将日期类型转换为字符类型：
```
SELECTCAST(SYSDATEASVARCHAR2(10))FROMDUAL;
```
结果为：转换为指定格式的字符串，根据当前NLS_DATE_FORMAT的设置来确定日期格式。
3.日期类型转换：
-将字符类型转换为日期类型：
```
SELECTCAST('2024-01-01'ASDATE)FROMDUAL;
``` 结果为：2024-01-01
-将数字类型转换为日期类型：
```
```
结果为：2024-01-01
4.其他类型转换：
-将字符串类型转换为CLOB类型：
```
SELECT CAST('Hello' AS CLOB) FROM DUAL;
```
-将BLOB类型转换为RAW类型：
oracle位运算函数
oracle位运算函数
摘要：
1.位运算概述
2.Oracle位运算函数介绍
3.常见位运算函数的使用示例
4.位运算在实际场景中的应用
5.总结与建议
正文：
1.位运算概述
位运算是一种直接操作二进制数据的运算方式，广泛应用于计算机科学领域。在数据库管理系统（如Oracle）中，位运算同样具有重要应用。本文将重点介绍Oracle中的位运算函数，并通过实例演示其用法。
2.Oracle位运算函数介绍
Oracle提供了丰富的位运算函数，包括以下几类：
（1）位与运算：&
（2）位或运算：|
（3）位异或运算：^
（4）位非运算：~
（5）位左移运算：<<
（6）位右移运算：>>
（7）位翻转运算：ROL（左循环位移）和ROR（右循环位移） 3.常见位运算函数的使用示例
以下以位与运算（&）和位左移运算（<<）为例，演示如何在Oracle中使用位运算函数：
（1）位与运算
SELECT binary_value1 & binary_value2 FROM table_name;
（2）位左移运算
SELECT binary_value << 1 FROM table_name;
4.位运算在实际场景中的应用
位运算在实际场景中具有广泛的应用，如：
（1）数据加密：通过位运算实现对敏感数据的加密，提高数据安全性。
（2）数据压缩：利用位运算实现数据压缩，减少存储空间和传输带宽。
（3）位字段操作：在数据库中处理位字段数据，如设置标志位、更新位状态等。
5.总结与建议
掌握Oracle位运算函数对于开发者具有重要意义。在实际开发过程中，灵活运用位运算可以有效提高代码效率，降低系统资源消耗。
oracle value函数的用法
oracle value函数的用法
Oracle数据库是一种常用的关系型数据库管理系统，它提供了多种函数用于数据查询和处理。其中一个常用的函数是VALUE函数，它可以将任意数据类型的值转换为另一种数据类型。
VALUE函数的语法如下：
VALUE(expression, datatype)
其中，expression表示要转换的值，datatype表示要转换成的数据类型。
VALUE函数可以用于以下几个方面：
1. 数据类型转换：VALUE函数可以将一个数据类型转换为另一个数据类型。例如，可以将一个字符串转换为数字，或者将一个日期转换为字符串。这在处理数据时非常有用，可以确保数据类型的一致性，避免出现错误。
2. 数据格式化：VALUE函数可以将一个值以指定的格式进行格式化。例如，可以将一个数字转换为货币格式，或者将一个日期转换为指定的日期格式。这在数据展示和报表生成时非常有用，可以使数据更易读和理解。
3. 数据截取：VALUE函数可以从一个字符串中截取指定的子字符串。例如，可以从一个完整的地址中截取出省份、城市和街道等信息。这在处理复杂的字符串时非常有用，可以提取出需要的数据，并进行进一步处理。
4. 数据合并：VALUE函数可以将多个值合并成一个字符串。例如，可以将姓名、性别和年龄合并成一个完整的个人信息字符串。这在数据拼接和生成动态SQL语句时非常有用，可以减少代码的编写量。
VALUE函数的使用示例：
1. 将字符串转换为数字：
SELECT VALUE('123', 'NUMBER') FROM DUAL;
结果为：123
2. 将日期转换为字符串：
SELECT VALUE(SYSDATE, 'VARCHAR2(20)') FROM DUAL;
结果为：2022-03-25 12:34:56
3. 截取字符串：
SELECT VALUE('北京市朝阳区', 'SUBSTR(ADDRESS, 1, 2)') FROM
oracle date函数用法
oracle date函数用法
Oracle DATE函数是用于将一个字符串转换为日期格式的函数。它有多种用法，以下是其中的几种：
1. TO_DATE函数：将字符串转换为日期格式
TO_DATE函数的语法如下：
TO_DATE(string, format)
其中，string是要转换的字符串，format是转换格式，它告诉Oracle如何将字符串转换为日期格式。例如，如果字符串是'2022-01-01'，format应该是'YYYY-MM-DD'。
示例：
SELECT TO_DATE('2022-01-01', 'YYYY-MM-DD') FROM DUAL;
结果为：01-JAN-22
2. SYSDATE函数：返回当前日期和时间
SYSDATE函数用于获取当前日期和时间。它没有任何参数。
示例：
SELECT SYSDATE FROM DUAL;
结果为：21-JUL-21 02.14.45.000000 PM
3. ADD_MONTHS函数：将月份加上指定的数量
ADD_MONTHS函数用于将一个日期的月份加上指定的数量。它的语法如下：
ADD_MONTHS(date, n)
其中，date是要处理的日期，n是要加上的月份数量。
示例：
SELECT ADD_MONTHS(TO_DATE('2022-01-01', 'YYYY-MM-DD'), 6)
FROM DUAL;
结果为：01-JUL-22
以上是Oracle DATE函数的常用用法，它们可以帮助您处理日期和时间相关的计算和操作。
oracle 数值比较函数
oracle 数值比较函数
Oracle数据库中的数值比较函数可以帮助我们方便地进行数值之间的比较，从而实现对数据的有效处理。以下将介绍一些常用的Oracle数值比较函数及其应用实例。
1.Oracle数值比较函数概述
在Oracle中，数值比较函数主要用于对两个数值进行比较，返回比较结果。这些函数可以应用于WHERE子句、HAVING子句等，以便在查询过程中对数据进行筛选。
2.常用的Oracle数值比较函数
- GREATEST（最大值）：比较两个数值，返回较大值。
- LEAST（最小值）：比较两个数值，返回较小值。
- ABS（绝对值）：计算数值的绝对值。
- ROUND（四舍五入）：对数值进行四舍五入处理。
- TRUNC（截断）：截断数值的小数部分。
- MOD（取模）：计算两个数值的余数。
3.函数实例及应用
实例1：查询员工工资高于10000的记录
```sql
SELECT * FROM employee
WHERE salary > 10000;
``` 实例2：查询订单金额小于100元的记录
```sql
SELECT * FROM order
WHERE amount < 100;
```
实例3：计算员工年龄的平方根
```sql
SELECT sqrt(age) FROM employee;
```
4.注意事项
在使用Oracle数值比较函数时，需要注意以下几点：
- 确保比较的数值类型相同，否则可能导致比较结果错误。
- 结合实际情况选择合适的比较函数，以提高查询效率。
- 在使用比较函数时，注意查询性能，避免过度使用导致系统负载过高。
总之，Oracle数据库中的数值比较函数为我们提供了丰富的比较方式，能够满足各种场景的需求。
oraclelag函数用法
oraclelag函数用法
Lag函数是Oracle数据库中的一个分析函数，用于获取指定列的前一个记录的值。Lag函数可以在查询结果中返回前一行的值，以便与当前行的值进行比较和计算。Lag函数的一般语法如下：
LAG (column, offset, default)
OVER (PARTITION BY partition_col ORDER BY order_col)
其中，column是指定的列名，offset是偏移量，表示要返回的前一行的偏移量，默认为1，即返回前一行的值。default是可选的默认值，用于指定当无前一行时返回的值。PARTITION BY子句是可选的，用于对查询结果进行分组，类似于GROUP BY子句。ORDER BY子句用于按照指定的列对查询结果进行排序。
Lag函数的使用场景非常多，可以用于计算各种数据间的差异，分析序列数据的变化趋势，或者在查询结果中获取前一行的值等。下面将通过一些示例来详细说明Lag函数的用法。
1.计算当前行与前一行的差值
假设有一个订单表，其中包含订单号、订单金额和订单日期等列。我们可以使用Lag函数计算当前订单金额与前一订单金额的差值，以便分析订单金额的变化情况。
SELECT order_number, order_amount,
order_amount - LAG(order_amount, 1, 0) OVER (ORDER BY
order_number) AS diff
FROM order_table; 在以上示例中，LAG函数用于获取前一行的订单金额，然后与当前行的订单金额进行相减，得到订单金额的变化量。第一个参数为订单金额列，第二个参数为偏移量1表示获取前一行的值，第三个参数为默认值0，表示当无前一行时返回0。
2.获取前一行的值
Lag函数还可以用于在查询结果中获取前一行的值，以便与当前行的值进行比较和计算。
SELECT id, value, LAG(value, 1, 0) OVER (ORDER BY id) AS
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）
Oracle分析函数-排序排列（rank、dense_rank、row_number、
ntile）
(1)rank函数返回⼀个唯⼀的值，除⾮遇到相同的数据时，此时所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录
的排名之间空出排名。
(2)dense_rank函数返回⼀个唯⼀的值，除⾮当碰到相同数据时，此时所有相同数据的排名都是⼀样的。
(3)row_number函数返回⼀个唯⼀的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
(4)ntile是要把查询得到的结果平均分为⼏组，如果不平均则分给第⼀组。
例如：
create table s_score
( s_id number(6)
,score number(4,2)
);
insert into s_score values(001,98);
insert into s_score values(002,66.5);
insert into s_score values(003,99);
insert into s_score values(004,98);
insert into s_score values(005,98);
insert into s_score values(006,80);
select
s_id
,score
,rank() over(order by score desc) rank --按照成绩排名，纯排名
,dense_rank() over(order by score desc) dense_rank --按照成绩排名，相同成绩排名⼀致
,row_number() over(order by score desc) row_number --按照成绩依次排名
,ntile(3) over (order by score desc) group_s --按照分数划分成绩梯队
from s_score;
排名/排序的时候，有时候，我们会想到利⽤伪列row_num，利⽤row_num确实可以解决某些场景下的问题(但是相对也⽐较复杂)，⽽且有些
oracle中decode函数用法
oracle中decode函数用法
详细用法讲解了Oracle中decode函数的用法，对大家的学习或者工作具有一定的参考学习价值。
1.decode函数的两种用法
第一种用法
含义解释：
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
第二种用法
decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
sign（）函数
解释：
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
用法示例：
select id,sign(id-2) from t_decode;
截图效果：
lpad（）函数
在字段id前边补字段0 长度为2 select lpad(id,2,0) from t_decode；
2.decode函数的常规用法示例
a.准备测试数据
创建测试表t_decode，并插入测试数据
//创建表，插入数据，查询
create table t_decode(
id integer,
name varchar2(10)
);
//插入数据
insert into t_decode values (1,'a');
insert into t_decode values (2,'b');
insert into t_decode values (3,'c');
insert into t_decode values (4,'a');
oracle 的table函数
oracle 的table函数
Oracle中的TABLE函数是用于将嵌套表（nested table）或者VARRAY转换为普通表的函数。嵌套表是一种特殊的列类型，它允许在单个列中存储多个行值，而VARRAY是一种可以存储固定数量的元素的数组类型。
当我们使用TABLE函数时，它会将嵌套表或VARRAY中的元素展开为普通的行和列，这样我们就可以对这些元素进行查询和操作。这个功能在处理嵌套表和VARRAY数据时非常有用，因为它允许我们像操作普通表一样来处理这些复杂的数据类型。
在使用TABLE函数时，我们需要使用关键字TABLE来引用嵌套表或VARRAY，并在其后紧跟着实际的列名或表达式。例如，我们可以这样使用TABLE函数来查询嵌套表中的数据：
SELECT.
FROM TABLE(nested_table_column);
这将会把嵌套表中的数据展开为普通表，然后我们就可以对其进行普通的SELECT、INSERT、UPDATE和DELETE操作了。
另外，TABLE函数也可以和LATERAL关键字一起使用，以便在查询中引用前面的列。这在需要对嵌套表中的数据进行进一步处理或者与其他表进行关联时非常有用。
总之，Oracle中的TABLE函数是一个非常有用的工具，它允许我们以普通表的方式来处理嵌套表和VARRAY数据，为我们在处理复杂数据类型时提供了便利。