oracle的存储过程和函数以及java怎么调用oracle的存储过程和函数
过程和函数
a.将过程的执行权限授予其他用户：GRANT EXECUTE ON find_emp TO MARTIN;
b.删除过程：DROP PROCEDURE find_emp
c.函数的调用：SELECT fun_hello FROM DUAL;
d.查看所有的过程:select object_name,created,status from user_objects
where object_type in ('PROCEDURE','FUNCTION')
e.查看过程源码 select text from user_source where name='procedure_name';
１.在命令窗口中调用有一个in参数,一个out参数的存储过程
CREATE OR REPLACE PROCEDURE FIND_EMP(AID in CHAR,res out varchar2)
AS
ENAME DEPTO.NAME%TYPE;
BEGIN
SELECT NAME INTO ENAME from bankaccount WHERE ID=AID;
res:='姓名是:'||ename;
EXCEPTION
WHEN NO_DATA_FOUND THEN
res:='not found';
END FIND_EMP;
调用:
VAR SSS VARCHAR2;
EXECUTE FIND_EMP('1111',:SSS);
用java调用:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
public class Sub
{
static Connection co=null;
public static Connection connection()
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
co=DriverManager.getConnection
("jdbc:oracle:thin:@192.168.0.111:1521:emp","scott","admin");
}catch(Exception e)
{
System.out.println(e);
}
return co;
}
public void query() throws Exception
{
Connection conn=connection();
CallableStatement c=conn.prepareCall("{call FIND_EMP(?,?)}");
c.registerOutParameter(2,Types.VARCHAR);
c.setString(1,"11111");
c.execute();
String bal=c.getString(2);
System.out.println(bal);
conn.close();
}
public static void main(String args[]) throws Exception
{
Sub f=new Sub();
f.query();
}
}
２.在java中调用pl/sql函数例子
函数:
CREATE OR REPLACE FUNCTION query3(id1 in char) RETURN VARCHAR2
AS BAL VARCHAR2(20);
A_ID CHAR(5);
BEGIN
SELECT BALANCE INTO A_ID FROM BANKACCOUNT where ID=id1;
BAL:='您的余额为'||A_ID;
RETURN BAL;
END query3;
调用:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
class Function
{
static Connection co=null;
public static Connection connection()
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
co=DriverManager.getConnection
("jdbc:oracle:thin:@192.168.0.111:1521:emp","scott","admin");
}catch(Exception e)
{
System.out.println(e);
}
return co;
}
public void query() throws Exception
{
Connection conn=connection();
CallableStatement c=conn.prepareCall("{? = call QUERY3(?)}");
c.registerOutParameter(1,Types.VARCHAR);
c.setString(2,"11111");
c.execute();
String bal=c.getString(1);
System.out.println(bal);
conn.close();
}
public static void main(String args[]) throws Exception
{
Function f=new Function();
f.query();
}
}
３.调用一个in参数，一个out参数的另一种方法
CREATE OR REPLACE PROCEDURE TEST(VAL1 IN VARCHAR2,VAL2 OUT NUMBER) IS
IDD NUMBER;
BEGIN
SELECT ID INTO IDD FROM DEPTO WHERE ADDRESS=VAL1;
IF SQL%FOUND THEN
VAL2:=300;
END IF;
END;
调用：
DECLARE
VA1 DEPTO.ADDRESS%TYPE:='dalu';
VA2 NUMBER;
BEGIN
TEST(VA1,VA2);
DBMS_OUTPUT.PUT_LINE(VA2);
END;
4.利用函数/过程返回结果集.
利用函数:
定义包:create or replace package pkg_resultset
as
type yourtype is ref cursor;
function operation_resultset(phonenumber varchar2) return yourtype;
end pkg_resultset;
定义包体:
create or replace package body pkg_resultset
as
function operation_resultset(phonenumber varchar2) return yourtype
is
yourcur yourtype;
begin
open yourcur for SELECT a.operation_name, c.charge_kind, c.charge FROM
t_phone_operation a,t_phone_operation_charge b, t_cost c
where a.operation_id=b.operation_id and b.phone_num=phonenumber and
b.operation_id=c.operation_id;
return yourcur;
end operation_resultset;
end pkg_resultset;
在java中调用:
String phoneNum = querybuniessForm.getPhonenumber();
Connection conn=ConnectDataBase.connection();
ActionForward mg=null;
CallableStatement cs=null;
try
{
cs=conn.prepareCall("{call queryphonenumber(?,?)}");
cs.registerOutParameter(2,Types.VARCHAR);
cs.setString(1,phoneNum);
cs.execute();
String res=cs.getString(2);
ActionErrors errors=new ActionErrors();
cs.close();
ResultSet rs=null;
if(res.equals("无此手机号"))
{
errors.add("notfoundnumber",new ActionError("notfoundnumber"));
this.saveErrors(request,errors);
mg=mapping.getInputForward();
}else
{ errors.add("foundnumber",new ActionError("foundnumber"));
this.saveErrors(request,errors);
mg=mapping.getInputForward();
CallableStatement cs2=null;
cs2=conn.prepareCall("{?=call pkg_resultset.operation_resultset(?)}");
cs2.registerOutParameter(1,OracleTypes.CURSOR);
cs2.setString(2,phoneNum);
cs2.execute();
rs = ((OracleCallableStatement)cs2).getCursor(1);
while(rs.next())
{
System.out.print(rs.getString(1)+" ");//输出第一个字段
System.out.print(rs.getString(2)+" ");//输入第二个字段
System.out.print(rs.getString(3)+" ");
}........
利用过程:
定义包:
create or replace package pkg_resultset
as
type yourtype is ref cursor;
function operation_resultset(phonenumber varchar2) return yourtype;
end pkg_resultset;
定义包体:
create or replace package body wyj
as
PROCEDURE wyj1(phoneNum in varchar2,res OUT res1)
is
BEGIN
OPEN res FOR
SELECT a.operation_name, c.charge_kind, c.charge FROM
t_phone_operation a,t_phone_operation_charge b, t_cost c
where a.operation_id=b.operation_id and b.phone_num=phoneNum and
b.operation_id=c.operation_id;
END wyj1;
end wyj;
在java中调用:同上....
5.在java中调用存储过程返回结果集,并在jsp页面中显示出结果的方法.(以struts工程为例)
主要思路:在包内定义一个funcion或procudure,返回类型为游标,然后在java中得到结果集并遍历他们
以得到其中字段的结果,把他们放到一个bean中,并把bean放到ArrayList对象中,最后在jsp页面中用struts
标签得到结果.
在oracle中定义上述4中的包和包体,"pkg_resultset",在Action这样使用:
......
errors.add("foundnumber",new ActionError("foundnumber"));
this.saveErrors(request,errors);
mg=mapping.getInputForward();
CallableStatement cs2=null;
cs2=conn.prepareCall("{?=call pkg_resultset.operation_resultset(?)}");
cs2.registerOutParameter(1,OracleTypes.CURSOR);
cs2.setString(2,phoneNum);
cs2.execute();
rs = ((OracleCallableStatement)cs2).getCursor(1);
ArrayList list = new ArrayList();
while(rs.next())
{
String operationName = rs.getString(1);
String operationType = rs.getString(2);
String operationCharge = rs.getString(3);
System.out.println(operationName);
System.out.println(operationType);
System.out.println(operationCharge);
GetResultList resultList=new GetResultList //见下面的bean类
(operationName,operationType,operationCharge);
list.add(resultList);
}
request.setAttribute("GetResultLists",list);
//下面是一个bean类
package com.penguin.bean;
public class GetResultList
{
private String operationName;
private String operationType;
private String operationCharge;
public GetResultList(String operationName,String operationType,String operationCharge)
{
this.operationCharge=operationCharge;
this.operationName=operationName;
this.operationType=operationType;
}
public String getOperationCharge() {
return operationCharge;
}
public void setOperationCharge(String operationCharge) {
this.operationCharge = operationCharge;
}
public String getOperationName() {
return operationName;
}
public void setOperationName(String operationName) {
this.operationName = operationName;
}
public String getOperationType() {
return operationType;
}
public void setOperationType(String operationType) {
this.operationType = operationType;
}
}
然后在jsp页面中用标签:
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-bean
" prefix="bean" %>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-html
" prefix="html" %>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-logic
" prefix="logic" %>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-tiles
" prefix="tiles" %>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-template
" prefix="template" %>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-nested
" prefix="nested" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html:html locale="true">
<head>
<html:base />
<title>query.jsp</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
</head>
<body>
<html:form action="querybuniess.do" method="post" focus="login">
<table >
<tr>
<td>phone number:</td>
<td><html:text property="phonenumber" /></td>
<td><html:submit value="搜索" /></td>
<td><font color="red"><html:errors property="phonenumbererr"/></font></td>
<td><font color="red"><html:errors property="notfoundnumber"/></font></td>
<td><FONT color="red"><html:errors property="foundnumber"/></FONT></td>
</tr>
</table>
<br>
<br>
<logic:present name="GetResultLists" scope="request">
<table cellspacing="1" border="1" width="500" >
<tr>
<th align="center" >栏目名</th>
<th align="center" >资费类型</th>
<th align="center" >金额</th>
</tr>
<logic:iterate id="GetResultList" name="GetResultLists">
<logic:present name="GetResultList">
<tr>
<td align="center" ><bean:write name="GetResultList" property="operationName"/></td>
<td align="center" ><bean:write name="GetResultList" property="operationType"/></td>
<td align="center" ><bean:write name="GetResultList" property="operationCharge"/></td>
</tr>
</logic:present>
</logic:iterate>
</logic:present>
</table>
</html:form>
</body>
</html:html>
我的异常网推荐解决方案：oracle存储过程,http://www..net/oracle-develop/177537.html