Go语言视频零基础入门到精通

lll2nw

package com.ztf.test;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;

public class TestProcedure {

/**

* @param args

*/

public static void main(String[] args) {

String driver = "oracle.jdbc.driver.OracleDriver";

String url="jdbcracle:thin127.0.0.1:1521:ZTF";

String user = "scott";

String pass="tiger";

Connection conn=null;

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try {

conn = DriverManager.getConnection(url, user, pass);

} catch (SQLException e) {

e.printStackTrace();

}

//String sql = "{call pro_emp(?,?)}";

//try {

//CallableStatement cst =conn.prepareCall(sql);

//cst.setString(1, "SCOTT");

//cst.registerOutParameter(2, OracleTypes.INTEGER);

//cst.execute();

//System.out.println(cst.getInt(2));

//

//} catch (SQLException e) {

//e.printStackTrace();

//}

// 执行 带游标返回值的存储过程

//String sql ="{call pro_emp2(?)}";

//try {

//CallableStatement cst = conn.prepareCall(sql);

//cst.registerOutParameter(1, OracleTypes.CURSOR);

//cst.execute();

//ResultSet rs =(ResultSet) cst.getObject(1);

//while(rs.next()){

//System.out.print(rs.getString("DEPTNO")+"  ");

//System.out.print(rs.getString("SAL")+"  ");

//System.out.print(rs.getString("ENAME")+"  ");

//System.out.print(rs.getString("EMPNO")+"  ");

//System.out.println();

//}

//

//} catch (SQLException e) {

//e.printStackTrace();

//}


//

////以分页的方式显示数据

//int page =2;

//int size=3;

//String sql="{call pro_emp3(?,?,?,?)}";

//try {

//CallableStatement cst = conn.prepareCall(sql);

//cst.setInt(1, page);//设置显示的页数；

//cst.setInt(2, size);//设置每页显示的条数；

//cst.registerOutParameter(3,OracleTypes.CURSOR);

//cst.registerOutParameter(4, OracleTypes.INTEGER);

//cst.execute();

//ResultSet rs =(ResultSet)cst.getObject(3);

//int content = cst.getInt(4);

//System.out.println("当前结果共有："+content+"条");

//System.out.println("当前页为："+page);

//while(rs.next()){

//System.out.print(rs.getString("RN")+"  ");

//System.out.print(rs.getString("DEPTNO")+"  ");

//System.out.print(rs.getString("ENAME")+"  ");

//System.out.print(rs.getString("SAL")+"  ");

//System.out.print(rs.getString("EMPNO")+"  ");

//System.out.print(rs.getString("COMM")+"  ");

//System.out.println("");

//}

//

//} catch (SQLException e) {

//e.printStackTrace();

//}

//根据不同的表进行分页；

String sql="{call pro_emp4(?,?,?,?,?,?)}";

int page=1;

int sizes=3;

String tableName="EMP";

try {

CallableStatement cst =conn.prepareCall(sql);

cst.setInt(1, page);

cst.setInt(2, sizes);

cst.setString(3, tableName);

cst.registerOutParameter(4,OracleTypes.INTEGER);

cst.registerOutParameter(5,OracleTypes.INTEGER);

cst.registerOutParameter(6,OracleTypes.CURSOR);

cst.execute();

ResultSet rst = (ResultSet) cst.getObject(6);

System.out.println("总记录数 ："+cst.getInt(4));

System.out.println("总页数："+cst.getInt(5));

System.out.println("当前页数为："+page);

int totle = (rst.getMetaData().getColumnCount());

while(rst.next()){

for(int i=1;i<=totle;i++){

System.out.print(rst.getString(i)+"   ");

if(i==totle){

System.out.println("");

}

}

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

//分页的存储过程的

CREATE OR REPLACE PROCEDURE PRO_EMP3(PAGE NUMBER,SIZES NUMBER,C OUT SYS_REFCURSOR,T OUT NUMBER)

IS

CONTENTSIZE VARCHAR2(200):='SELECT COUNT(*) FROM EMP';

BEGIN

    EXECUTE IMMEDIATE CONTENTSIZE INTO T;

     OPEN C FOR SELECT * FROM (

       SELECT ROWNUM RN ,E.* FROM EMP E

       )WHERE RN >=(PAGE-1)*SIZES+1 AND RN<AGE*SIZES+1;           

END;

--根据动态的表创建分页 此时的表也为变量传递进来；

CREATE OR REPLACE PROCEDURE PRO_EMP4(

PAGE NUMBER,  --当前的页数

SIZES NUMBER,   --当前页显示的条数

TABIN VARCHAR , --表名

TOTLE OUT NUMBER,     --总的记录数

PAGESIZE OUT NUMBER,  --总的 页数

C OUT SYS_REFCURSOR   --返回的游标

)

IS

CONTENT VARCHAR2(200):='SELECT COUNT(*) FROM '|| TABIN;

S VARCHAR2(500);

PS NUMBER(10):=PAGE;

BEGIN

      EXECUTE IMMEDIATE CONTENT INTO TOTLE;

         

      --判断 传入的 页数 是否超出 中的的记录 并作处理

      IF MOD(TOTLE,SIZES)=0 THEN

         PAGESIZE:=TOTLE/SIZES;

      ELSE

           PAGESIZE:=FLOOR(TOTLE/SIZES)+1;

      END IF;

       IF PS<=0 THEN

         PS:=1;

      ELSIF PS>=TOTLE THEN

         PS:=PAGESIZE;

      END IF;

           
      S:='SELECT * FROM (SELECT ROWNUM RN ,E.* FROM '||TABIN||' E) T WHERE T.RN BETWEEN '||(PS*SIZES-SIZES+1)||' AND '||(PS*SIZES);      

      OPEN C FOR S;

END;

注意  ： 在面试的 时候 尤其是 在进行笔试 或者机试的时候  是经常考察的  也是经常犯错的

五菱

不错 谢谢楼主分享。