import java.sql.*;class Test
{
public static void main(String[] args) throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora","scott","tiger");
int a = 0;
CallableStatement cstmt = conn.prepareCall("{?= call insert_student1(?) }");
cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.setInt(2,1003);
cstmt.execute();
System.out.println(cstmt.getInt(1)); cstmt.close();
conn.close(); }
}
{
public static void main(String[] args) throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora","scott","tiger");
int a = 0;
CallableStatement cstmt = conn.prepareCall("{?= call insert_student1(?) }");
cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.setInt(2,1003);
cstmt.execute();
System.out.println(cstmt.getInt(1)); cstmt.close();
conn.close(); }
}
这个是procedureimport java.sql.*;class Test
{
public static void main(String[] args) throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora","scott","tiger");
int a = 0;
CallableStatement cstmt = conn.prepareCall("{call insert_student(?,?) }");
cstmt.setInt(1,1003);
cstmt.registerOutParameter(2,Types.INTEGER);
cstmt.execute();
System.out.println(cstmt.getInt(2)); cstmt.close();
conn.close(); }
}
传送一个类实例比较耗资源,可以传字符串数组。楼主可以自己在Oracle中定义存储过程参数类型。以下代码处理过程是正确的,因为
系统在跑,但有可能我在复制时缺了什么,或者母错了什么。如:CREATE OR REPLACE TYPE RET_TYPE_VARCHAR AS TABLE OF VARCHAR2(200);这就是一个字符串数组类型,可能接收java字符串
数组,也可以返回字符串数组到java中。存储过程脚本
PROCEDURE mytest
(
p_sb_nsrsbh varchar2, -- 纳税人识别号
p_sb_skssq varchar2, -- 税款所属期
p_sb_by1 varchar2, -- 输入参数备用1
p_sb_by2 varchar2, -- 输入参数备用2
p_sb_cljg out VARCHAR2, -- 处理结果标志
p_sb_fhxx1 out ret_type_varchar -- 处理结果返回值1
)
AS
.....java调用代码import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import asciiconverter45.*;........
CallableStatement callFunction = connection.prepareCall("{ ? = call mytest(?,?,?,?,?) }");// register the out parameter
callFunction.registerOutParameter(1,OracleTypes.INTEGER);
callFunction.setString(2,"1234567890123456");
callFunction.setString(3,"200406");
callFunction.setString(4,"parameter3");
callFunction.setString(5,"parameter4");
callFunction.registerOutParameter(6,OracleTypes.ARRAY, "RET_TYPE_VARCHAR");// execute the function and return the result
callFunction.execute();
int sbResult = callFunction.getInt(1);
System.out.println("处理结果:"+sbResult);
Array outputArray = null;
String[] surnames = null;
outputArray = callFunction.getArray(6);
surnames = (String[]) outputArray.getArray();// output each surname
for (int i = 0; i < surnames.length; i++) {
if (surnames[i] == null)
continue;
convertHtoA c = new convertHtoA();
//System.out.println("结果返回值信息为=" + surnames[i].substring(2, surnames[i].length()));
c.convertStringToAscString(surnames[i].substring(2, surnames[i].length()));
System.out.println("审核信息 " + i + " is " +
new String(c.getAscii().getBytes("ISO-8859-1"), "GB2312"));}
{
public static void main(String[] args) throws Exception
{
String driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=northwind";
Class.forName(driver);
Connection conn=null;
try
{
conn = DriverManager.getConnection(url,"sa","king");
}
catch(SQLException se)
{
System.out.println("connection occur");
}
int a = 0;
//CallableStatement cstmt = conn.prepareCall("{call insert_student(@Beginning_Date,@Ending_Date?) }");
CallableStatement cstmt = conn.prepareCall("{ call shigang(?,?) }");
cstmt.setDate(0,new Date(1978,1,1));
cstmt.setDate(1,new Date(2004,1,1));
//cstmt.registerOutParameter(2,Types.DATE);
cstmt.execute();
System.out.println(cstmt.getDate(0));
System.out.println(cstmt.getDate(1)); cstmt.close();
conn.close(); }
}
存储过程为
CREATE PROCEDURE dbo.shigang (
@Beginning_Date DateTime, @Ending_Date DateTime
)AS
/* SET NOCOUNT ON */
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
RETURN
GO
cstmt.setDate(1,new Date(2004,1,1));
更改一下吧cstmt.setString(1,“20041210”);
cstmt.setString(2,“20041230”);
看看
注意不是单一值,而是一个对象(gundam_king(东方不败))
一个procedure要用到一个表的一些字段,如果有二十个,我不想把二十个参数传进去,而是在java中写一个类,包括这些字段,然后把这个类传到procedure中,prcedure只要
PROCEDURE COMPLETESIGNOFF(v_dtr in tablename%rowtype)就可以,我想还是有难度的,请看清,不要想当然。