序列的作用
序列是指的是序列号生成器(SEQUENCE),可以为表中的行自动生成序列号,产生一组等间隔的数值。不占用磁盘空间,但是占用内存空间。
序列的主要作用是生成表的主键值,可以在插入语句中使用,也可以通过检查当前值,使得序列曾只下一个值。
创建序列
创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下:
CREATE SEQUENCE 序列名
[INCREMENT BY n] 用于定义序列的步长,如果省略,则默认为1,出现负值,则oralce序列的值按照此步长呈现递减变化。
[START WITH n] 定义序列的初始值
[{MAXVALUE/ MINVALUE n}] 定义序列产生的最大值/最小值
[{CYCLE|NOCYCLE}] CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
[{CACHE n| NOCACHE}];定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。
NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用。
CURRVAL 中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ,二者应同时有效。
例如:新建表student
CREATE TABLE "DEARZ"."student" (
"id" NUMBER NOT NULL,
"name" VARCHAR2(255 BYTE),
"age" NUMBER(10,0),
"gender" NUMBER,
CONSTRAINT "SYS_C0011053" PRIMARY KEY ("id")
)
创建序列,注意序列初始值不能小于最小值 否则会报错
CREATE sequence seq_stu
INCREMENT by 1
START WITH 1
minvalue 1
maxvalue 99999
order
cache 20
cycle
在插入语句的时候使用序列,主键id的值会自动增加
insert into "student" values (seq_stu.nextval,'zhangsan',null,null)
对student表添加触发器,则不需要写id这一列,id也会自动增加
create or replace TRIGGER tg_insertId
before insert on "student" for each row
BEGIN
SELECT seq_stu.Nextval into:new."id" from dual;END;
测试一下:
insert into "student"("name","age","gender") values ('王五',null,null)
通过JAVA导出ORACLE序列、表结构、触发器、索引的SQL语句
查询这四种类型的关键的SQL语句为
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name) as sql_statement, U.OBJECT_TYPE as table_factor
FROM USER_OBJECTS U
where U.OBJECT_TYPE = 'TABLE'
or U.OBJECT_TYPE = 'VIEW'
or U.OBJECT_TYPE = 'INDEX'
or U.OBJECT_TYPE = 'PROCEDURE'
or U.OBJECT_TYPE = 'SEQUENCE'
or U.OBJECT_TYPE = 'TRIGGER'
order by U.OBJECT_TYPE desc;
测试结果如下,可以看出对应的SQL语句已经被查询出来了
接下来就是通过java代码来获取查询的结果,并写入新建的sql文件中
主要步骤如下
1 连接Oracle数据库
2 执行上述SQL语句
3 解析返回的结果
4 通过Java IO流写入SQL文件中
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.springframework.util.StringUtils;
import java.io.*;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
@Slf4j
public class test1 {
private static final String TYPE = "oracle";
private static final String URL = "localhost:1521";
private static final String SERVICE_NAME = "ORCL";
/*
* oracle jdbc连接后缀为服务名 username 等价于 mysql的数据库名 原因在于表空间和用户进行了绑定
* */
private static final String USERNAME = "DEARZ";
private static final String PASSWORD = "123456";
//sql DBMS_METADATA
private static final String SQL = "SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name) as sql_statement, U.OBJECT_TYPE as table_factor " +
" FROM USER_OBJECTS U " +
" where U.OBJECT_TYPE = 'TABLE' " +
" or U.OBJECT_TYPE = 'VIEW' " +
" or U.OBJECT_TYPE = 'INDEX' " +
" or U.OBJECT_TYPE = 'PROCEDURE' " +
" or U.OBJECT_TYPE = 'SEQUENCE' " +
" or U.OBJECT_TYPE = 'TRIGGER' " +
" order by U.OBJECT_TYPE desc";
//导出oracle table 文件
@Test
public void importOracle() throws SQLException {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// conn = ConnectionUtil.getConnection(TYPE,URL, USERNAME,PASSWORD,SERVICE_NAME);
//三个参数的分别为连接的URL,用户名,密码
conn = DriverManager.getConnection("jdbc:oracle:thin:@"+URL+":"+SERVICE_NAME,USERNAME,PASSWORD);
log.info(conn.getCatalog());
// String sql = "select * from \"student\"";
//创建sql语句执行的对象
PreparedStatement ps = conn.prepareStatement(SQL);
//执行查询语句,返回结果集合
ResultSet res = ps.executeQuery();
//处理任何语句,有结果集合返回true,没有结果集合返回false
//boolean execute = ps.execute(); 执行完之后会调用conn.close()方法
/* List<Student> list = new ArrayList<>();
Student student = new Student();
while (res.next()){
Integer id = res.getInt("id");
String name = res.getString("name");
Integer age = res.getInt("age");
Integer gender = res.getInt("gender");
student.setId(id);
student.setAge(age);
student.setName(name);
student.setGender(gender);
list.add(student);
}
System.out.println(list);*/
Map<String,String> map = new HashMap<>();
while (res.next()){
String sql_statement = res.getString("sql_statement");
String table_factor = res.getString("table_factor");
map.put(table_factor,sql_statement);
}
System.out.println(map);
//导出DEARZ库中表的序列
importSequence(map);
} catch (Exception e) {
e.printStackTrace();
}finally {
conn.close();
}
}
public void importSequence(Map map) throws IOException {
//todo import sequence file
String seq = (String) map.get("SEQUENCE");
File file = new File("D:\\import\\sequence.sql");
if (!file.exists()){
file.createNewFile();
}
FileWriter fw = null;
BufferedWriter bw = null;
try {
fw = new FileWriter(file, true);
bw = new BufferedWriter(fw);
if (!StringUtils.isEmpty(seq)){
bw.write(seq.trim());
bw.newLine();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}finally {
bw.close();
fw.close();
}
}
}
导出结果如下图所示,其余的也是三类SQL语句也是类似的做法。