指存储在数据库中的供所有用户程序带哦用的子程序(PL/SQL)叫存储过程(不能返回值)、存储函数(可以通过return语句返回值)
1、存储过程
为了完成特定功能的SQL语句集,经编译后存储在数据库中。
(1)新建:
(2)书写存储过程的代码:
create or replace procedure raiseSalary(eno in number)
is
psal emp.sal%type;
begin
select sal into psal from emp where empno=eno;
update emp set sal= sal + 100 where empno = eno ;
dbms_output.put_line('前:'||psal||'后:'||(psal+100));
end raiseSalary;
(3)编译运行代码:
(4)调用存储过程:
2、存储函数
存储函数与存储过程的结构类似,但是必须有一个return子句,用于返回函数值。
(1)创建一个存储函数:
(2)书写代码:
create or replace function queryEmpIncome(eno in number)
return number
is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
return psal*12+nvl(pcomm,0);
end queryEmpIncome;
(3)右键选择test:
3、存储过程和存储函数的OUT
(1)创建存储过程:
(2)书写程序:查询员工的信息
create or replace procedure queryEmpInfeno(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
is
begin
select ename,sal,job into pename,psal,pjob from emp where empno=eno;
end queryEmpInfeno;
(3)测试结果:
4、java程序调用存储过程和存储函数
(1)先在虚拟机中找到需要导入的jar包并进行导入:
(2)书写一个工具类:
package pers.zhb.utils;
import java.sql.*;
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.125.129:1521/orcl";
private static String user = "scott";
private static String password = "tiger";
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
(3)创建测试类,调用存储过程和存储函数:
public class Test {
public void testProcedure(){
String sql = "{call raiseSalary(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.setInt(1,7839);
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
call.execute();
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name+"\t"+sal+"\t"+job);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
public void testFunction(){
String sql = "{?=call queryEmpIncome(?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.NUMBER);
call.setInt(2, 7839);
call.execute();
double income = call.getDouble(1);
System.out.println(income);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
public static void main(String [] args){
Test test=new Test();
test.testFunction();
}
}