C#对Oracle存储过程的调用 _c# 调用oracle存储过程字符集问题-CSDN博客

本文链接：https://blog.csdn.net/ypzgq/article/details/5884004

--创建表
declare num number;
begin
select count(1) into num from user_tables where table_name='SRCT';   --判断当前要创建的表在数据库中是否存在.
if num>0
then execute immediate 'drop table '||'SRCT'; --表名要大写
end if;
execute immediate 'CREATE TABLE SRCT
(
  SN  char(11),
  XM  varchar2(30),  --姓名
  KSCJ  number(3),   --考试成绩
  KSRQ  Date         --考试日期

)';
end;
/
commit;
/

--注:上面的表名要大写.

-- 插入数据

declare

maxrecords constant int : = 50 ;

i int : = 1 ;

begin

for i in 1 ..maxrecords

loop

insert into SRCT(SN,XM,KSCJ,KSRQ) values (i, ' frj ' || i,i + 10 ,sysdate);

end loop

-- dbms_output.put_line('成功录入数据!');

commit ;

end ;

-- 查询数据,检查数据插入操作是否成功.

SELECT * FROM SRCT WHERE ROWNUM < 3 ;

-- 检查存储过程是否存在

declare

num number ;

msg varchar2 ( 30 ): = ' 数据库中不存在该存储过程 ' ;

begin

select count ( 1 ) into num

from user_objects

where object_type = ' PROCEDURE '

and object_name = ' WRITE_SRC ' ;

if num > 0

then

msg: = ' 该存储过程已经存在 ' ;

end if ;

dbms_output.put_line(msg);

end ;

-- 创建存储过程

CREATE OR REPLACE Procedure

WRITE_SRC( M_SN in char , M_XM in varchar2 ,M_KSCJ in integer ,

M_KSRQ in Date,RES out integer ,ERR out

varchar2 )

V_COUNT number : = 0 ;

Begin

RES: =- 2 ;

ERR: = ' 数据库中不存在该纪录,更新失败. ' ;

select count (SN) INTO V_COUNT

FROM SRCT

Where SN = M_SN AND XM = M_XM;

IF V_COUNT > 0 THEN

Update SRCT

Set KSCJ = M_KSCJ,KSRQ = M_KSRQ

Where SN = M_SN AND XM = M_XM;

Commit ;

RES: = 1 ;

ERR: = ' 更新成功! ' ;

return ;

END IF ;

exception

when others then

RES: =- 1 ;

ERR: = ' 更新失败 ' ;

return ;

End ;

commit ;

-- 调用存储过程

declare

res int ;

err Varchar2 ( 80 );

Begin

res: = ' 3 ' ;

err: = ' 更新成功 ' ;

WRITE_SRC( ' 1 ' , ' frj1 ' , 300 ,sysdate,res,err);

COMMIT ;

dbms_output.put_line(res);

dbms_output.put_line(err);

End ;

select sn,xm,kscj from srct WHERE SN = ' 1 ' AND XM = ' frj1 ' ;

-- 注:易犯错误

-- 以上语句均在"SQL*Plus 工作单"上运行;

1 .每一个小单元的语句后要加 ' ; ' 号;

2 .不能将字符串赋值的单引号写成双引号;

如: err: = ' 更新成功! ' ; 不能写成 err: = "更新成功!";

以上错误系统将提示:"警告: 创建的过程带有编译错误。"

3 .存储过程传递与赋值的参数名称,个数,类型(字段类型，返回的类型( in/ out))要与调用的存储过程以及该存储过程

所访问的表中相应的字段类型严格对应.

还有一些约定的写法也需遵守.

如:其中的" M_KSCJ "对应表中的" KSCJ "字段, 应写成(M_KSCJ in integer )不能写成( M_KSCJ in number ( 3 ));

"M_SN"对应表中的 "SN" 字段,应写成 (M_SN in char ) 不能写成 (M_SN in Varchar2 )

