存储过程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;
}