RESPUESTA A LA PREGUNTA 6645 - VISUAL BASIC


Ejemplo:

DROP TABLE person;
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;
 /  
Crear el siguiente package en Oracle
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;
/  
Crear el siguiente package body en Oracle
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;
/  
Abrir un nuevo proyecto en Visual Basic 5.0 o 6.0 Enterprise edition.
Control     Name             Text/Caption
-----------------------------------------
Button      cmdGetEveryone   Get Everyone
   Button      cmdGetOne        Get One 

Código:
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()
  '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 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 


Adolfo Quinteros
aquinteros@act.cl

http://www.lawebdelprogramador.com