Java jdbc executes oracle stored procedures
Java code
Public Connection conn () {
Try {
/ / step 1: load the JDBC driver
Class.forName ("oracle.jdbc.driver.OracleDriver")
/ / step 2: create a database connection
Con = DriverManager.getConnection ("jdbc:oracle:thin:@192.177.2.2:1521/db", "test", "test")
Return con
} catch (Exception e) {
E.printStackTrace ()
Return null
}
}
Public List getCanorderTemplateBySms (String version, String orderFrom,String acco, String accoType, String tunnelType, String callFrom) {
CallableStatement callStat=null
ResultSet rs=null
List list=new ArrayList ()
String sql = "{call pkg_customerorder.sp_sms_canorder2 (?)}"
Try {
CallStat = this.conn () .prepareCall (sql)
CallStat.setString (1)
/ / out
CallStat.registerOutParameter (2century oracle.jdbc.OracleTypes.VARCHAR)
CallStat.registerOutParameter (3century oracle.jdbc.OracleTypes.VARCHAR)
CallStat.registerOutParameter (4century oracle.jdbc.OracleTypes.CURSOR)
/ / in
CallStat.setString (5, orderFrom); / / customizable channel 1: customer service 2: website 3: website and customer service
CallStat.setString (6, acco); / / account
CallStat.setString (7, accoType); / 1 represents the client number 2 and represents the fund account
CallStat.setString (8 driving tunnelType); / / Channel type 0: regardless of channel 1: ordinary channel 2: dedicated account channel
CallStat.setString (9, callFrom); / / A fixed parameter 9 is required to call the Hang Seng API.
CallStat.execute ()
String result= callStat.getString (2); / / get the return parameter return code '0000' to indicate success
If (resulting null & & "0000" .equals (result.trim () {
Rs= (ResultSet) callStat.getObject (4)
While (rs.next ()) {
TaskTemp taskTemp = new TaskTemp ()
If ("1" .equals (rs.getString (3). Trim ()) {/ / whether fund customers can be customized: 0-non-customizable 1-customizable
TaskTemp.setTaskcode (rs.getString (1))
TaskTemp.setTaskname (rs.getString (2))
TaskTemp.setBusinsubtype (rs.getString (15)); / / the classification number needs to be checked tomorrow
TaskTemp.setBusinsubname (rs.getString (16)); / / Classification names need to be checked tomorrow
List.add (taskTemp)
}
}
}
} catch (SQLException e) {
E.printStackTrace ()
}
Return list
}
Stored procedure
/ *
* the following APIs are peripheral encapsulation interfaces (all input parameters are strings), which are mainly used by competitors, but are not recommended internally.
* function: query customers' customizable SMS templates
* input parameter: sInVersion version information
* customizable channels for sOrderFrom
* sAcco account
* sAccotype account type 1-customer ID 2-Fund account number 3-Trading account (not in use for now)
* nTunnelType tunnel type 0: regardless of channel; default 1: ordinary channel; 2: private account channel
* callFrom call source (Hang Seng system call API does not need to pass parameters, and non-Hang Seng system calls such as Jiarui need to pass a value of 9)
* output parameter: sOutResultCode return code return code '0000' indicates success
* sOutResultInfo returns "success" if the message is successful, otherwise it returns the specific reason for failure.
, /
Procedure sp_sms_canorder2 (
SInVersion in varchar2,-- version information
SOutResultCode out varchar2
SOutResultInfo out varchar2
CurOutResult out refcusor
SOrderFrom in varchar2,-customizable channel
SAcco in varchar2
SAccotype in varchar2
NTunnelType in pls_integer: = 0m / m / h / m / m
-- 1: ordinary channel
-- 2: special account access
CallFrom in varchar2 default'5'
);
