调用存储过程

调用存储过程的几种方法
存储过程如下：

CREATE OR REPLACE PACKAGE relationOFowner AS
       PROCEDURE SBowner(GC_OID IN NUMBER,ERRFID OUT VARCHAR2);
       FUNCTION ISowner(SBFID IN NUMBER,STR IN VARCHAR2) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY relationOFowner as

  -- 判断是否正确建立从属关系的函数
  FUNCTION ISowner(SBFID IN NUMBER,STR IN VARCHAR2) RETURN NUMBER
  IS
  COUS NUMBER;
  NUMS NUMBER;
  OW NUMBER;
  strsql varchar2(500);

  type type_cur is ref cursor;
  c1 type_cur;
  BEGIN
       SELECT COUNT(*) INTO COUS FROM B$COMMON_N WHERE G3E_FID=SBFID;
       IF COUS>1 THEN -- 判断是否跟正确的设备建有从属关系
          strsql :='select COUNT(*) from b$common_n where g3e_id in (
           select owner1_id from b$common_n where g3e_fid =' || SBFID || ' AND SUBSTR(LTT_STATUS,1,1)<>''I'' AND LTT_STATUS<>''DELETE'' AND LTT_ID<>0 
           union select owner2_id from b$common_n where g3e_fid =' || SBFID || ' AND SUBSTR(LTT_STATUS,1,1)<>''I'' AND LTT_STATUS<>''DELETE'' AND LTT_ID<>0) 
           and g3e_fno in ' || STR || '';
       ELSE
           strsql := 'select COUNT(*) from b$common_n where g3e_id in (
           select owner1_id from b$common_n where g3e_fid =' || SBFID || ' union 
           select owner2_id from b$common_n where g3e_fid =' || SBFID || ' ) 
           and g3e_fno in ' || STR || '';
       END IF;
       execute immediate strsql;
       open c1 for strsql;
          fetch c1 into NUMS;
       close c1; 

       IF NUMS>0 THEN  --有从属关系
          OW := 1; 
       ELSE  --没有从属关系
          OW := 0;
       END IF;
       RETURN OW;
  END;

  -- 配网需要调用的从属关系的判断的过程，返回错误数据给配网
  PROCEDURE SBowner(GC_OID IN NUMBER,errFID OUT VARCHAR2)
  IS
  SQLSTR varchar2(100);
  COU NUMBER;
  AZWZ VARCHAR2(50);
  FIDSTR VARCHAR2(200);

  --定义游标，查出该竣工单所编辑的设备
  cursor c_emp is 
         select distinct g3e_fid,g3e_fno from CV_GG_WM_SMZQ_DZLB  where gcbh=GC_OID and SUBSTR(ltt_STATUS,1,1)<>'I' 
         AND G3E_FNO IN (146,148,143,173,147,144,90,180,39) 
         and g3e_fid not in (select g3e_fid from CV_GG_WM_SMZQ_DZLB where gcbh=GC_OID and ltt_status='DELETE')
         order by g3e_fid;
  BEGIN   
       FIDSTR:=NULL;
       --循环编辑设备结果集
       FOR R_EMP IN C_EMP LOOP
           --判断从属关系
          IF R_EMP.G3E_FNO = 146 THEN  --10kV开关  
             select COUNT(*) INTO COU from b$GG_PD_ZFHWG_N where g3e_fid =R_EMP.G3E_FID; -- dlt=''
             IF COU>1 THEN
                 select DLT INTO AZWZ from b$GG_PD_ZFHWG_N where g3e_fid =R_EMP.G3E_FID AND SUBSTR(LTT_STATUS,1,1)<>'I' AND LTT_STATUS<>'DELETE' AND LTT_ID<>0;
             ELSE
                 select DLT INTO AZWZ from b$GG_PD_ZFHWG_N where g3e_fid =R_EMP.G3E_FID;
             END IF;
             IF AZWZ='户外' THEN  -- "安装位置"为"户外"的开关从属于电杆
                 SQLSTR := '(201)';
             END IF;
             IF AZWZ='户内' THEN  -- "户内"则从属于电房或箱式设备
                 SQLSTR := '(163,142,149)';
             ELSE
                 SQLSTR := '(0)';
             END IF;

          END IF;

          IF R_EMP.G3E_FNO = 148 THEN  --变压器，跟电房、箱式设备、电杆建从属关系
             SQLSTR := '(163,142,149,201)';
          END IF;

          -- 站房母线、站房接地刀闸、站房电缆头、站房引线、DTU  （跟电房、箱式设备建从属关系）
          IF R_EMP.G3E_FNO = 143 or R_EMP.G3E_FNO = 173 or R_EMP.G3E_FNO = 147 or R_EMP.G3E_FNO = 144 or R_EMP.G3E_FNO = 90 THEN 
             SQLSTR := '(163,142,149)';
          END IF;

          IF R_EMP.G3E_FNO = 180 or R_EMP.G3E_FNO = 39 THEN  -- FTU、CT （跟开关建从属关系）
             SQLSTR := '(146)';
          END IF;
          -- 耦合器（还没建，暂时没有这个设备）
          IF SQLSTR<>'(0)' THEN
              IF ISOWNER(R_EMP.G3E_FID,SQLSTR)=0 THEN --没有建立正确的从属关系
                  IF FIDSTR IS NULL THEN
                     FIDSTR:=R_EMP.G3E_FID;
                  ELSE
                     FIDSTR:=FIDSTR || ',' || R_EMP.G3E_FID;
                  END IF;
                  --RAISE_APPLICATION_ERROR (-20001, '开关' || R_EMP.G3E_FID || '，请建立正确的从属关系在会审！');
              END IF;
          END IF;

       END LOOP;
       IF FIDSTR IS NOT NULL THEN
          ERRFID:='以下设备没有建立正确的从属关系：' || FIDSTR;
       ELSE
           ERRFID:=NULL;
       END IF;
  END;
END relationOFowner;
/

1、jdbc调用

public class SpringStore extends JdbcDaoSupport {

	/**
	 * 方法说明：调用GIS的存储过程
	 * 
	 * @param oid 是配网单的OID，需要传入参数。
	 * @return String  返回的字符串有值，就不让通过审核，给出提示。返回字符串为空，就正常。
	 */
	public String checkEquipmentGISOFowner(Long oid) throws Exception {
		CallableStatement proc = null;
		String result;
		try {
			proc = this.getDataSource().getConnection().prepareCall(
					"call RELATIONOFOWNER.SBowner(?,?)");
			proc.setLong(1, oid); // 传入参数
			proc.registerOutParameter(2, Types.VARCHAR);  // 返回值
			proc.execute();
			result = proc.getString(2);
			System.out.println(result);
			return result;
		} catch (RuntimeException e) {
			e.printStackTrace();
			return "";
		}

	}
}