有很多这样的帖子了,给你一个例子 这是msdn中有关返回记录集和vb调用存取过程的办法 DROP TABLE person;CREATE TABLE person (ssn NUMBER(9) PRIMARY KEY, fname VARCHAR2(15), lname VARCHAR2(20));INSERT INTO person VALUES(555662222,'Sam','Goodwin');INSERT INTO person VALUES(555882222,'Kent','Clark');INSERT INTO person VALUES(666223333,'Jane','Doe');COMMIT; / Create the following package on your Oracle server:CREATE OR REPLACE PACKAGE packperson AS TYPE tssn is TABLE of NUMBER(10) INDEX BY BINARY_INTEGER; TYPE tfname is TABLE of VARCHAR2(15) INDEX BY BINARY_INTEGER; TYPE tlname is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER; PROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname); PROCEDURE oneperson (onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT tlname); END packperson; / Create the following package body on your Oracle server:CREATE OR REPLACE PACKAGE BODY packperson ASPROCEDURE allperson (ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS CURSOR person_cur IS SELECT ssn, fname, lname FROM person; percount NUMBER DEFAULT 1;BEGIN FOR singleperson IN person_cur LOOP ssn(percount) := singleperson.ssn; fname(percount) := singleperson.fname; lname(percount) := singleperson.lname; percount := percount + 1; END LOOP; END;PROCEDURE oneperson (onessn IN NUMBER, ssn OUT tssn, fname OUT tfname, lname OUT tlname) IS CURSOR person_cur IS SELECT ssn, fname, lname FROM person WHERE ssn = onessn; percount NUMBER DEFAULT 1;BEGIN FOR singleperson IN person_cur LOOP ssn(percount) := singleperson.ssn; fname(percount) := singleperson.fname; lname(percount) := singleperson.lname; percount := percount + 1; END LOOP; END; END; / Open a new project in Visual Basic 5.0 or 6.0 Enterprise edition. Form1 is created by default. Place the following controls on the form: Control Name Text/Caption ----------------------------------------- Button cmdGetEveryone Get Everyone Button cmdGetOne Get One From the Tools menu, select the Options item. Click the "Default Full Module View" option and then click OK. This will allow you to view all of the code for this project. Paste the following code into your code window:Option Explicit Dim Cn As ADODB.Connection Dim CPw1 As ADODB.Command Dim CPw2 As ADODB.Command Dim Rs As ADODB.Recordset Dim Conn As String Dim QSQL As String Dim inputssn As LongPrivate Sub cmdGetEveryone_Click() Set Rs.Source = CPw1 Rs.Open While Not Rs.EOF MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2) Rs.MoveNext Wend Rs.CloseEnd SubPrivate Sub cmdGetOne_Click() Set Rs.Source = CPw2 inputssn = InputBox("Enter the SSN you wish to retrieve:") CPw2(0) = inputssn Rs.Open MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2) Rs.CloseEnd SubPrivate Sub Form_Load() 'Replace <User ID>, <Password>, and <Server> with the 'appropriate parameters. Conn = "UID=*****;PWD=*****;driver=" _ & "{Microsoft ODBC for Oracle};SERVER=dseOracle;" Set Cn = New ADODB.Connection With Cn .ConnectionString = Conn .CursorLocation = adUseClient .Open End With QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _ & "lname})}" Set CPw1 = New ADODB.Command With CPw1 Set .ActiveConnection = Cn .CommandText = QSQL .CommandType = adCmdText End With QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _ & "lname})}" Set CPw2 = New ADODB.Command With CPw2 Set .ActiveConnection = Cn .CommandText = QSQL .CommandType = adCmdText .Parameters.Append .CreateParameter(, adInteger, adParamInput) End With Set Rs = New ADODB.Recordset With Rs .CursorType = adOpenStatic .LockType = adLockReadOnly End WithEnd SubPrivate Sub Form_Unload(Cancel As Integer) Cn.Close Set Cn = Nothing Set CPw1 = Nothing Set CPw2 = Nothing Set Rs = NothingEnd Sub Go to the Project menu item and select References. Select the "Microsoft Active Data Objects 2.x Library." Run the project. When you click on the "Get Everyone" button, it executes this query:QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_ & "lname})}"
还可以用REF CURSOR 返回,要用到包 --包的定义
TYPE t_CurGetTable IS REF CURSOR ; --包体的过程 /****************************************** t_CurGetTable 返回的记录集 *******************************************/ PROCEDURE sp_Get_Test( io_cursor IN OUT t_CurGetCandListImg) IS …… OPEN io_cursor FOR SELECT ……; EXCEPTION WHEN OTHERS THEN raise; END sp_Get_CandidateImg;
还可以用REF CURSOR 返回,要用到包 --包的定义
TYPE t_CurGetRecordset IS REF CURSOR ; --包体的过程 /****************************************** t_CurGetTable 返回的记录集 *******************************************/ PROCEDURE sp_Get_Test( io_cursor IN OUT t_CurGetRecordset) IS …… OPEN io_cursor FOR SELECT ……; EXCEPTION WHEN OTHERS THEN raise; END sp_Get_Test;
TO:只取一飘,是否有java调用oracle的方法
the following article has examples for SQLPlus, Pro*C, Java/JDBC, ODBC, ADO/ASP, DBI Perl and OCI:Result Sets from Stored Procedures In Oracle http://osi.oracle.com/~tkyte/ResultSets/index.html
这是msdn中有关返回记录集和vb调用存取过程的办法
DROP TABLE person;CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));INSERT INTO person VALUES(555662222,'Sam','Goodwin');INSERT INTO person VALUES(555882222,'Kent','Clark');INSERT INTO person VALUES(666223333,'Jane','Doe');COMMIT;
/
Create the following package on your Oracle server:CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER; PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
Create the following package body on your Oracle server:CREATE OR REPLACE PACKAGE BODY packperson
ASPROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person; percount NUMBER DEFAULT 1;BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn; percount NUMBER DEFAULT 1;BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
/
Open a new project in Visual Basic 5.0 or 6.0 Enterprise edition. Form1 is created by default.
Place the following controls on the form:
Control Name Text/Caption
-----------------------------------------
Button cmdGetEveryone Get Everyone
Button cmdGetOne Get One
From the Tools menu, select the Options item. Click the "Default Full Module View" option and then click OK. This will allow you to view all of the code for this project. Paste the following code into your code window:Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As LongPrivate Sub cmdGetEveryone_Click() Set Rs.Source = CPw1 Rs.Open While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend Rs.CloseEnd SubPrivate Sub cmdGetOne_Click() Set Rs.Source = CPw2 inputssn = InputBox("Enter the SSN you wish to retrieve:") CPw2(0) = inputssn Rs.Open MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2) Rs.CloseEnd SubPrivate Sub Form_Load() 'Replace <User ID>, <Password>, and <Server> with the
'appropriate parameters.
Conn = "UID=*****;PWD=*****;driver=" _
& "{Microsoft ODBC for Oracle};SERVER=dseOracle;" Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _
& "lname})}" Set CPw1 = New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _
& "lname})}" Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End WithEnd SubPrivate Sub Form_Unload(Cancel As Integer) Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set Rs = NothingEnd Sub
Go to the Project menu item and select References. Select the "Microsoft Active Data Objects 2.x Library." Run the project. When you click on the "Get Everyone" button, it executes this query:QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_
& "lname})}"
--包的定义
TYPE t_CurGetTable IS REF CURSOR ; --包体的过程
/******************************************
t_CurGetTable 返回的记录集
*******************************************/
PROCEDURE sp_Get_Test( io_cursor IN OUT t_CurGetCandListImg)
IS
……
OPEN io_cursor FOR
SELECT ……;
EXCEPTION
WHEN OTHERS THEN
raise;
END sp_Get_CandidateImg;
--包的定义
TYPE t_CurGetRecordset IS REF CURSOR ; --包体的过程
/******************************************
t_CurGetTable 返回的记录集
*******************************************/
PROCEDURE sp_Get_Test( io_cursor IN OUT t_CurGetRecordset)
IS
……
OPEN io_cursor FOR
SELECT ……;
EXCEPTION
WHEN OTHERS THEN
raise;
END sp_Get_Test;
http://osi.oracle.com/~tkyte/ResultSets/index.html
源程序是:
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
System.out.println("*******************1");
String strUrl="jdbc:oracle:thin:@172.16.14.94:1521:gadb"; Properties props = new Properties();
props.put("user", "developer");
props.put("password", "developer");
//props.put("weblogic.codeset","gb2312");
System.out.println("*******************2");
//连接数据库
conn = DriverManager.getConnection(strUrl,props);
System.out.println("*******************3");
//callstatement = conn.prepareCall("{call packperson.allperson(?,?,?)}");
callstatement = conn.prepareCall("{call packperson.allnum(?)}");
callstatement.registerOutParameter(1,java.sql.Types.INTEGER);
callstatement.registerOutParameter(2,java.sql.Types.CHAR);
callstatement.registerOutParameter(3,java.sql.Types.CHAR); System.out.println("*******************4");
callstatement.executeQuery();报错信息是最后一行:
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'ALLPERSON' 时参数数量或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored请帮忙
但看你的错误,好象是参数类型传错了。
如果是函数的返回值,你试试用SELECT FUNCNAME() FROM DUAL;,但如果这样做就不能在函数内部对数据库做任何操作了.