我做了一个测试连接的问题,老出错
下面是orcal包头
create package huxb00
is
TYPE Sj is TABLE of date INDEX BY BINARY_INTEGER;
TYPE Dh is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE Js is TABLE of number INDEX BY BINARY_INTEGER;
procedure Huxb001 (a1 Varchar2, a2 Varchar2,b1 out Dh, b2 out Sj,c1 out Js);
ZRows number:=0;
end;下面是包体:
create package body huxb00
is
procedure Huxb001 (a1 Varchar2, a2 Varchar2,b1 out Dh, b2 out Sj,c1 out Js)
is
Num Number default 1;
begin
declare cursor cur_1 is
select Sheetid,Receivedate from sfp_pdreceivingsh
where pdfactoryid=a1 and operator=a2
order by Sheetid Desc;
begin
open cur_1;
loop
fetch cur_1 into b1(Num),b2(Num);
c1(Num):=Num;
Num:=Num+1;
exit when cur_1%notfound;
end loop;
close cur_1;
end;
end;
end;
下面是VB程序
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: Rs(0) "
Rs.MoveNext
Wend
Rs.Close
End Sub Private Sub Form_Load()
Conn = "UID=zhongyaerp;PWD=zhongya;driver={Microsoft ODBC for Oracle};SERVER=Huxb0001;"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With 'QSQL = "{call Huxb01.Huxb001({resultset 9,a1})}"
'QSQL = "{call packperson.oneperson({resultset 2,ssn,fname,lname})}"
QSQL = "Huxb0003" '存储过程名
Dim CPw1 As New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdStoredProc
'.CommandType = adCmdText
'Set Rs = .Execute
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set CPw1 = Nothing
Set Rs = Nothing
End Sub
下面是orcal包头
create package huxb00
is
TYPE Sj is TABLE of date INDEX BY BINARY_INTEGER;
TYPE Dh is TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
TYPE Js is TABLE of number INDEX BY BINARY_INTEGER;
procedure Huxb001 (a1 Varchar2, a2 Varchar2,b1 out Dh, b2 out Sj,c1 out Js);
ZRows number:=0;
end;下面是包体:
create package body huxb00
is
procedure Huxb001 (a1 Varchar2, a2 Varchar2,b1 out Dh, b2 out Sj,c1 out Js)
is
Num Number default 1;
begin
declare cursor cur_1 is
select Sheetid,Receivedate from sfp_pdreceivingsh
where pdfactoryid=a1 and operator=a2
order by Sheetid Desc;
begin
open cur_1;
loop
fetch cur_1 into b1(Num),b2(Num);
c1(Num):=Num;
Num:=Num+1;
exit when cur_1%notfound;
end loop;
close cur_1;
end;
end;
end;
下面是VB程序
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: Rs(0) "
Rs.MoveNext
Wend
Rs.Close
End Sub Private Sub Form_Load()
Conn = "UID=zhongyaerp;PWD=zhongya;driver={Microsoft ODBC for Oracle};SERVER=Huxb0001;"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With 'QSQL = "{call Huxb01.Huxb001({resultset 9,a1})}"
'QSQL = "{call packperson.oneperson({resultset 2,ssn,fname,lname})}"
QSQL = "Huxb0003" '存储过程名
Dim CPw1 As New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdStoredProc
'.CommandType = adCmdText
'Set Rs = .Execute
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set CPw1 = Nothing
Set Rs = Nothing
End Sub
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
放到
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdStoredProc
'.CommandType = adCmdText
'Set Rs = .Execute
End With
前面去
并把
'Set Rs = .Execute
的注释去掉
set rs=.execute
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
_CommandPtr pCmd(__uuidof(Command)); pConn->ConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs";
pConn->Open("","","",adConnectUnspecified); //pRst=pConn->Execute("select * from authors",NULL,adCmdText);
//pRst->Open("select * from authors",_variant_t((IDispatch*)pConn),
// adOpenDynamic,adLockOptimistic,adCmdText);
pCmd->put_ActiveConnection(_variant_t((IDispatch*)pConn));
pCmd->CommandText="select * from authors";
pRst=pCmd->Execute(NULL,NULL,adCmdText);
while(!pRst->rsEOF)
{
((CListBox*)GetDlgItem(IDC_LIST1))->AddString(
(_bstr_t)pRst->GetCollect("au_lname"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pCmd.Release();
pRst.Release();
pConn.Release();
CoUninitialize();基本上操作过程是一样的,定义一个连接,一个记录集,一个执行过程,就可以了。VC的,但是在VB中所有的属性都有。
CREATE OR REPLACE PROCEDURE "HUXB0004" (a1 varchar2,a2 varchar2,b1 out number,b2 out number,b3 out number,b4 out number)
--通过输入订单号和产品名称,执行后得到生产该产品需购买原料所需金额,以及实际发生金额。
is
Wcgsl number default 0;
Wcgje number default 0;
Wllsl number default 0;
Wllje number default 0;
JCgsl number default 0;
Jcgje number default 0;
Jllsl number default 0;
Jllje number default 0;
JOne number default 0;
JAll number default 0;
begin--纬的原料采购
--纬的采购
select sum(Quantity),sum(money) into Wcgsl,Wcgje from(
select a.MaterialID as MaterialID,a.Quantity as OneQ,b.Quantity as NeedQ,c.OrderQuantity as OrderQ,d.price as price,(c.OrderQuantity/b.Quantity)*a.Quantity as Quantity,(c.OrderQuantity/b.Quantity)*a.Quantity*d.price as Money
from
(SELECT MaterialID,sum(Quantity) as Quantity
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2
group by MaterialID) a,
(select MaterialID as MaterialID,sum(Quantity) as Quantity
from SPL_BUYMTRNEEDSH where PO_SERIALNUM in (
SELECT d.serialnum
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=a1
AND a.PRODUCTID=a2)
group by MaterialID) b,
(SELECT MaterialID,sum(OrderQuantity) as OrderQuantity
FROM SPL_MTRORDERSHDETAILS
where SERIALNUM in (
select PO_SERIALNUM
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2)
group by MaterialID) c,
(SELECT distinct MaterialID,price
FROM SPL_MTRORDERSHDETAILS
where SERIALNUM in (
select PO_SERIALNUM
FROM OrderandBuy
WHERE ORDERID=a1 AND PRODUCTID=a2)) d
where a.MaterialID=b.MaterialID and b.MaterialID=c.MaterialID and c.MaterialID=d.MaterialID
order by a.MaterialID) ;--纬的领料
select sum(Quantity),sum(Money) into Wllsl,Wllje
from Mtr_AxisCardFShDetails
where ORDERID=a1 AND PRODUCTID=a2;--经的原料采购
--经轴上一个产品的数量
select sum(productquantity) into JOne
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2;--经所对应的所有产品的总数量
select sum(a.ProductQuantity) into JAll
from Scd_WarpPlanShDetails a, (
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) b
where a.WarpPlanID=b.WarpPlanID;--经的采购
select sum(Quantity) ,sum(money) into Jcgsl,Jcgje from(
select a.MaterialID as MaterialID,a.Quantity as OneQ,b.Quantity as NeedQ,c.OrderQuantity as OrderQ,d.price as price,(c.OrderQuantity/b.Quantity)*a.Quantity as Quantity,(c.OrderQuantity/b.Quantity)*a.Quantity*d.price as Money
from
(SELECT MaterialID,sum(Quantity) as Quantity
FROM OrderandBuy
WHERE ORDERID in (
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2)
group by MaterialID) a,
(select MaterialID as MaterialID,sum(Quantity) as Quantity
from SPL_BUYMTRNEEDSH a,
(SELECT d.serialnum
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID in
(select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) ) e
where a.PO_SERIALNUM = e.SERIALNUM
group by MaterialID) b,
(SELECT d.MaterialID,sum(d.OrderQuantity) as OrderQuantity
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d,(
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) e
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=e.WarpPlanID
group by d.MaterialID) c,
(SELECT distinct d.MaterialID as MaterialID,d.price as price
FROM OrderandBuy a,SPL_MTRORDERSHDETAILS d,(
select distinct WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2) e
WHERE a.PO_SERIALNUM = d.SERIALNUM
and a.ORDERID=e.WarpPlanID
) d
where a.MaterialID=b.MaterialID and b.MaterialID=c.MaterialID and c.MaterialID=d.MaterialID
order by a.MaterialID) ;
--经的领料select sum(a.Quantity) ,sum(a.Money) into Jllsl,Jllje
from Mtr_WarpAxisFShDetails a,Mtr_WarpAxisFSh b
where a.SheetID=b.SheetID and b.WarpAxisCardID in
(select WarpPlanID
from Scd_WarpPlanShDetails
where ORDERID=a1
AND PRODUCTID=a2);
b1:=round(Wcgsl+Jcgsl*(JOne/JAll),2);
b2:=round(Wcgje+Jcgje*(JOne/JAll),2);
b3:=round(Wllsl+Jllsl*(JOne/JAll),2);
b4:=round(Wllje+Jllje*(JOne/JAll),2);b1:=nvl(b1,0);
b2:=nvl(b2,0);
b3:=nvl(b3,0);
b4:=nvl(b4,0);end ;