【数据库学习笔记】Oracle_02_序列,分页,常用函数,jdbc,PL/SQL

Oracle_02

A.序列

1.sequence序列

是oracle提供的用于产生一系列唯一数字的数据库对象

oracle没有自增长，因此就要使用序列

主要用于提供主键值，将序列值装入内存可以提高访问效率

2.创建序列

要有创建序列的权限 create sequence 或 create any sequence

语法：

a.CREATE SEQUENCE sequence 

创建序列名称

b.[INCREMENT BY n] 

递增的序列值是n，如果n是正数就递增

如果是负数就递减，默认是1

c.[START WITH n]  

开始的值，递增默认是minvalue，递减是maxvalue

d.[{MAXVALUE n | NOMAXVALUE}] 最大值

e.[{MINVALUE n | NOMINVALUE}]   最小值

f.[{CYCLE | NOCYCLE}]     循环/不循环

g.[{CACHE n | NOCACHE}]  分配并存入到内存中

先创建一个表

查看表

创建序列

3.使用序列

一旦定义了某个序列，可以使用currval,nextval获取值

currval: 返回sequence的当前值

nextval: 返回sequence的下一个值

问题：我是从1开始，但实际上确从2开始

11.2.0.1版本开始，提供了一个延迟段创建特性

当我们创建了新表和序列，在插入语句时

序列会跳高第一个值

解决方法：更改数据库延迟段创建特性为false(用最高权限)

注意事项

a.currval总是返回当前sequence的值，只有在第一次nextval初始化后，才能使用currval，否则会出错

b.每使用一次nextval，就会增加一次sequence的值，同一个语句里面要是有多个nextval，其数值就是不一样的

c.第一次nextval返回的值是初始值：随后的nextval会自动增加定义的increment by值，然后返回增加后的值

d.如果指定cache值，oracle就可以预先在内存里面放置一些sequence，这样存取会更快，cache里面取完后，oracle自动再取一组到cache

e使用cache或许会跳号，比如数据库突然不正常down掉，cache中的sequence就会丢失，可以在定义sequence的时候，使用nocache防止这种情况

4.使用触发器实现自增长

如果每次都要插入seq.nextval的值会非常累赘与麻烦

因此可以考虑使用触发器来完成这一步工作

dual表是oracle内部提供的临时数据计算的特殊表

添加数据

此时，id是从1开始

B.分页

1.概述

Oracle中的表，除了建表时的各个字段，还有两个字段

分别是ROWID（行标示符）和ROWNUM（行号）

使用DESC查看表的结构，也是看不到这两个列

只在数据库内部使用的，所以也通常称他们为伪列（pseudo column）

rowid我们一般用不到，Oracle数据库内部使用它来存储行的物理位置

而使用rownum来进行分页查询的，它的值表示行号

2.实现

准备

select t1.* from (			-- t1为中间表
select info.*, rownum rn from info) t1 -- 将行号和所有数据放到一个中间表上
where rn between pageSize*(curPage-1)+1 -- 当页第一条
and pageSize*curPage	-- 当页最后一条

每页展示三条

C.常用函数

1.字符类函数

a.CONCAT(X,Y)

连接字符串X和Y

b.LENGTH(X)

返回X的长度

c.SUBSTR(X,start[,length])

返回X的子字串，从start处开始，截取length个字符

缺省length，默认到结尾

d.LTRIM(X[,TRIM_STR])

把X的左边截去trim_str字符串，缺省截去空格

e.RTRIM(X[,TRIM_STR])

把X的右边截去trim_str字符串

2.数字函数

接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式

a.ABS(X) X的绝对值

b.CEIL(X) 大于或等于X的最小整数值

c.FLOOR(X) 小于或等于X的最大整数值

d.ROUND(X[,Y]) X在第Y位四舍五入

e.POWER(X,Y) X的Y次幂

3.日期函数

对日期进行运算

a.ADD_MONTHS(d,n)

在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期

d 表示日期，n 表示加的月数

b.LAST_DAY(d)

返回指定日期当月的最后一天

c.EXTRACT(fmt FROM d)

提取日期中的特定部分

fmt 为：YEAR、DAY等。

其中 YEAR、MONTH、DAY可以为 DATE 类型匹配

也可以与 TIMESTAMP 类型匹配

但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配

4.转换函数

a.TO_CHAR(d|n[,fmt])

把日期和数字转换为制定格式的字符串。Fmt是格式串

b.TO_DATE(X,[,fmt])

把一个字符串以fmt格式转换成一个日期类型

c.TO_NUMBER(X,[,fmt])

把一个字符串以fmt格式转换为一个数字

5.其他单行函数

a.NVL(X,VALUE)

如果X为空，返回value，否则返回X

b.NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2

D.JDBC连接oracle(与mysql相同)

1.下载oracle驱动jar

2.拷贝到web应用的WEB-INF下lib目录

3.配置对应的驱动类  url user password等

url：jdbc:oracle:thin:@localhost:1521:orcl

4.使用jdbc方式登录操作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

public class Test {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "123456");
			Statement stmt = conn.createStatement();
			String sql = "select * from emp";
			List<Map<String,Object>> list = DbUtils.getResult(sql);
			System.out.println(list);
		} catch (ClassNotFoundException e) {
			System.out.println("驱动类有误：" + e);
		} catch (Exception e) {
			System.out.println("sql有误：" + e);
		} 
	}
}

