mybatis oracle 游标 抛出空异常,Oracle学习第三天

1.PLSQL入门

Oracle数据库对SQL进行了扩展,然后加入了一些编程语言的特点,可以对SQL的执行过程进行控制,然后实现一些复杂的业务功能.

1.1 PLSQL的组成

组成部分

declare

- 是声明部分

begin

- 是plsql的主体部分

exception

- 异常处理部分

end

注意:可以没有declare和exception,但是不可以没有begin和end.

dbms_output.put_line():向控制台输出内容.

如果要向控制台输出内容之前,就必须要打开输出功能.

set serveroutput on;

该命令只需要执行一次就可以了,不需要每次都执行.

1.2 运算符

算术运算符: + - * /

比较运算符: > >= < <= != = <>(和!=是一样的)

逻辑运算符: or and not

赋值运算符: :=

范围运算符: ..

特殊的赋值:

可以使用select into

1.3 变量

语法: 变量名[constant]数据类型 [:=值];

如果指定了constant关键字,那么声明该变量的时候就必须要进行初始化,而且该变量的值是不可以改变的.

常见的数据类型:

数据类型

作用

NUMBER

数值型

VARCHAR2

字符串类型

CHAR

字符型

DATE

日期型

TIMESTAMP

时间类型

BOOLEAN

布尔类型

特殊的数据类型

%type:该变量的数据类型与某一个变量或者是某一个字段的数据类型相同

%rowtype:指定数据类型与某一个表相同,只可以使用该类型的变量接收一行数据.

1.4 流程控制

1.4.1 条件判断

语法格式:

if 条件表达式 then

代码...

elsif 条件表达式 then

代码...

...

else

代码...

end if;

注意:

elsif没有e,也没有空格

代码

-- 查询指定员工编号的员工的工资等级,如果是大于3000是A;2000-3000是B,其他等级就是C

declare

p_sal emp.sal%type;

p_empno emp.empno%type := 7900;

begin

select sal into p_sal from emp where empno = p_empno;

if p_sal > 3000 then

dbms_output.put_line('工资等级为A');

elsif p_sal > 2000 then

dbms_output.put_line('工资等级为B');

else

dbms_output.put_line('工资等级为C');

end if;

end;

1.4.2 循环语句

loop循环(类似于java中的do..while循环)

语法格式

loop

循环体

exit when 条件; -- 退出条件

end loop;

示例

-- 实现1到10 的相加

declare

p_sum number(4) := 0; -- 保存相加的结果

p_num number(4) := 1;

begin

loop

p_sum := p_sum + p_num;

p_num := p_num +1;

exit when p_num > 10;

end loop;

dbms_output.put_line(p_sum);

end;

while...loop循环(类似于java中的while循环)

语法格式

while 条件表达式

loop

循环体

end loop;

示例:

-- 实现1-10之间的奇数相加

declare

p_sum number(4) := 0;

p_count number(4) := 1;

begin

while p_count <= 10

loop

if mod(p_count,2) = 1 then

p_sum := p_sum + p_count;

end if;

p_count := p_count + 1;

end loop;

dbms_output.put_line(p_sum);

end;

for循环

语法格式

for 变量名 in 开始值..结束值

loop

循环

end loop;

示例

-- 实现1-10之间的偶数相加

declare

p_sum number(2) := 0;

begin

for i in 1..10

loop

if mod(i,2) = 0 then

p_sum := p_sum + i;

end if;

end loop;

dbms_output.put_line(p_sum);

end;

练习:输出99乘法表

dbms_output.put() 输出不换行

dbms_output.new_line() 换一行

示例:

set SERVEROUTPUT ON;

begin

for i in 1..9

loop

for j in 1..i

loop

dbms_output.put(j || '*' || i || '=' || (j*i));

dbms_output.put(' ');

end loop;

dbms_output.new_line();

end loop;

end;

1.5 异常处理

1.5.1 异常处理的语法结构

exception

when 异常名1 then

异常处理语句

when 异常名1 then

异常处理语句

.....

when others then

.....异常处理语句.....

1.5.2 关键字

sqlcode:用来获取异常的编号

sqlerrm:用来获取异常的信息

1.5.3 异常分类:预定义异常和自定义异常

1.5.4 预定义异常

预定义异常可以根据异常的编号去文档中进行查找;

有些异常是有名字可以进行捕获,但是并不是所有的异常都有名字,某些异常只有异常

exceptionname.png

