ตอนที่ 20 : Visual Basic (VB.Net) เรียกใช้ Oracle Stored Procedure

CREATE OR REPLACE PROCEDURE GET_CUSTOMER
  (pCountryCode IN VARCHAR2, pRowFound OUT NUMBER, pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
  
  -- OUT pCustomer
  OPEN pCustomer FOR
       SELECT *
         FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;
         
  -- OUT pRowFound       
  SELECT COUNT(*) INTO pRowFound
         FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;
END;

VAR  pRowFound NUMBER;
VAR  pCustomer REFCURSOR;
EXEC GET_CUSTOMER('US',:pRowFound,:pCustomer)
PRINT pRowFound;
PRINT pCustomer;

Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Module MyModule

    Sub Main()

        Dim objConn As New OracleConnection()
        Dim objCmd As New OracleCommand()
        Dim dtAdapter As New OracleDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As [String], strStored As [String]

        strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
        objConn.ConnectionString = strConnString
        objConn.Open()

        Dim sCountryCode As String = "US"

        strStored = "GET_CUSTOMER"
        objCmd.Parameters.Add(New OracleParameter("pCountryCode", OracleDbType.Varchar2)).Value = sCountryCode ' IN

        objCmd.Parameters.Add(New OracleParameter("pRowFound", OracleDbType.Int32)).Direction = ParameterDirection.Output ' OUT
        objCmd.Parameters.Add(New OracleParameter("pCustomer", OracleDbType.RefCursor)).Direction = ParameterDirection.Output ' OUT

        objCmd.Connection = objConn
        objCmd.CommandText = strStored
        objCmd.CommandType = CommandType.StoredProcedure

        ' objCmd.Parameters("pCustomer").Value (REF CURSOR)
        dtAdapter.SelectCommand = objCmd
        dtAdapter.Fill(ds)

        ' Get Result 
        ' objCmd.Parameters("pRowFound").Value
        Console.WriteLine(String.Format("pRowFound = {0}", objCmd.Parameters("pRowFound").Value))
        Console.WriteLine("")

        ' Loop Data Table
        dt = ds.Tables(0)
        ' *** Cursor at 0 , 1 , 2, ...
        Console.WriteLine("pCustomer")
        For i As Integer = 0 To dt.Rows.Count - 1
            Console.Write(dt.Rows(i)("CUSTOMER_ID"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("NAME"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("EMAIL"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("COUNTRY_CODE"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("BUDGET"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("USED"))
            Console.WriteLine("")
        Next

        objConn.Close()
        objConn = Nothing

    End Sub

End Module

CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER
(
  pCustomerID IN VARCHAR2,
  pName IN VARCHAR2,
  pEmail IN VARCHAR2,
  pCountryCode IN VARCHAR2,
  pBudget IN DECIMAL,
  pUsed IN DECIMAL,
  pResult OUT NUMBER,
  pMessage OUT VARCHAR2
)
AS
BEGIN

  -- Insert Statement 1
  INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
    VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
 
  -- Return row effected  
  pResult := SQL%ROWCOUNT;
  pMessage := 'Insert Data Successfully';
  
  -- Return error exception
   EXCEPTION 
    WHEN OTHERS THEN
      BEGIN
        pResult := 0;
        pMessage := 'Error - ' || SQLERRM; 
      END;  
    
END;

VAR pResult NUMBER;
VAR pMessage VARCHAR2;
EXEC INSERT_CUSTOMER('C005','Fun Wipa','[email protected]','TH','100000','0',:pResult,:pMessage);
PRINT pResult;
PRINT pMessage;

Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Module MyModule

    Sub Main()

        Dim objConn As New OracleConnection()
        Dim objCmd As New OracleCommand()

        Dim strConnString As String, strStored As String

        strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
        objConn.ConnectionString = strConnString
        objConn.Open()

        Dim strCustomerID As String = "C005"
        Dim strName As String = "Fun Wipa"
        Dim strEmail As String = "[email protected]"
        Dim strCountryCode As String = "TH"
        Dim dBudget As Decimal = 100000
        Dim dUsed As Decimal = 0

        strStored = "INSERT_CUSTOMER"
        objCmd.Parameters.Add(New OracleParameter("pCustomerID", OracleDbType.Varchar2, 4)).Value = strCustomerID ' IN
        objCmd.Parameters.Add(New OracleParameter("pName", OracleDbType.Varchar2, 150)).Value = strName ' IN
        objCmd.Parameters.Add(New OracleParameter("pEmail", OracleDbType.Varchar2, 150)).Value = strEmail ' IN
        objCmd.Parameters.Add(New OracleParameter("pCountryCode", OracleDbType.Varchar2, 2)).Value = strCountryCode ' IN

        objCmd.Parameters.Add(New OracleParameter("pBudget", OracleDbType.Varchar2)).Value = dBudget ' IN
        objCmd.Parameters.Add(New OracleParameter("pUsed", OracleDbType.Varchar2)).Value = dUsed ' IN

        objCmd.Parameters.Add(New OracleParameter("pResult", OracleDbType.Int32)).Direction = ParameterDirection.Output ' OUT
        objCmd.Parameters.Add(New OracleParameter("pMessage", OracleDbType.Varchar2, 500)).Direction = ParameterDirection.Output ' OUT

        objCmd.Connection = objConn
        objCmd.CommandText = strStored
        objCmd.CommandType = CommandType.StoredProcedure

        objCmd.ExecuteNonQuery()

        ' Get Result 
        ' objCmd.Parameters("pResult").Value
        Console.WriteLine(String.Format("pResult = {0}", objCmd.Parameters("pResult").Value))

        ' objCmd.Parameters("pMessage").Value
        Console.WriteLine(String.Format("pMessage = {0}", objCmd.Parameters("pMessage").Value))

        objConn.Close()
        objConn = Nothing

    End Sub

End Module