我要写一个pl/sql程序。程序中进行很多运算统计,最后将小计,总计信息返回。
比如最后的计算结果可能是:
northsnow 1000.20
liujincai 1300.18
bill gates 3700.82
ford 5000.80
总计 11001.1计算的过程中可能会生成如上的数据。如何将这些数据返回。不都说通过ref cursor可以返回记录集吗。那这种动态生成的记录集如何返回呢?高手帮忙解决。
还有大家还有什么其他的方法也可以指点一下。
比如最后的计算结果可能是:
northsnow 1000.20
liujincai 1300.18
bill gates 3700.82
ford 5000.80
总计 11001.1计算的过程中可能会生成如上的数据。如何将这些数据返回。不都说通过ref cursor可以返回记录集吗。那这种动态生成的记录集如何返回呢?高手帮忙解决。
还有大家还有什么其他的方法也可以指点一下。
cursor可以返回动态的数据集
如:
declare
str varchar2(1000);
begin
str:='select empno from emp';
open yourcursor for str;
end;如果是带输入参数的
open yourcursor for str using 。
即:
create or replace type mytype as object(name varchar(100),n number);
create or replace type myTableType as table of mytype;
然后定义一个myTableType的变量就可以了
http://community.csdn.net/Expert/topic/4166/4166721.xml?temp=.1421015
http://www.techonthenet.com/oracle/questions/cursor1.php
http://www.enterprisedt.com/publications/oracle/result_set.html
CREATE OR REPLACE PACKAGE ProcTest_Pkg
AS
TYPE tblNumber IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE tblName IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
PROCEDURE GenerateDynamicRecordset
(
o_number OUT tblNumber,
o_name OUT tblName);
END ProcTest_Pkg;
CREATE OR REPLACE PACKAGE BODY ProcTest_Pkg
AS
PROCEDURE GenerateDynamicRecordset
(
o_number OUT tblNumber,
o_name OUT tblName)
IS RecordCount NUMBER DEFAULT 0;
BEGIN
RecordCount:= RecordCount + 1;
o_number(RecordCount):= 1000;
o_name(RecordCount):= 'northsnow';
RecordCount:= RecordCount + 1;
o_number(RecordCount):= 1300;
o_name(RecordCount):= 'liujincai';
END GenerateDynamicRecordset;
END ProcTest_Pkg;
Dim objCommand
Dim objRecordset
Set objConnection = CreateObject("ADODB.Connection")
With objConnection
.ConnectionString = "Provider=MSDAORA.1; Data Source=orcl; User ID=system; Password=oracle"
'''''''''The following connection string does not work''''''''''''
'''.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=orcl;User Id=system;Password=oracle;"
.CursorLocation = 3 'adUseClient
.Open
End With
Set objCommand = CreateObject("ADODB.Command")
With objCommand
Set .ActiveConnection = objConnection
.CommandText = "{call ProcTest_Pkg.GenerateDynamicRecordset(" & _
" {resultset 100, o_number, o_name})}"
.CommandType = 1 'adCmdText
' .Parameters(0).Value = "1 "
Set objRecordset = .Execute()
End With
Do While Not objRecordset.EOF
msgbox objRecordset.Fields("o_number") & objRecordset.Fields("o_name")
objRecordset.MoveNext
Loop
Set objRecordset = Nothing
Set objCommand = Nothing
Set objConnection = Nothing