有名字的异常捕获示例

set SERVEROUTPUT ON;

declare

p_temp number(4);

begin

p_temp := 10/0; -- 异常信息

exception

when zero_divide then

dbms_output.put_line('除数不可以为0');

when others then

dbms_output.put_line('异常代码:' || sqlcode);

dbms_output.put_line('异常信息' || sqlerrm);

end;

1.5.5 自定义异常

语法结构 异常名 exception

Oracle抛出异常的方式

方式一:Oracle自动抛出异常

方式二:使用raise关键字人手抛出异常.

方式一示例:

-- 自定义异常

declare

myEx exception;

p_sal emp.sal%type;

begin

-- 判断员工的工资,如果工资是少于1000元就抛出异常

select sal into p_sal from emp where empno = 7369; -- 800

if p_sal < 1000 then

raise myEx; --抛出异常

else

dbms_output.put_line('工资还是可以的');

end if;

exception

when myEx then

dbms_output.put_line('唉,工资太低了!');

when others then

dbms_output.put_line('异常代码:' || sqlcode);

dbms_output.put_line('异常信息' || sqlerrm);

end;

使用raise_application_error(sqlcode,sqlerrms)抛出异常

-- 自定义异常

declare

myEx exception;

p_sal emp.sal%type;

pragma exception_init(myEx,-20001);-- 把指定的异常编号绑定在一个异常变量

begin

-- 判断员工的工资,如果工资是少于1000元就抛出异常

select sal into p_sal from emp where empno = 7369; -- 800

if p_sal < 1000 then

RAISE_APPLICATION_ERROR(-20001, '哎工资也太少了');-- 抛出异常

else

dbms_output.put_line('工资还是可以的');

end if;

exception

when others then

dbms_output.put_line('异常代码:' || sqlcode);

dbms_output.put_line('异常信息' || sqlerrm);

end;

注意:对于那些没有名称的异常,如果需要捕获这些异常,就需要先把异常的编号与一个异常的变量进行绑定;使用pragma exception_init(异常名,sqlcode)将异常编号与异常变量名进行绑定

1.5 事务控制

1.5.1 数据库中事务的概念

在一个事务中,所有的DML操作都会作为一个整体来执行,要么就是全部成功,要么就是全部失败,如果一个事务没有结束,那么该事务中的数据只会在当前的会话中有效.

问题:一个事务什么时候才会结束?

提交事务

回滚事务

执行了DDL(增加表,修改表,删除表),CONN等操作

-- 账户表

create table tb_account (

id number(4) primary key,

name varchar2(20) unique,

mon number(7,2)

)

insert into tb_account values(1,'狗娃',5000);

insert into tb_account values(2,'狗剩',5000);

declare

p_temp number(2) := 12;

begin

update tb_account set mon = mon -1000 where name='狗娃';

p_temp := p_temp / 0;

update tb_account set mon = mon + 1000 where name='狗剩';

commit; -- 事务提交

dbms_output.put_line('成功执行了');

exception

when others then

dbms_output.put_line('错误信息' || sqlerrm);

rollback; -- 事务回滚

dbms_output.put_line('转账失败,事务回滚了');

end;

设置保存点: 语法savepoint 保存点

设置保存点的作用:让事务回到保存点的位置

注意:执行事务回滚的时候,保存点之前的DML语句是不受到影响的.

-- 保存点

begin

savepoint a;

insert into emp(empno,ename)

values (1200,'jacky');

SAVEPOINT b; -- 保存点b

insert into emp(empno,ename)

values (1300,'rose');

--事务回滚到b

rollback to b;

end;

2.游标

2.1 游标的概念

游标就是用来保存结果集的数据类型.游标有点类似于java中的迭代器(Iterator).

使用游标获取数据的时候,首先是先移动游标,然后获取游标所在的行数据.

2.2 创建游标

语法格式:

cursor 游标名(参数) is select 语句.

2.2 使用游标

打开游标

open 游标名;

抓取游标数据:

fetch 游标 into 行变量

关闭游标

close 游标.

示例

-- 创建游标

declare

-- 声明游标

cursor p_empCursor is select * from emp;

-- 声明行变量

p_emp emp%rowtype;

begin

-- 打开游标

open p_empCursor;

-- 获取游标的数据

fetch p_empCursor into p_emp;

dbms_output.put_line(p_emp.ename);

-- 关闭游标

close p_empCursor;

end;

2.3 游标的属性

