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

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

        ONE Goal , ONE Passion !

准备工作

• 创建表

    --- 创建测试用表 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.什么是存储函数

  存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用PL/SQL进行逻辑的处理。

存储函数语法结构

    CREATE [ OR REPLACE ] FUNCTION 函数名称
        （参数名称 [in|out|in out] 参数类型,...）
        RETURN 
        结果变量数据类型
        IS
        变量声明部分;
        BEGIN
        逻辑部分;
        RETURN 
        结果变量;
        [EXCEPTION 
        异常处理部分
        ]
       END;

---

创建函数

• 此处只是返回了一个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;

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.什么是存储过程

存储过程是被命名的PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如
下：

• 1.存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过
  传出参数返回多个值。

• 2.存储函数可以在select语句中直接使用，而存储过程不能。过程多数是
  被应用程序所调用。

• 3.存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

---

存储函数语法结构

        CREATE [ OR REPLACE ] PROCEDURE 存储过程名称（参数名 类型, 
        参数名 类型,参数名 类型...）
        IS|AS
        变量声明部分;
        BEGIN
        逻辑部分
        [EXCEPTION 
        异常处理部分
        ]
        END;

注意:

1.参数只指定类型,不指定长度

2.过程参数的三种模式：
IN :传入参数（默认）
OUT :传出参数主要用于返回程序运行结果
IN OUT :传入传出参数

一:不带传出参数

CREATE OR REPLACE PROCEDURE pro_insert(
    ID NUMBER,
    NAME VARCHAR2   ----注意这里不带长度,不能写成(VARCHAR(30))
    )
    AS
    BEGIN
     INSERT INTO school values(ID,NAME);
    END;

PL/SQL调用过程

   CALL pro_insert(4,'郑州大学'); 

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

---

二:带传出参数

---.  带传出参数
    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;

PL/SQL调用过程

--- PL/SQL调用
    declare
    s_count number;  --定义传出参数的变量
    begin
    pro_getCount('大学',s_count); --- 执行过程
    DBMS_OUTPUT.put_line('查询结果,数量:'||s_count);    ---打印结果
    end;

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

二:带传出参数

 ---- 返回值为游标

    CREATE OR REPLACE PROCEDURE pro_cursor(s_cur OUT SYS_REFCURSOR)
    AS
    BEGIN
      OPEN s_cur FOR SELECT * FROM school;
    END;

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; 

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