Oracle的存储过程（procedure）

创建

?

1 2 3 4 5 6 7 8 9

--存储过程定义 create or replace procedure proce01(a in varchar2, b in out varchar2) as   begin    -- b := a + b; -- 会将 字符转换为数值 ，注意a为in，所以不能被赋值，这与c里的函数不同    b := a || b;    dbms_output.put_line('a:'||a||'   b:'||b); end proce01;

执行

?

1 2 3 4 5 6 7 8

-- 执行 declare      a  varchar2(20):= '01';      b  varchar2(20):= 'bg'; begin      proce01(a ,b);     dbms_output.put_line('a:'||a||'   b:'||b); end;

执行结果：
a:01 b:01bg
a:01 b:01bg

删除存储过程

?

1 2	-- 删除存储过程 DROP PROCEDURE proce01

 

 

 

存储过程返回多条记录

Oracle 参照游标(SYS_REFCURSOR)使用

 

-- 创建 procedure
create or replace procedure getResult(p_cursor out sys_refcursor)
as
begin
  open p_cursor for
  select * from user_info;
end;

 

 

-- 使用存储过程

-- 使用存储过程
declare
  v_cursor sys_refcursor;
  u user_info%rowtype;
begin
  getResult(v_cursor);
  loop 
  fetch v_cursor into u.user_id, u.user_name,u.sex;          
  exit when v_cursor%notfound;
  dbms_output.put_line(u.user_id||'-'||u.user_name||'-'||u.sex);
  end loop;
end;

 

Java中调用存储过程

 

参考：java调用oracle存储过程返回多条结果集 - BorD的选择 - 博客园

package vip.yaocn.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 * 存储过程测试
 *
 * @author yacon
 */
public class ProcedureTest {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String driverName = "oracle.jdbc.driver.OracleDriver";
        String username = "scott";
        String password = "tiger";
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, username, password);
            String callSql = "{call pack_myPackage.proc_getSomeInfo(?, ?) }";
            cstmt = conn.prepareCall(callSql);
            cstmt.setString(1, "1001");
            cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            cstmt.execute();
            rs = (ResultSet) cstmt.getObject(2);
            if (rs != null) {
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (cstmt != null) {
                    cstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}