- %found:如果fetch成功,该属性就会返回true,否则就返回false

- %notfound:如果fetch不成功,该属性就会true,否则返回false

- %rowcount:返回当前游标已经遍历的行数.

- %isopen:判断游标是否已经打开,如果已经打开了,该属性就会返回true,如果没有打开,该属性就会返回false;如果没有打开游标就调用了fecth,就会出现错误.(提示信息:无效的游标.)

set SERVEROUTPUT ON;

-- 创建游标

declare

-- 声明游标

cursor p_empCursor is select * from emp;

-- 声明行变量

p_emp emp%rowtype;

begin

-- 打开游标

open p_empCursor;

-- 循环

loop

-- 获取游标的数据

fetch p_empCursor into p_emp;

-- 关闭游标

exit when p_empCursor%notfound;

dbms_output.put_line('员工的姓名是:' || p_emp.ename || '员工的工资是:' || p_emp.sal);

end loop;

dbms_output.put_line('当前已经遍历的行数:' || p_empCursor%rowcount);

close p_empCursor;

end;

2.3 游标的for循环

如果使用了for循环遍历了游标,就不需要打开游标,fetch,关闭游标的操作.

语法格式:

for 变量 in 游标

loop

循环体

end loop;

代码示例

-- for...in方式遍历游标

declare

cursor cur_emp is select * from emp;

begin

for p_emp in cur_emp

loop

dbms_output.put_line(p_emp.ename || '的工资是' || p_emp.sal);

end loop;

end;

2.4 带有参数的游标

使用方法:如果是使用带有参数的游标,在打开游标的时候传递参数

declare

-- 声明带有参数的游标

cursor p_empCursor(p_deptno emp.deptno%type) is select * from emp where deptno= p_deptno;

-- 行变量

p_emp emp%rowtype;

begin

-- 打开游标

open p_empCursor(10);

loop

-- 获取游标的数据

fetch p_empCursor into p_emp;

exit when p_empCursor%notfound;

dbms_output.put_line('员工的姓名是:' || p_emp.ename || '员工的工资是:' || p_emp.sal);

end loop;

dbms_output.put_line('当前已经遍历的行数:' || p_empCursor%rowcount);

close p_empCursor;

end;

如果是游标for循环,需要需要在游标名的后面把参数传递到游标里面.

2.5 隐式游标

隐式游标又称之为SQL游标,隐式游标不需要用户创建,当用户执行DML操作的时候,Oracle数据库就会自动创建一个游标,通过这个游标可以操作DML操作的状态信息.

2.5.1 隐式游标的属性

%notfound:如果执行DML操作不成功,那么该属性就返回true,否则就返回false.

%found:如果执行DML操作成功,那么该属性就返回true,否则就返回false.

%rowcount:返回受到影响的行数.

-- 隐式游标

declare

begin

delete from emp where deptno is null;

if sql%found then

dbms_output.put_line('删除成功,已经删除了' || sql%rowcount || '记录');

else

dbms_output.put_line('删除失败');

end if;

end;

3. 存储过程

3.1 概念

存储过程就是一个命名了的PLSQL块,通过存储过程,客户端可以调用数据库服务器的PLSQL块.

好处

提高了数据库的安全性

减少了IO次数,提高了程序的执行效率

提高了代码的复用性

缺点

移植性比价差,如果更换数据库代码就无法运行了

3.2 创建存储过程

语法格式:

create or replace procedure 过程名(参数...) as

--声明部分

begin

-- 过程主体部分(PLSQL块)

end

示例:

-- 创建存储过程

create or replace procedure proc_hello as

begin

SYS.DBMS_OUTPUT.PUT_LINE('hello procedure');

end;

注意

如果没有参数就不需要括号了.

3.3 使用存储过程

方式一:在PLSQL块内部调用存储过程:

begin

过程名(参数)

end;

示例:

begin

proc_hello; -- 如果没有参数也不需要写括号

end;

方式二:在PLSQL外部调用存储过程,需要使用到的关键字是exec

-- 调用存储过程

exec proc_hello;

3.4 存储过程的分类

1.按照参数进行划分:

- 带输入参数的存储过程

- 带输出参数存储过程

- 带输入输出参数的存储过程

3.5 带有输入参数的存储过程

定义输入参数:参数名 [in] 参数类型;

-- 创建一个存储过程,往emp表查询数据

create or replace procedure proc_add_emp(p_empno emp.empno%type,p_ename emp.ename%type) as

