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.myexception.cn/oracle-develop/177537.html