ตอนที่ 22 : Visual C# (C# .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;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            OracleConnection objConn = new OracleConnection();
            OracleCommand objCmd = new OracleCommand();
            OracleDataAdapter dtAdapter = new OracleDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt;
            String strConnString, strStored;

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

            string sCountryCode = "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 (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                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("");
            }

            objConn.Close();
            objConn = null;
        }
    }
}

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;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            OracleConnection objConn = new OracleConnection();
            OracleCommand objCmd = new OracleCommand();

            String strConnString, strStored;

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

            string strCustomerID = "C005";
            string strName = "Fun Wipa";
            string strEmail = "[email protected]";
            string strCountryCode = "TH";
            decimal dBudget = 100000;
            decimal dUsed = 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 = null;
        }
    }
}