begin

insert into emp(empno,ename)

values(p_empno,p_ename);

end;

-- 往emp中插入数据

exec proc_add_emp(1313,'美美');

3.6 带有输出参数的存储过程

输出参数的作用:就是把存储过程中的数据输出到存储过程的外部

定义输出参数:参数名 out 类型

-- 创建带有输出参数的存储过程,实现1到10相加,并且把结果输出到存储过程的外部

/*

使用有输出参存储过程，计算1到10的总和并通过参数返回

*/

create or replace procedure pro_1to10_sum(

p_sum out number

)

as

tem_sum number(4):=0;

begin

for i in 1..10

loop

tem_sum := tem_sum + i;

end loop;

p_sum := tem_sum;

end;

/

-- 调用存储过程

declare

p_sum number(4);

begin

pro_1to10_sum(p_sum);

dbms_output.put_line('1至10的和为：'|| p_sum);

end;

3.7 带有输入和输出参数的存储过程

定义格式: 参数名 in out 类型;既可以传入,也可以传出

/*

使用有输入、输出参存储过程；根据empno查询该员工号对应的员工的姓名和工资

*/

create or replace procedure pro_query_enameAndSal_by_empno(

s_empno emp.empno%type,

s_ename out emp.ename%type,

s_sal out emp.sal%type

)

as

begin

select ename,sal into s_ename, s_sal from emp where empno= s_empno;

end;

/

-- 调用存储过程

declare

p_ename emp.ename%type;

p_sal emp.sal%type;

begin

--pro_query_enameAndSal_by_empno(7369, p_ename, p_sal);

pro_query_enameAndSal_by_empno(7369, s_sal => p_sal, s_ename => p_ename);

dbms_output.put_line('员工号为7369的员工名称为：'|| p_ename||'，其工资为：'|| p_sal);

end;

3.8 使用JDBC调用存储过程

3.8.1 准备阶段

准备一个存储过程:

-- 需求,根据员工的编号查询上司的编号

create or replace procedure proc_get_mgr_no(

p_empno in out emp.empno%type)

as

begin

select mgr into p_empno from emp where empno = d_empno;

end;

CallableStatement:

是PrepareStatement的一个子接口;该接口是用来执行SQL的存储过程.

setXxx():用来设置输入参数;

getXxx():用来获取输出参数的值;

registerOutParameter():如果存储过程有输出参数的话,那么就必须要对输出参数进行注册;(指定输出参数的类型).

3.8.1使用JDBC调用存储过程的步骤:

第一步:获取数据库的连接

第二步:创建一个CallableStatement对象;

第三步:如果存储过程包含了输入参数,设置输入参数的值,如果存储过程包含输出参数,那么就对该输出参数进行注册

第四步:执行存储过程

第五步:如果有输出参数,就获取到输出参数的值

第六步:释放资源

代码示例:

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

/**

* 使用JDBC调用存储过程

*

* @author like

*

*/

public class Demo {

public static void main(String[] args) {

// 首先获取到连接对象,由于jdbc4.0之后就已经自动注册驱动了

try (Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

// 创建CallableStatement对象

CallableStatement call = con

.prepareCall("{call proc_get_mgr_no(?)}");

) {

// 设置输入参数

call.setInt(1, 7900);

// 注册输出参数,注册的目的就是设置参数

call.registerOutParameter(1, OracleTypes.NUMBER);

call.execute();

// 如果有输出参数的话,就获取输出参数值

int mgrNo = call.getInt(1);

System.out.println("该员工上司的编号是:" + mgrNo);

} catch (SQLException e) {

e.printStackTrace();

}

}

}

3.8 删除存储过程

语法:

drop procedure 存过程名字

4. 存储函数

4.1 概念

存储函数与存储过程用法非常的类似,但是存储函数是有返回值的,但是存储过程是没有返回值的.

4.1 创建函数

语法格式:

create or replace function 函数名(参数...) return 返回值类型 as

-- 声明部分

begin

-- 函数的主体部分

end;

注意函数与存储过程的一个区别点就是函数的声明那里必须要有返回值return 返回值类型.

示例:

-- 创建函数

create or replace function func_hello return varchar2 as

begin

return 'hello function';

end;

4.2 使用函数

在QLSQL块中使用函数

-- 使用函数

declare

p_temp varchar2(20);

begin

p_temp := FUNC_HELLO; -- 如果没有参数可以不写括号

