Oracle存储过程和Java调用

--通过用户ID,查找出名字,以及城市
create or replace procedure testp1(my_id in tt3.id%type,my_user_name out tt3.user_name%type,my_city out tt3.city%type)
as
begin
  select user_name||'先生',city into my_user_name,my_city from tt3 where id=my_id;
end;
----------------------------
select * from tt3
------------------------
declare
  a1 varchar2(100);
  b1 varchar2(100);
begin
  testp1(1,a1,b1);
  dbms_output.put_line(a1);
  dbms_output.put_line(b1);
end;
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String dbURL = "jdbc:oracle:thin:@192.168.88.88:1521:orcl";
            Connection conn = DriverManager.getConnection(dbURL, "oa", "123456");
            System.out.println("成功获取连接,连接信息是:"+conn);
            CallableStatement prepareCall = conn.prepareCall("call testp1(?,?,?)");
prepareCall.registerOutParameter(2, Types.VARCHAR);
            prepareCall.registerOutParameter(3, Types.VARCHAR);
            prepareCall.setInt(1, 1);
            prepareCall.execute();
            String userName = prepareCall.getString(2);
            String city = prepareCall.getString(3);
            prepareCall.close();
            conn.close();
            System.out.println(userName);
            System.out.println(city);
        }catch(Exception e){
            System.out.println("异常:"+e.getMessage());
        }

 

posted @ 2020-05-20 01:40  YC_Muck  阅读(158)  评论(0编辑  收藏  举报