<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<%@ page contentType=“text/html; charset=gb2312“ %>
<link href="../jsp.css" type=text/css rel=stylesheet>
</head>
<body><%@ page language="java"%>
<%@ page import="
java.io.*,
java.sql.*,
javax.servlet.*,
javax.servlet.http.*,
java.util.Properties,
weblogic.db.jdbc.*
"%>try,try,forward,forward,finished.<%
Connection conn = null;
try {
Driver myDriver = (Driver) Class.forName("weblogic.jdbc.pool.Driver").newInstance();
conn = myDriver.connect("jdbc:weblogic:pool:OraclePool",null );
Statement stmt = null;
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
String sql="create view daysdwd(jgmc,gnsl,gnsr,gjsl,gjsr) as"+
"select a.jgmc,b.Sdgnsl,b.Sdgnsr,nvl(c.Sdgjsl,0),nvl(c.Sdgjsr,0) from pbjginfo a, "+
"( select jgdm,count(*) as Sdgnsl,"+
"sum(zzf) as Sdgnsr"+
" from BsDzzjsdsj where gb='0' and sjrq>='20000101' and sjrq<='20030909' group by jgdm ) b, "+
"( select jgdm,count(*) as Sdgjsl,"+
"sum(zzf) as Sdgjsr"+
" from BsDzzjsdsj where gb='1' and sjrq>='20000101' and sjrq<='20030909' group by jgdm ) c "+
" where a.jgdm = b.jgdm(+) and a.jgdm=c.jgdm(+) ";
stmt.executeQuery(sql);
out.println("恭喜,用户已修改成功!!");
}
catch (Exception e){
out.print("Connection Failed" + e.getMessage());
}
finally {
if(conn != null)
try{
conn.close();
} catch (Exception e){
out.print("Exception:" + e);
}
}
%></body>
</html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<%@ page contentType=“text/html; charset=gb2312“ %>
<link href="../jsp.css" type=text/css rel=stylesheet>
</head>
<body><%@ page language="java"%>
<%@ page import="
java.io.*,
java.sql.*,
javax.servlet.*,
javax.servlet.http.*,
java.util.Properties,
weblogic.db.jdbc.*
"%>try,try,forward,forward,finished.<%
Connection conn = null;
try {
Driver myDriver = (Driver) Class.forName("weblogic.jdbc.pool.Driver").newInstance();
conn = myDriver.connect("jdbc:weblogic:pool:OraclePool",null );
Statement stmt = null;
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
String sql="create view daysdwd(jgmc,gnsl,gnsr,gjsl,gjsr) as"+
"select a.jgmc,b.Sdgnsl,b.Sdgnsr,nvl(c.Sdgjsl,0),nvl(c.Sdgjsr,0) from pbjginfo a, "+
"( select jgdm,count(*) as Sdgnsl,"+
"sum(zzf) as Sdgnsr"+
" from BsDzzjsdsj where gb='0' and sjrq>='20000101' and sjrq<='20030909' group by jgdm ) b, "+
"( select jgdm,count(*) as Sdgjsl,"+
"sum(zzf) as Sdgjsr"+
" from BsDzzjsdsj where gb='1' and sjrq>='20000101' and sjrq<='20030909' group by jgdm ) c "+
" where a.jgdm = b.jgdm(+) and a.jgdm=c.jgdm(+) ";
stmt.executeQuery(sql);
out.println("恭喜,用户已修改成功!!");
}
catch (Exception e){
out.print("Connection Failed" + e.getMessage());
}
finally {
if(conn != null)
try{
conn.close();
} catch (Exception e){
out.print("Exception:" + e);
}
}
%></body>
</html>
应为: stmt.executeUpdate(sql);
将SQL语句COPY到SQLPLUS下执行,如果没有问题,那么看看stmt.executeQuery(sql);这句话是否应该换成stmt.excuteUpdate(sql);
变成一个select 语句后 stmt.executeQuery(sql),没问题。
stmt.excuteUpdate()肯定支持创建视图,SQLPLUS报错,可能是SQL语句组织的不对,你再看看SQL的语法,另外注意变量引号的问题.
sql中去掉 "create view daysdwd(jgmc,gnsl,gnsr,gjsl,gjsr) as"+ 一行,
变成一个select 语句后 stmt.executeQuery(sql),jsp网页能正常显示。
说明sql语句没问题。
但加上create view ..后,用stmt.executeUpdate(sql);jsp网页提示:
Connection FailedORA-00905: 缺少关键字。