Oracle存储过程的实现

Oracle存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（是否给参数要看该存储过程定义的过程中是否设置了参数）来执行它。

准备工作

创建一张测试表 students

1
2
3
4
5
6
7
8
9
10
create table STUDENTS(
  id      VARCHAR2(50) default sys_guid() not null,
  name    VARCHAR2(20),
  age     NUMBER(4,1),
  school  VARCHAR2(100),
  grade   VARCHAR2(50),
  address VARCHAR2(500),
  remarks VARCHAR2(500),
  ts      CHAR(19) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
)

插入一条测试数据。当然，也可以插入自己想插入的内容。

1
2
insert into STUDENTS (id, name, age, school, grade, address, remarks, ts)
values ('8A17DE17428E45D6E0530100007FABEB', 'xiaoming', 20, 'Changchun University of Architecture', 'Junior', 'Changchun, Jilin', null, '2019-06-12 11:07:57');

第一个简单的存储过程

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE stu_school
AS school_name VARCHAR2(100);
BEGIN
  SELECT school INTO school_name FROM students WHERE ID='8A17DE17428E45D6E0530100007FABEB';
  dbms_output.put_line(school_name);
END;

执行存储过程，可以在PLSQL对象中看到我们刚才新创建的存储过程，并且没有报错，代表编译成功。

简单的存储过程

执行存储过程

1
CALL stu_school();

调用时，"()"是必不可少的，无论是有参数还是无参数

在SQL窗口输出页签中可以看到正确的输出内容

输出

四种存储过程

存储过程有一下四种情况 - 无参数存储过程 - 仅有输入参数存储过程 - 仅有输出参数存储过程 - 既有输入又有输出存储过程

下面将对这四种存储过程分别举例说明

无参数存储过程

无参数存储过程就如上面写的那个简单的存储过程，也可以这样写：

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE stu_school
AS school_name students.school%TYPE;
BEGIN
  SELECT school INTO school_name FROM students WHERE ID='8A17DE17428E45D6E0530100007FABEB';
  dbms_output.put_line(school_name);
END;

仅有输入参数存储过程

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE stu_address(stu_id IN students.id%TYPE)
AS addr students.address%TYPE;
BEGIN
  SELECT address INTO addr FROM students WHERE ID=stu_id;
  dbms_output.put_line(addr);
END;

执行存储过程

1
CALL stu_address('8A17DE17428E45D6E0530100007FABEB');
仅有输入参数存储过程

仅有输出参数存储过程

1
2
3
4
5
CREATE OR REPLACE PROCEDURE stu_age(stu_age OUT students.age%TYPE)
AS
BEGIN
  SELECT age INTO stu_age FROM students WHERE ID='8A17DE17428E45D6E0530100007FABEB';
END;

需要注意的是，此种存储过程不能直接通过call来调用，需要通过一下方式执行

注意，如果通过这种方式执行存储过程，要记得在存储过程中添加输出语句，不然的话，纵然执行成功，也没有结果输出。 dbms_output.put_line(stu_age);

1
2
3
4
5
DECLARE
stuage students.age%TYPE;
BEGIN
  stu_age(stuage);
END;

或者通过oracle函数调用带有输出参数的存储过程

1
2
3
4
5
CREATE OR REPLACE FUNCTION get_stuage(stuage OUT NUMBER) RETURN NUMBER IS
BEGIN
  stu_age(stuage);
  RETURN stuage;
END;

执行函数

1
2
3
4
5
6
DECLARE
  stuage students.age%TYPE;
BEGIN
  dbms_output.put_line('return result:' || get_stuage(stuage));
END;

既有输入又有输出参数的存储过程

1
2
3
4
5
CREATE OR REPLACE PROCEDURE stu_name(stuid IN students.id%TYPE, stuname OUT students.name%TYPE)
AS
BEGIN 
  SELECT NAME INTO stuname FROM students WHERE ID=stuid;
END;

新建存储函数调用存储过程

1
2
3
4
5
CREATE OR REPLACE FUNCTION get_stuname(stuid IN students.id%TYPE, stuname OUT students.name%TYPE) RETURN VARCHAR2 IS
BEGIN
  stu_name(stuid, stuname);
  RETURN stuname;
END;

执行函数

1
2
3
4
5
DECLARE
  stuname students.name%TYPE;
BEGIN
  dbms_output.put_line('The student name is:' || get_stuname('8A17DE17428E45D6E0530100007FABEB', stuname));
END;

Java调用存储过程

Java调用仅有输出参数的存储过程

针对存储过程 stu_age

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE stu_age(stu_age OUT students.age%TYPE)
AS
BEGIN
  SELECT age INTO stu_age FROM students WHERE ID='8A17DE17428E45D6E0530100007FABEB';
END;

Java代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
private void OnlyOutputProcedure() {
    try {
        Class.forName(DRVIER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "{call stu_age(?)}";
        CallableStatement statement = connection.prepareCall(sql);
        statement.registerOutParameter(1, OracleTypes.NUMBER);
        statement.execute();
        System.out.println(statement.getInt(1));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

Java调用既有输入参数又有输出参数的存储过程

针对存储过程 stu_name

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE stu_name(stuid IN students.id%TYPE, stuname OUT students.name%TYPE)
AS
BEGIN
  SELECT NAME INTO stuname FROM students WHERE ID=stuid;
END;

Java代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
private void InAndOutputProcedure() {
    try {
        Class.forName(DRVIER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "{call stu_name(?,?)}";
        CallableStatement statement = connection.prepareCall(sql);
        statement.setString(1, "8A17DE17428E45D6E0530100007FABEB");
        statement.registerOutParameter(2, OracleTypes.VARCHAR);
        statement.execute();
        System.out.println(statement.getString(2));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

以上是有OUT输出参数的存储过程，Java在调用存储过程后还会获得存储过程返回的参数。那么如果存储过程没有OUT输出参数怎么办？

Java调用仅有输入参数的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
private void OnlyInputProcedure() {
    try {
        Class.forName(DRVIER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "{call stu_address(?)}";
        CallableStatement statement = connection.prepareCall(sql);
        statement.setString(1, "8A17DE17428E45D6E0530100007FABEB");
        statement.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

Java调用无参的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
private void NoParameterProcedure() {
    try {
        Class.forName(DRVIER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "{call stu_school()}";
        CallableStatement statement = connection.prepareCall(sql);
        statement.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

Java调用存储函数

创建一个存储函数 get_address 调用

1
2
3
4
5
6
7
create or replace function get_address(stuid in students.id%type) return varchar2 is
stuaddress students.address%type;
begin
  select address into stuaddress from students where id=stuid;
  return stuaddress;
end;

Java调用存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
private void NoParameterProcedure() {
    try {
        Class.forName(DRVIER);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "{?=call get_address(?)}";
        CallableStatement statement = connection.prepareCall(sql);
        statement.registerOutParameter(1, Types.VARCHAR);
        statement.setString(2, "8A17DE17428E45D6E0530100007FABEB");
        statement.execute();
        System.out.println(statement.getString(1));
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

这里有一个关于Oracle存储过程的PPT文档，供大家下载学习点击下载

⬆︎TOP