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();? ?? ???}? ? }