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>