Java,PL/SQL调用 ORACLE存储函数以及存储过程

2023-08-22 21:23:34

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

码农公寓

相关文章