1.首先编写一个测试用的 function
1 CREATE OR REPLACE FUNCTION getDealmainNo_test(dealdate IN varchar2, 2 productcodename in varchar2) 3 /*********************************************************** 4 * linc 2018-01-09 5 * 方法：获取序列编号 6 * 入参：成交日期，产品代码 7 * 返回：序列编号 8 ***********************************************************/ 9 RETURN varchar2 AS 10 today varchar2(10); 11 seqno integer; 12 begindate varchar2(20) := dealdate || ' 00:00:00'; 13 enddate varchar2(20) := dealdate || ' 23:59:59'; 14 seqnoname varchar2(30); 15 pragma autonomous_transaction; 16 BEGIN 17 seqno := 0; 18 select to_char(sysdate, 'yyyy-mm-dd') into today from dual; 19 if today = dealdate then 20 execute immediate 'select seq_' || productcodename || 21 '_no.nextval from dual' 22 into seqno; 23 else 24 begin 25 select nvl(max(to_number(substr(s.tno, -6))), 0) + 1 26 into seqno 27 from (select s.tno 28 from table_A s 29 where s.dealdate between 30 to_date(begindate, 'yyyy-mm-dd hh24:mi:ss') and 31 to_date(enddate, 'yyyy-mm-dd hh24:mi:ss') 32 and s.tcode = productcodename) s ; 33 34 exception 35 when others then 36 return ''; 37 end; 38 end if; 39 seqnoname := productcodename || replace(dealdate, '-', '') || 40 lpad(seqno, 6, '0'); 41 RETURN seqnoname; 42 END getDealmainNo_test; |
2.使用java对func进行调用 每次获得一个最大的成交编号 ；
1 package main.java.javaprictice; 2 3 import main.java.C3P0XmlSimplify; 4 import org.testng.annotations.Test; 5 6 import java.sql.*; 7 import java.text.SimpleDateFormat; 8 import java.util.Date; 9 10 /** 11 * Created by linbo.yang on 2018/1/9. 12 */ 13 public class CallOracleFunc { 14 Connection conn = null; 15 CallableStatement callableStatement = null; 16 PreparedStatement ps = null; 17 ResultSet rs; 18 19 public Integer save(String no) { 20 21 Integer rowCount=null ; 22 String sql="insert into table_A(dealdate,tno,tdate,tcode) values(?,?,?,?) "; 23 conn = C3P0XmlSimplify.getInstance().getConnection(); 24 //ddate 25 SimpleDateFormat df =new SimpleDateFormat("yyyy-MM-dd HH24:mm:ss"); 26 String date = df.format(new Date()); 27 Timestamp timestamp = Timestamp.valueOf(date); 28 //dealdate 29 SimpleDateFormat df1 =new SimpleDateFormat("yyyy-MM-dd"); 30 String dealdate = df1.format(new Date()); 31 32 33 try { 34 ps = conn.prepareStatement(sql); 35 ps.setDate(1,java.sql.Date.valueOf("2018-01-08")); 36 ps.setString(2,no); 37 ps.setTimestamp(3,timestamp); 38 ps.setString(4,"IBO"); 39 rowCount= ps.executeUpdate(); 40 System.out.println("rowCount"+rowCount); 41 } catch (SQLException e) { 42 e.printStackTrace(); 43 }finally { 44 C3P0XmlSimplify.releaseSources(conn); 45 return rowCount ; 46 } 47 48 49 } 50 51 52 53 54 55 public String getDealmainNo() { 56 String string = null; 57 try { 58 59 conn = C3P0XmlSimplify.getInstance().getConnection(); 60 /* ?=call getDealmainNo(?,?); 61 * 第一个问号（？） 代表输入值，但要制定输入类型:callableStatement.registerOutParameter(1, Types.VARCHAR); 62 * 第二个问号（？） 代表输入值，通过callableStatement.setString（）方法类制定入参的类型 ； 63 * 。。。。。。。 64 * */ 65 callableStatement = conn.prepareCall("{?=call getDealmainNo_test(?,?)}"); 66 callableStatement.registerOutParameter(1, Types.VARCHAR); 67 callableStatement.setString(2, "2018-01-08"); 68 callableStatement.setString(3, "IBO"); 69 callableStatement.execute(); 70 string = callableStatement.getString(1); 71 System.out.println(string); 72 73 } catch (Exception e) { 74 e.printStackTrace(); 75 } finally { 76 C3P0XmlSimplify.releaseSources(conn); 77 return string; 78 } 79 } 80 @Test(threadPoolSize = 3,invocationCount = 10,timeOut = 100000) 81 public void getDealmainNo_() { 82 CallOracleFunc c=new CallOracleFunc(); 83 String dealmainNo = c.getDealmainNo(); 84 Integer row = c.save(dealmainNo); 85 System.out.println("dealmainNo="+dealmainNo+" ;row="+row); 86 87 } 88 89 90 public static void main(String[] args) { 91 CallOracleFunc c=new CallOracleFunc(); 92 String dealmainNo = c.getDealmainNo(); 93 Integer row = c.save(dealmainNo); 94 System.out.println("dealmainNo="+dealmainNo+" ;row="+row); 95 96 97 } 98 99 100 } |
--数据的相关脚本
1 create table table_A( 2 tid number , 3 dealdate date , 4 tcode varchar2(100), 5 tno varchar2(100), 6 tdate timestamp 7 ) ; 8 drop table table_A ; 9 select * from table_A t where t.tid is null ; 10 delete table_A t where t.tid is null ; 11 insert into table_A 12 select v.ID, 13 v.DEALDATE, 14 v.PRODUCTCODE, 15 v.NO, 16 v.CREATETIME 17 from v_product_deal_main v ; 18 insert into table_A(tno,tdate) values(?,?) ; 19 20 select nvl(max(to_number(substr(s.tno, -6))), 0) + 1 21 from (select s.tno 22 from table_A s 23 where s.dealdate between 24 to_date('2018-01-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and 25 to_date('2018-01-08 23:00:00', 'yyyy-mm-dd hh24:mi:ss') 26 and s.tcode = 'IBO') s |
上文内容不用于商业目的，如涉及知识产权问题，请权利人联系博为峰小编(021-64471599-8017)，我们将立即处理。
