2019-05-13
存储过程是用来完成指定任务的程序，存储在数据库系统中，用户可以远程调用存储过程。
存储过程的优点：
1）存储过程存放于服务器数据库中，一般由客户端进行远程调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；
2）使用存储过程可以减少SQL注入式攻击，提高系统的安全性，执行存储过程的用户要具有权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据；有些企业如金融银行对数据的安全要求比较严格，通常将业务处理包含在存储过程中，不允许程序员直接操作数据表。
定义存储过程
create [or replace] procedure 存储过程名称 [(参数列表)]
as|is [局部变量列表]
begin
存储过程主体代码
end ;
create or replace procedure emp_sal_update_proc is
begin
update scott.emp set sal = sal + 100 ;
commit;
end;
调用存储过程
在PL/SQL程序块中调用存储过程
begin
存储过程名称 [参数];
end ;
在SQLPlus中调用存储过程
SQL> execute 存储过程名称 [参数] ;
删除存储过程
drop procedure 过程名;
存储过程参数
in参数
示例：根据职员编号更新工资
create procedure update_emp_sal_proc(var_empno varchar2, var_newsal number) is
begin
update emp set sal = var_newsal where empno = var_empno;
end;
注意：不能指定char类型和varchar2类型形参的长度，也不能指定number形参的精度和长度。这些约束由实参来传递。
JAVA程序调用存储过程：
Connection con = null;
CallableStatement proc = null;
try {
con = DBConnectionUtil.getConnection();
proc = con.prepareCall("{ call update_emp_sal_proc(?,?) }");
proc.setInt(1, 7369);
proc.setDouble(2, 5000);
proc.execute();// 执行存储过程
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (proc != null) {
proc.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
out参数
create or replace procedure count_emp_proc(cnt out number) as
begin
select count(*) into cnt from scott.emp;
end;
注意：out参数在存储过程中只能被赋值，用于向调用者返回。
Connection con = null;
CallableStatement proc = null;
try {
con = DBConnectionUtil.getConnection();
proc = con.prepareCall("{ call count_emp_proc(?) }");
proc.registerOutParameter(1, Types.INTEGER); //注册输出参数
proc.execute();// 执行存储过程
int cnt = proc.getInt(1);
System.out.println("员工数量：" + cnt);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (proc != null) {
proc.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
返回结果集
(1)定义动态游标
create or replace package pkg1
is
type query_emp_cursor is ref cursor;
end pkg1;
(2)定义存储过程
create or replace procedure query_emp_proc
(var_cursor out pkg1.query_emp_cursor) is
begin
open var_cursor for select * from scott.emp;
end;
Connection con = null;
CallableStatement proc = null;
ResultSet rs = null;
try {
con = DBConnectionUtil.getConnection();
proc = con.prepareCall("{ call query_emp_proc(?) }");
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.execute();// 执行存储过程
rs = (ResultSet) proc.getObject(1);
System.out.println("编号\t姓名\t职位");
while (rs.next()) {
System.out.print(rs.getString(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getString(3) + "\t");
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (proc != null) {
proc.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
函数
函数与存储过程类似，它也是一种存储在数据库中的命名程序块，并且函数也可以接受零个或多个输入参数。函数与过程之间的主要区别在于，函数必须有返回值。
create [or replace] function 函数名(参数列表)
return 返回值类型 as [局部变量]
begin
函数体代码
end ;
示例：统计某部门职员的平均工资
create or replace function dept_sal_avg_fun(var_deptno number)
return number as var_result number;
begin
select avg(sal) into var_result from scott.emp
where emp.deptno = var_deptno;
return var_result;
end;
调用函数：
begin
dbms_output.put_line(dept_sal_avg_fun(10));
end;
