1、最快的方式,一条SQL执行完毕(耗时92.266S):
SELECT COUNT(DV.DEVICE_ID)
FROM PROD_FUNCTION_LOCATION FL,
PROD_FL_DEVICE FD,
PROD_DEVICE DV,
PUB_CLASSIFY CL,
(SELECT FL.VOLTAGE_LEVEL, FL.FUNCTION_LOCATION_CODE
FROM PROD_FUNCTION_LOCATION FL
WHERE FL.FUNCTION_LOCATION_NAME LIKE '%kV电压等级区%'
AND FL.VOLTAGE_LEVEL > 1000
AND FL.COPY_FLAG = 0) TBL
WHERE DV.DEVICE_ID = FD.DEVICE_ID
AND DV.VOLTAGE_LEVEL != TBL.VOLTAGE_LEVEL
AND FD.DEVICE_CLASSIFY_CODE = CL.ALIAS_NAME
AND CL.CODE LIKE '0101%'
AND FD.FUNCTION_LOCATION_ID = FL.FUNCTION_LOCATION_ID
AND FL.COPY_FLAG = 0
AND FL.FUNCTION_LOCATION_CODE LIKE TBL.FUNCTION_LOCATION_CODE || '-%';
2、稍慢的方式,通过JDBC实现(133S):
package test.batch;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import test.DBUtil;
public class VolCount {
private List<VO1> getList() {
StringBuffer sbSQL = new StringBuffer(200);
sbSQL.append("SELECT FL.VOLTAGE_LEVEL, FL.FUNCTION_LOCATION_CODE");
sbSQL.append(" FROM PROD_FUNCTION_LOCATION FL");
sbSQL.append(" WHERE FL.FUNCTION_LOCATION_NAME LIKE '%kV电压等级区%'");
sbSQL.append(" AND FL.VOLTAGE_LEVEL > 1000");
sbSQL.append(" AND FL.COPY_FLAG = 0");
List<VO1> values = new ArrayList<VO1>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 设置数据库连接
conn = DBUtil.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sbSQL.toString());
VO1 vo = null;
while (rs.next()) {
vo = new VO1();
vo.vol = rs.getInt(1);
vo.code = rs.getString(2);
values.add(vo);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
DBUtil.closeConnection(null, stmt, conn);
}
return values;
}
private int getSingleCount(int vol, String code) {
StringBuffer sbSQL = new StringBuffer(411);
sbSQL.append("SELECT COUNT(DV.DEVICE_ID)");
sbSQL.append(" FROM PROD_FUNCTION_LOCATION FL,");
sbSQL.append(" PROD_FL_DEVICE FD,");
sbSQL.append(" PROD_DEVICE DV,");
sbSQL.append(" PUB_CLASSIFY CL");
sbSQL.append(" WHERE DV.DEVICE_ID = FD.DEVICE_ID");
sbSQL.append(" AND DV.VOLTAGE_LEVEL != ?");
sbSQL.append(" AND FD.DEVICE_CLASSIFY_CODE = CL.ALIAS_NAME");
sbSQL.append(" AND CL.CODE LIKE '0101%'");
sbSQL.append(" AND FD.FUNCTION_LOCATION_ID = FL.FUNCTION_LOCATION_ID");
sbSQL.append(" AND FL.COPY_FLAG = 0");
sbSQL.append(" AND FL.FUNCTION_LOCATION_CODE LIKE ?");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int value = 0;
try {
// 设置数据库连接
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sbSQL.toString());
pstmt.setInt(1, vol);
pstmt.setString(2, code+"-%");
rs = pstmt.executeQuery();
if (rs.next()) {
value = rs.getInt(1);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
DBUtil.closeConnection(null, pstmt, conn);
}
return value;
}
class VO1 {
int vol;
String code;
}
public static void main(String[] args) {
VolCount v = new VolCount();
int total = 0;
long time = System.currentTimeMillis();
for(VO1 vo:v.getList()){
total+=v.getSingleCount(vo.vol, vo.code);
}
System.out.println("total:"+total+" time:"+(System.currentTimeMillis()-time)/1000);
}
}
3、最慢的方式,游标实现(在我的耐心范围内都没有出现结果):
CREATE OR REPLACE PROCEDURE P_DATA_AUDIT_DEV_VOL(DETAIL_ID IN INTEGER,
COLLECT_ID IN INTEGER) IS
V_TOTAL INTEGER;
V_TEMP INTEGER;
V_DETAIL_ID INTEGER;
V_DATE DATE;
CURSOR C_OUTER IS
SELECT FL.VOLTAGE_LEVEL, FL.FUNCTION_LOCATION_CODE
FROM PROD_FUNCTION_LOCATION FL
WHERE FL.FUNCTION_LOCATION_NAME LIKE '%kV电压等级区%'
AND FL.VOLTAGE_LEVEL > 1000
AND FL.COPY_FLAG = 0;
CURSOR C_INNER(V_CODE VARCHAR2, V_VOL INTEGER) IS
SELECT COUNT(DV.DEVICE_ID) COUNT
FROM PROD_FUNCTION_LOCATION FL,
PROD_FL_DEVICE FD,
PROD_DEVICE DV,
PUB_CLASSIFY CL
WHERE DV.DEVICE_ID = FD.DEVICE_ID
AND DV.VOLTAGE_LEVEL != V_VOL
AND FD.DEVICE_CLASSIFY_CODE = CL.ALIAS_NAME
AND CL.CODE LIKE '0101%'
AND FD.FUNCTION_LOCATION_ID = FL.FUNCTION_LOCATION_ID
AND FL.COPY_FLAG = 0
AND FL.FUNCTION_LOCATION_CODE LIKE V_CODE || '-%';
BEGIN
V_TOTAL := 0;
V_TEMP := 0;
V_DETAIL_ID := DETAIL_ID;
V_DATE := SYSDATE;
FOR O_REC IN C_OUTER LOOP
FOR I_REC IN C_INNER(O_REC.FUNCTION_LOCATION_CODE, O_REC.VOLTAGE_LEVEL) LOOP
DBMS_OUTPUT.PUT_LINE('COUNT'||I_REC.COUNT);
V_DETAIL_ID := V_DETAIL_ID + I_REC.COUNT;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('V_DETAIL_ID:' || V_DETAIL_ID || ' TIME:' ||
TO_NUMBER(SYSDATE - V_DATE) * 24 * 60 * 60 || 's');
EXCEPTION
WHEN OTHERS THEN
IF C_INNER%ISOPEN THEN
CLOSE C_INNER;
END IF;
IF C_OUTER%ISOPEN THEN
CLOSE C_OUTER;
END IF;
END;
/
分享到:
相关推荐
用所学的pl/sql知识做一下程序,要求可能出现的异常要处理: 1、写一个存储函数 给一个地区id,查找这个地区工资最高的部门的名称,并输出。 CREATE OR REPLACE FUNCTION fun_getDept( p_rid s_region.id%TYPE)...
mysql procedure源码for mysql-essential-5.1.55-win32
最近由于工作需要,简单了解了下SQL Server 2005 数据库创建简单的在存储过程。一、首先说明如何创建存储过程: CREATE PROCEDUER my_pro @inputDate varchar ,//声明输入变量 @Result varchar(255) output //声明...
学习使用存储过程(Stored Procedure),是ASP程序员的必须课之一。所有的大型数据库SPAN>都支持存储过程,比如Oracle、MS SQL等,(但MS Access不支持,不过,在Access里可以使用参数化的查询)
为解决编程模型严重阻碍多核处理器性能的进一步提升,尝试将编程单位即方法(proceduer)引入 到多核处理器结构设计中,提出了面向高性能计算的Metric(Method Centric)以方法为中心多核架构且采用 编译技术,...