指存储在数据库中的供所有用户程序带哦用的子程序(PL/SQL)叫存储过程(不能返回值)、存储函数(可以通过return语句返回值)
1、存储过程
为了完成特定功能的SQL语句集,经编译后存储在数据库中。
(1)新建:
(2)书写存储过程的代码:
create or replace procedure raiseSalary(eno innumber)ispsal emp.sal%type;
beginselect sal into psal from emp where empno=eno;
update empset 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 innumber)returnnumberispsal emp.sal%type;
pcomm emp.comm%type;
beginselect 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 innumber,
penameoutvarchar2,
psaloutnumber,
pjoboutvarchar2)isbeginselect ename,sal,job into pename,psal,pjob from emp where empno=eno;
end queryEmpInfeno;
(3)测试结果:
4、java程序调用存储过程和存储函数
(1)先在虚拟机中找到需要导入的jar包并进行导入:
(2)书写一个工具类:
packagepers.zhb.utils;import java.sql.*;public classJDBCUtils {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 newExceptionInInitializerError(e);
}
}public staticConnection getConnection(){try{returnDriverManager.getConnection(url, user, password);
}catch(SQLException e) {
e.printStackTrace();
}return null;
}public static voidrelease(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 classTest {public voidtestProcedure(){
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+""+sal+""+job);
}catch(Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call,null);
}
}public voidtestFunction(){
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 voidmain(String [] args){
Test test=newTest();
test.testFunction();
}
}