Oracle之存储过程

Oracle（持续更新中）

《Oracle11g之安装与卸载》
《Oracle之常用命令》
《Oracle之用户管理》
《Oracle之pl/sql》
《Oracle之块》
《Oracle之存储过程》

---

　　存储过程用于执行特定的操作。当建立存储过程时，既可以指定输入参数(in)，也可以指定输出参数(out)。
通过在存储过程中使用输入参数，可以将数据传递到执行部分。通过使用输出参数，可以将执行部分的数据传递到应用环境。在sql/plus中可以使用create procedure 命令来建立过程。

优点：sql语句每次都需要被编译，过程只需要编译一次。实现模块化、减少网络传输量、提高安全性（在Java中调用只需要传参数，而不需要传完整的sql语句）。

缺点：移植性不好，如：更换数据库，所有存储过程需手动导入

目录

一、插入例子

1. 创建存储过程
2. 执行存储过程
3. java中调用存储过程

二、查询例子

1. 查询结果单一
2. 查询结果多个

---

一、插入例子

　　插入的存储过程与删除、更改类似。

1、创建存储过程

　　代码如下：

create or replace procedure p_insert(param1 in varchar2) is
begin
  insert into data(info) values(param1);
end;
/--以/作为结束，不能直接复制，后续单独加上
 
 
1
2
3
4
5
 
 
1
2
3
4
5

　　解析：
　　　　create or replace ：代表创建或替换，也可单独使用create
　　　　p_insert ：代表存储过程名
　　　　varchar2：Oracle专有的字符数据类型
　　　　param1：输入参数名

2、执行存储过程

　　可以使用 call或exe[cute]执行存储过程

　　如图所示：

3、java中调用存储过程

　　代码如下：

Class.forName("oracle.jdbc.driver.OracleDriver");//加载驱动
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");//获得连接

CallableStatement  cstm = conn.prepareCall("{call p_insert(?)}");//连接对应的存储过程
cstm.setString(1, "332211");//插入数据，该参数在第一位，则第一个参数为1
cstm.execute();//执行
 
 
1
2
3
4
5
6
 
 
1
2
3
4
5
6

二、查询例子

　　补充：data表是博主自己创建的，内容在上面图中有给出。

1、查询结果单一

　　（1）创建存储过程

　　　　代码如下：

create or replace procedure p_query1(param1 in varchar2,param2 out varchar2) is
begin
--查询结果使用into 放入param2变量（查询结果单一）
  select info into param2 from data where info=param1;
end;
/
 
 
1
2
3
4
5
6
 
 
1
2
3
4
5
6

　　（2）java中调用存储过程

　　　　代码如下：

        Class.forName("oracle.jdbc.driver.OracleDriver");//加载驱动
        Connectionconn conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");//获得连接

        CallableStatement  cstm = conn.prepareCall("{call p_query1(?,?)}");//连接对应的存储过程p_query1
        cstm.setString(1, "ccc");//设置查询数据为ccc，该参数在第1位
        cstm.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); //设置返回类型
        cstm.execute();//执行
        String result = cstm.getString(2);//得到返回数据
        System.out.println("执行结果："+result);
 
 
1
2
3
4
5
6
7
8
9
 
 
1
2
3
4
5
6
7
8
9

java代码中，设置返回类型要与创建存储过程中时一致。

　　　　结果如图所示：

2、查询结果多个

　　（1）创建package

　　　　包名为pack_info，内部定义个游标类型。

create or replace package pack_info is
--定义一个游标类型，名为c_info
type c_info is ref cursor;
end pack_info;
/
 
 
1
2
3
4
5
 
 
1
2
3
4
5

　　（2）创建存储过程

create or replace procedure p_query2(param1 in varchar2,param2 out pack_info.c_info) is
begin
--打开游标执行查询语句
 open param2 for select * from data where info=param1;
end;
/
 
 
1
2
3
4
5
6
 
 
1
2
3
4
5
6

pack_info.c_info：代表使用pack_info中的c_info类型，该类型为游标类型。

　　（3）java中调用存储过程

　　　　代码如下：

        Class.forName("oracle.jdbc.driver.OracleDriver");//加载驱动
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");//获得连接

        CallableStatement  cstm = conn.prepareCall("{call p_query2(?,?)}");//连接对应的存储过程
        cstm.setString(1, "ccc");//设置查询数据，该参数在第1位
        cstm.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR); //设置返回类型为游标
        cstm.execute();//执行

        ResultSet result = (ResultSet) cstm.getObject(2);//得到返回数据
        while (result.next()) {
            System.out.println(result.getString("info"));
            System.out.println(result.getString("num"));    
        }
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13

　　　　结果如图所示：