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

上一篇:java JDBC-statement接口实现简单的sql语句调用


下一篇:java之事务