???????? ????????? Oracle ?? ??? .NET ???????? ??????? ?? ????? ?? ???? Oracle ?? ?????? Ref_cursors ?? ??????? ???? ????

???? ID: 321715 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ??? ?? ??? ???? ????? ???? ?? ?? ??? Oracle ???????? ????????? ?? ?????? Ref ????? ?? ??????? ???? ?? ??? Oracle ?? ??? .NET ???? ??????? ?? ????? ???? ???? ?? Ref ????? ?????? ??? ?? ?? ?????? ?? ??? ???? ????? ????? ?? ??? Dataset ???????? ?? ????? ??? ?? ????? ???? ?????

??????????

????? ???? outlines ???????? ?????????, ??????????, ??????? ?????? ?? ?????? ??? ?? ???? ???:
  • Microsoft Windows 2000 Professional, Microsoft Windows 2000 ?????, Microsoft Windows 2000 ????? ????? ?? Microsoft Windows XP Professional
  • Microsoft Visual Studio .NET
  • ??????? Oracle 8.1.7 ?? ??? ???
  • .NET Oracle (???? ??????? ???? ?? ?? ??????? ?? ??? ???? ???????Microsoft ??????? ??????
    (http://www.microsoft.com/downloads/details.aspx?FamilyId=4F55D429-17DC-45EA-BFB3-076D1C052524&displaylang=en)
    )
?? ???? ????? ?? ?? ?? ????? ???? ?? ?????? ???:
  • Microsoft Visual Basic .NET
  • ADO.NET fundamentals ?? ????????

Oracle ?????? ?????

?? ????? Oracle Scott/Tiger ?????? ??? ????????? ???????? ?? ????? ???? ??? ???????? ??? ??, Oracle Scott/Tiger ?????? ?? ?? ???? Oracle ??????? ?? ??? ????? ???

?? ?????? ????? ???? ??, ??? ?? ?????? ????? ???? ?? ???????? ?? ??? ????????? ???????? ????:
   CREATE TABLE DEPT
   (DEPTNO NUMBER(2,0) NOT NULL, 
   DNAME VARCHAR2(14) NULL, 
   LOC VARCHAR2(13) NULL,
   PRIMARY KEY (DEPTNO)
   );

   INSERT INTO Dept VALUES(11,'Sales','Texas');
   INSERT INTO Dept VALUES(22,'Accounting','Washington');
   INSERT INTO Dept VALUES(33,'Finance','Maine');

   CREATE TABLE EMP
   (EMPNO NUMBER(4,0) NOT NULL, 
   ENAME VARCHAR2(10) NULL, 
   JOB VARCHAR2(9) NULL, 
   MGR NUMBER(4,0) NULL, 
   HIREDATE DATE,
   SAL NUMBER(7,2) NULL, 
   COMM NUMBER(7,2) NULL, 
   DEPTNO NUMBER(2,0) NULL,
   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),  
   PRIMARY KEY (EMPNO)
   ); 

   INSERT INTO Emp VALUES(123,'Bob','Sales',555,sysdate,35000,12,11);
   INSERT INTO Emp VALUES(321,'Sue','Finance',555,sysdate,42000,12,33);
   INSERT INTO Emp VALUES(234,'Mary','Account',555,sysdate,33000,12,22);

Oracle ????? ?????

????? Oracle ????? Oracle ????? ?? ??? ?????:
 CREATE OR REPLACE PACKAGE MultiRefCursors AS 
        TYPE Emp_cursor IS REF CURSOR ; 
     TYPE Dept_cursor IS REF CURSOR ; 
        Procedure EmpDept (io_cursor1 IN OUT Emp_cursor, io_cursor2 IN OUT Dept_cursor); 
   END MultiRefCursors;
   /
????? Oracle ????? ????? Oracle ????? ?? ??? ?????:
   CREATE OR REPLACE PACKAGE BODY MultiRefCursors AS
   Procedure EmpDept (io_cursor1 IN OUT Emp_cursor, io_cursor2 IN OUT Dept_cursor) 
   IS 
        E_cursor Emp_cursor;
     D_Cursor Dept_Cursor;
   BEGIN 
        
        
             OPEN E_cursor FOR 
             SELECT * from Emp;

         
             OPEN D_cursor FOR 
             SELECT * from Dept;

        io_cursor1 := E_cursor;
     io_cursor2 := D_cursor;
   END EmpDept; 
   END  MultiRefCursors;
   /

Visual Basic .NET ????????? ?????

  1. ??? ?? Windows ????????? ?? Visual Basic ????????? ?????? ???????? ??? ??, Form1 ????????? ??? ????? ???
  2. ????? ????????????????? ??,?????? ??????, ?? ???? ??? System.Data.OracleClient ?? ??? ?? ?????? ??? ?????
  3. ??????? ?? ??? ??? ???? ??? ?? ?? Datagrid ???????? ???????
  4. ??? ????? ?? ???? ??? ??? ?????????? ??? ?? ??????:
    Imports System.Data.OracleClient
  5. ????? ??? ??????Button1_ClickForm1 ?????:
            Dim Ds As New DataSet()
        Dim Oraclecon As New OracleConnection("Data Source=YourOracle;User ID=scott;Password=tiger")
       
        Oraclecon.Open()


        Dim myCMD As New OracleCommand()
        myCMD.Connection = Oraclecon
        myCMD.CommandText = "multiRefCursors.EmpDept"
        myCMD.CommandType = CommandType.StoredProcedure
        myCMD.Parameters.Add(New OracleParameter("io_cursor1", OracleType.Cursor)).Direction = ParameterDirection.Output
        myCMD.Parameters.Add(New OracleParameter("io_cursor2", OracleType.Cursor)).Direction = ParameterDirection.Output

        Dim MyDA As New OracleDataAdapter(myCMD)

        Try
            MyDA.Fill(Ds)
        Catch Myex As Exception
            MessageBox.Show(Myex.Message.ToString)
        End Try


        Ds.Relations.Add("EmpDept", Ds.Tables(0).Columns("Deptno"), Ds.Tables(1).Columns("Deptno"))
        DataGrid1.DataSource = Ds.Tables(0)
        Oraclecon.Close()
  6. Modify the "OracleConnection" string as appropriate for your environment.
  7. Press F5 to compile and run the application.
????? ?? ???? ???? | ??????????? ???

??????

???????? ??????? ?? ??? % 1, ???? ?????? ????? ???? % 2% 2 ???? ?? Microsoft ???????? ??? ?????:
321718
(http://support.microsoft.com/kb/321718/EN-US/ )
How To Call Oracle Stored Procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider
????? ?? ???? ???? | ??????????? ???

???

???? ID: 321715 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft Visual Studio .NET 2002 Professional Edition
  • Microsoft Visual Studio .NET 2003 Professional Edition
??????: 
kbhowtomaster kboracle kbsystemdata kbmt KB321715 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:321715
(http://support.microsoft.com/kb/321715/en-us/ )
????? ?? ???? ???? | ??????????? ???

??????????? ???

 
????? ?? ???? ????????? ?? ???? ????