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