【传智播客郑州校区】Java PL/SQL调用 ORACLE存储函数以及存储过程

【郑州校区】Java,PL/SQL调用 ORACLE存储函数以及存储过程 文/传智播客郑州校区就业服务部

1.Java,PL/SQL调用 ORACLE存储函数以及存储过程1.1.1.准备工作 ·创建表 --- 创建测试用表 school CREATE TABLE school( ID number, --学校id NAME VARCHAR2(30) ---学校名 ); --- 添加数据 INSERT into school values(1,'北京大学'); INSERT into school values(2,'南京大学'); INSERT into school values(1,'东京大学'); COMMIT; ·编写java代码连接数据库,获取Connection连接对象 public class OracleUtil { //加载Oracle驱动 static { try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } // 获取数据库连接 public static Connection getConnection() throws SQLException { Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@iP:1521:orcl", "system", "密码"); return connection; } } 1.1.——-存储函数——- · 1.什么是存储函数 · 存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用PL/SQL进行逻辑的处理。 · 存储函数语法结构 CREATE [ OR REPLACE ] FUNCTION 函数名称 （参数名称 [in|out|in out] 参数类型,...） RETURN 结果变量数据类型 IS 变量声明部分; BEGIN 逻辑部分; RETURN 结果变量; [EXCEPTION 异常处理部分 END; 1.1.1.创建函数 ·此处只是返回了一个varchar2的数据. 返回值可以为cursor,table. --- 创建函数. 根据地址ID查询地址名称 CREATE OR REPLACE FUNCTION fn_getName(sid NUMBER) RETURN VARCHAR2 ---返回值类型 AS r_name VARCHAR2(30);---声明变量 BEGIN SELECT name INTO r_name FROM school WHERE id=sid; ---将查询的name存储到r_name中 RETURN r_name; --返回r_name END; 1.1.2.PL/SQL调用oracle函数 --- PL/SQL调用过程 使用函数,传参 id = 2 select fn_getName(2) 学校 from dual; java代码调用oracle函数 /** *调用ORACLE函数 * * @param sid */ public static void getenamefun(int sid) { String sname; java.sql.Connection conn = null; java.sql.PreparedStatement stmt = null; String sql = "{?=call fn_getName(?)}"; //调用方式 CallableStatement fun = null; try { conn = OracleUtil.getConnection(); fun = conn.prepareCall(sql); fun.setInt(2, sid); fun.registerOutParameter(1, Types.VARCHAR);//注册传出参数类型 fun.execute(); sname = fun.getString(1); //取返回的参数结果 System.out.println(sname); } catch (SQLException e) { e.printStackTrace(); } } 1.2.——-存储过程——- · 1.什么是存储过程 存储过程是被命名的PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。 存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如

下： 1.存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过

传出参数返回多个值。 2.存储函数可以在select语句中直接使用，而存储过程不能。过程多数是

被应用程序所调用。 3.存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码 存储函数语法结构 CREATE [ OR REPLACE ] PROCEDURE存储过程名称（参数名类型, 参数名类型,参数名 类型...） IS|AS 变量声明部分; BEGIN 逻辑部分 [EXCEPTION 异常处理部分 END; 注意: 1.参数只指定类型,不指定长度 2.过程参数的三种模式：

IN :传入参数（默认）

OUT :传出参数主要用于返回程序运行结果

IN OUT :传入传出参数 1.3.一:不带传出参数 CREATE OR REPLACE PROCEDURE pro_insert( ID NUMBER, NAME VARCHAR2 ----注意这里不带长度,不能写成(VARCHAR(30)) ) AS BEGIN INSERT INTO school values(ID,NAME); END; 1.3.1.PL/SQL调用过程 CALL pro_insert(4,'郑州大学'); · 1 1.3.2.java代码调用oracle函数 public static void callPr() { java.sql.Connection conn = null; java.sql.PreparedStatement stmt = null; try { conn = OracleUtil.getConnection(); stmt = conn.prepareCall("{ call pro_insert(?,?) }"); //设置存储过程call为关键字. stmt.setInt(1, 4); //设置第一个输入参数 stmt.setInt(2, "郑州大学");//设置第二个输入参数 stmt.execute();//执行 } catch (SQLException e) { e.printStackTrace(); } } 1.4.二:带传出参数 ---.带传出参数 CREATE OR REPLACE PROCEDURE pro_getCount(s_name VARCHAR2,s_count OUT NUMBER) AS BEGIN SELECT COUNT(*) INTO s_count FROM school WHERE name LIKE '%'||s_name||'%'; END; 1.4.1.PL/SQL调用过程 --- PL/SQL调用 declare s_count number;--定义传出参数的变量 begin pro_getCount('大学',s_count); ---执行过程 DBMS_OUTPUT.put_line('查询结果,数量:'||s_count); ---打印结果 end; 1.4.2.java代码调用oracle函数 public static void pro_Gount(String name) { java.sql.Connection conn = null; java.sql.CallableStatement stmt = null; try { conn = DaoUtil.getConnection(); stmt = conn.prepareCall("{ call pro_getCount(?,?) }"); //设置存储过程call为关键字. stmt.setString(1, "大学"); //设置第一个输入参数 stmt.registerOutParameter(2,OracleTypes.NUMBER);//设置第二个输入参数 stmt.execute();//执行. //执行完毕后,从占位符中去对应的列就可以了 int count = stmt.getInt(2); System.out.println("查询结果"+ count); // 4 } catch (SQLException e) { e.printStackTrace(); } } 1.5.二:带传出参数 ---- 返回值为游标 CREATE OR REPLACE PROCEDURE pro_cursor(s_cur OUT SYS_REFCURSOR) AS BEGIN OPEN s_cur FOR SELECT * FROM school; END; 1.5.1.PL/SQL调用过程 declare s_cur SYS_REFCURSOR;--定义传出参数的变量 s_row school%ROWTYPE; --将传出参数赋值给s_row begin pro_cursor(s_cur); --执行过程 loop--循环结果 FETCH s_cur into s_row; EXIT WHEN s_cur%NOTFOUND; dbms_output.put_line(s_row.name); end loop; end; 1.5.2.java代码调用oracle函数 /** *返回结果为cursor */ public static void pro_cur() { java.sql.Connection conn = null; java.sql.CallableStatement stmt = null; try { conn = DaoUtil.getConnection(); stmt = conn.prepareCall("{ call pro_cursor(?) }"); //设置存储过程call为关键字 stmt.registerOutParameter(1,OracleTypes.CURSOR);//设置第一个输入参数 stmt.execute();//执行. //执行完毕后,从占位符中去对应的列就可以了 ResultSet resultSet = (ResultSet) stmt.getObject(1); while (resultSet.next()){ String id = resultSet.getString("id"); String name = resultSet.getString("name"); System.out.println("序号"+ id + "学校名"+ name); } } catch (SQLException e) { e.printStackTrace(); } }

传智播客·黑马程序员郑州校区

高新区长椿路11号大学科技园（西区）东门8号楼三层

地铁一号线梧桐街站A口出

联系方式0371-56061160/61/62