精华内容
下载资源
问答
  • oracle函数调用存储过程.doc
  • java调用oracle存储过程或者函数
  • 如何调用oracle函数存储过程
  • ibatis调用oracle函数存储过程的方法,从网上收集的,还没有整理。
  • oracle 函数调用存储过程

    千次阅读 2012-04-04 19:26:57
    开始是使用拼接sql的方式在存储过程中对应sql的外面包装一层来进行处理,但是觉得这种方式拼接的sql太长,而且以后业务逻辑改变要同时修改存储过程和代码,容易发生遗漏,故产生了在函数中处理存储过程的结果(游标...

      项目中开发了一个存储过程来给报表提供数据源,而程序业务逻辑中需要有一个对该数据源进行判断的功能,开始是使用拼接sql的方式在存储过程中对应sql的外面包装一层来进行处理,但是觉得这种方式拼接的sql太长,而且以后业务逻辑改变要同时修改存储过程和代码,容易发生遗漏,故产生了在函数中处理存储过程的结果(游标)的想法。但是在网上搜索发现对存储过程返回的游标进行处理的例子很少,自己弄了半天终于成功了,在此记录一下。

    存储过程plan_station_contrast

    create or replace procedure plan_station_contrast(
     groupid in varchar2 ,--到站计划主表id,非空
     refCursor out sys_refcursor)
    is
    planDate  varchar2(6);--计划年月
    deptCode  varchar2(10);--上报单位编码
    
    begin
      select p.plan_date,p.dept_code into planDate,deptCode from p_station_group p where id=to_number(groupid);
    
     open refcursor for 'select nvl(station.oil_code,config.oil_code) oilCode,nvl(station.oil_name,config.oil_name) oilName
      ,nvl(station.planQuantity,0) planQuantity  --到站上报
      ,nvl(station.configStock,0)  configStock   --外采上报
      ,nvl(config.configQuantity,0) configQuantity --直炼配置
      ,nvl(config.stockQuantity,0) stockQuantity --外采配置
      
      from
      --到站计划
      (select p.oil_code,p.oil_name
              ,sum(plan_Quantity)-sum(special_Quantity) planQuantity 
              ,sum(stock_Quantity) configStock
        from
          (select oil_code,oil_name
             ,decode(special,''0'',nvl(train_quantity, 0) + nvl(ship_quantity, 0)+ nvl(pipeline_quantity, 0) +nvl(truck_quantity, 0),0)plan_Quantity
             ,decode(special,''1'',nvl(train_quantity, 0),0) special_Quantity
             ,decode(special,''2'',nvl(train_quantity, 0) + nvl(ship_quantity, 0)+ nvl(pipeline_quantity, 0) +nvl(truck_quantity, 0),0) stock_Quantity
          from p_station where sid = '||groupid||' 
        )p
      group by p.oil_code,p.oil_name) station
      full join
      --配置计划
      (
        select p.oil_code,p.oil_name
        ,sum(decode(a.dtype,''factory'',decode(a.f_type,1,p.plan_quantity,0),''stock'',0,p.plan_quantity)) configQuantity --配置量 
        ,sum(decode(a.dtype,''factory'',decode(a.f_type,1,0,p.plan_quantity),''stock'',p.plan_quantity,0)) stockQuantity --外采量 
        from p_disbtn p
        left outer join P_CONFIG_PLAN_FLOW f on p.flow_code=f.flow_code --关联配置计划流向
        left outer join acc_domain a on p.company_code=a.code 
        where bill_month='''||planDate||'''
        and (select status from p_disbtn_group where bill_month='''||planDate||''')=''2''
        and f.company_code='''||deptCode||'''
        and p.plan_quantity>0 
        group by p.oil_code,p.oil_name
      ) config
      on station.oil_code=config.oil_code ';
    end plan_station_contrast;


    函数fun_plan_station_contrast

    create or replace function fun_plan_station_contrast(groupid varchar2)
      return number is
      tmp varchar2(200);
      mycur sys_refcursor;
      res number :=0;--不符合条件的记录条数
      type station_record is record(
      strCode varchar2(200),
      strName varchar2(200),
      planQuantity number(19,2),
      configStock number(19,2),
      configQuantity number(19,2),
      stockQuantity number(19,2)
      );--定义新类型来存放存储过程返回的结果
    
      tmp_record station_record;
    begin
      tmp := 'call plan_station_contrast(:param1,:param2) ';
      execute immediate tmp using groupid,out mycur ;--调用存储过程
    
      --游标默认已打开,因为存储过程中是open refcursor for 
      if(mycur%isopen) then
        dbms_output.put_line('打开');
      else
        dbms_output.put_line('关闭');
      end if;
    
      loop
        fetch mycur into tmp_record;
        exit when mycur%notfound;
         if tmp_record.planQuantity<>tmp_record.configQuantity or tmp_record.stockQuantity<>tmp_record.configStock then
          res:=res+1;
        end if;
      end loop;
      
      close mycur;--关闭游标
      
      return res;
    end fun_plan_station_contrast;

    结果如下:


     

    展开全文
  • Java,PL/SQL调用 ORACLE存储函数以及存储过程 ONE Goal , ONE Passion ! 准备工作 创建表 --- 创建测试用表 school CREATE TABLE school( ID number, --学校id NAME VARCHAR2(30) ---学校名

    Java,PL/SQL调用 ORACLE存储函数以及存储过程

            ONE Goal , ONE Passion !
    

    准备工作

    • 创建表
        --- 创建测试用表 school
            CREATE TABLE school(
                ID number,   --学校id
                NAME VARCHAR2(30)    ---学校名
            );
    
            --- 添加数据
            INSERT into school values(1,'北京大学');
            INSERT into school values(2,'南京大学');
            INSERT into school values(1,'东京大学');
            COMMIT;

    • 编写java代码连接数据库,获取Connection连接对象
       public class OracleUtil {
        // 加载Oracle驱动
        static {
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
         }
    
        // 获取数据库连接
    
        public static Connection getConnection() throws SQLException {
            Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@iP:1521:orcl", "system",
                    "密码");
            return connection;
        }
        }
    

    ——-存储函数——-

    • 1.什么是存储函数

      存储函数又称为自定义函数。可以接收一个或多个参数,返回一个结果。在函数中我们可以使用PL/SQL进行逻辑的处理。

    存储函数语法结构

        CREATE [ OR REPLACE ] FUNCTION 函数名称
            (参数名称 [in|out|in out] 参数类型,...)
            RETURN 
            结果变量数据类型
            IS
            变量声明部分;
            BEGIN
            逻辑部分;
            RETURN 
            结果变量;
            [EXCEPTION 
            异常处理部分
            ]
           END;
    

    创建函数

    • 此处只是返回了一个varchar2的数据. 返回值可以为cursor,table.
    
            --- 创建函数.     根据地址ID查询地址名称
            CREATE OR REPLACE FUNCTION fn_getName(sid NUMBER)
            RETURN VARCHAR2        --- 返回值类型
            AS
            r_name VARCHAR2(30);  --- 声明变量
    
            BEGIN
              SELECT name INTO r_name FROM school WHERE id=sid;      ---将查询的name 存储到r_name 中
              RETURN r_name;        -- 返回r_name
            END;

    PL/SQL调用oracle函数

         --- PL/SQL调用过程     使用函数,传参 id = 2 
            select fn_getName(2) 学校 from dual;
    

    java代码调用oracle函数

       /**
         * 调用ORACLE函数
         *
         * @param sid
         */
        public static void getenamefun(int sid) {
            String sname;
            java.sql.Connection conn = null;
            java.sql.PreparedStatement stmt = null;
            String sql = "{?=call fn_getName(?)}"; // 调用方式
            CallableStatement fun = null;
    
            try {
                conn = OracleUtil.getConnection();
                fun = conn.prepareCall(sql);
                fun.setInt(2, sid);
                fun.registerOutParameter(1, Types.VARCHAR);//注册传出参数类型
                fun.execute();
                sname = fun.getString(1); // 取返回的参数结果
                System.out.println(sname);
            } catch (SQLException e) {
    
                e.printStackTrace();
            }
    
        }

    ——-存储过程——-

    • 1.什么是存储过程

    存储过程是被命名的PL/SQL块,存储于数据库中,是数据库对象的一种。应用程序可以调用存储过程,执行相应的逻辑。

    存储过程与存储函数都可以封装一定的业务逻辑并返回结果,存在区别如
    下:

    • 1.存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过
      传出参数返回多个值。

    • 2.存储函数可以在select语句中直接使用,而存储过程不能。过程多数是
      被应用程序所调用。

    • 3.存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务代码


    存储函数语法结构

    
    
            CREATE [ OR REPLACE ] PROCEDURE 存储过程名称(参数名 类型, 
            参数名 类型,参数名 类型...)
            IS|AS
            变量声明部分;
            BEGIN
            逻辑部分
            [EXCEPTION 
            异常处理部分
            ]
            END;

    注意:

    1.参数只指定类型,不指定长度

    2.过程参数的三种模式:
    IN :传入参数(默认)
    OUT :传出参数主要用于返回程序运行结果
    IN OUT :传入传出参数

    一:不带传出参数

    CREATE OR REPLACE PROCEDURE pro_insert(
        ID NUMBER,
        NAME VARCHAR2   ----注意这里不带长度,不能写成(VARCHAR(30))
        )
        AS
        BEGIN
         INSERT INTO school values(ID,NAME);
        END;

    PL/SQL调用过程

       CALL pro_insert(4,'郑州大学'); 

    java代码调用oracle函数

    public static void callPr() {
            java.sql.Connection conn = null;
            java.sql.PreparedStatement stmt = null;
            try {
                conn = OracleUtil.getConnection();
                stmt = conn.prepareCall("{ call pro_insert(?,?) }");   //设置存储过程 call为关键字.
                stmt.setInt(1, 4); //设置第一个输入参数
                stmt.setInt(2, "郑州大学");//设置第二个输入参数
                stmt.execute();//执行
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    

    二:带传出参数

    ---.  带传出参数
        CREATE OR REPLACE PROCEDURE pro_getCount(s_name VARCHAR2,s_count OUT NUMBER)
        AS
        BEGIN
        SELECT COUNT(*) INTO s_count FROM school
        WHERE name LIKE '%'||s_name||'%';
        END;

    PL/SQL调用过程

    --- PL/SQL调用
        declare
        s_count number;  --定义传出参数的变量
        begin
        pro_getCount('大学',s_count); --- 执行过程
        DBMS_OUTPUT.put_line('查询结果,数量:'||s_count);    ---打印结果
        end;

    java代码调用oracle函数

     public static void pro_Gount(String name) {
            java.sql.Connection conn = null;
            java.sql.CallableStatement stmt = null;
            try {
                conn = DaoUtil.getConnection();
                stmt = conn.prepareCall("{ call pro_getCount(?,?) }"); //设置存储过程 call为关键字.
                stmt.setString(1, "大学"); //设置第一个输入参数
                stmt.registerOutParameter(2,OracleTypes.NUMBER);// 设置第二个输入参数
                stmt.execute();//执行. 
                // 执行完毕后,从占位符中去对应的列就可以了
                int count = stmt.getInt(2);
                System.out.println("查询结果" + count); // 4
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    二:带传出参数

     ---- 返回值为游标
    
        CREATE OR REPLACE PROCEDURE pro_cursor(s_cur OUT SYS_REFCURSOR)
        AS
        BEGIN
          OPEN s_cur FOR SELECT * FROM school;
        END;

    PL/SQL调用过程

    declare 
         s_cur SYS_REFCURSOR;  -- 定义传出参数的变量
         s_row school%ROWTYPE; -- 将传出参数赋值给s_row
        begin  
        pro_cursor(s_cur);    -- 执行过程
        loop                    -- 循环结果
        FETCH s_cur into s_row;
        EXIT WHEN s_cur%NOTFOUND;
        dbms_output.put_line(s_row.name);   
        end loop;   
        end; 

    java代码调用oracle函数

      /**
         *  返回结果为cursor
         *
         */
        public static void pro_cur() {
            java.sql.Connection conn = null;
            java.sql.CallableStatement stmt = null;
            try {
                conn = DaoUtil.getConnection();
                stmt = conn.prepareCall("{ call pro_cursor(?) }"); //设置存储过程 call为关键字
                stmt.registerOutParameter(1,OracleTypes.CURSOR);// 设置第一个输入参数
                stmt.execute();//执行.
                // 执行完毕后,从占位符中去对应的列就可以了
                ResultSet resultSet = (ResultSet) stmt.getObject(1);
                while (resultSet.next()){
                    String id = resultSet.getString("id");
                    String name = resultSet.getString("name");
                    System.out.println("序号" + id + "学校名" + name);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
    展开全文
  • JAVA调用ORACLE存储过程函数的返回结果集 2017年05月11日 21:03:18 彖爻之辞 阅读数:1091 标签: oracle存储 更多 个人分类: --------1.1 ORACLE 具体过程如下: CREATE TABLE STOCK_PRICES( RIC ...

    JAVA调用ORACLE的存储过程、函数的返回结果集

    2017年05月11日 21:03:18 彖爻之辞 阅读数:1091 标签: oracle存储 更多

    个人分类: --------1.1 ORACLE

    具体过程如下:
    
    CREATE TABLE STOCK_PRICES(
        RIC VARCHAR(6) PRIMARY KEY,
        PRICE NUMBER(7,2),
        UPDATED DATE );
    
    --对表插入数据
    INSERT INTO stock_prices SELECT '1110',1.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1111',2.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1112',3.0,SYSDATE FROM DUAL;
    INSERT INTO stock_prices SELECT '1113',4.0,SYSDATE FROM DUAL;
    
    --建立一个返回游标
    CREATE OR REPLACE PACKAGE ZZH_TEST.PKG_PUB_UTILS IS
        --动态游标
        TYPE REFCURSOR IS REF CURSOR;
    END PKG_PUB_UTILS;
    
    --创建一个测试用的存储过程
    CREATE OR REPLACE PROCEDURE ZZH_TEST.P_GET_PRICE(AN_O_RET_CODE         OUT NUMBER,
                                                     AC_O_RET_MSG          OUT VARCHAR2,
                                                     CUR_RET               OUT PKG_PUB_UTILS.REFCURSOR,
                                                     AN_I_PRICE IN NUMBER
                                                     ) IS
    BEGIN
        AN_O_RET_CODE := 0;
        AC_O_RET_MSG  := '操作成功';
    
        OPEN CUR_RET FOR
            SELECT * FROM STOCK_PRICES WHERE PRICE<AN_I_PRICE;
    EXCEPTION
        WHEN OTHERS THEN
            AN_O_RET_CODE := -1;
            AC_O_RET_MSG  := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
    
    END P_GET_PRICE;
    
    --创建一个测试用的函数:
    CREATE OR REPLACE FUNCTION ZZH_TEST.F_GET_PRICE(v_price IN NUMBER)
        RETURN PKG_PUB_UTILS.REFCURSOR
    AS
        stock_cursor PKG_PUB_UTILS.REFCURSOR;
    BEGIN
        OPEN stock_cursor FOR
        SELECT ric,price,updated FROM stock_prices WHERE price < v_price;
    
        RETURN stock_cursor;
    END;
    
    --开发JAVA调用存储过程返回结果集的例子:JDBCoracle10G_INVOKEPROCEDURE 
    --开发JAVA调用函数返回结果集的例子:JDBCoracle10G_INVOKEFUNCTION
    
    --2份JAVA源代码:
    
    -- 开发JAVA调用存储过程返回结果集的例子:JDBCoracle10G_INVOKEPROCEDURE
    
    import java.sql.*;
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
     /* 本例是通过调用oracle的存储过程来返回结果集:
      * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip
      */
     public class JDBCoracle10G_INVOKEPROCEDURE {
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null; 
      CallableStatement stmt = null;
    
    
      String driver;
      String url;
      String user;
      String pwd; 
      String sql;
      String  in_price ;  
      public  JDBCoracle10G_INVOKEPROCEDURE()
      {   
       driver = "oracle.jdbc.driver.OracleDriver";;
       url = "jdbc:oracle:thin:@10.168.1.193:1521:orcl"; 
       //oracle 用户
       user = "ZZH_TEST";
       //oracle 密码   
       pwd = "ZZH_TEST";
    
       init();
    
       //mysid:必须为要连接机器的sid名称,否则会包以下错:
       //     java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
       //参考连接方式:
       //   Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
       //   cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );   
    
      }
    
      public void init()
      {
    
       System.out.println("oracle jdbc test");
    
       try{
          Class.forName(driver);
          System.out.println("driver is ok");
    
          conn = DriverManager.getConnection(url,user,pwd);
          System.out.println("conection is ok");
    
          statement = conn.createStatement();
             // conn.setAutoCommit(false);
    
          //输入参数
           in_price="3.0";
    
           //调用函数
           stmt = conn.prepareCall( "call P_GET_PRICE(?,?,?,?)");
           stmt.registerOutParameter(1, java.sql.Types.FLOAT);
           stmt.registerOutParameter(2, java.sql.Types.CHAR);
           stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
           stmt.setString(4, in_price);
           stmt.executeUpdate();
           int retCode = stmt.getInt(1);
           String retMsg = stmt.getString(2);
           if (retCode == -1) { //如果出错时,返回错误信息
           System.out.println("报错!");
           } else {
           //取的结果集的方式一:
               rs = ((OracleCallableStatement)stmt).getCursor(3);
            //取的结果集的方式二:
                 //  rs = (ResultSet) stmt.getObject(3);
    
            String ric ;
         String price ;
         String updated ;
            //对结果进行输出
         while(rs.next()){
          ric = rs.getString(1);
          price = rs.getString(2);
          updated = rs.getString(3);      
    
          System.out.println("ric:"+ric+";-- price:"+price+"; --"+updated+"; "); 
         }
           }
    
        }
           catch(Exception e)
        {
         e.printStackTrace();
        }
              finally{
               System.out.println("close ");
           }       
      }
    
      public static void main(String args [])//自己替换[]
      {
       new JDBCoracle10G_INVOKEPROCEDURE();
      }
      }
    
    --=======================================================================
    
    --源代码:
    
    --开发JAVA调用函数返回结果集的例子:JDBCoracle10G_INVOKEFUNCTION
    
    import java.sql.*;
    import oracle.jdbc.OracleCallableStatement;
    import oracle.jdbc.OracleTypes;
     /*
     /* 本例是通过调用oracle的函数来返回结果集:
      * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip  
      */
     public class JDBCoracle10G_INVOKEFUNCTION {
      Connection conn = null;
      Statement statement = null;
      ResultSet rs = null; 
      CallableStatement stmt = null;
    
      String driver;
      String url;
      String user;
      String pwd; 
      String sql;
      String  in_price ;  
      public  JDBCoracle10G_INVOKEFUNCTION()
      {
       driver = "oracle.jdbc.driver.OracleDriver";;
       url = "jdbc:oracle:thin:@10.168.1.193:1521:orcl"; 
       //oracle 用户
       user = "ZZH_TEST";
       //oracle 密码   
       pwd = "ZZH_TEST";
    
       init();
    
       //mysid:必须为要连接机器的sid名称,否则会包以下错:
       //     java.sql.SQLException: Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
       //参考连接方式:
       //   Class.forName( "oracle.jdbc.driver.OracleDriver" ); 
       //   cn = DriverManager.getConnection( "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );   
      }
    
      public void init()
      {
       System.out.println("oracle jdbc test");
    
       try{
          Class.forName(driver);
          System.out.println("driver is ok");
    
          conn = DriverManager.getConnection(url,user,pwd);
          System.out.println("conection is ok");
    
          statement = conn.createStatement();
           //conn.setAutoCommit(false);
    
          //输入参数      
           in_price="5.0";
    
           //调用函数
           stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");
          // stmt.registerOutParameter(1, java.sql.Types.FLOAT);
          // stmt.registerOutParameter(2, java.sql.Types.CHAR);
           stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
           stmt.setString(2, in_price);
           stmt.executeUpdate();
           //取的结果集的方式一:
                rs = ((OracleCallableStatement)stmt).getCursor(1);
           //取的结果集的方式二:
              //  rs = (ResultSet) stmt.getObject(1);
             String ric ;
         String price ;
         String updated ;
    
         while(rs.next()){
          ric = rs.getString(1);
          price = rs.getString(2);
          updated = rs.getString(3);                 
             System.out.println("ric:"+ric+";-- price:"+price+"; --"+updated+"; "); 
         }
    
        }
           catch(Exception e)
        {
         e.printStackTrace();
        }
              finally{
               System.out.println("close ");
           }       
      }
    
      public static void main(String args [])//自己替换[]
      {
       new JDBCoracle10G_INVOKEFUNCTION();
      }
      }
    

    https://blog.csdn.net/paul50060049/article/details/71698108

     

     

     

     

     

    oracle调用存储过程和函数返回结果集

    2010年10月21日 13:56:00  阅读数:25787

       在程序开发中,常用到返回结果集的存储过程,这个在mysqlsql server 里比较好处理,直接返回查询结果就可以了,
    但在oracle里面 要 out 出去,就多了一个步骤,对于不熟悉的兄弟们还得出上一头汗:),这里我简单介绍一下,以供参考,
       1  定义包
          oracle 返回的结果集需要自定义一个 CURSOR (游标变量)性质的变量,这个要在包头定义,所以要建立一个包,如下是包头

    Pl/sql代码

    CREATE OR REPLACE PACKAGE PAK_rstest

      IS

        TYPE   retcursor    IS   REF   CURSOR;

       PROCEDURE pro_read

         (

           outcurse  IN OUT retcursor

         );

       END; -- Package spec

     

     

     

     

     

     

     

     

         上面是建立了一个名称为PAK_rstest的包头,里面定义了一个CURSOR 类型,类型名为retcursor ,有了这个定义我们就可以用他来返回结果集了,比如该包里面的 pro_read 过程就是 一个返回结果集的过程,下面是他的包体,

    Pl/sql代码

    CREATE OR REPLACE PACKAGE BODY PAK_rstest IS

       PROCEDURE pro_read

       (

           outcurse  IN OUT retcursor

       )

       IS

       begin

            OPEN outcurse FOR

             select * from tbl_test

                    where rownum<6;

            return;

       end;

     

    END;

     

     

     

     

     

     

     

     

     

     

     

     

     

     

        这样就定义好了一个包,这个包里面有个返回结果集的过程 pro_read
      

     2 在程序里面调用,
         下面就是如果在程序里面调用了,这里用java为例子简单介绍一下,
         假设你现在已经有一个Connection  conn 对象连接上了数据库(如何连接数据库我这里就不详细说了), 
         
    则用下面的代码调用过程,

    Pl/sql代码

     

    if(conn !=null){

        String sqlstr = "{call  PAK_SMS2_ROUTE.MO_ISSUE(?)}";

         CallableStatement cstmt = conn.prepareCall(sqlstr);

         cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //outcurse

         cstmt.executeUpdate();

         ResultSet rs = (ResultSet) cstmt.getObject(1); // 这里吧信息已经读入rs结果集里面,剩下的大家都熟悉了吧

       while (rs.next()) {

            System.out.println(rs.getString("s_date1")); //tbl_test 表里的字段名称或是结果集的列名称

         System.out.println(rs.getString("s_date2"));

          }   

             conn.close();

             conn = null;       

    }

     

     

     

     

     

     

     

     

     

     

     

     

     

      好了到这里就可以看到返回的结果集内容了,是不是比较简单啊,:)

       Oracle 存储过程返回结果集:

    过程返回记录集代码

     

    CREATE OR REPLACE PACKAGE pkg_test

    AS

        TYPE myrctype IS REF CURSOR;

     

        PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);

    END pkg_test;

     

     

    CREATE OR REPLACE PACKAGE BODY pkg_test

    AS

        PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)

        IS

           sqlstr   VARCHAR2 (500);

        BEGIN

           IF p_id = 0 THEN

              OPEN p_rc FOR

                 SELECT ID, NAME, sex, address, postcode, birthday

                   FROM student;

           ELSE

              sqlstr :=

                 'select id,name,sex,address,postcode,birthday

                from student where id=:w_id';

              OPEN p_rc FOR sqlstr USING p_id;

           END IF;

        END get;

    END pkg_test;  

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    函数返回记录集:
    建立带ref cursor定义的包和包体及函数:

     

    函数返回记录集代码

    CREATE OR REPLACE

    package pkg_test as

    /* 定义ref cursor类型

        不加return类型,为弱类型,允许动态sql查询,

        否则为强类型,无法使用动态sql查询;

    */

     

       type myrctype is ref cursor; 

      

    --函数申明

       function get(intID number) return myrctype;

    end pkg_test; 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    包体代码

     

    CREATE OR REPLACE

    package body pkg_test as

    --函数体

        function get(intID number) return myrctype is

          rc myrctype;  --定义ref cursor变量

          sqlstr varchar2(500);

        begin

          if intID=0 then

             --静态测试,直接用select语句直接返回结果

             open rc for select id,name,sex,address,postcode,birthday from

    student;

          else

             --动态sql赋值,用:w_id来申明该变量从外部获得

             sqlstr := 'select id,name,sex,address,postcode,birthday from

    student where id=:w_id';

             --动态测试,用sqlstr字符串返回结果,用using关键词传递参数

             open rc for sqlstr using intid;

          end if;

      

          return rc;

        end get;

      

    end pkg_test;

     

    Java调用oracle函数返回游标处理代码

     

    CallableStatement cstmt = null;

    ResultSet rs = null;

    try {

    String callSql = "{? = call AAAAA(?)}";

    cstmt = conn.prepareCall(callSql);

       

    cstmt.setString(2, "userName");

    cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

    cstmt.execute();

    rs = (ResultSet) cstmt.getObject(1);

    if (rs != null) {

           System.out.print("usercd");

           System.out.print("userName");               

           System.out.println("EMAIL");

           while (rs.next()) {       

        System.out.print(rs.getString(1)+"   ");

        System.out.print(rs.getString(2)+"   ");               

        System.out.println(rs.getString(3));

           }   

    }

    https://blog.csdn.net/whaosy/article/details/5956460

     

     

     

    展开全文
  • 在应用程序中调用Oracle存储过程存储函数.pdf
  • 调用oracle函数存储过程

    千次阅读 2010-07-02 09:29:00
     try { <br /> // 调用一个没有参数的函数; 函数返回 a VARCHAR  // 预处理callable语句  cs = connection.prepareCall("{? = call myfunc}");  // 注册返回值类型  cs....

    1.在java里面调用

    CallableStatement cs;
      try {
      

         // 调用一个没有参数的函数; 函数返回 a VARCHAR
      // 预处理callable语句
      cs = connection.prepareCall("{? = call myfunc}");
      // 注册返回值类型
      cs.registerOutParameter(1, i);
      // Execute and retrieve the returned value
      cs.execute();
      String retValue = cs.getString(1);
      


      // 调用有一个in参数的函数; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncin(?)}"); 
      // Register the type of the return value
      cs.registerOutParameter(1, Types.VARCHAR);
      // Set the value for the IN parameter
      cs.setString(2, "a string");
      // Execute and retrieve the returned value
      cs.execute();
      retValue = cs.getString(1);
      


      // 调用有一个out参数的函数; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncout(?)}");
      // Register the types of the return value and OUT parameter
      cs.registerOutParameter(1, Types.VARCHAR);
      cs.registerOutParameter(2, Types.VARCHAR);
      // Execute and retrieve the returned values
      cs.execute();
      retValue = cs.getString(1);      // return value
      String outParam = cs.getString(2);  // OUT parameter
      


      // 调用有一个in/out参数的函数; the function returns a VARCHAR
      cs = connection.prepareCall("{? = call myfuncinout(?)}");
      // Register the types of the return value and OUT parameter
      cs.registerOutParameter(1, Types.VARCHAR);
      cs.registerOutParameter(2, Types.VARCHAR);
      // Set the value for the IN/OUT parameter
      cs.setString(2, "a string");
      // Execute and retrieve the returned values
      cs.execute();
      retValue = cs.getString(1);      // return value
      outParam = cs.getString(2);      // IN/OUT parameter


      } catch (SQLException e) {
      }

     

    2.直接在sql语句里面调用:

         select function() from dual

     

    3.调用oracle存储过程:

    首先来了解一下以下几个概念:

    1)、什么是存储过程。

         存储过程是数据库服务器端的一段程序,它有两种类型。一种类似于SELECT查询,用于检索数据,检索到的数据能够以数据集的形式返回给客户。另一种类似于INSERT或DELETE查询,它不返回数据,只是执行一个动作。有的服务器允许同一个存储过程既可以返回数据又可以执行动作。
    2)、什么时候需要用存储过程
      如果服务器定义了存储过程,应当根据需要决定是否要用存储过程。存储过程通常是一些经常要执行的任务,这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程,可以改善应用程序的性能。这是因为: .服务器往往具有强大的计算能力和速度。 避免把大量的数据下载到客户端,减少网络上的传输量。 例如,假设一个应用程序需要计算一个数据,这个数据需要涉及到许多记录。如果不使用存储过程的话,把这些数据下载到客户端,导致网络上的流量剧增。不仅如此,客户端可能是一台老掉牙的计算机,它的运算速度很慢。而改用存储过程后,服务器会很快地把数据计算出来,并且只需传递一个数据给客户端,其效率之高是非常明显的。
    3)、存储过程的参数
      要执行服务器上的存储过程,往往要传递一些参数。这些参数分为四种类型:
      第一种称为输入参数,由客户程序向存储过程传递值。
      第二种称为输出参数,由存储过程向客户程序返回结果。
      第三种称为输入/输出参数,既可以由客户程序向存储过程传递值,也可以由存储过程向客户程序返回结果。
      第四种称为状态参数,由存储过程向客户程序返回错误信息。
      要说明的是,并不是所有的服务器都支持上述四种类型的参数,例如,InterBase就不支持状态参数。
    4)、oracle 存储过程的基本语法
    1.基本结构
    CREATE OR REPLACEPROCEDURE 存储过程名字
    (
    参数1 IN NUMBER,
    参数2 IN NUMBER
    ) IS
    变量1 INTEGER :=0;
    变量2 DATE;
    BEGIN
    END 存储过程名字

    2.SELECT INTO STATEMENT
    将select查询的结果存入到变量中,可以同时将多个列存储多个变量中,必须有一条
    记录,否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
    例子:

    BEGIN
    SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    xxxx;
    END;


    一:无返回值的存储过程
    存储过程为:

    CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS
    BEGIN
    INSERT INTO HYQ.B_ID (I_ID,I_NAME) S (PARA1, PARA2);
    END TESTA;

    然后呢,在java里调用时就用下面的代码:

    package com.hyq.src;

    import java.sql.*;
    import java.sql.ResultSet;

    public class TestProcedureOne {
    public TestProcedureOne() {
    }
    public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    CallableStatement cstmt = null;

    try {
    Class.forName(driver);
    conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
    CallableStatement proc = null;
    proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
    proc.setString(1, "100");
    proc.setString(2, "TestOne");
    proc.execute();
    }
    catch (SQLException ex2) {
    ex2.printStackTrace();
    }
    catch (Exception ex2) {
    ex2.printStackTrace();
    }
    finally{
    try {
    if(rs != null){
    rs.close();
    if(stmt!=null){
    stmt.close();
    }
    if(conn!=null){
    conn.close();
    }
    }
    }
    catch (SQLException ex1) {
    }
    }
    }
    }

    当然了,这就先要求要建张表TESTTB,里面两个字段(I_ID,I_NAME)。

    二:有返回值的存储过程(非列表)
    存储过程为:

    CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
    BEGIN
    SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
    END TESTB;

    在java里调用时就用下面的代码:

    package com.hyq.src;

    public class TestProcedureTWO {
    public TestProcedureTWO() {
    }
    public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    try {
    Class.forName(driver);
    conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
    CallableStatement proc = null;
    proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");
    proc.setString(1, "100");
    proc.registerOutParameter(2, Types.VARCHAR);
    proc.execute();
    String testPrint = proc.getString(2);
    System.out.println("=testPrint=is="+testPrint);
    }
    catch (SQLException ex2) {
    ex2.printStackTrace();
    }
    catch (Exception ex2) {
    ex2.printStackTrace();
    }
    finally{
    try {
    if(rs != null){
    rs.close();
    if(stmt!=null){
    stmt.close();
    }
    if(conn!=null){
    conn.close();
    }
    }
    }
    catch (SQLException ex1) {
    }
    }
    }
    }

    }

    注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。

    三:返回列表
    由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
    1, 建一个程序包。如下:

    CREATE OR REPLACE PACKAGE TESTPACKAGE AS
    TYPE Test_CURSOR IS REF CURSOR;
    end TESTPACKAGE;

    2,建立存储过程,存储过程为:

    CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
    BEGIN
    OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
    END TESTC;

    可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
    在java里调用时就用下面的代码:

    package com.hyq.src;
    import java.sql.*;
    import java.io.OutputStream;
    import java.io.Writer;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import oracle.jdbc.driver.*;


    public class TestProcedureTHREE {
    public TestProcedureTHREE() {
    }
    public static void main(String[] args ){
    String driver = "oracle.jdbc.driver.OracleDriver";
    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;

    try {
    Class.forName(driver);
    conn = DriverManager.getConnection(strUrl, "hyq", "hyq");

    CallableStatement proc = null;
    proc = conn.prepareCall("{ call hyq.testc(?) }");
    proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
    proc.execute();
    rs = (ResultSet)proc.getObject(1);

    while(rs.next())
    {
    System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
    }
    }
    catch (SQLException ex2) {
    ex2.printStackTrace();
    }
    catch (Exception ex2) {
    ex2.printStackTrace();
    }
    finally{
    try {
    if(rs != null){
    rs.close();
    if(stmt!=null){
    stmt.close();
    }
    if(conn!=null){
    conn.close();
    }
    }
    }
    catch (SQLException ex1) {
    }
    }
    }
    }

    在这里要注意,在执行前一定要先把oracle的驱动包放到class路径里,否则会报错。

     

    4.Oracle的存储过程和函数区别

    展开全文
  • 1、存储过程是自定义的且封装在数据库服务器上的 PLSQL 代码片段,它已经编译好了,可以像调用数据库函数一样来调用存储过程,可以给它传递参数,它也可以有返回值。 2、因为存储过程是编译好放在数据库服务器上的...
  • 你还在为如何调用oracle中的存储过程函数而烦恼吗,请看看该文档。
  • 存储过程调用与删除;例子:--调用无参的存储过程 begin first_proc; end;--调用带有输出参数的存储过程 DECLARE v_avgsalary NUMBER; v_count NUMBER; BEGIN PROC2(10,v_avgsalary,v_count); DBMS_OUTPUT.put...
  • 1、写了一个调用远端sayHello存储过程存储过程,远端sayHello存储过程如下:   -- 远端 sayHello 定义如下: create or replace procedure sayHello(currentTime out varchar) is begin select 'Hello World'...
  • Oracle10G_函数存储过程 Oracle数据库中不仅可以使用单条语句对数据库进行增、删、改、查操作,而且可以多条语句组成一个语句块,并一起执行。这些语句块可以进行显式命名,并被其他应用调用。这些命名的语句块被...
  • oracle触发器调用存储过程,最简单的例子适用于初学者
  • package com.dada.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; ...//测试调用oracle函数 public int fun_add() { System.o
  • 废话不说,直接上代码: 应用实例 //引用方式package.function或package.procedure StoredProcedure sp = new org.springframework.jdbc.object.StoredProcedure...//调用函数时必须,调用存储过程不要 sp.setFun
  • JDBC 调用存储函数 存储过程

    千次阅读 2015-08-28 10:10:40
    JDBC调用存储过程 步骤: 1:通过Connection 对象的prepareCall()方法创建一个CallableStatement对象的实例,  在使用Connection对象的prepareCall()方法时,需要传入一个String类型的字符串,  该方法指明...
  • Oracle中的函数存储过程、包. 函数 ①系统函数 ②自定义函数 ③使用函数 ④编写过程过程调用
  • 存储过程(PROCEDURE),存储函数(FUNCTION) 通过之前两篇文章对pl/sql程序的简单介绍...oracle允许将pl/sql程序块存储在数据库中,并在任何地方可以调用他,这样的程序块我们就成为存储过程存储函数过程函数是pl/
  • KETTLE调用Oracle存储过程

    千次阅读 2019-10-13 16:46:16
    使用KETTLE调用存储过程 1.创建调用存储过程的转换 1.1创建转换 点击右上角【文件】->【新建】->【转换】 1.2添加步骤 1.2.1 在新创建的转换中添加【表输入】,【调用DB存储过程】步骤 在右侧【核心...
  • oracle函数存储过程的区别和联系  在oracle中,函数存储过程是经常使用到的,他们的语法中有很多相似的地方,但也有自己的特点。刚学完函数存储过程,下面来和大家分享一下自己总结的关于函数存储过程的...
  • Java调用Oracle存储过程存储函数

    千次阅读 2017-06-18 16:16:03
    存储过程create or replace procedure queryEmpInfo(eno in number, pename out varchar2, psal out number,
  • oracle创建有返回值的函数调用函数 1 创建调用只有输入参数的函数 create or replace function fun_test_1(param1 in number,param2 in number) return number as begin if param1>param2 then return ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 80,379
精华内容 32,151
关键字:

函数调用存储过程oracle