本文共 4392 字，大约阅读时间需要 14 分钟。

Oracle 数据库常用函数与操作指南

转换函数

Oracle 数据库中的转换函数用于将数据从一种数据类型转换为另一种类型。主要包括 TO_CHAR、TO_DATE 和 TO_NUMBER 三个函数。

TO_CHAR()

TO_CHAR 函数用于将字符、日期或数字转换为字符类型。其功能包括：

转换字符：将 nchar、nvarchar2、clob、nclob 等类型转换为 char 类型。

• 示例：SELECT TO_CHAR('AABBCC') FROM dual;

转换时间：将指定日期按照指定格式转换为 varchar2 类型。

• 示例：SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

转换数值：将指定数值按照指定格式转换为字符类型。

• 示例：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM dual;

TO_DATE()

TO_DATE 函数用于将字符转换为日期类型。其功能包括：

• 将 char、nchar、varchar2、nvarchar2 等类型转换为日期类型。
• 如果指定格式 fmt，则按照指定格式进行转换。fmt 为 'J' 表示使用公元制 Julian day。
• 示例：SELECT TO_DATE(2454336, 'J') FROM dual;
• 示例：SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM dual;

TO_NUMBER()

TO_NUMBER 函数用于将字符转换为数值类型。其功能包括：

• 将 char、nchar、varchar2、nvarchar2 等类型按照指定格式转换为数值类型。
• 示例：SELECT TO_NUMBER('-100.00', '9G999D99') FROM dual;

---

聚合函数

Oracle 的聚合函数用于对多行数据进行操作，常见的聚合函数包括 AVG、SUM、COUNT、MAX 和 MIN。

AVG()

AVG(n) 用于计算记录集中 n 列的平均值。

• 示例：SELECT AVG(col2) FROM temp;
• 示例：SELECT col1, AVG(col2) FROM temp GROUP BY col1;

SUM()

SUM(n) 用于计算记录集中 n 列的总和。

• 示例：SELECT SUM(col2) FROM temp;
• 示例：SELECT col1, SUM(col2) FROM temp GROUP BY col1;

COUNT()

COUNT(n) 用于统计记录集中指定列的记录数。

• 示例：SELECT COUNT(*) FROM temp;
• 示例：SELECT col1, COUNT(*) FROM temp GROUP BY col1;

MAX() 和 MIN()

MAX(n) 和 MIN(n) 分别用于获取记录集中 n 列的最大值和最小值。

• 示例：SELECT MAX(col2) FROM temp;
• 示例：SELECT col1, MAX(col2) FROM temp GROUP BY col1;
• 示例：SELECT MIN(col2) FROM temp;
• 示例：SELECT col1, MIN(col2) FROM temp GROUP BY col1;

---

日期函数

Oracle 提供多种日期函数用于操作日期数据，常见函数包括 SYSDATE、LAST_DAY、ADD_MONTHS、MONTHS_BETWEEN、NEXT_DAY、ROUND 等。

SYSDATE

获取当前日期和时间。

• 示例：SELECT SYSDATE FROM dual;

LAST_DAY()

返回指定日期所在月份的最后一天。

• 示例：SELECT LAST_DAY(TO_DATE('2011-2-1', 'YYYY-MM-DD')) FROM dual;

ADD_MONTHS()

返回指定日期前或之后的月份。

• 示例：SELECT add_months(sysdate,6) FROM dual;

MONTHS_BETWEEN()

返回两个日期间的月份数。

• 示例：SELECT MONTHS_BETWEEN('04-11月-05', '11-1月-01') FROM dual;

NEXT_DAY()

返回指定日期后的指定星期几或天数。

• 示例：SELECT NEXT_DAY(SYSDATE, '星期一') FROM dual;

ROUND()

按指定格式对日期进行四舍五入。

• 示例：SELECT ROUND(TO_DATE('13-2月-03'), 'YEAR') FROM dual;

---

字符函数

Oracle 提供丰富的字符函数用于对字符串进行操作，常见函数包括 CHR、LOWER、UPPER、CONCAT、LTRIM、RTRIM、REPLACE、INITCAP、INSTR、LENGTH 等。

CHR()

返回字符集合中与指定代码等价的字符。

• 示例：SELECT CHR(97) FROM dual;

LOWER() 和 UPPER()

将字符串转换为小写或大写。

• 示例：SELECT LOWER('A') FROM dual;
• 示例：SELECT UPPER('字') FROM dual;

CONCAT()

