首先 , 创建一个队列 Sequence
-- Create sequence
create sequence PRIMARYKEYSEQUENCE
minvalue 0
maxvalue 999999999999999999999999999
start with 83
increment by 1
nocache;
然后,创建触发器Triggers
-- Create triggers
create or replace trigger XG_ATTENDANCETRIGGER
before insert on xg_attendance
for each row
declare
nextid number;
begin
if:new.AID is null or:new.AID=0
then
select primarykeysequence.nextval into nextid from sys.dual;
:new.AID:= nextid;
end if;
end XG_ATTENDANCETRIGGER;
最后,在包Packages中创建存储过程Procedure
-- Create procedure
create or replace package body xg.AttendancePackage is
procedure proc_insertAttendance(wid in varchar2,inTime in varchar2,aid out varchar2)
is
begin
insert into xg.xg_attendance(wid,aintime) values(wid,inTime);
select xg.primarykeysequence.currval into aid from sys.dual;
end;
end AttendancePackage;
在ASP.NET中用企业库调用Oracle的存储过程
using System;
using System.Data;
using EntityLibrary;
using System.Data.Common;
using System.Diagnostics;
public class AttendanceBiz:Biz
{
/// <summary>
/// 记录职工上班时间
/// </summary>
/// <param name="wid"> 职工号 </param>
/// <param name="inTime"> 上班时间 </param>
/// <returns> 出勤记录编号 </returns>
public int SetInTime( string wid, string inTime)
{
DbCommand cmd = _database.GetStoredProcCommand( " AttendancePackage.proc_insertAttendance ");
_database.AddInParameter(cmd, " wid ", DbType.String,wid);
_database.AddInParameter(cmd, " inTime ", DbType.String,inTime);
_database.AddOutParameter(cmd, " aid ", DbType.Int32, 22);
cmd.Connection = _database.CreateConnection();
int aid = 0;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
aid = Convert.ToInt32(cmd.Parameters[ " aid "].Value);
}
catch(DbException e)
{
if (!EventLog.Exists( " csit "))
{
EventLog.CreateEventSource( " csit ", " csit ");
}
EventLog.WriteEntry( " csit ", e.Message);
}
finally
{
cmd.Connection.Close();
}
return aid;
}
}
using System.Data;
using EntityLibrary;
using System.Data.Common;
using System.Diagnostics;
public class AttendanceBiz:Biz
{
/// <summary>
/// 记录职工上班时间
/// </summary>
/// <param name="wid"> 职工号 </param>
/// <param name="inTime"> 上班时间 </param>
/// <returns> 出勤记录编号 </returns>
public int SetInTime( string wid, string inTime)
{
DbCommand cmd = _database.GetStoredProcCommand( " AttendancePackage.proc_insertAttendance ");
_database.AddInParameter(cmd, " wid ", DbType.String,wid);
_database.AddInParameter(cmd, " inTime ", DbType.String,inTime);
_database.AddOutParameter(cmd, " aid ", DbType.Int32, 22);
cmd.Connection = _database.CreateConnection();
int aid = 0;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
aid = Convert.ToInt32(cmd.Parameters[ " aid "].Value);
}
catch(DbException e)
{
if (!EventLog.Exists( " csit "))
{
EventLog.CreateEventSource( " csit ", " csit ");
}
EventLog.WriteEntry( " csit ", e.Message);
}
finally
{
cmd.Connection.Close();
}
return aid;
}
}
本文转自钢钢博客园博客，原文链接：http://www.cnblogs.com/xugang/archive/2008/02/20/1074535.html，如需转载请自行联系原作者
