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})}"
(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})}"
解决方案 »
- 问下oracle的备份与还原在官方文档的地址是哪个 谢谢
- 左连接表连接重复值
- 求助oracle的触发器!!
- 麻烦给提供个oracle 下载地址 急用
- 安装oracle9i时出现 登控台错误---请输入有效的“源”和“目标”
- 郁闷,从SQL SERVER存储过程转到ORACLE来,没写一个都有问题,帮忙看看
- 关于监听器的问题?
- 请问VB中如何存取ORACLE数据库的CLOB类型的字段?
- ora-01033 ORACLE initialization or shutdown in process
- oracle的密码问题
- 菜鸟的问题
- 有关jsp和Oracle连接的一个问题,各位大虾拿分得好机会!!!
create or replace package tinger01 as
type myrctype is ref cursor;
procedure selectbudgettitle(p_depart_id in d_budget.depart_id%type,
myrc out myrctype);
end tinger01;我在ASP中调用时出现问题,下面是我的代码:
conn.open STRDSN
cmd.ActiveConnection = conn
'cmd.commandtype = 1
cmd.commandtext = "{call tinger01.selectbudgettitle(?, {resultset 100,a})}"
cmd.parameters.append cmd.createparameter("p_depart_id",adInteger,adParamInput,10,1)
'cmd.parameters.append cmd.createparameter("myrc",,4)
rec = cmd.execute在最后一句出错。
Set rec.Source = cmd
rec.Open
http://expert.csdn.net/Expert/topic/1182/1182914.xml?temp=.179516