一.在Sybase中創建存儲過程如下：
Sql代碼：
- drop procedure getPageWiseData
- go
- create procedure getPageWiseData
- (
- @sqlStr varchar(8000),
- @start int,
- @limit int
- )
- as
- DECLARE @dt varchar(10) --生成臨時表的隨機數
- BEGIN
- --# variable to hold the first row number of the page.
- SELECT @dt= substring(convert(varchar, rand()), 3, 10) --一個字符型的隨機數
-
- SELECT @sqlStr = stuff(@sqlStr, 1, 7, 'select rownum=identity(12), ')
- SELECT @sqlStr = stuff(@sqlStr, charindex(' FROM ', upper(@sqlStr)), 6 ,' into tempdb..Lining' + @dt + ' from ')
- execute (@sqlStr)
- --# select the data with the calculated range for first and last row on page.
- select @sqlStr = 'select * from tempdb..Lining' + @dt + ' where rownum >= '+convert(varchar, @start)+' and rownum < '+convert(varchar, (@start+@limit))
- execute (@sqlStr)
- --刪除臨時表
- SELECT @sqlStr = 'DROP TABLE tempdb..Lining'+@dt
- EXECUTE (@sqlStr)
- END
二.用jdts驅動調用Sybase數據庫
原文參考自站長網：http://www.software8.co/wzjs/java/3541.html
Java代碼：
- public class JdbcSybaseProcedure {
- public static void main(String[] args) {
- Connection conn = null;
- CallableStatement cs = null;
- try {
- Class.forName("net.sourceforge.jtds.jdbc.Driver");
- conn = DriverManager.getConnection("jdbc:jtds:sybase://192.9.190.98:4100/inner_dbs", "emp", "empemp");
- //下面的意思要調用那個存儲過程，存儲過程名字是getPageWiseData;
- cs = conn.prepareCall("{call getPageWiseData(?,?,?)}");
- cs.setString(1, "select * from lps_mst order by pan");
- cs.setInt(2, 1);
- cs.setInt(3, 5);
- //執行存儲過程
- ResultSet rs = cs.executeQuery();
- while(rs.next()) {
- System.out.println(rs.getString("pan"));
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- try {
- cs.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }finally {
- cs = null;
- }
- try {
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }finally {
- conn = null;
- }
- }
- }
- }