ADO ���g�p���� Oracle �X�g�A�h �v���V�[�W�����烌�R�[�h�Z�b�g���擾������@

�T���v�� �v���W�F�N�g�̍쐬�Ǝ��s

1. Oracle �T�[�o�[�Ŏ��� DDL �X�N���v�g�����s���܂��B

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

2. Oracle �T�[�o�[��Ŏ��̃p�b�P�[�W���쐬���܂��B

   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. Oracle �T�[�o�[��Ŏ��̃p�b�P�[�W�{�̂��쐬���܂��B

   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. Visual Basic 5.0 Enterprise Edition �܂��� Visual Basic 6.0 Enterprise Edition �ŐV�����v���W�F�N�g���J���܂��B�f�t�H���g�ŁAForm1 ���쐬����܂��B
5. �t�H�[����Ɏ��̃R���g���[����z�u���܂��B

   �R���g���[��     �I�u�W�F�N�g��        Caption �t�B�[���h
   -------------------------------------------------
   �{�^��           cmdGetEveryone    Get Everyone
   �{�^��           cmdGetOne         Get One
   					

6. [�c�[��] ���j���[�� [�I�v�V����] ���N���b�N���܂��B[���W���[���S�̂�A���\��] �`�F�b�N �{�b�N�X���I���ɂ��A[OK] ���N���b�N���܂��B���̐ݒ�ɂ��A�v���W�F�N�g�̃R�[�h�S�̂��Q�Ƃł���悤�ɂȂ�܂��B
7. ���̃R�[�h���R�[�h �E�B���h�E�ɓ\��t���܂��B

   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

8. [�v���W�F�N�g] ���j���[�� [�Q�Ɛݒ�] ���N���b�N���܂��B[Microsoft Active Data Objects 2.x Library] �`�F�b�N �{�b�N�X���I���ɂ��܂��B
9. �v���W�F�N�g�����s���܂��B[Get Everyone] ���N���b�N����ƁA���̃N�G�������s����܂��B

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

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

�擪�֖߂� | �t�B�[�h�o�b�N