dbms_output.put_line('函数的返回值是:' || p_temp);

end;

在SQL语句中使用

-- 在sql语句中使用函数

select func_hello from dual;

4.3 函数的分类

带有输入参数的函数

带有输出参数的函数

带有输入输出参数的函数

4.4 带有输入参数的函数

-- 定义一个函数,根据员工的编号返回该员工的年薪

create or replace function func_get_year_sal (p_empno emp.empno%type) return number as

p_sal emp.sal%type;

p_comm emp.comm%type;

begin

select sal,comm into p_sal,p_comm from emp where empno = p_empno;

return p_sal*12 + p_comm;

end;

select FUNC_GET_YEAR_SAL(7788) from dual;

4.5 带有输出参数的函数

-- 定义一个函数,返回员工的姓名,工资,年薪;要求:员工的姓名,工资通过输出参数输出,

-- 年薪通过返回值返回

create or replace function func_get_user_info(

p_empno emp.empno%type,

p_ename out emp.ename%type,

p_sal out emp.sal%type

) return number as

-- 函数的局部变量

p_comm emp .comm%type;

begin

select ename,sal,comm into p_ename,p_sal,p_comm from emp where empno = p_empno;

return p_sal*12 + p_comm;

end;

-- 调用函数

declare

p_ename emp.ename%type;

p_sal emp.sal%type;

p_yearSal number;

begin

p_yearSal := func_get_user_info(7900,p_ename,p_sal);

dbms_output.put_line('姓名:' || p_ename || ' 工资:' || p_sal || ' 年薪:' || p_yearSal);

end;

4.5 带有输入和输出参数的函数

一般很少使用输出参数,可以自行了解.

4.6 使用JDBC调用函数

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

/**

* 使用JDBC调用函数

*

* @author like

*

*/

public class Demo2 {

public static void main(String[] args) {

// 首先获取到连接对象,由于jdbc4.0之后就已经自动注册驱动了

try (Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

// 创建CallableStatement对象

CallableStatement call = con

.prepareCall("{?= call func_get_user_info(?,?,?)}");) {

// 注册返回值类型

call.registerOutParameter(1, OracleTypes.NUMBER);

// 设置输入参数的值

call.setInt(2, 7900);

// 注册输入参数

call.registerOutParameter(3, OracleTypes.VARCHAR);

call.registerOutParameter(4, OracleTypes.NUMBER);

// 执行函数

call.execute();

// 获取返回值和输出参数

int yearSal = call.getInt(1);

String ename = call.getString(3);

double sal = call.getDouble(4);

System.out.println(ename + "的工资是:" + sal + "年薪是:" + yearSal);

} catch (SQLException e) {

e.printStackTrace();

}

}

}

注意:

使用JDBC调用函数,必须要注册返回值,如果有输出参数的话,还要注册输出参数.

4.6 删除函数

语法格式:

drop function 函数名

5. 分区技术

5.1 分区的概念

分区技术就是把一个表分成不同的部分,每一个部分就是一个独立的分区.

优点

将数据分散在不同的区,减少了数据损坏的可能性

可以对单独的分区进行备份和恢复

将分区映射到不同的物理磁盘上

提高可管理性,可用性和性能.

一般包括范围分区,散列分区,列表分区,复合分区,间隔分区和系统分区.

5.2 范围分区

范围分区:就是根据某列值的范围进行分区.

范围分区的语法:

create table 之后

partition by range(字段)(

partition 分区1 values less than(值1),

partition 分区2 values less than(值2),

....

partition 分区3 values less than(maxvalue),

)

示例

-- 范围分区

create table emp2(

empno number(4) primary key,

ename varchar2(20),

sal number(7,2),

deptno number(4)

)

partition by range(sal) (

partition p1 values less than(2000),

partition p2 values less than(3000),

partition p3 values less than(maxvalue)

);

-- 初始化数据

insert into emp2

select empno,ename,sal,deptno from emp;

-- 使用分区

select * from emp2 partition(p1);

5.3 列表分区

列表分区可以根据某一列的值来进行分区管理.

-- 列表分区

create table emp3(

empno number(4) primary key,

ename varchar2(20),

sal number(7,2),

deptno number(4)

)

partition by list(deptno) (

partition p1 values(10),

partition p2 values(20),

partition p3 values(30),

partition p4 values(default)

);

-- 初始化数据

insert into emp3

select empno,ename,sal,deptno from emp;

-- 使用分区

select * from emp3 partition(p4);