一、存储过程
一、存储过程 是一段封装的代码块 编译好放在服务器,调用直接运行
Ⅰ 重复利用提升开发效率
Ⅱ 提升运行效率
1)创建语法:
create [or replace] procedure 过程名(参数名 out|in 参数数据类型)
as|is
–声明变量的部分
begin
–处理的代码
end;
2)调用过程语句
在begin 和 end 直接 过程名传参数调用
/*
一、存储过程 是一段封装的代码块 编译好放在服务器,调用直接运行
重复利用提升开发效率
提升运行效率
1)创建语法:
create [or replace] procedure 过程名(参数名 out|in 参数数据类型)
as|is
--声明变量的部分
begin
--处理的代码
end;
2)调用过程语句
在begin 和 end 直接 过程名传参数调用
*/
--1.参数的传入
---例1.使用存储过程实现增加某个员工的工资100 --打印一下原始工资和增加后工资
create or replace procedure add_sal(eno in number )
as
emp_sal number;
begin
select sal into emp_sal from emp where empno=eno;
dbms_output.put_line('原始工资=='||emp_sal);
update emp set sal=sal+100 where empno=eno;
commit;
select sal into emp_sal from emp where empno=eno;
dbms_output.put_line('涨工资后=='||emp_sal);
end;
--调用过程增加7654的工资
declare
begin
add_sal(7654);
end;
--2.参数的封装
/*
例2.需求:调用过程得到某个员工的年薪
用于判断 输出
*/
create or replace procedure count_sal(eno in number,year_sal out number)
as
begin
select sal*12+nvl(comm,0) into year_sal from emp where empno=eno;
end;
--调用过程得到7369的年薪
declare
emp_sal number; --声明变量作为参数传递
begin
count_sal(7369,emp_sal);
dbms_output.put_line('年薪为-==='||emp_sal);
end;
--3.使用游标封装结果集
/*
通过存储过程得到某个部门下的所有员工记录
通过传进去的部门号得到记录集合 作为输出参数
1)cursor 声明赋值集合
2)sys_refcursor 声明不需要集合
3)open 游标 for select 语句
*/
create or replace procedure dept_emp(dno in number,deptEmp out sys_refcursor)
as
begin
--通过传递的dno得到记录赋值给deptEmp
open deptEmp for select * from emp where deptno=dno;
end;
----调用过程得到30号部门的员工记录
declare
emp_cursor sys_refcursor;--声明系统引用游标作为参数传递
emp_row emp%rowtype;
begin
dept_emp(30,emp_cursor);
--提取记录
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line('编号=='||emp_row.empno||'姓名=='||emp_row.ename);
end loop;
close emp_cursor;
end;
二、存储函数
二、存储函数 是一段封装的代码块 编译好放在服务器,调用直接运行
重复利用提升开发效率
提升运行效率
1)创建语法:
create [or replace] function 函数名(参数名 out|in 参数数据类型) return 数据类型
as|is
–声明变量的部分
begin
–处理的代码
return 变量;
end;
2)调用过程语句
在begin 和 end 直接 函数名传参数调用 必须有变量接收函数的返回值
/*
二、存储函数 是一段封装的代码块 编译好放在服务器,调用直接运行
重复利用提升开发效率
提升运行效率
1)创建语法:
create [or replace] function 函数名(参数名 out|in 参数数据类型) return 数据类型
as|is
--声明变量的部分
begin
--处理的代码
return 变量;
end;
2)调用过程语句
在begin 和 end 直接 函数名传参数调用 必须有变量接收函数的返回值
*/
--1.使用函数实现跟过程一样的统计年薪
create or replace function count_fun_sal(eno in number,year_sal out number) return number
as
emp_year_sal number :=0;
begin
select sal*12+nvl(comm,0) into year_sal from emp where empno=eno;
return emp_year_sal;
end;
--调用函数得到7499年薪
declare
emp_year_sal number;
emp_sal number;
begin
emp_sal:=count_fun_sal(7499,emp_year_sal);
dbms_output.put_line(emp_year_sal);--年薪
dbms_output.put_line(emp_sal); --0
end;
---2.不使用out输出参数得到年薪
create or replace function count_sal_noout(eno in number) return number
as
emp_year_sal number :=0;
begin
select sal*12+nvl(comm,0) into emp_year_sal from emp where empno=eno;
return emp_year_sal;
end;
--调用函数得到7499年薪
declare
emp_sal number;
begin
emp_sal:=count_sal_noout(7499);
dbms_output.put_line(emp_sal); --年薪
end;
三、函数和过程
过程和函数的使用
都是业务功能的封装 提供调用
1.创建的关键字
2.函数在声明的时候 必须return 数据类型
3.函数begin end 必须返回变量
4.函数调用必须有返回值接收
5.函数可以用在select 语句中
使用场景:
通用习惯 java调用过程处理逻辑,
如果处理的过程中用到功能的封装,调用函数得到
过程和函数可以互换,也可以互相调用
/*
select emp.*,count_sal_noout(empno) from emp;
四、触发器
触发器 理解为监视器 监视对表中数据的操作
如果对表中数据操作满足触发器的执行条件 触发器自动运行
创建语法:
create or replace trigger 触发器名
before|after –执行时机
insert|update|delete –监视动作
on 表名 –表级触发器
[for each row] –行级触发器
:new 记录将要变成的数据
:old 记录原始数据
declare
begin
end;
触发器的实际应用
/*
例4真实触发器应用案例
使用触发器实现 id 自增长
*/
create or replace trigger auto_inr
before
insert
on p
for each row
declare
begin
--给插入的记录id 补全 :new.pid
--补全的id 必须 是 自增长的数值、
select order_sequence.nextval into :new.pid from dual;
end;
/*
四、触发器 理解为监视器 监视对表中数据的操作
如果对表中数据操作满足触发器的执行条件 触发器自动运行
创建语法:
create or replace trigger 触发器名
before|after --执行时机
insert|update|delete --监视动作
on 表名 --表级触发器
[for each row] --行级触发器
:new 记录将要变成的数据
:old 记录原始数据
declare
begin
end;
*/
--例1.针对表中数据监视 如果插入数据输出欢迎语句
create or replace trigger inser_tri
after
insert
on p
declare
begin
dbms_output.put_line('欢迎加入');
end;
insert into p values(1,'ss22');
commit;
/*
例2.针对表中数据监视 如果发现是休息日不允许插入数据
raise_application_error(v1,v2);
v1 错误代码 -20000 -20999
v2 错误提示语句
*/
create or replace trigger no_work_day
before
insert
on p
declare
v_day varchar2(10);
begin
--1.先得到当前的星期
select to_char(sysdate,'day') into v_day from dual;
--2.--判断星期是否是休息日
if trim(v_day) in ('friday','sunday') then
--如果休息日阻止插入 谈错误框提示
raise_application_error(-20001,'今天休息 不接待');
end if;
end;
select to_char(sysdate,'day') from dual;
/*
例3.监视表中数据操作 不能给员工降低工资
*/
create or replace trigger can_not_low
before
update
on emp
for each row --行级触发器
declare
begin
--判断修改后工资<原始工资--不允许修改
if :new.sal < :old.sal then
raise_application_error(-20001,'不能给员工降薪');
end if;
end;
--修改SMITH的工资降低200
update emp set sal=sal-200 where ename='SMITH';
/*
例4真实触发器应用案例
使用触发器实现 id 自增长
*/
create or replace trigger au_to_inr
before
insert
on p
for each row
declare
begin
--给插入的记录id 补全 :new.pid
--补全的id 必须 是 自增长的数值、
select order_sequence.nextval into :new.pid from dual;
end;
insert into p(pname) values('ss22');
commit;
五、java调用过程或函数
1.连接数据库的四个属性
1).Oracle驱动 :oracle.jdbc.driver.OracleDriver
2).Oracle连接:jdbc:oracle:thin:@IP地址:1521:orcl
3)user
4)password
2.存储过程/函数的调用
* 1)
* a调用过程格式 {call <procedure-name>[(<arg1>,<arg2>, ...)]}
* b调用函数格式 {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
* 2) 调用过程的对象
* CallableStatement cst = con.prepareCall("{call add_sal (?,?)}");
* 3) 调用过程的赋值
* cst.setInt(1, 7369);
* cst.setInt(2, 100);
* 4)out输出参数必须先注册
* cst.registerOutParameter(2, OracleTypes.NUMBER);
3. 存储过程的结果集为游标
* 1)注册游标类型输出值
* cst.registerOutParameter(2, OracleTypes.CURSOR);
* 2)获取游标
* ResultSet rs = ost.getCursor(2); 必须先转换为 OracleCallableStatement
* OracleCallableStatement ost = (OracleCallableStatement)cst;
* 3)便利游标
* while(rs.next()){
* System.out.println(rs.getInt(1)+"==="+rs.getString("ename"));
}
package cn.it.demo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class TestProcedure {
// 连接数据库的四个属性
// 1).Oracle驱动 :oracle.jdbc.driver.OracleDriver
// 2).Oracle连接:jdbc:oracle:thin:@IP地址:1521:orcl
private String driverClass = "oracle.jdbc.driver.OracleDriver";
private String jdbcUrl = "jdbc:oracle:thin:@192.168.109.128:1521:orcl";
private String user = "it";
private String password = "it";
// 1:oracle数据库的连接
@Test
public void test() {
try {
// 1.注册驱动
Class.forName(driverClass);
// 2.获取链接
Connection con = DriverManager.getConnection(jdbcUrl, user, password);
// 3.获取预编译的statement
PreparedStatement pst = con.prepareStatement("select * from emp");
// 4.执行sql
ResultSet rs = pst.executeQuery();
// 5.处理结果
while (rs.next()) {
System.out.println(rs.getInt(1) + "===" + rs.getString("ename"));
}
// 6.释放资源
rs.close();
pst.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 2.存储过程的调用
* 调用增加工资的存储过程 add_sal(eno in number,addsal in number )
*
* 1) 调用过程格式{call <procedure-name>[(<arg1>,<arg2>, ...)]}
* 2) 调用过程的对象
* CallableStatement cst = con.prepareCall("{call add_sal (?,?)}");
* 3) 调用过程的赋值
* cst.setInt(1, 7369);
* cst.setInt(2, 100);
* 4)out输出参数必须先注册
* cst.registerOutParameter(2, OracleTypes.NUMBER);
*/
@Test
public void callProAddSal() {
try {
// 1.注册驱动
Class.forName(driverClass);
// 2.获取链接
Connection con = DriverManager.getConnection(jdbcUrl, user, password);
// 3.获取预编译的CallableStatement
CallableStatement cst = con.prepareCall("{call ADD_SAL (?,?)}");
// 4.给参数赋值
cst.setInt(1, 7369);
cst.setInt(2, 100);
// 5.执行过程
cst.execute();
// 6.释放资源
cst.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/*
* 3调用存储过程,并获得结果
* 4)out输出参数必须先注册
* cst.registerOutParameter(2, OracleTypes.NUMBER);
* 使用存储过程得到7499的年薪
* countSal(eno in number,year_sal out number)
* */
@Test
public void callProCountSal(){
try{
//1.注册驱动
Class.forName(driverClass);
//2.获取链接
Connection con = DriverManager.getConnection(jdbcUrl, user, password);
//3.获取预编译的CallableStatement
CallableStatement cst = con.prepareCall("{call countSal (?,?)}");
//4.给参数赋值
cst.setInt(1, 7499);
//如果参数为out输出参数必须先注册
cst.registerOutParameter(2, OracleTypes.NUMBER);
//5.执行过程
cst.execute();
System.out.println(cst.getInt(2));
//6.得到输出参数
//6.释放资源
cst.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
/*4.存储过程的结果集为游标
* 1)注册游标类型输出值
* cst.registerOutParameter(2, OracleTypes.CURSOR);
* 2)获取游标
* ResultSet rs = ost.getCursor(2); 必须先转换为 OracleCallableStatement
* OracleCallableStatement ost = (OracleCallableStatement)cst;
* 3)便利游标
* while(rs.next()){
System.out.println(rs.getInt(1)+"==="+rs.getString("ename"));
}
* 使用存储过程得到30号部门的员工
*deptemp(dno in number,deptEmp out sys_refcursor) as
*
*
* */
@Test
public void callProDeptEmp(){
try{
//1.注册驱动
Class.forName(driverClass);
//2.获取链接
Connection con = DriverManager.getConnection(jdbcUrl, user, password);
//3.获取预编译的CallableStatement
CallableStatement cst = con.prepareCall("{call deptemp (?,?)}");
//4.给参数赋值
cst.setInt(1, 30);
//如果参数为out输出参数必须先注册
cst.registerOutParameter(2, OracleTypes.CURSOR);
//5.执行过程
cst.execute();
//如果out参数为游标类型需要使用 CallableStatement的子类 OracleCallableStatement
OracleCallableStatement ost = (OracleCallableStatement)cst;
//6.得到输出参数
ResultSet rs = ost.getCursor(2);
while(rs.next()){
System.out.println(rs.getInt(1)+"==="+rs.getString("ename"));
}
//6.释放资源
rs.close();
ost.close();
cst.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
/*4.调用存储函数得到7499的年薪
* 1)调用函数格式
* {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
* year_sal(eno in number) return number
* */
@Test
public void callFunCountSal(){
try{
//1.注册驱动
Class.forName(driverClass);
//2.获取链接
Connection con = DriverManager.getConnection(jdbcUrl, user, password);
//3.获取预编译的CallableStatement
CallableStatement cst = con.prepareCall(" {?= call year_sal(?)}");
//4.给参数赋值
cst.setInt(2, 7499);
//如果参数为out输出参数必须先注册
cst.registerOutParameter(1, OracleTypes.NUMBER);
//5.执行过程
cst.execute();
System.out.println(cst.getInt(1));
//6.得到输出参数
//6.释放资源
cst.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}