存储过程操作Oracle
存储过程操作Oracle
参考http://www.cnblogs.com/datasky/archive/2007/11/07/952141.html

 Code
Codeusing 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;
}
}
}
 
                    
                 
                
            
         浙公网安备 33010602011771号
浙公网安备 33010602011771号