oracle 存储过程 例子 简单demo

娅索9068

调用存储过程

/**
* 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, 20101:31:17 PM
*
* @see  
*/
public class ProcedureTest {
/**@author . 程仁银
* @email  . 13813375172@139.com
* @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;