存储过程操作Oracle

存储过程操作Oracle

 参考http://www.cnblogs.com/datasky/archive/2007/11/07/952141.html

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OracleClient;

/*
 用存储过程实现对数据表的

1 查询 2 修改 3 删除 4 新增

都使用调用存储过程来实现
 */
namespace T1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            /*
             CREATE OR REPLACE PROCEDURE Test_JOB_HISTORY
(
reccount OUT NUMBER
)
IS
BEGIN
SELECT COUNT(*) INTO reccount
FROM workstat_report;
END Test_JOB_HISTORY;
             */
            //调用存储过程得到返回值
            OracleConnection conn = new OracleConnection("Password=TALLY;User ID=TALLY;Data Source=CLAMS2008;Max Pool Size=50");
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "Test_JOB_HISTORY";
            cmd.CommandType = CommandType.StoredProcedure;
            //cmd.Parameters.Add("reccount", OracleType.Number).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("reccount", OracleType.Number);
            cmd.Parameters["reccount"].Direction = ParameterDirection.Output;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            MessageBox.Show(cmd.Parameters["reccount"].Value.ToString());

        }

        private void button2_Click(object sender, EventArgs e)
        {
            /*
             CREATE OR REPLACE PROCEDURE Test_DELETE_JOB_HISTORY
(
p_auto_id NUMBER
)
IS
BEGIN
DELETE FROM 结果
WHERE autoid = p_auto_id;
END Test_DELETE_JOB_HISTORY;
             */
            OracleConnection conn = new OracleConnection("Password=TALLY;User ID=TALLY;Data Source=CLAMS2008;Max Pool Size=50");

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "Test_DELETE_JOB_HISTORY";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("p_auto_id", OracleType.Number).Value = 1;
            OracleString rowId;

            conn.Open();
            int rowsAffected = cmd.ExecuteNonQuery();
            conn.Close();

            MessageBox.Show("Rows affected: " + rowsAffected);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            /*
              
CREATE OR REPLACE FUNCTION Test_GET_Customer_ID(
p_auto_ID NUMBER
)
RETURN VARCHAR2
IS p_Customer_ID VARCHAR2(10);
BEGIN
select t.Customer_ID into p_Customer_ID  from 结果 t
WHERE AUTOID = p_auto_ID;
RETURN p_Customer_ID;
END Test_GET_Customer_ID;             
             */
            OracleConnection conn = new OracleConnection("Password=TALLY;User ID=TALLY;Data Source=CLAMS2008;Max Pool Size=50");

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "Test_GET_Customer_ID";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("p_auto_ID", OracleType.Number).Value = 1;
            cmd.Parameters.Add("p_Customer_ID", OracleType.VarChar, 10).Direction =ParameterDirection.ReturnValue;

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            MessageBox.Show("Customer_ID : " + cmd.Parameters["p_Customer_ID"].Value);

        }

        private void button4_Click(object sender, EventArgs e)
        {
            /*使用多个结果集*/
            /*
CREATE OR REPLACE PACKAGE test_SELECT_data_show AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployeesAndJobs (
cur_result OUT T_CURSOR,
cur_container_info OUT T_CURSOR,
p_recordlist_no in VARCHAR2
);
END test_SELECT_data_show;
             */
            /*
CREATE OR REPLACE PACKAGE BODY test_SELECT_data_show AS
PROCEDURE GetEmployeesAndJobs
(
cur_result OUT T_CURSOR,
cur_container_info OUT T_CURSOR,
p_recordlist_no VARCHAR2
)
IS
BEGIN
-- return all EMPLOYEES records
OPEN cur_result FOR
select t.*  from 结果 t;
-- return all JOBS records
OPEN cur_container_info FOR
select c.* from container_info c where c.recordlist_no=p_recordlist_no;
END GetEmployeesAndJobs;
END test_SELECT_data_show;
             */

            OracleConnection conn = new OracleConnection("Password=TALLY;User ID=TALLY;Data Source=CLAMS2008;Max Pool Size=50");

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "test_SELECT_data_show.GetEmployeesAndJobs";

            cmd.Parameters.Add("cur_result", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("cur_container_info", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("p_recordlist_no", OracleType.VarChar, 20).Value = "D2320166000106100006";



            cmd.CommandType = CommandType.StoredProcedure;

            OracleDataAdapter da = new OracleDataAdapter(cmd);


            da.TableMappings.Add("Table", "结果");
            da.TableMappings.Add("Table1", "container_info");

            DataSet ds = new DataSet();
            da.Fill(ds);

            MessageBox.Show("OK");


        }

        private void button5_Click(object sender, EventArgs e)
        {
            //新增加记录
            /*
             CREATE OR REPLACE PROCEDURE test_ADD_LOCATION (
p_location_id OUT NUMBER,
p_autoid IN NUMBER,
p_customer_ID IN VARCHAR2,
p_CUSTOMER_TYPE  IN CHAR
)
AS
BEGIN

INSERT INTO 结果 (
autoid,
customer_ID,
CUSTOMER_TYPE
)
VALUES (
p_autoid,
p_customer_ID,
p_CUSTOMER_TYPE
);
SELECT p_autoid INTO p_location_id FROM DUAL;
END test_ADD_LOCATION;
             */
            OracleConnection conn = new OracleConnection("Password=TALLY;User ID=TALLY;Data Source=CLAMS2008;Max Pool Size=50");

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "test_ADD_LOCATION";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("p_location_id", OracleType.Number).Direction = ParameterDirection.Output;

            cmd.Parameters.Add("p_autoid", OracleType.Number).Value = 2;

            cmd.Parameters.Add("p_customer_ID", OracleType.VarChar, 10).Value = "test1";

            cmd.Parameters.Add("p_CUSTOMER_TYPE", OracleType.Char).Value = "2";

            OracleString rowId;
            conn.Open();
            int rowsAffected = cmd.ExecuteOracleNonQuery(out rowId);
            conn.Close();


            MessageBox.Show(" Rows affected: " + rowsAffected + "|" + cmd.Parameters["p_location_id"].Value);



        }

        private void button6_Click(object sender, EventArgs e)
        {
            //更新记录 用包头包体的方法解决 把数据加载到OracleDataAdapter 进行更新
            /*
CREATE OR REPLACE PACKAGE test_CRUD_LOCATIONS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR,p_autoid IN NUMBER);
PROCEDURE UpdateLocations (
p_autoid IN NUMBER,
p_customer_ID IN VARCHAR2,
p_CUSTOMER_TYPE IN CHAR
);
END test_CRUD_LOCATIONS;

             


CREATE OR REPLACE PACKAGE BODY test_CRUD_LOCATIONS AS
-- retrieve all LOCATION records
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR,p_autoid IN NUMBER)
IS
BEGIN
OPEN cur_Locations FOR
SELECT * FROM 结果 where autoid=p_autoid;
END GetLocations;
-- update a LOCATION record
PROCEDURE UpdateLocations (
p_autoid IN NUMBER,
p_customer_ID IN VARCHAR2,
p_CUSTOMER_TYPE IN CHAR)
IS
BEGIN
UPDATE 结果
SET
--autoid = p_autoid,
customer_ID = p_customer_ID,
CUSTOMER_TYPE = p_CUSTOMER_TYPE
WHERE
autoid = p_autoid;
END UpdateLocations;
END test_CRUD_LOCATIONS;
             */
            string connString = "Password=TALLY;User ID=TALLY;Data Source=CLAMS2008;Max Pool Size=50";
            

            OracleDataAdapter da = new OracleDataAdapter();
            OracleCommand selectCommand = new OracleCommand("test_CRUD_LOCATIONS.GetLocations", new OracleConnection(connString));
            selectCommand.CommandType = CommandType.StoredProcedure; 
            selectCommand.Parameters.Add("cur_Locations", OracleType.Cursor).Direction = ParameterDirection.Output;

            //selectCommand.Parameters.Add("p_autoid", OracleType.Number, 4, "autoid") = 2;
            //
            selectCommand.Parameters.Add("p_autoid", OracleType.Number).Value = 2;

            da.SelectCommand = selectCommand;




            OracleCommand updateCommand = new OracleCommand("test_CRUD_LOCATIONS.UpdateLocations", new OracleConnection(connString));
            updateCommand.CommandType = CommandType.StoredProcedure;

            updateCommand.Parameters.Add("p_autoid", OracleType.Number, 4, "autoid");

            updateCommand.Parameters.Add("p_customer_ID", OracleType.NVarChar, 10, "customer_ID");
            updateCommand.Parameters.Add("p_CUSTOMER_TYPE", OracleType.Char, 2, "CUSTOMER_TYPE");
            da.UpdateCommand = updateCommand;


            //OracleString rowId;
            //conn.Open();

            //int rowsAffected = updateCommand.ExecuteNonQuery();            
            //    //cmd.ExecuteOracleNonQuery(out rowId);
            //conn.Close();

            DataTable dt = new DataTable();

            try
            {
                da.Fill(dt);
                /*这里可以更新dt*/
                dt = dt_New(dt);
                da.Update(dt);
                MessageBox.Show("OK");

            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }

        }

        private DataTable  dt_New(DataTable dt_Old)
        {
            //对autoid=2 的进行更新
            DataTable dt = dt_Old;
            for (int i = 0; i < dt.Rows.Count; i++)
            {               

                DataRow dr = dt.Rows[i];
                //模拟修改的值
                dr["customer_ID"] = "test1";
                dr["CUSTOMER_TYPE"] = "9";                
                //dt[i]["customer_ID"] = "aaaa111111";
                //dt[i]["CUSTOMER_TYPE"] = "3";                
            }
            return dt;

        }


    }

}