这是建立环境的代码:
prompt PL/SQL Developer import file
prompt Created on 2002年12月19日 by administrator
set feedback off
set define off
prompt Creating STU...
-- Create table
create table STU
(
XH VARCHAR2(4) not null,
XM VARCHAR2(4),
AGE NUMBER(3),
SEX NUMBER(1),
BIRTHDAY DATE,
AXM VARCHAR2(10)
)
tablespace TOOLS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 32K
next 32K
minextents 1
maxextents 4096
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table STU
add constraint XHPK primary key (XH)
using index
tablespace TOOLS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 32K
next 32K
minextents 1
maxextents 4096
pctincrease 0
);prompt Disabling triggers for STU...
alter table STU disable all triggers;
prompt Loading STU...
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1002', 'chen', 19, 0, to_date('05-12-2002 13:58:56', 'dd-mm-yyyy hh24:mi:ss'), null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1001', 'mike', 18, 1, to_date('15-08-2002', 'dd-mm-yyyy'), null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1003', 'li', 17, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1004', 'li', 17, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1005', 'li', 17, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1006', 'zha', 18, 4, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1007', 'zha', 18, 4, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1009', 'zha', 10, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1010', 'liu', 160, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1011', null, 20, 0, null, null);
commit;
prompt 10 records loaded
prompt Enabling triggers for STU...
alter table STU enable all triggers;
set feedback on
set define on
prompt Done.这是建立包的代码:
create or replace package vbtest is
type cur is ref cursor;
procedure getstus(xm varchar,mycur out cur);
end vbtest;
/
create or replace package body vbtest is
procedure getstus(xm varchar,mycur out cur)
is
begin
open mycur for 'select xh,xm from stu where xm like '||'''%'||xm||'%''';
end;
end vbtest;
/这是测度的代码:
----test this package
declare
mycur vbtest.cur;
xm varchar(10):='';
myxh varchar(4);
myxm varchar(10);
begin
xm:='&vara';
vbtest.getstus(xm,mycur);
loop
fetch mycur into myxh,myxm;
exit when mycur%notfound;
dbms_output.put_line(myxh||' '||myxm);
end loop;
end;
/这是VB调用时的代码:
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Conn = "Provider=MSDAORA.1;Password=manager;User ID=system;Data Source=oracle;Persist Security Info=True"
Set cn = New ADODB.Connection
With cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
plsql = "{call vbtest.getstus(?,{resultset 10,mycur})}"
Dim cpwx As New ADODB.Command
Dim Rs As New ADODB.Recordset
With cpwx
.Parameters.Append .CreateParameter("xm", adVarChar, adParamInput, 9, "c")
Set .ActiveConnection = cn
.CommandText = plsql
.CommandType = adCmdText
End With
Set Rs.Source = cpwx
'------------------------------------
Rs.Open ---这里报错!说不能对数组进行赋值或执行!!!!!!!!!!
'-------------------------------------
Set DataGrid1.DataSource = Rs
DataGrid1.Refresh
End Sub为什么?
我该怎么做!我其实只想让ORACLE返回一个游标,然后,用VB调用,得到这个游标,最后绑室到表格!为什么!!!!!
prompt PL/SQL Developer import file
prompt Created on 2002年12月19日 by administrator
set feedback off
set define off
prompt Creating STU...
-- Create table
create table STU
(
XH VARCHAR2(4) not null,
XM VARCHAR2(4),
AGE NUMBER(3),
SEX NUMBER(1),
BIRTHDAY DATE,
AXM VARCHAR2(10)
)
tablespace TOOLS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 32K
next 32K
minextents 1
maxextents 4096
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table STU
add constraint XHPK primary key (XH)
using index
tablespace TOOLS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 32K
next 32K
minextents 1
maxextents 4096
pctincrease 0
);prompt Disabling triggers for STU...
alter table STU disable all triggers;
prompt Loading STU...
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1002', 'chen', 19, 0, to_date('05-12-2002 13:58:56', 'dd-mm-yyyy hh24:mi:ss'), null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1001', 'mike', 18, 1, to_date('15-08-2002', 'dd-mm-yyyy'), null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1003', 'li', 17, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1004', 'li', 17, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1005', 'li', 17, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1006', 'zha', 18, 4, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1007', 'zha', 18, 4, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1009', 'zha', 10, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1010', 'liu', 160, 1, null, null);
insert into STU (XH, XM, AGE, SEX, BIRTHDAY, AXM)
values ('1011', null, 20, 0, null, null);
commit;
prompt 10 records loaded
prompt Enabling triggers for STU...
alter table STU enable all triggers;
set feedback on
set define on
prompt Done.这是建立包的代码:
create or replace package vbtest is
type cur is ref cursor;
procedure getstus(xm varchar,mycur out cur);
end vbtest;
/
create or replace package body vbtest is
procedure getstus(xm varchar,mycur out cur)
is
begin
open mycur for 'select xh,xm from stu where xm like '||'''%'||xm||'%''';
end;
end vbtest;
/这是测度的代码:
----test this package
declare
mycur vbtest.cur;
xm varchar(10):='';
myxh varchar(4);
myxm varchar(10);
begin
xm:='&vara';
vbtest.getstus(xm,mycur);
loop
fetch mycur into myxh,myxm;
exit when mycur%notfound;
dbms_output.put_line(myxh||' '||myxm);
end loop;
end;
/这是VB调用时的代码:
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Conn = "Provider=MSDAORA.1;Password=manager;User ID=system;Data Source=oracle;Persist Security Info=True"
Set cn = New ADODB.Connection
With cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
plsql = "{call vbtest.getstus(?,{resultset 10,mycur})}"
Dim cpwx As New ADODB.Command
Dim Rs As New ADODB.Recordset
With cpwx
.Parameters.Append .CreateParameter("xm", adVarChar, adParamInput, 9, "c")
Set .ActiveConnection = cn
.CommandText = plsql
.CommandType = adCmdText
End With
Set Rs.Source = cpwx
'------------------------------------
Rs.Open ---这里报错!说不能对数组进行赋值或执行!!!!!!!!!!
'-------------------------------------
Set DataGrid1.DataSource = Rs
DataGrid1.Refresh
End Sub为什么?
我该怎么做!我其实只想让ORACLE返回一个游标,然后,用VB调用,得到这个游标,最后绑室到表格!为什么!!!!!
这样写返回值行么,感觉有点悬
Parameters.Append .CreateParameter("xm", adVarChar, adParamInput, 9, "c")还有一点要注意:记录集最好还是定义以下几个属性
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
Set rs.Source = cpwx