C#/java 执行oracle package

使用pl/sql创建package

CREATE OR REPLACE PACKAGE FirstPage is

 type outlist is ref cursor;
 
 Procedure p_get( maxrow in number, minrow in number, return_list out outlist );
 
 function f_get(str in varchar2)return varchar2;

END FirstPage;

CREATE OR REPLACE package body FirstPage is 

Procedure p_get( maxrow in number, minrow in number, return_list out outlist ) 
is 
begin 
      open return_list for 
      select * from (select a.*,rownum rnum from IPS_WL_INNOLUXPN a where rownum<=maxrow) where rnum >=minrow; 
end ;
 
 Function f_get(str in varchar2)
 return varchar2 
 is
 str_temp varchar2(200) := 'Good Luck!';
 begin
       str_temp := str_temp || str;
      return str_temp;
 end f_get;
 
      
end FirstPage;

JAVA部分

新建 Java Project

添加包 demo

添加一个class，勾上生成Main函数

代码如下

 

package demo;

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

public class test {

    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        DriverManager.registerDriver (new oracle.jdbc.OracleDriver());   

        Connection  conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.15.50:1521:orcl","aaaa","aaaa");
        String sqlStr = "{call FIRSTPAGE.p_get(?,?,?)}";
        CallableStatement cs = conn.prepareCall(sqlStr);
           
           cs.setInt(1,50);
           cs.setInt(2,10);
           
           cs.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
           
           cs.execute();
           ResultSet rs = (ResultSet)cs.getObject(3);
           int rowCount= 0;
           while(rs.next()) {
               
               System.out.println(rs.getString(7));
           } 
           System.out.print(rowCount);
    }

}

 C#部分

using System;using System.Text;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {

            string connString = "User ID=aaaa;Password=aaaa;Data Source=aaq";
            var conn = new OracleConnection(connString);
            try
            {
                conn.Open();
                using (var cmd = new OracleCommand("FIRSTPAGE.p_get", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    var p1 = new OracleParameter("maxrow", OracleType.Number)
                    {
                        Direction = ParameterDirection.Input,
                        Value = 50
                    };
                    var p2 = new OracleParameter("minrow", OracleType.Number)
                    {
                        Direction = ParameterDirection.Input,
                        Value = 10
                    };
                    var p3 = new OracleParameter("return_list", OracleType.Cursor)
                    {
                        Direction = ParameterDirection.Output
                    };
                    cmd.Parameters.Add(p1);
                    cmd.Parameters.Add(p2);
                    cmd.Parameters.Add(p3);

                    var dt = new DataTable();
                    var da = new OracleDataAdapter(cmd);
                    da.Fill(dt);
                    Console.WriteLine("All rows : {0}",dt.Rows.Count);
                }
                Console.WriteLine(conn.State.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
            }
            finally
            {
                conn.Close();
            }
            Console.Read();
        }
    }
}