oracle 在java中调用存储过程，execute时被阻塞

ChangXiangzhong 2008-03-21 05:08:50



package com.jykj.util;



import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.Types;



import com.jykj.util.ConnPool;



public class ProcedureDriver {



	public ProcedureDriver() {

		String mac = "chang";

		String outer = "222.30.55.10";

		String inner = "192.168.1.2";

		int barId = 1;

		String ie = null;

		String desktop = null;

		String sql = "{ call bind(?,?,?,?,?,?) }";

		try {

			Connection conn = ConnPool.getConenction();

			CallableStatement proc = conn.prepareCall(sql);

			proc.setString(1, mac);

			proc.setString(2, outer);

			proc.setString(3, inner);

			proc.setInt(4, barId);

			proc.registerOutParameter(5, Types.VARCHAR);

			proc.registerOutParameter(6, Types.VARCHAR);







			proc.execute();//调试的时候一旦到这里就被阻塞







			ie = proc.getString(5);

			desktop = proc.getString(6);

			proc.close();

			conn.close();

		} catch (Exception e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		}

		System.out.println(ie);

		System.out.println(desktop);

	}



	public static void main(String[] args) {

		new ProcedureDriver();

	}



}



create or replace  

PROCEDURE Bind(   

  mac IN COMPUTER.COMP_MAC%TYPE, --'abc'   

  outer_ip IN COMPUTER.COMP_OUTERIP%TYPE, --222.30.55.11   

  inner_ip IN COMPUTER.COMP_INTERIP%TYPE, --'192.168.1.1'   

  bar IN COMPUTER.BAR_ID%TYPE, --1   

  url_ie OUT adresource.ADRES_URL%TYPE,   

  url_desktop OUT adresource.ADRES_URL%TYPE   

  )   

AS  

  

var NUMBER;   

  

--Initial a cursor   

CURSOR cur_url(bar_id IN AD2BAR.BAR_ID%TYPE)   

  IS  

  SELECT ADRES_URL   

  FROM ADRESOURCE    

    LEFT JOIN ADPLAN    

    ON ADRESOURCE.ADRES_ID = ADPLAN.ADRES_ID   

    LEFT JOIN AD2BAR   

    ON ADPLAN.PLAN_ID = AD2BAR.PLAN_ID   

  WHERE AD2BAR.BAR_ID = bar_id   

    AND ADPLAN.PLAN_START < SYSDATE   

    AND ADPLAN.PLAN_END > SYSDATE   

  ORDER BY ADRESOURCE.ADRES_TYPE;   

  

TYPE UrlTab IS TABLE OF adresource.ADRES_URL%TYPE;   

  

urls URLTAB;   

  

BEGIN  

  

SELECT COUNT(*) INTO var   

FROM computer   

WHERE computer.BAR_ID=bar   

  AND computer.COMP_MAC=mac   

  AND computer.COMP_INTERIP=inner_ip   

  AND computer.COMP_OUTERIP=outer_ip;   

  

IF var = 0 THEN  

--if the computer which contains the mac does not exist,   

--delete the record which has the same mac address and    

--re-insert it with the new information   

  

  --delete the record   

  DELETE FROM COMPUTER   

  WHERE COMPUTER.COMP_MAC = mac;   

  --insert the record   

  insert into computer( bar_id, comp_mac, comp_interip, comp_outerip, comp_time, comp_updatetime)   

  values(bar,mac,inner_ip,outer_ip,systimestamp, systimestamp);   

  --   

     

  --DBMS_OUTPUT.PUT_LINE('0');--trace tool   

ELSE-- This client already exists in the db.   

     

  --update the record to notify the computer's last power on   

  UPDATE COMPUTER   

  SET COMP_UPDATETIME=SYSTIMESTAMP   

  WHERE MAC = mac;   

     

  --DBMS_OUTPUT.PUT_LINE('NOT 0');--trace tool   

END IF;   

  

  OPEN cur_url(bar);   

  FETCH cur_url BULK COLLECT INTO urls;   

  CLOSE cur_url;   

     

  --DBMS_OUTPUT.PUT_LINE(urls(1));--trace tool   

  --DBMS_OUTPUT.PUT_LINE(urls(2));--trace tool   

  url_ie := urls(1);   

  url_desktop := urls(2);   

  

END;