oracle函数,序列与分页代码实现及相关练习题

### Oracle函数、序列与分页代码实现及相关知识点详解 #### 一、Oracle序列 在Oracle数据库中，**序列**是一种非常实用的对象，主要用于自动产生唯一编号。它可以在表中插入新记录时，自动生成唯一的键值。序列可以是递增或递减的，并且可以指定最小值、最大值、初始值以及步长等参数。 ##### 创建序列的基本语法： ```sql CREATE SEQUENCE 序列名 MINVALUE 最小值 MAXVALUE 最大值 START WITH 初始值 INCREMENT BY 步长 NOCYCLE | CYCLE CACHE 缓存大小 | NOCACHE; ``` - **MINVALUE** 和 **MAXVALUE**：分别指定序列的最小值和最大值，默认情况下没有限制。 - **START WITH**：指定序列的初始值，默认为1。 - **INCREMENT BY**：指定序列每次增加的步长，默认为1。 - **CYCLE** / **NOCYCLE**：指定序列达到最大值或最小值后是否循环。如果选择 **CYCLE**，则到达边界后会重新回到最小值或最大值；如果选择 **NOCYCLE**，则会报错。 - **CACHE** / **NOCACHE**：**CACHE** 表示序列的值被预先分配到内存中，以提高性能；而 **NOCACHE** 则表示每次调用序列时都会实时生成下一个值。 ##### 示例 以下是一个创建序列的示例： ```sql CREATE SEQUENCE seq_dept_deptno START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE; ``` 使用序列时，可以通过 `.nextval` 方法获取序列的下一个值： ```sql INSERT INTO dept (depid, depname) VALUES (seq_dept_deptno.NEXTVAL, '其他部 12'); ``` #### 二、Oracle分页查询 在Oracle中进行分页查询时，通常使用 `ROWNUM` 函数来实现。`ROWNUM` 是Oracle提供的一个伪列，用于返回行的顺序号。分页查询的基本思路是先通过子查询获取行号，然后根据行号进行筛选。 ##### 基本分页查询语法： ```sql SELECT * FROM ( SELECT A.*, ROWNUM rn FROM ( SELECT * FROM 表名 ) A WHERE ROWNUM <= 当前页 * 每页显示条数 ) WHERE rn > (当前页 - 1) * 每页显示条数; ``` ##### 示例 假设我们需要每页显示2条记录，那么对于第一页（第1页）的SQL语句如下： ```sql SELECT * FROM ( SELECT A.*, ROWNUM rn FROM ( SELECT * FROM emp ) A WHERE ROWNUM <= 2 ) WHERE rn > 0; ``` 对于第二页（第2页）的SQL语句如下： ```sql SELECT * FROM ( SELECT A.*, ROWNUM rn FROM ( SELECT * FROM emp ) A WHERE ROWNUM <= 4 ) WHERE rn > 2; ``` #### 三、Oracle内置函数 Oracle提供了丰富的内置函数，包括字符串函数、数字函数、日期函数等。 ##### 字符串函数 - **CONCAT()**：用于连接两个或多个字符串。 - **LENGTH()**：返回字符串的长度。 - **UPPER()** / **LOWER()**：将字符串转换为全大写或全小写。 - **TRIM()**：去除字符串首尾的空格。 - **REPLACE()**：替换字符串中的某些字符。 - **SUBSTR()**：截取字符串的一部分。 ##### 示例 - 字符串连接： ```sql SELECT CONCAT('abc', 'hello') AS str FROM dual; ``` - 获取字符串长度： ```sql SELECT LENGTH('abc 天津') AS len FROM dual; ``` - 转换字母大小写： ```sql SELECT UPPER('abc') FROM dual; SELECT LOWER('aBc') FROM dual; ``` - 去除左右空格： ```sql SELECT TRIM(' rererer dfdfd ') FROM dual; ``` - 查找替换： ```sql SELECT REPLACE('abcbc123bc123', 'bc', 'k') FROM dual; ``` - 截取字符串： ```sql SELECT SUBSTR('1234567890', 2, 3) FROM dual; SELECT SUBSTR('1234567890', 2) FROM dual; ``` ##### 数字函数 - **ABS()**：求绝对值。 - **CEIL()** / **FLOOR()**：向上取整或向下取整。 - **ROUND()**：四舍五入。 ##### 示例 - 求绝对值： ```sql SELECT ABS(-2) FROM dual; ``` - 向上取整： ```sql SELECT CEIL(4.34) FROM dual; SELECT CEIL(4.01) FROM dual; SELECT CEIL(4.00) FROM dual; ``` - 向下取整： ```sql SELECT FLOOR(4.01) FROM dual; SELECT FLOOR(4.94) FROM dual; ``` - 四舍五入： ```sql SELECT ROUND(4.41) FROM dual; SELECT ROUND(4.94) FROM dual; ``` ##### 日期函数 - **SYSDATE**：获取当前系统的日期及时间。 ##### 示例 ```sql SELECT SYSDATE FROM dual; ``` 通过以上内容，我们可以了解到Oracle中序列、分页查询及各种内置函数的具体用法和示例。这些知识点在实际开发中非常有用，能够帮助我们更高效地管理和操作数据。