自己写的一个存储过程例子

数据库表名:WDW002

-- Create table
create table WDW002
(
  WD01 NUMBER not null,
  WD02 VARCHAR2(10) not null,
  WD03 VARCHAR2(10),
  WD04 VARCHAR2(10)
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table WDW002
  add constraint PK_WDW002 primary key (WD01)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table WDW002
  add unique (WD03)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

源码如下:

/*
 * <p>Title:       测试存储过程</p>
 * <p>Description: 测试存储过程</p>
 * <p>Copyright:   Copyright (c) 2004</p>
 * <p>Company:     ***</p>
 */
package com.soft.procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import com.neusoft.basedao.DataContainer;

/**
 * 类ProceDure.java的实现描述：测试存储过程
 *
 * @author         wangdw wdw004@163.com
 * @version        1.0
 * Date             2004-12-31
 * @see            java.lang.Class
 * History:
 *    <author>     <time>      <version>      <desc>
 */public class ProceDure
{
    private Connection con;

    private Statement sql;

    private ResultSet rs;

    public ProceDure()
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 默认连接
     * @return
     */
    public Connection defaultConnection()
    {
        try
        {
            con = DriverManager.getConnection(
                    "jdbc:oracle:thin:@202.118.6.254:1521:orcl",
                    "wangdw", "wangdw");
            sql = con.createStatement();
            return con;

        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 测试存储过程
     *
     */
    public void proceDureExample()
    {
        defaultConnection();

        //创建存储过程
        String createProcedure = "create or replace procedure test_WDW is begin INSERT INTO WDW002(WD01,WD02,WD03,WD04) VALUES(5,5,5,5);end test_WDW;";
        try
        {
            sql.executeUpdate("DELETE WDW002 WHERE WD01=5");
            sql.executeUpdate(createProcedure);

        } catch (SQLException e1)
        {
            e1.printStackTrace();
        }

        String procedure = "{call test_WDW }";

        try
        {
            //执行存储过程
            CallableStatement cstmt = con.prepareCall(procedure);

            cstmt.executeUpdate();

                    } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 利用存储过程插入数据
     * @param wd01
     * @param wd02
     * @param wd03
     * @param wd04
     * @return
     */
    public void insertWDW002(int wd01, String wd02, String wd03,
            String wd04)
    {
        defaultConnection();
        //产生存储过程
        String createProcedure = "create or replace procedure INSERT_WDW002(wd01 int,wd02 String,wd03 String,wd04 String) is begin INSERT INTO WDW002(WD01,WD02,WD03,WD04) VALUES(wd01,wd02,wd03,wd04);end INSERT_WDW002;";

        try
        {
            sql.executeUpdate("DELETE WDW002 where WD01=" + wd01);
            sql.executeUpdate(createProcedure);

        } catch (SQLException e1)
        {
            e1.printStackTrace();
        }

        try
        {
            //执行存储过程
            CallableStatement cstmt = con
                    .prepareCall("call INSERT_WDW002(?,?,?,?)");
            cstmt.setInt(1, wd01);
            cstmt.setString(2, wd02);
            cstmt.setString(3, wd03);
            cstmt.setString(4, wd04);
            cstmt.executeUpdate();

           } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    /**
     * 利用存储过程删除数据
     * @param wd01
     * @return
     */
    public int deleteWDW002(int wd01)
    {
        defaultConnection();
        //产生存储过程
        String createProcedure = "create or replace procedure DELETE_WDW002(intValue int) is begin delete wdw002 where WD01=intValue;end DELETE_WDW002;";

        try
        {
            sql.executeUpdate(createProcedure);

        } catch (SQLException e1)
        {
            e1.printStackTrace();
        }

        try
        {
            //执行存储过程
            CallableStatement cstmt = con.prepareCall("call DELETE_WDW002(?)");
            cstmt.setInt(1, wd01);

            int intValue = cstmt.executeUpdate();

            return intValue;

        } catch (SQLException e)
        {
            e.printStackTrace();
        }

        return -1;
    }

      /**
     * 测试输出数据
     * @param args
     */
    public static void main(String[] args)
    {
        ProceDure proceDure = new ProceDure();
        proceDure.proceDureExample();
        //插入数据
        proceDure.insertWDW002(6, "6", "6", "6");
        //删除数据
        int intReturn = proceDure.deleteWDW002(6);
    }
}