5.还可以用c3p0

E.PL/SQL

1.概述

Oracle PL/SQL语言（Procedural Language/SQL）

是结合了结构化查询和Oracle自身过程控制为一体的强大语言

PL/SQL不但支持更多的数据类型，拥有自身的变量申明，赋值语句

而且还有条件，循环等流程控制语句

过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言

可以创建过程和函数以及程序包

2.特点

PL/SQL是一种块结构的语言，它将一组语句放在一个块中

一次性的发送给服务器，由服务器和自身引擎两个执行器执行代码

3.有点

a.支持SQL

SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库的数据

PL/SQL支持所有的SQL数据操纵命令、游标控制命令

事务控制命令、SQL函数、运算符和伪列。

同时PL/SQL和SQL语言紧密集成

PL/SQL支持所有的SQL数据类型和NULL值

b.支持面向对象编程

PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型

可以对类型进行继承，可以在子程序中重载方法等

c.更好的性能

SQL是非过程语言，只能一条一条的执行

而PL/SQL把一个PL/SQL统一进行编译后执行

同时还可以把编译好的PL/SQL块存储起来，以备重用

减少了应用程序和服务器之间的通信时间，所以PL/SQL是高效而快速的

d.可移植性

使用PL/SQL编写的应用程序语言

可以移植到任何操作平台的ORACLE服务器

同时还可以编写可移植程序库，在不同环境中使用

e.安全性

可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分割

这样可以限制对ORACLE数据库的访问

数据库还可以授权和撤销其他用户的访问权利

4.基本语法

PL/SQL是一种块结构的语言

一个PL/SQL包含了一个或多个逻辑块

逻辑块中可以声明变量、写程序主体、还可以捕获异常和异常处理

每个逻辑块分为三个部分

[DECLARE]  

-- 变量或常量或游标声明。可选。          　　　　
BEGIN

--执行部分。主要的逻辑和控制运算均在此实现

以BEGIN开始，END（加;号）结束。必选

[EXCEPTION]　　　　　　　　　

--异常处理部分。可选。作用和java中异常一样

END;

5.注意事项

a.LP/SQL是一种编程语言，有自己独有的数据类型，变量声明和赋值以及流程控制语句

b.对大小写不敏感，但是为了规范：关键字全部大写，其余部分小写

c.每一条语句以分号结束

6.设置服务端显示执行结果

否则不打印结果，设置一次即可

7.求两个数商

8.PL/SQL字符集

所有的PL/SQL程序元素(如关键字  变量名  常量名)中可以使用的合法字符集

大写和小写字母

数字

非显示的字符：制表符 空格  回车

数学符号：+ - * / ><=等

间隔符：() {} [] ? ! ; : @ # % $ &等

9.常用数据类型

a.基本数据类型

数值类型

包括NUMBER,PLS_INTEGER,BINARY_INTEGER

等3种基本类型，前者可存储整数或浮点数

后两个只能存储整数

为与其他编程语言数据类型的兼容

也可使用子类型(即别名)表示NUMBER类型：DEC DECIMAL DOUBLE INTEGER INT NUMERIC SMALLINT PLS_等

可使用NUMBER(P,S)来格式化数字

p代表精度即有效数据个数，S代表刻度范围即小数点后有效位数

b.字符类型

包括VARCHAR2 CHAR LONG NCHAR NVARCHAR2等

1)VARCHAR2

和数据库中VARCHAR2类似，存储可变长字符串

无默认长度，故必须给出最大长度参数，不能超过32767字节

2)CHAR类型

存储定长字符串，不足空格补全，最大3767字节

默认长度为1字节

3))LONG类型

可变字符串，最大长度32767字节

数据库中LONG最大2GB，所以可以给数据库中LONG字段赋值

c.日期类型

DATE类型：存储日期和时间信息

存储空间7字节，分别使用一个字节存储世纪 年 月 日 小时 分 秒

d.布尔类型

BOOLEAN类型：用于程序的流程控制和业务逻辑判断

值可以是TRUE FALSE NULL值之一

e.特殊数据类型

1)%TYPE类型

声明与指定列相同的数据类型，通常紧跟在指定列名后面

可以不查看列类型确定变量类型

已有列改变数据类型则变量类型同步改变

SCOTT模式下使用

%type类型输出emp表中编号为7396员工的名字和职务信息

2)RECORD记录类型

可以存储多个列值组成的一行数据

但在声明记录类型变量前，必须定义记录类型

然后才声明记录类型的变量

记录类型是一种结构化的数据类型

使用TYPE定义，包含 成员变量和数据类型

语法格式：

type record_type is record(

var_member1 data_type [not null] [:=default_value],

......

var_membern data_type [not null] [:=default_value]

)

其中record_type是记录类型

var_member1表示变量名称

data_type表示变量类型

3)%ROWTYPE类型

结合了%TYPE和RECORD记录类型的优点

可以根据数据库中一行的结构定义一个类型

存储检索到的一行记录

rowVar_name table_name%rowtype;

rowVar_name变量名

table_name指定表名

10.流程控制语句

a.选择语句：条件语句

b.if..then...else双条件

c.if...then...elsif多条件语句

d.case语句

与if...then...elsif相似

在case后面有一个选择器，通常是变量，接着是when子句

后面紧跟表达式，选择器值和表达式值匹配

11.循环控制语句

a.loop语句

计算1-100的和

b.while语句

c.for语句

求1-100偶数和