将两个字符串连接。

• 示例：SELECT CONCAT('Hello', 'world') FROM dual;

LTRIM() 和 RTRIM()

去除字符串的前后空格。

• 示例：SELECT LTRIM('ABA', 'A') FROM dual;
• 示例：SELECT RTRIM('ABA', 'A') FROM dual;

REPLACE()

替换字符串中的子字符串。

• 示例：SELECT REPLACE('Hello', 'H', 'h') FROM dual;

INITCAP()

将字符串的第一个字母大写，其余字母小写。

• 示例：SELECT INITCAP('hello') FROM dual;

INSTR()

查找字符串中指定子字符串的位置。

• 示例：SELECT INSTR('ABACAAA', 'A', 2, 2) FROM dual;

LENGTH()

返回字符串的长度。

• 示例：SELECT LENGTH('Hello') FROM dual;

---

数字函数

Oracle 提供多种数字函数用于对数值数据进行运算，包括 ABS、ACOS、COS、SIN、ASIN、EXP、FLOOR、MOD、POWER、ROUND、TRUNC、SIGN、SQRT 等。

ABS()

返回数值的绝对值。

• 示例：SELECT ABS(-13) FROM dual;

EXP()

返回 e 的 n 次方。

• 示例：SELECT exp(6) FROM dual;

FLOOR()

返回小于等于数值的最大整数。

• 示例：SELECT FLOOR(9.6) FROM dual;

MOD()

返回余数。

• 示例：SELECT MOD(9,6) FROM dual;

ROUND()

执行四舍五入运算。

• 示例：SELECT ROUND(2.936, 2) FROM dual;

TRUNC()

截取数值的小数部分。

• 示例：SELECT TRUNC(2.936, 2) FROM dual;

SIGN()

检测数值的正负。

• 示例：SELECT SIGN(6.698), SIGN(-9), SIGN(0) FROM dual;

SQRT()

返回数值的平方根。

• 示例：SELECT SQRT(4) FROM dual;

---

数据表操作

插入数据

Insert into 表名 (列名) Values ('插入的数据')

更新数据

Update 表名 set 列名 = '数据' where 条件

数据排列

Order by 列名 [asc|desc]

去除重复关键字

Select distinct 列名 from 表名

删除记录

Delete from 表名 where 条件

---

高级查询

比较运算符

• =
• !=
• <
• >
• <=
• >=
• BETWEEN ... AND
• IN (列表)
• LIKE (匹配字符模式)
• IS NULL (检查是否为空)

逻辑操作符

• AND
• OR
• NOT

涉及子查询的示例

Select * from 表名 where 条件子查询

涉及连接的示例

• 内连接：WHERE column1 = column2
• 不等值连接：WHERE column1 != column2
• 自然连接：WHERE column1 = column2（Oracle 自动匹配）
• 外连接：WHERE column1 = column2（左外或右外连接）
• 自连接：WHERE column1 = column2
• 交叉连接：FROM 表1, 表2 WHERE 条件

---

PL/SQL 编程

函数类型

• 单行函数：直接返回单个值。
• 聚合函数：对多行数据进行操作。
• 分析函数：详见 Oracle 文档。

虚拟表 dual

• 示例：SELECT col1, col2 FROM dual;

截取函数 TRUNC

• 示例：SELECT TRUNC(col1, 5) FROM dual;

四舍五入函数 ROUND

• 示例：SELECT ROUND(col1, 2) FROM dual;

---

数据库备份与恢复

数据泵技术

• 使用 EXP 和 IMP 导出和导入数据。
• 示例：
  • 导出：exp file_name=‘test.exp’ owner=scott
  • 导入：imp file_name=‘test.exp’ owner=scott

游标及事务处理

• 游标分类：
  • 静态游标：隐式或显式。
  • 动态游标：强类型或弱类型。
• 游标属性：%FOUND、NOT FOUND。
• 事务控制语句：
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
  • SET TRANSACTION

---

子程序和触发器

• 存储过程：使用 CREATE PROCEDURE 定义。
• 函数：使用 CREATE FUNCTION 定义。
• 触发器：使用 CREATE TRIGGER 定义。

---

Oracle 创建序列

创建序列

CREATE SEQUENCE 序列名INCREMENT BY 1START WITH 1MAXVALUE 999999999

获取序列值

SELECT seq_newsid.nextval FROM sys.dual;

删除序列

DROP SEQUENCE 序列名

转载地址：http://sxtoz.baihongyu.com/