WEB开发网
开发学院数据库Oracle Oracle大文本在ASP中存取问题的解决 阅读

Oracle大文本在ASP中存取问题的解决

 2007-05-12 12:22:35 来源:WEB开发网   
核心提示: 3. 在Oracle服务器上创建以下包体(package body):CREATE OR REPLACE PACKAGE BODY packpersonASPROCEDURE allperson(ssn OUT tssn,fname OUT tfname,lname OUT tlname)

3. 在Oracle服务器上创建以下包体(package body):

  CREATE OR REPLACE PACKAGE BODY packperson
  AS
  PROCEDURE 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;
  /

4. 在 VB 6.0 中打开一个新的工程,缺省创建表单 Form1。

5. 在表单上添加二个按钮,cmdGetEveryone和cmdGetOne。

6. 在代码窗口中添加以下代码:  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 Long
  
  Private 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.Close
  End Sub
  
  Private 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.Close
  End Sub
  
  Private Sub Form_Load()
  '使用合适的值代替以下用户ID,
  口令(PWD)和服务器名称(SERVER)
  Conn = "UID=*****;PWD=*****;driver=" _
  & "{Microsoft ODBC for
  Oracle};SERVER=dseOracle;"
  Set Cn = New ADODB.Connection
  '创建Connection对象
  With Cn
  .ConnectionString = Conn
  .CursorLocation = adUseClient
  .Open
  End With
  QSQL = "{call packperson.allperson(
  {resultset 9,ssn,fname,"_
  & "lname})}"
  Set CPw1 = New ADODB.Command
  '创建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 With
  End Sub
  
  Private Sub Form_Unload(Cancel As Integer)
  Cn.Close
  Set Cn = Nothing
  Set CPw1 = Nothing
  Set CPw2 = Nothing
  Set Rs = Nothing
  End Sub

7. 运行程序。当点下cmdGetEveryone按钮时,程序调用Oracle数据库中不带参数的存储过程packperson.allperson,点下cmdGetOne按钮时调用packperson.oneperson存储过程。

上一页  1 2 

Tags:Oracle 文本 ASP

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接