4 .在c#中进行调用时,还要注意它的输入 / 输出类型,如上例中的" out integer res " 为输出类型,应将其

OracleParameter [] parm = new OracleParameter [ 1 ] ;

parm [ 0 ] = new OracleParameter("RES", OracleType.Int16 );

parm [ 0 ] .Direction = ParameterDirection.Output ; -- 将其设为输出类型;

具体调用方法将在稍后进行介绍;

5 .定义存储过程时,其参数名称最好不要与字段名称同名(不区分大小写);

如上面的存储过程建议不要写成:

WRITE_SRC( SN in char , XM in varchar2 ,KSCJ in integer ,

KSRQ in Date,RES out integer ,ERR out varchar2 )

// --在c#中的调用

public int upInfo( string m_sn, string m_sxm, int m_ikscj,DateTime m_dksrq, out int m_ires, out string m_serr)

{

string ConnStr=GetConnStr();

OracleCommand cmd = new OracleCommand();

OracleConnection conn = new OracleConnection(ConnStr);

int rows = 0;

mres = -110;

merr = "";

try

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "WRITE_SRC";

OracleParameter[] parm = new OracleParameter[6];

//in

parm[0] = new OracleParameter("M_SN", OracleType.Char, 11); --与SQL区别,sql存储过程需要在定义与此处,在其参数前加"@"符号;

parm[1] = new OracleParameter("M_XM", OracleType.VarChar, 2);

parm[2] = new OracleParameter("M_KSCJ", OracleType.Number, 3);

parm[3] = new OracleParameter("M_KSRQ", OracleType.DateTime , 8);

//out

parm[4] = new OracleParameter("RES", OracleType.Int16);

parm[5] = new OracleParameter("ERR", OracleType.VarChar, 50);

//指明参数是输入还是输出型

for (int i = 0; i < parm.Length-2; i++)

{

parm[i].Direction = ParameterDirection.Input;

}

parm[4].Direction = ParameterDirection.Output;

parm[5].Direction = ParameterDirection.Output;

//给参数赋值

parm[0].Value = m_sn;

parm[1].Value = m_sxm;

parm[2].Value = m_ikscj;

parm[3].Value = OracleDateTime.Parse(m_dksrq.ToShortDateString());

--直接用update语句更新时,需要采用下面的日期格式.

-- string msksrq = mksrq.Day.ToString() + "-" + mksrq.Month.ToString() + "月" + " -" + mksrq.Year.ToString().Substring(2, 2);

//传递参数给Oracle命令

for (int i = 0; i < parm.Length; i++)

{

cmd.Parameters.Add(parm[i]);

}

//打开连接

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

rows = cmd.ExecuteNonQuery();

//取出返回值

m_ires = Convert.ToInt16(parm[4].Value);//res

m_serr = parm[5].Value.ToString();//err

}

catch (Exception er)

{

merr = System.Environment.NewLine + "res:" + m_ires.ToString() + "err:" + er.ToString();

MrfuWriteEventLog.C_WriterEventLog.WriteEventLogAppend("UploadDriInfo: mres=" + m_ires.ToString() + "merr:" + er.ToString());

}

finally

{

//关闭连接,释放空间.

if (conn.State == ConnectionState.Open)

conn.Close();

conn.Dispose();

cmd.Parameters.Clear();

cmd.Dispose();

}

return rows;

}

-- 按时间段分页显示

select sn,xm,kscj,ksrq from SRCT

where ksrq between to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ) and to_date( ' 2007-06-28 ' , ' yyyy-mm-dd ' ) order by ksrq;

select * from

( select b. * ,rownum row_num from

(

select sn,xm,kscj,ksrq from SRCT c

where ksrq between to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ) and to_date( ' 2007-06-28 ' , ' yyyy-mm-dd ' ) order by c.sn

)a where a.row_num between 1 and 10 ;

-- 注: oracle的rownum是在提取记录时就已经生成，它先于排序操作，所以必须使用子查询先排序.

