建表 准备数据。
-- Create table
create table WHS_RY
(
NM VARCHAR2(10) not null,
ID VARCHAR2(10) not null,
NAME VARCHAR2(20) default '꛼˃' not null,
COMPANY VARCHAR2(80) default 'inspur',
ADDR VARCHAR2(80) default 'ɽ´ó·224ºÅ',
BIRTHDAY VARCHAR2(8) default '19810806',
IMAGE BLOB
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table WHS_RY
is 'WHS ²âÊÔ±í';
创建package ,主要是存储过程中要使用到curcer 。要在这里声明。
create or replace package pkg_whs_const as
type REF_CURSOR is ref cursor;
end pkg_whs_const;
创建存储过程
CREATE OR REPLACE PROCEDURE PRO_SELECT_WHS_RY(IDStr IN STRING,
out_curEmp out pkg_whs_const.REF_CURSOR) as
BEGIN
open out_curEmp for
select nm , id , name , company,addr,birthday, image from whs_ry where id = IDStr;
END PRO_SELECT_WHS_RY;
调试 : 在brows里面找到pkg_whs_const ,点击右键 点击"测试" ,填入相应的参数,按F8。出现结果。
C#中低用此存储过程。
try
{
//OleDbConnection cnn = new OleDbConnection();
//cnn.ConnectionString = "Provider=MSDAORA.1;Password=aaaaaa;User ID=lccrcc9999;Data Source=mybase;Persist Security Info=True";
//cnn.Open();
//OleDbCommand comm = new OleDbCommand();
//comm.CommandType = CommandType.StoredProcedure;
//comm.Parameters.Add("IDStr" ,OleDbType.VarChar);
//comm.Parameters.Add(OleDbType.cur);
OracleConnection cnn = new OracleConnection();
cnn.ConnectionString = this.textBox1.Text.Trim();
cnn.Open();
MessageBox.Show("success!");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "PRO_SELECT_WHS_RY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("IDStr", OracleType.Number)).Value = "0000000001";
cmd.Parameters.Add(new OracleParameter("out_curEmp", OracleType.Cursor)).Direction = ParameterDirection.Output;
cmd.Connection = cnn;
//返回的结果集放到Dataset
DataSet ds = new DataSet();
OracleDataAdapter adap = new OracleDataAdapter();
adap.SelectCommand = cmd;
adap.Fill(ds);
this.gridControl1.DataSource = ds.Tables[0].DefaultView;
//返回的结果集使用 OracleDataReader 获取
OracleDataReader rdr;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//对读取的数据进行处理。
}
rdr.Close();
cnn.Close();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}