oracle 存储过程 例子 简单demo

调用存储过程

/**
 * ProcedureTest.java
 * com.exec
 *
 * Function： TODO 
 *
 *   ver     date      		author
 * ──────────────────────────────────
 *   		 Jun 17, 2010 		程仁银
 *
 * Copyright (c) 2010,  All Rights Reserved.
*/

package com.exec;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.db.DBconnect;

/**
 * ClassName:ProcedureTest
 * Project: 
 * Company: LINKAGE
 *
 * @author   程仁银
 * @version  
 * @since    Ver 1.1
 * @Date	 Jun 17, 2010		1:31:17 PM
 *
 * @see 	 
 */
public class ProcedureTest {

	/**@author . 程仁银
	 * @email  . [e-mail]
	 * @createTime .Jun 17, 2010 1:31:23 PM
	 * @parameters . 
	 * @description .
	 */

	public static void main(String[] args) 
	{
		Connection con = null;
		con = new DBconnect().getConectionByJdbc();
		try {
			CallableStatement cs = con.prepareCall("{call cryProcedure(?)}");
			cs.setLong(1,10);
			cs.execute();
			con.commit();
			System.out.println("执行完成 ... ");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

sql - CRY_SEQUENCES.NEXTVAL 为sequence，你也可以测试写个固定的值

CREATE OR REPLACE procedure cryProcedure(p_id IN NUMBER)
IS
v_name VARCHAR(20);
BEGIN
SELECT col1 INTO v_name FROM cry WHERE ID=p_id;
INSERT INTO cry(ID,col1)values(CRY_SEQUENCES.NEXTVAL,v_name);
END cryProcedure;

----------------------------------------------------------
另外一个例子

 private void call(long xh, String flag) {
        DBConn db = new DBConn();
        Connection conn = db.getConn();
        CallableStatement cs = null;
        try {
            cs = conn.prepareCall("{CALL PTR_MO_BARCODE_SEND(?,?)}");
            cs.setLong(1, xh);
            cs.setString(2, flag);
            cs.execute();
            conn.commit();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            if (cs != null) {
                try {
                    cs.close();
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            db.freeConn();
        }
    }


	CREATE OR REPLACE PROCEDURE PTR_MO_BARCODE_SEND(
                                   V_xh in number,
                                   V_FLAG  IN VARCHAR2 --查到1，没有值查不到-1
                                   ) IS
v_dn varchar2(13);
v_order_id number(16);

--扣减积分过程,并下发条码
BEGIN
  select CONTENT into v_order_id from Tm_BARCODE_SEND
  where xh=v_xh;
 insert into TT_BARCODE_SEND
  (XH,
  SP_ID,
  SP_ORDER_NUMBER,
  BUSINESS_CODE,
  LOCATION_ID,
  PHONE_REQ,
  PHONE_RECE,
  PHONE_TYPE,
  VALID_TIME,
  OUT_SMIL_ID,
  MEMO,
  SEND_TYPE,
  CONTENT,
  INFO_TITLE,
  NOTES,
  MMS_NOTES,
  ADDITION_NUMBER,
  PRO_ID,
  FROMID,
  OP_DATE,
  DO_FLAG,
  DO_DATE,
  DO_REMARK
  )
  select
    XH,
    SP_ID,
    SP_ORDER_NUMBER,
    BUSINESS_CODE,
    LOCATION_ID,
    PHONE_REQ,
    PHONE_RECE,
    PHONE_TYPE,
    VALID_TIME,
    OUT_SMIL_ID,
    MEMO,
    SEND_TYPE,
    CONTENT,
    INFO_TITLE,
    NOTES,
    MMS_NOTES,
    ADDITION_NUMBER,
    PRO_ID,
    FROMID,
    OP_DATE,
    V_FLAG,
    sysdate,
    ''
  from
    TM_BARCODE_SEND
    where xh=v_xh;

IF V_FLAG<>1 THEN
  --下行短信提醒
  select PHONE_REQ into v_dn from TM_BARCODE_SEND where xh=v_xh;
    p_send_sms(
      'HELP',
      v_dn,
      '',
      '尊敬的客户，对不起，条码发送失败，请您联系10086客服人员进行条码重发！'
    );
   update td_order_user set is_send=1,send_state=1 where order_id=v_order_id;
END IF;
update td_order_user set is_send=1,send_state=0 where order_id=v_order_id;
--删除表
DELETE FROM TM_BARCODE_SEND WHERE XH=V_XH;
commit;

exception
 when others then
   dbms_output.put_line(sqlerrm);
   rollback;

END PTR_MO_BARCODE_SEND;