关闭

jabc调用pl/sql的包

281人阅读 评论(0) 收藏 举报
  • ProDemo.java
    package com.test;
    import java.util.Scanner;
    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.CallableStatement;
    import java.sql.Types;
    public class ProDemo {
     private Connection conn;
     private Scanner sca = new Scanner(System.in);
     public ProDemo(){
      try {
       Class.forName("oracle.jdbc.driver.OracleDriver");
          conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oracle",
                                        "scott","tiger");
      } catch (ClassNotFoundException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
      } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
      }
     }
     
     public void doProc(){
      //System.out.println("请输入插入的数据");
      //int depid = sca.nextInt();
      //String depname = sca.next();
      //int manid =  sca.nextInt();
        // int locid =  sca.nextInt();
      if(conn == null){
       System.out.println("连接失败");
       return;
      }
      
      String sql = "{call level_sal.pro_sal(?,?)}";
      try {
       CallableStatement cst = conn.prepareCall(sql);
       //cst.setInt(1, depid);
       //cst.setString(2, depname);
       //cst.setInt(3, manid);
       //cst.setInt(4, locid);
       cst.registerOutParameter(1,Types.INTEGER);
       cst.registerOutParameter(2,Types.INTEGER);
       
       //执行
       cst.execute();
       
       //获取out参数
      int count1 = cst.getInt(1);
       
       System.out.println("添加了 :" + count1);
       
      int count2 = cst.getInt(2);
       System.out.println("添加了 :" + count2);
       
       cst.close();
      } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
      }finally{
       try {
        conn.close();
       } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
       }
      }
     }
    }
    
    Start.java-------------------------测试
    public class Start {
     public static void main(String[] args) {
      // TODO Auto-generated method stub
      ProDemo demo = new ProDemo();
      demo.doProc();
     }
    }
    
    运行结果:
    添加了 :7
    添加了 :5
    包体
    create or replace package body level_sal
    IS
      PROCEDURE pro_sal( v1 OUT NUMBER, v2 OUT NUMBER) 
      IS
      CURSOR cur IS
      SELECT * FROM emp;
      va1 NUMBER := 0;
      va2 NUMBER := 0;
      BEGIN
      FOR rec IN cur LOOP
      IF rec.sal < 2000 THEN
      va1 := va1 +1;
      INSERT INTO tt1 VALUES(rec.empno,rec.ename,rec.job
                      ,rec.mgr,rec.hiredate,rec.sal,rec.comm,
                      rec.deptno);
      ELSIF rec.sal >= 2000 THEN
      va2 := va2 +1;
      INSERT INTO tt2 VALUES(rec.empno,rec.ename,rec.job
                      ,rec.mgr,rec.hiredate,rec.sal,rec.comm,
                      rec.deptno);  
                      
      END IF; 
      v1 := va1;
      v2 := va2; 
      END LOOP;
      
      END pro_sal;
    end level_sal;
    
    包头
    create or replace package level_sal is
      PROCEDURE pro_sal(
                                v1 OUT NUMBER,
                                v2 OUT NUMBER
                                             );
    end level_sal;
    
    

  • 0
    0

    查看评论
    * 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
      个人资料
      • 访问:36829次
      • 积分:584
      • 等级:
      • 排名:千里之外
      • 原创:22篇
      • 转载:19篇
      • 译文:0篇
      • 评论:1条
      文章存档
      最新评论