Oracle'nýn normal Resultsets Al nasýl yordamlar depolanan...

Üste

Adým adým örnek

1. DDL aþaðýdaki komut dosyasý, Oracle sunucunuzun çalýþtýrýn:
   

         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,'Sally','Burnett');
   
         COMMIT;
         / 
   
   						

2. Aþaðýdaki paketi Oracle sunucunuzun oluþturun:
   

         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;
         / 
   
   						

3. Aþaðýdaki paketi gövdesi, Oracle sunucunuzun oluþturun:
   

         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. Yeni bir proje, Visual Basic Enterprise sürümünde açýn. Varsayýlan olarak, Form1 oluþturulur.
5. Aþaðýdaki denetimler form üzerindeki yerleþtirin:
   

      Control     Name             Text/Caption
      -----------------------------------------
      Button      cmdGetEveryone   Get Everyone
      Button      cmdGetOne        Get One
   
   						

6. Araçlar menüsünden Seçenekler öðesini seçin. "Varsayýlan Tam Modül Görünümü" seçeneðini týklatýn ve sonra Tamam'ý týklatýn. Böylece, bu proje kodunu görüntülemek.
7. Aþaðýdaki kodu kodu penceresine yapýþtýrýn:
   

         Option Explicit
         Dim Cn As rdoConnection
         Dim En As rdoEnvironment
         Dim CPw1 As rdoQuery
         Dim CPw2 As rdoQuery
         Dim Rs As rdoResultset
         Dim Conn As String
         Dim QSQL As String
         Dim tempcnt As Integer
     
         Private Sub cmdGetEveryone_Click()
   
            Set Rs = CPw1.OpenResultset(rdOpenStatic, rdConcurReadOnly)
   
            While Not Rs.EOF
   
               MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
               Rs.MoveNext
   
            Wend
   
            Rs.Close
   
            Set Rs = Nothing
   
         End Sub
   
         Private Sub cmdGetOne_Click()
   
            Dim inputssn As Long
   
            inputssn = InputBox("Enter an SSN number:")
   
            CPw2(0) = inputssn
   
            Set Rs = CPw2.OpenResultset(rdOpenStatic, rdConcurReadOnly)
   
            MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
   
            Rs.Close
   
            Set Rs = Nothing
   
         End Sub
   
         Private Sub Form_Load()
   
            'Change the text in <> to the appropriate logon
            'information.
            Conn = "UID=<your user ID>;PWD=<your password>;" _
                    & "DRIVER={Microsoft ODBC for Oracle};" _
                    & "SERVER=<your database alias>;"
   
            Set En = rdoEnvironments(0)
            En.CursorDriver = rdUseOdbc
            Set Cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn)
   
            QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _
                 & "lname})}"
   
            Set CPw1 = Cn.CreateQuery("", QSQL)
   
            QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _
                 & "lname})}"
   
            Set CPw2 = Cn.CreateQuery("", QSQL)
   
         End Sub
   
         Private Sub Form_Unload(Cancel As Integer)
   
            En.Close
   
         End Sub
   
   						

8. Projeyi çalýþtýrýn.

   QSQL = "{call packperson.allperson({resultset 9, ssn, fname, "_ & "lname})}"
				

   QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, "_ & "lname})}"
				

Üste