CREATE OR REPLACE PROCEDURE JL_P_METER_DET_ZS_BASE ( V_METER_TYPE CHAR, V_PRINT_SN NUMERIC, o_cursor out Highsoft_Types.dsCursor) AS /******************************************************************* 功能: 打印电能表检定证书 参数说明: 返回结果 V_PRINT_SN 打印序号 返回统计的结果集 修改说明: *************************************************************/ v_error_code number(5) := -10; --内部错误代码 BEGIN --删除临时表数据 delete from JL_TEMP_METER_DETECT_ZS_BASE where PRINT_SN = V_PRINT_SN; ---基本信息 v_error_code := -30;
--插入基本信息 insert into JL_TEMP_METER_DETECT_ZS_BASE(Print_Sn,App_No,Assets_No,det_no, MADE_NO,UNIT,CONCLUSION,MASTER,DET_MAN,VERIFYER,Det_Date,YXQ,Temperature,Wetness, Zgjc,Qdsy,Qidsy,Qddl,Gpny,Xhcs,Zdbzpcwc,Zdxlwc,Zdxlzqwc,Ra,FREQUENCY,USER_NO,REGULATION) select V_PRINT_SN,JTM.APP_NO,JTM.ASSETS_NO,JMD.DET_NO, JMD.MADE_NO,nvl(JTM.UNIT,JMD.UNIT),F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.CONCLUSION), NVL(JTM.MASTER,JMD.MASTER),NVL(JTM.DET_MAN,JMD.DET_MAN),NVL(JTM.VERIFYER,JMD.VERIFYED),JMD.DET_DATE,JTM.YXQ, JMD.TEMPERATURE,JMD.WETNESS,F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.D_CHECK), F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.SHUNT_RUN),F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.TRIG_CURNT), JMD.START_CURNT,F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.MAX_VOLT),'',S1,'','',NVL(JTM.REMARKS,JMD.REMARKS), JTM.FREQUENCE,JTM.USER_NO,F_GET_DISPLAY_VALUE('JL_DETECT_REGULATION',REGULATION) from JL_METER_DETECT JMD,JL_TMP_METER_DETECT_ZS_RECORD JTM WHERE JMD.APP_NO = JTM.APP_NO AND JMD.ASSETS_NO = JTM.ASSETS_NO AND JTM.PRINT_SN = V_PRINT_SN; v_error_code := -100; --更新制造厂家 型号 精度等信息 UPDATE JL_TEMP_METER_DETECT_ZS_BASE JT SET (MODEL_CODE,FACTORY,ACCU_LEVEL,MADE_DATE,MADE_STAND,VOLT_LEVEL,CURRNT,CONST,MADE_NO,JL_ASSIST_NAME,PHASE_LINE) = (SELECT F_GET_DISPLAY_VALUE('METER_MODEL_CODE',JM.MODEL_CODE), F_GET_DISPLAY_VALUE('METER_FACTRY_CODE',JM.FAC_CODE), F_GET_DISPLAY_VALUE('METER_ACCU_CODE',JM.ACCU_LEVEL_CODE), JM.MADE_DATE, F_GET_DISPLAY_VALUE('METER_MADE_STANDARD',JM.MADE_STDARD), F_GET_DISPLAY_VALUE('METER_VOLT_CODE',JM.RATED_VOLT_CODE), F_GET_DISPLAY_VALUE('METER_RATED_CURNT',JM.RATED_CURNT_CODE), F_GET_DISPLAY_VALUE('METER_CONST_NUM',JM.CONST)||F_GET_DISPLAY_VALUE('METER_CONST',JM.CONST_UNIT), JM.MADE_NO, F_GET_DISPLAY_VALUE('METER_FUNC_KIND',JM.FUNC_KIND_CODE), F_GET_DISPLAY_VALUE('METER_PHASE_LINE',JM.PHASE_LINE) FROM JL_METER JM WHERE JM.ASSETS_NO = JT.ASSETS_NO); v_error_code := -110;
IF (V_METER_TYPE <> 'S') THEN --三相表更新 UPDATE JL_TEMP_METER_DETECT_ZS_BASE JT SET (RJSWC,SDTQWC,ZDXLZQWC,XHCS) = (select F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JD.DAY_TIME_CONCLUSION),F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JD.SDTQ_CONCLUSION), JD.XLZQWC,F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JD.TEST_CONSTANT_CONCLUSION) from JL_METER_DETECT_MULTIFUNC JD where JD.ASSETS_NO = JT.ASSETS_NO AND JD.APP_NO = JT.APP_NO); v_error_code := -120; --更新最大需量误差 UPDATE JL_TEMP_METER_DETECT_ZS_BASE JT SET ZDXLWC = (SELECT XLWC FROM JL_METER_DETECT_XL JDX WHERE JT.APP_NO = JDX.APP_NO AND JT.ASSETS_NO = JDX.ASSETS_NO AND JDX.CSDL = 'Imax'); v_error_code := -130; END IF;
( V_METER_TYPE CHAR,
V_PRINT_SN NUMERIC,
o_cursor out Highsoft_Types.dsCursor) AS
/*******************************************************************
功能:
打印电能表检定证书
参数说明:
返回结果
V_PRINT_SN 打印序号
返回统计的结果集
修改说明:
*************************************************************/
v_error_code number(5) := -10; --内部错误代码
BEGIN
--删除临时表数据
delete from JL_TEMP_METER_DETECT_ZS_BASE where PRINT_SN = V_PRINT_SN; ---基本信息
v_error_code := -30;
--插入基本信息
insert into JL_TEMP_METER_DETECT_ZS_BASE(Print_Sn,App_No,Assets_No,det_no,
MADE_NO,UNIT,CONCLUSION,MASTER,DET_MAN,VERIFYER,Det_Date,YXQ,Temperature,Wetness,
Zgjc,Qdsy,Qidsy,Qddl,Gpny,Xhcs,Zdbzpcwc,Zdxlwc,Zdxlzqwc,Ra,FREQUENCY,USER_NO,REGULATION)
select V_PRINT_SN,JTM.APP_NO,JTM.ASSETS_NO,JMD.DET_NO,
JMD.MADE_NO,nvl(JTM.UNIT,JMD.UNIT),F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.CONCLUSION),
NVL(JTM.MASTER,JMD.MASTER),NVL(JTM.DET_MAN,JMD.DET_MAN),NVL(JTM.VERIFYER,JMD.VERIFYED),JMD.DET_DATE,JTM.YXQ,
JMD.TEMPERATURE,JMD.WETNESS,F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.D_CHECK),
F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.SHUNT_RUN),F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.TRIG_CURNT),
JMD.START_CURNT,F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JMD.MAX_VOLT),'',S1,'','',NVL(JTM.REMARKS,JMD.REMARKS),
JTM.FREQUENCE,JTM.USER_NO,F_GET_DISPLAY_VALUE('JL_DETECT_REGULATION',REGULATION)
from JL_METER_DETECT JMD,JL_TMP_METER_DETECT_ZS_RECORD JTM
WHERE JMD.APP_NO = JTM.APP_NO AND
JMD.ASSETS_NO = JTM.ASSETS_NO AND
JTM.PRINT_SN = V_PRINT_SN;
v_error_code := -100;
--更新制造厂家 型号 精度等信息
UPDATE JL_TEMP_METER_DETECT_ZS_BASE JT SET
(MODEL_CODE,FACTORY,ACCU_LEVEL,MADE_DATE,MADE_STAND,VOLT_LEVEL,CURRNT,CONST,MADE_NO,JL_ASSIST_NAME,PHASE_LINE) =
(SELECT F_GET_DISPLAY_VALUE('METER_MODEL_CODE',JM.MODEL_CODE),
F_GET_DISPLAY_VALUE('METER_FACTRY_CODE',JM.FAC_CODE),
F_GET_DISPLAY_VALUE('METER_ACCU_CODE',JM.ACCU_LEVEL_CODE),
JM.MADE_DATE,
F_GET_DISPLAY_VALUE('METER_MADE_STANDARD',JM.MADE_STDARD),
F_GET_DISPLAY_VALUE('METER_VOLT_CODE',JM.RATED_VOLT_CODE),
F_GET_DISPLAY_VALUE('METER_RATED_CURNT',JM.RATED_CURNT_CODE),
F_GET_DISPLAY_VALUE('METER_CONST_NUM',JM.CONST)||F_GET_DISPLAY_VALUE('METER_CONST',JM.CONST_UNIT),
JM.MADE_NO,
F_GET_DISPLAY_VALUE('METER_FUNC_KIND',JM.FUNC_KIND_CODE),
F_GET_DISPLAY_VALUE('METER_PHASE_LINE',JM.PHASE_LINE)
FROM JL_METER JM
WHERE JM.ASSETS_NO = JT.ASSETS_NO);
v_error_code := -110;
IF (V_METER_TYPE <> 'S') THEN --三相表更新
UPDATE JL_TEMP_METER_DETECT_ZS_BASE JT SET
(RJSWC,SDTQWC,ZDXLZQWC,XHCS) =
(select F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JD.DAY_TIME_CONCLUSION),F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JD.SDTQ_CONCLUSION),
JD.XLZQWC,F_GET_DISPLAY_VALUE('VERIFY_PASS_SIGN',JD.TEST_CONSTANT_CONCLUSION)
from JL_METER_DETECT_MULTIFUNC JD
where JD.ASSETS_NO = JT.ASSETS_NO AND JD.APP_NO = JT.APP_NO);
v_error_code := -120;
--更新最大需量误差
UPDATE JL_TEMP_METER_DETECT_ZS_BASE JT SET
ZDXLWC = (SELECT XLWC FROM JL_METER_DETECT_XL JDX
WHERE JT.APP_NO = JDX.APP_NO AND
JT.ASSETS_NO = JDX.ASSETS_NO AND
JDX.CSDL = 'Imax');
v_error_code := -130;
END IF;
--主管 核验员 校验员在证书打印出来以后由本人签字,更新为空
update JL_TEMP_METER_DETECT_ZS_BASE set
MASTER = null,
VERIFYER = null,
DET_MAN =null
WHERE PRINT_SN = V_PRINT_SN;
v_error_code := -140;
--更新检定结论
/* update JL_TEMP_METER_DETECT_ZS_BASE set
CONCLUSION = '依据 ' || F_GET_DISPLAY_VALUE('JL_DETECT_REGULATION',REGULATION) || CONCLUSION
WHERE PRINT_SN = V_PRINT_SN;
v_error_code := -150; */
COMMIT;
OPEN o_cursor FOR
select *
from JL_TEMP_METER_DETECT_ZS_BASE
where PRINT_SN = V_PRINT_SN
order by ASSETS_NO;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
p_pub_error_log(v_error_code, SQLCODE, SQLERRM, 'JL_P_METER_DET_ZS_BASE');
OPEN o_cursor FOR
select *
from JL_TEMP_METER_DETECT_ZS_BASE
where PRINT_SN = V_PRINT_SN
order by ASSETS_NO;END;
I_DATE IN varchar(5),
O_DATE OUT varchar(5)
)
begin
.....数据处理
exception
...... 异常处理
end;下面是java调用函数Set_Data
Connection aConn= DriverManager.getConnection(databaseurl,databaseuser,databasepassword);
CallableStatement call = aConn.prepareCall("{call Set_Data(?,?)}");
//stmt = aConn.prepareStatement(sql11.toString());
call.setString(1, “输入A”);//
call.registerOutParameter(2,“输出B”);
ResultSet rs= call.executeQuery();
String str= cmt.getString(2);//获取输出值
call.close();
aConn.close();
以前没写过呀,请帮看看。谢谢了。