oracle函数如下：
Create or REPLACE FUNCTION AAAAA (
P_USER_ID_I IN VARCHAR2 --接收输入参数
)
RETURN SYS_REFCURSOR
AS
P_RESULT_SET_O SYS_REFCURSOR; --返回游标
X_SQL VARCHAR2(200);
BEGIN
X_SQL :='select user_id,user_name,email from user_mst
where
user_id like ''%' || P_USER_ID_I ||'%'''; --生成SQL语句
OPEN P_RESULT_SET_O FOR X_SQL;
RETURN P_RESULT_SET_O; --返回游标
END AAAAA;
java中调用，并处理：
CallableStatement cstmt = null;
ResultSet rs = null;
try {
String callSql = "{? = call AAAAA(?)}";
cstmt = conn.prepareCall(callSql);
cstmt.setString(2, "userName");
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(1);
if (rs != null) {
System.out.print("usercd");
System.out.print("userName");
System.out.println("EMAIL");
while (rs.next()) {
System.out.print(rs.getString(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println(rs.getString(3));
}
}
----------------MODIFY--------------------------------------------------
某列定义非空约束
SQL>ALTER TABLE office_organization
>MODIFY(desc VARCHAR2(20)
>CONSTRAINT nn_desc NOT NULL)
某列定义唯一关键字
SQL>ALTER TABLE office_organization
>MODIFY(office_name VATCHAR2(20)
>CONSTRAINT uq_officename UNIQUE)
----------------ADD--------------------------------------------------
ALTER TABLE jxzy.switch
>ADD (DESC VARCHAR2(30));
----------------DISABLE---------------------------------------------
使主键约束无效
SQL>ALTER TABLE switch DISABLE PRIMARY KEY
内容或简介：
/**
调用数据库里的一个函数
一个函数本质上一个返回一个结果的存储过程，这个例子示范了怎么调用有in、out和in/out参数的函数
***********************************/
CallableStatement cs;
try {
// 调用一个没有参数的函数; 函数返回 a VARCHAR
// 预处理callable语句
cs = connection.prepareCall("{? = call myfunc}");
// 注册返回值类型
cs.registerOutParameter(1, i);
// Execute and retrieve the returned value
cs.execute();
String retValue = cs.getString(1);
// 调用有一个in参数的函数; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncin(?)}");
// Register the type of the return value
cs.registerOutParameter(1, Types.VARCHAR);
// Set the value for the IN parameter
cs.setString(2, "a string");
// Execute and retrieve the returned value
cs.execute();
retValue = cs.getString(1);
// 调用有一个out参数的函数; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncout(?)}");
// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
String outParam = cs.getString(2); // OUT parameter
// 调用有一个in/out参数的函数; the function returns a VARCHAR
cs = connection.prepareCall("{? = call myfuncinout(?)}");
// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Set the value for the IN/OUT parameter
cs.setString(2, "a string");
// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
outParam = cs.getString(2); // IN/OUT parameter
} catch (SQLException e) {
}
1、在struts做查询数据的页面时，页面报错
javax.servlet.ServletException: Cannot find bean jlist in scope request
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:867)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:800)
org.apache.jsp.version.list_jsp._jspService(list_jsp.java:143)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
2、定位问题
1）action类代码如下，且经过验证list是存在数据的
List list = new ArrayList();
VersionBean bean = new VersionBean();
ServletContext app = request.getSession().getServletContext();
String sql = (String) app.getAttribute("f_listVersion");
try {
list = bean.qryVersion(sql);
} catch (SQLException e) {
logger.error("QryVersion failed！");
logger.error(e.getMessage());
return mapping.findForward("fail");
}
request.setAttribute("jlist",list);
return mapping.findForward("success");
2）jsp显示页面，代码也正常
<logic:present name="jlist" >
<logic:iterate id="list" name="jlist" scope="request">
<tr>
<td height="46" width="82" align="center">
<bean:write name="list" property="verID"/>
</td>
<td height="46" width="138" align="center">
<bean:write name="list" property="name"/>
</td>
<td height="46" width="58" align="center">
<bean:write name="list" property="status"/>
</td>
<td height="46" width="232" align="center">
<bean:write name="list" property="description"/>
</td>
<td height="46" width="50" align="center">
<input type="radio" value="<bean:write name="list" property="verID"/>" name="verID">
</td>
</tr>
</logic:iterate>
</logic:present>
3）接下来查看struts-config.xml 配置文件，发现forward redirect属性为true，当试着修改成false的时候，重启tomcat，就查询出来结果了。
<action
path="/version/listVersion"
type="com.webike.action.ListVersionAction"
scope="request">
<forward name="success" path="/version/list.jsp" redirect="true"/>
<forward name="fail" path="/version/index.html" redirect="true"/>
</action>
"^\d+$" //非负整数......
"^\d+$" //非负整数（正整数 + 0）
"^[0-9]*[1-9][0-9]*$" //正整数
"^((-\d+)|(0+))$" //非正整数（负整数 + 0）
"^-[0-9]*[1-9][0-9]*$" //负整数
"^-?\d+$" //整数
"^\d+(\.\d+)?$" //非负浮点数（正浮点数 + 0）
"^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$" //正浮点数
"^((-\d+(\.\d+)?)|(0+(\.0+)?))$" //非正浮点数（负浮点数 + 0）
"^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$" //负浮点数
"^(-?\d+)(\.\d+)?$" //浮点数
^[A-Za-z]+$ //匹配由26个英文字母组成的字符串
^[A-Z]+$ //匹配由26个英文字母的大写组成的字符串
^[a-z]+$ //匹配由26个英文字母的小写组成的字符串
^[A-Za-z0-9]+$ //匹配由数字和26个英文字母组成的字符串
^\w+$ //匹配由数字、26个英文字母或者下划线组成的字符串
^[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)+$ //匹配email地址
^[a-zA-z]+://匹配(\w+(-\w+)*)(\.(\w+(-\w+)*))*(\?\S*)?$ //匹配url