Oracle学习笔记 -- day08 PL/SQL语法之存储过程、存储函数

一、存储过程

1、定义

存储过程是一段被命名化的 plsql 语句，是预编译到数据库中的

2、创建语法

create or replace  procedure 存储过程名(参数1  [in] /out 数据类型...)   --  in 是输入类型的参数，可以省略，默认为 in ，  out 是输出类型的参数

as（is）     -- as/is的作用替代了declare

begin  

    执行的动作

end；

3、存储过程的创建

示例：用储存过程计算指定员工的年薪

create or replace  procedure pro_yearsal(v_no   number)is  --替代了declare v_yearsal number(8,2); begin  select sal*12+nvl(comm,0) into v_yearsal from emp where empno=v_no;  dbms_output.put_line('员工年薪是：'||v_yearsal);end;

4、存储过程的使用

（1）、使用 call 关键字

call pro_yearsal(7788);call pro_yearsal(7369);

（2）、使用 plsql 语法

begin   pro_yearsal(7369);end;

二、带out参数的存储过程应用

1、示例：用储存过程计算指定员工的年薪  把计算出来的年薪放到out参数中

create or replace  procedure pro_yearsal2(v_no number,v_year_sal out number)is  --替代了declare begin  select sal*12+nvl(comm,0) into v_year_sal from emp where empno=v_no; -- dbms_output.put_line('员工年薪是：'||v_yearsal);end;

2、使用存储过程

注意：调用带out参数的存储过程的时候，不能使用 call 的方式

declare  v_yearsal number(8,2);begin  pro_yearsal2(7788,v_yearsal);  -- 执行完存储过程之后，v_yearsal变量才会有值  dbms_output.put_line(v_yearsal);end;

三、存储函数

1、语法

create or replace function 存储函数名称(参数1 [in] / out 数据类型)

return 数据类型

is（as）

begin  

  return 具体的值;

end;

2、创建存储函数

示例：计算指定员工的年薪

create or replace function  func_yearsal(v_no in number )return number is v_sal number(8,2);beginselect sal*12+nvl(comm,0) into v_sal from emp where empno=v_no;  return v_sal;end;

3、使用存储函数

declare v_year_sal number(8,2);begin v_year_sal:= func_yearsal(7788); dbms_output.put_line(v_year_sal);end;

四、存储函数和存储过程的区别

1、语法上不同

2、使用场景不同，

        存储函数多被存储过程调用，

        项目与项目之间的相互调用使用的是存储过程

3、存储函数可以在sql中使用，存储过程不能在sql中使用

五、使用jdbc的方式调用存储函数和存储过程

1、导入jar包

2、创建工具类 ：BaseDao

package com.itcast.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class BaseDao {/** * 加载驱动 */static{try {Class.forName("oracle.jdbc.OracleDriver");} catch (ClassNotFoundException e) {e.printStackTrace();}}/** * 获取连接 * @return * @throws SQLException */public static Connection getConn() throws SQLException{String url="jdbc:oracle:thin:@192.168.204.10:1521:orcl";String user="scott";String password="tiger";return DriverManager.getConnection(url, user, password);}public static void closeAll(ResultSet rs,Statement stmt,Connection conn){if(rs!=null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if(stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

3、测试类普通sql

package com.itcast.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class EmpDao {/** * 输出指定部门的员工信息 * @param deptno */public static void getEmp(Long deptno){Connection conn = null;PreparedStatement stmt =null;ResultSet rs = null;try {conn = BaseDao.getConn();    stmt = conn.prepareStatement("select * from emp where deptno=?");    stmt.setLong(1, deptno);    rs = stmt.executeQuery();while(rs.next()){System.out.println(rs.getLong(1)+rs.getString("ENAME"));}} catch (SQLException e) { e.printStackTrace();}finally {BaseDao.closeAll(rs, stmt, conn);}}}

4、调用存储过程

package com.itcast.dao;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import oracle.jdbc.driver.OracleTypes;public class ProcedureDao {//调用的存储过程：procedure pro_yearsal2(v_no number,v_year_sal out number)/** * 根据员工编号，查询年薪，调用的存储过程是pro_yearsal2 */public static void getyearsal(Long v_no){Connection conn = null;CallableStatement stmt = null;   //CallableStatement 用来出来处理存储过程、存储函数Long yearsal = 0l;//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}//{call <procedure-name>[(<arg1>,<arg2>, ...)]}try {conn = BaseDao.getConn();//procedure pro_yearsal2(v_no number,v_year_sal out number)stmt = conn.prepareCall("{call pro_yearsal2(?,?)}");stmt.setLong(1, v_no);stmt.registerOutParameter(2, OracleTypes.NUMBER);  // 对out参数指定数据类型stmt.execute();  //本行代码执行完成之后，out参数中已经有值yearsal = stmt.getLong(2); //获取out参数中的值System.out.println(yearsal);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {BaseDao.closeAll(null, stmt, conn);}}}

5、调用存储函数

package com.itcast.dao;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import oracle.jdbc.driver.OracleTypes;public class FunctionDao {//function  func_yearsal(v_no in number )/** * 调用的存储函数是func_yearsal */public static void getyearsal(Long v_no){Connection conn = null;CallableStatement stmt = null;   //CallableStatement 用来出来处理存储过程、存储函数Long yearsal = 0l;//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} -- 存储函数写法//{call <procedure-name>[(<arg1>,<arg2>, ...)]}  -- 存储过程写法try {conn = BaseDao.getConn();stmt = conn.prepareCall("{?=call func_yearsal(?)}");stmt.setLong(2, v_no);stmt.registerOutParameter(1, OracleTypes.NUMBER);  // 对返回值参数指定数据类型stmt.execute();yearsal = stmt.getLong(1);System.out.println(yearsal);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {BaseDao.closeAll(null, stmt, conn);}}}

6、调用out类型参数是游标的存储过程

package com.itcast.dao;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import oracle.jdbc.driver.OracleCallableStatement;import oracle.jdbc.driver.OracleTypes;public class ProcedureCursorDao {// pro_cursor(v_no in number,v_cursor out sys_refcursor)/** * 调用的存储过程是pro_cursor */public static void getEmpInfo(Long v_no){Connection conn = null;CallableStatement stmt = null;   //CallableStatement 用来出来处理存储过程、存储函数ResultSet rs = null;//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}//{call <procedure-name>[(<arg1>,<arg2>, ...)]}try {conn = BaseDao.getConn();//procedure pro_yearsal2(v_no number,v_year_sal out number)stmt = conn.prepareCall("{call pro_cursor(?,?)}");stmt.setLong(1, v_no);stmt.registerOutParameter(2, OracleTypes.CURSOR);  // 对out参数指定数据类型stmt.execute();rs = ((OracleCallableStatement)stmt).getCursor(2);while(rs.next()){System.out.println(rs.getLong(1)+"--"+rs.getString("ENAME"));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {BaseDao.closeAll(rs, stmt, conn);}}}