存储过程Demo

存储过程写法：
create or replace procedure SP_Get_TicketCode(/**产生电子券号存储过程**/
sy_name1 in VARCHAR2,
sy_type_name in VARCHAR2,
ticket_code OUT VARCHAR2/**电子券号**/
)
AS
v_round_num VARCHAR2(6);--6位随即码
v_temp number;
v_ticket_code VARCHAR2(30);

begin
v_temp := 1;
while v_temp > 0 loop
select round(dbms_random.value(1,1000000)) into v_round_num from dual;

IF v_round_num < 10 then
v_ticket_code := sy_name1 || TO_CHAR (SYSDATE, 'yyMMdd') || sy_type_name || '00000' || v_round_num;
elsif v_round_num < 100 then
v_ticket_code := sy_name1 || TO_CHAR (SYSDATE, 'yyMMdd') || sy_type_name || '0000' || v_round_num;
elsif v_round_num < 1000 then
v_ticket_code := sy_name1 || TO_CHAR (SYSDATE, 'yyMMdd') || sy_type_name || '000' || v_round_num;
elsif v_round_num < 10000 then
v_ticket_code := sy_name1 || TO_CHAR (SYSDATE, 'yyMMdd') || sy_type_name || '00' || v_round_num;
elsif v_round_num < 100000 then
v_ticket_code := sy_name1 || TO_CHAR (SYSDATE, 'yyMMdd') || sy_type_name || '0' || v_round_num;
else
v_ticket_code := sy_name1 || TO_CHAR (SYSDATE, 'yyMMdd') || sy_type_name || v_round_num;
END IF;

select count(t.TICKET_ID) into v_temp from t_ec_ticket t where t.TICKET_CODE = v_ticket_code;

IF v_temp = 0 THEN --不重复
ticket_code := v_ticket_code;
exit;
else --重复了
ticket_code := '';
END IF;
end loop;
end SP_Get_TicketCode;


java中调用存储过程的代码：
/**
* 得到电子券号
*
* @return
*/
private String getTicketCode(String syName1,String syTypeName) throws Exception {

String ticketCode = "";
Connection con = null;
CallableStatement ps = null;
try {

con = JDBCUtil.getConn();
// 调用存储过程，生成一个电子券code
ps = con.prepareCall("{call SP_Get_TicketCode(?,?,?)}");
ps.setString(1, syName1);
ps.setString(2, syTypeName);
ps.registerOutParameter(3, Types.VARCHAR);
ps.execute();

// 获得输出结果
ticketCode = ps.getString(3);
if ("-1".equals(ps.getString(3))) {
throw new ServiceException(ps.getString(3));
}
} catch (SQLException e) {
throw new SystemException(e.getMessage());
} finally {
try {
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e1) {
throw new SystemException(e1.getMessage());
}
}

return ticketCode;
}