-- ==转oracle分页存储过程==

CREATE OR REPLACE PACKAGE DotNet is

TYPE type_cur IS REF CURSOR ; -- 定义游标变量用于返回记录集

PROCEDURE DotNetPagination

(

Pindex in number , -- 分页索引

Psql in varchar2 , -- 产生dataset的sql语句

Psize in number , -- 页面大小

Pcount out number , -- 返回分页总数

v_cur out type_cur -- 返回当前页数据记录

);

procedure DotNetPageRecordsCount

(

Psqlcount in varchar2 , -- 产生dataset的sql语句

Prcount out number -- 返回记录总数

);

end DotNet;

CREATE OR REPLACE PACKAGE BODY DotNet is

-- ***************************************************************************************

PROCEDURE DotNetPagination

(

Pindex in number ,

Psql in varchar2 ,

Psize in number ,

Pcount out number ,

v_cur out type_cur

)

v_sql VARCHAR2 ( 1000 );

v_count number ;

v_Plow number ;

v_Phei number ;

Begin

-- ----------------------------------------------------------取分页总数

v_sql : = ' select count(*) from ( ' || Psql || ' ) ' ;

execute immediate v_sql into v_count;

Pcount : = ceil(v_count / Psize);

-- ----------------------------------------------------------显示任意页内容

v_Phei : = Pindex * Psize + Psize;

v_Plow : = v_Phei - Psize + 1 ;

-- Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段

v_sql : = ' select * from ( ' || Psql || ' ) where rn between ' || v_Plow || ' and ' || v_Phei ;

open v_cur for v_sql;

End DotNetPagination;

-- **************************************************************************************

procedure DotNetPageRecordsCount

(

Psqlcount in varchar2 ,

Prcount out number

)

v_sql varchar2 ( 1000 );

v_prcount number ;

begin

v_sql : = ' select count(*) from ( ' || Psqlcount || ' ) ' ;

execute immediate v_sql into v_prcount;

Prcount : = v_prcount; -- 返回记录总数

end DotNetPageRecordsCount;

-- **************************************************************************************

end DotNet;

// ==使用示例==

/// <summary>

/// 填充dataSet数据集－Oracle库

/// </summary>

/// <param name="pindex">当前页</param>

/// <param name="psql">执行查询的SQL语句</param>

/// <param name="psize">每页显示的记录数</param>

/// <returns></returns>

private bool gridbind( int pindex, string psql, int psize)

{

OracleConnection conn = new OracleConnection();

OracleCommand cmd = new OracleCommand();

OracleDataAdapter dr = new OracleDataAdapter();

conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

cmd.Connection = conn;

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

cmd.CommandText = "DotNet.DotNetPageRecordsCount";

cmd.Parameters.Add("psqlcount", OracleType.VarChar).Value = psql;

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

cmd.ExecuteNonQuery();

string PCount = cmd.Parameters["prcount"].Value.ToString();

cmd.Parameters.Clear();

cmd.CommandText = "DotNet.DotNetPagination";

if (pindex != 0)

{

cmd.Parameters.Add("pindex", OracleType.Number).Value = pindex - 1;

}

else

{

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

}

cmd.Parameters.Add("psql", OracleType.VarChar).Value = psql;

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

cmd.Parameters.Add("v_cur", OracleType.Cursor).Direction = ParameterDirection.Output;

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

dr.SelectCommand = cmd;

try

{

ds = new DataSet();

dr.Fill(ds);

//显示页码条的状态

showStatus(Convert.ToInt32(cmd.Parameters["pindex"].Value) + 1,

Convert.ToInt32(cmd.Parameters["pcount"].Value),

Convert.ToInt32(PCount));

for (int i = 0; i < ds.Tables.Count; i++)

{ //把数据行为零的表删除

if (ds.Tables[i].Rows.Count == 0)

ds.Tables.Remove(ds.Tables[i].TableName);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

return false;

}

conn.Close();

return true;

}